Java-JDBC和连接池

最新推荐文章于 2024-07-29 14:22:26 发布

m0_67402588

最新推荐文章于 2024-07-29 14:22:26 发布

阅读量185

点赞数

分类专栏： java 后端文章标签： java 后端

本文链接：https://blog.csdn.net/m0_67402588/article/details/123603117

版权

java 同时被 2 个专栏收录

270 篇文章 13 订阅

订阅专栏

后端

14 篇文章 0 订阅

订阅专栏

JDBC和连接池

JDBC概述

JDBC为访问不同的数据库提供了统一的接口，为使用者屏蔽了细节问题
Java程序员使用JDBC，可以连接任何提供了JDBC驱动程序的数据库系统，从而完成对数据库的各种操作。
JDBC原理图：
JDBC是Java提供一套用于数据库操作的接口API，Java程序员只需要面向这个接口编程即可。不同的数据库厂商，需要针对这套接口，提供不同实现。

JDBC入门

基本步骤

注册驱动 – 加载Driver类
获取连接 – 得到Connection类
执行增删改查 – 发送SQL命令给mysql执行
释放资源 – 关闭相关连接

连接方式

方式1

//        0. 加入jar包
//        1. 注册驱动 -- 加载Driver类
        Driver driver = new Driver();
//        2. 获取连接 -- 得到Connection类
        // 2.1 jdbc:mysql://   规定好的协议，通过jdbc的方式连接mysql
        // 2.2 localhost 主机，可以是ip地址
        // 2.3 3306 表示mysql监听的端口
        // 2.4 study_java  表示连接的数据库
        String url = "jdbc:mysql://localhost:3306/Java_Study";
        // 2.5 将用户名和密码封装到Properties对象中
        //  key 是 规定好的  user password不要修改
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "wxy981023");
        Connection connect = driver.connect(url, properties);
//        3. 执行增删改查 -- 发送SQL命令 给mysql执行
        String sql = "insert into actor values (2, 'wxy', '男', '1998-12-02', '15588089909')";
        // 3.1 创建statement
        Statement statement = connect.createStatement();
        // 3. 利用statement执行SQL语句
        int rows = statement.executeUpdate(sql);
        System.out.println(rows);
//        4. 释放资源 -- 关闭相关连接
        statement.close();
        connect.close();

方式2

public void connect02() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
    // 使用反射加载
    Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
    Driver driver = (Driver) aClass.newInstance();
    String url = "jdbc:mysql://localhost:3306/Java_Study";
    Properties properties = new Properties();
    properties.setProperty("user", "root");
    properties.setProperty("password", "wxy981023");
    Connection connect = driver.connect(url, properties);
    Statement statement = connect.createStatement();
    String sql = "insert into actor values (2, 'wxy', '男', '1998-12-02', '15588089909')";
    int rows = statement.executeUpdate(sql);
    System.out.println(rows);
    statement.close();
    connect.close();
}

方式3

// 使用DriverManger替代Diver 进行统一管理
@Test
public void connect03() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
    Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
    Driver driver = (Driver) aClass.newInstance();
    String url = "jdbc:mysql://localhost:3306/Java_Study";
    String user = "root";
    String pwd = "wxy981023";
    DriverManager.registerDriver(driver); // 注册驱动
    Connection connection = DriverManager.getConnection(url, user, pwd);
    System.out.println(connection);
    connection.close();
}

方式4 △

// 这种使用较多
@Test
public void connect04() throws ClassNotFoundException, SQLException {
    // 存在静态Driver类，底层帮助实现了
    // 在加载Driver时，完成了注册，反射存在静态代码块
    // 这句话也可以省略，当jar包版本大于5.1.6之后，可以实现
    // 建议写出
    Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
    String url = "jdbc:mysql://localhost:3306/Java_Study";
    String user = "root";
    String pwd = "wxy981023";
    Connection connection = DriverManager.getConnection(url, user, pwd);
    System.out.println(connection);
    connection.close();
}

方式5 △

// 在方式4 的基础上， 增加配置文件，使连接mysql更加灵活
@Test
public void connect05() throws IOException, ClassNotFoundException, SQLException {
    // 1. 通过Properties对象获取配置文件的信息
    Properties properties = new Properties();
    properties.load(new FileInputStream("/Users/xinyu/IdeaProjects/IdeaWorkspace" +
            "/Study_Java/Study_Java_thirteen/src/mysql.properties"));
    String user = properties.getProperty("user");
    String password = properties.getProperty("password");
    String url = properties.getProperty("url");
    String driver = properties.getProperty("driver");
    // 2. 加载Driver
    Class.forName(driver);
    // 3. 连接
    Connection connection = DriverManager.getConnection(url, user, password);
    System.out.println(connection);
}

resultSet底层

表示数据库结果集的数据表，通常通过执行查询数据库的语句生成
ResultSet对象保持一个光标指向其当前的数据行。最初，光标魏宇第一行之前
next方法将光标移动到一下行，并且由于在ResultSet对象中没有更好行时返回false，因此可以在while循环中使用循环来遍历结果集

// 1. 通过Properties对象获取配置文件的信息
Properties properties = new Properties();
properties.load(new FileInputStream("/Users/xinyu/IdeaProjects/IdeaWorkspace" +
“/Study_Java/Study_Java_thirteen/src/mysql.properties”));
String user = properties.getProperty(“user”);
String password = properties.getProperty(“password”);
String url = properties.getProperty(“url”);
String driver = properties.getProperty(“driver”);
// 2. 加载Driver
Class.forName(driver);
// 3. 连接
Connection connection = DriverManager.getConnection(url, user, password);
// 4. 得到statement
Statement statement = connection.createStatement();
// 5. sql
String sql = “select id,name,sex,borndate from actor”;
/* ResultSet
1. size
2. rows --> elementData 通过 ASCII 存储
3. rowData 所有数据
4. rows 所有行
5. elementData 所有列
*/
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String sex = resultSet.getString(3);
Date date = resultSet.getDate(4);
System.out.println(id + " " + name + " " + sex + " " + date);
}

// 6. 关闭
resultSet.close();
statement.close();
connection.close();

JDBC API △

Statement

statement对象，用于执行静态sql语句并返回其生成的结果的对象
在建立连接后，需要第数据库进行访问，执行命名或是sql语句，可以通过

statement 【存在sql注入的问题】

prepareStatement 【预处理】

CallableStatement 【存储过程】
Statement对象执行SQL语句，存在SQL注入风险
SQL注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的SQL语句段或命令，恶意攻击数据库
要防范SQL注入，只要用PrepareStatement(从Statement扩展而来)取代Statement就可以

// 管理员输入登录用户名和密码万能密码
// String admin_name = " 1’ or";
// String admin_pwd = “or ‘1’ = 1’”;
Scanner scanner = new Scanner(System.in);
String admin_name = scanner.nextLine();
String admin_pwd = scanner.nextLine();
String mysqlPath = “/Users/xinyu/IdeaProjects/IdeaWorkspace/Study_Java/Study_Java_thirteen/src/mysql.properties”;
Properties properties = new Properties();
properties.load(new FileInputStream(mysqlPath));
String user = properties.getProperty(“user”);
String password = properties.getProperty(“password”);
String driver = properties.getProperty(“driver”);
String url = properties.getProperty(“url”);
Class<?> aClass = Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sql = “select name, pwd from admin where name = '”
+ admin_name + “’ and pwd = '” + admin_pwd + “’”;
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
String name = resultSet.getString(“name”);
String pwd = resultSet.getString(“pwd”);
System.out.println(name + " " + pwd);
}
resultSet.close();
statement.close();
connection.close();

PreparedStatement 预处理

PrepareStatement执行的SQL语句中的参数用问号()来表示，调用PreparedStatement对象的setXxx()方法来设置这些参数。setXxx()方法有两个参数，第一个参数是要设置的SQL语句中的参数的索引（从1开始），第二个参数是设置SQL语句中的参数的值
调用executeQuery()，返回ResultSet对象
调用executeUpdate() 执行更新，包括增删改，返回rows-受影响的行数
好处：
1. 不再使用+拼接sql语句，减少语法错误
2. 有效的解决了sql注入问题
3. 大大减少了编译次数，效率较高
Scanner scanner = new Scanner(System.in);
String admin_name = scanner.nextLine();
String admin_pwd = scanner.nextLine();
String mysqlPath = “/Users/xinyu/IdeaProjects/IdeaWorkspace/Study_Java/Study_Java_thirteen/src/mysql.properties”;
Properties properties = new Properties();
properties.load(new FileInputStream(mysqlPath));
String user = properties.getProperty(“user”);
String password = properties.getProperty(“password”);
String driver = properties.getProperty(“driver”);
String url = properties.getProperty(“url”);
Class<?> aClass = Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
String sql1 = “select name, pwd from admin where name = ? and pwd = ?”;
String sql2 = “insert into admin values (?, ?)”;
String sql3 = “update admin set pwd = ? where name = ?”;
java.sql.PreparedStatement preparedStatement = connection.prepareStatement(sql1);
java.sql.PreparedStatement preparedStatement2 = connection.prepareStatement(sql2);
java.sql.PreparedStatement preparedStatement3 = connection.prepareStatement(sql3);
preparedStatement.setString(1, admin_name);
preparedStatement.setString(2, admin_pwd);
preparedStatement2.setString(1, “wxy2”);
preparedStatement2.setString(2, “456”);
preparedStatement3.setString(1, “abc”);
preparedStatement3.setString(2, “wxy”);
// 不再添加sql了
ResultSet resultSet = preparedStatement.executeQuery();
int rows = preparedStatement2.executeUpdate();
int rows3 = preparedStatement3.executeUpdate();
while (resultSet.next()) {
String name = resultSet.getString(“name”);
String pwd = resultSet.getString(“pwd”);
System.out.println(name + " " + pwd);
}
System.out.println(rows);
System.out.println(rows3);

resultSet.close();
preparedStatement.close();
connection.close();

JDBCUtils

Utils

public class JDBCUtils {
    // 1. 定义常用属性（4个） 做成静态
    private static String user; //用户
    private static String password; //用户
    private static String url; //用户
    private static String driver; //用户

    // 2. 通过静态代码块完成初始化
    static {
        String mysqlPath = "/Users/xinyu/IdeaProjects/IdeaWorkspace/Study_Java/Study_Java_thirteen/src/mysql.properties";
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream(mysqlPath));
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
        } catch (IOException e) {
            // 在实际开发中，可以这样处理
            // 1. 将编译异常转成 运行异常
            // 2. 这个是调用者，可以选择捕获该异常，也可以选择默认处理该异常，比较方便
            throw new RuntimeException();
        }
    }

    // 3. 连接
    public static Connection getConnection() {
        Connection connection = null;
        try {
//            Class<?> aClass = Class.forName(driver);
            connection = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            throw new RuntimeException();
        }
        return connection;
    }

    // 4. 关闭资源
    // 4,1 ResultSet PreparedStatement Connection
    // 4.2 若Connection
    // 4.3 若需要关闭资源，就传入对象，否则传入null
    public static void closeSource(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null)
                resultSet.close();
            if (statement != null)
                statement.close();
            if (connection != null)
                connection.close();
        } catch (SQLException e) {
            throw new RuntimeException();
        }

    }
}

Use

public class JDBCUse {

    @Test
    public void testDML() {
        // 1.获取连接
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;
        // 2. sql
        String sql = "insert into admin values (?, ?)";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "xwr");
            preparedStatement.setString(2, "789");
            int rows = preparedStatement.executeUpdate();
            System.out.println("受影响行数为：" + rows);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 3. 关闭资源
            JDBCUtils.closeSource(null, preparedStatement, connection);
        }

    }

    @Test
    public void testSelect() {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = null;
        // 2. sql
        String sql = "select * from admin";
        try {
            preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String name = resultSet.getString(1);
                String pwd = resultSet.getString(2);
                System.out.println(name + "  " + pwd);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 3. 关闭资源
            JDBCUtils.closeSource(null, preparedStatement, connection);
        }
    }


}

事务 △

基本介绍

JDBC程序中当一个Connection对象创建时，默认情况下是自动提交事务：每次执行一个SQL语句时，若执行成功，就会向数据库自动提交，而不能回滚
JDBC程序中为了让多个SQL语句作为一个整体执行，需要使用事务
调用Connection的setAutoCommit(false)可以取消自动提交事务
在所有的SQL语句都成功执行后，调用Connection的commit()方法提交事务
在其中某个操作失败或出现异常时，调用rollback()方法回滚事务

事务处理

@Test
public void noTransaction() {
    Connection connection = JDBCUtils.getConnection();
    String sql1 = "update account set balance = balance -1000 where id = 1";
    String sql2 = "update account set balance = balance +1000 where id = 2";
    PreparedStatement preparedStatement = null;
    try {
        preparedStatement = connection.prepareStatement(sql1);
        int rows1 = preparedStatement.executeUpdate();
        int i = 1 / 0; // 异常后不再执行
        preparedStatement = connection.prepareStatement(sql2);
        int rows2 = preparedStatement.executeUpdate();
        System.out.println("受影响行数：" + rows1);
        System.out.println("受影响行数：" + rows2);
    } catch (SQLException e) {
        throw new RuntimeException();
    } finally {
        JDBCUtils.closeSource(null, preparedStatement, connection);
    }
}

@Test
public void uesTransaction() {
    Connection connection = JDBCUtils.getConnection();
    String sql1 = "update account set balance = balance -1000 where id = 1";
    String sql2 = "update account set balance = balance +1000 where id = 2";
    PreparedStatement preparedStatement = null;
    try {
        preparedStatement = connection.prepareStatement(sql1);
        connection.setAutoCommit(false);
        connection.setSavepoint("a"); // 设置回滚点 可选
        int rows1 = preparedStatement.executeUpdate();
        int i = 1 / 0; // 异常后不再执行
        preparedStatement = connection.prepareStatement(sql2);
        int rows2 = preparedStatement.executeUpdate();
        System.out.println("受影响行数：" + rows1);
        System.out.println("受影响行数：" + rows2);
        // 最后进行提交
        connection.commit();
    } catch (SQLException e) {
        // 这里可以进行回滚，撤销执行的SQL
        try {
            connection.rollback(); // 回滚到开启事务
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        throw new RuntimeException();
    } finally {
        JDBCUtils.closeSource(null, preparedStatement, connection);
    }
}

批处理

基本介绍

当需要成批插入或者更新记录时，可以采用java的批量更新机制，这一机制云溪许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率。
JDBC的批量处理语句包括下面方法：
1. addBatch() 添加需要批量处理的sql语句或参数
2. executeBatch() 执行批量处理语句
3. clearBatch() 情况批处理包的语句
JDBC连接MySql时，若要使用批处理功能，请在url中加参数rewriteBatchedStatements=true
批处理往往和PrepareStatement一起搭配使用，可以既减少编译次数，又减少运行次数，效率提高

批处理操作

@Test
public void noBatch() throws SQLException {
    Connection connection = JDBCUtils.getConnection();
    String sql = "insert into admin2 values(null, ?, ?)";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    System.out.println("开始执行");
    long start = System.currentTimeMillis();
    for (int i = 0; i < 5000; i++) {
        preparedStatement.setString(1, "wxy" + 1);
        preparedStatement.setString(2, "wxy" + 1);
        preparedStatement.executeUpdate();
    }
    long end = System.currentTimeMillis();
    System.out.println(end - start);
    JDBCUtils.closeSource(null, preparedStatement, connection);
}

@Test
public void useBatch() throws SQLException {
    Connection connection = JDBCUtils.getConnection();
    String sql = "insert into admin2 values(null, ?, ?)";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    System.out.println("开始执行");
    long start = System.currentTimeMillis();
    for (int i = 0; i < 5000; i++) {
        preparedStatement.setString(1, "wxy" + 1);
        preparedStatement.setString(2, "wxy" + 1);
        // 加入到批处理中
        preparedStatement.addBatch();
        // 当够1000条时，执行一次
        if ((i + 1) % 1000 == 0) {
            preparedStatement.executeBatch();
            preparedStatement.clearBatch();
        }
    }
    long end = System.currentTimeMillis();
    System.out.println(end - start);
    JDBCUtils.closeSource(null, preparedStatement, connection);
}

addBatch()机制

/* 1.
    public void addBatch() throws SQLException {
    try {
        synchronized(this.checkClosed().getConnectionMutex()) {
            QueryBindings<?> queryBindings = ((PreparedQuery)this.query).getQueryBindings();
            queryBindings.checkAllParametersSet();  检查参数
            this.query.addBatch(queryBindings.clone());  添加
        }
    } catch (CJException var6) {
        throw SQLExceptionsMapping.translateException(var6, this.getExceptionInterceptor());
    }
}
    2.
    if (this.batchedArgs == null) { // 若第一次添加，则创建一个数组链表
        this.batchedArgs = new ArrayList();
    }
    3.
    batchedArgs 有  elementData对象数组
    4.
    elementData[size++] = e; // 将要添加的数据放入到elementData中
    5. 当超过容量限制后，会进行扩容1.5*
    6. 当添加到指定值后，就executeBatch
            批处理会减少发送SQL语句的网络开销，而且减少编译次数
 */

连接池 △

传统获取Connection问题

传统的JDBC数据库连接使用DriverManager来获取，每次想数据库建立连接的时候都要将Connection加载到内存中，再验证IP地址，用户名和密码（0.05s-1s）。需要数据库连接的时候，就想数据库要求一个，频繁的进行数据库连接操作将占用很多的系统资源，容易造成服务器崩溃
每一次数据库连接，使用完后都得断开，若程序出现异常而未能关闭，将导致数据库内存泄漏，最终将导致重启数据库
传统获取连接的方式，不能控制创建的连接的数量，若连接过多，也可能导致内存泄漏，MySQL崩溃
解决传统开发中的数据库连接问题，可以采用数据库连接池技术（Connection pool）

基本介绍

预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从”缓冲池“中取出一个，使用完毕之后再放回去
数据库连接池负责分配、管理和释放数据库的链接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。
当应用程序向连接池请求的链接数超过最大连接数量时，这些请求将被加入到等待队列中

连接池种类

JDBC的数据库连接池使用javax.sql.DataSource来表示，DataSource只是一个接口，该接口由第三方提供实现【提供jar包】
C3P0数据库连接池，速度相对较慢，稳定性不错
DBCP数据库连接池，速度相对C3P0较快，但不稳定
Proxool数据库连接池，有监控连接池状态的功能，稳定性交C3P0差一点
BoneCP数据库连接池，速度快
Druid（德鲁伊）是阿里提供的数据库连接池，集DBCP、C3P0、Proxool优点于一身的数据库连接池

C3P0

@Test
// 方式1 ： 相关参数，在程序中指定user, url, password等
public void testC3P0_1() throws Exception {
    // 1. 创建一个数据源对象
    ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
    // 2. 通过配置文件 获取相关连接信息
    String mysqlPath = "/Users/xinyu/IdeaProjects/IdeaWorkspace/Study_Java/Study_Java_thirteen/src/mysql.properties";
    Properties properties = new Properties();
    properties.load(new FileInputStream(mysqlPath));
    String user = properties.getProperty("user");
    String password = properties.getProperty("password");
    String driver = properties.getProperty("driver");
    String url = properties.getProperty("url");
    // 3. 传给数据源comboPooledDataSource 设置相关参数
    // 注意: 连接管理是由comboPooledDataSource来管理的
    comboPooledDataSource.setDriverClass(driver);
    comboPooledDataSource.setJdbcUrl(url);
    comboPooledDataSource.setUser(user);
    comboPooledDataSource.setPassword(password);
    // 设置初始化链接数
    comboPooledDataSource.setInitialPoolSize(10);
    // 设置最大连接数
    comboPooledDataSource.setMaxPoolSize(50);
    long start = System.currentTimeMillis();
    Connection connection = null;
    for (int i = 0; i < 5000; i++) {
        connection = comboPooledDataSource.getConnection();
        JDBCUtils.closeSource(null, null, connection);
    }
    long end = System.currentTimeMillis();
    System.out.println("连接成功");
    System.out.println(end - start);
    connection.close();
    comboPooledDataSource.close();
}


@Test
// 第二种方式 使用配置文件来完成
// 1. c3p0提供的c3p0.config.xml 拷贝到进来
// 2. 该文件制定了连接数据库和连接池的相关参数
public void testC3P0_2() throws Exception {
    ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("wxy");
    Connection connection = comboPooledDataSource.getConnection();
    System.out.println("连接成功");
    connection.close();
}

Druid △

@Test
public void testDruid1() throws Exception {
    // 1. 加入jar包
    // 2. 加入配置文件 拷贝到src
    // 3. 创建Properties对象
    Properties properties = new Properties();
    properties.load(new FileInputStream("/Users/xinyu/IdeaProjects/IdeaWorkspace/Study_Java/Study_Java_thirteen/src/druid.properties"));
    // 4. 创建一个制定参数的数据库连接池
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    long start = System.currentTimeMillis();
    for (int i = 0; i < 500000; i++) {
        Connection connection = dataSource.getConnection();
        connection.close();
    }
    long end = System.currentTimeMillis();
    System.out.println("连接成功");
    System.out.println(end - start);
}


public class JDBCUtilsByDruid {
    private static DataSource ds;

    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream(
                    "/Users/xinyu/IdeaProjects/IdeaWorkspace/Study_Java/Study_Java_thirteen/src/druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    // 关闭连接，在数据库连接池技术中，close不是真的断掉连接
    // 而是把使用的Connection对象放回连接池
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException();
        }
    }


}


@Test
public void testSelect() throws Exception {
    Connection connection = JDBCUtilsByDruid.getConnection();
    String sql = "select * from admin";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    ResultSet resultSet = preparedStatement.executeQuery();
    while (resultSet.next()) {
        String name = resultSet.getString(1);
        String pwd = resultSet.getString(2);
        System.out.println(name + "	" + pwd);
    }
    JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
}

Apache–DBUtils

// 使用apache-DbUtils 工具类+ druid 完成对表的操作
    @Test
    public void QueryMany() throws Exception {
        // 1. 获得连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        // 2. 使用DBUtils类和接口，先引入jar包
        // 3. 创建QuerRunner
        QueryRunner queryRunner = new QueryRunner();
        // 4. 执行相关方法，返回ArrayList结果集   可以查询部分字段
        String sql = "select * from actor where id > ?";
//        String sql = "select id, name, sex from actor where id > ?";
        // 4.1 query 方法就是执行sql语句，获得ResultSet --》 封装袋 --》 Arraylis集合中
        // 4.2 返回集合
        // 4.3 connection 连接
        // 4.4 sql 执行的SQL语句
        // 4.5 new BeanListHandler<>(Actor.class)  在将ResultSet --> Actor 对象 --> 封装ArraList
        //          底层使用反射机制，以获取Actor类的属性，然后进行封装
        // 4.6 1  就是给sql语句中的？ 赋值， 可以有多个值，因为是可变参数
        // 4.7 底层得到的resultSet  会在query关闭，   关闭 PreparedStatement
        List<Actor> list =
                queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 0);

        for (Actor actor : list) {
            System.out.println(actor);
        }
        JDBCUtilsByDruid.close(null, null, connection);

    }
				/**
         * 底层实现
         * PreparedStatement stmt = null;  --定义PreparedStatement
         *             ResultSet rs = null; --接收返回的结果集ResultSet
         *             Object result = null; --返回ArrayList
         *
         *             try {
         *                 stmt = this.prepareStatement(conn, sql); --创建prepareStatement
         *                 this.fillStatement(stmt, params); --对sql进行 ？ 赋值
         *                 rs = this.wrap(stmt.executeQuery()); // 执行sql 返回resultSet
         *                 result = rsh.handle(rs); --返回的resultSet --> arrayList[result] [使用反射机制，对传入的class进行处理]
         *            } catch (SQLException var33) {
         *                 this.rethrow(var33, sql, params);
         *             } finally {
         *                 try {
         *                     this.close(rs); --关闭返回resultSet
         *                 } finally {
         *                     this.close(stmt); --关闭定义PreparedStatement
         *                     if (closeConn) {
         *                         this.close(conn); 关闭connection
         *                     }
         *
         *                 }
         *             }
         *
         *
         */

Query

@Test
public void QuerySingle() throws Exception {
    Connection connection = JDBCUtilsByDruid.getConnection();
    String sql = "select * from actor where id = ?";
    QueryRunner queryRunner = new QueryRunner();
    // 返回一行信息，用BeanHandler
    Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 10);
    System.out.println(actor);
    JDBCUtilsByDruid.closeConn(connection);
}

@Test
public void QueryScalar() throws Exception {
    Connection connection = JDBCUtilsByDruid.getConnection();
    String sql = "select name from actor where id = ?";
    QueryRunner queryRunner = new QueryRunner();
    // 因为返回的是一个地下，使用handler就是ScalarHandler
    Object o = queryRunner.query(connection, sql, new ScalarHandler<>(), 1);
    System.out.println(o);
    JDBCUtilsByDruid.closeConn(connection);
}

Update

@Test
public void UpdateMess() throws Exception {
    Connection connection = JDBCUtilsByDruid.getConnection();
    String sql1 = "update actor set name = ? where id = ?";
    String sql2 = "insert into actor values(null, ?, ?, ?, ?)";
    String sql3 = "delete from actor where id = ?";
    QueryRunner queryRunner = new QueryRunner();
    // 1. 操作是queryRunner.update()
    // 2. 返回值是受影响的行数
    int affectRow1 = queryRunner.update(connection, sql1, "zhangsan", 1);
    int affectRow2 = queryRunner.update(connection, sql2,
            "lisi", "女", "1999-09-03", "123456789");
    int affectRow3 = queryRunner.update(connection, sql3, 5);
    System.out.println(affectRow1);
    System.out.println(affectRow2);
    System.out.println(affectRow3);
    JDBCUtilsByDruid.closeConn(connection);
}

Dao 增删改查 – BasicDao △

Actor

public class Actor {
    private Integer id;
    private String name;
    private String sex;
    private Date borndate;
    private String phone;

    public Actor() {
    }

    public Actor(Integer id, String name, String sex, Date borndate, String phone) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.borndate = borndate;
        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 getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBorndate() {
        return borndate;
    }

    public void setBorndate(Date borndate) {
        this.borndate = borndate;
    }

    public String getPhone() {
        return phone;
    }

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

    @Override
    public String toString() {
        return "Actor{" +
                "id=" + id +
                ", name='" + name + ''' +
                ", sex='" + sex + ''' +
                ", borndate=" + borndate +
                ", phone='" + phone + ''' +
                '}';
    }
}

JDBCUtilsByDruid

public class JDBCUtilsByDruid {
    private static DataSource ds;

    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream(
                    "/Users/xinyu/IdeaProjects/IdeaWorkspace/Study_Java/Study_Java_thirteen/src/druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    // 关闭连接，在数据库连接池技术中，close不是真的断掉连接
    // 而是把使用的Connection对象放回连接池
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException();
        }
    }

    public static void closeConn(Connection connection) {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException();
        }
    }

}

BasicDao

public class BasicDao<T> { // 泛型，指定pojo


    private QueryRunner queryRunner = new QueryRunner();

    // 开发统一dml方法，针对任意表
    // 修改表 增删改
    public int update(String sql, Object... paramters) {
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            int update = queryRunner.update(connection, sql, paramters);
            return update;
        } catch (SQLException e) {
            throw new RuntimeException();
        } finally {
            JDBCUtilsByDruid.closeConn(connection);
        }
    }
    // 查询多个对象  记得传入Class对象，进行反射
    public List<T> queryMulti(String sql, Class<T> clazz, Object... paramters) {
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return queryRunner.query(connection, sql, new BeanListHandler<>(clazz), paramters);
        } catch (SQLException e) {
            throw new RuntimeException();
        } finally {
            JDBCUtilsByDruid.closeConn(connection);
        }
    }

    // 查询单行记录
    public T querySingle(String sql, Class<T> clazz, Object... paramters) {
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return queryRunner.query(connection, sql, new BeanHandler<>(clazz), paramters);
        } catch (SQLException e) {
            throw new RuntimeException();
        } finally {
            JDBCUtilsByDruid.closeConn(connection);
        }
    }

    // 查询单行记录
    public Object queryScalar(String sql, Object... paramters) {
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return queryRunner.query(connection, sql, new ScalarHandler<>(), paramters);
        } catch (SQLException e) {
            throw new RuntimeException();
        } finally {
            JDBCUtilsByDruid.closeConn(connection);
        }
    }


}

ActorDao

public class ActorDao extends BasicDao<Actor> {

    // 1. 拥有BasicDao的方法
    // 2. 根据需求，可以编写特有的方法

}

Test

public class Test {
    public static void main(String[] args) {
        ActorDao actorDao = new ActorDao();
        String sql = "select * from actor where id > ?";
        List<Actor> actors = actorDao.queryMulti(sql, Actor.class, 0);
        for (Actor actor : actors) {
            System.out.println(actor);
        }

        Actor actor = actorDao.querySingle("select * from actor where id = ?",
                Actor.class, 1);
        System.out.println(actor);

        Object o = actorDao.queryScalar("select name from actor where id = 1");
        System.out.println(o);

        int affectRows = actorDao.update("insert into actor values(null, ?, ? ,?, ?)",
                "wuwuwu", "女", "2000-5-7", "123456");
        System.out.println(affectRows);

    }
}

m0_67402588

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Java-JDBC和连接池

JDBC和连接池JDBC概述JDBC为访问不同的数据库提供了统一的接口，为使用者屏蔽了细节问题Java程序员使用JDBC，可以连接任何提供了JDBC驱动程序的数据库系统，从而完成对数据库的各种操作。JDBC原理图：JDBC是Java提供一套用于数据库操作的接口API，Java程序员只需要面向这个接口编程即可。不同的数据库厂商，需要针对这套接口，提供不同实现。JDBC入门基本步骤注册驱动 – 加载Driver类获取连接 – 得到Connection类执行增删改查 –
复制链接

扫一扫

专栏目录