dbutils的增删改查

package utils;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

public class JdbcUtils {
	private static ThreadLocal
 
   tl = new ThreadLocal
  
   ();// 得到当前会话的线程，里面维护了一个map集合 private static Properties prop; private static DataSource ds; static { try { prop = new Properties(); prop.load(JdbcUtils.class.getClassLoader().getResourceAsStream( "dbcpconfig.properties")); BasicDataSourceFactory factory = new BasicDataSourceFactory(); ds = factory.createDataSource(prop); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); throw new ExceptionInInitializerError(); } } public static DataSource getDataSource() { return ds; } public static Connection getConnection() { try { Connection conn = tl.get();// 得到当前线程中保存的链接 if (conn == null) {// 如果链接为空，表示此时是第一次访问，从连接池中获得链接 conn = ds.getConnection(); tl.set(conn);// 在线程中存一份链接 } return conn; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); throw new RuntimeException(); } } public static void startTransaction() { try { Connection conn = getConnection();// 获得当前线程中保存的链接 conn.setAutoCommit(false); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); throw new RuntimeException(); } } public static void commitTransaction() { try { Connection conn = getConnection(); if (conn != null) { conn.commit(); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); throw new RuntimeException(); } } public static void closeConnection() { try { Connection conn = getConnection(); if (conn != null) { conn.close(); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); throw new RuntimeException(); }finally{ tl.remove();//解除当前线程中的链接 } } }
  
 

package cn.jxau.dbutils.Demo;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import cn.jxau.domain.User;
import utils.JdbcUtils;

public class Demo1 {
	QueryRunner qr1 = new QueryRunner(JdbcUtils.getDataSource());

	@Test
	public void insert() throws SQLException {
		String sql = "insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";
		Object[] params = { 1, "老孔", "123", "qq@qq.com", new Date() };
		qr1.update(sql, params);
	}

	@Test
	public void find() throws SQLException {
		String sql = "select * from users where id=?";
		User user = (User) qr1.query(sql, 1, new BeanHandler(User.class));
		System.out.println(user.getBirthday());
	}

	@Test
	public void batch() throws SQLException {
		String sql = "insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";
		Object[][] params = new Object[3][];
		for (int i = 0; i < params.length; i++) {
			params[i] = new Object[]{ i+2, "老孔"+(i+2), "123", (i+2)+"qq@qq.com", new Date() };
		}
		qr1.batch(sql, params);
	}
	@Test
	public void getAll() throws SQLException {
		String sql = "select * from users";
		 List list =  (List) qr1.query(sql, new BeanListHandler(User.class));
		 System.out.println(list);
	}
}