JAVA---dbutils的使用

package java5.dbutils;

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

import javax.management.Query;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
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.ScalarHandler;
import org.junit.Test;

import java2.bean.Customer;

public class QueryRunnerTest {
	
	//测试插入
	@Test
	public void testInsert()  {
		Connection conn=null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection3();
			String sql="insert into customers(name,email,birth)values(?,?,?)";
			int insertCount = runner.update(conn, sql,"白狼","wolf@white.com","2015-1-1");
			System.out.println("添加了"+insertCount+"条记录");
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCUtils.closeResource1(conn,null,null);
		}
		
	}
	
	//测试查询
	/*
	 * BeanHander:是ResultSetHandler接口的实现类，用于封装表中的一条记录
	 * 
	 */
	@Test
	public void testQuery1()  {
		Connection conn=null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection3();
			String sql="select id,name,email,birth from customers where id=?";
			BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
			Customer customer = runner.query(conn, sql, handler,22);
			System.out.println(customer);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCUtils.closeResource1(conn, null, null);
		}
		
	}
	
	/*
	 * BeanListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录构成的集合
	 * 
	 */
	@Test
	public void testQuery2()  {
		Connection conn=null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection3();
			String sql="select id,name,email,birth from customers where id < ?";
			BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
			List<Customer> query = runner.query(conn, sql,handler,22);
			query.forEach(System.out::println);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCUtils.closeResource1(conn, null, null);
		}
		
	}
	
	/*
	 * 	MapHander:是ResultSethandler接口的实现类，对应表中的一条记录
	 * 将字段及相应字段的值作为map中的key和value
	 */
	@Test
	public void testQuery3()  {
		Connection conn=null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection3();
			String sql="select id,name,email,birth from customers where id=?";
			MapHandler mapHandler = new MapHandler();
			Map<String, Object> query = runner.query(conn, sql,mapHandler,22);
			System.out.println(query);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCUtils.closeResource1(conn, null, null);
		}
		
	}
	
	
	/*
	 * 	ScalarHandler:用于查询特殊值
	 */
	@Test
	public void testQuery5()  {
		Connection conn=null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection3();
			String sql="select count(*) from customers";
			ScalarHandler scalarHandler = new ScalarHandler();
			Long count = (Long) runner.query(conn,sql,scalarHandler);
			System.out.println(count);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCUtils.closeResource1(conn, null, null);
		}
		 
	}
	
	
	/*
	 * 	自定义ResultSetHandler的实现类
	 */
	
	@Test
	public void testQuery7() throws Exception{
		Connection conn=null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection3();
			String sql="select id,name,email,birth from customers where id=?";
			
			ResultSetHandler<Customer> handler=new ResultSetHandler<Customer>() {
				
				@Override
				public Customer handle(ResultSet rs) throws SQLException {
					
					return null;
				}
				
			};
			
			Customer query = runner.query(conn, sql,handler,22);
			
			System.out.println(query);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCUtils.closeResource1(conn, null, null);
		}
		
		
		
	}

	
	
	
	
	
}

package java5.dbutils;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbutils.DbUtils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

public class JDBCUtils {

	/**
	 * 使用Druid数据库连接池技术
	 */
	private static DataSource source1;
	static{
		try {
			Properties pros = new Properties();
			
			InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
			
			pros.load(is);
			
			source1 = DruidDataSourceFactory.createDataSource(pros);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection3() throws SQLException{
		
		Connection conn = source1.getConnection();
		return conn;
	}
	
	
	/**
	 * 
	 * @Description 使用dbutils.jar中提供的DbUtils工具类，实现资源的关闭
	 * @author shkstart
	 * @date 下午4:53:09
	 * @param conn
	 * @param ps
	 * @param rs
	 */
	public static void closeResource1(Connection conn,Statement ps,ResultSet rs){

		DbUtils.closeQuietly(conn);
		DbUtils.closeQuietly(ps);
		DbUtils.closeQuietly(rs);
	}
}