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秒,这还算慢的