spring jdbcTemplate的使用 (原创)

如题，spring框架中spring-orm包含了众多集成dao成的框架，如jdbcTemplate 封装对jdbc的操作，   hibernateTemplate封装了对hibernate的操作等。本篇，我们介绍下spring中使用JdbcTemplate封装的api操作底层的jdbc数据库操作。主要使用的是DriverManagerDataSource 类，暂未涉及到数据库连接池。

以下是示例代码：

JdbcTemplateFactory.java

package com.tingcream.springJdbc.jdbcTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

 /**
  * jdbcTemplate工厂 
  * 
  * @author jelly
  *
  */
public class JdbcTemplateFactory {
	private DriverManagerDataSource dataSource;
	private  JdbcTemplate jdbcTemplate;
	{
		  dataSource = new DriverManagerDataSource();
		  dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		  dataSource.setUrl("jdbc:mysql://localhost:3306/myDB?useUnicode=true&characterEncoding=UTF8&useSSL=false");
		  dataSource.setUsername("root");
		  dataSource.setPassword("123456");
		  
		  jdbcTemplate=new JdbcTemplate(dataSource);
	}
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
	public DriverManagerDataSource getDataSource() {
		return dataSource;
	}
	
}

spring.xml 

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:cache="http://www.springframework.org/schema/cache"
    xsi:schemaLocation="
          http://www.springframework.org/schema/beans
          http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
          http://www.springframework.org/schema/context
          http://www.springframework.org/schema/context/spring-context-4.3.xsd
          http://www.springframework.org/schema/aop
          http://www.springframework.org/schema/aop/spring-aop-4.3.xsd
          http://www.springframework.org/schema/tx
          http://www.springframework.org/schema/tx/spring-tx-4.3.xsd
          http://www.springframework.org/schema/cache
          http://www.springframework.org/schema/cache/spring-cache-4.3.xsd">
    
       
    
   <bean id="jdbcTemplateFactory" class="com.tingcream.springJdbc.jdbcTemplate.JdbcTemplateFactory">
   </bean>
     
    <bean id="jdbcTemplate" factory-bean="jdbcTemplateFactory"     factory-method="getJdbcTemplate">
    </bean>
    
    
</beans>

我们在业务代码中如何使用JdbcTemplate呢？

JdbcTemplateTest.java

package com.tingcream.springJdbc.test;

import java.util.List;

import javax.annotation.Resource;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import com.tingcream.springJdbc.model.User;
import com.tingcream.springJdbc.model.UserRowMapper;
 

/**
 * spring4测试
 * 
 * jdbcTemplate CRUD 使用
 * @author jelly
 *
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration({"/spring.xml"})
public class JdbcTemplateTest {
	//@Resource
	//private  JdbcTemplateFactory jdbcTemplateFactory;
	
	@Resource
	private JdbcTemplate  jdbcTemplate ;
	/**
	 * 根据id查询一个用户
	 */
	@Test
	public void test1(){
		String sql ="select * from t_user where id=?";
	    User user=   jdbcTemplate.queryForObject(sql, new UserRowMapper(), 2);
	   System.out.println(user);
	}
	
	/**
	 * 查询列表
	 */
	@Test
	public void test2(){
		//根据条件查询列表
			String sql ="select * from t_user where age>?";
			  List list =	jdbcTemplate.query(sql,  new UserRowMapper(),new Object[]{20});
			   System.out.println(list);
		
 //查询所有列表
//		 	String sql ="select * from t_user ";
//			List list = jdbcTemplate.query(sql, new UserRowMapper());
//			System.out.println(list); 
		 
	}
	
	/**
	 * 添加用户
	 */
	@Test
	public void  test3(){
		  String sql ="INSERT INTO t_user(`name`,sex,age,city,`describe`) VALUES(?,?,?,?,?)";
	  	 int count =jdbcTemplate.update(sql, new Object[]{"陈六",1,24,"成都","aaaa"});
		System.out.println("受影响的行数:"+count);
		
	}
	
	/**
	 * 修改用户
	 */
	@Test
	public void test4(){
		  String sql ="UPDATE t_user a SET a.`age`=? ,a.`city`=? WHERE a.`id`=?";
		  int count = jdbcTemplate.update(sql, new Object[]{33,"广州",4});
		System.out.println("受影响的行数:"+count);
	}
	
	/**
	 * 删除用户
	 */
	@Test
	public void test5(){
		  String sql ="DELETE  FROM t_user  WHERE id=?";
		  int count = jdbcTemplate.update(sql, new Object[]{1});
		System.out.println("受影响的行数:"+count);
		
	}
}

User.java

package com.tingcream.springJdbc.model;

import java.io.Serializable;

public class User implements Serializable{
 
	private static final long serialVersionUID = 1L;
	private Integer id;//id主键自增
	private String  name;//姓名
	private Integer sex;//性别 1男 2女
	private Integer age;//年龄
	private String city;//城市
	private String describe;//描述
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Integer getSex() {
		return sex;
	}
	public void setSex(Integer sex) {
		this.sex = sex;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
	public String getCity() {
		return city;
	}
	public void setCity(String city) {
		this.city = city;
	}
	public String getDescribe() {
		return describe;
	}
	public void setDescribe(String describe) {
		this.describe = describe;
	}

}

package com.tingcream.springJdbc.model;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class UserRowMapper implements RowMapper {

	@Override
	public User mapRow(ResultSet rs, int rowNum) throws SQLException {
		  User user =new User();
		  user.setId(rs.getInt("id"));
		  user.setName(rs.getString("name"));
		  user.setSex(rs.getInt("sex"));
		  user.setAge(rs.getInt("age"));
    	  user.setCity(rs.getString("city"));
		  user.setDescribe(rs.getString("describe"));
	      return user;
	}

}

Servlet中使用spring 容器中的bean，并使用jdbcTemplate查询用户列表

UserFindServlet.java

package com.tingcream.springJdbc.web;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.jdbc.core.JdbcTemplate;

import com.alibaba.fastjson.JSON;
import com.tingcream.springJdbc.common.SpringContextHelper;
import com.tingcream.springJdbc.model.User;
import com.tingcream.springJdbc.model.UserRowMapper;


@WebServlet("/userFindServlet")
public class UserFindServlet extends HttpServlet{
 
	private static final long serialVersionUID = 1L;

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		this.doPost(req, resp);
	}

	@Override
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		 request.setCharacterEncoding("utf-8");
		 response.setContentType("text/html;charset=utf-8");
		 
		 JdbcTemplate jdbcTemplate= SpringContextHelper.getBean("jdbcTemplate", JdbcTemplate.class);
		 
		     String sql ="select * from t_user ";
			 List<User> list = jdbcTemplate.query(sql, new UserRowMapper());
			//System.out.println(list); 
			//输出json
		    response.getWriter().write(JSON.toJSONString(list));
		 
	}

}

SpringContextHelper.java

package com.tingcream.springJdbc.common;

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

/**
 * Spring 上下文辅助类
 * @author jelly
 *
 */
@Component
public class SpringContextHelper implements ApplicationContextAware {
       private static ApplicationContext applicationContext;  
       
       @Override  
       public void setApplicationContext(ApplicationContext applicationContext)  
               throws BeansException {  
           SpringContextHelper.applicationContext = applicationContext;  
       }  
         
       public static ApplicationContext getApplicationContext(){  
           return applicationContext;  
       }  
         
       public static Object getBean(String name){  
           return applicationContext.getBean(name);  
       }  
        /**
         * 从spring 上下文中获取bean
         * @param name
         * @param requiredClass
         * @return
         */
       public static <T> T getBean(String name, Class<T>  requiredClass){  
           return applicationContext.getBean(name, requiredClass);  
       }  
       public  static <T> T getBean(Class<T> requiredType){
    	   return applicationContext.getBean(requiredType);
       }
  
     
}

pom.xml

 
<!-- spring 事务管理jar包 -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>4.3.2.RELEASE</version>
</dependency>
 
<!-- spring 容器上下文 jar包 -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>4.3.2.RELEASE</version>
</dependency>
 
      <!-- spring-jdbc 事务支持jar包 -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.3.2.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-orm</artifactId>
    <version>4.3.2.RELEASE</version>
</dependency>
 
  <!-- spring 测试支持jar包 -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-test</artifactId>
    <version>4.3.2.RELEASE</version>
</dependency>
  
 <!-- mysql jdbc 驱动 -->
 <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.40</version>
</dependency>

<!-- 阿里巴巴 fastJson 解析器 jar包 -->
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>fastjson</artifactId>
	<version>1.2.31</version>
</dependency> 

ok！！