[大数据]JDBC完结

一:Druid数据库连接池的使用

package Dbutils;

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

import org.apache.commons.dbutils.QueryRunner;
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 Bean.Customer;
import JDBCutil4.JDBCconn3;
import JDbcutil1.JDBCconn1;
/**
 * 
 *commons-dbutils 是Apache组织提供的一个开源JDBC工具类库，封装了针对了对于数据库的增删改查操作
 *增删改查操作主要用QueryRunner的对象调用QueryRunner中定义的方法
 *QueryRunner queryRunner = new QueryRunner();
 *调用对象queryRunner里面QueryRunner定义的update()方法实现增删改
 *
 * close()/closeQuitly() 实现资源关闭
 * @Description
 * @author Cjh
 * @date 2021年10月9日下午8:33:51
 *
 */
public class QuetyRunnerTest {
@Test
public void testInsert()  {
	Connection conn=null;
	try {
		QueryRunner queryRunner = new QueryRunner();
		conn = JDBCconn3.getConn();
		//增
//		String sql="insert into customers(name,email,birth)values(?,?,?)";
//		int update = queryRunner.update(conn, sql, "无话说","wuhuashuo@126.com","1997-01-01");
		//改
//		String sql="update customers set name = ? where id =?";
//		int update = queryRunner.update(conn,sql,"蔡徐坤",26);
		//删
		String sql = "delete from customers where id = ?";
		int update = queryRunner.update(conn,sql,27);
		System.out.println(update);
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally {
		JDBCconn1.closeResource(conn, null, null);
	}
	
}
@Test 
/*
 * 查询测试：
 *  
 * BeanHander:是ResultSetHandler接口的实现类，用于封装表中的一条记录
 * 
 * */
public void testQuety() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id = ?";
	BeanHandler<Customer> beam=new BeanHandler<Customer>(Customer.class);
	Customer query = queryRunner.query(conn, sql, beam, 6);
	System.out.println(query);
}
/**
 * BeanListHandler:是ResultSetHandler接口的实现类，用于封装背中的多条记录构成的集合
 * @throws SQLException
 */
@Test 
public void testQuety1() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id <= ?";
//	BeanHandler<Customer> beam=new BeanHandler<Customer>(Customer.class);
	BeanListHandler<Customer> beam=new BeanListHandler<Customer>(Customer.class);
	List<Customer> query = queryRunner.query(conn, sql, beam, 10);
	query.forEach(System.out::println);
	
}
/**
 * MapHander:是ResultSetHandler接口的实现类，用于封装表中的一条记录以键值对的形式//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}
 * 将字段及其相应字段的值作为map中的key和value
 * @throws SQLException
 */
@Test 
public void testQuety2() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id <= ?";
    MapHandler handler = new MapHandler();
    Map<String, Object> query = queryRunner.query(conn, sql, handler, 6);
	System.out.println(query);
	//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}

}
/**
 * MapListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录 以键值对的形式//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}.....
 * 将字段及其相应字段的值作为map中的key和value,将这些map添加到List中
 * @throws SQLException
 */
@Test 
public void testQuety3() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id <= ?";
    MapListHandler handler = new MapListHandler();
    List<Map<String, Object>> query = queryRunner.query(conn, sql, handler, 6);
	query.forEach(System.out::println);
	//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}

}
/**
 * ScalarHandler：是ResultSetHandler接口的实现类,用于查询分组函数，即特殊需求
 * 包括查询数量，最大值，最小值平均数，和等
 * mysql中：sum(),avg(),Min(),max(),count(*),
 * @throws SQLException
 */
@Test 
public void testQuety4() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select count(*) from customers ";
	ScalarHandler handler = new ScalarHandler();
     Object query = queryRunner.query(conn, sql, handler);
	System.out.println(query);

}
//如果ResultSetHandler中的接口都不满足我们的需求
//我们可以自定义ResultSetHandler
@Test 
public void testQuety5() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id = ?";
	ResultSetOrther handler = new ResultSetOrther();
    Customer query = queryRunner.query(conn, sql, handler,6);
	System.out.println(query);

}
}

这里我们也面临着于上述的数据库连接池同样的问题：

处理：

private static DataSource data =null;
?? ? ?static{
? ? ? try {
?? ??? ? ?Properties pros = new Properties();
?? ??? ? ?InputStream is = ClassLoader.getSystemClassLoader().getSystemResourceAsStream("Druid.properties");
?? ??? ? ?pros.load(is);
?? ??? ? ?//createDataSource为一个静态方法?
?? ??? ? ?data = DruidDataSourceFactory.createDataSource(pros);
?? ?} catch (Exception e) {
?? ??? ?e.printStackTrace();
?? ?}

将这段代码写到类中而不是方法中，从而解决调用时创建多个数据库池子

用Druid数据库连接池提供链接：

package Druid;

import java.io.IOException;
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 org.junit.Test;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.mysql.jdbc.PreparedStatement;

public class DruidTest {
	  private static DataSource data =null;
	  static{
      try {
		  Properties pros = new Properties();
		  InputStream is = ClassLoader.getSystemClassLoader().getSystemResourceAsStream("Druid.properties");
		  pros.load(is);
		  //createDataSource为一个静态方法 
		  data = DruidDataSourceFactory.createDataSource(pros);
	} catch (Exception e) {
		e.printStackTrace();
	}
	  }
	  @Test
  public static void getConnection() throws Exception {	
	//方一：
//	  DruidDataSource source1 = new DruidDataSource();
//	  source1.setUrl(null);.....
	  //方式二:
//	  DruidDataSourceFactory source = new DruidDataSourceFactory();
//	  Properties pros = new Properties();
//	  InputStream is = ClassLoader.getSystemClassLoader().getSystemResourceAsStream("Druid.properties");
//	  pros.load(is);
//	  DataSource data = source.createDataSource(pros);
	  Connection conn = data.getConnection();
	  
	  System.out.println(conn);
  }
	  //用dbutils.jar中提供的工具类实现资源的关闭 closeQuietly()/close()
	  public static void closeResourse1(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 (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		  try {
			DbUtils.close(rs);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	  }
	  public static void closeResourse2(Connection conn,Statement ps,ResultSet rs) {
		  DbUtils.closeQuietly(conn);
		  DbUtils.closeQuietly(ps);
		  DbUtils.closeQuietly(rs);
	  }
}

?

二.Dbutils工具类的使用?:

package Dbutils;

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

import org.apache.commons.dbutils.QueryRunner;
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 Bean.Customer;
import JDBCutil4.JDBCconn3;
import JDbcutil1.JDBCconn1;
/**
 * 
 *commons-dbutils 是Apache组织提供的一个开源JDBC工具类库，封装了针对了对于数据库的增删改查操作
 *增删改查操作主要用QueryRunner的对象调用QueryRunner中定义的方法
 *QueryRunner queryRunner = new QueryRunner();
 *调用对象queryRunner里面QueryRunner定义的update()方法实现增删改
 *
 * close()/closeQuitly() 实现资源关闭
 * @Description
 * @author Cjh
 * @date 2021年10月9日下午8:33:51
 *
 */
public class QuetyRunnerTest {
@Test
public void testInsert()  {
	Connection conn=null;
	try {
		QueryRunner queryRunner = new QueryRunner();
		conn = JDBCconn3.getConn();
		//增
//		String sql="insert into customers(name,email,birth)values(?,?,?)";
//		int update = queryRunner.update(conn, sql, "无话说","wuhuashuo@126.com","1997-01-01");
		//改
//		String sql="update customers set name = ? where id =?";
//		int update = queryRunner.update(conn,sql,"蔡徐坤",26);
		//删
		String sql = "delete from customers where id = ?";
		int update = queryRunner.update(conn,sql,27);
		System.out.println(update);
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally {
		JDBCconn1.closeResource(conn, null, null);
	}
	
}
@Test 
/*
 * 查询测试：
 *  
 * BeanHander:是ResultSetHandler接口的实现类，用于封装表中的一条记录
 * 
 * */
public void testQuety() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id = ?";
	BeanHandler<Customer> beam=new BeanHandler<Customer>(Customer.class);
	Customer query = queryRunner.query(conn, sql, beam, 6);
	System.out.println(query);
}
/**
 * BeanListHandler:是ResultSetHandler接口的实现类，用于封装背中的多条记录构成的集合
 * @throws SQLException
 */
@Test 
public void testQuety1() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id <= ?";
//	BeanHandler<Customer> beam=new BeanHandler<Customer>(Customer.class);
	BeanListHandler<Customer> beam=new BeanListHandler<Customer>(Customer.class);
	List<Customer> query = queryRunner.query(conn, sql, beam, 10);
	query.forEach(System.out::println);
	
}
/**
 * MapHander:是ResultSetHandler接口的实现类，用于封装表中的一条记录以键值对的形式//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}
 * 将字段及其相应字段的值作为map中的key和value
 * @throws SQLException
 */
@Test 
public void testQuety2() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id <= ?";
    MapHandler handler = new MapHandler();
    Map<String, Object> query = queryRunner.query(conn, sql, handler, 6);
	System.out.println(query);
	//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}

}
/**
 * MapListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录 以键值对的形式//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}.....
 * 将字段及其相应字段的值作为map中的key和value,将这些map添加到List中
 * @throws SQLException
 */
@Test 
public void testQuety3() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id <= ?";
    MapListHandler handler = new MapListHandler();
    List<Map<String, Object>> query = queryRunner.query(conn, sql, handler, 6);
	query.forEach(System.out::println);
	//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}

}
/**
 * ScalarHandler：是ResultSetHandler接口的实现类,用于查询分组函数，即特殊需求
 * 包括查询数量，最大值，最小值平均数，和等
 * mysql中：sum(),avg(),Min(),max(),count(*),
 * @throws SQLException
 */
@Test 
public void testQuety4() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select count(*) from customers ";
	ScalarHandler handler = new ScalarHandler();
     Object query = queryRunner.query(conn, sql, handler);
	System.out.println(query);

}
//如果ResultSetHandler中的接口都不满足我们的需求
//我们可以自定义ResultSetHandler
@Test 
public void testQuety5() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id = ?";
	ResultSetOrther handler = new ResultSetOrther();
    Customer query = queryRunner.query(conn, sql, handler,6);
	System.out.println(query);

}
}

自定义ResultHandler类:以Customers类为例子

package Dbutils;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.dbutils.ResultSetHandler;

import Bean.Customer;

public class ResultSetOrther implements ResultSetHandler<Customer> {

?? ?@Override
?? ?public Customer handle(ResultSet rs) throws SQLException {
?? ??? ?if(rs.next()) {
?? ??? ??? ?int id = rs.getInt("id");
?? ??? ??? ?String name = rs.getString("name");
?? ??? ??? ?String email = rs.getString("email");
?? ??? ??? ?Date birth = rs.getDate("birth");
?? ??? ??? ?return new Customer(id, name, email, birth);
?? ??? ?}
?? ??? ?return null;
?? ?}

}
?

总结：

1.字符串拼串写法

?

2.处理Blob

?

?

3.Preparestatement于Statement

?

4.事务

5.具体做法?

?