JDBC

SUN公司为了简化、统一对数据库的操作，定义了一套Java操作数据库的规范，称之为JDBC。

JDBC（Java Database Connectivity）API，即Java数据库编程接口，是一组标准的Java语言中的接口和类，使用这些接口和类，Java客户端程序可以访问各种不同类型的数据库。比如建立数据库连接、执行SQL语句进行数据的存取操作。¹

原理

JDBC是接口，JDBC驱动才是接口的实现，JDBC驱动由每个数据库厂商开发。

JDBC协议

JDBC的URL＝协议名（总是jdbc）＋数据库子协议名＋数据源名（主机：端口/连接的数据库）。 常见数据库连接：

#mysql
url=jdbc:mysql://127.0.0.1:3306/j2ee?serverTimezone=UTC&generateSimpleParameterMetadata=true
driverClass=com.mysql.cj.jdbc.Driver

#mssql
url=jdbc:sqlserver://127.0.0.1:1433;DatabaseName=j2ee
driverClass=com.microsoft.sqlserver.jdbc.SQLServerDriver

#oracle
url=jdbc:oracle:thin:@//127.0.0.1:1521/orcl
driverClass=oracle.jdbc.driver.OracleDriver

Maven中央仓库没有Oracle驱动。

JDBC的URL中，可以不包含数据库名称，执行conn.setCatalog()可以切换数据库。²

连接数据库

// 注册驱动
Class.forName(driverClass);
// 获取连接
Connection conn = DriverManager.getConnection(url, user, password);

Statement

• 执行DDL、DML、DQL语句

  // 省略获取连接
  // 执行DDL、DML语句
  String sql = "CREATE DATABASE IF NOT EXISTS J2EE DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI";
  String sql = "CREATE TABLE IF NOT EXISTS student(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20),gender VARCHAR(2))";
  String sql = "INSERT INTO student(name,gender) VALUES('李四','女')";
  String sql = "UPDATE student SET name='" + name + "' WHERE id=" + id + "";
  String sql = "DELETE FROM student WHERE id=" + id + "";
    
  Statement stmt = conn.createStatement();
  int count = stmt.executeUpdate(sql);
    
  //------分隔------
    
  // 执行DQL语句
  String sql = "SELECT * FROM student";
  Statement stmt = conn.createStatement();
  ResultSet rs = stmt.executeQuery(sql);
  // 遍历ResultSet结果集，支持通过列名和列索引获取字段值
  while (rs.next()) {
      int id = rs.getInt("id");
      String name = rs.getString("name");
      String gender = rs.getString("gender");
      System.out.println(id + "," + name + "," + gender);
  }
  // 省略关闭连接
  

  此外，还可以使用Statement的execute()方法执行所有SQL语句，返回值为boolean类型，表示SQL语句是否有结果集。如果使用execute()方法执行的是更新语句，那么还要调用int getUpdateCount()来获取insert、update、delete语句所影响的行数。如果使用execute()方法执行的是查询语句，那么还要调用ResultSet getResultSet()来获取select语句的查询结果。

• 执行批处理

  // 批处理，只针对增删改，不能执行查询语句
  Statement stmt = conn.createStatement();
    
  String sql = "insert into student(name,gender) values('钱七','女')";
  // 把SQL语句加入到批命令中
  stmt.addBatch(sql);
    
  sql = "update student set name='周八' where id=3";
  //把SQL语句加入到批命令中
  stmt.addBatch(sql);
    
  // 执行批处理
  int[] ints = stmt.executeBatch();
  

• 获取自增长主键

  MySQL获取自增长主键

  Oracle获取自增长主键

• 大字段处理³

  Clob（长文本类型，MySQL不支持使用Text替代），和Blob（二进制类型）。

• 分页处理⁴

  -- pageNow：当前第几页；pageSize：每页显示的记录数 
  MySQL分页：
  select * from tableName limit (pageNow-1)*pagesize,pagesize;
    
  SQLServer分页：
  select top pageSize * from tableName where id not in (select top  pageSize * (pageNow - 1) id from tableName);
        
  Oracle分页：
  select t.* from (select rownum sn,te.* from tableName te where rownum <= pageSize * pageNow)t where t.sn > pageSize * (pageNow - 1);
  

JDBC无法同时执行多条语句，可以通过简单的;号或其他库解析出SQL脚本再执行，或者通过Mybatis的ScriptRunner类执行SQL文件。⁵

PreparedStatement

PreparedStatement可以使用预编译的SQL，而Statment只能使用静态的SQL；PreparedStatement可以使用SQL缓存区，效率比Statment高；PreparedStatement可以有效防止SQL注入，而Statment不能防止SQL注入。

• 执行DDL、DML、DQL语句

  // 准备预编译的sql，?表示一个参数的占位符
  String sql = "xxx where name=? and id=?";
    
  // 执行预编译sql语句（检查语法）
  pstmt = conn.prepareStatement(sql);
    
  // 设置参数值，参数位置，从1开始
  pstmt.setString(1, "赵六");
  pstmt.setInt(2, 9);
    
  // 执行DDL、DML语句
  int count = pstmt.executeUpdate();
  // 执行DQL语句
  ResultSet rs = pstmt.executeQuery();
  // 执行所有语句
  boolean hasResultSet = pstmt.execute(sql);
  

• 执行批处理

  // 准备预编译的sql，?表示一个参数的占位符
  String sql = "INSERT INTO student(name,gender) VALUES(?,?)";
    
  // 执行预编译sql语句（检查语法）
  pstmt = conn.prepareStatement(sql);
    
  // 设置参数值，参数位置，从1开始
  pstmt.setString(1, "王五");
  pstmt.setString(2, "男");
    
  // 添加批处理，不需要传入SQL
  pstmt.addBatch();
    
  // 设置参数值，参数位置，从1开始
  pstmt.setString(1, "赵六");
  pstmt.setString(2, "女");
    
  // 添加批处理，不需要传入SQL
  pstmt.addBatch();
    
  // 批量执行
  int[] ints = pstmt.executeBatch();
  

• 执行

CallableStatement

CallableStatement用于执行存储过程。

// 准备sql，第一个？是输入参数，第二个？是输出参数
String sql = "CALL pro_findById2(?,?)";

// 预编译
stmt = conn.prepareCall(sql);

// 设置输入参数
stmt.setInt(1, 2);

// 设置输出参数（注册输出参数），参数一：参数位置；参数二：存储过程中的输出参数的jdbc类型VARCHAR(20)
stmt.registerOutParameter(2, java.sql.Types.VARCHAR);

// 执行存储过程，结果不是返回到结果集中，而是返回到输出参数中
stmt.executeQuery();

// 得到输出参数的值，getXX方法专门用于获取存储过程中的输出参数，索引值为预编译sql中的输出参数的位置
String result = stmt.getString(2);

元数据

DatabaseMetaData，获取数据库基本信息、所有的数据库⁶、所有的表、存储过程、索引等；ResultSetMetaData，获取查询结果集列名等；ParameterMetaData，获取参数类型等。

事务

事务ACID，原子性，一致性，隔离性，持久性。

隔离引发的问题，脏读，不可重复读，幻读。

事务隔离级别，READ UNCOMMITTED（读未提交），READ COMMITTED（读已提交），REPEATABLE READ（可重复读），SERIALIZABLE（串行化）。

String sql_zs = "UPDATE account SET money=money-1000 WHERE accountName='张三';";
// 该SQL将产生错误
String sql_ls = "UPDATE1 account SET money=money+1000 WHERE accountName='李四';";

try {
    // 设置事务为手动提交，默认为开启的隐士事务
    conn.setAutoCommit(false);

    // 第一次执行SQL
    pstmt = conn.prepareStatement(sql_zs);
    pstmt.executeUpdate();

    // 第二次执行SQL
    pstmt = conn.prepareStatement(sql_ls);
    pstmt.executeUpdate();
} catch (Exception e) {
    // 出现异常，需要回滚事务
    conn.rollback();
} finally {
    // 所有的操作执行成功, 提交事务
    conn.commit();
}

MySQL默认事务隔离级别为：REPEATABLE READ；Oracle默认事务隔离级别为：READ COMMITTED。

MySQL需要设置数据库引擎为InnoDB，否则事务不起作用。

通常在Service层处理事务。

DbUtils组件

DbUtils组件是对JDBC的简单封装，主要方法包括update(Connection conn, String sql, Object param)、update(Connection conn, String sql, Object… param)、batch(Connection conn, String sql, Object[][] params)、query(Connection conn ,String sql, ResultSetHandler rsh, Object... params)等。

// 创建DbUtils核心工具类对象
QueryRunner qr = new QueryRunner();

String sql = "delete from admin where id=?";
qr.update(conn, sql, 2);

String sql = "insert into admin (userName, pwd) values(?,?)";
qr.batch(conn, sql, new Object[][]{ {"jack1", "888"}, {"jack2", "999"} });

/**
 * DBUtils提供多个ResultSetHandler查询结果集接口的实现
 * BeanHandler，查询返回单个对象
 * BeanListHandler，查询返回List对象
 * ArrayHandler，查询返回结果记录的第一行，封装为对象数组, 即返回：Object[]
 * ArrayListHandler，把查询的每一行都封装为对象数组，再添加到list集合中
 * MapListHandler，把查询的每一行都封装为map，再添加到list集合中
 * ScalarHandler，查询返回结果记录的第一行的第一列（在聚合函数统计的时候用）
 * MapHandler，查询返回结果的第一条记录封装为map
 */
String sql = "select * from admin";
List<Admin> list = qr.query(conn, sql, new BeanListHandler<Admin>(Admin.class));

连接池

一次数据库访问对应一个物理连接，每次操作数据库都要打开、关闭该物理连接，系统性能严重受损。

数据库连接池，在每次应用应用程序请求数据库连接时，无需重新打开连接，而是从池中取出已有的连接，使用完后，不再关闭，而是归还。

主流Java数据库连接池：DBCP、C3P0、Tomcat Jdbc Pool、Druid、HikariCP。⁷

JNDI（Java Naming and Directory Interface），Java命名和目录接口，通过在服务器上配置资源，然后通过统一的方式来获取配置的资源，Tomcat支持配置JNDI连接池。⁸

数据库操作方式

JDBC、JDBCTemplate（对JDBC的封装）、JPA（Java Persistence API， 即 Java 持久化 API，基于ORM规范）、Hibernate（ORM对象关系映射框架，JPA的一种实现）、Mybatis（半ORM框架）、Spring Data JPA（基于Hibernate）。⁹