jdbc百万数据批量导入，流式读取

最新推荐文章于 2025-07-24 13:43:06 发布

原创最新推荐文章于 2025-07-24 13:43:06 发布 · 5.1k 阅读

2 ·

CC 4.0 BY-SA版权

java技术同时被 2 个专栏收录

64 篇文章

订阅专栏

JDBC

2 篇文章

订阅专栏

本文介绍了一种使用Java和MySQL进行大数据量批量插入的方法。通过建立读写分离连接，设置合理的fetch大小，并采用预编译SQL及批处理提交的方式，实现了高效的数据迁移。文中详细展示了代码实现过程及性能表现。

 Connection connReader = null;
 Connection connWrite = null;
 String url = "jdbc:mysql://XX:3306/XX?"
        + "user=XX&password=XX&useUnicode=true&characterEncoding=UTF8";

   Class.forName("com.mysql.jdbc.Driver");// 动态加载mysql驱动

connReader = DriverManager.getConnection(url);//读
connWrite = DriverManager.getConnection(url);//写
long b1 = System.currentTimeMillis();
String sql = "select * from building_czw a INNER JOIN building_ga b ON a.BUILDING_CODE = b.BUILDING_CODE where   b.MLPLX is NOT NULL AND  a.CREATETIME >= '" + begain + "' and a.CREATETIME <= '" + end + "'";
Statement stmtReader = connReader.createStatement();
stmtReader.setFetchSize(10000);
ResultSet rs = stmtReader.executeQuery(sql);// executeQuery会返回结果的集合，否则返回空值
rs.setFetchSize(10000);
connWrite.setAutoCommit(false);
PreparedStatement ps = null;
Statement stmtWrite = connReader.createStatement();
String sqlWrite = "INSERT INTO building_temp ( building_code, building_type, area_code, district, street, community, grid, address_type, isdoorplate, isbuilding_number, isunit_number, number_rule, building_status, manager, policeroom, ordinary_room_count, public_room_count, building_area, floor_count, underground_count, ground_count, number_address, other_address, propertyright_address, monitor_video, door_system, building_name, elevator, building_nature, parkinglot, property_type, manageUnit, contacts, contacts_phone, building_structure, designuse, usedfor, userofinfo, remarks, reporting_unit, registrant, registration_time, branch_office, police_station, police_room, room_type, room_rule, street_lane_name, room_number, building_number, building_called, unit_number, unit_name, unit_building_name, ismarkbrand,detailed_address,number_type,create_date,status) VALUES " +
        "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?,?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?);";
ps = connWrite.prepareStatement(sqlWrite, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

while (rs.next()) {
    i++;
    count++;
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String datestring = sdf.format(new Date());
    Date date = null;
    date = sdf.parse(datestring);
    BuildingNeum buildingNeum = new BuildingNeum();
    ps.setString(1, rs.getString("BUILDING_CODE"));
    ps.setString(2, buildingNeum.getbuildingType(rs.getString("BUILDING_TYPE")));
    ps.setString(3, rs.getString("XZGH"));
    ps.setString(4, rs.getString("DETAIL_DISTRICT"));
    ps.setString(5, rs.getString("DETAIL_STREET"));
    ps.setString(6, rs.getString("DETAIL_COMMUNITY"));
    ps.setString(7, rs.getString("ADMIN_REGION_ID"));
    ps.setString(8, buildingNeum.getbuildingAddressType(rs.getString("BUILDING_ADDR_TYPE")));
    ps.setString(9, rs.getString("MENPAI"));
    ps.setString(10, rs.getString("LOUPAI"));
    ps.setString(11, rs.getString("DANYUANPAI"));
    ps.setString(12, rs.getString("ROOMRULE"));
    ps.setString(13, buildingNeum.getbuildingStatus(rs.getString("BUILD_STATE")));
    ps.setString(14, rs.getString("ADMINISTRATOR"));
    ps.setString(15, rs.getString("JINGWUSHI"));
    ps.setString(16, rs.getString("ROOM_NUM"));
    ps.setString(17, rs.getString("PUBLIC_NUM"));
    ps.setString(18, rs.getString("BUILDING_AREA"));
    ps.setString(19, rs.getString("BUILDING_LAYER"));
    ps.setString(20, rs.getString("UNDERGROUND_LAYER"));
    ps.setString(21, rs.getString("UPGROUND_LAYER"));
    ps.setString(22, rs.getString("DOORPLATE_BUILDING_FULL_ADDR"));
    ps.setString(23, rs.getString("OTHER_BUILDING_FULL_ADDR"));
    ps.setString(24, rs.getString("ESTATE_BUILDING_FULL_ADDR"));
    ps.setString(25, rs.getString("MEDIA"));
    ps.setString(26, rs.getString("ENTRANCE_GUARD"));
    ps.setString(27, rs.getString("BUILDING_NAME"));
    ps.setString(28, rs.getString("BUILDING_ELEVATOR"));
    ps.setString(29, rs.getString("BUILD_KIND"));
    ps.setString(30, rs.getString("POITCAR"));
    ps.setString(31, buildingNeum.getroomRcardType(rs.getString("REAL_ESTATE_LICENCE_TYPE")));
    ps.setString(32, rs.getString("PROPERTY_MANAGEMENT_COMPANY"));
    ps.setString(33, rs.getString("CONTACT_PERSON"));
    ps.setString(34, rs.getString("CONTACT_TEL"));
    ps.setString(35, buildingNeum.getbuildingStructure(rs.getString("BUILDING_STRUCTURE")));
    ps.setString(36, buildingNeum.getDesignuse((rs.getString("INTENT_USE_TYPE"))));
    ps.setString(37, buildingNeum.getUserinfo(rs.getString("USE_STATE")));
    ps.setString(38, buildingNeum.getuse(rs.getString("USAGE")));
    ps.setString(39, rs.getString("REMARKS"));
    ps.setString(40, rs.getString("UNIT"));
    ps.setString(41, rs.getString("DENGJIREN"));
    Date date1 = null;

    if (StringUtils.isNotBlank(rs.getString("DENGJITIME"))) {
        datestring = rs.getString("DENGJITIME");
        //datestring=datestring.substring(0,19);
        date1 = sdf.parse(datestring);
    } else {
        //date1=sdf.parse(datestring);
    }
    if (date1 == null) {
        ps.setTimestamp(42, null);
    } else {
        ps.setTimestamp(42, new Timestamp(date1.getTime()));
    }
    String mlplx = rs.getString("MLPLX");
    ps.setString(43, rs.getString("SSFJ"));
    ps.setString(44, rs.getString("SSPCF"));
    ps.setString(45, rs.getString("SQJWS"));
    ps.setString(46, mlplx);
    ps.setString(47, rs.getString("MPGG"));
    ps.setString(48, rs.getString("JLXMC"));
    ps.setString(49, rs.getString("MPHM"));
    ps.setString(50, rs.getString("LPHM"));
    ps.setString(51, rs.getString("LPSC"));
    ps.setString(52, rs.getString("DYPHM"));
    ps.setString(53, rs.getString("DYPSC"));
    ps.setString(54, rs.getString("XDNM"));
    ps.setString(55, rs.getString("IFZP"));
    ps.setString(56, rs.getString("OTHER_ADDRESS"));
    if ("门牌".equals(mlplx)) {
        ps.setInt(57, 2);
    } else if ("楼牌".equals(mlplx)) {
        ps.setInt(57, 3);
    } else if ("单元牌".equals(mlplx)) {
        ps.setInt(57, 4);
    } else {
        ps.setInt(57, 0);
    }
    ps.setTimestamp(58, new Timestamp(date.getTime()));
    ps.setInt(59, 0);
    ps.addBatch();
    if (i % 5000 == 0) {
       /* System.out.println("执行之前");
        System.out.println(Runtime.getRuntime().totalMemory()/1024/1024);
        System.out.println(Runtime.getRuntime().maxMemory()/1024/1024);
        System.out.println(Runtime.getRuntime().freeMemory()/1024/1024);*/
        long i4 = System.currentTimeMillis();
        ps.executeBatch();
        long i5 = System.currentTimeMillis();
      /*  System.out.println("5000条插入结束,耗时" + (i5 - i4) / 1000 + "秒");
        System.out.println(Runtime.getRuntime().totalMemory()/1024/1024);
        System.out.println(Runtime.getRuntime().maxMemory()/1024/1024);
        System.out.println(Runtime.getRuntime().freeMemory()/1024/1024);*/
        //优化插入第三步       提交，批量插入数据库中。
        connWrite.commit();
        ps.clearBatch();        //提交后，Batch清空。
        System.out.println("任务结束" + i);
    }


}

ps.executeBatch();
//优化插入第三步       提交，批量插入数据库中。
connWrite.commit();
ps.clearBatch();

我的表数据量很大，单行60个字段插入，数据2G，耗时13分钟，5000条插入需要2秒，这还算慢的