【JavaSE】JDBC丶连接池丶DBUtils丶事务

1.JDBC

1.1 入门例子

	Connection conn=null;
	Statement stmt=null;
	ResultSet rs=null;
	try {
		//1.加载mysql驱动
		Class.forName("com.mysql.jdbc.Driver");
		//2.根据数据库名、用户、密码创建连接对象
		conn=DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "root");
		//3.获取操作sql语句的Statement对象
		stmt=conn.createStatement();
		//4.执行查询语句，返回结果集ResultSet
		rs=stmt.executeQuery("select * from student");
		//5.遍历结果集读取数据
		while(rs.next()){
			int id=rs.getInt("id");
			String name=rs.getString("name");
			int age=rs.getInt("age");
			System.out.println("id="+id+",name="+name+",age="+age);
		}
	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		//关闭各种资源
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if(stmt!=null){
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

1.2 DBUtil简单封装

package com.util;

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

public class DBUtil {
	private static Connection conn=null;
	
	static{
		try {
			/*从配置文件中读取jdbc信息*/
			Properties prop=new Properties();
			//jdbc.properties存放在src下
			prop.load(DBUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"));
			String driver=prop.getProperty("driver");
			String url=prop.getProperty("url");
			String user=prop.getProperty("user");
			String password=prop.getProperty("password");
			
			//加载驱动
			Class.forName(driver);
			//创建连接
			conn=DriverManager.getConnection(url,user,password);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	//获取数据库连接对象
	public static Connection getConnection(){
		return conn;
	}
	
	//关闭资源
	public static void close(Connection conn,Statement stmt,ResultSet rs){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if(stmt!=null){
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void close(Connection conn,Statement stmt){
		close(conn,stmt,null);
	}
}

1.3 PreparedStatement

PreparedStatement：对sql语句预编译，提高效率，而且可以防止sql注入

• 查询

	Connection conn=null;
	PreparedStatement pstmt=null;
	ResultSet rs=null;
			
	try {
		conn=DBUtil.getConnection();
		
		//使用?占位符代替变量
		pstmt=conn.prepareStatement("select * from student where name=?");
		//为占位符赋值，位置是从1算起
		pstmt.setString(1, "张三");
		
		rs= pstmt.executeQuery();
		if(rs.next()){
			String name=rs.getString("name");
			int age=rs.getInt("age");
			System.out.println("name="+name+",age="+age);
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}finally{
		DBUtil.close(conn, pstmt,rs);
	}

• 增删改

	Connection conn=null;
	PreparedStatement pstmt=null;	
	try {
		conn=DBUtil.getConnection();		
		pstmt=conn.prepareStatement("insert into student values (null,?,?)");		
		pstmt.setString(1, "田七");
		pstmt.setInt(2, 27);
		
		//执行增删改操作,返回受影响的行数
		int result=pstmt.executeUpdate();
		if(result>0){
			System.out.println("添加成功");
		}else{
			System.out.println("添加失败");
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}finally{
		DBUtil.close(conn, pstmt);
	}

2.连接池

2.1 C3P0连接池

• 导入c3p0-0.9.1.2.jar包

• 在src下创建c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<!-- 默认的连接池配置 -->
	<default-config>
		<!-- 数据库驱动 -->
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<!-- 数据库连接字符串 -->
		<property name="jdbcUrl">jdbc:mysql://localhost/test</property>
		<!-- 数据库用户名 -->
		<property name="user">root</property>
		<!-- 数据库密码 -->
		<property name="password">root</property>
		<!-- 连接池的初始化的连接数量 -->
		<property name="initialPoolSize">5</property>
		<!-- 连接池最大的连接数 -->
		<property name="maxPoolSize">20</property>
	</default-config>

	<!-- 带名字的连接池 -->
	<named-config name="mypool">
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost/test</property>
		<property name="user">root</property>
		<property name="password">root</property>
	</named-config>
</c3p0-config>

• 连接池使用

	//获取带名字的连接池
	//DataSource ds=new ComboPooledDataSource("mypool");
	
	//1.获取默认的连接池
	DataSource ds=new ComboPooledDataSource();
	Connection conn=null;
	PreparedStatement pstmt=null;
	ResultSet rs=null;
	try {
		//2.从连接池中获取连接
		conn=ds.getConnection();
		pstmt=conn.prepareStatement("select * from student where id=1");
		rs=pstmt.executeQuery();
		if(rs.next()) {
			System.out.println("name="+rs.getString("name")+",age="+rs.getInt("age"));
		}
	} catch (SQLException e) {
		
		e.printStackTrace();
	}finally {
		//3.释放资源，连接会放回到连接池中，并不会直接关闭
		//C3P0对Connection.close()进行了增强，这里因为多态实际使用的是增强类的close()
		DBUtil.close(conn, pstmt, rs);
	}

• C3P0简单封装

package com.util;

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

public class C3P0Util {
	private static DataSource ds=new ComboPooledDataSource();
	
	//获取数据源对象
	public static DataSource getDataSource() {
		return ds;
	}
	
	//从连接池中取出数据库连接
	public static Connection getConnection() {
		try {
			return ds.getConnection();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
}

2.2 DBCP连接池

• 导入commons-dbcp-1.4.jar和commons-pool-1.5.6.jar
• 在src下新建dbcp-config.properties

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost/test
username=root
password=root

#初始化连接
initialSize=10

#最大连接数量
maxActive=50

#最大空闲连接
maxIdle=20

#最小空闲连接
minIdle=5

#超时等待时间以毫秒为单位 6000毫秒/1000等于60秒
maxWait=60000

• dbcp简单工具类

package com.util;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

public class DBCPUtil {
	private static DataSource ds = null;

	static {
		try {
			Properties prop = new Properties();
			//加载dbcp配置项进集合中
			prop.load(DBCPUtil.class.getClassLoader().getResourceAsStream("dbcp-config.properties"));
			
			//根据配置项集合创建连接池
			ds = BasicDataSourceFactory.createDataSource(prop);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	// 获取数据源对象
	public static DataSource getDataSource() {
		return ds;
	}

	// 从连接池中取出数据库连接
	public static Connection getConnection() {
		try {
			return ds.getConnection();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

}

3. DBUtils

• 导入commons-dbutils-1.4.jar
• 例子

package com.test;

import java.sql.SQLException;
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.domain.Student;
import com.util.C3P0Util;

public class TestDBUtils {
	@Test
	public void testAdd() {
		//根据数据源创建QueryRunner对象,会自动管理连接池的连接获取与回收
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		//定义sql语句
		String sql="insert into student values(null,?,?)";
		//占位符填充的数据
		Object[] params= {"小二",22};
		try {
			//进行增删改，返回受影响的行数
			int rows=qr.update(sql, params);
			if(rows>0) {
				System.out.println("添加成功");
			}else {
				System.out.println("添加失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		//不用手动释放数据库连接
	}
	
	@Test
	public void testGetAll() {		
		try {
			QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());			
			//把所有记录集封装成对象集合
			List<Student> students= qr.query("select * from student", new BeanListHandler<>(Student.class));
			for (Student student : students) {
				System.out.println(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public void testGetOne() {		
		try {
			QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
			//把丢一行记录封装成单个对象
			Student student= qr.query("select * from student where id=?", new BeanHandler<>(Student.class),1);			
			System.out.println(student);			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public void testGetScalar() {		
		try {
			QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
			//返回记录的第一行第一列的单个数据
			//count(*) 返回的是long类型
			 long count= (long)qr.query("select count(*) from student where age>?", new ScalarHandler(),24);			
			System.out.println(count);			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}

4. 事务处理

事务操作需要在同一个数据库连接上

4.1 JDBC方式

	Connection conn=null;			
	try {
		conn=DriverManager.getConnection(url, user, password);
		//1.关闭事务的自动提交
		conn.setAutoCommit(false);
		
		// ... 执行相关的数据库操作
	
		//2.正常运行提交事务
		conn.commit();
		
	//这里的异常要用Exception，任何异常都需要回滚事务
	} catch (Exception e) {
		
		if(conn!=null) {
			try {
				//3.出现异常回滚事务
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
	}finally {
		//  ...释放相关资源
	}

4.2 DBUtils方式

	Connection conn=null;	
	try {
		
		conn=DataSourceUtils.getConnection();
		//1.关闭事务的自动提交
		conn.setAutoCommit(false);
		//2.空参的QueryRunner创建对象,不要传递数据源
		QueryRunner qr=new QueryRunner();
		//3.update方法需要传递Connection对象
		qr.update(conn,"update user set money=money-1000 where username='zhangsan'");
		qr.update(conn,"update user set money=money+1000 where username='lisi'");
		
		//4.事务提交
		conn.commit();
	} catch (Exception e) {
		if(conn!=null) {
			try {
				//5.事务回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		e.printStackTrace();
	}finally {
		DataSourceUtils.closeConnection(conn);
	}

4.3 自定义封装类DataSourceUtils方式

package com.utils;

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 DataSourceUtils {

	// C3P0数据源
	private static DataSource dataSource = new ComboPooledDataSource();

	// 本地线程的数据库连接,可以跨类跨方法访问同一个数据库连接(同一个线程内)
	// 以当前运行的线程作为Map的键，以Connection对象为值
	private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();

	// 获取连接池
	public static DataSource getDataSource() {
		return dataSource;
	}

	// 从本地线程中获取连接对象
	public static Connection getConnection() throws SQLException {
		// 从本地线程中获取连接对象,如果为null则从连接池中获取，并把连接对象放进本地线程中
		Connection conn = tl.get();
		if (conn == null) {
			conn = dataSource.getConnection();
			tl.set(conn);
		}
		return conn;
	}

	// 开启事务
	public static void startTransaction() throws SQLException {
		Connection conn = getConnection();
		if (conn != null) {
			conn.setAutoCommit(false);
		}
	}

	// 事务回滚
	public static void rollback() throws SQLException {
		Connection conn = getConnection();
		if (conn != null) {
			conn.rollback();
		}
	}

	// 事务回滚并把连接对象归还连接池中
	public static void rollbackAndClose() throws SQLException {
		Connection conn = getConnection();
		if (conn != null) {
			conn.rollback(); // 回滚事务
			closeConnection(conn); // 释放连接
			tl.remove(); // 从本地线程绑定中移除
		}
	}

	// 提交事务
	public static void commit() throws SQLException {
		Connection conn = getConnection();
		if (conn != null) {
			conn.commit(); // 事务提交
		}
	}

	// 提交事务并把连接对象归还连接池中
	public static void commitAndClose() throws SQLException {
		Connection conn = getConnection();
		if (conn != null) {
			conn.commit();// 事务提交
			closeConnection(conn); // 释放连接
			tl.remove(); // 从本地线程绑定中移除
		}
	}

	// 释放资源
	public static void closeConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn=null;
		}
	}

	public static void closeStatement(Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			stmt=null;
		}
	}

	public static void closeResultSet(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs=null;
		}
	}

	public static void close(Connection conn, Statement stmt) {
		closeStatement(stmt);
		closeConnection(conn);
	}

	public static void close(Connection conn, Statement stmt, ResultSet rs) {
		closeResultSet(rs);
		closeStatement(stmt);
		closeConnection(conn);
	}

}

	Connection conn=null;		
	try {
		
		//1.获取连接对象
		conn=DataSourceUtils.getConnection();
		//2.开启事务
		DataSourceUtils.startTransaction();
		
		QueryRunner qr=new QueryRunner();
		qr.update(conn,"update user set money=money-1000 where username='zhangsan'");
		qr.update(conn,"update user set money=money+1000 where username='lisi'");
		
		//3.提交事务并释放连接
		DataSourceUtils.commitAndClose();
	} catch (Exception e) {
		e.printStackTrace();
		try {
			//4.回滚事务并释放连接
			DataSourceUtils.rollbackAndClose();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}		
	}

5. 事务特性与隔离级别

5.1 事务特性ACID

• 原子性（Atomicity)：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
• 一致性（Consistency）：一个事务中，事务前后数据的完整性必须保持一致。
• 隔离性（Isolation）：多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。
• 持久性（Durability）：一个事务一旦被提交，数据将会持久化到数据库文件中

5.2 脏读、不可重复读、幻读

• 脏读：A事务读取B事务未提交的数据；B用户给A用户转账但未提交事务，此时A用户查看自己的钱已经到账(读取的是内存保存的数据，未被持久化)，然后B用户回滚事务，最终并没有转账,所以A用户看到的是脏数据。

• 不可重复读：事务A多次读取同样的数据，但返回的结果却不一样，因为查询期间事务B修改并提交了该数据的值

• 幻读：事务A多次读取同样的数据，但结果集数量发生了变化，因为查询期间事务B删除或新增了数据

• 区别：脏读是针对未提交数据，不可重复读和幻读针对已提交的数据;不可重复读针对update(数据本身的变化),幻读针对insert和delete(数据数量的变化)

5.3 隔离级别

• read uncommitted : 读取尚未提交的数据 (相当于不加锁)
• read committed：读取已经提交的数据(相当于给修改的数据加锁), oracle默认级别
• repeatable read：重读读取(相当于给读取的数据加锁),mysql默认级别
• serializable：串行化，安全性最高，但并发性能最低（相当于锁表）

▷ 各种隔离级别能解决的并发问题

隔离级别	脏读	不可重复读	幻读
未提交读(read uncommitted)	✖	✖	✖
已提交读(read committed)	✔	✖	✖
可重复读(repeatable read)	✔	✔	✖
串行化读(serializable)	✔	✔	✔