• java中使用JDBC的preparedStatement批处理数据的添加


    在项目中我们偶尔可能会遇到批量向数据库中导入数据,如果批处理的情况较多的情况下可以使用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)); } }
  • 相关阅读:
    Java日志体系(1) —— 那些年那些事,那些日志的历史
    直播工作原理
    【PAT乙级 】1003. 我要通过!
    [牛客网刷题]被3整除
    [牛客网刷题]牛牛找工作
    Mybatis的简单分析
    数位DP
    正则表达式
    能量球
    从此,我们相伴,不离不弃
  • 原文地址:https://www.cnblogs.com/whx20100101/p/10593643.html
Copyright © 2020-2023  润新知