526互联

JDBC p6 ApDBUtils

发布时间 2023-08-02 10:45:30作者: 凉白茶

ApDBUtils

引出

问题分析：

  1. 关闭 connection 之后，resultSet 结果集无法使用。

  2. resultSet 不利于数据的管理

  3. 示意图：

自己完成封装

package com.hspedu.jdbc.datasource;

import org.junit.jupiter.api.Test;

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


public class JDBCUtilsByDruid_test {

    // 使土方法解决ResultSet 封装 => ArrayList
    @Test
    public void testSelectToArrayList(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select * from actor where id >= ?";
        ArrayList<Actor> actors = new ArrayList<>();

        try {
            connection = JDBCUtilsByDruid.getConnection();
            System.out.println(connection.getClass());//运行类型class com.alibaba.druid.pool.DruidPooledConnection
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "1");
            resultSet = preparedStatement.executeQuery();


            // 如果在这里将connection关闭，接下来将会报错
            // 因为resultSet结果集 是和connection关联的，connection关闭了结果集就不能用了，不利与多次复用
//            connection.close();//报错：java.sql.SQLException: Operation not allowed after ResultSet closed
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                Date borndate = resultSet.getDate("borndate");
                String phone = resultSet.getString("phone");

                //把得到的resultSet对象封装到 Actor对象，放入到list集合中
                actors.add(new Actor(id, name, sex, borndate, phone));
            }

            System.out.println("actors集合数据 = " + actors);
            for (Actor actor : actors) {
                System.out.println("id" + actor.getId());
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        //关闭
        JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);

        //因为ArrayList 和 connection 没有关联
//        return actors;
    }
}