jdbc中大批量的获取数据(idea + jdbc) preparementstatment 和 Result 使用参数 setFetchSize(int row）

浅浅的花香味﹌ 2022-02-01 05:55 282阅读 0赞

遇到一个比较坑的问题 就是在大批量数据从数据库导出 再存入到redis当中 会耗费很长的时间。(在服务器山大概花费了40分钟左右) 按实际情况不能这么久 所以需要优化连接数据库取值这块，记录以防忘记。

**目录**

1.原本的连接代码

2.使用参数

3.修改后的连接代码

4.比较

--------------------

# 1.原本的连接代码 #

public synchronized void initVehicleInfo() throws SQLException, InterruptedException {
    
            log.info("initVehicleInfo___开始加载数据库....");
            connection = SHDBUtil.getConnection();
            String sql = " SELECT DV.VEHICLE_ID, DV.LICENSE_PLATE, DV.LICENSE_PLATE_COLOR, DV.SIM_ID, DV.ORG_ID,DV.TERMINAL_ID"
                    + " FROM D_VEHICLEINFO DV "
                    + "WHERE DV.SIM_ID IS NOT NULL AND DV.TERMINAL_ID IS NOT NULL";
    
            log.info("initVehicleInfo___等待查询数据...................");
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            //存储两套车辆数据.... 一个用 Sim卡作为表示， 一个用终端code, 作为标识
            Map<String, Object> basicInfoMapBySim;
            Map<String, Object> basicInfoMapByTerminId;
            VehicleBasicInfo basicInfo;
    
            log.info("initVehicleInfo___等待组装数据 ...................");
            //todo: 有终端重复的和sim卡重复的，所以 最后存储的数据不会是完整的  有些车辆是不会完整的 柳汶明说暂时不处理
            while (resultSet.next()) {
                basicInfo = new VehicleBasicInfo();
                basicInfo.setVehicleId(resultSet.getString(1).trim());
                basicInfo.setBrandNum(resultSet.getString(2).trim());
                basicInfo.setBrandColor(resultSet.getString(3).trim());
                basicInfo.setSimCard(resultSet.getString(4).trim());
                basicInfo.setOrgId(resultSet.getString(5).trim());
                basicInfo.setTerminalCode(resultSet.getString(6).trim());
                String jsonMsg = JSON.toJSONString(basicInfo);
                basicInfoMapBySim = new HashMap<>();
                basicInfoMapByTerminId = new HashMap<>();
                basicInfoMapBySim.put(resultSet.getString(4).trim(), jsonMsg);
                basicInfoMapByTerminId.put(resultSet.getString(6).trim(), jsonMsg);
                redisUtil.hmset("carInfo-sim", basicInfoMapBySim);
                redisUtil.hmset("carInfo-terminal", basicInfoMapByTerminId);
            }
            log.info("initVehicleInfo___初始化车辆数据完毕.....");
    
        }

这样的处理的话是每次取出数据后循环存到本地和redis里面，这样有个弊端，就是会创建大量的链接，导致会浪费很多时间

# 2.使用参数 #

preparedStatement.setFetchSize(10000);
    
    resultSet.setFetchSize(10000);

# 3.修改后的连接代码 #

public synchronized void initVehicleInfo() throws SQLException, ClassNotFoundException {
    
            Map<Object, Object> basicInfoMapBySim;
            Map<Object, Object> basicInfoMapByTerminId;
    
            log.info("initVehicleInfo___开始加载数据库....");
            try {
                log.debug("当前时间为:" + new Date().getTime());
                //事务管理
                connection.setAutoCommit(false);
                String sql = " SELECT DV.VEHICLE_ID, DV.LICENSE_PLATE, DV.LICENSE_PLATE_COLOR, DV.SIM_ID, DV.ORG_ID,DV.TERMINAL_ID"
                        + " FROM D_VEHICLEINFO DV "
                        + " WHERE DV.SIM_ID IS NOT NULL AND DV.TERMINAL_ID IS NOT NULL AND DV.LICENSE_PLATE IS NOT NULL AND DV.LICENSE_PLATE_COLOR IS NOT NULL AND DV.ORG_ID IS NOT NULL";
                log.info("initVehicleInfo___等待查询数据...................");
                PreparedStatement preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setFetchSize(10000);
                ResultSet resultSet = preparedStatement.executeQuery();
                resultSet.setFetchSize(10000);
                /*
                 * 存储两套车辆数据.... 一个用 Sim卡作为表示， 一个用终端code, 作为标识
                 * 尽量使用一个对象处理
                 * */
                VehicleBasicInfo basicInfo = new VehicleBasicInfo();
                log.info("initVehicleInfo___等待组装数据 ...................");
                basicInfoMapBySim = new ConcurrentHashMap<>();
                basicInfoMapByTerminId = new ConcurrentHashMap<>();
                //todo: 有终端重复的和sim卡重复的，所以 最后存储的数据不会是完整的  有些车辆是不会完整的 柳汶明说暂时不处理
                while (resultSet.next()) {
    //                basicInfo = new VehicleBasicInfo();
                    basicInfo.setVehicleId(resultSet.getString(1).trim());
                    basicInfo.setLicensePlate(resultSet.getString(2).trim());
                    basicInfo.setLicensePlateColor(resultSet.getString(3).trim());
                    basicInfo.setSimId(resultSet.getString(4).trim());
                    basicInfo.setOrgId(resultSet.getString(5).trim());
                    basicInfo.setTerminalId(resultSet.getString(6).trim());
                    String jsonMsg = JSONObject.toJSONString(basicInfo);
                    basicInfoMapBySim.put(resultSet.getString(4).trim(), jsonMsg);
                    localBasicInfoBySim.put(resultSet.getString(4).trim(), jsonMsg);
                    basicInfoMapByTerminId.put(resultSet.getString(6).trim(), jsonMsg);
                    localBasicInfoByTerminal.put(resultSet.getString(6).trim(), jsonMsg);
                    System.out.println(jsonMsg);
                }
                redisUtil.hmset(GPSSIM, basicInfoMapBySim);
                redisUtil.hmset(GPSTWERMINAL, basicInfoMapByTerminId);
                log.info("initVehicleInfo___初始化车辆数据完毕....本地缓存加载完毕.....");
                connection.setAutoCommit(true);
                System.out.println(localBasicInfoBySim.size() + "...." + localBasicInfoByTerminal.size());
                log.debug("处理完毕后的时间为:" + new Date().getTime());
            } catch (SQLException e) {
                log.error("连接失败");
            } finally {
                connection.close();
            }
        }

首先因为是线程要用到的 所以map是改为了线程安全的ConcurrentHashMap

紧接着设置了取数据的次数为10000  如果不设置额setFetchSize的话 是默认为10条一次，设置为1W条 会每次取出1W条 这样节约时间。

# 4.比较 #

根据日志查看最为直观：

修改后的：

![20190513174656502.png][]

初始化前:记得是40多分钟 很慢很慢

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MTk4MTgx_size_16_color_FFFFFF_t_70][]

以此记录 数据库查询这块使用参数

[20190513174656502.png]: /images/20220201/dc4e44c576ba4249b67f35b11fe4724b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MTk4MTgx_size_16_color_FFFFFF_t_70]: /images/20220201/c8ce1d530d564f4f9df98dc06219f8ae.png