Dbutils如何实现高效的数据库增删改查操作？

本文共计637个文字，预计阅读时间需要3分钟。

加载c3p0配置文件，将连接信息存放在ThreadLocal中（配置文件写法见上文）。

javapackage utils;

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

public class DataSourceUtil { private static DataSource dataSource;

static { try { Properties prop=new Properties(); prop.load(DataSourceUtil.class.getClassLoader().getResourceAsStream(c3p0-config.properties)); dataSource=new com.mchange.v2.c3p0.ComboPooledDataSource(); dataSource.setDriverClass(prop.getProperty(driver)); dataSource.setJdbcUrl(prop.getProperty(url)); dataSource.setUser(prop.getProperty(user)); dataSource.setPassword(prop.getProperty(password)); } catch (IOException | SQLException e) { e.printStackTrace(); } }

public static Connection getConnection() throws SQLException { return dataSource.getConnection(); }}

加载c3p0配置文件，链接放在ThreadLocal中（配置文件写法见上文）

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); } }