JDBC和数据库连接池

1 JDBC概述

1.1 数据持久化

  • 持久化(persistence)：把数据保存到可掉电式存储设备中以供之后使用。大多数情况下，特别是企业级应用，数据持久化意味着将内存中的数据保存到硬盘上加以”固化”，而持久化的实现过程大多通过各种关系数据库来完成。
  • 持久化的主要应用是将内存中的数据存储在关系型数据库中，当然也可以存储在磁盘文件、XML数据文件中。

image-20230824194700670

1.2 Java中的数据存储技术

  • 在Java中，数据库存取技术可分为如下几类：

    • JDBC直接访问数据库
    • JDO (Java Data Object )技术
    • 第三方O/R工具，如Hibernate, Mybatis 等

  • JDBC是java访问数据库的基石，JDO、Hibernate、MyBatis等只是更好的封装了JDBC。

1.3 JDBC介绍

  • JDBC(Java Database Connectivity)是一个独立于特定数据库管理系统、通用的SQL数据库存取和操作的公共接口（一组API），定义了用来访问数据库的标准Java类库，（java.sql,javax.sql）使用这些类库可以以一种标准的方法、方便地访问数据库资源。
  • JDBC为访问不同的数据库提供了一种统一的途径，为开发者屏蔽了一些细节问题。
  • JDBC的目标是使Java程序员使用JDBC可以连接任何提供了JDBC驱动程序的数据库系统，这样就使得程序员无需对特定的数据库系统的特点有过多的了解，从而大大简化和加快了开发过程。
  • 如果没有JDBC，那么Java程序访问数据库时是这样的：

image-20230824194843088

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

image-20230824194932308

image-20230824194958057

1.4 JDBC体系结构

  • JDBC接口（API）包括两个层次：

    • 面向应用的API：Java API，抽象接口，供应用程序开发人员使用（连接数据库，执行SQL语句，获得结果）。
    • 面向数据库的API：Java Driver API，供开发商开发数据库驱动程序用。

JDBC是sun公司提供一套用于数据库操作的接口，java程序员只需要面向这套接口编程即可。不同的数据库厂商，需要针对这套接口，提供不同实现。不同的实现的集合，即为不同数据库的驱动。 ————面向接口编程

2 JDBC详解

2.1 JDBC入门案例

image-20230824195136463

  1. 导入.sql数据
  2. 创建maven项目，引入依赖

image-20230824195259631

pom.xml中引入MySQL数据库的java连接器

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.31</version>
        </dependency>
    </dependencies>
  1. 练习
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        //Connection
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
        //Statement，PrepareStatement
        PreparedStatement ps = conn.prepareStatement("SELECT name FROM customers");
        //ResultSet
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString("name"));
        }
        //必须关闭连接
        rs.close();
        ps.close();
        conn.close();
    }

package com.gk.demo;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Main {
    public static void main(String[] args) throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        //Connection
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");
        //PrepareStatement
        PreparedStatement ps = con.prepareStatement("SELECT * FROM customers where id=16");
        //ResultSet
        ResultSet rs = ps.executeQuery();
        InputStream in = null;
        String name = null;
        while (rs.next()){
            /*System.out.print(rs.getInt("id"));
            System.out.print(rs.getString("name"));
            System.out.println(rs.getDate("birth"));*/
            in =  rs.getBinaryStream("photo");
            name = rs.getString("name");
        }

        FileOutputStream out = new FileOutputStream(name + ".jpg");
        int len = -1;
        byte[] buffer = new byte[1024];
        while ((len = in.read(buffer)) != -1){
            out.write(buffer,0,len);
            out.flush();
        }

        //Close
        out.close();
        in.close();
        rs.close();
        ps.close();
        con.close();
    }
}

2.2 Class.forName() 与 Connection

  • Class.forName() 是 Java 中的一个方法，用于加载并初始化指定的类。它可以通过提供类的全限定名作为参数来加载类。

    在旧版本的 Java 中，通过调用 Class.forName(className) 加载类会触发该类的静态初始化块，进而执行一些静态操作。这通常用于加载和注册 JDBC 驱动程序、加载特定的类库或执行其他需要在运行时进行类加载的操作。

    然而，从 Java 9 开始，在大多数情况下，不再需要显式调用 Class.forName() 来加载类。JVM 在需要使用类时会自动进行类加载，并且可以通过模块系统、服务提供者接口（SPI）等机制更方便地指定要加载的类。因此，在较新的 Java 版本中，很少需要使用 Class.forName()。

    需要注意的是，如果你在使用某些需要旧版兼容性的代码或框架，可能仍然需要使用 Class.forName()。但在大多数情况下，可以通过让 JVM 自动加载类来简化代码并提高可读性。

  • Connection 是 Java 中表示与数据库建立的连接的接口，位于 java.sql.Connection 包中。它是使用 JDBC（Java Database Connectivity）进行数据库操作的关键组件之一。

    通过创建 Connection 对象，可以与数据库建立连接，并执行各种操作，如执行 SQL 语句、事务管理、设置连接属性等。

2.3 PreparedStatement 和 Statement

  • 数据库连接被用于向数据库服务器发送命令和SQL语句，并接受数据库服务器返回的结果。其实一个数据库连接就是一个Socket连接
  • 在java.sql包中有三个接口分别定义了对数据库的调用的不同方式：
    • Statement：用于执行静态SQL语句并返回它所生成结果的对象
    • PrepareStatement：SQL语句被预编译并存储在此对象中，可以使用此对象多次高效地执行该语句
    • CallableStatement：用于执行SQL存储过程

image-20230824200438063

2.2.1 PreparedStatement

  • 可以通过调用Connection对象的prepareStatement(String sql)方法获取PrepareStatement对象
  • PrepareStatement接口是Statement的子接口，表示一条预编译过的SQL语句
  • PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示，调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数. setXxx() 方法有两个参数，第一个参数是要设置的 SQL 语句中的参数的索引(从 1 开始)，第二个是设置的 SQL 语句中的参数的值
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "your-username";
        String password = "your-password";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            // 创建 PreparedStatement 对象
            String sql = "SELECT * FROM mytable WHERE id = ?";
            PreparedStatement statement = connection.prepareStatement(sql);

            // 设置参数的值
            int idParam = 1;
            statement.setInt(1, idParam);//第一个1代表第一个问号

            // 执行查询
            ResultSet resultSet = statement.executeQuery();

            // 处理查询结果
            while (resultSet.next()) {
                // 通过列名或索引获取数据
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");

                // 打印结果
                System.out.println("ID: " + id + ", Name: " + name);
            }

            // 关闭连接
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

在上述代码中，我们创建了一个带有参数的 SQL 查询语句，并将参数索引设置为 1（即第一个问号）。然后，使用 setInt() 方法将参数的值设置为 idParam 变量的值。最后，调用 executeQuery() 方法执行查询。

注意，在设置参数索引时，从 1 开始计数。具体使用哪个 setXxx() 方法取决于参数的类型（如 setInt()、setString() 等）。

通过使用 PreparedStatement 对象可以安全地处理带有参数的 SQL 语句，避免了 SQL 注入攻击，并提高了性能和可读性。

2.2.2 PreparedStatement 与 Statement

  • PreparedStatement 能最大可能提高性能：
    • DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。
    • 在statement语句中,即使是相同操作但因为数据内容不一样,所以整个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存。这样每执行一次都要对传入的语句编译一次。
    • (语法检查，语义检查，翻译成二进制命令，缓存)
  • PreparedStatement 可以防止 SQL 注入
  • Statement需要拼接SQL语句代码繁琐，存在SQL注入问题

1. 登录Statement实现案例

public class StatementTest {

    /**
     * Statement
     *   - 不能预编译语句，性能差
     *   - sql语句，拼接字符串，繁琐
     *   - sql注入问题
     *      - 用户名输入：”1' or “
     *      - 密码输入：“=1 or '1' = '1”
     */
    public static void main(String[] args) throws Exception {
        Scanner sc = new Scanner(System.in);
        System.out.println("输入用户名");
        String username = sc.nextLine();
        System.out.println("输入密码");
        String password = sc.nextLine();

        String sql = "SELECT * FROM user_table WHERE USER = '" + username +  "' AND PASSWORD = '" + password +"'";
        Class.forName("com.mysql.cj.jdbc.Driver");
        //Connection
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
        //Statement，PrepareStatement
        Statement st = conn.createStatement();
        //ResultSet
        ResultSet rs = st.executeQuery(sql);
        if (rs.next()) {
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败");
        }
        //必须关闭连接
        rs.close();
        st.close();
        conn.close();
    }

}

2. 登录PreparedStatement

public class PrepareStatementTest {

    /**
     * PrepareStatement
     *   性能好
     *   使用?代替参数，避免字符串拼接，避免SQL注入	
     */
    public static void main(String[] args) throws Exception {
        Scanner sc = new Scanner(System.in);
        System.out.println("输入用户名");
        String username = sc.nextLine();
        System.out.println("输入密码");
        String password = sc.nextLine();

        String sql = "SELECT * FROM user_table WHERE USER = ? AND PASSWORD = ?";
        Class.forName("com.mysql.cj.jdbc.Driver");
        //Connection
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
        //Statement，PrepareStatement
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, username);
        ps.setString(2, password);
        //ResultSet
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败");
        }
        //必须关闭连接
        rs.close();
        ps.close();
        conn.close();
    }

}

2.2.3 SQL注入

SQL 注入是一种常见的安全漏洞，它发生在应用程序未正确验证用户输入数据，并将其直接插入到构成 SQL 查询的字符串中。攻击者可以通过恶意构造的输入数据修改原始 SQL 查询的含义，以执行未授权的操作或暴露敏感信息。

下面是一个示例，展示了 SQL 注入攻击的可能性：

假设有一个登录页面，用户可以输入用户名和密码进行身份验证。后台使用用户输入直接拼接 SQL 查询语句，例如：

String username = request.getParameter("username");
String password = request.getParameter("password");

String sql = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";

如果不对用户输入进行验证和转义，而是直接将其插入到 SQL 查询字符串中，攻击者可以通过输入特殊字符来修改查询的含义。

  • 举例1，如果用户输入的用户名为 admin' --，则构成的 SQL 查询变为：
SELECT * FROM users WHERE username = 'admin' --' AND password = 'xxxx'

​ 这个查询通过注释符 -- 将原始密码条件注释掉，因此无需提供正确的密码即可绕过身份验证。

  • 举例2，用户名输入：1' or，密码输入：=1 or '1' = '1
SELECT * FROM user_table WHERE USER = '1' or' and password='=1 or '1' = '1'

​ 这个查询只执行最后一个or后的语句

  1. 使用参数化查询或预编译语句：使用 PreparedStatement 接口或其他支持参数化查询的机制。通过将用户输入作为参数传递给查询，而不是直接拼接到查询字符串中，可以防止注入攻击。

  2. 输入验证和过滤：对用户的输入进行验证和过滤，确保只接受预期的数据格式和字符。可以使用正则表达式、白名单或黑名单等方法对输入进行验证。

  3. 参数化存储过程：如果使用存储过程，确保正确使用参数化语句和绑定输入参数，以防止注入。

  4. 最小权限原则：数据库用户应该具有最少的权限来执行所需的操作，以限制攻击者对数据库的影响范围。

通过采取这些防御措施，可以有效地预防 SQL 注入攻击，并提高应用程序的安全性。记住，永远不要相信用户输入，始终进行适当的验证和转义。

3 JDBC的CRUD实现

3.1 程序的分层

java开发手册推荐的工程的分层

image-20230825202425618

  • Dao(data access object)：数据访问层
  • Service：业务逻辑层
  • Controller：控制层
  • domain：实体类·，领域模型（persistence object）

3.2 领域模型（实体类）

创建表对应的实体类：表->类，字段->属性

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

Java类型SQL类型
booleanBIN
byteTINYINT
shortSMALLINT
intINTEGER
longBIGINT
StringCHAR,VARCHAR,LONGVARCHAR
byte arrayBINARY,VAR BINARY
java.sql.DateDATE
java.sql.TimeTIME
java.sql.TimestampTIMESTAMP

domain

User

package com.gk.c_crud.domain;

public class User {
    //实体类不要使用基本数据类型，使用包装类，原始基本数据类型不能接受null
    private Integer id;
    private String name;
    private String password;
    private String address;
    private String phone;

    public User() {
    }

    public User(Integer id, String name, String password, String address, String phone) {
        this.id = id;
        this.name = name;
        this.password = password;
        this.address = address;
        this.phone = phone;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", address='" + address + '\'' +
                ", phone='" + phone + '\'' +
                '}';
    }
}

Order

public class Order {

    private Integer orderId;
    private String orderName;
    private Date orderDate;

    public Integer getOrderId() {
        return orderId;
    }

    public void setOrderId(Integer orderId) {
        this.orderId = orderId;
    }

    public String getOrderName() {
        return orderName;
    }

    public void setOrderName(String orderName) {
        this.orderName = orderName;
    }

    public Date getOrderDate() {
        return orderDate;
    }

    public void setOrderDate(Date orderDate) {
        this.orderDate = orderDate;
    }
}

3.3 Dao层

UserDao

package com.gk.c_crud.dao;

import com.gk.c_crud.domain.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class UserDao {
    /**
     * 查询所有
     * */
    public List<User> find(){
        List<User> list = new ArrayList<>(10);
        String sql = "select `id`,`name`,`password`,`address`,`phone` from user";
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                User user = new User();
                user.setId(rs.getInt(1));
                user.setName(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setAddress(rs.getString("address"));
                user.setPhone(rs.getString("phone"));
                list.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null && !con.isClosed()) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    /**
    * 查询单个id
    */
    public User findSingle(){
        User user = new User();
        Scanner sc = new Scanner(System.in);
        System.out.println("输入要查询的人物的id：");
        int id = sc.nextInt();
        String sql = "SELECT `id`,`name`,`password`,`address`,`phone` FROM user where id = ?";

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");
            ps = con.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();

            if(rs.next()){
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setAddress(rs.getString("address"));
                user.setPhone(rs.getString("phone"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null && !con.isClosed()) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return user;
    }

    /**
    *  插入数据
    */
    public int insert(){
        int rows = 0;
        Scanner src = new Scanner(System.in);
        System.out.println("输入名字");
        String name = src.next();
        System.out.println("输入密码");
        String password = src.next();
        System.out.println("输入地址");
        String address = src.next();
        System.out.println("输入电话");
        String phone = src.next();

        Connection con = null;
        String sql = "insert into user(`name`, `password`, `address`, `phone`) values(?,?,?,?)";
        PreparedStatement ps = null;
        //ResultSet rs = null;

        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");
            ps = con.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,password);
            ps.setString(3,address);
            ps.setString(4,phone);
            //rs = ps.executeQuery();
            rows = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {

            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null && !con.isClosed()) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    /**
    *  输入id,删除数据
    */
    public int delete(){
        int rows = 0;
        Scanner src = new Scanner(System.in);
        System.out.println("输入要删除的人的id:");
        Integer id = src.nextInt();
        String sql = "delete from `user` where id = ?";
        Connection con = null;
        PreparedStatement ps = null;

        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");
            ps = con.prepareStatement(sql);
            ps.setInt(1,id);
            rows = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null && !con.isClosed()) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return rows;
    }

    /**
    *  输入id，更改数据
    */
    public int update(){
        User user = new User();
        int rows = 0;
        Scanner src = new Scanner(System.in);
        System.out.println("输入要修改的人的id:");
        Integer id = src.nextInt();
        System.out.println("输入改后的id");
        Integer ids = src.nextInt();
        System.out.println("输入改后的名字");
        String name = src.next();
        System.out.println("输入改后的密码");
        String password = src.next();
        System.out.println("输入改后的地址");
        String address = src.next();
        System.out.println("输入改后的手机号");
        String phone = src.next();

        String sql = "update `user` set `id` = ?, `name` = ?, `password` = ?, `address` = ?, `phone` = ? where id = ?";
        Connection con = null;
        PreparedStatement ps = null;

        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");
            ps = con.prepareStatement(sql);
            ps.setInt(1,ids);
            ps.setString(2,name);
            ps.setString(3,password);
            ps.setString(4,address);
            ps.setString(5,phone);
            ps.setInt(6,id);
            rows = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null && !con.isClosed()) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return rows;
    }
    
     /**
     * 添加
     * @param user
     * @return 返回语句影响的函数
     */
    public int update1(User user) {
        int rows = 0;
        String sql = "update user set `name`=?, `password`=?,  `address`=?,  `phone`=? WHERE `id` = ?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getName());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getAddress());
            ps.setString(4, user.getPhone());
            ps.setInt(5, user.getId());

            rows = ps.executeUpdate(); //执行修改,返回语句影响的行数

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

}

OrderDao

public class OrderDao {

    /**
     * `order_id`,  `order_name`,  `order_date`
     */

    /**
     * 查询所有
     * @return
     */
    public List<Order> find() {
        List<Order> list = new ArrayList<>(10);
        String sql = "SELECT `order_id`,  `order_name`,  `order_date` from `order`";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Order order = new Order();
                order.setOrderId(rs.getInt(1));
                order.setOrderName(rs.getString(2));
                order.setOrderDate(rs.getDate(3));
                list.add(order);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    /**
     * 查询主键对应的记录
     * @param id
     * @return
     */
    public Order get(Integer id) {
        Order order = new Order();
        String sql = "SELECT `order_id`,  `order_name`,  `order_date` from `order` where `order_id` = ?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                order.setOrderId(rs.getInt(1));
                order.setOrderName(rs.getString(2));
                order.setOrderDate(rs.getDate(3));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return order;
    }

    /**
     * 添加
     * @param order
     * @return 返回语句影响的函数
     */
    public int insert(Order order) {
        int rows = 0;
        String sql = "insert into `order`( `order_name`,  `order_date`) values(?,?)";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            ps = conn.prepareStatement(sql);
            ps.setString(1, order.getOrderName());
            ps.setDate(2, new Date(order.getOrderDate().getTime()) );

            rows = ps.executeUpdate(); //执行修改,返回语句影响的行数

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    /**
     * 删除
     * @param id
     * @return 返回语句影响的函数
     */
    public int delete(Integer id) {
        int rows = 0;
        String sql = "delete from `order` where order_id = ?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);

            rows = ps.executeUpdate(); //执行修改,返回语句影响的行数

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    /**
     * 添加
     * @param order
     * @return 返回语句影响的函数
     */
    public int update(Order order) {
        int rows = 0;
        String sql = "update `order` set `order_name`=?, `order_date`=? WHERE `order_id` = ?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            ps = conn.prepareStatement(sql);
            ps.setString(1, order.getOrderName());
            ps.setDate(2, new Date(order.getOrderDate().getTime()));
            ps.setInt(3, order.getOrderId());

            rows = ps.executeUpdate(); //执行修改,返回语句影响的行数

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }
}

3.4 测试

ctrl+shift+t

UserDaoTest

package com.gk.c_crud.dao;

import com.gk.c_crud.domain.User;
import org.junit.Test;



public class UserDaoTest {
    UserDao userDao = new UserDao();

    @Test
    public void find() {
        for (User user : userDao.find()) {
            System.out.println(user);
        }
    }
    @Test
    public void findSingle(){
        System.out.println(userDao.findSingle());
    }
    @Test
    public void insert(){
        System.out.println(userDao.insert());
    }
    @Test
    public void delete(){
        System.out.println(userDao.delete());
    }
    @Test
    public void update(){
        System.out.println(userDao.update());
    }
     @Test
    public void update() {
        User user = new User(1, "张学友", "123", "香港", "110");
        userDao.update(user);
    }
}

OrderDaoTest

public class OrderDaoTest {

    OrderDao dao = new OrderDao();

    @Test
    public void find() {
        dao.find().forEach(o -> System.out.println(o));
    }

    @Test
    public void get() {
        System.out.println(dao.get(1));
    }

    @Test
    public void insert() {
        Order o = new Order(null, "华为", new Date());
        dao.insert(o);
    }

    @Test
    public void delete() {
    }

    @Test
    public void update() {
    }
}

3.5 JdbcUtil

  1. 创建属性文件jdbc.properties,配置连接字符串，驱动，用户，密码
user=root
password=123456
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.cj.jdbc.Driver
  1. JdbcUtil工具类，处理重复使用的方法
public class JdbcUtil {

    static String driver;
    static String url;
    static String user;
    static String password;

    static {
//        InputStream in = JdbcUtil.class.getResourceAsStream("/jdbc.properties");
        InputStream in = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties pro = new Properties();
        try {
            pro.load(in);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        driver = pro.getProperty("driverClass");
        url = pro.getProperty("url");
        user = pro.getProperty("user");
        password = pro.getProperty("password");

    }

    public static Connection getConnection() throws Exception {
        Class.forName(driver);
        return DriverManager.getConnection(url, user, password);
    }

    public static void closeResource(Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            if (rs != null && !rs.isClosed()) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (ps != null && !ps.isClosed()) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
  1. 使用
    /**
     * 查询所有
     * @return
     */
    public List<User> find() {
        List<User> list = new ArrayList<>(10);
        String sql = "SELECT `id`,  `name`,  `password`,  `address`,  `phone` from user";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt(1));
                user.setName(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setAddress(rs.getString("address"));
                user.setPhone("phone");
                list.add(user);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeResource(conn, ps, rs);
        }
        return list;
    }

3.6 ResultSet和ResultSetMetaData

3.6.1 ResultSet

  • 查询需要调用PreparedStatement 的 executeQuery() 方法，查询结果是一个ResultSet 对象

  • ResultSet 对象以逻辑表格的形式封装了执行数据库操作的结果集，ResultSet 接口由数据库厂商提供实现

  • ResultSet 返回的实际上就是一张数据表。有一个指针指向数据表的第一条记录的前面。

  • ResultSet 对象维护了一个指向当前数据行的游标，初始的时候，游标在第一行之前，可以通过 ResultSet 对象的 next() 方法移动到下一行。调用 next()方法检测下一行是否有效。若有效，该方法返回 true，且指针下移。相当于Iterator对象的 hasNext() 和 next() 方法的结合体。

  • 当指针指向一行时, 可以通过调用 getXxx(int index) 或 getXxx(int columnName) 获取每一列的值。

    • 例如: getInt(1), getString(“name”)
    • 注意：Java与数据库交互涉及到的相关Java API中的索引都从1开始。

    ResultSet 接口的常用方法：

  • next(): 将光标移动到结果集的下一行，并返回一个布尔值，指示是否存在更多的行。通常与循环结构一起使用，以遍历结果集中的所有行。

java复制代码while (resultSet.next()) {
   // 处理当前行的数据
}
  1. getString(columnName) / getString(columnIndex): 获取指定列的值作为字符串。可以根据列名或列索引进行获取。
java复制代码String value = resultSet.getString("column_name");
// 或者
String value = resultSet.getString(1);  // 列索引从 1 开始
  1. getInt(columnName) / getInt(columnIndex): 获取指定列的值作为整数类型。
java复制代码int value = resultSet.getInt("column_name");
// 或者
int value = resultSet.getInt(1);
  1. getDouble(columnName) / getDouble(columnIndex): 获取指定列的值作为双精度浮点数类型。
java复制代码double value = resultSet.getDouble("column_name");
// 或者
double value = resultSet.getDouble(1);
  1. getDate(columnName) / getDate(columnIndex): 获取指定列的值作为日期类型。
java复制代码Date value = resultSet.getDate("column_name");
// 或者
Date value = resultSet.getDate(1);
  1. wasNull(): 返回一个布尔值，指示最后一个获取的列是否包含空值。
java复制代码if (resultSet.wasNull()) {
    // 最后一个获取的列包含空值
}

image-20230825204557784

3.6.2 ResultSetMetaData

  • 可用于获取关于 ResultSet 对象中列的类型和属性信息的对象

  • ResultSetMetaData meta = rs.getMetaData();

    • getColumnName(int column)：获取指定列的名称
    • getColumnLabel(int column)：获取指定列的别名
    • getColumnCount()：返回当前 ResultSet 对象中的列数。
    • getColumnTypeName(int column)：检索指定列的数据库特定的类型名称。
    • getColumnDisplaySize(int column)：指示指定列的最大标准宽度，以字符为单位。
    • isNullable(int column)：指示指定列中的值是否可以为 null。
    • isAutoIncrement(int column)：指示是否自动为指定列进行编号，这样这些列仍然是只读的。

image-20230825204634849

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

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

问题2：关于ResultSetMetaData

  1. 如何获取 ResultSetMetaData： 调用 ResultSet 的 getMetaData() 方法即可
  2. 获取 ResultSet 中有多少列：调用 ResultSetMetaData 的 getColumnCount() 方法
  3. 获取 ResultSet 每一列的列的别名是什么：调用 ResultSetMetaData 的getColumnLabel() 方法

image-20230825204703311

3.7 BaseDao

Dao层的基类，把Dao层重复的代码逻辑放到BaseDao中，减少开发

BaseDao

public class BaseDao<T> {

    Class<T> clazz;

    //得到当前对象，父类声明的返回类型

    {
        //获取当前BaseDAO的子类继承的父类中的第一个泛型
        //    - this = UserDao extends BaseDao<User>
        //    - this = OrderDao extends BaseDao<Order>
        Type genericSuperclass = this.getClass().getGenericSuperclass();
        
        ParameterizedType paramType = (ParameterizedType) genericSuperclass;

        Type[] typeArguments = paramType.getActualTypeArguments();//获取了父类的泛型参数
        clazz = (Class<T>) typeArguments[0];//泛型的第一个参数

    }

    /**
     * 查询List
     * @return
     */
    public List<T> findList(String sql, Object... params) {
        List<T> list = new ArrayList<>(10);

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConnection();
            ps = conn.prepareStatement(sql);

            //处理查询条件
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }

            rs = ps.executeQuery();

            //查询结果的元数据（列名，列类型，列数量...)
            ResultSetMetaData metaData =  rs.getMetaData();
            int columnCount = metaData.getColumnCount(); //多少列

            while (rs.next()) {
                //
                T instance = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    //列的值
                    Object value = rs.getObject(i + 1);

                    //列名（约定：列名等于属性名）
                    String name = metaData.getColumnLabel(i + 1);

                    Field field = clazz.getDeclaredField(name);
                    field.setAccessible(true);
                    field.set(instance, value);
                }
                list.add(instance);
            }

        } 
        return list;
    }

    /**
     * 查询List
     * @return
     */
    public T findOne(String sql, Object... params) {
        T instance  = null;

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConnection();
            ps = conn.prepareStatement(sql);
            //处理查询条件
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            rs = ps.executeQuery();

            //查询结果的元数据（列名，类类型，类数量...)
            ResultSetMetaData metaData =  rs.getMetaData();
            int columnCount = metaData.getColumnCount(); //多少列

            while (rs.next()) {
                instance = clazz.newInstance();

                for (int i = 0; i < columnCount; i++) {
                    //列的值
                    Object value = rs.getObject(i + 1);

                    //列名（约定：列名等于属性名）
                    String name = metaData.getColumnLabel(i + 1);

                    Field field = clazz.getDeclaredField(name);
                    field.setAccessible(true);
                    field.set(instance, value);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeResource(conn, ps, rs);
        }
        return instance;
    }

    /**
     *
     * @param sql：语句
     * @param params：?参数
     * @return
     */
    public int update(String sql, Object... params) {
        int rows = 0;

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }

            rows = ps.executeUpdate(); //执行修改,返回语句影响的行数

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeResource(conn, ps, rs);
        }
        return rows;
    }
}

public class UserDao extends BaseDao<User> {

    /**
     * 查询所有
     * @return
     */
    public List<User> find() {
        String sql = "SELECT `id`,  `name`,  `password`,  `address`,  `phone` from user";
        return super.findList(sql);
    }

    /**
     * 查询主键对应的记录
     * @param id
     * @return
     */
    public User get(Integer id) {
        String sql = "SELECT `id`,  `name`,  `password`,  `address`,  `phone` from user where id = ?";
        return super.findOne(sql, id);
    }

    /**
     * 添加
     * @param user
     * @return 返回语句影响的函数
     */
    public int insert(User user) {
        String sql = "insert into user(`name`, `password`, `address`, `phone`) values(?,?,?,?)";
        return super.update(sql, user.getName(), user.getPassword(), user.getAddress(), user.getPhone());
    }

    /**
     * 删除
     * @param id
     * @return 返回语句影响的函数
     */
    public int delete(Integer id) {
        String sql = "delete from user where id = ?";
        return super.update(sql, id);
    }

    /**
     * 添加
     * @param user
     * @return 返回语句影响的函数
     */
    public int update(User user) {
        String sql = "update user set `name`=?, `password`=?,  `address`=?,  `phone`=? WHERE `id` = ?";
        return super.update(sql, user.getName(), user.getPassword(), user.getAddress(), user.getPhone(), user.getId());

    }
}

4 MySQL BLOB类型

  • MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。
  • 插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写的。
  • MySQL的四种BLOB类型(除了在存储的最大信息量上不同外，他们是等同的)
类型大小（单位：字节）
TinyBlob最大 255k
Blob最大 65k
MediumBlob最大 16M
LongBlob最大 4G
  • 实际使用中根据需要存入的数据大小定义不同的BLOB类型。
  • 需要注意的是：如果存储的文件过大，数据库的性能会下降。
  • 如果在指定了相关的Blob类型以后，还报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数： max_allowed_packet=16M。同时注意：修改了my.ini文件之后，需要重新启动mysql服务。
public class BlobDemo1 {

    BaseDao dao = new BaseDao();

    /**
     * 添加文件
     *
     */
    @Test
    public void test1() {
        String sql = "insert into customers(name,email,birth, photo) values(?,?,?,?)";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        FileInputStream in = null;
        try {
            conn = JdbcUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,"mickey");
            ps.setString(2,"mickey@163.com");
            ps.setDate(3,new Date(new java.util.Date().getTime()));

            in = new FileInputStream("a.jpg");

            ps.setBlob(4, in);

            ps.executeUpdate(); //执行修改,返回语句影响的行数

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeResource(conn, ps, rs);
            try {
                in.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }

    }

    /**
     * 查询文件
     */
    @Test
    public void test2() {
        String sql = "select id, name, photo from customers where id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        InputStream in = null;
        FileOutputStream out = null;

        try {
            conn = JdbcUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, 20);

            rs = ps.executeQuery();

            if (rs.next()) {
                System.out.println(rs.getInt("id"));
                System.out.println(rs.getString(2));

                //读取blob
                Blob blob = rs.getBlob(3);
                in = blob.getBinaryStream();

                out = new FileOutputStream("a1.jpg");

                int length = -1;
                byte[] buffer = new byte[1024];
                while ((length = in.read(buffer)) != -1) {
                    out.write(buffer, 0, length);
                }

            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeResource(conn, ps, rs);

            try {
                in.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
            try {
                out.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }


    }


    /**
     * 修改BLOB
     *
     */
    @Test
    public void test3() {
        String sql = "update customers set photo = ? where id=?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        FileInputStream in = null;
        try {
            conn = JdbcUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(2, 20);

            in = new FileInputStream("b.jpg");

            ps.setBlob(1, in);

            ps.executeUpdate(); //执行修改,返回语句影响的行数

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeResource(conn, ps, rs);
            try {
                in.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }

    }
}

5 批量插入

当需要成批插入或者更新记录时，可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理。

通过使用批处理方式，可以减少与数据库的交互次数，提高插入数据的效率。

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

  • addBatch(String)：添加需要批量处理的SQL语句或是参数；
  • executeBatch()：执行批量处理语句；
  • clearBatch():清空缓存的数据

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

  • 多条SQL语句的批量处理；
  • 一个SQL语句的批量传参；
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true

package com.gk.e_sqlfiledemo;

import com.gk.c_crud.util.JdbcUtil;
import org.junit.Test;

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

public class BatchDemo {
    /*@Test
    public void test1(){
        long start = System.currentTimeMillis();

        *//*Connection con = JdbcUtil.getConnection();
        con.setAutoCommit(false);*//*
        String sql = "insert into goods(name) values";
    }*/
        @Test
        public void test1() throws Exception {
            long start = System.currentTimeMillis();

            Connection conn = JdbcUtil.getConnection();
            conn.setAutoCommit(false);

            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();
            }

            conn.commit();//5588

            long end = System.currentTimeMillis();
            System.out.println("花费的时间为：" + (end - start));//33717

            JdbcUtil.closeResource(conn, ps, null);
        }

        @Test
        public void test2() throws Exception {
            long start = System.currentTimeMillis();

            Connection conn = JdbcUtil.getConnection();
            conn.setAutoCommit(false);

            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();
                ps.addBatch(); //添加语句到Batch,攒sql语句

                if (i % 10000 == 0) {
                    ps.executeBatch(); //每500个语句，批量执行一次
                    ps.clearBatch(); //清空批量
                }
            }
            conn.commit();

            long end = System.currentTimeMillis();
            System.out.println("花费的时间为：" + (end - start));//2205


            JdbcUtil.closeResource(conn, ps, null);
        }

}

  • 在 JDBC 中，每个 SQL 语句的执行都会被当作一个事务来处理。如果在每次执行 SQL 时都开启一个新的事务，会增加系统的负担，降低数据库的性能。

    为了避免这种情况，在编写代码时一般会将自动提交模式（Auto Commit Mode）关闭，即使用 setAutoCommit(false) 方法将自动提交功能设置为关闭状态。这样，可以让多个 SQL 语句共享一个事务，减少产生的开销并提高数据库的性能。

    设置自动提交模式为 false 后，需要手动调用 commit() 方法来提交当前事务，或者在发生异常时调用 rollback() 方法进行回滚操作。

    在大量数据操作时，可以使用 JDBC 提供的批处理机制，将多个 SQL 语句打包成一个请求批量提交到数据库执行，从而进一步优化性能。但是，请注意，批处理仅对相同类型的 SQL 语句有效，且需要在手动提交事务前执行。

    关闭自动提交功能可以优化 JDBC 的性能，并且需要手动管理事务的提交和回滚。

6 数据库连接池

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

image-20230829084910027

  • 在使用开发基于数据库的web程序时，传统的模式基本是按以下步骤：

    • 在主程序（如servlet、beans）中建立数据库连接
    • 进行sql操作
    • 断开数据库连接

  • 这种模式开发，存在的问题:

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

6.2 数据库连接池技术

  • 为解决传统开发中的数据库连接问题，可以采用数据库连接池技术。
  • 数据库连接池的基本思想：就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。
  • 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。
  • 数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。

img

工作原理

image-20230829085338848

  • 数据库连接池技术的优点

    • 资源重用由于数据库连接得以重用，避免了频繁创建，释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增加了系统运行环境的平稳性。

    • 更快的系统反应速度数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于连接池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而减少了系统的响应时间

    • 新的资源分配手段对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置，实现某一应用最大可用数据库连接数的限制，避免某一应用独占所有的数据库资源

    • 统一的连接管理，避免数据库连接泄漏在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露

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

  • JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口，该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现：

    • DBCP 是Apache提供的数据库连接池。tomcat 服务器自带dbcp数据库连接池。速度相对c3p0较快，但因自身存在BUG，Hibernate3已不再提供支持。
    • C3P0 是一个开源组织提供的一个数据库连接池，**速度相对较慢，稳定性还可以。**hibernate官方推荐使用
    • Druid 是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池，但是速度不确定是否有BoneCP快

  • DataSource 通常被称为数据源，它包含连接池和连接池管理两个部分，习惯上也经常把 DataSource 称为连接池

  • DataSource用来取代DriverManager来获取Connection，获取速度快，同时可以大幅度提高数据库访问速度。

  • 特别注意：

    • 数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。
    • 当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但conn.close()并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。

6.3.1 C3P0数据库连接池

pom.xml

        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.2.1</version>
        </dependency>

resource下创建配置文件：【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>

连接测试 两种方式

public class C3p0Demo1 {

    @Test
    //使用C3P0数据库连接池的方式，获取数据库的连接：不推荐
    public void test1() throws Exception{
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        cpds.setDriverClass("com.mysql.jdbc.Driver");
        cpds.setJdbcUrl("jdbc:mysql://localhost:3306/test");
        cpds.setUser("root");
        cpds.setPassword("123456");

        //  cpds.setMaxPoolSize(100);

        Connection conn = cpds.getConnection();
        System.out.println(conn);
    }

    @Test
    public void test2() throws SQLException {
        DataSource cpds = new ComboPooledDataSource("hellc3p0");
        Connection conn = cpds.getConnection();
        System.out.println(conn);
    }

}

6.3.2 DBCP数据库连接池

  • DBCP 是 Apache 软件基金组织下的开源连接池实现，该连接池依赖该组织下的另一个开源系统：Common-pool。如需使用该连接池实现，应在系统中增加如下两个 jar 文件：

    • Commons-dbcp.jar：连接池的实现
    • Commons-pool.jar：连接池实现的依赖库

  • **Tomcat 的连接池正是采用该连接池来实现的。**该数据库连接池既可以与应用服务器整合使用，也可由应用程序独立使用。

  • 数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。

  • 当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但上面的代码并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。

  • 配置属性说明

属性默认值说明
initialSize0连接池启动时创建的初始化连接数量
maxActive8连接池中可同时连接的最大的连接数
maxIdle8连接池中最大的空闲的连接数，超过的空闲连接将被释放，如果设置为负数表示不限制
minIdle0连接池中最小的空闲的连接数，低于这个数量会被创建新的连接。该参数越接近maxIdle，性能越好，因为连接的创建和销毁，都是需要消耗资源的；但是不能太大。
maxWait无限制最大等待时间，当没有可用连接时，连接池等待连接释放的最大时间，超过该时间限制会抛出异常，如果设置-1表示无限等待
poolPreparedStatementsfalse开启池的Statement是否prepared
maxOpenPreparedStatements无限制开启池的prepared 后的同时最大连接数
minEvictableIdleTimeMillis连接池中连接，在时间段内一直空闲， 被逐出连接池的时间
removeAbandonedTimeout300超过时间限制，回收没有用(废弃)的连接
removeAbandonedfalse超过removeAbandonedTimeout时间后，是否进 行没用连接（废弃）的回收

pom.xml

        <dependency>
            <groupId>org.apache.tomcat</groupId>
            <artifactId>dbcp</artifactId>
            <version>6.0.29</version>
        </dependency>

resource下配置文件：【dbcp.properties】

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

initialSize=10

test

public class DBCPTest {

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

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

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

pom.xml中

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>

配置文件为：【druid.properties】

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

initialSize=10
maxActive=10

test

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

详细配置参数

配置缺省说明
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(建议配置下)
initialSize0初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
maxActive8最大连接池数量
maxIdle8已经不再使用，配置了也没效果
minIdle最小连接池数量
maxWait获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁。
poolPreparedStatementsfalse是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
maxOpenPreparedStatements-1要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100
validationQuery用来检测连接是否有效的sql，要求是一个查询语句。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会其作用。
testOnBorrowtrue申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
testOnReturnfalse归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
testWhileIdlefalse建议配置为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，是组合关系，并非替换关系

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.mfbz.cn/a/784319.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

鸿蒙语言基础类库：【@ohos.url (URL字符串解析)】

鸿蒙语言基础类库：【@ohos.url (URL字符串解析)】

URL字符串解析 说明&#xff1a; 本模块首批接口从API version 7开始支持。后续版本的新增接口&#xff0c;采用上角标单独标记接口的起始版本。开发前请熟悉鸿蒙开发指导文档&#xff1a;gitee.com/li-shizhen-skin/harmony-os/blob/master/README.md点击或者复制转到。 导入…
阅读更多...
第一百四十九节 Java数据类型教程 - Java子字符串、字符串转换

第一百四十九节 Java数据类型教程 - Java子字符串、字符串转换

Java数据类型教程 - Java子字符串 获取子字符串 我们可以使用substring()方法来获取字符串的子部分。 我们可以将开始索引作为参数&#xff0c;并返回一个从开始索引开始到字符串结尾的子串。 我们还可以将开始索引和结束索引作为参数。 它返回从开始索引开始的子字符串和小…
阅读更多...
使用预加载库优化 PostgreSQL 函数#postgresql认证

使用预加载库优化 PostgreSQL 函数#postgresql认证

在 POSTGRESQL 中执行函数和过程 为了理解 PostgreSQL 的工作原理&#xff0c;我们首先要看一个简单的函数调用。下一个清单显示了一些简单的PostGIS代码&#xff1a; PgSQL test# timing Timing is on. test# SELECT * FROM hans.points WHERE id 1;id │ …
阅读更多...
【工具分享】零零信安攻击面管理平台

【工具分享】零零信安攻击面管理平台

文章目录 00SEC-ASM™功能介绍功能演示 最近闲来无事&#xff0c;到处网上冲浪&#xff0c;无意间发现了长亭云图攻击面管理平台&#xff0c;无奈需要授权才能使用&#xff0c;于是就找到了平替&#xff1a;零零信安攻击面管理平台。 长亭云图攻击面管理平台&#xff1a;https:…
阅读更多...
代码随想录-Day50

代码随想录-Day50

1143. 最长公共子序列 给定两个字符串 text1 和 text2&#xff0c;返回这两个字符串的最长 公共子序列 的长度。如果不存在 公共子序列 &#xff0c;返回 0 。 一个字符串的 子序列 是指这样一个新的字符串&#xff1a;它是由原字符串在不改变字符的相对顺序的情况下删除某些…
阅读更多...
Kotlin linkedMapOf filterKeys

Kotlin linkedMapOf filterKeys

Kotlin linkedMapOf filterKeys fun main(args: Array<String>) {val lhm linkedMapOf<String, Any>(Pair("name", "phil"), //因为key相同都为 name&#xff0c;被后面的覆盖。Pair("year", 2024),Pair("name", "f…
阅读更多...
【TB作品】51单片机 Proteus仿真 00013红外proteus仿真循迹避障小车

【TB作品】51单片机 Proteus仿真 00013红外proteus仿真循迹避障小车

实验报告&#xff1a;智能小车系统设计与实现 一、背景介绍 本实验旨在设计并实现一个基于STC89C52单片机控制的智能小车系统。该系统通过超声波传感器进行避障&#xff0c;通过红外接收器实现远程控制&#xff0c;同时具备循迹功能。整个系统的核心是单片机&#xff0c;它通…
阅读更多...
初识c++（命名空间，缺省参数，函数重载）

初识c++（命名空间，缺省参数，函数重载）

一、命名空间 1、namespace的意义 在C/C中&#xff0c;变量、函数和后面要学到的类都是大量存在的&#xff0c;这些变量、函数和类的名称将都存在于全 局作用域中&#xff0c;可能会导致很多冲突。使用命名空间的目的是对标识符的名称进行本地化&#xff0c;以避免命名 冲突…
阅读更多...
python对象

python对象

类 我们目前所学习的对象都是Python内置的对象但是内置对象并不能满足所有的需求&#xff0c;所以我们在开发中经常需要自定义一些对象类&#xff0c;简单理解它就相当于一个图纸。在程序中我们需要根据类来创建对象类就是对象的图纸&#xff01;我们也称对象是类的实例&#…
阅读更多...
caeses软件许可优化解决方案

caeses软件许可优化解决方案

Caeses软件介绍 CAESES是一款十分很不错的三维建模仿真的软件。它功能很大、优化效率高、可以自动化优化、分析工具快速 CAESES拥有多种不同的试验设计及单目标、多目标优化算法&#xff0c;能够根据仿真计算评估的结果。软件可以帮助用户轻松的打造出自各种船舶、汽车、航空航…
阅读更多...
grafana数据展示

grafana数据展示

目录 一、安装步骤 二、如何添加喜欢的界面 三、自动添加注册客户端主机 一、安装步骤 启动成功后 可以查看端口3000是否启动 如果启动了就在浏览器输入IP地址&#xff1a;3000 账号密码默认是admin 然后点击 log in 第一次会让你修改密码 根据自定义密码然后就能登录到界面…
阅读更多...
1-3分钟爆款视频素材在哪找啊？这9个热门爆款素材网站分享给你

1-3分钟爆款视频素材在哪找啊？这9个热门爆款素材网站分享给你

在如今快节奏的时代&#xff0c;短视频已成为吸引观众注意力的黄金手段。然而&#xff0c;要制作出1-3分钟的爆款视频&#xff0c;除了创意和剪辑技巧外&#xff0c;选择合适的素材至关重要。那么&#xff0c;哪里可以找到那些能让你的视频脱颖而出的爆款素材呢&#xff1f;不用…
阅读更多...
顶会FAST24最佳论文｜阿里云块存储架构演进的得与失-1.引言

顶会FAST24最佳论文｜阿里云块存储架构演进的得与失-1.引言

今年早些时候&#xff0c;2月份举办的全球计算机存储顶会USENIX FAST 2024&#xff0c;最佳论文来自阿里云&#xff0c;论文名称《What’s the Story in EBS Glory: Evolutions and Lessons in Building Cloud Block Store》 &#xff0c;论文详尽地探讨了阿里云在过去十年中开…
阅读更多...
ASAN排查程序中内存问题使用总结

ASAN排查程序中内存问题使用总结

简介 谷歌有一系列Sanitizer工具&#xff0c;可用于排查程序中内存相关的问题。常用的Sanitizer工具包括&#xff1a; Address Sanitizer&#xff08;ASan&#xff09;&#xff1a;用于检测内存使用错误。Leak Sanitizer&#xff08;LSan&#xff09;&#xff1a;用于检测内存…
阅读更多...
YOLOv9：一个关注信息丢失问题的目标检测

YOLOv9：一个关注信息丢失问题的目标检测

本文来自公众号“AI大道理” 当前的深度学习方法关注的是如何设计最合适的目标函数&#xff0c;使模型的预测结果最接近地面的真实情况。同时&#xff0c;必须设计一个适当的体系结构&#xff0c;以方便获取足够的预测信息。 现有方法忽略了一个事实&#xff0c;即输入数据在逐…
阅读更多...
docker安装以及简单使用

docker安装以及简单使用

如何安装安装 yum install -y yum-utils yum-config-manager --add-repo https://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo # 列出可用的版本 yum list docker-ce.x86_64 --showduplicates | sort -r yum install -y docker-ce-23.0.6-1.el8 #开机自动启动 …
阅读更多...
Yolov10训练，转化onnx，推理

Yolov10训练，转化onnx，推理

yolov10对于大目标的效果好&#xff0c;小目标不好 一、如果你训练过yolov5&#xff0c;yolov8&#xff0c;的话那么你可以直接用之前的环境就行 目录 一、如果你训练过yolov5&#xff0c;yolov8&#xff0c;的话那么你可以直接用之前的环境就行 二、配置好后就可以配置文件…
阅读更多...
DBA 数据库管理

DBA 数据库管理

数据库&#xff1a;存储数据的仓库 数据库服务软件&#xff1a; 关系型数据库&#xff1a; 存在硬盘 &#xff0c;制作表格的 数据库的参数 [rootmysql50 ~]# cat /etc/my.cnf.d/mysql-server.cnf 主配置文件 [mysqld] datadir/var/lib/mysql 存放数据库目录…
阅读更多...
黑马点评商户缓存查询作业——Redis中查询商户类型

黑马点评商户缓存查询作业——Redis中查询商户类型

记录下自己在gpt帮助下完成的第一个需求~~~ 1. ShopTypeController 2. IShopTypeService 3. ShopTypeServiceImpl&#xff08;模仿ShopServiceImpl来写的&#xff09; 一共分为“1.redis中查询缓存”→“2.判断缓存是否存在&#xff0c;存在直接返回”→“3.缓存不存在则去查数…
阅读更多...
sql盲注

sql盲注

文章目录 布尔盲注时间盲注 布尔盲注 介绍&#xff1a;在网页只给你两种回显的时候是用&#xff0c;类似于布尔类型的数据&#xff0c;1表示正确&#xff0c;0表示错误。 特点&#xff1a;思路简单&#xff0c;步骤繁琐且麻烦。 核心函数&#xff1a; length()函数substr()函…
阅读更多...
最新文章

Copyright @ 2022~2023