import com.alibaba.nacos.client.naming.utils.CollectionUtils;
import org.springframework.stereotype.Service;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
/**
* @ClassName : DataTableCreateService
* @Description :
*/
@Service
public class DataTableCreateServiceImpl implements IDataTableCreateService {
/**
* Description : 创建表格
*
* @param connection
* @param tableName 表名称
* @param tableRemarks 表注释
* @param columnVos 字段信息
* @throws Exception
*/
@Override
public R createTable(Connection connection, String tableName, String tableRemarks, List<ColumnVo> columnVos) throws Exception {
try {
if (connection != null && !CollectionUtils.isEmpty(columnVos)) {
//判断表名是否重复
ResultSet tables = connection.getMetaData().getTables(null, null, tableName, new String[]{"TABLE", "VIEW"});
if (tables.next()) {
return R.fail("该表名已存在,请重新输入！");
}
tables.close();
String sql = "";
if (connection.getMetaData().getDriverName().toUpperCase().indexOf("MYSQL") != -1) {
sql = getMySqlCreate(tableName, tableRemarks, columnVos);
} else if (connection.getMetaData().getDriverName().toUpperCase().indexOf("SQLSERVER") != -1) {
sql = getSqlServerCreate(tableName, tableRemarks, columnVos);
} else if (connection.getMetaData().getDriverName().toUpperCase().indexOf("ORACLE") != -1) {
sql = getOracleCreate(tableName, tableRemarks, columnVos);
} else if (connection.getMetaData().getDriverName().toUpperCase().indexOf("HIVE") != -1) {
sql = getHiveCreate(tableName, tableRemarks, columnVos);
} else if (connection.getMetaData().getDriverName().toUpperCase().indexOf("PHOENIX") != -1) {
sql = getHbaseCreate(tableName, tableRemarks, columnVos);
}
Statement statement = connection.createStatement();
statement.execute(sql);
connection.close();
return R.success("新建表成功");
} else {
return R.fail("新建表方法参数空值异常");
}
} catch (Exception e) {
e.printStackTrace();
throw new Exception("创建表格出现异常！");
}
}
/**
* Description : 生成MySql的建表语句
*
* @param tableName 表名称
* @param tableRemarks 表注释
* @param columnVos 字段信息
* @return : java.lang.String
* @throws Exception
*/
public String getMySqlCreate(String tableName, String tableRemarks, List<ColumnVo> columnVos) throws Exception {
try {
StringBuffer sqlBuffer = new StringBuffer();
sqlBuffer.append("create table " + tableName + " (");
for (int i = 0; i < columnVos.size(); i++) {
ColumnVo columnVo = columnVos.get(i);
String columnName = columnVo.getColumnName();
String columnType = columnVo.getColumnType();
Integer columnSize = columnVo.getColumnSize();
if ("INT".equals(columnType.toUpperCase())) {
sqlBuffer.append(columnName + " int " + "(" + columnSize + ") ");
} else if ("DOUBLE".equals(columnType.toUpperCase())) {
sqlBuffer.append(columnName + " double ");
} else if ("VARCHAR".equals(columnType.toUpperCase())) {
sqlBuffer.append(columnName + " varchar " + "(" + columnSize + ") ");
} else if ("DATETIME".equals(columnType.toUpperCase())) {
sqlBuffer.append(columnName + " datetime ");
}
Boolean isPrimary = columnVo.getPrimary();
String primary = "";
if (isPrimary != null && isPrimary) {
primary = " primary key ";
}
Boolean isNullable = columnVo.getNullable();
String nullable = "";
if (isNullable != null && !isNullable) {
nullable = " not null ";
}
String remarks = columnVo.getRemarks();
sqlBuffer.append(primary + nullable + " comment '" + remarks + "',");
}
String s = sqlBuffer.toString();
String sql = s.substring(0, s.length() - 1) + ") COMMENT='" + tableRemarks + "'";
return sql;
} catch (Exception e) {
e.printStackTrace();
throw new Exception("生成MySql的建表语句出现异常！");
}
}
/**
* Description : 生成SqlServer的建表语句
*
* @param tableName 表名称
* @param tableRemarks 表注释
* @param columnVos 字段信息
* @return : java.lang.String
* @throws Exception
*/
String getSqlServerCreate(String tableName, String tableRemarks, List<ColumnVo> columnVos) {
StringBuffer sqlBuffer = new StringBuffer();
sqlBuffer.append("create table " + tableName + " (");
for (int i = 0; i < columnVos.size(); i++) {
ColumnVo columnVo = columnVos.get(i);
String columnName = columnVo.getColumnName();
String columnType = columnVo.getColumnType();
Integer columnSize = columnVo.getColumnSize();
String type = "";
if ("整数".equals(columnType)) {
type = "int";
sqlBuffer.append(columnName + " " + type + "(" + columnSize + ") ");
} else if ("小数".equals(columnType)) {
type = "float";
sqlBuffer.append(columnName + " " + type + " ");
} else if ("时间".equals(columnType)) {
type = "datetime";
sqlBuffer.append(columnName + " " + type + " ");
} else if ("字符串".equals(columnType)) {
type = "varchar";
sqlBuffer.append(columnName + " " + type + "(" + columnSize + ") ");
}
Boolean isPrimary = columnVo.getPrimary();
String primary = "";
if (isPrimary != null && isPrimary) {
primary = " primary key ";
}
Boolean isNullable = columnVo.getNullable();
String nullable = "";
if (isNullable != null && !isNullable) {
nullable = " not null ";
}
sqlBuffer.append(primary + nullable + ",");
}
String s = sqlBuffer.toString();
String sql = s.substring(0, s.length() - 1) + ")" + "\n" + "GO" + "\n ";
StringBuffer sqlBuffer1 = new StringBuffer();
for (int i = 0; i < columnVos.size(); i++) {
ColumnVo columnVo = columnVos.get(i);
String columnName = columnVo.getColumnName();
String remarks = columnVo.getRemarks();
sqlBuffer1.append("exec sp_addextendedproperty N'MS_Description', N'" + remarks + "', N'user', N'dbo', N'table', N'" + tableName + "', N'column', N'" + columnName + "' " + "\n" + "GO" + "\n ");
}
sqlBuffer1.append("exec sp_addextendedproperty N'MS_Description', N'" + tableRemarks + "', N'user', N'dbo', N'table', N'" + tableName + "', null, null" + "\n" + "GO" + "\n ");
String s1 = sql.concat(sqlBuffer1.toString());
return s1;
}
/**
* Description : 生成Oracle的建表语句
*
* @param tableName 表名称
* @param tableRemarks 表注释
* @param columnVos 字段信息
* @return : java.lang.String
* @throws Exception
*/
String getOracleCreate(String tableName, String tableRemarks, List<ColumnVo> columnVos) {
StringBuffer sqlBuffer = new StringBuffer();
sqlBuffer.append("create table " + tableName + " (");
for (int i = 0; i < columnVos.size(); i++) {
ColumnVo columnVo = columnVos.get(i);
String columnName = columnVo.getColumnName();
String columnType = columnVo.getColumnType();
Integer columnSize = columnVo.getColumnSize();
String type = "";
if ("整数".equals(columnType)) {
type = "NUMBER";
sqlBuffer.append(columnName + " " + type + "(" + columnSize + ") ");
} else if ("小数".equals(columnType)) {
type = "NUMBER";
sqlBuffer.append(columnName + " " + type + " ");
} else if ("字符串".equals(columnType)) {
type = "VARCHAR2";
sqlBuffer.append(columnName + " " + type + "(" + columnSize + ") ");
} else if ("时间".equals(columnType)) {
type = "DATE";
sqlBuffer.append(columnName + " " + type + " ");
}
Boolean isPrimary = columnVo.getPrimary();
String primary = "";
if (isPrimary != null && isPrimary) {
primary = " primary key ";
}
Boolean isNullable = columnVo.getNullable();
String nullable = "";
if (isNullable != null && !isNullable) {
nullable = " not null ";
}
sqlBuffer.append(primary + nullable + ",");
}
String s = sqlBuffer.toString();
String sql = s.substring(0, s.length() - 1) + ");";
StringBuffer sqlBuffer1 = new StringBuffer();
for (int i = 0; i < columnVos.size(); i++) {
ColumnVo columnVo = columnVos.get(i);
String columnName = columnVo.getColumnName();
String remarks = columnVo.getRemarks();
sqlBuffer1.append("comment on column " + tableName + "." + columnName + " is '" + remarks + "';");
}
sqlBuffer1.append("comment on table " + tableName + " is '" + tableRemarks + "';");
String s1 = sql.concat(sqlBuffer1.toString());
return s1;
}
/**
* Description : 生成Hive的建表语句
*
* @param tableName 表名称
* @param tableRemarks 表注释
* @param columnVos 字段信息
* @return : java.lang.String
* @throws Exception
*/
public String getHiveCreate(String tableName, String tableRemarks, List<ColumnVo> columnVos) throws Exception {
try {
StringBuffer sqlBuffer = new StringBuffer();
sqlBuffer.append("create table " + tableName + " (");
for (int i = 0; i < columnVos.size(); i++) {
ColumnVo columnVo = columnVos.get(i);
String columnName = columnVo.getColumnName();
String columnType = columnVo.getColumnType();
Integer columnSize = columnVo.getColumnSize();
if ("INT".equals(columnType.toUpperCase())) {
sqlBuffer.append(columnName + " int ");
} else if ("DOUBLE".equals(columnType.toUpperCase())) {
sqlBuffer.append(columnName + " double ");
} else if ("VARCHAR".equals(columnType.toUpperCase())) {
sqlBuffer.append(columnName + " varchar " + "(" + columnSize + ") ");
} else if ("DATETIME".equals(columnType.toUpperCase())) {
sqlBuffer.append(columnName + " datetime ");
} else if ("TIMESTAMP".equals(columnType.toUpperCase())) {
sqlBuffer.append(columnName + " timestamp ");
}
String remarks = columnVo.getRemarks();
sqlBuffer.append(" comment '" + remarks + "',");
}
String s = sqlBuffer.toString();
String sql = s.substring(0, s.length() - 1) + ") COMMENT '" + tableRemarks + "'";
return sql;
} catch (Exception e) {
e.printStackTrace();
throw new Exception("生成hive的建表语句出现异常！");
}
}
/**
* Description : 生成Hbase的建表语句
*
* @param tableName 表名称
* @param tableRemarks 表注释
* @param columnVos 字段信息
* @return : java.lang.String
* @throws Exception
*/
public String getHbaseCreate(String tableName, String tableRemarks, List<ColumnVo> columnVos) throws Exception {
try {
StringBuffer sqlBuffer = new StringBuffer();
sqlBuffer.append("create table " + tableName + " (");
for (int i = 0; i < columnVos.size(); i++) {
ColumnVo columnVo = columnVos.get(i);
String columnName = columnVo.getColumnName();
String columnType = columnVo.getColumnType();
Integer columnSize = columnVo.getColumnSize();
if ("INT".equals(columnType.toUpperCase())) {
sqlBuffer.append(columnName + " BIGINT ");
} else if ("CHAR".equals(columnType.toUpperCase())) {
sqlBuffer.append(columnName + " char " + "(" + columnSize + ") ");
} else if ("DATE".equals(columnType.toUpperCase())) {
sqlBuffer.append(columnName + " date ");
}
Boolean isNullable = columnVo.getNullable();
String nullable = "";
if (isNullable != null && !isNullable) {
nullable = " not null ";
}
Boolean isPrimary = columnVo.getPrimary();
String primary = "";
if (isPrimary != null && isPrimary) {
primary = " primary key ";
}
String remarks = columnVo.getRemarks();
sqlBuffer.append(nullable + primary + " ,");
}
String s = sqlBuffer.toString();
String sql = s.substring(0, s.length() - 1) + ") ";
return sql;
} catch (Exception e) {
e.printStackTrace();
throw new Exception("生成hbase的建表语句出现异常！");
}
}
}
