JAVA高效批量插入数据到数据库demo

1、批量插入数据到sqlserver数据库:10000条数数据平均耗时1800ms

public static void main(String[] args) {

PreparedStatement ps;

Connection con = getConnection();

String sql = "insert into t_s_log (ID,broswer,logcontent,loglevel,note, operatetime,operatetype,userid,username,realname) values(?,?,?,?,?,?,?,?,?,?)";

try {

ps = con.prepareStatement(sql);

List<TSLog> logList = getList();

for (TSLog log : logList) {

ps.setString(1, log.getId());

ps.setString(2, log.getBroswer());

ps.setString(3, log.getLogcontent());

ps.setShort(4, log.getLoglevel());

ps.setString(5, log.getNote());

ps.setDate(6, new java.sql.Date(log.getOperatetime().getTime()));

ps.setShort(7, log.getOperatetype());

ps.setString(8, log.getUserid());

ps.setString(9, log.getUsername());

ps.setString(10, log.getRealname());

ps.addBatch();

}

long a = System.currentTimeMillis();

ps.executeBatch();

System.out.println("插入 " + logList.size() + " 条数据耗时 " + (System.currentTimeMillis() - a) + " 毫秒");

con.commit();

ps.clearBatch();

ps.close();

} catch (SQLException throwables) {

throwables.printStackTrace();

}

}

public static List<TSLog> getList() {

List<TSLog> logList = new ArrayList<>();

for (int i = 0; i < 10000; i++) {

TSLog log = new TSLog();

log.setId(UUID.randomUUID().toString().replaceAll("-", ""));

log.setBroswer("Chrome");

log.setLogcontent("测试");

log.setLoglevel((short) 1);

log.setNote("测试");

log.setOperatetime(new Date());

log.setOperatetype((short) 1);

log.setUserid("402885af6745c5ae016745dfdb590014");

log.setUsername("admin");

log.setRealname("admin");

logList.add(log);

}

return logList;

}

public static Connection getConnection() {

String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

String dbURL = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=xxx";

String userName = "xxx";

String userPwd = "xxx";

try {

Class.forName(driverName);

System.out.println("加载SQLServer驱动类成功!");

} catch (ClassNotFoundException a) {

System.out.println("加载SQLServer驱动失败!");

a.printStackTrace();

}

Connection dbcon = null;

try {

dbcon = DriverManager.getConnection(dbURL, userName, userPwd);

System.out.println("数据库连接成功!");

} catch (SQLException e) {

System.out.println("数据库连接失败!");

e.printStackTrace();

}

return dbcon;

}

2、批量插入数据到mysql数据库:10000条数数据平均耗时700ms

jdbc:mysql://127.0.0.1:3306/xxx&allowMultiQueries=true&rewriteBatchedStatements=true

public List<Object[]> initJDBCDemos() {

List<Object[]> demos = new ArrayList<>();

for (int i = 0; i < 10000; i++) {

Object[] demo = new Object[11];

demo[0] = i + "" + new Date();

demo[1] = i + "name";

demo[2] = i + "keyWord";

demo[3] = new Date();

demo[4] = BigDecimal.ONE;

demo[5] = 1d;

demo[6] = "1";

demo[7] = 10;

demo[8] = new Date();

demos.add(demo);

}

return demos;

}

// jdbc批量插入

@Test

public void testJdbcInsert100000BatchSave() {

List<Object[]> jeecgDemoList = initJDBCDemos();

DruidDataSource dataSource = DynamicDBUtil.getDbSourceByDbKey("qyt-dd-sys");

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

long a = System.currentTimeMillis();

String sql = "INSERT INTO `demo`( `id`, `name`, " +

" `key_word`, " +

" `punch_time`, " +

" `salary_money`, " +

" `bonus_money`, " +

" `sex`, `age`, `birthday`, " +

" `email`, `content`) " +

" VALUES (?,?,?,?,?,?,?,?,?,?,?)";

jdbcTemplate.batchUpdate(sql, jeecgDemoList);

System.out.println("处理" + jeecgDemoList.size() + "条数据耗时:" + (System.currentTimeMillis() - a) + "毫秒");

}

经验分享 程序员 微信小程序 职场和发展