学步园

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;
	}*/
	@Autowired
	public 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;
	}

	@Override
	public List<User> queryUsers() {
		
		List<User> users = jdbcTemplate.query("select * from user", new RowMapper<User>(){
			@Override
			public 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;
	}

	@Override
	public List<User> queryUsersByFinalClass() {
		return jdbcTemplate.query("select * from user",new UserMapper());
	}
	private static final class UserMapper implements RowMapper<User>{

		@Override
		public 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;
		}
	}
	@Override
	public 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;
	}

	@Override
	public 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);
	}

	@Override
	public 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);
	}

	@Override
	public 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;
	}

	@Override
	public 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;
	}

	@Override
	public 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;
	}

	@Override
	public 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);
	}

	@Override
	public 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);
	}

	@Override
	public void addUseBeanPro(Student stu) {
		SqlParameterSource parameters = new BeanPropertySqlParameterSource(stu);
		Number newId = insertActor2.executeAndReturnKey(parameters);
		stu.setId(newId.intValue());
	}

	@Override
	public void addUserUseMapParam(Student stu) {
		SqlParameterSource parameters = new MapSqlParameterSource()
		.addValue("name", stu.getName())
		.addValue("age", stu.getAge());
		Number newId = insertActor.executeAndReturnKey(parameters);
	}

	@Override
	public 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;
	}

	@Override
	public 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 {
	@Autowired
	private UserDao userDao;
	//@Autowired
	private JdbcTemplate jdbcTemplate;
	@Test
	public void testCreateUser() {
		userDao.createUser();
	}
	@Test
	public 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);
	}
	@Test
	public void deleteUser(){
		jdbcTemplate.execute("delete from user");
	}
	
	@Test
	public void testQueryUser(){
		Assert.assertEquals("tom",userDao.queryUser(2, "tom").getName());
	}
	
	@Test
	public void testQueryUsers(){
		Assert.assertEquals(9,userDao.queryUsers().size());
	}
	@Test
	public void testQueryUsersByFinalClass(){
		Assert.assertEquals(9,userDao.queryUsersByFinalClass().size());
	}
	@Test
	public void testQueryForListUser(){
		User u = userDao.queryForListUser(2, "tom", 2);
		Assert.assertEquals(2,u.getAge().intValue());
	}
	
	@Test
	public void testQueryCountForUserUseNameParam(){
		Assert.assertEquals(1,userDao.queryCountForUserUseNameParam(2,"tom",2));
	}
	@Test
	public void testQueryCountUseBeanPro(){
		User u = new User();
		u.setId(2);
		u.setName("tom");
		u.setAge(2);
		Assert.assertEquals(1,userDao.queryCountUseBeanPro(u));
	}
	@Test
	public 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]);
		}
	}
	@Test
	public 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]);
		}
	}
	
	@Test
	public 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]);
		}
	}
	@Test
	public void testAddUserUseSimpleInsert(){
		userDao.addUserUseSimpleInsert(new User(11,"lily",25));
	}
	@Test
	public void testAddUserUseSimpleInsertWithGeneratedKey(){
		Student s = new Student();
		s.setName("obam");
		s.setAge(56);
		userDao.addUserUseSimpleInsertWithGeneratedKey(s);
	}
	
	@Test
	public void testAddUseBeanPro(){
		Student s = new Student(2,"jiki",99);
		userDao.addUseBeanPro(s);
	}
	@Test
	public void testAddUserUseMapParam(){
		Student s = new Student(3,"jiky",100);
		userDao.addUseBeanPro(s);
	}
	@Test
	public void testCallProc(){
		Student stu = userDao.callProc(6);
		Assert.assertEquals("jiki",stu.getName());
	}
	@Test
	public 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/beans

http://www.springframework.org/schema/beans/spring-beans.xsd


http://www.springframework.org/schema/context

		http://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 )
     BEGIN
		SELECT name, age
		INTO out_name, out_age
		FROM 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 |
+---------+--------------------------------------
-------------------------------------------------

应该够用了.