Spring Jdbc常规操作

版本：Spring 2.5

数据库：Mysql

内容主要包括Spring JdbcTemplate SimpleJdbcTemplate的一些操作，不包括调用存储过程（mysql的存储过程不会写）

数据库名：Spring_Test，表名：tb_user

package com.spring.xkey.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

class User {
	private int userid;
	private String username;
	private String email;

	public void setUserid(int id) {
		userid = id;
	}

	public int getUserid() {
		return userid;
	}

	public void setUsername(String user) {
		username = user;
	}

	public String getUsername() {
		return username;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public String getEmail() {
		return email;
	}
}

public class Test {

	private static JdbcTemplate jdbcTemplate;

	public void setUp() {
		String url = "jdbc:mysql://localhost:3306/Spring_Test";
		String username = "root";
		String password = "123456";
		DriverManagerDataSource dataSource = new DriverManagerDataSource(url,
				username, password);
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

	public void testPreparedStatementCreator() {
		final String sql = "select count(*) from tb_user where username=? and email=?";
		Object count = jdbcTemplate.execute(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				PreparedStatement pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, "xkey");
				pstmt.setString(2, "*********@gmail.com");
				return pstmt;

			}
		}, new PreparedStatementCallback() {
			public Integer doInPreparedStatement(PreparedStatement pstmt)
					throws SQLException {
				pstmt.execute();
				ResultSet rs = pstmt.getResultSet();
				rs.next();
				int value = rs.getInt(1);
				return value;
			}
		});
		System.out.println(count.toString());
	}

	public void testPreparedStatementSetter() {
		String sql = "insert into tb_user(username,email) values(?,?)";
		jdbcTemplate.update(sql, new PreparedStatementSetter() {

			public void setValues(PreparedStatement pstmt) throws SQLException {
				// TODO Auto-generated method stub
				pstmt.setString(1, "color");
				pstmt.setString(2, "*********@qq.com");
			}

		});
	}

	public void insert() {
		String sql = "insert into tb_user(username,email) values('xukai','*********@163.com')";
		jdbcTemplate.update(sql);
	}

	public void update() {
		String sql = "update tb_user set email='my********@163.com' where userid=?";
		jdbcTemplate.update(sql, new Object[] { 2 });
	}

	public void test() {
		String sql = "select * from tb_user";
		final List<User> res = new ArrayList<User>();
		jdbcTemplate.query(sql, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
//				int id = rs.getInt("userid");
//				String value = rs.getString("email");
//				System.out.println("Column TABLENAME:" + id + "," + value);
				User user = new User();
				user.setUserid(rs.getInt("userid"));
				user.setUsername(rs.getString("username"));
				user.setEmail(rs.getString("email"));
				res.add(user);
			}
		});
		
		for(Iterator it = res.iterator(); it.hasNext();){
			User user = (User)it.next();
			System.out.println(user.getUsername());
		}
		
		List ans = (List)jdbcTemplate.query(sql, new ResultSetExtractor(){
			
			public List extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<User> users = new ArrayList<User>();
				while(rs.next()){
					User user = new User();
					user.setUserid(rs.getInt("userid"));
					user.setUsername(rs.getString("username"));
					user.setEmail(rs.getString("email"));
					users.add(user);
				}
				return users;
			}
			
		});
		
		for(Iterator it = ans.iterator(); it.hasNext();){
			User user = (User)it.next();
			System.out.println(user.getUserid());
		}
		
		List result = jdbcTemplate.query(sql, new RowMapper() {

			public User mapRow(ResultSet rs, int arg1) throws SQLException {
				User user = new User();
				user.setUserid(rs.getInt("userid"));
				user.setUsername(rs.getString("username"));
				user.setEmail(rs.getString("email"));
				return user;
			}
		});
		Iterator it = result.iterator();
		while (it.hasNext()) {
			User user = (User) it.next();
			System.out.println(user.getEmail());
		}
		System.out.println(result.size());

	}
	
	public void testSimpleJdbcTemplate(){
		SimpleJdbcTemplate simpleJdbcTemplate = new SimpleJdbcTemplate(jdbcTemplate);
		String sql = "select * from tb_user where username = ?";
		List<Map<String, Object>> result = simpleJdbcTemplate.queryForList(sql, "color");
		for(Iterator it = result.iterator() ; it.hasNext() ;){
			Map user = (Map)it.next();
			Iterator itr = user.entrySet().iterator();
			while(itr.hasNext()){
				Map.Entry val = (Map.Entry)itr.next();
				System.out.println(val.getKey()+", "+val.getValue());
			}
			System.out.println(user.keySet().toString());
			System.out.println(user.toString());
		}
		System.out.println("===================================================");
		Map<String,Object> ans = simpleJdbcTemplate.queryForMap(sql, "xkey");
		Iterator iter = ans.entrySet().iterator();
		while(iter.hasNext()){
			Map.Entry val = (Map.Entry) iter.next();
			System.out.println(val.getKey()+", "+val.getValue());
		}
		String sql2 = "select count(*) from tb_user where username = ?";
		int count = simpleJdbcTemplate.queryForInt(sql2, "color");
		System.out.println(count);
		String sql3 = "select * from tb_user where userid=? and username=?";
		List<User>users = simpleJdbcTemplate.query(sql3, new ParameterizedRowMapper<User>(){


			public User mapRow(ResultSet rs, int arg1) throws SQLException {
				User user = new User();
				user.setUserid(rs.getInt("userid"));
				user.setUsername(rs.getString("username"));
				user.setEmail(rs.getString("email"));
				return user;
			}
		}, 1,"xkey");
		for(Iterator i = users.iterator() ;i.hasNext();){
			User user = (User)i.next();
			System.out.println(user.getUserid() + ","+user.getUsername()+","+user.getEmail());
		}
	}

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Test t = new Test();
		t.setUp();
		// t.insert();
		// t.test();
		// t.testPreparedStatementCreator();
		// t.testPreparedStatementSetter();
		// t.update();
		//t.test();
		t.testSimpleJdbcTemplate();
	}

}

上述testSimpleJdbcTemplate中的 ParameterizedRowMapper在Spring3.0以上版本已经不使用了，直接使用RowMapper就可以了。