Apache-DBUtils的使用

package NinaPassage;
/**
 * commons-dbutils是Apach组织提供的一个开源的JDBC工具类库，封装了对数据库的增删改查操作
 *
 *
 */

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

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
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.mysql.jdbc.Statement;

import SevenPassage.Dao.DBUtil;
import SevenPassage.Dao.Dao1.Customers;

public class Query {
	//测试插入
	@Test
	public void test1() throws Exception {
		QueryRunner runner = new QueryRunner();
		Connection conn = DBUtil.getConnection();
		String sql="update customers set name=? where id=?";
		int update = runner.update(conn, sql, "sxc1",50);
		System.out.println(update);
		
	}
	//测试查询
	/**
	 * BeanHander:是ResultSetHandler接口的实现类，用于封装表中的一条记录
	 *
	 */
	@Test
	public void Query() throws Exception {
		QueryRunner runner = new QueryRunner();
		Connection conn = DBUtil.getConnection();
		String sql="select id,name,email,birth from customers where is=?";
		//返回一条
//		BeanHandler<Customers> handler = new BeanHandler<>(Customers.class);
		//返回多条记录
		BeanListHandler<Customers> handler = new BeanListHandler<>(Customers.class);
		
		List<Customers> query = runner.query(conn, sql, handler,23);
		//查询全部表单信息
//		List<Customers> query = runner.query(conn, sql, handler);
		query.forEach(System.out::println);
	}
	
	
	
	
	
	
	//MapHander:是ResultSetHander接口的实现类，用于封装表中的一条数据
	@Test
	public void MapHander_1() throws Exception {
		MapHandler mapHandler = new MapHandler();
		String sql="select id,name,email,birth from customers";
		Connection conn = DBUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		Map<String, Object> query = runner.query(conn, sql, mapHandler);
		System.out.println(query);
	}
	
	
	
	
	//MapHander:是ResultSetHander接口的实现类，用于封装表中的一条数据
	@Test
	public void MapListHander_2() throws Exception {
		MapListHandler mapHandler = new MapListHandler();
		String sql="select id,name,email,birth from customers order by id";
	
		Connection conn = DBUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		List<Map<String,Object>> query = runner.query(conn, sql, mapHandler);
		query.forEach(System.out::println);
	}
	
	
	
	
	
	//ScalarHander:是ResultSetHander接口的实现类，用于查寻特殊值
	@Test
	public void ScalarHandler_3() throws Exception {
		
		String sql="select count(*) from customers";
	
		Connection conn = DBUtil.getConnection();
		QueryRunner runner = new QueryRunner();
		ScalarHandler handler = new ScalarHandler();
		long query =(long) runner.query(conn, sql, handler);
		System.out.println(query);
	}
	
	
	//自定义ResultSetHandler
	
	@Test
	public void test3() throws Exception {
		QueryRunner runner = new QueryRunner();
		String sql = "select id,name,email,birth from customers where id = ?";
		ResultSetHandler<Customers> handler=new ResultSetHandler<Customers>() {

			@Override
			public Customers handle(ResultSet rs) throws SQLException {
				if(rs.next()) {
					int id = rs.getInt("id");
					String name = rs.getString("name");
					String email = rs.getString("email");
					Date date = rs.getDate("birth");
					return	new Customers(id,name,email,date);
					
				}
				return null;
			}
		}; 
		Connection conn = DBUtil.getConnection();
		Customers query = runner.query(conn,sql, handler, 1);
		System.out.println(query);
		
			
	}
	
	//使用dbutils.jar中提供的Dbutils工具类，实现资源的关闭
	public static void closeConnection(Connection conn,Statement ps,ResultSet rs) {
		try {
			DbUtils.close(conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			DbUtils.close(ps);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			DbUtils.close(rs);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	//使用dbutils.jar中提供的Dbutils工具类，实现资源的关闭
			public static void closeCon(Connection conn,Statement ps,ResultSet rs) {
				DbUtils.closeQuietly(conn);
				DbUtils.closeQuietly(ps);
				DbUtils.closeQuietly(rs);
				
			}
	 		

}

需要使用的jdbc操作类
mysql驱动链接：链接：链接：https://pan.baidu.com/s/1Itb0PGFRhNmiRsGh4-ZuJw
提取码：sxcz
jdbc操作类：apache提供的dbutils
链接：https://pan.baidu.com/s/1aeJHQU0yOQqzpJTbOk9y0w
提取码：sxcz
数据库链接池druid-1.1.10.jar
链接：https://pan.baidu.com/s/1GwV6CMmpKF1IDP9wuzvgUQ
提取码：sxcz