比较大的数据写入数据库_特别大的数据存到数据库-优快云博客

通过将数据以批处理方式插入数据库，而非逐条插入，显著提高了数据处理效率。本篇介绍了一种具体实现方法，利用PreparedStatement的addBatch()方法，每2000条数据执行一次批量插入操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

现有数据800万条。。要求插入数据库。。如果一条一条的插入。。那你就是傻子。。很不幸，我最开始就是那个傻子。。

后来改成了2000条插入一次，果然好太多，代码如下：

        int batchCount = 1;
		Connection con = null;
   		PreparedStatement stmt = null;
   		String sql = "";
   		con = DBConnector.getInstance().connectToIncidentDB();		
		sql = "insert into incident_db.MyDataTest_tb(objectid,year_record,state_code,route_id,begin_point,end_point,aadt,aadt_combination,toll_type,truck,urban_code,paths,COUNTY,STATE) values(?,?,?,?,?,?,?,?,?,GEOMFROMTEXT(?),?,?)";

		try{
			stmt = con.prepareStatement(sql);
			
			for(int i=0; i <= jsonArray.size()-1; i++) {
				JSONObject tempObj = jsonArray.getJSONObject(i);
				JSONObject obj = null;
				if(tempObj != null){
					obj = tempObj.getJSONObject("attributes");
				}
				if(obj == null){
					LOGGER.warn("attributes is null");
					continue;
				}
				
				String objectid =  format(obj.get("objectid") == null ? "" : obj.getString("objectid"));
				String year_record = format(obj.get("year_record") == null ? "" : obj.getString("year_record"));
				String state_code = format(obj.get("state_code") == null ? "" :  obj.getString("state_code"));
				String route_id = format(obj.get("route_id") == null ? "" : obj.getString("route_id"));
                                        ..............
				//begin_point
				String begin_point = format(obj.get("begin_point") == null ? "" : obj.getString("begin_point"));
				String end_point = format(obj.get("end_point") == null ? "" : obj.getString("end_point"));
				String aadt = format(obj.get("aadt") == null ? "" : obj.getString("aadt"));
				String aadt_combination = format(obj.get("aadt_combination") == null ? "" : obj.getString("aadt_combination"));

				
				JSONObject geometry = tempObj.getJSONObject("geometry");
				JSONArray paths = null;
				String pathText = "";
				if(geometry == null){
					System.out.println();
					pathText = "";
				}
				if(geometry != null){
					if(geometry.toString().length() > 10){
						if(geometry.get("paths") != null){
							paths = geometry.get("paths") == null ? null : geometry.getJSONArray("paths");
						}
					}
				}
				if(paths != null){
					if(paths.size() > 0){
						for(int s =0; s < paths.size() ;s++){
							JSONArray oop = JSONArray.fromObject(paths.get(s));
							if(oop != null){
								for(int r = 0; r<oop.size(); r++){
									oop.get(r).toString().replace("[", "").replace("]", "");
									if(oop.get(r) != null){
										JSONArray sss = JSONArray.fromObject(oop.get(r));
										if(sss != null && sss.size() == 2){
											double x = Double.parseDouble(sss.get(0).toString());
											double y = Double.parseDouble(sss.get(1).toString());
											x = convertLonFromEPSG900913ToEPSG4326(x);
											y = convertLatFromEPSG900913ToEPSG4326(y);
											pathText = pathText + x + " " + y+ ", ";
										}
									}
										
									
							    }
							   
							}
						}
					}
			
					pathText = pathText.trim().replaceAll("(.*),\\s*$", "$1").trim();
				}
				
				String county = "";
				stmt.setString(1, objectid);
	 			stmt.setString(2, year_record);
	 			stmt.setString(3, state_code);
	 			stmt.setString(4, route_id);
	 			stmt.setString(5, begin_point);
	 			stmt.setString(6, end_point);
	 			stmt.setString(7, aadt);
	 			stmt.setString(8, aadt_combination);
                               ...........
	 			stmt.setString(32, "LINESTRING("+pathText+")");
	 			stmt.setString(33, county);
	 			stmt.setString(34, state);
	 			
	 			stmt.addBatch();
	 			batchCount++;
	 			if(batchCount == 2000){
	 				stmt.executeBatch();
	 				stmt.clearBatch();
	 			    batchCount = 1;			 
	 			}
			}
			stmt.executeBatch();
			stmt.close();
		    con.close();	
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			 if (stmt != null) 
					stmt.close();
				if (con != null) 
					con.close();
		}

省略了一些字段。。这里的path坐标我觉得需要稍微记一下，[[11.22,22.33],[22.22,23.23],[22.22,44.43]]这种格式，最后得存成这种格式，x y坐标之间是空格隔开，一个xy坐标以逗号隔开。。下次遇到记清楚怎么处理GEOMFROMTEXT(?)。。