使用Spring JDBCTemplate进行增删改查curd操作

• 项目介绍

Spring中关于JDBC的一个辅助类(JDBC Template)，它封装了JDBC的操作，使用它来构建项目

• 项目结构

创建maven项目，结构如上图

• maven依赖jar包汇总

  <dependencies>    <dependency>      <groupId>junit</groupId>      <artifactId>junit</artifactId>      <version>3.8.1</version>      <scope>test</scope>    </dependency>    <dependency><groupId>javax.servlet</groupId><artifactId>javax.servlet-api</artifactId><version>3.1.0</version></dependency>    <dependency><groupId>org.springframework</groupId><artifactId>spring-jdbc</artifactId><version>4.2.4.RELEASE</version></dependency>      <dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.35</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-context</artifactId><version>4.2.4.RELEASE</version></dependency>  </dependencies>

• 新建测试类，创建数据表

package com.curd.spring.test;import org.junit.Test;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.datasource.DriverManagerDataSource;public class JDBCTemplate {@Testpublic void demo(){DriverManagerDataSource dataSource=new DriverManagerDataSource();dataSource.setDriverClassName("com.mysql.jdbc.Driver");dataSource.setUrl("jdbc:mysql:///test");dataSource.setUsername("root");dataSource.setPassword("");JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);jdbcTemplate.execute("CREATE TABLE temp(`id` int(11) NOT NULL,`name` varchar(32) DEFAULT NULL,`password` varchar(32) DEFAULT NULL,PRIMARY KEY (`id`))");}}

创建完的表结构如下

• appliactionContext.xml配置文件

<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans"        xmlns:p="http://www.springframework.org/schema/p"        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.xsd">  <!--数据源的配置 --><bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"><property name="driverClassName" value="com.mysql.jdbc.Driver"></property><property name="url" value="jdbc:mysql:///test"></property><property name="username" value="root"></property><property name="password" value=""></property></bean><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><property name="dataSource" ref="dataSource"></property></bean> <bean id="userDao" class="com.curd.spring.dao.impl.UserDAOImpl"><property name="jdbcTemplate" ref="jdbcTemplate"></property></bean></beans>

注意修改数据库配置为你本地的配置

• 模型类User.java

package com.curd.spring.model;public class User {private int id;private String username;private String password;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}@Overridepublic String toString() {return "User [id=" + id + ", username=" + username + ", password="+ password + "]";}}

• UserDAO 接口IUserDAO.java

package com.curd.spring.dao;import java.util.List;import com.curd.spring.model.User;public interface IUserDAO {public void addUser(User user);    public void deleteUser(int id);    public void updateUser(User user);    public String searchUserName(int id);    public User searchUser(int id);    public List<User> findAll();}

• 接口实现类 UserDAOImpl.java

   按照以往Spring的依赖注入，我们需要在接口实现类中利用构造器去获取JdbcTemplate ，Spring早就帮我们想到了这点， 它为我们提供了JdbcDaoSupport支持类，所有DAO继承这个类，就会自动获得JdbcTemplate（前提是注入DataSource）

   在我们的实现类中直接利用getJdbcTemplate就可以获取操作对象了。
JdbcTemplate主要提供下列方法：
1、execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；
2、update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批处理相关语句；
3、query方法及queryForXXX方法：用于执行查询相关语句；
4、call方法：用于执行存储过程、函数相关语句。

package com.curd.spring.dao.impl;

import java.sql.ResultSet;import java.sql.SQLException;import java.util.List;import org.springframework.jdbc.core.RowMapper;import org.springframework.jdbc.core.support.JdbcDaoSupport;import com.curd.spring.dao.IUserDAO;import com.curd.spring.model.User;public class UserDAOImpl extends JdbcDaoSupport implements IUserDAO {@Overridepublic void addUser(User user) {// TODO Auto-generated method stubString sql = "insert into temp values(?,?,?)";this.getJdbcTemplate().update(sql, null,user.getUsername(),user.getPassword());}@Overridepublic void deleteUser(int id) {// TODO Auto-generated method stubString sql = "delete from temp where id = ?";this.getJdbcTemplate().update(sql, id);}@Overridepublic void updateUser(User user) {// TODO Auto-generated method stubString sql = "update temp set username =?,password=? where id = ?";this.getJdbcTemplate().update(sql, user.getUsername(),user.getPassword(),user.getId());}@Overridepublic String searchUserName(int id) {// TODO Auto-generated method stubString sql = "select username from temp where id = ?";return  this.getJdbcTemplate().queryForObject(sql, String.class, id);}@Overridepublic User searchUser(int id) {// TODO Auto-generated method stubString sql="select * from temp where id=?";return this.getJdbcTemplate().queryForObject(sql, new UserRowMapper(), id);}@Overridepublic List<User> findAll() {// TODO Auto-generated method stubString sql = "select * from temp";return this.getJdbcTemplate().query(sql, new UserRowMapper());} class UserRowMapper implements RowMapper<User> {//rs为返回结果集，以每行为单位封装着@Overridepublic User mapRow(ResultSet rs, int rowNum) throws SQLException {// TODO Auto-generated method stubUser user = new User();user.setId(rs.getInt("id"));user.setUsername(rs.getString("username"));user.setPassword(rs.getString("password"));return user;} }}

• 测试类UserTest.java

package com.curd.spring.test;import java.util.List;import org.junit.Test;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;import com.curd.spring.dao.IUserDAO;import com.curd.spring.model.User;public class UserTest {@Testpublic void  testaddUser(){User user = new User();user.setUsername("liu");user.setPassword("6");ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");IUserDAO dao = (IUserDAO) ctx.getBean("userDao");dao.addUser(user);}@Test    public void testdeleteUser(){ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");IUserDAO dao = (IUserDAO) ctx.getBean("userDao");dao.deleteUser(1);    };        @Test    public void testupdateUser(){    User user = new User();    user.setId(1);    user.setUsername("zhi");    user.setPassword("1234");    ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");    IUserDAO dao = (IUserDAO) ctx.getBean("userDao");    dao.updateUser(user);    };    @Test    public void testsearchUserName(){    ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");    IUserDAO dao = (IUserDAO) ctx.getBean("userDao");    String  username = dao.searchUserName(2);    System.out.println(username);    };    @Test    public void testsearchUser(){    ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");    IUserDAO dao = (IUserDAO) ctx.getBean("userDao");    User  u = dao.searchUser(2);        System.out.println(u.toString());        };    @Test    public void testfindAll(){    ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");    IUserDAO dao = (IUserDAO) ctx.getBean("userDao");    List<User>  u = dao.findAll();     for (User user : u) {System.out.println(user.toString());}        };}

赶紧测试一下吧！

• 知识点汇总

1、Spring 为每种持久化技术 提供一个支持类，在DAO 中注入 模板工具类
（1）JDBC ： org.springframework.jdbc.core.support.JdbcDaoSupport
（2）Hibernate 3.0 ：org.springframework.orm.hibernate3.support.HibernateDaoSupport
（3）iBatis ：org.springframework.orm.ibatis.support.SqlMapClientDaoSupport
用户自己编写DAO 只需要继承 JdbcDaoSupport， 就可以注入 JdbcTemplate
2、 通过jdbcTemplate 提供 int update(String sql, Object... args) 实现增加 、修改 、删除
3、简单查询，返回原始数据类型， String类型
String sql = "select count(*) from user";
// int queryForInt(String sql)
String sql = "select name from user where id = ? ";
// <T> T queryForObject(String sql, Class<T> requiredType, Object... args)
4、 复杂查询 JdbcTemplate 没有handler， 手动完成对象封装

编写实体类 RowMapper
class UserRowMapper implements RowMapper<User> {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
// rs 已经指向每一条数据，不需要自己调用 next，将rs指向数据 转换 User对象
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
return user;
}
}
查询单个对象 <T> T
queryForObject(String sql, RowMapper<T> rowMapper, Object... args)
return this.getJdbcTemplate().queryForObject(sql, new UserRowMapper(),id);
查询所有对象List集合 <T> List<T>
query(String sql, RowMapper<T> rowMapper, Object... args)
return this.getJdbcTemplate().query(sql, new UserRowMapper());

• 参考资料  

http://www.tuicool.com/articles/nYjEjy

• 源码下载

http://download.csdn.net/detail/sunxiaoyu94/9402507