PreparedStatement 和Statement 各分享一个,直接看代码:
以MySQL为例,原理就是sql的这种语法:
sql">insert into tableName(字段一, 字段二)
VALUE (数据一, 数据二), (数据X, 数据Y),(...,...), (...,...)
Statement 对象处理方式;
java">try {
Class.forName(driver);
conn = DriverManager.getConnection(url, username, password);
Statement statement = null;
String csvFilePath = "D:\\t_c804.csv";
CsvReader r = new CsvReader(csvFilePath,',', Charset.forName("UTF-8"));
//读取表头
r.readHeaders();
//逐条读取记录,直至读完
int i = 0;
String sql = "insert into story804 (ID,LAC,CI,AbJie," +
"cellId,rsrp,rsrq) values ";
String bufferSql = "";
while (r.readRecord()) {
JSONObject obj = JSONObject.parseObject(r.get("DisplayData"));
String cellId = obj.getString("N383");
Double rsrp = null;
if (null != obj.getString("N384")) {
rsrp = Double.parseDouble(obj.getString("N384"));
}
Double rsrq = null;
if (null != obj.getString("N385")) {
rsrq = Double.parseDouble(obj.getString("N385"));
}
if("".equals(bufferSql)){
bufferSql = "('" + r.get("ID") + "','" + r.get("LAC") + "','" + r.get("CI") + "','" +
r.get("AbJie") + "','" + cellId + "'," +
rsrp + "," + rsrq + ")";
}else{
bufferSql = bufferSql +
",('" + r.get("ID") + "','" + r.get("LAC") + "','" + r.get("CI") + "','" +
r.get("AbJie") + "','" + cellId + "'," +
rsrp + "," + rsrq + ")";
}
i++;
if(i > 1000){
if(null == statement){
statement = conn.createStatement();
}
System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()) + "---执行一次");
statement.execute(sql + bufferSql);
bufferSql = "";
i = 0;
}
}
if(i > 0){
System.out.println("最后一次插入 " + i + " 行");
if(null == statement){
statement = conn.createStatement();
}
statement.execute(sql + bufferSql);
}
conn.close();
r.close();
System.out.println("-------------------------------------------------");
System.out.println("--------------------执行结束---------------------");
System.out.println("-------------------------------------------------");
} catch (Exception e) {
e.printStackTrace();
}
PreparedStatement 的处理方式
java">String sql = "insert into story804 (id, name) VALUES (?,?)";
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
for (int i = 1; i <= 2848000; i++) {
ps.setObject(1, i);
ps.setObject(2, NameUtil.getName());
ps.addBatch();
if (i % 1000 == 0) {
ps.executeBatch();
ps.clearBatch();
}
}
ps.executeBatch();
ps.clearBatch();
} catch (Exception e) {
e.printStackTrace();
}
一般使用jdbc都是自己写个工具或者处理手动一点数据,正式项目中几乎没有这么用的,仅供大家参考