Spring 数据访问那些事儿（二）Spring + JdbcTemplate

哈喽，大家好，很高兴大家能看到这篇粗浅的博客，在这鸡年即将到来的时刻，提前给大家拜个早年，祝大家新的一年里“万行code丛中过，片叶bug不沾身”。今天给大家介绍一下spring结合jdbcTemplate进行数据库的访问操作。

前一篇文章，简要说明了spring与jdbc进行数据操作的过程，有很多沉重冗余的代码，在JdbcTemplate的帮助下，你会省下很多重复的代码，因为JdbcTemplate会帮你自动处理。本篇文章继续用上一篇的cutomer表做示例说明。

1.通过eclipse创建maven项目，目录结构如下：

2.项目依赖pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">  <modelVersion>4.0.0</modelVersion>  <groupId>spring-jdbcTemplate</groupId>  <artifactId>spring-jdbcTemplate</artifactId>  <version>0.0.1-SNAPSHOT</version><dependencies>      <!-- Spring Core --><dependency><groupId>org.springframework</groupId><artifactId>spring-core</artifactId><version>4.2.1.RELEASE</version></dependency><!-- Spring context --><dependency><groupId>org.springframework</groupId><artifactId>spring-context</artifactId><version>4.2.1.RELEASE</version></dependency>  <!-- Spring JDBC --><dependency><groupId>org.springframework</groupId><artifactId>spring-jdbc</artifactId><version>4.2.1.RELEASE</version></dependency><dependency>  <groupId>commons-dbcp</groupId>  <artifactId>commons-dbcp</artifactId>  <version>1.4</version>  </dependency><!-- mysql驱动包 -->          <dependency>              <groupId>mysql</groupId>              <artifactId>mysql-connector-java</artifactId>              <version>5.1.29</version>          </dependency>    </dependencies></project>

3.DAO层

这里的dao层与jdbc的dao层是最大的不同之处，jdbcTemplate对象封住了很多方法，你只需要专注自己的业务逻辑，而不是像jdbc那样冗余的代码。异常也不需要你来补货，而是jdbctemplate会自动帮你处理。

package org.thinkingingis.dao;import java.sql.ResultSet;import java.sql.SQLException;import java.util.List;import javax.sql.DataSource;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowMapper;import org.thinkingingis.model.Customer;public class JdbcTemplateCustomerDAO {private JdbcTemplate jdbcTemplate;public void setDataSource(DataSource dataSource) {        this.jdbcTemplate = new JdbcTemplate(dataSource);    }//统计该表所有记录数public int CountRowNumber(){int rowCount = this.jdbcTemplate.queryForObject("select count(*) from customer", Integer.class);return rowCount;}//根据cust_id查customer对象public Customer findCustomerById(int id){String sql = "select * from customer where cust_id = ?";Customer customer = this.jdbcTemplate.queryForObject(sql, new Object[]{id}, new RowMapper<Customer>(){public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {Customer customers = new Customer();customers.setId(rs.getInt("cust_id"));customers.setName(rs.getString("name"));customers.setAge(rs.getInt("age"));return customers;}});return customer;}//根据id获得namepublic String getCustomerNameById(int id){String strName = this.jdbcTemplate.queryForObject("select name from customer where cust_id = ?", new Object[]{id}, String.class);return strName;}//查询所有表数据public List<Customer> findAllCustomer(){return this.jdbcTemplate.query("select * from customer", new CustomerMapper());}public static final class CustomerMapper implements RowMapper<Customer>{public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {Customer customer = new Customer();customer.setId(rs.getInt("cust_id"));customer.setName(rs.getString("name"));customer.setAge(rs.getInt("age"));return customer;}}//更新操作public void updateCustomerNameById(String name, int id){this.jdbcTemplate.update("update customer set name = ? where cust_id = ?", name, id);}}

4.通过xml文件方式配置数据源与bean

这次将数据源与jdbcTemplateCustomerDAO写在一个xml文件中

<beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans-4.0.xsd"><bean id="dataSource"class="org.apache.commons.dbcp.BasicDataSource"><property name="driverClassName" value="com.mysql.jdbc.Driver" /><property name="url" value="jdbc:mysql://localhost:3306/springmvcjdbc" /><property name="username" value="root" /><property name="password" value="123456" /></bean><bean id="jdbcTemplateCustomerDAO" class="org.thinkingingis.dao.JdbcTemplateCustomerDAO"> <property name="dataSource" ref="dataSource"/></bean></beans>

5.controller层 App.java

package org.thinkingingis.controller;import java.util.List;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;import org.thinkingingis.dao.JdbcTemplateCustomerDAO;import org.thinkingingis.model.Customer;public class App {public static void main(String[] args){ApplicationContext context = new ClassPathXmlApplicationContext("application-context.xml");JdbcTemplateCustomerDAO jdbcTemplateCustomerDao = (JdbcTemplateCustomerDAO) context.getBean("jdbcTemplateCustomerDAO");int rowCount = jdbcTemplateCustomerDao.CountRowNumber();System.out.println(rowCount);jdbcTemplateCustomerDao.updateCustomerNameById("CAN", 4);Customer customer = jdbcTemplateCustomerDao.findCustomerById(1);System.out.println("name: " + customer.getName());String name = jdbcTemplateCustomerDao.getCustomerNameById(2);System.out.println("id 为2 的 name: " + name);List<Customer> lists = jdbcTemplateCustomerDao.findAllCustomer();for(int i=0; i<lists.size(); i++){System.out.println(lists.get(i).getId() + "--" + lists.get(i).getName() + "--" + lists.get(i).getAge());}}}

6结果截图

spring与jdbctemplate访问数据就完成啦，值得注意的是相比jdbc方式，他会省去你很多时间去写jdbc代码。