研究apache出品的DBUtilsResultSetHandler的子类 附带c3p0连接池

给大家推荐个靠谱的公众号程序员探索之路，大家一起加油 ​

apache出品的DBUtils  是数据库操作工具类

注意：没有带有注解的情况  需要自己补充

 

package com.qf.zzh;

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

import org..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.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.qf.Beans.Student;
import com.qf.Beans.User;
import com.qf.dbutils.c3p0Utils;
import com.sun.org.apache.bcel.internal.generic.NEW;

public class TestResultSetHandler {

	/**
	 * @param args
	 */

	// 1.ArrayHandler:适合去一条记录，例如：登录
	public static void test1() throws SQLException {
		// 加载数据源 也就是连接池 qr相当于是获取可以执行sql语句的对象
		QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource());
		String sql = "select id,username,password from user where id=? and username=?";
		// 执行完sql语句后 将结果解出来 参数是sql语句，然后返回值类型，后面是不定长参数取决于你的sql语句中有多少问号
		Object[] obj = qr.query(sql, new ArrayHandler(), 1, "zhangsan");
		for (int i = 0; i < obj.length; i++) {
			System.out.print(obj[i] + " ");
		}
		System.out.println();
	}

	// 2.ArrayListHandler :适合取多条记录，每一条记录都是Object[]，再把Object[]放入到list中 返回list
	public static void test2() throws SQLException {
		QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource());
		String sql = "select * from user";
		List<Object[]> list = qr.query(sql, new ArrayListHandler());
		for (int i = 0; i < list.size(); i++) {
			for (int j = 0; j < list.get(i).length; j++) {
				System.out.print(list.get(i)[j] + " ");
			}
			System.out.println();
		}
	}

	// 3.ColumnListHandler,取某一列的数据，保存到list中
	// 这个类的构造方法是列的序号new ColumnListHandler(colIndex),colIndex从1开始，如果没默认是1
	// 用这个东西的时候构造方法里面最好传一个数字n，代表了查询结果n这一列没有重复的值

	public static void test3() throws SQLException {
		QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource());
		String sql = "select tname from teacher";
		List<Object> list = qr.query(sql, new ColumnListHandler());
		for (int i = 0; i < list.size(); i++) {
			System.out.println(list.get(i));
		}
	}

	// 4.keyedHandler它取的是一个查询集合，返回一个map套map，每一条记录封装到 一个map中，每一个字段又在map里的map中
	// Map<object,map<string,object>>
	public static void test4() throws SQLException {
		QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource());
		String sql = "select * from teacher";
		Map<Object, Map<String, Object>> map = qr
				.query(sql, new KeyedHandler());
		for (Map.Entry<Object, Map<String, Object>> m : map.entrySet()) {
			System.out.println(m.getKey());
			for (Map.Entry<String, Object> mm : m.getValue().entrySet()) {
				System.out.print(mm.getKey() + "=" + mm.getValue());
			}
			System.out.println("-----------------");
		}
	}

	// 5.MapHandler用来查询一条记录，多用于登录功能
	public static void test5() throws SQLException {
		QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource());
		String sql = "select * from user where username=? and password=?";
		Map<String, Object> obj = qr.query(sql, new MapHandler(), "lisi",
				"654321");
		for (Map.Entry<String, Object> m : obj.entrySet()) {
			System.out.print(m.getKey() + "=" + m.getValue());
		}
		System.out.println();
	}

	// 6.MapListHandler 每一行都是一个map，把map放入list里表示多行*** 三颗星重点
	public static void test6() throws SQLException {
		QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource());
		String sql = "select * from teacher";
		List<Map<String, Object>> list = qr.query(sql, new MapListHandler());
		for (int i = 0; i < list.size(); i++) {
			for (Map.Entry<String, Object> m : list.get(i).entrySet()) {
				System.out.print(m.getKey() + m.getValue() + " ");
			}
			System.out.println();
		}
	}

	// 7.ScalarHandler取单行单列*** 加强记忆
	// 他的构造方法的参数默认是1，意思是取第一行的第一列，你可以改为其他列，但都是第一行的
	// *****当查询的内容如果是count，那么实际的返回值是long类型
	public static void test7() throws SQLException {
		QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource());
		String sql = "select * from student";
		Object obj = qr.query(sql, new ScalarHandler(4));
		System.out.println(obj);
	}

	// 8,BeanHandler*****
	public static void test8() throws SQLException {
		QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource());
		String sql = "select * from user where id=?";
		User user = qr.query(sql, new BeanHandler<User>(User.class), 1);
		System.out.println(user);
	}

	// 9.BeanListHandler*****
	public static void test9() throws SQLException {
		QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource());
		String sql = "select * from student";
		List<Student> list = qr.query(sql, new BeanListHandler<Student>(
				Student.class));
		for (int i = 0; i < list.size(); i++) {
			System.out.println(list.get(i));
		}
	}

	// insert,update,delete
	// 插入
	public static void testInsert() throws SQLException {
		QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource());
		String sql = "insert into user(id,username,password) values(?,?,?)";
		int r = qr.update(sql, 3, "wangwu", "222222");
		if (r > 0) {
			System.out.println("插入成功");
		}
	}

	// 修改
	public static void testUpdate() throws SQLException {
		QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource());
		String sql = "update user set username=?,password=? where id=?";
		int num = qr.update(sql, "lili", "321654", 3);
		if (num > 0) {
			System.out.println("修改成功");
		}
	}

	// 删除
	public static void testDelete() throws SQLException {
		QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource());
		String sql = "delete from user where username=? and password=?";
		int num = qr.update(sql, "lili", "321654");
		if (num > 0) {
			System.out.println("删除成功");
		}
	}

	// 研究ResultSetHandler的子类
	public static void main(String[] args) throws SQLException {
		// TODO Auto-generated method stub
		// test1();
		// test2();
		// test3();
		// test4();
		// test5();
		// test6();
		// test7();
		// test8();
		// test9();
		// testInsert();
		// testUpdate();
		// testDelete();
	}
}

 

package com.qf.dbutils;

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 c3p0Utils {
	private static DataSource dataSource = new ComboPooledDataSource();
	public static DataSource getDataSource(){
		return dataSource;
	}
	// 获取连接的
	// 回收
	public static Connection getConnection() {
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			// e.printStackTrace();
			throw new RuntimeException("服务器忙。。。");
		}
	}

	/**
	 * 表面上是回收方法 实际上执行的是close方法
	 * 
	 * @param rs
	 * @param ps
	 * @param conn
	 */
	public static void release(ResultSet rs, Statement ps, Connection conn) {
		if (rs != null) {
			try {
				//放心关   不会真的关闭   c3p0已经实现过了  不会真的关闭  而是回收资源
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		release(ps, conn);
	}

	public static void release(Statement ps, Connection conn) {
		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				// 放心关闭，因为不会真的关闭的，而是把close装饰过了，于是close变成了归还。
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}