prepareStatement
String sql = "insert ignore into db.test_table(a,b,c,d,e) VALUES (?,?,1,1,0)";
PreparedStatement pstmt = null;
int i=0;
try {
pstmt = connection.prepareStatement(sql);
for(Integer peopleID:peopleIDList){
for(Integer gameID:gameIDList){
pstmt.setInt(1, peopleID);
pstmt.setInt(2,gameID );
pstmt.addBatch();
i++;
}
pstmt.executeBatch();
}
需使用预处理,否则不生效
批量插入静态SQL使用 Statement
ObaseConnection connection = JdbcUtil.getConnection();
connection.setAutoCommit(false);
Statement prepareStatement = connection.createStatement();
prepareStatement.addBatch(resSql);
prepareStatement.executeBatch();
prepareStatement.clearBatch();
connection.commit();
Demo
public class Main {
public static void main(String[] args) throws SQLException {
ObaseConnection connection = JdbcUtil.getConnection();
connection.setAutoCommit(false);
System.out.println(Main.class.getResource("/"));
String replace = Main.class.getResource("/").toString().replace("file:/", "");
/*文件目录*/
String path = replace + "pgxxb_202112011057.sql";
// String path = replace + "pgxxb_202112011055.sql";
// String path = replace + "pgxxb_202112011103.sql";
/*文件目录*/
/*读取文件*/
File file = new File(path);
int count = 1;
int failCount = 0;
long startTime = System.currentTimeMillis();
StringBuffer sql = new StringBuffer();
String resSql;
Statement prepareStatement = connection.createStatement();
String s;
BufferedReader br = null;
try {
//构造一个BufferedReader类来读取文件
br = new BufferedReader(new FileReader(file));
while ((s = br.readLine()) != null) {
sql.append(s);
if (s.endsWith(");")) {
resSql = sql.toString().replace("\t", "");
prepareStatement.addBatch(resSql);
try {
if (count % 10 == 0) {
System.out.println("执行 ----------------- " + count + " ----------------- 条!");
prepareStatement.executeBatch();
prepareStatement.clearBatch();
connection.commit();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
count++;
}
sql.setLength(0);
}
}
prepareStatement.executeBatch();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
br.close();
prepareStatement.close();
connection.close();
long endTime = System.currentTimeMillis();
System.out.println(String.format("成功 %s 条 失败 %s 批次! 用时: %s 分!", count, failCount, (startTime - endTime) / 1000 / 60));
}
}
}