JDBC连接批量处理数据入库

最新推荐文章于 2024-06-06 15:20:00 发布

MolinOne

最新推荐文章于 2024-06-06 15:20:00 发布

阅读量594

点赞数

分类专栏： Java

本文链接：https://blog.youkuaiyun.com/u010537432/article/details/110393538

版权

Java 专栏收录该内容

7 篇文章

订阅专栏

该代码实现了一个JdbcTemplate类，用于获取数据库连接、提交事务、开启事务、回滚事务以及释放数据库资源。此外，还展示了如何使用线程池ExecutorService执行批量SQL插入操作，每个线程处理一组InsertSqlEntity对象，确保数据入库的并发处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

public class JdbcTemplate {
    private static String DRIVER_CLASS_NAME = "*";
    private static String URL = "*";
    private static String USERNAME = "*";
    private static String PASSWORD = "*";



    /**
     *
     * <Description> 获取数据库连接<br>
     *
     * @return
     * @throws Exception
     * <br>
     */
    public static Connection getConnection() throws Exception {
        Class.forName(DRIVER_CLASS_NAME);
        Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        return connection;
    }

    /**
     *
     * <Description> 提交事务<br>
     *
     * @param connection
     * <br>
     */
    public static void commit(Connection connection) {
        try {
            connection.commit();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     *
     * <Description> 开启事务<br>
     *
     * @param connection
     * <br>
     */
    public static void beginTx(Connection connection) {
        try {
            connection.setAutoCommit(false);
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     *
     * <Description> 回滚<br>
     *
     * @param connection
     * <br>
     */
    public static void rollback(Connection connection) {
        try {
            connection.rollback();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     *
     * <Description> TODO<br>
     *     
     * @param statement
     * @param connection
     * <br>
     */
    public static void releaseDb(Statement statement, Connection connection) {
        try {
            if(statement != null){
                statement.close();
            }

            if(connection != null){
                connection.close();
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void insertData(List<String> sqlList) {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = JdbcTemplate.getConnection();
            JdbcTemplate.beginTx(connection);
            statement = connection.createStatement();
            for (String sql : sqlList) {
                statement.addBatch(sql);
            }
            statement.executeBatch();
            statement.clearBatch();
            JdbcTemplate.commit(connection);
        } catch (Exception ex) {
            JdbcTemplate.rollback(connection);
            ex.printStackTrace();
        } finally {
            JdbcTemplate.releaseDb(statement, connection);
        }

    }
}

调用（线程池处理）：

public Boolean intoDB(List<List<InsertSqlEntity>> lists, String handle, String strTime) throws InterruptedException {
        System.out.println("数据入库线程任务执行开始");
        // 开始时间
        long start = System.currentTimeMillis();

        // 线程数
        int threadNum = 50;

        // 创建一个线程池
        ExecutorService exec = Executors.newFixedThreadPool(threadNum);

        // 定义一个任务集合
        List<Callable<Integer>> tasks = new ArrayList<Callable<Integer>>();

        Callable<Integer> task = null;

        List<Map<String, Object>> cutList = null;


        // 确定每条线程的数据
        for (List<InsertSqlEntity> list : lists) {
            task = new Callable<Integer>() {

                @Override
                public Integer call() throws Exception {
                    try {
                        List<String> sqlList = new ArrayList<>();
                        for (InsertSqlEntity insertSqlEntity : list) {
                            String insertHeadSql = "";
                            if (handle.intern() == "update") {
                                try {
                                    String[] keys = insertSqlEntity.getColumns().split(",");
                                    String[] vals = insertSqlEntity.getValues().split(",");
                                    insertHeadSql = "update " + insertSqlEntity.getTableName() + " set ";
                                    for (int i = 2; i < keys.length - 1; i++) {
                                        insertHeadSql += keys[i] + " = " + vals[i] + ",";
                                    }

                                    insertHeadSql = insertHeadSql.substring(0, insertHeadSql.length() - 1);
                                    insertHeadSql += " where sb_id = " + vals[0] + " and type = " + vals[1] + " and READABLE_DATE = to_date('" + strTime + "','yyyy-mm-dd')";
                                } catch (Exception exception) {
                                    exception.printStackTrace();
                                }
                            } else {
                                insertHeadSql = "insert into " + insertSqlEntity.getTableName() + "(" + insertSqlEntity.getColumns() + ") values(" + insertSqlEntity.getValues() + ")";
                            }

                            sqlList.add(insertHeadSql);
                        }
                        JdbcTemplate.insertData(sqlList);
                        sqlList.clear();
                    } catch (Exception exception) {
                        exception.printStackTrace();
                    }

                    return 1;
                }

            };

            // 这里提交的任务容器列表和返回的Future列表存在顺序对应的关系
            tasks.add(task);
        }

        List<Future<Integer>> results = exec.invokeAll(tasks);

        // 关闭线程池
        exec.shutdown();
        System.out.println("数据入库线程任务执行结束");
        System.err.println("执行任务消耗了 ：" + (System.currentTimeMillis() - start) + "毫秒");

        return true;
    }

实体类：

public class InsertSqlEntity {

  private String tableName;
  private String columns;
  private String values;

  private List<String> valueList;

  public String getTableName() {
    return tableName;
  }

  public void setTableName(String tableName) {
    this.tableName = tableName;
  }

  public String getColumns() {
    return columns;
  }

  public void setColumns(String columns) {
    this.columns = columns;
  }

  public String getValues() {
    return values;
  }

  public void setValues(String values) {
    this.values = values;
  }

  public List<String> getValueList() {
    return valueList;
  }

  public void setValueList(List<String> valueList) {
    this.valueList = valueList;
  }
}