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));
    }
}