6.JDBC

JDBC操作

一、概述

在Java中，数据库存取技术可分为如下几类：

1). JDBC直接访问数据库

2). 第三方O/R工具，如Hibernate, Mybatis 等

JDBC是java访问数据库的基石，Mybatis, Hibernate等只是更好的封装了JDBC。

1、什么是JDBC

JDBC(Java Database Connectivity)是一个独立于特定数据库管理系统（DBMS）、通用的SQL数据库存取和操作的公共接口（一组API），定义了用来访问数据库的标准Java类库，使用这个类库可以以一种标准的方法、方便地访问数据库资源。

JDBC为访问不同的数据库提供了一种统一的途径，为开发者屏蔽了一些细节问题。

JDBC的目标是使Java程序员使用JDBC可以连接任何提供了JDBC驱动程序的数据库系统，这样就使得程序员无需对特定的数据库系统的特点有过多的了解，从而大大简化和加快了开发过程。

如果没有JDBC，那么Java程序访问数据库时是这样的：

2、JDBC API

JDBC API是一系列的接口，它统一和规范了应用程序与数据库的连接、执行SQL语句，并到得到返回结果等各类操作。声明在java.sql与javax.sql包中。

3、JDBC 可以做什么

我们可以做以下事情：增删改查 CRUD

① 查询某个女神的基本信息

② 查询女神们的详细信息

③ 查询心仪女神的电话号码

④ 添加心仪的女神

⑤ 删除不符合要求的女神

⑥ 修改女神的信息

4、JDBC程序编写步骤

1). 注册驱动

2). 获取连接

3). 执行增删改查

4). 释放资源

二、引入JDBC驱动类

1、引入相关jar包

驱动程序由数据库提供商提供下载。 MySQL的驱动下载地址：http://dev.mysql.com/downloads/

2、在Java Project项目中添加jar包

eclipse版本

（1）把
拷贝到项目中一个目录中

（2）添加到项目的类路径下

在驱动jar上右键–>Build Path–>Add to Build Path

注意：如果是Dynamic Web Project（动态的web项目）话，则是把驱动jar放到WebContent（有的开发工具叫WebRoot）目录中的WEB-INF目录中的lib目录下即可

Idea版本

（1）在项目中新建lib文件夹 ， 把
拷贝到项目中一个lib中

（2）添加到项目的类路径下

在Project Structure中，添加jar包至项目

三、JDBC操作步骤

1、加载并注册驱动

加载并注册驱动：

加载驱动，把驱动类加载到内存

注册驱动，把驱动类的对象交给DriverManager管理，用于后面创建连接等使用。

调用 Class 类的静态方法 forName()，向其传递要加载的 JDBC 驱动的全类名

//通过反射，加载与注册驱动类，解耦合（不直接依赖）

Class.forName("com.mysql.jdbc.Driver");

2、获取数据库连接对象

​ 可以通过 DriverManager 类建立到数据库的连接Connection：DriverManager 试图从已注册的 JDBC 驱动程序集中选择一个适当的驱动程序。

public static Connection getConnection(String url) 

public static Connection getConnection(String url,String user, String password)

//其中Properties info通常至少应该包括 "user" 和 "password" 属性
public static Connection getConnection(String url,Properties info)

JDBC URL 用于标识一个被注册的驱动程序，驱动程序管理器通过这个 URL选择正确的驱动程序，从而建立到数据库的连接。JDBC URL的标准由三部分组成，各部分间用冒号分隔。

jdbc:<子协议>:<子名称>

协议：JDBC URL中的协议总是jdbc 

子协议：子协议用于标识一个数据库驱动程序

子名称：一种标识数据库的方法。子名称可以依不同的子协议而变化，用子名称的目的是为了定位数据库提供足够的信息 

例如：

MySQL的连接URL编写方式:

jdbc:mysql://主机名称:mysql服务端口号/数据库名称?参数=值&参数=值

jdbc:mysql://localhost:3306/testdb

jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=utf8（如果JDBC程序与服务器端的字符集不一致，会导致乱码，那么可以通过参数指定服务器端的字符集）

jdbc:mysql://localhost:3306/testdb?user=root&password=123456

Oracle9i【了解】

jdbc:oracle:thin:@主机名称:oracle服务端口号:数据库名称

jdbc:oracle:thin:@localhost:1521:testdb

SQLServer【了解】

jdbc:sqlserver://主机名称:sqlserver服务端口号:DatabaseName=数据库名称

jdbc:sqlserver://localhost:1433:DatabaseName=testdb

mysql代码如下：

//1、加载与注册驱动		
Class.forName("com.mysql.jdbc.Driver");				
//2、获取数据库连接		
String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";	
Connection conn = DriverManager.getConnection(url, "root", "root");

3、操作或访问数据库

​ 数据库连接被用于向数据库服务器发送命令和SQL语句，并接受数据库服务器返回的结果。其实一个数据库连接就是一个Socket连接。

在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式：

Statement：// 用于执行静态 SQL 语句并返回它所生成结果的对象。 

PrepatedStatement：// SQL 语句被预编译并存储在此对象中，然后可以使用此对象多次高效地执行该语句。

CallableStatement：// 用于执行 SQL 存储过程

PreparedStatement

​ 通过调用Connection对象的preparedStatement(String sql) 方法创建该对象，该对象用于执行静态的SQL语句，并且返回执行结果。

PreparedStatement 接口中定义了下列方法用于执行 SQL 语句：

int excuteUpdate()：// 执行更新操作INSERT、UPDATE、DELETE

ResultSet excuteQuery()： //执行查询操作SELECT

ResultSet

通过调用 Statement 对象的 excuteQuery() 方法创建该对象

ResultSet 对象以逻辑表格的形式封装了执行数据库操作的结果集，ResultSet 接口由数据库厂商实现

ResultSet 对象维护了一个指向当前数据行的游标，初始的时候，游标在第一行之前，可以通过 ResultSet 对象的 next() 方法移动到下一行

ResultSet 接口的常用方法：

boolean next()

getXxx(String columnLabel)：columnLabel使用 SQL AS 子句指定的列标签。如果未指定 SQL AS 子句，则标签是列名称

getXxx(int index) :索引从1开始

4、释放资源

​ Connection、PreparedStatement、ResultSet都是应用程序和数据库服务器的连接资源，使用后一定要关闭，可以在finally中执行其close()进行关闭资源。

四、封装JDBCUtils

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

/**
* JDBC 获得连接和关闭资源
*/
public class JDBCUtils {

   /**
    * 创建连接对象，并返回
    * @return 
    */
   public static Connection getConn() {
   	Connection conn = null;
   	try {
   		//1.加载驱动类
   		Class.forName("com.mysql.jdbc.Driver");
   		//2.通过驱动类，创建连接对象
   		conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/employee?characterEncoding=utf8", "root", "12345678");
   	} catch (ClassNotFoundException e) {
   		e.printStackTrace();
   	} catch (SQLException e) {
   		e.printStackTrace();
   	}
   	return conn;
   }
   
   /**
    * 关闭资源
    * @param conn  连接对象
    * @param st    语句对象
    * @param rs    结果集对象
    */
   public static void close(Connection conn,Statement st,ResultSet rs) {
   	if(conn!=null) {
   		try {
   			conn.close();
   		} catch (SQLException e) {
   			e.printStackTrace();
   		}
   	}
   	if(st!=null) {
   		try {
   			st.close();
   		} catch (SQLException e) {
   			e.printStackTrace();
   		}
   	}
   	if(rs!=null) {
   		try {
   			rs.close();
   		} catch (SQLException e) {
   			e.printStackTrace();
   		}		
   	}
   }
   
   public static void main(String[] args) {
   	Connection conn = JDBCUtils.getConn();
   	System.out.println(conn);
   }

}

五、PreparedStatement

1、Statement的不足

（1）SQL拼接

String sql = "select * from s_user where name='"+name+"' and pwd='"+pwd+"'";

（2）SQL注入

SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的 SQL 语句段或命令，从而利用系统的 SQL 引擎完成恶意行为的做法。对于 Java 而言，要防范 SQL 注入，只要用 PreparedStatement 取代 Statement 就可以了。

（3）处理Blob类型的数据

BLOB (binary large object)，二进制大对象，BLOB常常是数据库中用来存储二进制文件的字段类型。

插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写的。

MySQL的四种BLOB类型(除了在存储的最大信息量上不同外，他们是等同的)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5faOSeHe-1637130345326)(image/wpso9DT6J.png)]

如果还是报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数： max_allowed_packet=16M 注意：修改了my.ini文件，一定要重新启动服务。

实际使用中根据需要存入的数据大小定义不同的BLOB类型。

需要注意的是：如果存储的文件过大，数据库的性能会下降。

2、PreparedStatement概述

可以通过调用 Connection 对象的 preparedStatement(String sql) 方法获取 PreparedStatement 对象

PreparedStatement 接口是 Statement 的子接口，它表示一条预编译过的 SQL 语句

PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示，调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数. setXxx() 方法有两个参数，第一个参数是要设置的 SQL 语句中的参数的索引(从 1 开始)，第二个是设置的 SQL 语句中的参数的值

ResultSet executeQuery()执行查询，并返回该查询生成的 ResultSet 对象。

int executeUpdate()：执行更新，包括增、删、改

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import com.igeek.jdbc.entity.Girl;
import com.igeek.jdbc.util.C3P0Connection;
import com.igeek.jdbc.util.JDBCUtils;

/**
 * PreparedStatement
 * @author Administrator
 *
 */
public class GirlDao {

	/**
	 * 插入用户信息
	 * @param Girl
	 */
	public void insertGirl(Girl girl) {
		//1.获得连接对象
		Connection conn = JDBCUtils.getConn();
		//2.获得语句对象
		String sql = "insert into girl(name,sex,borndate,phone,photo,boyfriend_id) values(?,?,?,?,null,?)";
		PreparedStatement ppst = null;
		try {
			ppst = conn.prepareStatement(sql);
			//3.给?传参
			ppst.setString(1, girl.getName());
			ppst.setString(2, girl.getSex());
			ppst.setTimestamp(3, girl.getBorndate());
			ppst.setString(4, girl.getPhone());
			ppst.setInt(5, girl.getBoyfriendId());
			//4.执行
			ppst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			//4.关闭资源
			JDBCUtils.close(conn, ppst, null);
		}
	}
	
	
	/**
	 * 通过名称模糊查询所有
	 * @param name
	 * @return
	 */
	public List<Girl> selectAll(String name){
		List<Girl> list = new ArrayList<Girl>();
//		Connection conn = JDBCUtils.getConn();
		Connection conn = C3P0Connection.conn;
		PreparedStatement ppst = null;
		ResultSet rs = null;
		String sql = "select * from girl where name like concat('%',?,'%')";
		try {
			ppst = conn.prepareStatement(sql);
			ppst.setString(1, name);
			rs = ppst.executeQuery();
			while(rs.next()) {
				Girl girl = new Girl(rs.getInt(1),rs.getString(2),rs.getString(3),
						rs.getTimestamp(4),rs.getString(5),rs.getString(6),rs.getInt(7));
				list.add(girl);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(conn, ppst, rs);
		}
		return list;
	}
	
	public static void main(String[] args) throws ParseException {
		
		GirlDao dao = new GirlDao();
		
//		String str = "1999-01-01";
//		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//		Date date = sdf.parse(str);
//		Timestamp borndate = new Timestamp(date.getTime());
//		Girl girl = new Girl("张三","男", borndate ,"11111111111",1);
//		dao.insertGirl(girl);
//		System.out.println("插入成功！");
		
		List<Girl> list = dao.selectAll("王");
		System.out.println(list.size());
		for (Girl girl : list) {
			System.out.println(girl);
		}
	}

}

3、PreparedStatement VS Statement

• 代码的可读性和可维护性. Statement的sql拼接是个难题。

• PreparedStatement 可以防止 SQL 注入

• PreparedStatement 可以处理Blob类型的数据

• PreparedStatement 能最大可能提高性能：（Oracle和PostgreSQL8是这样，但是对于MySQL不一定比Statement高）

DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。

4、JDBC 取得数据库自动生成的主键

获取自增长的键值：

（1）在创建PreparedStatement对象时

原来：

PreparedStatement pst = conn.preparedStatement(sql);

现在：

PreparedStatement pst = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

（2）原来执行更新

原来：

int len = pst.executeUpdate();	

现在：

int len = pst.executeUpdate();	

ResultSet rs = pst.getGeneratedKeys();

if(rs.next()){
	 Object key = rs.getObject(第几列);//获取自增长的键值
}	

案例：

	/**
	 * 插入用户信息
	 * @param Girl
	 */
	public void insertGirl(Girl girl) {
		//1.获得连接对象
		Connection conn = JDBCUtils.getConn();
		
    //2.获得语句对象
		String sql = "insert into girl(name,sex,borndate,phone,photo,boyfriend_id) values(?,?,?,?,null,?)";
		
    	PreparedStatement ppst = null;
		ResultSet rs = null;
		
    	try {
       // 3、准备一个PreparedStatement：预编译sql
    	//执行添加语句，如果需要获取自增长的键值，那么在此处要告知mysql服务器，在创建PreparedStatement对象时，增加一个参数		
    	//autoGeneratedKeys - 指示是否应该返回自动生成的键的标志，它是 Statement.RETURN_GENERATED_KEYS 或 Statement.NO_GENERATED_KEYS 之一		
			ppst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);		
			 
			//4.给?传参
			ppst.setString(1, girl.getName());
			ppst.setString(2, girl.getSex());
			ppst.setTimestamp(3, girl.getBorndate());
			ppst.setString(4, girl.getPhone());
			ppst.setInt(5, girl.getBoyfriendId());
			
			//5.执行插入
			ppst.executeUpdate();
			
			//执行查询，获得刚刚插入对象的ID（ID自增长）
			rs = ppst.getGeneratedKeys();		
		    if(rs.next()){			
		      System.out.println("新女神编号是：" + rs.getObject(1));		
		    } 	
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			//6.关闭资源
			JDBCUtils.close(conn, ppst, null);
		}
	}

5、事务

JDBC程序中当一个连接对象被创建时，默认情况下是自动提交事务：每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。

JDBC程序中为了让多个 SQL 语句作为一个事务执行：（重点）

调用 Connection 对象的 setAutoCommit(false); 以取消自动提交事务

在所有的 SQL 语句都成功执行后，调用 commit(); 方法提交事务

在其中某个操作失败或出现异常时，调用 rollback(); 方法回滚事务

若此时 Connection 没有被关闭, 则需要恢复其自动提交状态 setAutoCommit(true);

注意：

​ 如果多个操作，每个操作使用的是自己单独的连接，则无法保证事务。即同一个事务的多个操作必须在同一个连接下。

代码：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestTransaction {
	public static void main(String[] args){
		Connection conn = null;
		try {
			//1、连接数据库
			Class.forName("com.mysql.jdbc.Driver");

			String url = "jdbc:mysql://localhost:3306/test";
			String user = "root";
			String password = "123456";
			conn = DriverManager.getConnection(url, user, password);
			
            //设置手动提交
			conn.setAutoCommit(false);

			String sql1 = "update t_department set description = ? where did = ?";
			PreparedStatement pst1 = conn.prepareStatement(sql1);
			pst1.setObject(1, "挣大钱的");
			pst1.setObject(2, 4);
			int len1 = pst1.executeUpdate();
			System.out.println(len1>0?"更新部门信息成功":"更新部门信息失败");
			pst1.close();
            
            String sql2 = "update t_employee set salary = salary + ? where did = ?";
			PreparedStatement pst2 = conn.prepareStatement(sql2);
			pst2.setObject(1, 20000);
			pst2.setObject(2, 4);
			int len2 = pst2.executeUpdate();
			System.out.println(len2>0?"更新部门信息成功":"更新部门信息失败");
			pst2.close();
			
			conn.commit();
		}catch (Exception e) {
			try {
				if(conn!=null){
					conn.rollback();
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally{
			try {
				if(conn!=null){
					//恢复自动提交
					conn.setAutoCommit(true);
					//释放连接
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

6、批处理

当需要成批插入或者更新记录时。可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率。

JDBC的批量处理语句包括下面两个方法：

addBatch()：添加需要批量处理的SQL语句或参数

executeBatch()：执行批量处理语句

clearBatch():清空批处理包的语句

通常我们会遇到两种批量执行SQL语句的情况：

多条SQL语句的批量处理；

一个SQL语句的批量传参；

注意：

JDBC连接MySQL时，如果要使用批处理功能，请再url中加参数?rewriteBatchedStatements=true

PreparedStatement作批处理插入时使用values（使用value没有效果）

代码：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

import org.junit.Test;

public class TestBatch {
	@Test
	public void noBatch()throws Exception{
		Class.forName("com.mysql.jdbc.Driver");
		
		String url = "jdbc:mysql://localhost:3306/test";
		String user = "root";
		String password = "123456";
		Connection conn = DriverManager.getConnection(url, user, password);
		
		
        String sql = "INSERT INTO t_department(dname,description) VALUES(?,?)";
        PreparedStatement st = conn.prepareStatement(sql);
        
        for(int i=0; i<1000; i++){
        	st.setString(1, "测试部门" + i);
        	st.setString(2, "测试部门描述"  + i);
        	
        	st.executeUpdate();
        }
		
		st.close();
		conn.close();
	}
	
	@Test
	public void useBatch()throws Exception{
		Class.forName("com.mysql.jdbc.Driver");
		
		String url = "jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true";
		String user = "root";
		String password = "123456";
		Connection conn = DriverManager.getConnection(url, user, password);
		
		
        String sql = "INSERT INTO t_department(dname,description) VALUES(?,?)";
        PreparedStatement st = conn.prepareStatement(sql);
        
        for(int i=0; i<1000; i++){
        	st.setString(1, "测试部门" + i);
        	st.setString(2, "测试部门描述"  + i);
        	
        	st.addBatch();
        }
        
        st.executeBatch();
		
		st.close();
		conn.close();
	}
}

7、Blob类型数据的读写（了解）

MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。

MySQL的四种BLOB类型(除了在存储的最大信息量上不同外，他们是等同的)

实际使用中根据需要存入的数据大小定义不同的BLOB类型。
需要注意的是：如果存储的文件过大，数据库的性能会下降

六、数据库连接池

1、数据库连接池

（1）数据库连接池的必要性

不使用数据库连接池存在的问题:

​ 普通的JDBC数据库连接使用 DriverManager 来获取，每次向数据库建立连接的时候都要将 Connection 加载到内存中，再验证IP地址，用户名和密码(得花费0.05s～1s的时间)。需要数据库连接的时候，就向数据库要求一个，执行完成后再断开连接。这样的方式将会消耗大量的资源和时间。数据库的连接资源并没有得到很好的重复利用.若同时有几百人甚至几千人在线，频繁的进行数据库连接操作将占用很多的系统资源，严重的甚至会造成服务器的崩溃。

​ 对于每一次数据库连接，使用完后都得断开。否则，如果程序出现异常而未能关闭，将会导致数据库系统中的内存泄漏，最终将导致重启数据库。

​ 这种开发不能控制被创建的连接对象数，系统资源会被毫无顾及的分配出去，如连接过多，也可能导致内存泄漏，服务器崩溃。

为解决传统开发中的数据库连接问题，可以采用数据库连接池技术（connection pool）。

​ 数据库连接池的基本思想就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。**数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。**连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。

数据库连接池技术的优点：

资源重用：

​ 由于数据库连接得以重用，避免了频繁创建、释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增加了系统运行环境的平稳性。

更快的系统反应速度：

​ 数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于连接池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而减少了系统的响应时间。

新的资源分配手段：

​ 对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置，实现某一应用最大可用数据库连接数的限制，避免某一应用独占所有的数据库资源。

统一的连接管理，避免数据库连接泄露：

​ 在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露。

（2）多种开源的数据库连接池

​ JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口，该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现：

DBCP 是Apache提供的数据库连接池，速度相对c3p0较快，但因自身存在BUG，Hibernate3已不再提供支持

C3P0 是一个开源组织提供的一个数据库连接池，速度相对较慢，稳定性还可以

Proxool 是sourceforge下的一个开源项目数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点

BoneCP 是一个开源组织提供的数据库连接池，速度快

Druid 是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池，但是速度不知道是否有BoneCP快

DataSource 通常被称为数据源，它包含连接池和连接池管理两个部分，习惯上也经常把 DataSource 称为连接池。

注意：

​ 数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。

​ 当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但conn.close()并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。

（3）Druid（德鲁伊）数据源

​ Druid是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、Proxool等DB池的优点，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池。

import java.sql.Connection;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

public class TestDruid {
	public static void main(String[] args) throws Exception {
		Properties pro = new Properties();
		pro.load(TestDruid.class.getClassLoader().getResourceAsStream("druid.properties"));
		DataSource ds = DruidDataSourceFactory.createDataSource(pro);
		Connection conn = ds.getConnection();
		System.out.println(conn);
	}
}

url=jdbc:mysql://localhost:3306/test
username=root
password=123456
driverClassName=com.mysql.jdbc.Driver
initialSize=10
maxActive=20
maxWait=1000
filters=wall

详细配置参数：

配置	缺省	说明
name		配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分开来。 如果没有配置，将会生成一个名字，格式是：”DataSource-” + System.identityHashCode(this)
jdbcUrl		连接数据库的url，不同数据库不一样。例如：mysql : jdbc:mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto
username		连接数据库的用户名
password		连接数据库的密码。如果你不希望密码直接写在配置文件中，可以使用ConfigFilter。详细看这里：https://github.com/alibaba/druid/wiki/%E4%BD%BF%E7%94%A8ConfigFilter
driverClassName		根据url自动识别 这一项可配可不配，如果不配置druid会根据url自动识别dbType，然后选择相应的driverClassName(建议配置下)
initialSize	0	初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
maxActive	8	最大连接池数量
maxIdle	8	已经不再使用，配置了也没效果
minIdle		最小连接池数量
maxWait		获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁。
poolPreparedStatements	false	是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
maxOpenPreparedStatements	-1	要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100
validationQuery		用来检测连接是否有效的sql，要求是一个查询语句。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会其作用。
testOnBorrow	true	申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
testOnReturn	false	归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
testWhileIdle	false	建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
timeBetweenEvictionRunsMillis		有两个含义： 1)Destroy线程会检测连接的间隔时间2)testWhileIdle的判断依据，详细看testWhileIdle属性的说明
numTestsPerEvictionRun		不再使用，一个DruidDataSource只支持一个EvictionRun
minEvictableIdleTimeMillis
connectionInitSqls		物理连接初始化的时候执行的sql
exceptionSorter		根据dbType自动识别 当数据库抛出一些不可恢复的异常时，抛弃连接
filters		属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有： 监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall
proxyFilters		类型是List，如果同时配置了filters和proxyFilters，是组合关系，并非替换关系

2、ThreadLocal

JDK 1.2的版本中就提供java.lang.ThreadLocal，ThreadLocal为解决多线程程序的并发问题提供了一种新的思路。使用这个工具类可以很简洁地编写出优美的多线程程序。

ThreadLocal用于保存某个线程共享变量，原因是在Java中，每一个线程中都有一个ThreadLocalMap<ThreadLocal, Object>，其key就是一个ThreadLocal，而Object即为该线程的共享变量。而这个map是通过ThreadLocal的set和get方法操作的。对于同一个static ThreadLocal，不同线程只能从中get，set，remove自己的变量，而不会影响其他线程的变量。

1、ThreadLocal.get: 获取ThreadLocal中当前线程共享变量的值。

2、ThreadLocal.set: 设置ThreadLocal中当前线程共享变量的值。

3、ThreadLocal.remove: 移除ThreadLocal中当前线程共享变量的值。

4、ThreadLocal.initialValue: ThreadLocal没有被当前线程赋值时或当前线程刚调用remove方法后调用get方法，返回此方法值。

3、案例（ c3p0）

以C3P0为例，配置文件 c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	
  <!-- 默认配置节点 -->
  <default-config>
      <property name="driverClass">oracle.jdbc.driver.OracleDriver</property>
      <property name="jdbcUrl">jdbc:oracle:thin:@127.0.0.1:1521:xe</property>
      <property name="user">cm</property>
      <property name="password">cm</property>
      <!--当池中的连接不够时，一次创建多少个连接-->
      <property name="acquireIncrement">1</property>
      <!--初始时池中的连接-->
      <property name="initialPoolSize">1</property>
      <!--最大有多少个-->
      <property name="maxPoolSize">1</property>
      <!--最小有多少个-->
      <property name="minPoolSize">1</property>
  </default-config>
 
  <!-- 根据名字返回对应配置 -->
  <named-config name="oracle"> 
     <property name="driverClass">oracle.jdbc.driver.OracleDriver</property>
     <property name="jdbcUrl">jdbc:oracle:thin:@127.0.0.1:1521:xe</property>
     <property name="user">cm</property>
     <property name="password">cm</property>
  </named-config>
  
  <!-- 根据名字返回对应配置 -->
  <named-config name="mysql"> 
     <property name="driverClass">com.mysql.jdbc.Driver</property>
     <property name="jdbcUrl">jdbc:mysql://localhost:3306/employee?useUnicode=true&amp;characterEncoding=utf-8</property>
     <property name="user">root</property>
     <property name="password">12345678</property>
  </named-config>
</c3p0-config>

数据库连接工具类

import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 通过数据库连接池 C3P0进行数据访问操作
 *
 * 核心类：ComboPooledDataSource
 */
public class JDBCByC3P0Utils {

    // 创建数据源
    private static DataSource dataSource = new ComboPooledDataSource("mysql");
    // 线程变量：多线程操作时，连接对象确保是同一个
    private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();

    //获取连接对象
    public static Connection getConn() {
        //通过线程变量获取连接
        Connection conn = tl.get();
        //若连接对象是null，或者被关闭了
        try {
            if(conn==null || conn.isClosed()){
                //从数据源中获取连接对象
                conn = dataSource.getConnection();
                //将连接对象返回至线程变量中
                tl.set(conn);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }


    //释放资源
    public static void releaseConnection(){
        // 获取当前线程中的连接
        Connection conn = tl.get();
        if (connection != null) {
			try {
				connection.close();
				// 将已经关闭的连接从当前线程中移除
				tl.remove();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
    }
}

七、Apache—DBUtils简介

commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。

1、DbUtils类

DbUtils ：提供如关闭连接、装载JDBC驱动程序等常规工作的工具类，里面的所有方法都是静态的。

主要方法如下：

public static void close(…) throws java.sql.SQLException：　DbUtils类提供了三个重载的关闭方法。这些方法检查所提供的参数是不是NULL，如果不是的话，它们就关闭Connection、Statement和ResultSet。

public static void closeQuietly(…): 这一类方法不仅能在Connection、Statement和ResultSet为NULL情况下避免关闭，还能隐藏一些在程序中抛出的SQLEeception。

public static void commitAndClose(Connection conn)throws SQLException 用来提交连接的事务，然后关闭连接

public static void commitAndCloseQuietly(Connection conn)： 用来提交连接的事务，然后关闭连接，并且在关闭连接时不抛出SQL异常。

public static void rollback(Connection conn)throws SQLException允许conn为null，因为方法内部做了判断

public static void rollbackAndClose(Connection conn)throws SQLException

rollbackAndCloseQuietly(Connection)

public static boolean loadDriver(java.lang.String driverClassName)：这一方装载并注册JDBC驱动程序，如果成功就返回true。使用该方法，你不需要捕捉这个异常ClassNotFoundException。

2、QueryRunner类

该类封装了SQL的执行，是线程安全的。

（1）可以实现增、删、改、查。

（2）考虑了事务处理需要共用Connection。

（3）该类最主要的就是简单化了SQL查询，它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作，能够大大减少编码量。

QueryRunner类提供了两个构造方法

QueryRunner()：默认的构造方法

QueryRunner(DataSource ds)：需要一个 javax.sql.DataSource 来作参数的构造方法。

QueryRunner类常用方法

提供的方法会自行处理 PreparedStatement 和 ResultSet 的创建和关闭

（1）更新 update (增、删、改)

/*
 * 执行更新（插入、更新或删除）操作
 * @param conn   连接对象
 * @param sql    sql语句
 * @param params 传递的多个参数
 */
public int update(Connection conn, String sql, Object... params) throws SQLException


/*
 * 执行更新操作，创建QueryRunner时，提供了数据源DataSource
 * @param sql    sql语句
 * @param params 传递的多个参数
 */
public int update(String sql, Object... params) throws SQLException

（2）查询 query

/*
 * 执行查询操作
 * @param conn   连接对象
 * @param sql    sql语句
 * @param rsh    ResultSetHandler结果集处理器
 * @param params 传递的多个参数
 */
public Object query(Connection conn, String sql, ResultSetHandler rsh,Object... params) throws SQLException


/*
 * 执行查询操作，创建QueryRunner时，提供了数据源DataSource
 * @param sql    sql语句
 * @param rsh    ResultSetHandler结果集处理器
 * @param params 传递的多个参数
 */
public Object query(String sql, ResultSetHandler rsh,Object... params) throws SQLException

（3）插入

/*
 * 只支持INSERT语句
 * @param rsh  The handler used to create the result object from the ResultSet of auto-generated keys.  
 *
 * @return An object generated by the handler.即自动生成的键值
 */
public <T> T insert(Connection conn,String sql,ResultSetHandler<T> rsh, Object... params) throws SQLException

（4）批处理

// INSERT, UPDATE, or DELETE语句
public int[] batch(Connection conn,String sql,Object params)throws SQLException
     
// 只支持INSERT语句     
public <T> T insertBatch(Connection conn,String sql,ResultSetHandler<T> rsh,Object params)throws SQLException

3、ResultSetHandler接口

该接口用于处理 java.sql.ResultSet，将数据按要求转换为另一种形式。

ResultSetHandler 接口提供了一个单独的方法：

//该方法的返回值将作QueryRunner类的query()方法的返回值，方法返回的是ResultSet结果集迭代遍历出的值
Object handle (java.sql.ResultSet  rs)

该接口有如下实现类可以使用：

BeanHandler:将结果集的第一行，封装成对象，并返回bean      
 				new BeanHandler<T>(Xxx.class)

BeanListHandler:将结果集中的所有行，封装成对象的集合，并返回        
 				new BeanListHandler<T>(Xxx.class)

ScalarHandler:将结果集中的第一行第一列，以Object形式返回   
 				new ScalarHandler()
    
MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List  
    			new List<Map<key,value>>()
    
ArrayHandler：把结果集中的第一行数据转成对象数组。
ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中。
ColumnListHandler：将结果集中某一列的数据存放到List中。
KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里，再把这些map再存到一个map里，其key为指定的key。
MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。

4、案例

import java.sql.Connection;
import java.util.List;
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.ScalarHandler;
import org.junit.Test;
import com.igeek.jdbc.entity.Boy;
import com.igeek.jdbc.util.JDBCUtils;

/**
 * 此类用于演示DBUtils的使用
 * 功能：封装了和数据库存取相关的一些方法
 * 通用的增删改查等等
 * 
 * QueryRunner类：
 * 		update(connection，sql，params):执行任何增删改语句
 *		update(sql，params):执行任何增删改语句，此前加载了数据源
 * 		query(connection,sql,ResultSetHandler,params)：执行任何查询语句
 *		query(sql,ResultSetHandler,params)：执行任何查询语句，此前加载了数据源
 * 
 * ResultSetHandler接口
 * 		BeanHandler:将结果集的第一行，封装成对象，并返回      
 				new BeanHandler<T>(Xxx.class)
 * 		BeanListHandler:将结果集中的所有行，封装成对象的集合，并返回        
 				new BeanListHandler<T>(Xxx.class)
 * 		ScalarHandler:将结果集中的第一行第一列，以Object形式返回   
 				new ScalarHandler()
 * 
 * 
 * 使用步骤：
 * 
 * 1、导入jar包commons-dbutils-1.3.jar
 * 2、使用
 *
 */
public class BoyDaoByDBUtils {
	@Test
	public void testUpadte() throws Exception{
		
		//1.获取连接
		Connection connection = JDBCUtils.getConn();
		
		//2.执行增删改
		QueryRunner qr = new QueryRunner();
		int update = qr.update(connection, "update boy set name=? where id=4", "慕容复");
		
		System.out.println(update>0?"success":"failure");
		
		//3.关闭连接
		JDBCUtils.close(connection , null, null );
	}
	
	@Test
	public void testQuerySingle() throws Exception{
		
		//1.获取连接
		Connection connection = JDBCUtils.getConn();
		
		//2.执行增删改
		QueryRunner qr = new QueryRunner();
		
		Boy boy = qr.query(connection, "select * from  boy where id=?", new BeanHandler<>(Boy.class),1);
		System.out.println(boy);
		
		
		//3.关闭连接
		JDBCUtils.close(connection , null, null );
	}

	@Test
	public void testQueryMulti() throws Exception{
		
		//1.获取连接
		Connection connection = JDBCUtils.getConn();
		
		//2.执行增删改
		QueryRunner qr = new QueryRunner();
		
		List<Boy> list = qr.query(connection, "select * from  boy where id>?", new BeanListHandler<>(Boy.class),1);
		
		for (Boy boy : list) {
			System.out.println(boy);
		}
		
		//3.关闭连接
		JDBCUtils.close(connection , null, null );
	}
	
	@Test
	public void testScalar() throws Exception{
		
		//1.获取连接
		Connection connection = JDBCUtils.getConn();
		
		//2.执行查询单个值
		QueryRunner qr = new QueryRunner();
		Object query = qr.query(connection, "select count(*) from boy", new ScalarHandler());
		System.out.println(query);
		
		//3.关闭
		JDBCUtils.close(connection , null, null );
		
	}

}

八、DAO和增删改查通用方法

DAO：Data Access Object访问数据信息的类和接口，包括了对数据的CRUD（Create、Retrival、Update、Delete），而不包含任何业务相关的信息

作用：为了实现功能的模块化，更有利于代码的维护和升级。

导入t_user表，实现以下案例

1、UserDAO接口

import com.igeek.user.entity.User;
import java.util.List;

public interface UserDao {

    //根据用户名和密码获取数据库中的记录
    //User：用户名和密码正确 null：用户名或密码不正确
    public User getUser(User user);

    //根据用户名获取数据库中的记录
    public List<User> getUserByName(String name);

    //将用户保存到数据库
    public void saveUser(User user);

}

2、BaseDao 基础CRUD实现类

import com.igeek.user.utils.C3P0Connection;
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.ScalarHandler;

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

// 定义一个用来被继承的对数据库进行基本操作的Dao
public class BaseDao<T> {

    private QueryRunner queryRunner = new QueryRunner();

    // 通用的增删改操作
    public int update(String sql , Object...params){
        int count = 0;
        try {
            count = queryRunner.update(C3P0Connection.getDataSource(),sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    // 查询一条记录
    public T getBean(Class<T> type , String sql , Object...params){
        T t = null;
        try {
            t = queryRunner.query(C3P0Connection.getDataSource(),sql, new BeanHandler<T>(type) ,  params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return t;
    }

    // 查询所有记录
    public List<T> getBeanList(Class<T> type , String sql , Object...params){
        List<T> list = null;
        try {
            list = queryRunner.query(C3P0Connection.getDataSource(),sql, new BeanListHandler<T>(type),  params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    // 获取单值的方法，专门用来执行像select count(*)这样的sql语句
    public Object getSingleValue(String sql , Object...params){
        Object value = null;
        try {
            value = queryRunner.query(C3P0Connection.getDataSource(),sql, new ScalarHandler(), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return value;
    }
}

3、UserDaoImpl实现类

import com.igeek.user.entity.User;
import java.util.List;

public class UserDaoImpl extends BaseDao<User> implements UserDao {
    @Override
    public User getUser(User user) {
        String sql = "select * from t_user where name = ? and pwd = ?";
        User bean = this.getBean(User.class,sql,user.getName(),user.getPwd());
        return bean;
    }

    @Override
    public List<User> getUserByName(String name) {
        String sql = "select * from t_user where name like concat('%',?,'%')";
        List<User> beanList = this.getBeanList(User.class, sql, name);
        return beanList;
    }

    @Override
    public void saveUser(User user) {
        String sql = "insert into t_user(name,pwd,age,sex) values(?,?,?,?)";
        int i = this.update(sql, user.getName(), user.getPwd(), user.getAge(), user.getSex());
        System.out.println(i>0?"插入成功":"插入失败");
    }
}

4、UserDaoImplTest 测试类

import com.igeek.user.dao.UserDaoImpl;
import com.igeek.user.entity.User;
import org.junit.Test;
import java.util.List;

public class UserDaoImplTest {

    private UserDaoImpl dao = new UserDaoImpl();

    @Test
    public void getUser() {
        User user = new User();
        user.setName("李四");
        user.setPwd("123456");
        User bean = dao.getUser(user);
        System.out.println(bean);
    }

    @Test
    public void getUserByName() {
        List<User> userList = dao.getUserByName("小");
        for (User user : userList) {
            System.out.println(user);
        }
    }

    @Test
    public void saveUser() {
        User user = new User("王朝","123",45,"男");
        dao.saveUser(user);
    }
}