commons-dbutils 工具使用

一、准备工作

1.1 环境准备

导入 commons-dbutils 工具包 ，本文采用：commons-dbutils-1.4.jar

1.2 数据库准备

本文采用 c3p0 技术创建 Connection 连接池

DataSourceUtils 代码实现：

package com.test.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DataSourceUtils {
	private static ComboPooledDataSource dataSource  = new ComboPooledDataSource();
	
	public static DataSource getDataSource() {
		return dataSource;
	}
	
	public static Connection getConnection() {
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			throw new RuntimeException("数据连接获取失败！");
		}
	}
	
	/**
	 * 释放资源
	 * @param conn
	 * @param st
	 * @param rs
	 */
	public static void colseResource(Connection conn,Statement st,ResultSet rs) {
		closeResultSet(rs);
		closeStatement(st);
		closeConnection(conn);
	}
	
	/**
	 * 释放连接 Connection
	 * @param conn
	 */
	public static void closeConnection(Connection conn) {
		if(conn !=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		//等待垃圾回收
		conn = null;
	}
	
	/**
	 * 释放语句执行者 Statement
	 * @param st
	 */
	public static void closeStatement(Statement st) {
		if(st !=null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		//等待垃圾回收
		st = null;
	}
	
	/**
	 * 释放结果集 ResultSet
	 * @param rs
	 */
	public static void closeResultSet(ResultSet rs) {
		if(rs !=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		//等待垃圾回收
		rs = null;
	}
}

二、DML 操作

增加、修改、删除操作

package com.test.dbtest;

import java.sql.SQLException;

import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;

import com.test.daomain.User;
import com.test.utils.DataSourceUtils;

public class DBUtilsDemo1 {
	@Test
	public void addTest() throws SQLException {
		User user = new User(14,"小鑫","123");
		new DBUtilsDemo1().add(user);
	}
	
	@Test
	public void deleteTest() throws SQLException {
		User user = new User(14,"小鑫","123");
		new DBUtilsDemo1().delete(user);
	}
	
	@Test
	public void updateTest() throws SQLException {
		User user = new User(3,"黑嘿嘿","32434");
		new DBUtilsDemo1().update(user);
	}
	
	/**
	 * 添加操作
	 * @throws SQLException 
	 */	
	public void add(User user) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		
		String sql = "insert into student (uid,username,password) values (?,?,?)";
		
		int i = qr.update(sql, user.getUid(),user.getUsername(),user.getPassword());
		
		if(i==1) {
			System.out.println("数据添加成功！");
		}else {
			System.out.println("数据添加失败！");
		}
		
	}
	
	/**
	 * 删除操作
	 * @throws SQLException 
	 */	
	public void delete(User user) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		
		String sql = "delete from student where uid = ?";
		
		int i = qr.update(sql, user.getUid());
		
		if(i==1) {
			System.out.println("数据删除成功！");
		}else {
			System.out.println("数据删除失败！");
		}
	}
	
	/**
	 * 修改操作
	 * @throws SQLException 
	 */	
	public void update(User user) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		
		String sql = "update student set username = ? where uid = ?";
		
		int i = qr.update(sql, user.getUsername(), user.getUid());
		
		if(i==1) {
			System.out.println("数据更新成功！");
		}else {
			System.out.println("数据更新失败！");
		}
	}
}

三、查询操作

QueryRunner 类常用的查询操作方法

query(Connection conn, String sql, Object param, ResultSetHandler<T> rsh)

query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params)

query() 方法需要自己传入一个 ResultSetHandler 接口的实现类结果集，如果语句执行成功之后就返回给用户最开始指定的结果集类型的结果

 

常见的 ResultSetHandler 结果集：

package com.test.dbtest;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import com.test.daomain.Student;
import com.test.utils.DataSourceUtils;

public class DBUtilsDemo2 {
	
	@Test
	public void arrayHandler() throws SQLException{
		QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
		
		String sql="select * from student";
		
		// 只查询一条记录
		Object[] query = qr.query(sql, new ArrayHandler());
		System.out.println(Arrays.toString(query));
	}
	
	@Test
	public void arrayListHandler() throws SQLException{
		QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
		
		String sql="select * from student";
		// 将查询到的结果封装到 List 集合中返回
		List<Object[]> list = qr.query(sql, new ArrayListHandler());
		for (Object[] obj : list) {
			System.out.println(Arrays.toString(obj));
		}
	}
	
	@Test
	public void beanHandler() throws SQLException{
		QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
		
		String sql="select * from student where uid = ?";
	
		// 将查询到的结果封装到一个javabean中并返回
		// 要求javabean 名称和 数据库表名称一致
		// 必须有对属性有setter 和 getter 方法
		// 必须有无参的构造方法
		Student bean = qr.query(sql, new BeanHandler<>(Student.class),new Integer(3));
		
		System.out.println(bean);
	}
	
	@Test
	public void beanListHandler() throws SQLException{
		QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
		
		String sql="select * from student";
		// 将查询到的数据封装到javabean中，并装在list 结合返回
		List<Student> students = qr.query(sql, new BeanListHandler<>(Student.class));
		for (Student stu : students) {
			System.out.println(stu);
		}
		
	}
	
	@Test
	public void mapHandler() throws SQLException{
		QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
		
		String sql="select * from student";
		
		Map<String, Object> map = qr.query(sql, new MapHandler());
		for (String stu : map.keySet()) {
			System.out.println(map.get(stu));
		}
		System.out.println(map);
	}
	
	@Test
	public void mapListHandler() throws SQLException{
		QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
		
		String sql="select * from student";
		
		List<Map<String, Object>> list = qr.query(sql, new MapListHandler());
		for (Map<String, Object> map : list) {
			System.out.println(map);
		}
	}
	
	@Test
	public void scalarHandler() throws SQLException{
		QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
		
		String sql="select count(*) from student where username like ?";
		// 将查询的列结果封装到 ScalarHandler结果集中，返回
		Object obj = qr.query(sql, new ScalarHandler(),"%小%");
		System.out.println(obj);
		System.out.println(obj.getClass().getName());	// obj 类型为 Long
	}
}