Druid连接TIDB数据库生成表和插入值代码

/**
 * @author daimao
 * @date 2022/3/15 5:57 下午
 */
@EqualsAndHashCode(callSuper = true)
@ConfigurationProperties(prefix = "datasource.tidb")
@Component
@Data
@Slf4j
public class TidbSource extends JDBCCacheSource {

    /**
     * 数据源
     */
    private static DataSource dataSource = null;

    /**
     * 数据库名
     */
    private String database;

    /**
     * 驱动类型
     */
    private String driverClassName = "com.mysql.jdbc.Driver";

    private String getJdbcUrl() {
        return String.format("jdbc:mysql://%s:%s/%s?useUnicode=true&characterEncoding=UTF-8&useSSL=false&rewriteBatchedStatements=true&autoReconnect=true&failOverReadOnly=false",
                this.host, this.port, this.database);
    }

    @PostConstruct
    public void init() {
        Properties properties = new Properties();
        properties.setProperty("url", this.getJdbcUrl());
        properties.setProperty("username", this.username);
        properties.setProperty("password", this.password);
        properties.setProperty("driverClassName", this.driverClassName);
        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
            throw new SqlExecuteException("tidb获得jdbc连接失败");
        }
    }

    /**
     * 获得connect
     */
    @Override
    public Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SqlExecuteException("tidb获得jdbc连接失败");
        }
    }


    /**
     * 创建tidb表
     *
     * @param tableName 表名称
     * @param columns   列表
     * @param comment   注释
     */
    @Override
    public void createTable(String tableName, List<Column> columns, String comment) {
        //获得创表语句
        String createTableSql = buildCreateTableSql(tableName, columns, comment);
        Connection connection = this.getConnection();
        /**
         * 扩大对象的使用范围，防止在catch处关闭资源爆红
         */
        Statement statement = null;
        //执行SQL
        try {
            statement = connection.createStatement();
            int count = statement.executeUpdate(createTableSql);
            log.info(String.format("tidb在%s创建表%s", DateTime.now().toDateStr(), tableName));
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SqlExecuteException(String.format("tidb创建表%s失败", tableName));
        } finally {
            closeStatement(statement);
        }


    }

    /**
     * 将数据插入表中
     *
     * @param tableName 表名
     * @param columns   字段
     * @param data      数据
     */
    @Override
    public void insertTable(String tableName, List<Column> columns, List<Map<String, Object>> data) {
        if (CollectionUtil.isEmpty(data) || StrUtil.isBlank(tableName)) {
            return;
        }
        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            //这里必须设置为false，我们手动批量提交
            connection.setAutoCommit(false);
            //SQL语句预处理，就是values(?,?,...,?)，否则批处理不起作用
            preparedStatement = connection.prepareStatement(buildInsertPrepareSql(tableName, data));
            //插入数据
            for (Map<String, Object> columnData : data) {
                //获得字段
                List<String> columnNames = new ArrayList<>(columnData.keySet());
                try {
                    for (int i = 0; i < columnNames.size(); i++) {
                        //获得当前字段的数据类型
                        int finalI = i;
                        Column phyColumn = columns.stream().filter(column -> column.getName().equals(columnNames.get(finalI))).collect(Collectors.toList()).get(0);
                        //赋值进入批序列
                        preparedStatementSetObject(preparedStatement, phyColumn, i, columnData.get(columnNames.get(i)));
                    }
                    //将要执行的SQL语句先添加进去，不执行
                    preparedStatement.addBatch();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new SqlExecuteException("批量插入数据出现错误");
                }
            }
            //执行插入
            preparedStatement.executeBatch();
            connection.commit();
            log.info(String.format("tidb %s 插入数据", tableName));
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SqlExecuteException("批量插入数据出现错误");
        } finally {
            //关闭预处理
            closeStatement(preparedStatement);
            //关闭连接
            closeConnection(connection);
        }

    }

    /**
     * 创建tidb表
     *
     * @param tableName 表名称
     * @param columns   列表
     * @param comment   注释
     */
    private String buildCreateTableSql(String tableName, List<Column> columns, String comment) {
        StringBuilder createSql = new StringBuilder();
        createSql.append(String.format("CREATE TABLE IF NOT EXISTS `%s` (", tableName));
        //设置主键
        createSql.append(" `id` BIGINT UNSIGNED AUTO_INCREMENT, ");
        //拼接字段列表
        columns.forEach(column -> {
            createSql.append(String.format(" `%s` %s COMMENT '%s',", column.getName(), DBUtils.convertJavaTypeToDBType(column.getDataType()), column.getComment()));
        });
        //拼接主键及引擎信息
        createSql.append(String.format(" PRIMARY KEY (`id`) )ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='%s';", comment));
        return createSql.toString();
    }

    private String buildInsertPrepareSql(String tableName, List<Map<String, Object>> data) {
        StringBuilder insertPrepareSql = new StringBuilder();
        insertPrepareSql.append(String.format("insert into %s (", tableName));
        //遍历字段
        Map<String, Object> columnData = data.get(0);
        //获得字段名
        Set<String> columnSet = columnData.keySet();
        //拼接sql
        columnSet.forEach(column -> {
            insertPrepareSql.append(String.format("`%s`,", column));
        });
        //删除最后一个逗号
        insertPrepareSql.deleteCharAt(insertPrepareSql.length() - 1);
        //拼接SQL
        insertPrepareSql.append(") values (");
        //拼接问号
        columnSet.forEach(column -> {
            insertPrepareSql.append("?,");
        });
        //删除最后一个逗号
        insertPrepareSql.deleteCharAt(insertPrepareSql.length() - 1);
        insertPrepareSql.append(")");
        return insertPrepareSql.toString();
    }

    private void preparedStatementSetObject(PreparedStatement preparedStatement, Column column, Integer index, Object data) throws SQLException {
        index++;
        switch (column.getDataType()) {
            case "int":
            case "Integer":
                preparedStatement.setInt(index, Convert.toInt(data));
                break;
            case "long":
            case "Long":
                preparedStatement.setLong(index, Convert.toLong(data));
                break;
            case "bool":
            case "Boolean":
                preparedStatement.setBoolean(index, Convert.toBool(data));
                break;
            case "String":
            default:
                preparedStatement.setString(index, Convert.toStr(data));
                break;
        }
    }
}