title: 【学习之路】JDBC学习
draft: true
tags:

  • 学习之路
  • JavaEE
  • JDBC
    categories:
  • JavaEE
  • JDBC
    description: ‘关于JDBC技术的学习.如何使用IDEA加载驱动类,使用JDBC操作数据库,SQL注入问题和数据库事务处理,学习Druid数据库连接池’
    abbrlink: 53289
    date: 2020-10-13 16:24:22
    cover: https://cdn.jsdelivr.net/gh/CodeZixuan/Blog_Images/img/封面.jpg

java中的数据存储技术

  • 在java中，数据库存取技术可分为以下几类

    • JDBC直接访问数据
    • JDO (Java Data Object)技术
    • 第三方O/R工具，如Hibernate，Mybatis
  • JDBC是java访问数据库的基石JDO、Hibernate、MyBatis只是更好的封装了JDBC

JDBC介绍

  • 如果没有JDBC，那么java程序访问数据库应该是这样的

JDBC - 图1

  • 有了JDBC，java程序访问数据库时是这样的

JDBC - 图2

  • 总结如下

JDBC - 图3

Driver接口实现类

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

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

    • Oracle的驱动：oracle.jdbc.driver.OracleDriver
    • mySql的驱动： com.mysql.jdbc.Driver

用IDEA导入JDBC驱动

点击File选择Project Structure

JDBC - 图4

点击Modules选择当前工程再点击+符号添加JARs包

JDBC - 图5

选择JDBC连接池的驱动

JDBC - 图6

点击方框应用驱动

JDBC - 图7

注意：如果是Dynamic Web Project（动态的web项目）话，则是把驱动jar放到WebContent（有的开发工具叫WebRoot）目录中的WEB-INF目录中的lib目录下

加载与注册JDBC驱动

  • 加载驱动：加载 JDBC 驱动需调用 Class 类的静态方法 forName()，向其传递要加载的 JDBC 驱动的类名

    • Class.forName(“com.mysql.jdbc.Driver”);

  • 注册驱动：DriverManager 类是驱动程序管理器类，负责管理驱动程序

    • 使用DriverManager.registerDriver(com.mysql.jdbc.Driver)来注册驱动

    • 通常不用显式调用 DriverManager 类的 registerDriver() 方法来注册驱动程序类的实例，因为 Driver 接口的驱动程序类都包含了静态代码块，在这个静态代码块中，会调用 DriverManager.registerDriver() 方法来注册自身的一个实例。下图是MySQL的Driver实现类的源码：

JDBC - 图8

JDBC URL

  • JDBC URL 用于标识一个被注册的驱动程序，驱动程序管理器通过这个 URL 选择正确的驱动程序，从而建立到数据库的连接。

  • JDBC URL的标准由三部分组成，各部分间用冒号分隔。

    • jdbc:子协议:子名称
    • 协议：JDBC URL中的协议总是jdbc
    • 子协议：子协议用于标识一个数据库驱动程序
    • 子名称：一种标识数据库的方法。子名称可以依不同的子协议而变化，用子名称的目的是为了定位数据库提供足够的信息。包含主机名(对应服务端的ip地址)，端口号，数据库名
  • 举例：

JDBC - 图9

  • 几种常用数据库的 JDBC URL

  • MySQL的连接URL编写方式：

    • jdbc:mysql://主机名称:mysql服务端口号/数据库名称?参数=值&参数=值

      • jdbc:mysql://localhost:3306/atguigu
      • jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=utf8（如果JDBC程序与服务器端的字符集不一致，会导致乱码，那么可以通过参数指定服务器端的字符集）
      • jdbc:mysql://localhost:3306/atguigu?user=root&password=123456

  • Oracle 9i的连接URL编写方式：

    • jdbc:oracle:thin:@主机名称:oracle服务端口号:数据库名称

      • jdbc:oracle:thin:@localhost:1521:atguigu

  • SQLServer的连接URL编写方式：

    • jdbc:sqlserver://主机名称:sqlserver服务端口号:DatabaseName=数据库名称

    • jdbc:sqlserver://localhost:1433:DatabaseName=atguigu

用户名和密码

  • user,password可以用“属性名=属性值”方式告诉数据库
  • 可以调用 DriverManager 类的 getConnection() 方法建立到数据库的连接

数据库连接方式

连接方式一

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

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

连接方式二

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

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

连接方式三

  1.     @Test
  2.     public void testConnection3() {
  3.         try {
  4.             //1.数据库连接的4个基本要素：
  5.             String url = "jdbc:mysql://localhost:3306/test";
  6.             String user = "root";
  7.             String password = "123";
  8.             String driverName = "com.mysql.jdbc.Driver";
  10.             //2.实例化Driver
  11.             Class clazz = Class.forName(driverName);
  12.             Driver driver = (Driver) clazz.newInstance();
  13.             //3.注册驱动
  14.             DriverManager.registerDriver(driver);
  15.             //4.获取连接
  16.             Connection conn = DriverManager.getConnection(url, user, password);
  17.             System.out.println(conn);
  18.         } catch (Exception e) {
  19.             e.printStackTrace();
  20.         }
  22.     }

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

连接方式四

  1.     @Test
  2.     public void testConnection4() {
  3.         try {
  4.             //1.数据库连接的4个基本要素：
  5.             String url = "jdbc:mysql://localhost:3306/test";
  6.             String user = "root";
  7.             String password = "123";
  8.             String driverName = "com.mysql.jdbc.Driver";
  10.             //2.加载驱动 （①实例化Driver ②注册驱动）
  11.             Class.forName(driverName);
  13.             //Driver driver = (Driver) clazz.newInstance();
  14.             //3.注册驱动
  15.             //DriverManager.registerDriver(driver);
  16.             /*
  17.             可以注释掉上述代码的原因，是因为在mysql的Driver类中声明有：
  18.             static {
  19.                 try {
  20.                     DriverManager.registerDriver(new Driver());
  21.                 } catch (SQLException var1) {
  22.                     throw new RuntimeException("Can't register driver!");
  23.                 }
  24.             }
  25.              */
  27.             //3.获取连接
  28.             Connection conn = DriverManager.getConnection(url, user, password);
  29.             System.out.println(conn);
  30.         } catch (Exception e) {
  31.             e.printStackTrace();
  32.         }
  34.     }

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

连接方式五(最终版)

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

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

  1. user=root
  2. password=123
  3. url=jdbc:mysql://localhost:3306/test
  4. driverClass=com.mysql.jdbc.Driver

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

使用配置文件的好处：

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

使用PreparedStatement实现CRUD操作

操作和访问数据库

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

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

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

JDBC - 图10

使用Statement操作数据库

创建User类

  • 定义user和password两个属性
  • 写get、set、构造方法和重写toString方法
  1. public class User {
  2.     private String user;
  3.     private String password;
  5.     public User(String user, String password) {
  6.         super();
  7.         this.user = user;
  8.         this.password = password;
  9.     }
  11.     public User() {
  12.     }
  14.     public String getUser() {
  15.         return user;
  16.     }
  18.     public void setUser(String user) {
  19.         this.user = user;
  20.     }
  22.     public String getPassword() {
  23.         return password;
  24.     }
  26.     public void setPassword(String password) {
  27.         this.password = password;
  28.     }
  30.     @Override
  31.     public String toString() {
  32.         return "User{" +
  33.                 "user='" + user + '\'' +
  34.                 ", password='" + password + '\'' +
  35.                 '}';
  36.     }
  37. }

创建StatementTest测试类

  1. public class StatementTest<T> {
  3.     public static void main(String[] args) {
  4.         StatementTest st = new StatementTest();
  5.         st.testLogin();
  6.     }
  7.     // 使用Statement的弊端：需要拼写sql语句，并且存在SQL注入的问题
  8.     public void testLogin() {
  9.         Scanner scan = new Scanner(System.in);
  11.         System.out.print("用户名：");
  12.         String userName = scan.nextLine();
  13.         System.out.print("密码：");
  14.         String password = scan.nextLine();
  16.         /*
  17.           当userName输入：1' or
  18.           password输入：='1' or '1' = '1
  19.           就会产生SQL注入问题
  20.          */
  21.         String sql = "SELECT user,password FROM user_table WHERE user = '" + userName + "' AND password = '" + password
  22.                 + "'";
  23.         User user = get(sql, User.class);
  24.         if (user != null) {
  25.             System.out.println("登陆成功!");
  26.         } else {
  27.             System.out.println("用户名或密码错误！");
  28.         }
  29.     }
  31.     // 使用Statement实现对数据表的查询操作
  32.     public <T> T get(String sql, Class<T> clazz) {
  33.         T t = null;
  35.         Connection conn = null;
  36.         Statement st = null;
  37.         ResultSet rs = null;
  38.         try {
  39.             // 1.加载配置文件
  40.             InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
  41.             Properties pros = new Properties();
  42.             pros.load(is);
  44.             // 2.读取配置信息
  45.             String user = pros.getProperty("user");
  46.             String password = pros.getProperty("password");
  47.             String url = pros.getProperty("url");
  48.             String driverClass = pros.getProperty("driverClass");
  50.             // 3.加载驱动
  51.             Class.forName(driverClass);
  52.             // 4.获取连接
  53.             conn = DriverManager.getConnection(url, user, password);
  54.             st = conn.createStatement();
  55.             rs = st.executeQuery(sql);
  56.             // 获取结果集的元数据
  57.             ResultSetMetaData rsmd = rs.getMetaData();
  59.             // 获取结果集的列数
  60.             int columnCount = rsmd.getColumnCount();
  62.             if (rs.next()) {
  63.                 t = clazz.newInstance();
  64.                 for (int i = 0; i < columnCount; i++) {
  65.                     // 1. 获取列的别名
  66.                     String columnName = rsmd.getColumnLabel(i + 1);
  67.                     // 2. 根据列名获取对应数据表中的数据
  68.                     Object columnVal = rs.getObject(columnName);
  69.                     // 3. 将数据表中得到的数据，封装进对象
  70.                     Field field = clazz.getDeclaredField(columnName);
  71.                     field.setAccessible(true);
  72.                     field.set(t, columnVal);
  73.                 }
  74.                 return t;
  75.             }
  76.         } catch (Exception e) {
  77.             e.printStackTrace();
  78.         } finally {
  79.             // 关闭资源
  80.             if (rs != null) {
  81.                 try {
  82.                     rs.close();
  83.                 } catch (SQLException e) {
  84.                     e.printStackTrace();
  85.                 }
  86.             }
  87.             if (st != null) {
  88.                 try {
  89.                     st.close();
  90.                 } catch (SQLException e) {
  91.                     e.printStackTrace();
  92.                 }
  93.             }
  94.             if (conn != null) {
  95.                 try {
  96.                     conn.close();
  97.                 } catch (SQLException e) {
  98.                     e.printStackTrace();
  99.                 }
  100.             }
  101.         }
  102.         return null;
  103.     }
  104. }

使用Statement操作数据表的弊端

  • 通过Connection对象的createStatement方法创建对象。该对象用于执行静态的SQL语句，并返回执行结果

  • Statement接口中定义了下列方法用于执行SQL语句：

    • int excuteUpdate(String sql) 执行更新操作INSERT、UPDATE、DELETE
    • ResultSet executeQuery(String sql) 执行查询操作

  • 使用Statement操作数据表存在弊端：

    1. 存在拼串操作，比较繁琐
    2. 存在SQL注入问题
  • SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的 SQL 语句段或命令(如：SELECT user, password FROM user_table WHERE user=’a’ OR 1 = ‘ AND password = ‘ OR ‘1’ = ‘1’) ，从而利用系统的 SQL 引擎完成恶意行为的做法。
  • 对于Java而言，需要防范SQL注入，我们只需要使用PreparedStatement(从Statement扩展而来)取代Statement

JDBC - 图11

PreparedStatement的使用

  • 可以通过Connection对象的PreparedStatement(String sql)方法获取PreparedStatement对象
  • PreparedStatement节后时候Statement的子接口，表示一条预编译过的SQL语句

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

使用PreparedStatement实现增删改操作

  • PreparedStatementSQL语句使用 ? 充当占位符
  1. public void testInsert(){
  2.     Connection conn = null;
  3.     PreparedStatement ps = null;
  4.     //读取配置文件的4个配置信息
  5.     try{
  6.         InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
  7.         Properties pros = new Properties();
  8.         pros.load(is);
  10.         String user = pros.getProperty("user");
  11.         String password = pros.getProperty("password");
  12.         String url = pros.getProperty("url");
  13.         String driverClass = pros.getProperty("driverClass");
  15.         //加载驱动
  16.         Class.forName(driverClass);
  18.         //获取连接
  19.         Connection conn = DriverManager.getConnection(url, user, password);
  21.         //预编译SQL语句返回PreparedStatement实列
  22.         //PreparedStatement使用?作为占位符?所代表的是对应填入的数据
  23.         String sql = "INSERT INTO customers(`name`, email, birth)VALUES(?, ?, ?)";
  24.         PreparedStatement ps = conn.PrepareStatement(sql);
  25.         //第一个值代表数据所代表的索引,第二个所代表字段的值
  26.         ps.setString(1, "张三");
  27.         ps.setString(2, "zhangsan@163.com");
  28.         SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
  29.         java.util.Date date = sdf.parse("2020-10-1");
  30.         ps.setDate(3, new Date(date.getTime()));
  32.         //执行SQL语句
  33.         ps.execute();
  34.     }catch(Exception e){
  35.         e.printStackTrace();
  36.     }finally{
  37.         //关闭资源
  38.         try{
  39.             if (ps != null)
  40.                 ps.close();
  41.         }catch(SQLException e){
  42.             e.printStackTrace();
  43.         }
  44.         try{
  45.             if (conn != null)
  46.                 conn.close();
  47.         }catch(SQLException e){
  48.             e.printStackTrace();
  49.         }
  50.     }
  51. }

连接操作和关闭资源操作每次执行SQL语句时都会使用，可以将这些代码写成方法便于使用

  1. public class JDBCUtils{
  2.     public static Connection getConnection() throws SQLException, ClassNotFoundException, IOException {
  3.         InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
  4.         Properties pros = new Properties();
  5.         pros.load(is);
  7.         String user = pros.getProperty("user");
  8.         String password = pros.getProperty("password");
  9.         String url = pros.getProperty("url");
  10.         String driverClass = pros.getProperty("driverClass");
  12.         //加载驱动
  13.         Class.forName(driverClass);
  15.         //获取连接
  16.         Connection conn = DriverManager.getConnection(url, user, password);
  17.         return conn;
  18.     }
  20.     public static void close(Connection conn, PreparedStatement ps){
  21.         try {
  22.             if (conn != null)
  23.                 conn.close();
  24.         } catch (SQLException e) {
  25.             e.printStackTrace();
  26.         }
  27.         try {
  28.             if (ps != null)
  29.                 ps.close();
  30.         } catch (SQLException e) {
  31.             e.printStackTrace();
  32.         }
  33.     }
  34. }
  • 实现PreparedStatement通用的增删改操作
  1. //通用的增删改操作
  2. //SQL中占位符的长度等于可变形参的长度
  3. public void update(String sql, Object... args){
  4.     Conneciton conn = null;
  5.     PreparedStatement ps = null;
  6.     try{
  7.         //获取数据库连接
  8.         conn = JDBCUtils.getConnection();
  9.         //获取PreparedStatement对象实列预编译SQL语句
  10.         ps = conn.PrepareStatement(sql);
  11.             //填充占位符
  12.         for(int i = 0; i < args.length; i++){
  13.             ps.setObject(i + i, args[i]);
  14.         }
  15.         //执行SQL语句
  16.         ps.execute();
  17.     } catch(Exception e){
  18.         e. printStackTrace();
  19.        }finally{
  20.         //关闭资源
  21.         JDBCUtils.closeResource(conn, ps)
  22.     } 
  23. }

使用PreparedStatement实现查询操作

  • 针对一条数据时的查询操作
  1. import java.util.Date;
  3. /**
  4.  * ORM编程思想
  5.  * 一个数据表对应一个Java类
  6.  * 表中的一条记录对应Java类的一个对象
  7.  * 表中的一个字段对应Java类的一个属性
  8.  */
  9.    public class Customer {
  10.        private int id;
  11.        private String name;
  12.        private String email;
  13.        private Date birth;
  15.        public Customer() {
  16.            super();
  17.        }
  19.        public Customer(int id, String name, String email, Date birth) {
  20.            this.id = id;
  21.            this.name = name;
  22.            this.email = email;
  23.            this.birth = birth;
  24.        }
  26.        public int getId() {
  27.            return id;
  28.        }
  30.        public void setId(int id) {
  31.            this.id = id;
  32.        }
  34.        public String getName() {
  35.            return name;
  36.        }
  38.        public void setName(String name) {
  39.            this.name = name;
  40.        }
  42.        public String getEmail() {
  43.            return email;
  44.        }
  46.        public void setEmail(String email) {
  47.            this.email = email;
  48.        }
  50.        public Date getBirth() {
  51.            return birth;
  52.        }
  54.        public void setBirth(Date birth) {
  55.            this.birth = birth;
  56.        }
  58.        @Override
  59.        public String toString() {
  60.            return "Customer{" +
  61.                    "id=" + id +
  62.                    ", name='" + name + '\'' +
  63.                    ", email='" + email + '\'' +
  64.                    ", birth=" + birth +
  65.                    '}';
  66.        }
  67.    }
  1. public void test(){
  2.         Connection conn = null;
  3.         PreparedStatement ps = null;
  4.         ResultSet re = null;
  5.         try {
  6.           //获取连接对象
  7.             conn = JDBCUtils.getConnection();
  8.             String sql = "SELECT id, name, email, birth FROM customers WHERE id = ?";
  9.             ps = conn.prepareStatement(sql);
  11.             ps.setObject(1,1);
  12.             //执行并返回结果集
  13.             re = ps.executeQuery();
  14.             //处理结果集
  15.             if(re.next()){
  16.                 int id = re.getInt(1);
  17.                 String name = re.getString(2);
  18.                 String email = re.getString(3);
  19.                 Date birth = re.getDate(4);
  21.                 //方法1：直接输出结果集
  22.                 System.out.println("id = " + id + ",name = " + name + ",email = " + email + ",birth = " + birth);
  24.                 //方法2：使用数组输出结果集
  25.                 Object[] data = {id, name, email, birth};
  27.                 //方法3：将数据封装成一个对象输出(推荐)
  28.                 Customer customer = new Customer(id, name, email, birth);
  29.                 System.out.println(customer);
  31.             }
  32.         } catch (Exception e) {
  33.             e.printStackTrace();
  34.         }finally {
  35.             JDBCUtils.close(conn, ps, re);
  36.         }
  37.     }
  • 针对一张表时的通用查询操作
  1. public Customer queryFroCustomers(String sql, Object... args){
  2.         Connection conn = null;
  3.         PreparedStatement ps = null;
  4.         ResultSet rs = null;
  5.         Customer cust = null;
  6.         try {
  7.             conn = JDBCUtils.getConnection();
  9.             ps = conn.prepareStatement(sql);
  10.             for (int i = 0; i < args.length; i++){
  11.                 ps.setObject(i + 1, args[i]);
  12.             }
  14.             rs = ps.executeQuery();
  15.             //获取结果集的元数据
  16.             ResultSetMetaData rsmd = rs.getMetaData();
  17.             //通过ResultSetMetaData获取结果集中的列数
  18.             int columnCount = rsmd.getColumnCount();
  20.             while (rs.next()) {
  21.                 cust = new Customer();
  22.                 //处理一列数据的结果集
  23.                 for (int i = 0; i< columnCount; i++){
  24.                     Object columnNameValue = rs.getObject(i + 1);
  25.                     //获取每个列的列名
  26.                     String columnName = rsmd.getColumnName(i + 1);
  27.                     //给cust对象指定的columnName属性，赋值为columnNameValue
  28.                     Field field = Customer.class.getDeclaredField(columnName);
  29.                     field.setAccessible(true);
  30.                     field.set(cust, columnNameValue);               
  31.                 }
  32.                 return cust;
  33.             }
  34.         } catch (Exception e) {
  35.             e.printStackTrace();
  36.         } finally {
  37.             JDBCUtils.close(conn, ps, rs);
  38.         }
  40.         return null;
  41. }

注意：如果表名和Java类中的属性名不相同时，可以给SQL语句的字段名取别名对应上Java类中的属性名，并将getColumnName替换成getColumnLable，并且也更加推荐使用getColumnLable

  • 针对所有表的查询操作
  1. public <T> List<T> getInstance(Class<T> clazz, String sql, Object... args){
  2.         Connection conn = null;
  3.         PreparedStatement ps = null;
  4.         ResultSet rs = null;
  5.         try {
  6.             //获取连接对象
  7.             conn = JDBCUtils.getConnection();
  8.             //预编译SQL语句
  9.             ps = conn.prepareStatement(sql);
  10.             for (int i = 0; i < args.length; i++){
  11.                 ps.setObject(i + 1, args[i]);
  12.             }
  14.             rs = ps.executeQuery();
  15.             //获取结果集的元数据
  16.             ResultSetMetaData rsmd = rs.getMetaData();
  17.             //通过ResultSetMetaData获取结果集中的列数
  18.             int columnCount = rsmd.getColumnCount();
  19.             //创建集合对象
  20.             ArrayList<T> list = new ArrayList<>();
  21.             while (rs.next()) {
  22.                 //通过反射技术获取对象
  23.                 T t = clazz.newInstance();
  24.                 //处理一列数据的结果集
  25.                 for (int i = 0; i< columnCount; i++){
  26.                     Object columnNameValue = rs.getObject(i + 1);
  27.                     //获取每个列的列名
  28.                     String columnName = rsmd.getColumnLable(i + 1);
  29.                     //给t对象指定的columnName属性，赋值为columnNameValue
  30.                     Field field = Customer.class.getDeclaredField(columnName);
  31.                     field.setAccessible(true);
  32.                     field.set(t, columnNameValue);
  33.                 }
  34.                 list.add(t);
  35.             }
  36.             return list;
  37.         } catch (Exception e) {
  38.             e.printStackTrace();
  39.         } finally {
  40.             JDBCUtils.close(conn, ps, rs);
  41.         }
  42.         return null;
  43. }

PreparedStatement操作BLOB类型字段

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

  • 插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写的。

  • MySQL的四种BLOB类型(除了在存储的最大信息量上不同外，他们是等同的)

类型 大小(单位字节)
TinyBlob 最大 225
Blob 最大 65k
MediumBlob 最大 16M
LongBlob 最大 4G
  • 注意：如果储存的文件过大，会导致数据库的性能下降

使用PreparedStatement批量插入数据

  1. public void test(){
  2.     Connection conn = null;
  3.     PreparedStatement ps = null;
  4.     try {
  5.         //计时
  6.         long start = System.currentTimeMillis();
  8.         conn = JDBCUtils.getConnection();
  9.         //设置不予许自动提交数据
  10.         conn.setAutoCommit(false);
  11.         String sql = "INSERT INTO goods(name)VALUES(?)";
  12.         ps = conn.prepareStatement(sql);
  13.         for (int i = 1; i <= 200000; i++){
  14.            ps.setObject(1, "name" + i);
  16.            //"攒sql"
  17.            ps.addBatch();
  18.             if (i % 500 == 0) {
  19.                 //执行batch
  20.                 ps.executeBatch();
  22.                 //清空batch
  23.                 ps.clearBatch();
  24.              }
  25.           }
  26.             //提交数据
  27.             conn.commit();
  28.             long end = System.currentTimeMillis();
  29.             System.out.println("花费的时间为" + (end - start));
  30.         } catch (Exception e) {
  31.             e.printStackTrace();
  32.         }  finally {
  33.             JDBCUtils.close(conn, ps);
  34.         }
  35. }

数据库事务

数据库事务介绍

  • 事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。
  • 事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。
  • 为确保数据库中数据的一致性，数据的操纵应当是离散的成组的逻辑单元：当它全部完成时，数据的一致性可以保持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有从起始点以后的操作应全部回退到开始状态。

JDBC事务处理

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

  • 那些操作会导致数据自动提交？

    1. DDL操作一旦执行就会自动提交
    2. DML默认情况下，一旦执行就会自动提交
    3. 默认关闭连接时，会自动提交数据

  • JDBC程序中为了让多个SQL语句作为一个事物执行

    1. 调用Connection对象的setAutoCommit(false) 以取消自动提交事务
    2. 在所有的SQL语句确保执行成功后，再调用commit()提交事务
    3. 在出现异常时，调用Rollback()方法回滚事务

若此时 Connection 没有被关闭，还可能被重复使用，则需要恢复其自动提交状态 setAutoCommit(true)。尤其是在使用数据库连接池技术时，执行close()方法前，建议恢复自动提交状态。

  • 案列：模拟数据库事务提交异常
  1. public void testJDBCTransaction(){
  2.     Connection conn = null;
  4.         //获取数据库连接
  5.         try {
  6.             conn = JDBCUtils.getConnection();
  7.             //开启事务
  8.             conn.setAutoCommit(false);
  9.             //进行数据库操作
  10.             String sql1 = "UPDATE user_table SET balance = balance - 100 WHERE user = ?";
  12.             upDate(conn, sql1, "AA");
  14.             //模拟网络异常
  15. //            System.out.println(10 / 0);
  17.             String sql2 = "UPDATE user_table SET balance = balance + 100 WHERE user = ?";
  18.             upDate(conn, sql2, "BB");
  19.             //如果没有异常那么就提交事务
  20.             conn.commit();
  21.         } catch (Exception e) {
  22.             e.printStackTrace();
  23.             //如果有异常那么就回滚事务
  24.             try {
  25.                 conn.rollback();
  26.             } catch (SQLException ex) {
  27.                 ex.printStackTrace();
  28.             }
  29.         }  finally {
  30.             //恢复每次DML操作的自动提交功能
  31.             //主要针对于数据库连接池使用
  32.             try {
  33.                 conn.setAutoCommit(true);
  34.             } catch (SQLException e) {
  35.                 e.printStackTrace();
  36.             }
  37.             //7.关闭连接
  38.             JDBCUtils.closeResource(conn, null); 
  39.         }
  40. }
  • 对数据的操作方法
  1. public void upDate(Connection conn, String sql, Object... args){
  2.         PreparedStatement ps = null;
  3.         try {
  4.             //获取PreparedStatement实列
  5.             ps = conn.prepareStatement(sql);
  6.             //填充占位符
  7.             for (int i = 0; i < args.length; i++){
  8.                 ps.setObject(i + 1, args[i]);
  9.             }
  10.             //执行SQL语句
  11.             ps.execute();
  12.         } catch (SQLException e) {
  13.             e.printStackTrace();
  14.         } finally {
  15.             //首先不关闭Connection等确定数据库操作成功后再自行手动关闭资源
  16.             JDBCUtils.close(null, ps);
  17.         }
  18.     }

事务的ACID属性

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

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

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

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

事务并发问题

  • 对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:

    • 脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段。
    • 不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段。之后, T1再次读取同一个字段, 值就不同了。
    • 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行。之后, 如果 T1 再次读取同一个表, 就会多出几行。

  • 数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。

  • 一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

数据库的四种隔离级别

  • 数据库提供的四种隔离级别

JDBC - 图12

  • Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED 。

  • Mysql 支持 4 种事务隔离级别。Mysql 默认的事务隔离级别为: REPEATABLE READ。

在MySQL中设置隔离级别

  • 每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别。

  • 查看当前隔离级别

    1. SELECT @@tx_isolation;
  • 设置当前 mySQL 连接的隔离级别:
    1. set  transaction isolation level read committed;
  • 设置数据库系统的全局的隔离级别:
    1. set global transaction isolation level read committed;

DAO及相关实现类

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

Druid(德鲁伊) 数据库连接池

  • Druid是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、Proxool等DB池的优点，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池，可以说是目前最好的连接池之一。
  1.  public void test() throws Exception {
  2.         Properties pros = new Properties();
  4.         InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
  6.         pros.load(is);
  8.         DataSource source = DruidDataSourceFactory.createDataSource(pros);
  10.         Connection conn = source.getConnection();
  11.         System.out.println(conn);
  13.         QueryRunner runner = new QueryRunner();
  15.     }
  • src配置文件
  1. url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
  2. username=root
  3. password=123456
  4. driverClassName=com.mysql.jdbc.Driver
  6. initialSize=10
  7. maxActive=20
  • 详细参数 | 配置 | 缺省 | 说明 | | —- | —- | —- | | 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/使用ConfigFilter | | 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，是组合关系，并非替换关系 |

使用Apache-DBUtils实现CRUD操作

Apache-DBUtils简介

  • commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。

  • 先导入DBUtils包

主要AIP使用

DbUtils ：提供如关闭连接、装载JDBC驱动程序等常规工作的工具类，里面的所有方法都是静态的。主要方法如下：

  1. public static void close(…) throws java.sql.SQLException：　DbUtils类提供了三个重载的关闭方法。这些方法检查所提供的参数是不是NULL，如果不是的话，它们就关闭Connection、Statement和ResultSet。

  2. public static void closeQuietly(…): 这一类方法不仅能在Connection、Statement和ResultSet为NULL情况下避免关闭，还能隐藏一些在程序中抛出的SQLEeception。

  3. public static void commitAndClose(Connection conn)throws SQLException： 用来提交连接的事务，然后关闭连接

  4. public static void commitAndCloseQuietly(Connection conn)： 用来提交连接，然后关闭连接，并且在关闭连接时不抛出SQL异常。

  5. public static void rollback(Connection conn)throws SQLException：允许conn为null，因为方法内部做了判断

  6. public static void rollbackAndClose(Connection conn)throws SQLException

  7. rollbackAndCloseQuietly(Connection)

  8. public static boolean loadDriver(java.lang.String driverClassName)：这一方装载并注册JDBC驱动程序，如果成功就返回true。使用该方法，你不需要捕捉这个异常ClassNotFoundException。

  • 测试
  1. //添加
  2. public void testDBUtils() throws SQLException, IOException, ClassNotFoundException {
  3.         QueryRunner runner = new QueryRunner();
  4.         Connection conn = JDBCUtils.getConnection();
  5.         String sql = "insert into customers(name,email,birth)values(?,?,?)";
  6.         int count = runner.update(conn, sql, "张三", "zhangsan@qq.com", "1992-09-08");
  7.         JDBCUtils.close(conn, null);
  8.         System.out.println("添加了" + count + "条记录");
  10.     }
  1. //删除
  2. public void testDelete() throws Exception {
  3.     QueryRunner runner = new QueryRunner();
  4.     Connection conn = JDBCUtils.getConnection();
  5.     String sql = "delete from customers where id < ?";
  6.     int count = runner.update(conn, sql,3);
  8.     System.out.println("删除了" + count + "条记录");
  10.     JDBCUtils.closeResource(conn, null);
  12. }

ResultSetHandler接口及实现类

  • 该接口用于处理 java.sql.ResultSet，将数据按要求转换为另一种形式。

  • ResultSetHandler 接口提供了一个单独的方法：Object handle (java.sql.ResultSet .rs)。

  • 接口的主要实现类：

    1. ArrayHandler：把结果集中的第一行数据转成对象数组。

    2. ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中。

    3. BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。

    4. BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。

    5. ColumnListHandler：将结果集中某一列的数据存放到List中。

    6. KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里，再把这些map再存到一个map里，其key为指定的key。

    7. MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。

    8. MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List

    9. ScalarHandler：查询单个值对象

  • 测试

  1. /*
  2.  * 测试查询:查询一条记录
  3.  * 
  4.  * 使用ResultSetHandler的实现类：BeanHandler
  5.  */
  6. @Test
  7. public void testQueryInstance() throws Exception{
  8.     QueryRunner runner = new QueryRunner();
  10.     Connection conn = JDBCUtils.getConnection();
  12.     String sql = "select id,name,email,birth from customers where id = ?";
  14.     //
  15.     BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
  16.     Customer customer = runner.query(conn, sql, handler, 23);
  17.     System.out.println(customer);    
  18.     JDBCUtils.closeResource(conn, null);
  19. }
  1. /*
  2.  * 测试查询:查询多条记录构成的集合
  3.  * 
  4.  * 使用ResultSetHandler的实现类：BeanListHandler
  5.  */
  6. @Test
  7. public void testQueryList() throws Exception{
  8.     QueryRunner runner = new QueryRunner();
  10.     Connection conn = JDBCUtils.getConnection();
  12.     String sql = "select id,name,email,birth from customers where id < ?";
  14.     //
  15.     BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
  16.     List<Customer> list = runner.query(conn, sql, handler, 23);
  17.     list.forEach(System.out::println);
  19.     JDBCUtils.closeResource(conn, null);
  20. }