在项目中我们偶尔可能会遇到批量向数据库中导入数据,如果批处理的情况较多的情况下可以使用spring batch,如果只是一个导入功能的话可以考虑使用jdbc的preparedStatement处理。
1.使用逻辑,首先根据导入的文件,将数据转换成我们的对象放进list集合中。
2.创建preparedStatement对象,处理数据。
下面是具体的示例:
1.创建表:
CREATE TABLE TEST ( ID VARCHAR2(50) NOT NULL, NAME VARCHAR2(50) NOT NULL, HOME VARCHAR2(50) NOT NULL, TEL VARCHAR2(50) NOT NULL, PATH VARCHAR2(50) )
2.实体类:
@Getter @Setter @ToString public class TestModel { private String id; private String name; private String home; private String tel; private String path; }
3.Dao层:
@Mapper public interface TestDao { public List<TestModel> selectList(); public void addData(List<TestModel> list); }
4.mapper.xml:
<mapper namespace="com.jason.springboot.demo.web.dao.TestDao"> <select id="selectList" resultType="com.jason.springboot.demo.web.pojo.TestModel"> select id id, name name, home home, tel tel, path path from test </select> <insert id="addData" parameterType="com.jason.springboot.demo.web.pojo.TestModel"> INSERT INTO TEST VALUES(#{id},#{name},#{home},#{tel},#{path}) </insert> </mapper>
5.批处理类:
/** * 使用JDBC preparedStatement批处理数据 * */ @Service public class SqlBatch { @Autowired private SqlSessionFactory sqlSessionFactory; public static final String MAPPER_01 = "com.jason.springboot.demo.web.dao.TestDao.addData"; public static final String SQL_ADD = "INSERT INTO TEST VALUES(?,?,?,?,?)"; public void batchSelect(List<TestModel> list) { String sql = ""; SqlSession session = null; PreparedStatement prepareStatement = null; try { if (list == null || list.isEmpty()) { return; } Map<String, Object> map = new HashMap<String, Object>(); map.put("id", list.get(0).getId()); map.put("name", list.get(0).getName()); map.put("home", list.get(0).getHome()); map.put("tel", list.get(0).getTel()); map.put("path", list.get(0).getPath()); sql = getSql(MAPPER_01, map); // 或者 // sql=SQL_ADD ; session = sqlSessionFactory.openSession(ExecutorType.BATCH, true); prepareStatement = session.getConnection().prepareStatement(sql); for (TestModel model : list) { prepareStatement.setString(1, model.getId()); prepareStatement.setString(2, model.getName()); prepareStatement.setString(3, model.getHome()); prepareStatement.setString(4, model.getTel()); prepareStatement.setString(5, model.getPath()); prepareStatement.addBatch(); } int[] executeBatch = prepareStatement.executeBatch(); System.out.println(executeBatch); } catch (SQLException e) { System.out.println("批量处理SQL异常:" + e); } catch (Exception e) { System.out.println("批处理添加数据异常:" + e); } finally { try { prepareStatement.close(); } catch (SQLException e) { System.out.println("关闭批处理异常:" + e); } } } // 根据参数从MyBatis中获取对应的sql private String getSql(String mapper, Map<String, Object> param) { return sqlSessionFactory.getConfiguration().getMappedStatement(mapper).getBoundSql(param).getSql(); } }
6.Controller类,测试添加数据:
@RestController public class DemoController { @Autowired private TestService testService; @Autowired private SqlBatch sqlBatch; private static final Logger LOGGER = LoggerFactory.getLogger(DemoController.class); @RequestMapping("/test") public String test1() { addData(); List<TestModel> selectList = testService.selectList();
List<TestModel> newList = new ArrayList<>(); newList.addAll(selectList.subList(0, 20));
for (TestModel testModel : newList) { LOGGER.info("数据:{}", testModel.toString()); } newList.clear();return " spring boot is starting ...."; } private void addData() { long start = System.currentTimeMillis();
// 造数据 List<TestModel> list = new ArrayList<TestModel>(); for (int i = 0; i < 50000; i++) { TestModel model = new TestModel(); model.setId(UUID.randomUUID().toString().replace("-", "")); model.setName("姓名" + i); model.setHome("家庭地址" + i); model.setTel("联系电话" + i); model.setPath("URL:" + i); list.add(model); }
// 批量添加 sqlBatch.batchSelect(list); long end = System.currentTimeMillis(); LOGGER.info("总共耗时:[{}]", (end - start)); } }