sql批量入库
public void saveBatch(List<Asset> entitys) throws ClassNotFoundException, SQLException {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
StringBuffer sql = new StringBuffer("INSERT IGNORE INTO 表名 (AssetId,Category,SubCategory,VideoUrl"
+ ",Anchor,AssetDesc,Keyword,Title,UploadTime,ImgUrl,PosterUrl,Duration,CreateTime,VideoStatus,ImgStatus,UpdateTime) "
+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");
String className = PropertyManager.getProperty("jdbc.driverClassName");
String url = PropertyManager.getProperty("jdbc.url")+"&useServerPrepStmts=false&rewriteBatchedStatements=true";
String userName = PropertyManager.getProperty("jdbc.username");
String password = PropertyManager.getProperty("jdbc.password");
Class.forName(className);
Connection connection = DriverManager.getConnection(url,userName,password);
//设置手动提交
connection.setAutoCommit(false);
PreparedStatement ps = connection.prepareStatement( sql.toString());
for (Asset entity : entitys) {
ps.setString(1,entity.getAssetId());
ps.setString(2,entity.getCategory());
ps.setString(3,entity.getSubCategory());
ps.setString(4,entity.getVideoUrl());
ps.setString(5,entity.getAnchor());
ps.setString(6,entity.getAssetDesc());
ps.setString(7,entity.getKeyword());
ps.setString(8,entity.getTitle());
ps.setString(9,entity.getUploadTime()+"");
ps.setString(10,entity.getImgUrl());
ps.setString(11,entity.getPosterUrl());
ps.setInt(12,entity.getDuration());
ps.setString(13,sdf.format(new Date()));
ps.setInt(14,0);
ps.setInt(15,0);
ps.setString(16,sdf.format(new Date()));
ps.addBatch();
}
ps.executeBatch();
ps.clearBatch();
//提交批处理
connection.commit();
//执行
connection.close();
}
INSERT IGNORE INTO 库中没有的入库,有的忽略,注意设置唯一索引,一般除了id只有一个。
REPLACE INTO 库中存在的直接更新。
INSERT INTO 表名(id,age,name)values ( id ,age,name) on duplicate key update age=? , name =?