JdbcTemplate批量插入数据
运行环境:SpringBoot,注入JdbcTemplate
@Autowired private JdbcTemplate jdbcTemplate;
1、单表批量插入数据
@Test public void batchTest() throws IOException { InputStream inputStream = new FileInputStream("O:\\files\\测试.txt"); List<Object[]> list = new ArrayList<>(); for (String readLine : IOUtils.readLines(inputStream,"utf-8")){ String[] s = readLine.split("\\|"); //list.add(new Object[]{s[0],s[1],s[2],s[3],s[4]}); list.add(new Object[]{s[1],s[0]}); } //遍历List<Object[]>集合中的每一个元素 /* for (Object[] o : list){ for (int i = 0; i < o.length; i++) { System.out.println("==========="+o[i]); } }*/ //执行批量插入操作 String sql = "INSERT INTO `test`(`name`,`area`) VALUES (?,?)"; jdbcTemplate.batchUpdate(sql, list); }
样本数据:测试.txt
测试数据1|测试数据2|测试数据3
测试数据4|测试数据5|测试数据6
测试数据7|测试数据8|测试数据9
单次循环插入:
1 public void insertToTag(String filename) throws IOException { 2 InputStream is = new FileInputStream(filename); 3 4 Map<String,String> map = new HashMap<>(); 5 for (String readLine : IOUtils.readLines(is, "utf-8")) { 6 String[] s = readLine.split("\\|"); 7 map.put(s[1],s[0]); 8 } 9 10 String sql = "INSERT INTO `test`(`name`,`area`) VALUES (?,?)"; 11 for (Map.Entry<String, String> stringStringEntry : map.entrySet()) { 12 // System.out.println(stringStringEntry.getKey()+"-->"+stringStringEntry.getValue()); 13 jdbcTemplate.update(sql,stringStringEntry.getKey(),stringStringEntry.getValue()); 14 } 15 16 }
2、联表插入数据,将字典表中的id插入到另一张表
1 public void insertToGuideBatch(String filename) throws IOException { 2 3 InputStream is = new FileInputStream(filename); 4 5 //字段名,id值 6 Map<String,Long> map1 = new HashMap<>(); 7 8 //该方法类似于set集合 9 SqlRowSet s1 = jdbcTemplate.queryForRowSet("SELECT `id`,`area` FROM g_enforcement_tag"); 10 while (s1.next()){ 11 map1.put(s1.getString("area"),s1.getLong("id")); 12 } 13 14 for (String readLine : IOUtils.readLines(is, "utf-8")) { 15 String[] s = readLine.split("\\|"); 16 Long id = map1.get(s[0]); 17 // jdbcTemplate.update("insert into xxx value (?,?)",id,s[4]); 18 jdbcTemplate.update("INSERT INTO `g_enforcement_guide`(`prop1`,`prop2`,`prop3`,`prop4`,`prop5`) VALUES (?,?,?,?,?)","test",id,s[2],s[3],s[4]); 19 20 } 21 }
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步