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获得name
	public 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/beans
	http://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代码。

（如遇到问题，请留言给作者，以便共同探讨gis知识。thinkingingis@qq.com）

Wechat公众号：ThinkingInGIS

欢迎大家关注：）