Spring JDBC 核心用法大总

User:

package com.domain;public class User {private Integer id;private String name;private Integer age;public User(){}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public User(Integer id, String name, Integer age) {this.id = id;this.name = name;this.age = age;}public void setName(String name) {this.name = name;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age = age;}}

Student：

package com.domain;public class Student {private Integer id;private String name;private Integer age;public Student(){}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public Student(Integer id, String name, Integer age) {this.id = id;this.name = name;this.age = age;}public void setName(String name) {this.name = name;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age = age;}}

UserDao:

package com.dao;import java.util.List;import com.domain.Student;import com.domain.User;public interface UserDao {public void createUser();public void insertUser();public User queryUser(int id,String name);public List<User> queryUsers();public List<User> queryUsersByFinalClass();public User queryForListUser(int id,String name,int age);public int queryCountForUserUseNameParam(int id,String name,int age);public int queryCountUseBeanPro(User user);public int[] batchUpdateUsers(List<User> users);public int[] batchUpdateUseNameedParam(List<User> users);public int[] batchUpdateUserObjArr(List<User> users);public void addUserUseSimpleInsert(User user);public void addUserUseSimpleInsertWithGeneratedKey(Student Student);public void addUseBeanPro(Student stu);public void addUserUseMapParam(Student stu);public Student callProc(Integer id);public Student callProcWithType(Integer id);}

UserDaoImpl:

package com.dao.impl;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Types;import java.util.ArrayList;import java.util.Collections;import java.util.HashMap;import java.util.List;import java.util.Map;import javax.sql.DataSource;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.jdbc.core.BatchPreparedStatementSetter;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowMapper;import org.springframework.jdbc.core.SqlOutParameter;import org.springframework.jdbc.core.SqlParameter;import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;import org.springframework.jdbc.core.namedparam.SqlParameterSource;import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;import org.springframework.jdbc.core.simple.SimpleJdbcCall;import org.springframework.jdbc.core.simple.SimpleJdbcInsert;import org.springframework.stereotype.Repository;import com.dao.UserDao;import com.domain.Student;import com.domain.User;@Repository("userDaoImpl")public class UserDaoImpl implements UserDao {private JdbcTemplate jdbcTemplate;private SimpleJdbcInsert insertActor;private SimpleJdbcInsert insertActor2;private SimpleJdbcCall procReadActor;private SimpleJdbcCall procReadActor2;private NamedParameterJdbcTemplate namedParameterJdbcTemplate;/*public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {this.jdbcTemplate = jdbcTemplate;}*/@Autowiredpublic void setDataSource(DataSource dataSource) {this.jdbcTemplate = new JdbcTemplate(dataSource);this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);this.insertActor = new SimpleJdbcInsert(dataSource).withTableName("user");this.insertActor2 = new SimpleJdbcInsert(dataSource).withTableName("student").usingGeneratedKeyColumns("id");;this.procReadActor = new SimpleJdbcCall(dataSource).withProcedureName("myproc");jdbcTemplate.setResultsMapCaseInsensitive(true);this.procReadActor2 = new SimpleJdbcCall(jdbcTemplate).withProcedureName("myproc").withoutProcedureColumnMetaDataAccess().useInParameterNames("in_id").declareParameters(new SqlParameter("in_id", Types.INTEGER),new SqlOutParameter("out_name", Types.VARCHAR),new SqlOutParameter("out_age", Types.INTEGER));}public void createUser() {jdbcTemplate.execute("create table user(id int,name varchar(32),age int)");}public void insertUser(){for(int i=1;i<10;i++){jdbcTemplate.update("insert into user (id, name,age) values (?, ?,?)",i, "tom",i);}}public User queryUser(int id, String name) {User user = jdbcTemplate.queryForObject("select id, name,age from user where id = ? and name = ?",new Object[]{id,name},new RowMapper<User>() {public User mapRow(ResultSet rs, int rowNum) throws SQLException {User user = new User();user.setId(rs.getInt("id"));user.setAge(rs.getInt("age"));user.setName(rs.getString("name"));return user;}});return user;}@Overridepublic List<User> queryUsers() {List<User> users = jdbcTemplate.query("select * from user", new RowMapper<User>(){@Overridepublic User mapRow(ResultSet rs, int rowNum) throws SQLException {User user = new User();user.setAge(rs.getInt("age"));user.setId(rs.getInt("id"));user.setName(rs.getString("name"));return user;}});return users;}@Overridepublic List<User> queryUsersByFinalClass() {return jdbcTemplate.query("select * from user",new UserMapper());}private static final class UserMapper implements RowMapper<User>{@Overridepublic User mapRow(ResultSet rs, int rowNum) throws SQLException {User user = new User();user.setAge(rs.getInt("age"));user.setId(rs.getInt("id"));user.setName(rs.getString("name"));return user;}}@Overridepublic User queryForListUser(int id, String name,int age) {List<Map<String, Object>> entries = jdbcTemplate.queryForList("select * from user where id = ? and name = ? and age = ?", new Object[]{id,name,age});User user = new User();for(Map<String, Object> entry : entries){user.setId((Integer)entry.get("Id"));user.setName((String)entry.get("name"));user.setAge((Integer)entry.get("age"));}return user;}@Overridepublic int queryCountForUserUseNameParam(int id, String name, int age) {String sql = "select count(*) from user where id = :id and name = :name and age = :age";MapSqlParameterSource namedParameters = new MapSqlParameterSource();namedParameters.addValue("id", id);namedParameters.addValue("name",name);namedParameters.addValue("age", age);return namedParameterJdbcTemplate.queryForObject(sql, namedParameters,Integer.class);}@Overridepublic int queryCountUseBeanPro(User user) {String sql = "select count(*) from user where name = :name and age = :age";SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(user);return namedParameterJdbcTemplate.queryForObject(sql, namedParameters,Integer.class);}@Overridepublic int[] batchUpdateUsers(final List<User> users) {int[] updateCounts = jdbcTemplate.batchUpdate("update user set id = ?," +" name = ? where age = ?",new BatchPreparedStatementSetter() {public void setValues(PreparedStatement ps, int i) throws SQLException {ps.setInt(1, users.get(i).getId());ps.setString(2, users.get(i).getName());ps.setLong(3, users.get(i).getAge());}public int getBatchSize() {return users.size();}});return updateCounts;}@Overridepublic int[] batchUpdateUseNameedParam(List<User> users) {SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(users.toArray());int[] updateCounts = namedParameterJdbcTemplate.batchUpdate("update user set id = :id, name = :name where age = :age",batch);return updateCounts;}@Overridepublic int[] batchUpdateUserObjArr(List<User> users) {List<Object[]> batch = new ArrayList<Object[]>();for (User user : users) {Object[] values = new Object[] {user.getId(),user.getName(),user.getAge()};batch.add(values);}int[] updateCounts = jdbcTemplate.batchUpdate("update user set id = ?, name = ? where age = ?",batch);return updateCounts;}@Overridepublic void addUserUseSimpleInsert(User user) {Map<String, Object> parameters = new HashMap<String, Object>(3);parameters.put("id", user.getId());parameters.put("name", user.getName());parameters.put("age", user.getAge());insertActor.execute(parameters);}@Overridepublic void addUserUseSimpleInsertWithGeneratedKey(Student student) {Map<String, Object> parameters = new HashMap<String, Object>(2);parameters.put("name", student.getName());parameters.put("age", student.getAge());Number newId = insertActor2.executeAndReturnKey(parameters);}@Overridepublic void addUseBeanPro(Student stu) {SqlParameterSource parameters = new BeanPropertySqlParameterSource(stu);Number newId = insertActor2.executeAndReturnKey(parameters);stu.setId(newId.intValue());}@Overridepublic void addUserUseMapParam(Student stu) {SqlParameterSource parameters = new MapSqlParameterSource().addValue("name", stu.getName()).addValue("age", stu.getAge());Number newId = insertActor.executeAndReturnKey(parameters);}@Overridepublic Student callProc(Integer id) {SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);Map<String, Object> out = procReadActor.execute(in);Student stu = new Student();stu.setId(id);stu.setName((String)out.get("out_name"));stu.setAge((Integer)out.get("out_age"));return stu;}@Overridepublic Student callProcWithType(Integer id) {SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);Map<String, Object> out = procReadActor2.execute(in);Student stu = new Student();stu.setId(id);stu.setName((String)out.get("out_name"));stu.setAge((Integer)out.get("out_age"));return stu;}}

UserDaoTest：

package com.dao.test;import java.util.ArrayList;import java.util.List;import junit.framework.Assert;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.test.context.ContextConfiguration;import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;import com.dao.UserDao;import com.domain.Student;import com.domain.User;@RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration("classpath:beans.xml")public class UserDaoTest {@Autowiredprivate UserDao userDao;//@Autowiredprivate JdbcTemplate jdbcTemplate;@Testpublic void testCreateUser() {userDao.createUser();}@Testpublic void testInsertUser() {/*userDao.insertUser();int rowCount = jdbcTemplate.queryForObject("select count(*) from user",Integer.class);Assert.assertEquals(9, rowCount);*//*Object[] obj = new Object[]{2,"tom"};int countOfActorsNamedJoe = this.jdbcTemplate.queryForObject("select count(*) from user where id = ? and name = ?", Integer.class, obj);Assert.assertEquals(1,countOfActorsNamedJoe);*/String lastName = this.jdbcTemplate.queryForObject("select name from user where id = ?",new Object[]{2}, String.class);Assert.assertEquals("tom",lastName);}@Testpublic void deleteUser(){jdbcTemplate.execute("delete from user");}@Testpublic void testQueryUser(){Assert.assertEquals("tom",userDao.queryUser(2, "tom").getName());}@Testpublic void testQueryUsers(){Assert.assertEquals(9,userDao.queryUsers().size());}@Testpublic void testQueryUsersByFinalClass(){Assert.assertEquals(9,userDao.queryUsersByFinalClass().size());}@Testpublic void testQueryForListUser(){User u = userDao.queryForListUser(2, "tom", 2);Assert.assertEquals(2,u.getAge().intValue());}@Testpublic void testQueryCountForUserUseNameParam(){Assert.assertEquals(1,userDao.queryCountForUserUseNameParam(2,"tom",2));}@Testpublic void testQueryCountUseBeanPro(){User u = new User();u.setId(2);u.setName("tom");u.setAge(2);Assert.assertEquals(1,userDao.queryCountUseBeanPro(u));}@Testpublic void testBatchUpdateUsers(){List<User> users = new ArrayList<User>();User u1 = new User(22,"jack",2);User u2 = new User(33,"jimi",3);User u3 = new User(44,"jiji",4);users.add(u1);users.add(u2);users.add(u3);int[] arr = {1,1,1};int[] effected = userDao.batchUpdateUsers(users);for(int i=0;i<effected.length;i++){Assert.assertEquals(arr[i],effected[i]);}}@Testpublic void testBatchUpdateUseNameedParam(){List<User> users = new ArrayList<User>();User u1 = new User(222,"aaa",2);User u2 = new User(333,"bbb",3);User u3 = new User(444,"ccc",4);users.add(u1);users.add(u2);users.add(u3);int[] arr = {1,1,1};int[] effected = userDao.batchUpdateUseNameedParam(users);for(int i=0;i<effected.length;i++){Assert.assertEquals(arr[i],effected[i]);}}@Testpublic void testBatchUpdateUserObjArr(){List<User> users = new ArrayList<User>();User u1 = new User(2222,"dd",2);User u2 = new User(3333,"ee",3);User u3 = new User(4444,"ff",4);users.add(u1);users.add(u2);users.add(u3);int[] arr = {1,1,1};int[] effected = userDao.batchUpdateUseNameedParam(users);for(int i=0;i<effected.length;i++){Assert.assertEquals(arr[i],effected[i]);}}@Testpublic void testAddUserUseSimpleInsert(){userDao.addUserUseSimpleInsert(new User(11,"lily",25));}@Testpublic void testAddUserUseSimpleInsertWithGeneratedKey(){Student s = new Student();s.setName("obam");s.setAge(56);userDao.addUserUseSimpleInsertWithGeneratedKey(s);}@Testpublic void testAddUseBeanPro(){Student s = new Student(2,"jiki",99);userDao.addUseBeanPro(s);}@Testpublic void testAddUserUseMapParam(){Student s = new Student(3,"jiky",100);userDao.addUseBeanPro(s);}@Testpublic void testCallProc(){Student stu = userDao.callProc(6);Assert.assertEquals("jiki",stu.getName());}@Testpublic void testCallProcWithType(){Student stu = userDao.callProcWithType(6);Assert.assertEquals("jiki",stu.getName());}}

Beans.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:p="http://www.springframework.org/schema/p"xmlns:context="http://www.springframework.org/schema/context"xsi:schemaLocation="http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans.xsdhttp://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context.xsd"><context:component-scan base-package="com.dao.impl" /><bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"destroy-method="close"p:driverClassName="com.mysql.jdbc.Driver"p:url="jdbc:mysql://localhost:3306/spring"p:username="root"p:password="root"/><!-- <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"><property name="driverClassName" value="${jdbc.driverClassName}"/><property name="url" value="${jdbc.url}"/><property name="username" value="${jdbc.username}"/><property name="password" value="${jdbc.password}"/></bean><context:property-placeholder location="jdbc.properties"/> --><!-- <bean name="jdbcTemelate" class="org.springframework.jdbc.core.JdbcTemplate">          <property name="dataSource" ref="dataSource"></property>         </bean> -->         <!-- <bean id="userDao" class="com.dao.impl.UserDaoImpl">          <property name="jdbcTemplate" ref="jdbcTemelate"></property>         </bean>  --></beans>

myproc:

delimiter //CREATE PROCEDURE myproc(IN in_id INTEGER,OUT out_name VARCHAR(30),OUT out_age INTEGER )     BEGINSELECT name, ageINTO out_name, out_ageFROM student where id = in_id;     END //delimiter ;

建表语句:

| user  | CREATE TABLE `user` (  `id` int(11) default NULL,  `name` varchar(32) default NULL,  `age` int(11) default NULL) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |+-------+-----------------------------------

| student | CREATE TABLE `student` (  `id` int(11) NOT NULL auto_increment,  `name` varchar(23) default NULL,  `age` int(11) default NULL,  PRIMARY KEY  (`id`)) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |+---------+---------------------------------------------------------------------------------------

应该够用了.