JDBC核心技术

以下为本人的学习笔记

第1章：JDBC概述

1.1 数据的持久化

1.2 Java中的数据存储技术

1.3 JDBC介绍

1.4 JDBC体系结构

JDBC是sun公司提供一套用于数据库操作的接口，java程序员只需要面向这套接口编程即可。

不同的数据库厂商，需要针对这套接口，提供不同实现。不同的实现的集合，即为不同数据库的驱动。 ————面向接口编程

面向接口编程的思想：

JDBC是sun公司提供一套用于数据库操作的接口，java程序员只需要面向这套接口编程即可。

不同的数据库厂商，需要针对这套接口，提供不同实现。不同的实现的集合，即为不同数据库的驱动。

面向接口编程，import那里都是接口，没有第三方导入

1.5 JDBC程序编写步骤

JDBC核心技术

第2章：获取数据库连接

2.1 要素一：Driver接口实现类

2.1.1 Driver接口介绍

导入mysql-connector-java-5.1.37-bin.jar

2.1.2 加载与注册JDBC驱动

2.2 要素二：URL

2.3 要素三：用户名和密码

2.4 数据库连接方式举例

2.4.1 连接方式一

    @Test
 public void testConnection1() {
   try {
     //1.提供java.sql.Driver接口实现类的对象
     Driver driver = null;
     driver = new com.mysql.jdbc.Driver();
​
     //2.提供url，指明具体操作的数据
     String url = "jdbc:mysql://localhost:3306/test";
​
     //3.提供Properties的对象，指明用户名和密码
     Properties info = new Properties();
     info.setProperty("user", "root");
     info.setProperty("password", "abc123");
​
     //4.调用driver的connect()，获取连接
     Connection conn = driver.connect(url, info);
     System.out.println(conn);
    } catch (SQLException e) {
     e.printStackTrace();
    }
  }

说明：上述代码中显式出现了第三方数据库的API

2.4.2 连接方式二

    @Test
 public void testConnection2() {
   try {
     //1.实例化Driver
     String className = "com.mysql.jdbc.Driver";
     Class clazz = Class.forName(className);
     Driver driver = (Driver) clazz.newInstance();
​
     //2.提供url，指明具体操作的数据
     String url = "jdbc:mysql://localhost:3306/test";
​
     //3.提供Properties的对象，指明用户名和密码
     Properties info = new Properties();
     info.setProperty("user", "root");
     info.setProperty("password", "abc123");
​
     //4.调用driver的connect()，获取连接
     Connection conn = driver.connect(url, info);
     System.out.println(conn);
​
    } catch (Exception e) {
     e.printStackTrace();
    }
  }

说明：相较于方式一，这里使用反射实例化Driver，不在代码中体现第三方数据库的API。体现了面向接口编程思想。

2.4.3 连接方式三

    @Test
 public void testConnection3() {
   try {
     //1.数据库连接的4个基本要素：
     String url = "jdbc:mysql://localhost:3306/test";
     String user = "root";
     String password = "abc123";
     String driverName = "com.mysql.jdbc.Driver";
​
     //2.实例化Driver
     Class clazz = Class.forName(driverName);
     Driver driver = (Driver) clazz.newInstance();
     //3.注册驱动
     DriverManager.registerDriver(driver);
     //4.获取连接
     Connection conn = DriverManager.getConnection(url, user, password);
     System.out.println(conn);
    } catch (Exception e) {
     e.printStackTrace();
    }
​
  }

说明：使用DriverManager实现数据库的连接。体会获取连接必要的4个基本要素。

2.4.4 连接方式四

    @Test
 public void testConnection4() {
   try {
     //1.数据库连接的4个基本要素：
     String url = "jdbc:mysql://localhost:3306/test";
     String user = "root";
     String password = "abc123";
     String driverName = "com.mysql.jdbc.Driver";
​
     //2.加载驱动 （①实例化Driver ②注册驱动）
     Class.forName(driverName);
​
​
     //Driver driver = (Driver) clazz.newInstance();
     //3.注册驱动
     //DriverManager.registerDriver(driver);
     /*
     可以注释掉上述代码的原因，是因为在mysql的Driver类中声明有：
     static {
       try {
         DriverManager.registerDriver(new Driver());
       } catch (SQLException var1) {
         throw new RuntimeException("Can't register driver!");
       }
     }
​
      */
​
​
     //3.获取连接
     Connection conn = DriverManager.getConnection(url, user, password);
     System.out.println(conn);
    } catch (Exception e) {
     e.printStackTrace();
    }
​
  }

说明：不必显式的注册驱动了。因为在DriverManager的源码中已经存在静态代码块，实现了驱动的注册。

2.4.5 连接方式五(最终版)

    @Test
 public void testConnection5() throws Exception {
      //1.加载配置文件
   InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
   Properties pros = new Properties();
   pros.load(is);
   
   //2.读取配置信息
   String user = pros.getProperty("user");
   String password = pros.getProperty("password");
   String url = pros.getProperty("url");
   String driverClass = pros.getProperty("driverClass");
​
   //3.加载驱动
   Class.forName(driverClass);
​
   //4.获取连接
   Connection conn = DriverManager.getConnection(url,user,password);
   System.out.println(conn);
​
  }

其中，配置文件声明在工程的src目录下：【jdbc.properties】

user=root
password=abc123
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver

说明：使用配置文件的方式保存配置信息，在代码中加载配置文件

使用配置文件的好处：

①实现了代码和数据的分离，如果需要修改配置信息，直接在配置文件中修改，不需要深入代码 ②如果修改了配置信息，省去重新编译的过程。

第3章：使用PreparedStatement实现CRUD操作

3.1 操作和访问数据库

3.2 使用Statement操作数据表的弊端

public class StatementTest {
​
// 使用Statement的弊端：需要拼写sql语句，并且存在SQL注入的问题
@Test
public void testLogin() {
Scanner scan = new Scanner(System.in);
​
System.out.print("用户名：");
String userName = scan.nextLine();
System.out.print("密  码：");
String password = scan.nextLine();
​
// SELECT user,password FROM user_table WHERE USER = '1' or ' AND PASSWORD = '='1' or '1' = '1';
String sql = "SELECT user,password FROM user_table WHERE USER = '" + userName + "' AND PASSWORD = '" + password
+ "'";
User user = get(sql, User.class);
if (user != null) {
System.out.println("登陆成功!");
} else {
System.out.println("用户名或密码错误！");
}
}
​
// 使用Statement实现对数据表的查询操作
public <T> T get(String sql, Class<T> clazz) {
T t = null;
​
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
// 1.加载配置文件
InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
​
// 2.读取配置信息
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
​
// 3.加载驱动
Class.forName(driverClass);
​
// 4.获取连接
conn = DriverManager.getConnection(url, user, password);
​
st = conn.createStatement();
​
rs = st.executeQuery(sql);
​
// 获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
​
// 获取结果集的列数
int columnCount = rsmd.getColumnCount();
​
if (rs.next()) {
​
t = clazz.newInstance();
​
for (int i = 0; i < columnCount; i++) {
// //1. 获取列的名称
// String columnName = rsmd.getColumnName(i+1);
​
// 1. 获取列的别名
String columnName = rsmd.getColumnLabel(i + 1);
​
// 2. 根据列名获取对应数据表中的数据
Object columnVal = rs.getObject(columnName);
​
// 3. 将数据表中得到的数据，封装进对象
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t, columnVal);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭资源
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
​
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
​
return null;
}
}

综上：

JDBC核心技术

connection连接需要四个基本信息：url，user，password，driver

3.3 PreparedStatement的使用

3.3.1 PreparedStatement介绍

3.3.2 面试题：PreparedStatement 和 Statement的异同

3.3.3 Java与SQL对应数据类型转换表

Java类型 SQL类型
boolean BIT
byte TINYINT
short SMALLINT
int INTEGER
long BIGINT
String CHAR,VARCHAR,LONGVARCHAR
byte array BINARY , VAR BINARY
java.sql.Date DATE
java.sql.Time TIME
java.sql.Timestamp TIMESTAMP

3.3.4 使用PreparedStatement实现增、删、改操作

    //通用的增、删、改操作（体现一：增、删、改 ； 体现二：针对于不同的表）
public void update(String sql,Object ... args){
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取数据库的连接
conn = JDBCUtils.getConnection();
//2.获取PreparedStatement的实例 (或：预编译sql语句)
ps = conn.prepareStatement(sql);
//3.填充占位符
for(int i = 0;i < args.length;i++){
ps.setObject(i + 1, args[i]);
}
//4.执行sql语句
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally{
//5.关闭资源
JDBCUtils.closeResource(conn, ps);
}
}

注意：

方法加注解@Test将不能被调用，否则报错

Connection conn = DriverManager.getConnection(url,user,password);//参数顺序不要混淆
prep.setDate(3, new Date(date.getTime()));//Date类型为sql包下的
​

Statement是把整个的sql语句解析，sql注入可以在where处把本来and关系变为or关系。

而PreparedStatement预编译后，要填的只有占位符的位置，不会混淆

除了解决Statement的拼串、sql问题之外，PreparedStatement还有哪些好处呢？

1.PreparedStatement操作Blob的数据，而Statement做不到。

2.PreparedStatement可以实现更高效的批量操作。

3.3.5 使用PreparedStatement实现查询操作

query：查询

    // 通用的针对于不同表的查询:返回一个对象 (version 1.0)
public <T> T getInstance(Class<T> clazz, String sql, Object... args) {
​
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 1.获取数据库连接
conn = JDBCUtils.getConnection();
​
// 2.预编译sql语句，得到PreparedStatement对象
ps = conn.prepareStatement(sql);
​
// 3.填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
​
// 4.执行executeQuery(),得到结果集：ResultSet
rs = ps.executeQuery();
​
// 5.得到结果集的元数据：ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
​
// 6.1通过ResultSetMetaData得到columnCount,columnLabel；通过ResultSet得到列值
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {// 遍历每一个列
​
// 获取列值
Object columnVal = rs.getObject(i + 1);
// 获取列的别名:列的别名，使用类的属性名充当
String columnLabel = rsmd.getColumnLabel(i + 1);
// 6.2使用反射，给对象的相应属性赋值
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnVal);
​
}
​
return t;
​
}
} catch (Exception e) {
​
e.printStackTrace();
} finally {
// 7.关闭资源
JDBCUtils.closeResource(conn, ps, rs);
}
​
return null;
​
}

说明：使用PreparedStatement实现的查询操作可以替换Statement实现的查询操作，解决Statement拼串和SQL注入问题。

3.4 ResultSet与ResultSetMetaData

3.4.1 ResultSet

3.4.2 ResultSetMetaData

元数据是指对值修饰的属性

问题1：得到结果集后, 如何知道该结果集中有哪些列 ？ 列名是什么？

需要使用一个描述 ResultSet 的对象， 即 ResultSetMetaData

问题2：关于ResultSetMetaData

  1. 如何获取 ResultSetMetaData： 调用 ResultSet 的 getMetaData() 方法即可

  2. 获取 ResultSet 中有多少列：调用 ResultSetMetaData 的 getColumnCount() 方法

  3. 获取 ResultSet 每一列的列的别名是什么：调用 ResultSetMetaData 的getColumnLabel() 方法

查询图解：

JDBC核心技术

return ps.execute()：

如果执行的是查询操作，有返回结果，则方法返回true；

如果执行的是增，删，改操作，没有返回结果，则方法返回false；

return ps.executeUpdate();

如果返回int型的row数，则增/删/改了row条；大于0则成功，否则失败

如果返回0，则没有执行成功，或没进入操作；

3.5 资源的释放

3.6 JDBC API小结

第4章 操作BLOB类型字段

4.1 MySQL BLOB类型

JDBC核心技术

if("a".equalsIgnoreCase(selection)); //"a"放前面避免空指针异常，最多返回false。

4.2 向数据表中插入大数据类型

//获取连接
Connection conn = JDBCUtils.getConnection();
String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
​
// 填充占位符
ps.setString(1, "徐海强");
ps.setString(2, "xhq@126.com");
ps.setDate(3, new Date(new java.util.Date().getTime()));
// 操作Blob类型的变量
FileInputStream fis = new FileInputStream("xhq.png");
ps.setBlob(4, fis);
//执行
ps.execute();
fis.close();
JDBCUtils.closeResource(conn, ps);
​

4.3 修改数据表中的Blob类型字段

Connection conn = JDBCUtils.getConnection();
String sql = "update customers set photo = ? where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
​
// 填充占位符
// 操作Blob类型的变量
FileInputStream fis = new FileInputStream("coffee.png");
ps.setBlob(1, fis);
ps.setInt(2, 25);
​
ps.execute();
​
fis.close();
JDBCUtils.closeResource(conn, ps);

4.4 从数据表中读取大数据类型

String sql = "SELECT id, name, email, birth, photo FROM customer WHERE id = ?";
conn = getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, 8);
rs = ps.executeQuery();
if(rs.next()){
Integer id = rs.getInt(1);
 String name = rs.getString(2);
String email = rs.getString(3);
 Date birth = rs.getDate(4);
Customer cust = new Customer(id, name, email, birth);
 System.out.println(cust);
 //读取Blob类型的字段
Blob photo = rs.getBlob(5);
InputStream is = photo.getBinaryStream();
OutputStream os = new FileOutputStream("c.jpg");
byte [] buffer = new byte[1024];
int len = 0;
while((len = is.read(buffer)) != -1){
os.write(buffer, 0, len);
}
 JDBCUtils.closeResource(conn, ps, rs);
if(is != null){
is.close();
}
if(os != null){
os.close();
}
 
}
​

第5章 批量插入

5.1 批量执行SQL语句

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

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

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

5.2 高效的批量插入

Batch：一批

举例：向数据表中插入20000条数据

CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);

5.2.1 实现层次一：使用Statement

Connection conn = JDBCUtils.getConnection();
Statement st = conn.createStatement();
for(int i = 1;i <= 20000;i++){
String sql = "insert into goods(name) values('name_' + "+ i +")";
st.executeUpdate(sql);
}

5.2.2 实现层次二：使用PreparedStatement

long start = System.currentTimeMillis();
Connection conn = JDBCUtils.getConnection();
String sql = "insert into goods(name)values(?)";
PreparedStatement ps = conn.prepareStatement(sql);
for(int i = 1;i <= 20000;i++){
ps.setString(1, "name_" + i);
ps.executeUpdate();
}
long end = System.currentTimeMillis();
System.out.println("花费的时间为：" + (end - start));//82340
JDBCUtils.closeResource(conn, ps);

5.2.3 实现层次三

/*
* 修改1： 使用 addBatch() / executeBatch() / clearBatch()
* 修改2：mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。
*       ?rewriteBatchedStatements=true 写在配置文件的url后面
* 修改3：使用更新的mysql 驱动：mysql-connector-java-5.1.37-bin.jar
*
*/
@Test
public void testInsert1() throws Exception{
long start = System.currentTimeMillis();
Connection conn = JDBCUtils.getConnection();
String sql = "insert into goods(name)values(?)";
PreparedStatement ps = conn.prepareStatement(sql);
for(int i = 1;i <= 1000000;i++){
ps.setString(1, "name_" + i);
//1.“攒”sql
ps.addBatch();
if(i % 500 == 0){
//2.执行
ps.executeBatch();
//3.清空
ps.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println("花费的时间为：" + (end - start));//20000条：625                                     //1000000条:14733 
JDBCUtils.closeResource(conn, ps);
}

5.2.4 实现层次四

/*
* 层次四：在层次三的基础上操作
* 使用Connection 的 setAutoCommit(false)  /  commit()
*/
@Test
public void testInsert2() throws Exception{
long start = System.currentTimeMillis();
Connection conn = JDBCUtils.getConnection();
//1.设置为不自动提交数据
conn.setAutoCommit(false);
String sql = "insert into goods(name)values(?)";
PreparedStatement ps = conn.prepareStatement(sql);
for(int i = 1;i <= 1000000;i++){
ps.setString(1, "name_" + i);
//1.“攒”sql
ps.addBatch();
if(i % 500 == 0){
//2.执行
ps.executeBatch();
//3.清空
ps.clearBatch();
}
}
//2.提交数据
conn.commit();
long end = System.currentTimeMillis();
System.out.println("花费的时间为：" + (end - start));//1000000条:4978
JDBCUtils.closeResource(conn, ps);
}

5.2.5 什么时候用throws，什么时候用try/catch

JDBC核心技术

第6章： 数据库事务

6.1 数据库事务介绍

6.2 JDBC事务处理

【案例：用户AA向用户BB转账100】

public void testJDBCTransaction() {
Connection conn = null;
try {
// 1.获取数据库连接
conn = JDBCUtils.getConnection();
// 2.开启事务
conn.setAutoCommit(false);
// 3.进行数据库操作
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");
// 4.若没有异常，则提交事务
conn.commit();
} catch (Exception e) {
e.printStackTrace();
// 5.若有异常，则回滚事务
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
  } finally {
   try {
//6.恢复每次DML操作的自动提交功能
conn.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
}
   //7.关闭连接
JDBCUtils.closeResource(conn, null, null);
  } 
}
​

其中，对数据库操作的方法为：

//使用事务以后的通用的增删改操作（version 2.0）
public void update(Connection conn ,String sql, Object... args) {
PreparedStatement ps = null;
try {
// 1.获取PreparedStatement的实例 (或：预编译sql语句)
ps = conn.prepareStatement(sql);
// 2.填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
// 3.执行sql语句
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
// 4.关闭资源
JDBCUtils.closeResource(null, ps);
​
}
}

6.3 事务的ACID属性

  1. 原子性（Atomicity） 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

  2. 一致性（Consistency） 事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

  3. 隔离性（Isolation） 事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

  4. 持久性（Durability） 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

6.3.1 数据库的并发问题

6.3.2 四种隔离级别

6.3.3 在MySql中设置隔离级别

java类：

//获取数据库隔离级别
System.out.println(conn.getTransactionIsolation());
//设置数据库隔离级别
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

第7章：DAO及相关实现类

【BaseDAO.java】

抽象方法BaseDAO封装通用的数据库操作，其他具体的DAO继承这个抽象方法BaseDAO，然后增加针对其具体的表或其他的功能。

package com.atguigu.bookstore.dao;
​
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
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;
​
​
/**
* 定义一个用来被继承的对数据库进行基本操作的Dao
*
* @author HanYanBing
*
* @param <T>
*/
public abstract class BaseDao<T> {
private QueryRunner queryRunner = new QueryRunner();
// 定义一个变量来接收泛型的类型
private Class<T> type;
​
// 获取T的Class对象，获取泛型的类型，泛型是在被子类继承时才确定
public BaseDao() {
// 获取子类的类型
Class clazz = this.getClass();
// 获取父类的类型
// getGenericSuperclass()用来获取当前类的父类的类型
// ParameterizedType表示的是带泛型的类型
ParameterizedType parameterizedType = (ParameterizedType) clazz.getGenericSuperclass();
// 获取具体的泛型类型 getActualTypeArguments获取具体的泛型的类型
// 这个方法会返回一个Type的数组
Type[] types = parameterizedType.getActualTypeArguments();
// 获取具体的泛型的类型·
this.type = (Class<T>) types[0];
}
​
/**
* 通用的增删改操作
*
* @param sql
* @param params
* @return
*/
public int update(Connection conn,String sql, Object... params) {
int count = 0;
try {
count = queryRunner.update(conn, sql, params);
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
​
/**
* 获取一个对象
*
* @param sql
* @param params
* @return
*/
public T getBean(Connection conn,String sql, Object... params) {
T t = null;
try {
t = queryRunner.query(conn, sql, new BeanHandler<T>(type), params);
} catch (SQLException e) {
e.printStackTrace();
}
return t;
}
​
/**
* 获取所有对象
*
* @param sql
* @param params
* @return
*/
public List<T> getBeanList(Connection conn,String sql, Object... params) {
List<T> list = null;
try {
list = queryRunner.query(conn, sql, new BeanListHandler<T>(type), params);
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
​
/**
* 获取一个但一值得方法，专门用来执行像 select count(*)...这样的sql语句
*
* @param sql
* @param params
* @return
*/
public Object getValue(Connection conn,String sql, Object... params) {
Object count = null;
try {
// 调用queryRunner的query方法获取一个单一的值
count = queryRunner.query(conn, sql, new ScalarHandler<>(), params);
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
}

【BookDAO.java】

package com.atguigu.bookstore.dao;
​
import java.sql.Connection;
import java.util.List;
​
import com.atguigu.bookstore.beans.Book;
import com.atguigu.bookstore.beans.Page;
​
public interface BookDao {
​
/**
* 从数据库中查询出所有的记录
*
* @return
*/
List<Book> getBooks(Connection conn);
​
/**
* 向数据库中插入一条记录
*
* @param book
*/
void saveBook(Connection conn,Book book);
​
/**
* 从数据库中根据图书的id删除一条记录
*
* @param bookId
*/
void deleteBookById(Connection conn,String bookId);
​
/**
* 根据图书的id从数据库中查询出一条记录
*
* @param bookId
* @return
*/
Book getBookById(Connection conn,String bookId);
​
/**
* 根据图书的id从数据库中更新一条记录
*
* @param book
*/
void updateBook(Connection conn,Book book);
​
/**
* 获取带分页的图书信息
*
* @param page：是只包含了用户输入的pageNo属性的page对象
* @return 返回的Page对象是包含了所有属性的Page对象
*/
Page<Book> getPageBooks(Connection conn,Page<Book> page);
​
/**
* 获取带分页和价格范围的图书信息
*
* @param page：是只包含了用户输入的pageNo属性的page对象
* @return 返回的Page对象是包含了所有属性的Page对象
*/
Page<Book> getPageBooksByPrice(Connection conn,Page<Book> page, double minPrice, double maxPrice);
​
}

【UserDAO.java】

package com.atguigu.bookstore.dao;
​
import java.sql.Connection;
​
import com.atguigu.bookstore.beans.User;
​
public interface UserDao {
​
/**
* 根据User对象中的用户名和密码从数据库中获取一条记录
*
* @param user
* @return User 数据库中有记录 null 数据库中无此记录
*/
User getUser(Connection conn,User user);
​
/**
* 根据User对象中的用户名从数据库中获取一条记录
*
* @param user
* @return true 数据库中有记录 false 数据库中无此记录
*/
boolean checkUsername(Connection conn,User user);
​
/**
* 向数据库中插入User对象
*
* @param user
*/
void saveUser(Connection conn,User user);
}

【BookDaoImpl.java】

package com.atguigu.bookstore.dao.impl;
​
import java.sql.Connection;
import java.util.List;
​
import com.atguigu.bookstore.beans.Book;
import com.atguigu.bookstore.beans.Page;
import com.atguigu.bookstore.dao.BaseDao;
import com.atguigu.bookstore.dao.BookDao;
​
public class BookDaoImpl extends BaseDao<Book> implements BookDao {
​
@Override
public List<Book> getBooks(Connection conn) {
// 调用BaseDao中得到一个List的方法
List<Book> beanList = null;
// 写sql语句
String sql = "select id,title,author,price,sales,stock,img_path imgPath from books";
beanList = getBeanList(conn,sql);
return beanList;
}
​
@Override
public void saveBook(Connection conn,Book book) {
// 写sql语句
String sql = "insert into books(title,author,price,sales,stock,img_path) values(?,?,?,?,?,?)";
// 调用BaseDao中通用的增删改的方法
update(conn,sql, book.getTitle(), book.getAuthor(), book.getPrice(), book.getSales(), book.getStock(),book.getImgPath());
}
​
@Override
public void deleteBookById(Connection conn,String bookId) {
// 写sql语句
String sql = "DELETE FROM books WHERE id = ?";
// 调用BaseDao中通用增删改的方法
update(conn,sql, bookId);
}
​
@Override
public Book getBookById(Connection conn,String bookId) {
// 调用BaseDao中获取一个对象的方法
Book book = null;
// 写sql语句
String sql = "select id,title,author,price,sales,stock,img_path imgPath from books where id = ?";
book = getBean(conn,sql, bookId);
return book;
}
​
@Override
public void updateBook(Connection conn,Book book) {
// 写sql语句
String sql = "update books set title = ? , author = ? , price = ? , sales = ? , stock = ? where id = ?";
// 调用BaseDao中通用的增删改的方法
update(conn,sql, book.getTitle(), book.getAuthor(), book.getPrice(), book.getSales(), book.getStock(), book.getId());
}
​
@Override
public Page<Book> getPageBooks(Connection conn,Page<Book> page) {
// 获取数据库中图书的总记录数
String sql = "select count(*) from books";
// 调用BaseDao中获取一个单一值的方法
long totalRecord = (long) getValue(conn,sql);
// 将总记录数设置都page对象中
page.setTotalRecord((int) totalRecord);
​
// 获取当前页中的记录存放的List
String sql2 = "select id,title,author,price,sales,stock,img_path imgPath from books limit ?,?";
// 调用BaseDao中获取一个集合的方法
List<Book> beanList = getBeanList(conn,sql2, (page.getPageNo() - 1) * Page.PAGE_SIZE, Page.PAGE_SIZE);
// 将这个List设置到page对象中
page.setList(beanList);
return page;
}
​
@Override
public Page<Book> getPageBooksByPrice(Connection conn,Page<Book> page, double minPrice, double maxPrice) {
// 获取数据库中图书的总记录数
String sql = "select count(*) from books where price between ? and ?";
// 调用BaseDao中获取一个单一值的方法
long totalRecord = (long) getValue(conn,sql,minPrice,maxPrice);
// 将总记录数设置都page对象中
page.setTotalRecord((int) totalRecord);
​
// 获取当前页中的记录存放的List
String sql2 = "select id,title,author,price,sales,stock,img_path imgPath from books where price between ? and ? limit ?,?";
// 调用BaseDao中获取一个集合的方法
List<Book> beanList = getBeanList(conn,sql2, minPrice , maxPrice , (page.getPageNo() - 1) * Page.PAGE_SIZE, Page.PAGE_SIZE);
// 将这个List设置到page对象中
page.setList(beanList);
return page;
}
​
}

【UserDaoImpl.java】

package com.atguigu.bookstore.dao.impl;
​
import java.sql.Connection;
​
import com.atguigu.bookstore.beans.User;
import com.atguigu.bookstore.dao.BaseDao;
import com.atguigu.bookstore.dao.UserDao;
​
public class UserDaoImpl extends BaseDao<User> implements UserDao {
​
@Override
public User getUser(Connection conn,User user) {
// 调用BaseDao中获取一个对象的方法
User bean = null;
// 写sql语句
String sql = "select id,username,password,email from users where username = ? and password = ?";
bean = getBean(conn,sql, user.getUsername(), user.getPassword());
return bean;
}
​
@Override
public boolean checkUsername(Connection conn,User user) {
// 调用BaseDao中获取一个对象的方法
User bean = null;
// 写sql语句
String sql = "select id,username,password,email from users where username = ?";
bean = getBean(conn,sql, user.getUsername());
return bean != null;
}
​
@Override
public void saveUser(Connection conn,User user) {
//写sql语句
String sql = "insert into users(username,password,email) values(?,?,?)";
//调用BaseDao中通用的增删改的方法
update(conn,sql, user.getUsername(),user.getPassword(),user.getEmail());
}
​
}

【Book.java】

package com.atguigu.bookstore.beans;
/**
* 图书类
* @author songhongkang
*
*/
public class Book {
​
private Integer id;
private String title; // 书名
private String author; // 作者
private double price; // 价格
private Integer sales; // 销量
private Integer stock; // 库存
private String imgPath = "static/img/default.jpg"; // 封面图片的路径
//构造器，get()，set()，toString()方法略
}

【Page.java】

package com.atguigu.bookstore.beans;
​
import java.util.List;
/**
* 页码类
* @author songhongkang
*
*/
public class Page<T> {
​
private List<T> list; // 每页查到的记录存放的集合
public static final int PAGE_SIZE = 4; // 每页显示的记录数
private int pageNo; // 当前页
//  private int totalPageNo; // 总页数，通过计算得到
private int totalRecord; // 总记录数，通过查询数据库得到
​

【User.java】

package com.atguigu.bookstore.beans;
/**
* 用户类
* @author songhongkang
*
*/
public class User {
​
private Integer id;
private String username;
private String password;
private String email;
​

第8章：数据库连接池

8.1 JDBC数据库连接池的必要性

8.2 数据库连接池技术

JDBC核心技术

JDBC核心技术

8.3 多种开源的数据库连接池

8.3.1 C3P0数据库连接池

//使用C3P0数据库连接池的方式，获取数据库的连接：不推荐
public static Connection getConnection1() throws Exception{
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass("com.mysql.jdbc.Driver");
cpds.setJdbcUrl("jdbc:mysql://localhost:3306/test");
cpds.setUser("root");
cpds.setPassword("abc123");
//  cpds.setMaxPoolSize(100);
Connection conn = cpds.getConnection();
return conn;
}
//使用C3P0数据库连接池的配置文件方式，获取数据库的连接：推荐
private static DataSource cpds = new ComboPooledDataSource("helloc3p0");
public static Connection getConnection2() throws SQLException{
Connection conn = cpds.getConnection();
return conn;
}

其中，src下的配置文件为：【c3p0-config.xml】

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<named-config name="helloc3p0">
<!-- 获取连接的4个基本信息 -->
<property name="user">root</property>
<property name="password">abc123</property>
<property name="jdbcUrl">jdbc:mysql:///test</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<!-- 涉及到数据库连接池的管理的相关属性的设置 -->
<!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 -->
<property name="acquireIncrement">5</property>
<!-- 初始化数据库连接池时连接的数量 -->
<property name="initialPoolSize">5</property>
<!-- 数据库连接池中的最小的数据库连接数 -->
<property name="minPoolSize">5</property>
<!-- 数据库连接池中的最大的数据库连接数 -->
<property name="maxPoolSize">10</property>
<!-- C3P0 数据库连接池可以维护的 Statement 的个数 -->
<property name="maxStatements">20</property>
<!-- 每个连接同时可以使用的 Statement 对象的个数 -->
<property name="maxStatementsPerConnection">5</property>
​
</named-config>
</c3p0-config>

8.3.2 DBCP数据库连接池

属性 默认值 说明
initialSize 0 连接池启动时创建的初始化连接数量
maxActive 8 连接池中可同时连接的最大的连接数
maxIdle 8 连接池中最大的空闲的连接数，超过的空闲连接将被释放，如果设置为负数表示不限制
minIdle 0 连接池中最小的空闲的连接数，低于这个数量会被创建新的连接。该参数越接近maxIdle，性能越好，因为连接的创建和销毁，都是需要消耗资源的；但是不能太大。
maxWait 无限制 最大等待时间，当没有可用连接时，连接池等待连接释放的最大时间，超过该时间限制会抛出异常，如果设置-1表示无限等待
poolPreparedStatements false 开启池的Statement是否prepared
maxOpenPreparedStatements 无限制 开启池的prepared 后的同时最大连接数
minEvictableIdleTimeMillis 连接池中连接，在时间段内一直空闲， 被逐出连接池的时间
removeAbandonedTimeout 300 超过时间限制，回收没有用(废弃)的连接
removeAbandoned false 超过removeAbandonedTimeout时间后，是否进 行没用连接（废弃）的回收 
public static Connection getConnection3() throws Exception {
BasicDataSource source = new BasicDataSource();
source.setDriverClassName("com.mysql.jdbc.Driver");
source.setUrl("jdbc:mysql:///test");
source.setUsername("root");
source.setPassword("abc123");
//
source.setInitialSize(10);
Connection conn = source.getConnection();
return conn;
}
//使用dbcp数据库连接池的配置文件方式，获取数据库的连接：推荐
private static DataSource source = null;
static{
try {
Properties pros = new Properties();
InputStream is = DBCPTest.class.getClassLoader().getResourceAsStream("dbcp.properties");
pros.load(is);
//根据提供的BasicDataSourceFactory创建对应的DataSource对象
source = BasicDataSourceFactory.createDataSource(pros);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection4() throws Exception {
Connection conn = source.getConnection();
return conn;
}

其中，src下的配置文件为：【dbcp.properties】

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&useServerPrepStmts=false
username=root
password=abc123
​
initialSize=10
#...

8.3.3 Druid（德鲁伊）数据库连接池

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

package com.atguigu.druid;
​
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);
}
}
​

JDBCUtils类

/*
* 使用druid数据库连接池技术
* */
private static DataSource source = null;
static{
 try {
   //一次只创建一个池即可，不能放进方法里，不然每次调用方法就会创建一个池子
   Properties pros = new Properties();
   InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
   pros.load(is);
   source = DruidDataSourceFactory.createDataSource(pros);
  } catch (Exception e) {
   e.printStackTrace();
  }
}
public static Connection getConnection() throws SQLException {
​
 Connection conn = source.getConnection();
 return conn;
}

其中，src下的配置文件为：【druid.properties】

url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
username=root
password=123456
driverClassName=com.mysql.jdbc.Driver
​
initialSize=10
maxActive=20
maxWait=1000
filters=wall
配置 缺省 说明
name 配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分开来。 如果没有配置，将会生成一个名字，格式是：”DataSource-” + System.identityHashCode(this)
url 连接数据库的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，是组合关系，并非替换关系

第9章：Apache-DBUtils实现CRUD操作

9.1 Apache-DBUtils简介

JDBC核心技术

JDBC核心技术

9.2 主要API的使用

9.2.1 DbUtils

9.2.2 QueryRunner类

// 测试添加
@Test
public void testInsert() throws Exception {
QueryRunner runner = new QueryRunner();
Connection conn = JDBCUtils.getConnection3();
String sql = "insert into customers(name,email,birth)values(?,?,?)";
int count = runner.update(conn, sql, "何成飞", "he@qq.com", "1992-09-08");
​
System.out.println("添加了" + count + "条记录");
JDBCUtils.closeResource(conn, null);
​
}
// 测试删除
@Test
public void testDelete() throws Exception {
QueryRunner runner = new QueryRunner();
Connection conn = JDBCUtils.getConnection3();
String sql = "delete from customers where id < ?";
int count = runner.update(conn, sql,3);
​
System.out.println("删除了" + count + "条记录");
JDBCUtils.closeResource(conn, null);
​
}

9.2.3 ResultSetHandler接口及实现类

/*
* 测试查询:查询一条记录
*
* 使用ResultSetHandler的实现类：BeanHandler
*/
@Test
public void testQueryInstance() throws Exception{
QueryRunner runner = new QueryRunner();
​
Connection conn = JDBCUtils.getConnection3();
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);
JDBCUtils.closeResource(conn, null);
}
/*
* 测试查询:查询多条记录构成的集合
*
* 使用ResultSetHandler的实现类：BeanListHandler
*/
@Test
public void testQueryList() throws Exception{
QueryRunner runner = new QueryRunner();
​
Connection conn = JDBCUtils.getConnection3();
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);
JDBCUtils.closeResource(conn, null);
}
/*
* 自定义ResultSetHandler的实现类
*/
@Test
public void testQueryInstance1() throws Exception{
QueryRunner runner = new QueryRunner();
​
Connection conn = JDBCUtils.getConnection3();
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 {
System.out.println("handle");
//          return new Customer(1,"Tom","tom@126.com",new Date(123323432L));
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);
JDBCUtils.closeResource(conn, null);
}
/*
* 如何查询类似于最大的，最小的，平均的，总和，个数相关的数据，
* 使用ScalarHandler
*
*/
@Test
public void testQueryValue() throws Exception{
QueryRunner runner = new QueryRunner();
​
Connection conn = JDBCUtils.getConnection3();
//测试一：
//  String sql = "select count(*) from customers where id < ?";
//  ScalarHandler handler = new ScalarHandler();
//  long count = (long) runner.query(conn, sql, handler, 20);
//  System.out.println(count);
//测试二：
String sql = "select max(birth) from customers";
ScalarHandler handler = new ScalarHandler();
Date birth = (Date) runner.query(conn, sql, handler);
System.out.println(birth);
JDBCUtils.closeResource(conn, null);
}

JDBC总结

总结
@Test
public void testUpdateWithTx() {
Connection conn = null;
try {
//1.获取连接的操作（
//① 手写的连接：JDBCUtils.getConnection();
//② 使用数据库连接池：C3P0;DBCP;Druid
//2.对数据表进行一系列CRUD操作
//① 使用PreparedStatement实现通用的增删改、查询操作（version 1.0 \ version 2.0)
//version2.0的增删改public void update(Connection conn,String sql,Object ... args){}
//version2.0的查询 public <T> T getInstance(Connection conn,Class<T> clazz,String sql,Object ... args){}
//② 使用dbutils提供的jar包中提供的QueryRunner类
//提交数据
conn.commit();
} catch (Exception e) {
e.printStackTrace();
try {
//回滚数据
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
//3.关闭连接等操作
//① JDBCUtils.closeResource();
//② 使用dbutils提供的jar包中提供的DbUtils类提供了关闭的相关操作
}
}

■免责申明
⒈ 本站是纯粹个人学习网站，与朋友交流共赏，不存在任何商业目的。
⒉ 本站利用了部分网络资源，版权归原作者及网站所有，如果您对本站所载文章及作品版权的归属存有异议，请立即通知我们，我们将在第一时间予以删除，同时向你表示歉意！

标签 , ,

发表回复