java向数据库批量插入数据
话不多说,代码附上。
// 配置文件获取数据库信息 @Value("${spring.datasource.url}") private String url; @Value("${spring.datasource.driver-class-name}") private String driver; @Value("${spring.datasource.username}") private String user; @Value("${spring.datasource.password}") private String password;
public void batcheInsert(List<Map<String,Object>>listMap, String tableName){ try { String sql = ""; String sqlStr1 = "INSERT INTO "+tableName+" ("; String sqlStr2= ") VALUES ("; String sqlStr3 = ")"; String sqlKey = ""; String sqlValue = ""; for (Object key: listMap.get(0).keySet() ) { sqlKey+= key + ","; sqlValue += "?,"; } sqlKey = sqlKey.substring(0,sqlKey.length() -1); sqlValue = sqlValue.substring(0,sqlValue.length() -1); sql = sqlStr1 + sqlKey + sqlStr2 + sqlValue + sqlStr3; logger.info("拼接的insert into SQL:" + sql); Connection conn = getConnection(); conn.setAutoCommit(false); //构造预处理statement PreparedStatement pst = conn.prepareStatement(sql); int count = 0; int index = 1; for(int i = 1;i <= listMap.size();i++){ for (Object val: listMap.get(i-1).values() ) { pst.setString((index++),nullToNull(val)); } index = 1; pst.addBatch(); //每10000次提交一次 if(i % 10000 == 0){//可以设置不同的大小; ++count; pst.executeBatch(); conn.commit(); pst.clearBatch(); } } // 最后插入不足1w条的数据 pst.executeBatch(); conn.commit(); logger.info("批次提交次数:" + ++count); pst.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } }
public Connection getConnection() {//建立返回值为Connectiong的方法 Connection con = null;//声明Connection对象 try {//加载数据库驱动类 Class.forName(driver); System.out.println("数据库驱动加载成功"); } catch (ClassNotFoundException e) { e.printStackTrace(); } try {//通过访问数据库的URL获取数据库连接对象 con = DriverManager.getConnection(url, user, password); System.out.println("数据库连接成功"); } catch (SQLException e) { e.printStackTrace(); } return con;//按方法要求返回一个Connectiong对象 }
相关推荐
Lzs 2020-10-23
聚合室 2020-11-16
零 2020-09-18
Justhavefun 2020-10-22
jacktangj 2020-10-14
ChaITSimpleLove 2020-10-06
Andrea0 2020-09-18
周游列国之仕子 2020-09-15
afanti 2020-09-16
88234852 2020-09-15
YClimb 2020-09-15
风雨断肠人 2020-09-04
卖口粥湛蓝的天空 2020-09-15
stulen 2020-09-15
pythonxuexi 2020-09-06
abfdada 2020-08-26
梦的天空 2020-08-25