Java代码批量插入数据到MySQL
2021/12/15 2:23:49
本文主要是介绍Java代码批量插入数据到MySQL,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
1、批量插入
import java.io.BufferedReader; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileReader; import java.io.FileWriter; import java.io.IOException; import java.util.Random; public class SQLGenApplication { public static void main(String[] args) throws IOException { StringBuilder insertBuf = new StringBuilder("INSERT INTO `table1` (`id`, `uid`, `a`, `b`) VALUES"); String values = "('%s', '%s', '%s.00', '%s.00'),"; BufferedReader fis = new BufferedReader(new FileReader("fileParam.txt")); String line = null; Random r = new Random(); int cnt = 0; int batch = 0; int perCnt = 2500; while((line = fis.readLine()) != null) { long id = Long.parseLong(line.substring(8)); int i = r.nextInt(100); while(i <= 0 ) { i = r.nextInt(100); } int i2 = r.nextInt(100); while(i2 < i ) { i2 = r.nextInt(100); } insertBuf.append(String.format(values, id, line, i, i2)); if(cnt < perCnt) { cnt++; } else { insertBuf.deleteCharAt(insertBuf.length()-1); insertBuf.append(";"); FileWriter fw = new FileWriter("fileSQL" + batch + ".sql"); fw.write(insertBuf.toString()); fw.flush(); fw.close(); cnt = 0; batch++; insertBuf = new StringBuilder("INSERT INTO `table1` (`id`, `uid`, `a`, `b`) VALUES"); } } if(cnt != 0 && cnt < perCnt) { insertBuf.deleteCharAt(insertBuf.length()-1); insertBuf.append(";"); FileWriter fw = new FileWriter("fileSQL" + batch + ".sql"); fw.write(insertBuf.toString()); fw.flush(); fw.close(); cnt = 0; } fis.close(); } }
2、存储过程插入
use `数据库名称`; DELIMITER $$ drop procedure if exists data_100w_gen$$ create procedure data_100w_gen(IN startIdx int, IN endIdx int, IN prefix varchar(1000), IN surfix varchar(1000), out ex_sql_out longtext) begin declare ex_sql longtext default prefix; -- 超时设置 set global delayed_insert_timeout=20000; set global connect_timeout = 20000; set global net_read_timeout = 20000; set global net_write_timeout = 20000; while startIdx<=endIdx-1 DO set ex_sql = concat(ex_sql, "(", CAST(startIdx AS CHAR), surfix, ","); set startIdx=startIdx+1; end while; set ex_sql_out = concat(ex_sql, "(", CAST(endIdx AS CHAR), surfix, ";"); -- select ex_sql_out from dual; end$$ drop procedure if exists data_batch_gen$$ create procedure data_batch_gen(IN startIdx int, IN endIdx int, IN prefix varchar(1000), IN surfix varchar(1000)) begin declare batchSize int default 25000; declare batchSize_1 int default batchSize-1; declare endIdxGen int default 0; set @ex_sql_out = ""; if(endIdx < batchSize) then call data_100w_gen(startIdx, endIdx, prefix, surfix, @ex_sql_out); PREPARE data_gen_prep FROM @ex_sql_out; EXECUTE data_gen_prep; DEALLOCATE PREPARE data_gen_prep; else while startIdx<=endIdx DO -- 循环开始 set endIdxGen = startIdx + batchSize_1; if(endIdxGen > endIdx) then set endIdxGen = endIdx; end if; call data_100w_gen(startIdx, endIdxGen, prefix, surfix, @ex_sql_out); PREPARE data_gen_prep FROM @ex_sql_out; EXECUTE data_gen_prep; DEALLOCATE PREPARE data_gen_prep; set startIdx = endIdxGen + 1; select endIdxGen from dual; -- 打印每次生成数量 end while; -- 循环结束 end if; -- select @ex_sql_out from dual; end$$ delimiter ;
转自:https://onefire.blog.csdn.net/article/details/120015121
这篇关于Java代码批量插入数据到MySQL的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-10-05小米13T Pro系统合集:性能与摄影的极致融合,值得你升级的系统ROM
- 2024-10-01基于Python+Vue开发的医院门诊预约挂号系统
- 2024-10-01基于Python+Vue开发的旅游景区管理系统
- 2024-10-01RestfulAPI入门指南:打造简单易懂的API接口
- 2024-10-01初学者指南:了解和使用Server Action
- 2024-10-01Server Component入门指南:搭建与配置详解
- 2024-10-01React 中使用 useRequest 实现数据请求
- 2024-10-01使用 golang 将ETH账户的资产平均分散到其他账户
- 2024-10-01JWT用户校验课程:从入门到实践
- 2024-10-01Server Component课程入门指南