JDBC学习日记(1)

JDBC概述

数据的持久化

• 持久化：把数据保存到可掉电式存储设备中以供之后使用，大多数情况下，特别是企业级应用，数据持久化意味着将内存中的数据保存到硬盘上加以固化，而持久化的实现过程大多通过各种关系数据库完成。

• 持久化的主要应用是将内存中的数据存储在关系型数据库中，当然也可以存储在磁盘文件、XML数据文件中。

Java中的数据存储技术

• 在Java中，数据库存储技术可以分为如下几类:
  • JDBC 直接访问数据库
  • JDO 技术
  • 第三方 O/R 工具，如Hibernate、Mybatis等
• JDBC 是基础，而 JDO、Hibernate、Mybatis只是更好的封装了JDBC。

JDBC 介绍

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

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

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

JDBC 体系结构

• JDBC接口包括两个层次
  • 面向应用的API：Java API，抽象接口，供应用程序开发人员使用(连接数据库、执行SQL语句、获得结果）。
  • 面向数据库的API：供开发商开发数据库驱动用。

JDBC 编写流程

获取数据库连接

Driver 接口

• java.sql.Driver接口是所有 JDBC 驱动程序需要实现的接口，这个接口是提供给数据库厂商使用的，不同数据库厂商提供不同的实现。

• 在程序中不需要直接去访问实现了Driver接口的类，而是由驱动程序管理器类(java.sql.DriverManager)去调用这些Driver实现。

获取数据库连接的五种方式

package com.an.connection;

import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import org.junit.Test;

import java.io.InputStream;
import java.sql.Connection;

public class ConnectionTest {
	
	// 方式1：
	@Test
	public void testConnection() throws SQLException {
		// 获取 Driver 的实现类对象
		Driver driver = new com.mysql.jdbc.Driver();
		// jdbc:mysql: 协议 
		// localhost: ip 地址
		// 3306: 默认的mysql端口号
		// test: test数据库
		String url = "jdbc:mysql://localhost:3306/test";
		
		// 将用户名和密码封装在Properties中
		Properties info = new Properties();
		info.setProperty("user","root");
		info.setProperty("password", "2232116722ca");
		Connection conn = driver.connect(url, info);
		System.out.println(conn);
	}
	
	// 方式2：对方式1的迭代: 在如下的程序中不出现第三方的api，使得程序具有更好的可移植性
	@Test
	public void testConnection2() throws Exception{
		// 1、获取Driver实例的实现类对象，使用反射
		Class clazz = Class.forName("com.mysql.jdbc.Driver");
		Driver driver = (Driver)clazz.newInstance();
		// 2、提供要连接的数据库
		String url = "jdbc:mysql://localhost:3306/test";
		// 3、提供要连接的用户名和密码
		Properties info = new Properties();
		info.setProperty("user","root");
		info.setProperty("password", "2232116722ca");
		// 4、获取连接
		Connection conn = driver.connect(url, info);
		System.out.println(conn);
	}
	// 方式3：使用 DriverManager 替换 Driver
	@Test
	public void testConnnection3() throws Exception {
		// 1、提供另外三个连接的基本信息
		String url = "jdbc:mysql://localhost:3306/test";
		String user = "root";
		String password = "2232116722ca";
		
		// 2、获取Driver实例的实现类对象，使用反射
		Class clazz = Class.forName("com.mysql.jdbc.Driver");
		Driver driver = (Driver)clazz.newInstance();
		
		
		
		// 注册驱动
		DriverManager.registerDriver(driver);
		
		// 获取连接
		Connection conn = DriverManager.getConnection(url,user,password);
		System.out.println(conn);
	}
	
	// 方式4：可以只是加载驱动，不用显式的注册驱动
	@Test
	public void testConnnection4() throws Exception {
		// 1、提供另外三个连接的基本信息
		String url = "jdbc:mysql://localhost:3306/test";
		String user = "root";
		String password = "2232116722ca";
		
		// 2、加载驱动、相较于方式3可以省略注册操作。
		Class.forName("com.mysql.jdbc.Driver");
		
		
//			// 注册驱动
//			DriverManager.registerDriver(driver);
		
		// 获取连接
		Connection conn = DriverManager.getConnection(url,user,password);
		System.out.println(conn);
	}
	
	// 方式5：将数据库连接需要的4个基本信息声明在文件中，通过读取配置文件，获取连接
	/**
	 * 好处
	 * 1、实现了数据和代码的分离，实现了解耦
	 * 2、如果需要修改配置文件信息，可以避免程序重新打包
	 * @throws Exception
	 */
	@Test
	public void testConnection5() throws Exception {
		// 1、读取配置文件下4个基本信息
		InputStream iStream = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
		Properties properties = new Properties();
		properties.load(iStream);
		
		String user = properties.getProperty("user");
		String password = properties.getProperty("password");
		String url = properties.getProperty("url");
		String driverClass = properties.getProperty("driverClass");
		
		// 2、加载驱动
		Class.forName(driverClass);
		
		// 3、获取连接
		Connection conn = DriverManager.getConnection(url,user,password);
		System.out.println(conn);
	}
		
}

操作和访问数据库

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

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

  • Statement: 用于执行静态 SQL 语句并返回它所生成结果的对象。
  • PreparedStatement: SQL 语句被预编译并存储在此对象中，可以使用该对象多次高效执行该语句。
  • CallableStatement: 用于执行 SQL 存储过程。

使用 Statement 操作和访问数据库

存在以下问题

• 问题一：存在拼串操作，繁琐

• 问题二：存在SQL注入问题

SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的 SQL 语句段或命令(如：SELECT user,password FROM user_table WHERE user='a' OR 1 = 'AND PASSWORD='OR'1'='1')，从而利用系统的 SQL 引擎完成恶意行为的做法。

使用 PreparedStatement 操作和访问数据库(增删改)

package com.an.preparedstatement.crud;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.sql.Date;
import java.util.Properties;

import org.junit.Test;

import com.an.connection.ConnectionTest;
import com.an.util.JDBCUtils;
import com.mysql.jdbc.UpdatableResultSet;

/**
 * 使用 PreparedStatement 来替换 statement 实现对数据表的增删改查
 * 
 * 增删改；查
 * 
 * @author an
 *
 */

public class PreparedStatementUpdateTest {

	// 向customers数据表添加一条数据
	@Test
	public void testInsert() {
		// 1、读取配置文件下4个基本信息
		// 3、获取连接
		Connection conn = null;
		PreparedStatement prepareStatement = null;
		try {
			InputStream iStream = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
			Properties properties = new Properties();
			properties.load(iStream);

			String user = properties.getProperty("user");
			String password = properties.getProperty("password");
			String url = properties.getProperty("url");
			String driverClass = properties.getProperty("driverClass");

			// 2、加载驱动
			Class.forName(driverClass);

			conn = DriverManager.getConnection(url, user, password);
			System.out.println(conn);

			// 4、预编译 sql 语句，返回 PreparedStatement 的实例
			String sql = "insert into customers (name,email,birth) values(?,?,?)"; // ? 占位符
			prepareStatement = conn.prepareStatement(sql);

			// 5、填充占位符
			prepareStatement.setString(1, "哪吒");
			prepareStatement.setString(2, "nezha@qq.com");
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
			java.util.Date date = simpleDateFormat.parse("1999-01-01");
			prepareStatement.setDate(3, new Date(date.getTime()));

			// 6、执行操作
			prepareStatement.execute();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 7、资源的关闭
			try {
				if (prepareStatement != null)
					prepareStatement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	// 修改 customers 表中的一条记录
	@Test
	public void testUpdate() throws Exception {
		// 1、获取数据库连接
		Connection connection = JDBCUtils.getConnection();
		// 2、预编译 sql 语句，返回 PreparedStatement实例
		String sql = "update customers set name=? where id=?";
		PreparedStatement ps = connection.prepareStatement(sql);
		// 3、填充占位符
		ps.setObject(1, "莫扎特");
		ps.setObject(2, 18);
		// 4、执行
		ps.execute();
		// 5、资源的关闭
		JDBCUtils.closeResource(connection, ps);
	}

	// 通用的增删改操作
	// sql 中占位符的个数应该与可变形参的长度相同
	public void update(String sql, Object... args) throws Exception {
		Connection connection = null;
		PreparedStatement ps = null;
		try {
			// 1、获取数据库的连接
			connection = JDBCUtils.getConnection();
			// 2、预编译sql语句，返回PreparedStatement的实例
			ps = connection.prepareStatement(sql);
			// 3、填充占位符
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			ps.execute();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(connection, ps);
		}

	}
	
	@Test
	public void testCommonUpdate() throws Exception {
		String sql = "delete from customers where id = ?";
		update(sql, 3);
		
	}
}

工具类

package com.an.util;

import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

import com.an.connection.ConnectionTest;
import java.sql.Connection;

/**
 * 操作数据库的工具类
 * @author an
 *
 */
public class JDBCUtils {
	/**
	 * 获取数据库连接
	 * @return
	 * @throws Exception
	 */
	public static Connection getConnection() throws Exception {
		// 1、读取配置文件下4个基本信息
		InputStream iStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
		Properties properties = new Properties();
		properties.load(iStream);
		
		String user = properties.getProperty("user");
		String password = properties.getProperty("password");
		String url = properties.getProperty("url");
		String driverClass = properties.getProperty("driverClass");
		
		// 2、加载驱动
		Class.forName(driverClass);
		
		// 3、获取连接
		Connection conn = DriverManager.getConnection(url,user,password);
		System.out.println(conn);
		return conn;
	}
	/**
	 * 关闭连接和statement 操作
	 */
	public static void closeResource(Connection conn,PreparedStatement prepareStatement) {
		try {
			if(prepareStatement!=null)
				prepareStatement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if(conn!=null)
				conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
}

使用 PreparedStatement 操作和访问数据库(查询)

针对 Customers 表

package com.an.preparedstatement.crud;

import org.junit.Test;

import com.an.util.JDBCUtils;


import java.sql.ResultSetMetaData;

import javax.security.auth.login.FailedLoginException;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.PseudoColumnUsage;
import java.sql.ResultSet;
import java.sql.Date;


/**
 * 针对于Customer表的查询操作
 * @author an
 *
 */

public class CustomerForQuery {
	
	@Test
	public void testQuery1() throws Exception {
		Connection conn = JDBCUtils.getConnection();
		String sql = "select id,name,email,birth from customers where id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		
		ps.setObject(1, 1);
		// 执行，并返回 sql 语句
		
		ResultSet resultSet = ps.executeQuery();
		
		
		// 处理结果集
		while(resultSet.next()) { // 判断结果集下一条是否有数据，如果有数据返回 true。
			// 获取当前这条数据的各个字段
			int id = resultSet.getInt(1);
			String name = resultSet.getString(2);
			String email = resultSet.getString(3);
			Date birth = resultSet.getDate(4);
			
			// 方式1：
			// system.out....
			// 方式2：
//			Object[] date = new Object[] {id,name,email,birth};
			// 方式3：创建一个类，将数据封装成对象
			Customer customer = new Customer(id,name,email,birth);
			System.out.println(customer);
		}
		// 关闭资源
		JDBCUtils.closeResource(conn, ps, resultSet);
	}
	
	/**
	 * 针对 customers 表的通用查询操作
	 */
	public Customer queryForCustomers(String sql,Object...args) throws Exception {
		Connection connection = JDBCUtils.getConnection();
		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		for(int i = 0;i<args.length;i++) {
			prepareStatement.setObject(i+1, args[i]);
		}
		ResultSet rs = prepareStatement.executeQuery();
		// 获取结果集的元数据
		ResultSetMetaData rsmd = rs.getMetaData();
		// 通过ResultSetMetaData 获取结果集的列数
		int columnCount = rsmd.getColumnCount();
		if(rs.next()) {
			Customer customer = new Customer();
			// 处理一行数据中的每一个列
			for(int i=0;i<columnCount;i++) {
				// 获取列名
				Object value = rs.getObject(i+1);
				// 获取每个列的列名
				String columnName = rsmd.getColumnName(i+1);
				// 给 custom 对象指定的 columnName 属性，通过反射
				Field field = Customer.class.getDeclaredField(columnName);
				field.setAccessible(true);
				field.set(customer, value);
				
				// 给 cust 对象指定的某个属性，赋值为value
				
			}
			return customer;
		}
		JDBCUtils.closeResource(connection, prepareStatement, rs);
		return null;
	}
	
	@Test
	public void testQueryForCustomers() throws Exception {
		String sql = "select id,name,birth,email from customers where id = ?";
		Customer customer = queryForCustomers(sql, 13);
		System.out.println(customer);
	}
}
/**
 * ORM 编程思想(object relation mapping)
 * 一个数据表对应一个java类
 * 表中一个记录对应java类的一个对象
 * 
 * @author an
 *
 */

class Customer {
	private int id;
	private String name;
	private String email;
	private Date birth;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public Date getBirth() {
		return birth;
	}
	public void setBirth(Date birth) {
		this.birth = birth;
	}
	@Override
	public String toString() {
		return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";
	}
	
	public Customer() {
		super();
	}
	public Customer(int id, String name, String email, Date birth) {
		super();
		this.id = id;
		this.name = name;
		this.email = email;
		this.birth = birth;
	}
	
}

针对 Order 表

package com.an.preparedstatement.crud;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.junit.Test;

import com.an.util.JDBCUtils;


import java.sql.ResultSetMetaData;


/**
 * 针对 Order 表的通用查询操作
 * 针对表的字段名和类的属性名不相同的情况：
 * 1、必须声明sql时，使用类的属性名来命名字段的别名
 * 2、使用ResultSetMetaData时，需要使用getColumnLabel()来替换getColumnName()获取类的
 * 别名
 * 说明，如果sql中没有给字段起别名getColumnLabel获取的就是列名
 * 
 * @author an
 *
 */
public class OrderForQuery {
	@Test
	public void testQuery1() throws Exception {
		Connection connection = JDBCUtils.getConnection();
		String sql = "select order_id,order_name,order_date from `order` where order_id=?";
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.setObject(1, 1);
		ResultSet rs = ps.executeQuery();
		if(rs.next()) {
			int id = (int) rs.getObject(1);
			String name = (String) rs.getObject(2);
			Date date = (Date) rs.getObject(3);
			Order order = new Order(id,name,date);
			System.out.println(order);
		}
		JDBCUtils.closeResource(connection, ps, rs);
	}
	
	
	
	/**
	 * 通用的针对于 order 表的查询操作
	 */
	
	public Order orderForQuery(String sql,Object...args)throws Exception {
		Connection connection = JDBCUtils.getConnection();
		PreparedStatement ps = connection.prepareStatement(sql);
		for(int i=0;i<args.length;i++) {
			ps.setObject(i+1, args[i]);
		}
		ResultSet rs = ps.executeQuery();
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		if(rs.next()) {
			Order order = new Order();
			for(int i=0;i<columnCount;i++) {
				Object columnValue = rs.getObject(i+1);
				// 获取列的列名，而不是别名
//				String columnName = rsmd.getColumnName(i+1); - 不推荐使用
				String columnName = rsmd.getColumnLabel(i+1);
				// 通过反射，将对象指定名的属性赋值为指定值columnValue
				Field field = Order.class.getDeclaredField(columnName);
				field.setAccessible(true);
				field.set(order,columnValue);
			}
			return order;
		}
		JDBCUtils.closeResource(connection, ps, rs);
		return null;
	}
	
	@Test
	public void testOrderForQuery() throws Exception {
		String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id=?";
		Order order = orderForQuery(sql,1);
		System.out.println(order);
	}
}


class Order {
	private int orderId;
	private String orderName;
	private Date orderDate;
	public int getOrderId() {
		return orderId;
	}
	public void setOrderId(int orderId) {
		this.orderId = orderId;
	}
	public String getOrderName() {
		return orderName;
	}
	public void setOrderName(String orderName) {
		this.orderName = orderName;
	}
	public Date getOrderDate() {
		return orderDate;
	}
	public void setOrderDate(Date orderDate) {
		this.orderDate = orderDate;
	}
	public Order(int orderId, String orderName, Date orderDate) {
		super();
		this.orderId = orderId;
		this.orderName = orderName;
		this.orderDate = orderDate;
	}
	public Order() {
		super();
	}
	@Override
	public String toString() {
		return "Order [orderId=" + orderId + ", orderName=" + orderName + ", orderDate=" + orderDate + "]";
	}
	
	
}

针对不同表的通用操作

package com.an.preparedstatement.crud;

import java.util.List;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;

import org.junit.Test;

import com.an.util.JDBCUtils;

/**
 * 使用 PreparedStatemnet 实现针对于不同表的查询操作
 * @author an
 *
 */

public class PreparedStatementQueryTest {
	public <T> T getInstance(Class<T> clazz, String sql, Object...args) throws Exception {
		Connection connection = JDBCUtils.getConnection();
		PreparedStatement ps = connection.prepareStatement(sql);
		for(int i=0;i<args.length;i++) {
			ps.setObject(i+1, args[i]);
		}
		ResultSet rs = ps.executeQuery();
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		if(rs.next()) {
//			Order order = new Order();
			T t = clazz.newInstance();
			for(int i=0;i<columnCount;i++) {
				Object columnValue = rs.getObject(i+1);
				// 获取列的列名，而不是别名
//				String columnName = rsmd.getColumnName(i+1); - 不推荐使用
				String columnName = rsmd.getColumnLabel(i+1);
				// 通过反射，将对象指定名的属性赋值为指定值columnValue
				Field field = clazz.getDeclaredField(columnName);
				field.setAccessible(true);
				field.set(t,columnValue);
			}
			return t;
		}
		JDBCUtils.closeResource(connection, ps, rs);
		return null;
	}
	
	@Test
	public void testGetInstance() throws Exception {
		String sql = "select id,name,email from customers where id=?";
		
		Customer customer = getInstance(Customer.class, sql, 12);
		System.out.println(customer);
		
		String sql1 = "select order_id orderId,order_name orderName from `order` where order_id=?";
		Order order = getInstance(Order.class, sql1, 1);
		System.out.println(order);
	}
	/**
	 * 返回表的多条记录
	 */
	public <T> List<T> getForList(Class<T> clazz, String sql, Object...args) throws Exception {
		Connection connection = JDBCUtils.getConnection();
		PreparedStatement ps = connection.prepareStatement(sql);
		for(int i=0;i<args.length;i++) {
			ps.setObject(i+1, args[i]);
		}
		ResultSet rs = ps.executeQuery();
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		// 创建集合对象
		ArrayList<T> arrayList = new ArrayList<T>();
		// 给 T 对象指定的属性赋值的过程
		while(rs.next()) {
//			Order order = new Order();
			T t = clazz.newInstance();
			for(int i=0;i<columnCount;i++) {
				Object columnValue = rs.getObject(i+1);
				// 获取列的列名，而不是别名
//				String columnName = rsmd.getColumnName(i+1); - 不推荐使用
				String columnName = rsmd.getColumnLabel(i+1);
				// 通过反射，将对象指定名的属性赋值为指定值columnValue
				Field field = clazz.getDeclaredField(columnName);
				field.setAccessible(true);
				field.set(t,columnValue);
				
			}
			arrayList.add(t);
		}
		JDBCUtils.closeResource(connection, ps, rs);
		return arrayList;
		
		
	}
	@Test
	public void testGetForList() throws Exception {
		String sql = "select id,name,email from customers where id<?";
		List<Customer> list = getForList(Customer.class, sql, 12);
		list.forEach(System.out::println);
	}
}

插入 BLOB 类型字段

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

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

类型	大小(字节)
TinyBlob	最大255
Blob	最大65k
MediumBlob	最大16M
LongBlob	最大4G

package com.an.blob;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.junit.Test;

import com.an.preparedstatement.crud.CustomerForQuery;
import com.an.util.JDBCUtils;
import java.sql.Blob;

/**
 * 测试使用PreparedStatement操作Blob类型的数据
 * @author an
 *
 */

public class BlobTest {
	// 向数据表 Customers 中插入Blob类型的字段
	@Test
	public void testInsert() throws Exception {
		Connection conn = JDBCUtils.getConnection();
		String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		
		ps.setObject(1, "张三");
		ps.setObject(2, "zhangsan@qq.com");
		ps.setObject(3, "1992-09-08");
		FileInputStream iStream = new FileInputStream(new File("D:/anchor.png"));
		ps.setBlob(4, iStream);
		ps.execute();
		JDBCUtils.closeResource(conn, ps);
	}
	
	// 查询数据表 customers 中 Blob 类型的字段
	@Test
	public void testQuery() throws Exception {
		Connection conn = JDBCUtils.getConnection();
		String sql = "select id,name,email,birth,photo from customers where id=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, 21);
		ResultSet rs = ps.executeQuery();
		if(rs.next()) {
//			方式1
//			int id = rs.getInt(1);
//			String name = rs.getString(2);
//			String email = rs.getString(3);
//			String birth = rs.getDate(4);
		// 方式2
			int id = rs.getInt("id");
			String name = rs.getString("name");
			String email = rs.getString("email");
			Date birth = rs.getDate("birth");
			
			Customer cust = new Customer(id,name,email,birth);
			
			System.out.println(cust);
			// 将 Blob 类型字段下载下来，以文件方式保存在本地
			Blob photo = rs.getBlob("photo");
			InputStream is = photo.getBinaryStream();
			FileOutputStream fos = new FileOutputStream("test.png");
			byte[] buffer = new byte[1024];
			int len ;
			while ((len = is.read(buffer))!=-1) {
				fos.write(buffer,0,len);
				
			}
			is.close();
			fos.close();
			JDBCUtils.closeResource(conn, ps, rs);
		}
	}
}


class Customer {
	private int id;
	private String name;
	private String email;
	private Date birth;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public Date getBirth() {
		return birth;
	}
	public void setBirth(Date birth) {
		this.birth = birth;
	}
	@Override
	public String toString() {
		return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";
	}
	
	public Customer() {
		super();
	}
	public Customer(int id, String name, String email, Date birth) {
		super();
		this.id = id;
		this.name = name;
		this.email = email;
		this.birth = birth;
	}
	
}

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

如果在指定相关的 Blob 类型后，还报错：xxx to large，那么需要在 mysql 的安装目录下的 my.ini 文件加上如下的配置：max_allowed_packet=16M，修改后，需要重新启动mysql服务。

PreparedStatement 批量操作

PreparedStatement vs Statement

• 代码的可读性和可维护性

• PreparedStatement 能最大可能提高性能

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

  • 在 statement 语句中，即使是相同操作但因为数据内容不一样，所以整个语句本身不匹配，没有缓存语句的意义，事实是没有数据库会对普通语句编译后的执行代码缓存，这样每执行一次都要对传入的语句编译一次。

  • 语法检查，语义检查，翻译成二进制命令，缓存

• PreparedStatement 可以放置 SQL 注入

package com.an.preparedstatement.crud;

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

import org.junit.Test;

import com.an.util.JDBCUtils;

/**
 * 使用 PreparedStatement 实现批量数据的操作
 * update、delete 本身就具有批量操作的效果
 * 此时的批量操作，主要指的是批量插入、使用PreparedStatement
 * 如何实现更高效的批量插入？
 * 
 * 题目：向 goods 表中插入20000条数据
 * CREATE TABLE goods(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(25)
	);
 * 方式1：使用statement 
 * JDBCUtils.getConnection();
 * conn.createStatement();
 * for(int i=1;i<=20000;i++) {
 * 	String sql = "insert into goods(name) values('name_"+i+"')"
 * 	st.execute(sql);
 * 	
 * }
 * 
 * @author an
 *
 */

public class InsertTest {
	// 批量插入的方式二: 使用 preparedStatement 取代statement
	@Test
	public void test2() throws Exception {
		Connection conn = JDBCUtils.getConnection();
		String sql = "insert into goods(name)values(?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		for(int i=1;i<=20000;i++) {
			ps.setObject(1, "name_"+i);
			ps.execute();
		}
		JDBCUtils.closeResource(conn, ps);
	}
	/**
	 * 批量插入的方式三：
	 * 1、addBatch()、executeBatch()、clearBatch();
	 * 2、mysql 服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理
	 * 的支持 ?rewriteBatchedStatements=true 写在配置文件的 url 后面
	 * 3、使用更新的mysql，驱动：mysql-connector-java-5.1.37-bin.jar
	 */
	@Test
	public void test3() throws Exception {
		Connection conn = JDBCUtils.getConnection();
		String sql = "insert into goods(name)values(?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		for(int i=1;i<=20000;i++) {
			ps.setObject(1, "name_"+i);
			// 1、攒 sql
			ps.addBatch();
			
			if(i%500==0) { 
				// 2、执行
				ps.executeBatch();
				// 3、清空 batch
				ps.clearBatch();
			}
		}
		JDBCUtils.closeResource(conn, ps);
	}
	/**
	 * 批量插入的方式4：不允许自动提交
	 */
	public void test4() throws Exception {
		Connection conn = JDBCUtils.getConnection();
		
		/*
		 * 设置不允许自动提交
		 */
		conn.setAutoCommit(false);
		String sql = "insert into goods(name)values(?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		for(int i=1;i<=20000;i++) {
			ps.setObject(1, "name_"+i);
			// 1、攒 sql
			ps.addBatch();
			
			if(i%500==0) {
				// 2、执行
				ps.executeBatch();
				// 3、清空 batch
				ps.clearBatch();
			}
		}
		// 提交数据
		conn.commit();
		JDBCUtils.closeResource(conn, ps);
	}
	
}

jdbc.properties

user=root
password=2232116722ca
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
driverClass=com.mysql.jdbc.Driver

数据库事务

数据库事务介绍

• 事务：一组逻辑操作单元，使数据从一种状态变换到另一种状态。

• 事务处理：保证所有的事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交，那么这些修改就永久的保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚到最初状态。

• 为了确保数据库中数据的一致性，数据的操纵应当是离散的成组的逻辑单元：当它全部完成时，数据的一致性可以抱持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有从起始点以后的操作应全部回退到开始状态。

JDBC 事务处理

• 数据一旦提交，都不可以回滚。

• 数据什么时候提交？

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

  • 关闭数据库连接，数据就会自动的提交。如果多个操作，每个操作使用的是自己单独的连接，则无法保证事务，即同一个事务的多个操作必须在同一个连接下。

package com.an.transaction;

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

import org.junit.Test;

import com.an.util.JDBCUtils;

/**
 * 数据一旦提交，就不可回滚。
 * 
 * 哪些操作会导致数据的自动提交？
 * 	> DDL 操作一旦执行，都会自动提交
 * 	> DML 默认情况下，一旦执行，就会自动提交
 * 		> 我们可以通过 set autocommit=false取消DML操作的自动提交，注意：对DDL没用
 * 	> 默认关闭连接时，会将未提交的数据自动提交
 *  
 * @author an
 *
 */

public class TransactionTest {
	// 通用的增删改 -- version 1.0
	public int update(String sql, Object... args)  {
		Connection connection = null;
		PreparedStatement ps = null;
		try {
			// 1、获取数据库的连接
			connection = JDBCUtils.getConnection();
			// 2、预编译sql语句，返回PreparedStatement的实例
			ps = connection.prepareStatement(sql);
			// 3、填充占位符
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			return ps.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(connection, ps);
		}
		return 0;
	}
	/**
	 * 针对于数据表 user_table 来说
	 * AA 用户给 BB 用户转账 100
	 * update user_table set balance = balance-100 where user='AA';
	 * update user_table set balance = balance+100 where user='BB';
	 * @throws Exception 
	 */
	@Test
	public void testUpdate() {
		// 未考虑数据库事务
		String sql1 = "update user_table set balance = balance-100 where user=?;";
		update(sql1, "AA");
		// 模拟网络异常
		System.out.println(10/0);
		
		String sql2 = "update user_table set balance = balance+100 where user=?;";
		update(sql2, "BB");
		System.out.println("转账成功");
	}
	
	
	/**
	 * 考虑事务
	 * @param sql
	 * @param args
	 * @return
	 */
	
	public int update(Connection conn, String sql, Object... args)  {
		PreparedStatement ps = null;
		try {
			// 1、预编译sql语句，返回PreparedStatement的实例
			ps = conn.prepareStatement(sql);
			// 2、填充占位符
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			return ps.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps);
		}
		return 0;
	}
	@Test
	public void testUpdateWithTx() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			// 取消数据的自动提交
			conn.setAutoCommit(false);
			String sql1 = "update user_table set balance = balance-100 where user=?;";
			update(conn, sql1, "AA");
			// 模拟网络异常
			System.out.println(10 / 0);

			String sql2 = "update user_table set balance = balance+100 where user=?;";
			update(conn, sql2, "BB");
			System.out.println("转账成功");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			// 重置连接为默认状态，针对于使用数据库连接池时
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			JDBCUtils.closeResource(conn, null);
		}
	}
	
	
}

数据库的隔离级别

隔离级别	描述
READ UNCOMMITTED	允许事务读取未被其他事务提交的变更、脏读、不可重复读和幻读都会出现
READ COMMITTED	只允许事务读取已经被其他事务提交的变更，可以避免脏读，但不可重复读和幻读问题仍然可能出现
REPEATABLE READ	确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新，可以避免脏读和不可重复读，但幻读的问题仍然存在
SERIALIZABLE	确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对表进行插入、更新和删除操作，所有并发问题都可避免，但性能低下

命令行验证数据库的隔离级别

创建用户，并且授予用户权限

create user tom indentified by 'abc123';
grant select,insert,update,delete on test.* to tom@localhost identified by 'abc123';

查询当前的隔离级别

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

登录用户tom，设置不自动提交

mysql -utom -pabc123
set autocommit=false;

用户tom执行更新语句

mysql> update user_table set balance=3000 where user='cc';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

用户tom执行查询语句

mysql> select * from user_table where user='cc';
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| CC   | abcd     |    3000 |
+------+----------+---------+
1 row in set (0.00 sec)

用户root执行查询语句

mysql> select * from user_table where user='cc';
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| CC   | abcd     |    2000 |
+------+----------+---------+
1 row in set (0.00 sec)

用户tom提交事务

commit;

用户root执行查询语句

mysql> select * from user_table where user='cc';
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| CC   | abcd     |    3000 |
+------+----------+---------+
1 row in set (0.00 sec)

设置当前 mySQL 连接的隔离级别：

set transaction isolation level read committed;

设置数据库系统的全局的隔离级别:

set global transaction isolation level read committed;

断掉当前连接并且重新进入，新的隔离级别生效。

继续更改隔离级别为读未提交

set global transaction isolation level read uncommitted;

set autocommit=false;

tom 用户（还未提交）

mysql> select * from user_table where user='cc';
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| CC   | abcd     |    3000 |
+------+----------+---------+
1 row in set (0.00 sec)

mysql> update user_table set balance=5000 where user='cc';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root 用户

mysql> select * from user_table where user='cc';
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| CC   | abcd     |    5000 |
+------+----------+---------+
1 row in set (0.00 sec)

说明出现了脏读问题。

JAVA 代码隔离级别

读已提交

	// 通用的查询操作用于返回数据表的基类（ver2.0，考虑事务）
	public <T> T getInstance(Connection conn, Class<T> clazz, String sql, Object...args) throws Exception {
//		Connection connection = JDBCUtils.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		for(int i=0;i<args.length;i++) {
			ps.setObject(i+1, args[i]);
		}
		ResultSet rs = ps.executeQuery();
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		if(rs.next()) {
//			Order order = new Order();
			T t = clazz.newInstance();
			for(int i=0;i<columnCount;i++) {
				Object columnValue = rs.getObject(i+1);
				// 获取列的列名，而不是别名
//				String columnName = rsmd.getColumnName(i+1); - 不推荐使用
				String columnName = rsmd.getColumnLabel(i+1);
				// 通过反射，将对象指定名的属性赋值为指定值columnValue
				Field field = clazz.getDeclaredField(columnName);
				field.setAccessible(true);
				field.set(t,columnValue);
			}
			return t;
		}
		JDBCUtils.closeResource(null, ps, rs);
		return null;
	}
	
	@Test
	public void testTransactionSelect() throws Exception {
		
		Connection conn = JDBCUtils.getConnection();
		conn.setAutoCommit(false);
		// 获取当前连接的隔离级别
		System.out.println(conn.getTransactionIsolation());
		// 设置数据库的隔离级别
		conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
		String sql = "select user,password,balance from user_table where user=?";
//		User user = User.class.newInstance();
		
		User user = getInstance(conn, User.class, sql, "CC");
		
		conn.commit();
		System.out.println(user);
		JDBCUtils.closeResource(conn, null);
	}
	
	@Test
	public void testTransactionUpdate() throws Exception {
		Connection conn = JDBCUtils.getConnection();
		conn.setAutoCommit(false);
		String sql = "update user_table set balance = ? where user=?;";
		update(conn, sql,6000 , "CC");
		System.out.println("转账成功");
		Thread.sleep(15000);
		System.out.println("修改结束");
		conn.commit();
		JDBCUtils.closeResource(conn, null);
	}

DAO

提供操作数据表的 BaseDAO

package com.an.dao;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.an.util.JDBCUtils;

/**
 * 封装了针对于数据表的通用操作
 * 
 * @author an
 *
 */
public abstract class BaseDAO {
	// 通用的查询操作用于返回数据表的基类（ver2.0，考虑事务）
	public <T> T getInstance(Connection conn, Class<T> clazz, String sql, Object... args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			if (rs.next()) {
				T t = clazz.newInstance();
				for (int i = 0; i < columnCount; i++) {
					Object columnValue = rs.getObject(i + 1);
					String columnName = rsmd.getColumnLabel(i + 1);
					Field field = clazz.getDeclaredField(columnName);
					field.setAccessible(true);
					field.set(t, columnValue);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps, rs);
		}
		return null;
	}
	// 通用更新操作
	public int update(Connection conn, String sql, Object... args) {
		PreparedStatement ps = null;
		try {
			// 1、预编译sql语句，返回PreparedStatement的实例
			ps = conn.prepareStatement(sql);
			// 2、填充占位符
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			return ps.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps);
		}
		return 0;
	}
	// 通用查询操作，用于返回多条对象构成的集合
	public <T> List<T> getForList(Connection conn, Class<T> clazz, String sql, Object... args) throws Exception {
		// Connection connection = JDBCUtils.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		// 创建集合对象
		ArrayList<T> arrayList = null;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			arrayList = new ArrayList<T>();
			// 给 T 对象指定的属性赋值的过程
			while (rs.next()) {
				// Order order = new Order();
				T t = clazz.newInstance();
				for (int i = 0; i < columnCount; i++) {
					Object columnValue = rs.getObject(i + 1);
					// 获取列的列名，而不是别名
					// String columnName = rsmd.getColumnName(i+1); - 不推荐使用
					String columnName = rsmd.getColumnLabel(i + 1);
					// 通过反射，将对象指定名的属性赋值为指定值columnValue
					Field field = clazz.getDeclaredField(columnName);
					field.setAccessible(true);
					field.set(t, columnValue);

				}
				arrayList.add(t);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps, rs);
		}

		return arrayList;

	}
	// 处理特殊需求，如 count()
	// 用于查询特殊值的方法
	public <E> E getValue(Connection conn,String sql,Object...args) throws Exception{
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			for(int i=0;i<args.length;i++) {
				ps.setObject(i+1, args[i]);
			}
			rs = ps.executeQuery();
			if(rs.next()) {
				return (E) rs.getObject(1);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps, rs);
		}
		return null;
	}
}

创建 CustomerDAO 的一个接口

package com.an.dao;

import java.util.List;
import java.sql.Connection;
import java.sql.Date;

/**
 * 针对于 Customer 表的规范
 * @author an
 *
 */
public interface CustomerDAO {
	/**
	 * 将 cust 对象添加到数据库中
	 * @param conn
	 * @param cust
	 */
	void insert(Connection conn, Customer cust);
	
	/**
	 * 根据指定的 id 删除表中的一条记录
	 * @param conn
	 * @param id
	 */
	void deleteById(Connection conn, int id);

	/**
	 * 针对于内存中的 cust 对象修改数据表中记录
	 * @param conn
	 * @param cust
	 */
	void updateById(Connection conn,Customer cust);
	
	/** 
	 * 根据指定的 id 查询客户的 customer
	 * @param conn
	 * @param id
	 */
	Customer getCustomerById(Connection conn,int id);
	
	/**
	 * 查询表中所有记录构成的集合
	 * @param conn
	 * @return
	 */
	List<Customer> getAll(Connection conn);
	
	/**
	 * 返回数据表中数据的条目数
	 * @param conn
	 * @return
	 */
	long getCount(Connection conn);
	
	/**
	 * 返回数据表中最大的生日
	 * @param conn
	 * @return
	 */
	Date getMaxByBirth(Connection conn);	
}

实现该接口

package com.an.dao;

import java.sql.Connection;
import java.sql.Date;
import java.util.List;

import javax.swing.text.DefaultEditorKit.CutAction;

/**
 * DAO: data(base) access object
 * 封装了对数据表的通用的操作
 * @author an
 *
 */

public class CustomerDAOImpl extends BaseDAO implements CustomerDAO {

	@Override
	public void insert(Connection conn, Customer cust) {
		String sql = "insert into customers(name,email,birth)values(?,?,?)";
		update(conn, sql, cust.getName(),cust.getEmail(),cust.getBirth());
	}

	@Override
	public void deleteById(Connection conn, int id) {
		String sql = "delete from customers where id=?";
		update(conn, sql, id);
	}

	@Override
	public void updateById(Connection conn, Customer cust) {
		String sql = "update customers set name=?,email=?,birth=? where id=?";
		update(conn, sql,cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());
	}

	@Override
	public Customer getCustomerById(Connection conn, int id) {
		String sql = "select id,name,email,birth from customers where id=?";
		Customer customer = getInstance(conn, Customer.class, sql, id);
		return customer;
	}

	@Override
	public List<Customer> getAll(Connection conn) {
		String sql = "select id,name,email,birth from customers";
		List<Customer> list = getForList(conn, Customer.class, sql);
		return list;
	}

	@Override
	public Long getCount(Connection conn) {
		String sql = "select count(*) from customers";
		return getValue(conn, sql);
	}

	@Override
	public Date getMaxByBirth(Connection conn) {
		String sql = "select max(birth) from customers";
		return getValue(conn, sql);
	}

}

实现测试类

package com.an.dao.junit;

import static org.junit.Assert.*;

import java.util.List;
import java.sql.Connection;
import java.sql.Date;

import org.junit.Test;

import com.an.dao.Customer;
import com.an.dao.CustomerDAOImpl;
import com.an.util.JDBCUtils;

public class CustomerDAOImplTest {

	private CustomerDAOImpl dao = new CustomerDAOImpl();
	@Test
	public void testInsert() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			Customer cust = new Customer(1,"小飞","232@qq.com",new Date(324323232L));
			dao.insert(conn,cust);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtils.closeResource(conn, null);
		}
		System.out.println("添加成功");
	}

	@Test
	public void testDeleteById() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			dao.deleteById(conn, 13);
			System.out.println("删除成功");
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testUpdateById() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			Customer cust = new Customer(18,"贝多芬","bdf@gmail.com",new Date(22234543L));
			dao.updateById(conn, cust);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtils.closeResource(conn, null);
		}
		System.out.println("修改成功");
	}

	@Test
	public void testGetCustomerById() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			Customer cust = dao.getCustomerById(conn, 19);
			System.out.println(cust);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtils.closeResource(conn, null);
		}
		System.out.println("查询成功");
	}

	@Test
	public void testGetAll() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			List<Customer> list = dao.getAll(conn);
			list.forEach(System.out::println);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtils.closeResource(conn, null);
		}
		System.out.println("查询成功");
	}

	@Test
	public void testGetCount() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			Long count = dao.getCount(conn);
			System.out.println("表中的记录数为:"+count);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtils.closeResource(conn, null);
		}
		System.out.println("查询成功");
	}

	@Test
	public void testGetMaxByBirth() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			Date maxBirth = dao.getMaxByBirth(conn);
			System.out.println("最大的生日为:"+maxBirth);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtils.closeResource(conn, null);
		}
		System.out.println("查询成功");
	}

}

BaseDAO 优化：使用泛型

package com.an.dao2;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.an.util.JDBCUtils;

/**
 * 封装了针对于数据表的通用操作
 * 
 * @author an
 *
 */
public abstract class BaseDAO<T> {
	
	private Class<T> clazz = null; 
	
	{
		Type genericSuperclass = this.getClass().getGenericSuperclass();
		ParameterizedType paramType = (ParameterizedType) genericSuperclass;
		Type[] actualTypeArguments = paramType.getActualTypeArguments(); // 获取了父类的泛型参数
		clazz = (Class<T>)actualTypeArguments[0];
	}
	// 通用的查询操作用于返回数据表的基类（ver2.0，考虑事务）
	public T getInstance(Connection conn, String sql, Object... args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			if (rs.next()) {
				T t = clazz.newInstance();
				for (int i = 0; i < columnCount; i++) {
					Object columnValue = rs.getObject(i + 1);
					String columnName = rsmd.getColumnLabel(i + 1);
					Field field = clazz.getDeclaredField(columnName);
					field.setAccessible(true);
					field.set(t, columnValue);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps, rs);
		}
		return null;
	}
	// 通用更新操作
	public int update(Connection conn, String sql, Object... args) {
		PreparedStatement ps = null;
		try {
			// 1、预编译sql语句，返回PreparedStatement的实例
			ps = conn.prepareStatement(sql);
			// 2、填充占位符
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			return ps.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps);
		}
		return 0;
	}
	// 通用查询操作，用于返回多条对象构成的集合
	public List<T> getForList(Connection conn, String sql, Object... args){
		// Connection connection = JDBCUtils.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		// 创建集合对象
		ArrayList<T> arrayList = null;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			arrayList = new ArrayList<T>();
			// 给 T 对象指定的属性赋值的过程
			while (rs.next()) {
				// Order order = new Order();
				T t = clazz.newInstance();
				for (int i = 0; i < columnCount; i++) {
					Object columnValue = rs.getObject(i + 1);
					// 获取列的列名，而不是别名
					// String columnName = rsmd.getColumnName(i+1); - 不推荐使用
					String columnName = rsmd.getColumnLabel(i + 1);
					// 通过反射，将对象指定名的属性赋值为指定值columnValue
					Field field = clazz.getDeclaredField(columnName);
					field.setAccessible(true);
					field.set(t, columnValue);

				}
				arrayList.add(t);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps, rs);
		}

		return arrayList;

	}
	// 处理特殊需求，如 count()
	// 用于查询特殊值的方法
	public <E> E getValue(Connection conn,String sql,Object...args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			for(int i=0;i<args.length;i++) {
				ps.setObject(i+1, args[i]);
			}
			rs = ps.executeQuery();
			if(rs.next()) {
				return (E) rs.getObject(1);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps, rs);
		}
		return null;
	}
}

数据库连接池

数据库连接池的必要性

• 在使用开发基于数据库的web程序时，传统的模式基本按照以下步骤：
  • 在主程序(如 servlet、beans)中建立数据库连接
  • 进行 sql 操作
  • 断开数据库连接
• 这种模式开发，存在的问题
  • 普通的 JDBC 数据库连接使用 DriverManager 来获取，每次向数据库建立连接时都要将 Connection 加载到内存中，再验证用户名和密码，需要数据库连接的时候，就向数据库要求一个，执行完成后再断开连接，这样的方式会消耗大量的时间和资源。数据库的连接资源并没有得到很好的重复利用。若同时有几百个人甚至几千个人在线，频繁的进行数据库连接操作将会占用很多的资源，严重的甚至会造成服务器的崩溃。
  • 对于每一次数据库连接，使用完成后都得断开，否则，如果程序出现异常而未能关闭，将会导致数据库系统中的内存泄漏，最终将导致重启数据库。
  • 这种开发不能控制被创建的连接对象数，系统资源将会被毫无顾忌的分配出去，如果连接过多，可能导致内存泄漏、服务器崩溃。

数据库连接池技术

• 为了解决传统开发的数据库连接问题，可以采用数据库连接池技术。

• 数据库连接池的基本思想：就是为数据库连接建立一个缓冲池，预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需要从缓冲池中取出一个，使用完毕后再放回去。

• 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。

• 数据库连接池在初始化时将创建一定数量的数据库连接放入到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多连接数量，连接池的最大连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的最大连接数超过最大连接数量时，这些请求将会被加入到等待队列中。

C3P0 数据库连接

安装数据库连接池驱动 c3p0-0.9.1.2.jar。

package com.an2.connection;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;

import org.junit.Test;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.DataSources;

public class C3P0Test {
	// 方式1
	@Test
	public void testGet() throws Exception {
		// 获取c3p0数据库连接池
		ComboPooledDataSource cpds = new ComboPooledDataSource();
		cpds.setDriverClass( "com.mysql.jdbc.Driver" ); //loads the jdbc driver            
		cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
		cpds.setUser("root");                                  
		cpds.setPassword("2232116722ca");   
		// 通过设置相关的参数对数据库连接池进行管理
		// 设置初始时数据库连接池中的连接数
		cpds.setInitialPoolSize(10);
		Connection conn = cpds.getConnection();
		System.out.println(conn);
		// 销毁c3p0数据库连接池
		DataSources.destroy(cpds);
	}
	// 方式2：使用配置文件
	@Test
	public void testGetConnection1() throws SQLException {
		ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
		Connection connection = cpds.getConnection();
		System.out.println(connection);
	}
	
}

c3p0config.xml

<?xml version="1.0" encoding="UTF-8"?>


<c3p0-config>


	<!-- This app is massive! -->
	<named-config name="helloc3p0">
		<!-- 提供获取连接的四个信息 -->
	
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
		<property name="user">root</property>
		<property name="password">2232116722ca</property>

		<!-- 进行数据库连接池管理的基本信息 -->
		<!-- 当数据库连接池中连接数不够时，c3p0一次向数据库服务器申请的连接数 -->
		<property name="acquireIncrement">5</property>
		<!-- c3p0数据库连接池中初始化时的连接数 -->
		<property name="initialPoolSize">10</property>
		<!-- c3p0数据库连接池中维护的最小连接数 -->
		<property name="minPoolSize">10</property>
		<!-- 最多连接数 -->
		<property name="maxPoolSize">100</property>
		<!-- c3p0数据库连接池维护的最多的Statement个数 -->
		<property name="maxStatements">50</property>
		<!-- 每一个连接可以使用的最多的statement个数 -->
		<property name="maxStatementsPerConnection">2
		</property>

	</named-config>
</c3p0-config>

使用c3p0重构jdbcutils

package com.an2.util;

import java.sql.Connection;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;


public class JDBCUtils {
	public static Connection getConnection1() throws SQLException {
		ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
		Connection connection = cpds.getConnection();
		return connection;
	}
}

DBCP 实现数据库连接池

添加相应驱动commons-dbcp-1.4.jar和commons-pool-1.5.5.jar。

package com.an2.connection;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;
import javax.xml.transform.Source;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.junit.Test;

public class DBCPTest {
	/**
	 * 测试 DBCP 的数据库连接池技术
	 * 方式1：硬编码，不推荐
	 * @throws SQLException 
	 */
	@Test
	public void testGetConnection() throws SQLException {
		// 创建了 DBCP 的数据库连接池
		BasicDataSource source = new BasicDataSource();
		// 设置基本信息
		source.setDriverClassName("com.mysql.jdbc.Driver");
		source.setUrl("jdbc:mysql:///test");
		source.setUsername("root");
		source.setPassword("2232116722ca");
		
		// 还可以设置其他涉及数据库连接池管理的相关属性
		source.setInitialSize(10);
		source.setMaxActive(10);
		
		// ...
		
		Connection conn = source.getConnection();
		
		System.out.println(conn);
	}
	// 方式2：使用配置文件
	@Test
	public void testGetConnection2() throws Exception {
		Properties pros = new Properties();
		
		// 方式1：
		//ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
		
		// 方式2：
		FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
		
		pros.load(is);
		DataSource source = BasicDataSourceFactory.createDataSource(pros);
		Connection conn = source.getConnection();
		System.out.println(conn);
	}
	
}

dbcp.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///test
username=root
password=2232116722ca
initialSize=10
maxActive=10

Druid 数据库连接池实现

导入驱动druid-1.1.10.jar。

package com.an2.connection;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;

import javax.sql.DataSource;

import org.junit.Test;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

public class DruidTest {
	@Test
	public void getConnection() throws Exception {
		Properties pros = new Properties();
		InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
		pros.load(is);
		DataSource source = DruidDataSourceFactory.createDataSource(pros);
		Connection connection = source.getConnection();
		System.out.println(connection);
	}
}

druid.properties

url=jdbc:mysql:///test
username=root
password=2232116722ca
driverClassName=com.mysql.jdbc.Driver

initialSize=10
maxActive=12

Apache-DBUtils 实现 CRUD 操作

Apache-DBUtils 简介

• common-dbutils 是 Apache 组织提供的一个开源 JDBC 工具类库，它是对 JDBC 的简单封装，能够减少 jdbc 编码的工作量。

使用 QueryRunner 测试添加、查询数据

导入驱动 commons-dbutils-1.3.jar。

package com.an3.dbutils;

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

import org.apache.commons.dbutils.DbUtils;
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.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import com.an.dao.Customer;
import com.an.util.JDBCUtils;
import com.mysql.jdbc.Statement;

public class QueryRunnerTest {

	// 测试插入
	@Test
	public void testInsert() {
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection();
			String sql = "insert into customers(name,email,birth)values(?,?,?)";
			int insertCount = runner.update(conn, sql, "小A", "a@126.com", "1997-09-08");
			System.out.println("添加了" + insertCount + "条记录");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}
	
	// 测试查询
	/**
	 * BeanHander 是 ResultSetHandler 接口的实现，用于封装表中的一条记录
	 * @throws Exception
	 */
	@Test
	public void testQuery1() throws Exception {
		Connection conn = null;
		QueryRunner runner = new QueryRunner();
		
		conn = JDBCUtils.getConnection();
		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, 23);
		System.out.println(customer);
	}
	// 查询一组数据
	@Test
	public void testQuery2() throws Exception {
		Connection conn = null;
		QueryRunner runner = new QueryRunner();
		
		conn = JDBCUtils.getConnection();
		String sql = "select id,name,email,birth from customers where id<?";
		BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
		List<Customer> list= runner.query(conn, sql, handler, 23);
		list.forEach(System.out::println);
	}
	// 对应表中的一条记录，用键值对的方式存储
	@Test
	public void testQuery3() throws Exception {
		Connection conn = null;
		QueryRunner runner = new QueryRunner();
		
		conn = JDBCUtils.getConnection();
		String sql = "select id,name,email,birth from customers where id=?";
		MapHandler handler = new MapHandler();
		Map<String, Object> map = runner.query(conn, sql, handler, 23);
		System.out.println(map);
//		list.forEach(System.out::println);
	}
	// MapListHandler
	@Test
	public void testQuery4() throws Exception {
		Connection conn = null;
		QueryRunner runner = new QueryRunner();
		
		conn = JDBCUtils.getConnection();
		String sql = "select id,name,email,birth from customers where id<?";
		MapListHandler handler = new MapListHandler();
		List<Map<String, Object>> list =  runner.query(conn, sql, handler, 23);
//		System.out.println(map);
		list.forEach(System.out::println);
	}
	
	// 返回特殊值
	@Test
	public void testQuery5() throws Exception {
		Connection conn = null;
		QueryRunner runner = new QueryRunner();
		
		conn = JDBCUtils.getConnection();
		String sql = "select count(*) from customers";
		ScalarHandler handler = new ScalarHandler();
		Long count =  (Long)runner.query(conn, sql, handler);
		System.out.println(count);
//		list.forEach(System.out::println);
	}
	
	
	// 自定义 ResultHandler 的实现类
	@Test
	public void testQuery6() throws Exception {
		Connection conn = null;
		QueryRunner runner = new QueryRunner();
		
		conn = JDBCUtils.getConnection();
		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 {
				// TODO Auto-generated method stub
				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;
			}
			
		};
		Customer customer = runner.query(conn, sql, handler, 23);
		System.out.println(customer);
//		list.forEach(System.out::println);
	}
	
	// 使用 dbutils 提供的工具类是心啊关闭资源
	public static void closeResource1(Connection conn, Statement ps, ResultSet rs) {
//		DbUtils.close(conn);
//		DbUtils.close(ps);
//		DbUtils.close(rs);
		DbUtils.closeQuietly(conn);
		DbUtils.closeQuietly(ps);
		DbUtils.closeQuietly(rs);
	}
	
	
	
	
}

完结撒花