/**
* @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;
}
}
}
Druid连接TIDB数据库生成表和插入值代码
于 2022-03-24 16:00:04 首次发布