JDBC学习笔记

JDBC概述

JDBC(Java Database Connectivity)是一个独立于特定数据库管理系统、通用的SQL数据库存取和操作的公共接口（一组API），定义了用来访问数据库的标准Java类库。不同的数据库厂商，需要针对这套接口，提供不同实现。不同实现的集合，即为不同数据库的驱动；java程序员则只需要面向这套接口编程即可。
JDBC API体现了面向接口编程和ORM（object relational mapping）思想，在使用上结合了元数据和反射的技术。
Snipaste_2022-05-19_14-58-21.png

ODBC(Open Database Connectivity，开放式数据库连接)是微软在Windows平台下推出的。使用者在程序中只需要调用ODBC API，由ODBC驱动程序将调用转换成为对特定的数据库的调用请求。设计思想同JDBC

数据库的连接

获取一个数据库连接需要导入JDBC驱动（Driver接口实现类）、提供JDBC URL、连接数据库的用户名和密码。

JDBC驱动_：_java.sql.Driver接口是所有JDBC驱动程序需要实现的接口，使用驱动程序前需注册驱动。

  1. 使用获取运行时类的方式加载驱动（即初始化Driver接口实现类）
  2. DriverManager类是驱动程序管理器类，负责管理驱动程序，可以显式调用该类的registerDriver()方法来注册驱动程序类的实例

JDBC URL：标识注册的驱动程序，驱动程序管理器通过URL选择正确的驱动程序，从而建立到数据库的连接。JDBC URL的标识由三部分组成，各部分间用冒号分隔，jdbc:子协议:子名称

  1. 协议：JDBC URL中的协议总是jdbc
  2. 子协议：子协议用于标识一个数据库驱动程序
  3. 子名称：数据库连接的信息，包括主机名、端口号、数据库名
  1. //显式调用第三方jar包，并使用硬编码方式传参
  2. @Test
  3. public void testmethod0() throws SQLException {
  4.     Driver driver = new com.mysql.cj.jdbc.Driver();
  5.     //Driver driver = new com.mysql.jdbc.Driver();适用MySQL5.7，操作会报方法deprecated
  6.     //mysql8.0的连接使用com.mysql.cj.jdbc.Driver,需加时区
  7.     String url = "jdbc:mysql://localhost:3306/testdb?serverTimezone=Asia/Shanghai";
  8.     Properties info = new Properties();
  9.     info.setProperty("user","root");
  10.     info.setProperty("password","mysql");
  11.     //创建连接
  12.     Connection connect = driver.connect(url, info);
  13.     System.out.println(connect);
  14.     //关闭连接
  15.     connect.close();
  16. }
  18. //显式调用第三方jar包，使用配置文件读取参数
  19. @Test
  20. public void testmethod1() throws Exception {
  21.     Driver driver = new com.mysql.cj.jdbc.Driver();
  22.     Properties info = new Properties();
  23.     FileInputStream fis = new FileInputStream("jdbc.properties");
  24.     //url中指明开启批处理
  25.     String url = "jdbc:mysql://localhost:3306/testdb?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
  26.     info.load(fis);
  27.     //创建连接
  28.     Connection connect = driver.connect(url, info);
  29.     System.out.println(connect);
  30.     //关闭连接
  31.     fis.close();
  32.     connect.close();
  33. }
  35. //使用DriverManager管理驱动，显式注册驱动
  36. @Test
  37. public void testmethod2() throws Exception {
  38.     Driver driver = new com.mysql.cj.jdbc.Driver();
  39.     FileInputStream fis = new FileInputStream("jdbc.properties");
  40.     Properties info = new Properties();
  41.     info.load(fis);
  42.     String url = info.getProperty("url");
  43.     //显式注册驱动
  44.     DriverManager.registerDriver(driver);
  45.     //创建连接
  46.     Connection connection1 = DriverManager.getConnection(url, info);
  47.     System.out.println(connection1);
  48.     //创建连接
  49.     String user = info.getProperty("user");
  50.     String password = info.getProperty("password");
  51.     Connection connection2 = DriverManager.getConnection(url, user, password);
  52.     System.out.println(connection2);
  53.     //关闭连接（略）
  54. }
  56. //使用DriverManager管理驱动，利用Driver自动为DriverManager注册驱动
  57. @Test
  58. public void testmethod3() throws Exception {
  59.     FileInputStream fis = new FileInputStream("jdbc.properties");
  60.     Properties info = new Properties();
  61.     info.load(fis);
  62.     String url = info.getProperty("url");
  63.     //触发类的加载,Driver自动为DriverManager注册驱动
  64.     Class.forName("com.mysql.cj.jdbc.Driver");
  65.     //创建连接
  66.     Connection connection = DriverManager.getConnection(url, info);
  67.     System.out.println(connection);
  68.     //关闭连接
  69.     connection.close();
  70. }
  72. //使用DriverManager管理驱动，利用Driver自动为DriverManager注册驱动
  73. //将驱动路径也写入配置文件
  74. @Test
  75. public void testmethod4() throws Exception {
  76.     //加载配置文件
  77.     FileInputStream fis = new FileInputStream("jdbc.properties");
  78.     Properties info = new Properties();
  79.     info.load(fis);
  80.     String url = info.getProperty("url");
  81.     String classname = info.getProperty("classname");
  82.     //触发类的加载,Driver自动为DriverManager注册驱动
  83.     Class.forName(classname);
  84.     //创建连接
  85.     Connection connection = DriverManager.getConnection(url, info);
  86.     System.out.println(connection);
  87.     //关闭连接
  88.     connection.close();
  89. }

JDBC的CRUD操作

【Statement和PreparedStatement】 Statement操作数据表存在的弊端：一是拼串操作繁琐；二是存在SQL注入问题 PreparedStatement接口是Statement的子接口，它表示一条预编译过的SQL语句，语句中的参数用?填充。预编译的特性能提高执行性能，且防止SQL注入

Java与SQL的数据类型存在对应关系，在SQL查询得到的结果集中按对应关系转换为Java的数据类型。
Snipaste_2022-05-19_16-17-04.png

ResultSet：查询需要调用PreparedStatement的executeQuery()方法，查询结果是一个ResultSet对象。对象以逻辑表格的形式封装了执行查询操作的结果集，ResultSet接口由数据库厂商提供实现。ResultSet实际上就是一张数据表，ResultSet对象维护了一个指向当前数据行的游标，游标初始位置在第一行之前，可调用next()方法检测是否存在下一行。若有效，该方法返回true且指针下移（相当于Iterator对象的hasNext()和next()方法的结合体） 注意：Java与数据库交互涉及到的相关Java API中的索引都从1开始

ResultSetMetaData：查询结果集中的元数据，可以获取查询结果集的列数、列名、列别名、类型等信息。主要方法如下，详见API文档 getColumnName(int column)：获取指定列的名称 getColumnLabel(int column)：获取指定列的别名 getColumnCount()：返回当前 ResultSet 对象中的列数 getColumnTypeName(int column)：检索指定列的数据库特定的类型名称

Snipaste_2022-05-19_16-44-46.png

资源释放：数据库连接使用完后需释放资源，包括ResultSet（结果集）、Statement（操作指令）、Connection（数据库连接）

DAO（Data Access Object）：一组访问数据信息的类和接口，包括了对数据的CRUD操作（Create、Retrieve、Update、 Delete），而不包含任何业务相关的信息。 作用：实现功能的模块化，更有利于代码的维护和升级

  1. public class JDBCutils {
  2.     //静态变量
  3.     private static Properties info = new Properties();
  4.     private static String url;
  5.     //仅在有连接指明使用数据库连接池技术才给dataSource赋值
  6.     private static DataSource dataSource;
  8.     //利用静态代码块初始化类
  9.     static {
  10.         FileInputStream fis = null;
  11.         try {
  12.             //读取配置文件
  13.             fis = new FileInputStream("jdbc.properties");
  14.             //在类中声明的info变量必须赋值，否则无法调用该方法
  15.             info.load(fis);
  16.             url = info.getProperty("url");
  17.             String classname = info.getProperty("classname");
  18.             //加载驱动
  19.             Class.forName(classname);
  20.         } catch (IOException e) {
  21.             e.printStackTrace();
  22.         } catch (ClassNotFoundException e) {
  23.             e.printStackTrace();
  24.         } finally {
  25.             try {
  26.                 if (fis != null) {
  27.                     fis.close();
  28.                 }
  29.             } catch (IOException e) {
  30.                 e.printStackTrace();
  31.             }
  32.         }
  33.     }
  36.     //返回数据库连接
  37.     public static Connection getConnection() throws SQLException {
  38.         return DriverManager.getConnection(url, info);
  39.     }
  41.     //返回具有事务特性的数据库连接
  42.     public static Connection getTraConnection() throws SQLException {
  43.         Connection conn = DriverManager.getConnection(url, info);
  44.         conn.setAutoCommit(false);
  45.         return conn;
  46.     }
  48.     //使用druid连接池，返回连接
  49.     public static Connection getDruidConnection(String propertiesfile) throws Exception {
  50. //        DataSource dataSource = DruidDataSourceFactory.createDataSource(pro);
  51.         if (dataSource == null) {
  52.             synchronized (JDBCutils.class) {
  53.                 if (dataSource == null) {
  54.                     Properties properties = new Properties();
  55.                     properties.load(new FileInputStream(propertiesfile));
  56.                     dataSource = DruidDataSourceFactory.createDataSource(properties);
  57.                 }
  58.             }
  59.         }
  60.         Connection connection = dataSource.getConnection();
  61.         return connection;
  62.     }
  64.     //销毁连接池
  65.     public static void DataSourceDestroy() throws Exception {
  66.         if (dataSource != null) {
  67.             Class clazz = dataSource.getClass();
  68.             Method close = clazz.getDeclaredMethod("close");
  69.             close.invoke(dataSource);
  70.         }
  71.     }
  73.     //关闭资源--方法：Connection conn, Statement ps
  74.     public static void close(Connection conn, Statement ps) {
  75.         if (conn != null) {
  76.             try {
  77.                 conn.setAutoCommit(true);
  78.                 conn.close();
  79.             } catch (SQLException e) {
  80.                 e.printStackTrace();
  81.             }
  82.         }
  84.         if (ps != null) {
  85.             try {
  86.                 ps.close();
  87.             } catch (SQLException e) {
  88.                 e.printStackTrace();
  89.             }
  90.         }
  91.     }
  93.     //关闭资源--重载方法：Connection conn, Statement ps, ResultSet rs
  94.     public static void close(Connection conn, Statement ps, ResultSet rs) {
  95.         close(conn, ps);
  96.         if (rs != null) {
  97.             try {
  98.                 rs.close();
  99.             } catch (SQLException e) {
  100.                 e.printStackTrace();
  101.             }
  102.         }
  103.     }
  105.     //查询时对获取的ResultSet进行解析,利用metadata和反射
  106.     //解析rs中的数据返回首条记录对应的对象
  107.     //泛型方法的泛型由传入的参数决定，所以必须在传入的参数中给出类型
  108.     public static <T> T parseone(Class<T> clazz, ResultSet rs) throws Exception {
  109.         ResultSetMetaData rsmd = rs.getMetaData();//元数据
  110.         int columnCount = rsmd.getColumnCount();//获取列数
  111.         T t = clazz.newInstance();//获取运行时类对象的实例
  113.         if (rs.next()) {
  114.             for (int i = 1; i <= columnCount; i++) {
  115.                 //获取对应的属性名和属性值
  116.                 String columnLabel = rsmd.getColumnLabel(i);
  117.                 Object object = rs.getObject(columnLabel);
  118.                 //利用反射将属性赋值
  119.                 Field field = clazz.getDeclaredField(columnLabel);
  120.                 field.setAccessible(true);
  121.                 field.set(t, object);
  122.             }
  123.         }
  124.         return t;
  125.     }
  127.     //解析rs中的数据返回多条记录对应的对象
  128.     //泛型方法的泛型由传入的参数决定，所以必须在传入的参数中给出类型
  129.     public static <T> ArrayList<T> parsemany(Class<T> clazz, ResultSet rs) throws Exception {
  130.         ResultSetMetaData rsmd = rs.getMetaData();
  131.         int columnCount = rsmd.getColumnCount();//获取列数
  132.         ArrayList<T> ts = new ArrayList<>();
  133.         T t = clazz.newInstance();//获取运行时类对象的实例
  135.         while (rs.next()) {
  136.             for (int i = 1; i <= columnCount; i++) {
  137.                 //获取对应的属性名和属性值
  138.                 String columnLabel = rsmd.getColumnLabel(i);
  139.                 Object object = rs.getObject(columnLabel);
  140.                 //利用反射将属性赋值
  141.                 Field field = clazz.getDeclaredField(columnLabel);
  142.                 field.setAccessible(true);
  143.                 field.set(t, object);
  144.             }
  145.             ts.add(t);
  146.         }
  147.         return ts;
  148.     }
  149. }

JDBC的其它操作

批量操作：将SQL语句批量提交给数据库处理的效率通常比单独提交处理的效率高

  1. addBatch(String)：添加需要批量处理的SQL语句
  2. executeBatch()：执行批量处理语句
  3. clearBatch()：清空缓存的数据
  1. Connection conn = JDBCUtils.getConnection();
  2. String sql = "insert into goods(name)values(?)";
  3. PreparedStatement ps = conn.prepareStatement(sql);
  4. for(int i = 1;i <= 1000000;i++){
  5.     ps.setString(1, "name_" + i);
  6.     //1.添加sql语句，但不提交执行
  7.     ps.addBatch();
  8.     if(i % 500 == 0){
  9.         //2.每达到500条语句提交批量执行
  10.         ps.executeBatch();
  11.         //3.清空
  12.         ps.clearBatch();
  13.     }
  14. }

事务：JDBC支持对事务进行操作

  1. 调用Connection对象的setAutoCommit(false)，取消自动提交事务
  2. 在所有的SQL语句都成功执行后，调用commit()提交事务
  3. 方法提交事务在出现异常时，调用rollback()回滚事务
  1. Connection conn = null;
  2. try {
  3.     // 1.获取数据库连接
  4.     conn = JDBCUtils.getConnection();
  5.     // 2.开启事务
  6.     conn.setAutoCommit(false);
  7.     // 3.进行数据库操作
  8.     String sql1 = "UPDATE person set account=account-500 WHERE NAME=?";
  9.     PreparedStatement ps1 = conn.prepareStatement(sql1);
  10.     ps1.setString(1,"xiaoming");
  11.     ps1.executeUpdate();
  12.     // 模拟网络异常
  13.     System.out.println(10 / 0);
  15.     String sql2 = "UPDATE person set account=account+500 WHERE NAME=?";
  16.     PreparedStatement ps2 = conn.prepareStatement(sql2);
  17.     ps2.setString(1,"xiaohong");
  18.     ps2.executeUpdate();
  19.     // 4.若没有异常，则提交事务
  20.     conn.commit();
  21. } catch (Exception e) {
  22.     e.printStackTrace();
  23.     // 5.若有异常，则回滚事务
  24.     try {
  25.         conn.rollback();
  26.     } catch (SQLException e1) {
  27.         e1.printStackTrace();
  28.     }
  29. } finally {
  30.     try {
  31.         //6.恢复每次DML操作的自动提交功能
  32.         conn.setAutoCommit(true);
  33.     } catch (SQLException e) {
  34.         e.printStackTrace();
  35.     }
  36.     //7.关闭连接
  37.     JDBCutils.close(conn,ps1);
  38.     JDBCutils.close(null,ps2);
  39. }

数据库连接池

为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕后再放回去。 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。

数据库连接池技术的优点：实现资源重用，避免数据库连接初始化和释放过程的时间开销，从而减少响应时间

  1. //使用druid连接池，返回连接
  2. public static Connection getDruidConnection(String propertiesfile) throws Exception {
  3.     if (dataSource == null) {
  4.         synchronized (JDBCutils.class) {
  5.             if (dataSource == null) {
  6.                 Properties properties = new Properties();
  7.                 properties.load(new FileInputStream(propertiesfile));
  8.                 dataSource = DruidDataSourceFactory.createDataSource(properties);
  9.             }
  10.         }
  11.     }
  12.     Connection connection = dataSource.getConnection();
  13.     return connection;
  14. }

Apache-DBUtils实现CRUD操作

commons-dbutils是Apache组织提供的一个开源JDBC工具类库，它是对JDBC的简单封装。

  1. DbUtils：提供如关闭连接、装载JDBC驱动程序等常规工作的工具类
  2. QueryRunner：简化SQL查询，与ResultSetHandler组合在一起使用完成大部分的数据库操作
  3. ResultSetHandler：用于处理 java.sql.ResultSet，将数据按要求转换为另一种形式
  1. @Test
  2. public void test1() throws Exception {
  3.     //获取连接
  4.     Connection druidConnection = JDBCutils.getDruidConnection("druid.properties");
  5.     //构造SQL模板
  6.     String sql1 = "UPDATE person SET account=account-200 WHERE account > 3000";
  7.     String sql2 = "INSERT person(id,name,account) VALUES(?,?,?)";
  8.     String sql3 = "DELETE FROM person WHERE id=?";
  9.     //执行变更
  10.     QueryRunner queryRunner = new QueryRunner();
  11.     //        int i = queryRunner.update(druidConnection, sql1);
  12.     //        System.out.println("变更了" + i + "条记录");
  13.     int i = queryRunner.update(druidConnection, sql2,6,"xiaotang",3300);
  14.     System.out.println("变更了" + i + "条记录");
  15.     //        int i = queryRunner.update(druidConnection, sql3,6);
  16.     //        System.out.println("变更了" + i + "条记录");
  18.     //关闭资源
  19.     DbUtils.close(druidConnection);
  20. }
  23. @Test
  24. public void test2() throws Exception {
  25.     //获取连接
  26.     Connection druidConnection = JDBCutils.getDruidConnection("druid.properties");
  27.     //构造SQL模板
  28.     String sql = "SELECT * FROM person WHERE account > ? AND account < ?";
  29.     //使用resultsethandler解析查询的数据
  30.     BeanHandler<Person> personBeanHandler = new BeanHandler<Person>(Person.class);
  31.     BeanListHandler<Person> personBeanListHandler = new BeanListHandler<>(Person.class);
  32.     //执行查询
  33.     QueryRunner queryRunner = new QueryRunner();
  34.     //接收首个对象
  35.     Person oneperson = queryRunner.query(druidConnection, sql, personBeanHandler, 2000, 3800);
  36.     System.out.println(oneperson);
  37.     //接收对象的列表
  38.     List<Person> manypeople = queryRunner.query(druidConnection, sql, personBeanListHandler, 2000, 3800);
  39.     manypeople.forEach(System.out::println);
  40.     //关闭资源
  41.     DbUtils.close(druidConnection);