package com.product.server.report.controller;
|
|
import java.sql.Connection;
|
import java.sql.PreparedStatement;
|
import java.sql.ResultSet;
|
import java.sql.ResultSetMetaData;
|
import java.sql.SQLException;
|
import java.sql.Statement;
|
|
import javax.sql.DataSource;
|
|
import com.product.core.config.ErrorCode;
|
import com.product.core.connection.ConnectionManager;
|
import com.product.core.dao.support.Dao;
|
import com.product.core.entity.DataTableEntity;
|
import com.product.core.entity.FieldSetEntity;
|
import com.product.core.exception.BaseException;
|
import com.mysql.cj.jdbc.DatabaseMetaData;
|
|
|
public class TestDao {
|
/**
|
* 创建表
|
* @param tfs
|
* @return
|
*/
|
public static boolean createTable(FieldSetEntity tfs) throws BaseException{
|
if(tfs == null || tfs.getString("table_name") ==null || "".equals(tfs.getString("table_name")) ) {
|
throw new BaseException(
|
ErrorCode.DATA_CREATE_TABLE.getValue()
|
,ErrorCode.DATA_CREATE_TABLE.getText()
|
,Dao.class
|
,"createTable(FieldSetEntity tfs)"
|
);
|
}
|
int table_type=tfs.getInteger("table_type");
|
|
PreparedStatement ps = null;
|
Connection con = null;
|
try{
|
if(table_type==2) {
|
//创建视图表
|
con =ConnectionManager.getConnection();
|
StringBuffer viewsql=new StringBuffer();
|
viewsql.append(" create view `").append(tfs.getString("table_name")).append("` as ( ");
|
viewsql.append(tfs.getString("create_view_sql")).append(" ); ");//msv_execute_sql 执行的SQL语句
|
System.out.println(" DDL:"+viewsql.toString());
|
ps = con.prepareStatement(viewsql.toString());
|
ps.execute();
|
return true;
|
}
|
con =ConnectionManager.getConnection();
|
StringBuffer sql=new StringBuffer();
|
sql.append(" CREATE TABLE `").append(tfs.getString("table_name")).append("` ( ");
|
sql.append("`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键' , ");
|
sql.append("`uuid` varchar(64) NOT NULL COMMENT '唯一标识', ");
|
sql.append("`created_by` varchar(50) DEFAULT NULL COMMENT '创建者', ");
|
sql.append("`created_utc_datetime` datetime DEFAULT NULL COMMENT '创建时间', ");
|
sql.append("`updated_by` varchar(50) DEFAULT NULL COMMENT '更新者', ");
|
sql.append("`updated_utc_datetime` datetime DEFAULT NULL COMMENT '更新时间', ");
|
sql.append("PRIMARY KEY (`id`) USING BTREE, ");
|
sql.append("UNIQUE KEY `uuid` (`uuid`) USING BTREE ");
|
sql.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='").append(tfs.getString("table_description")==null?"":tfs.getString("table_description")).append("' ");
|
System.out.println(" DDL:"+sql.toString());
|
ps = con.prepareStatement(sql.toString());
|
ps.execute();
|
return true;
|
}catch(Exception e){
|
throw new BaseException(
|
ErrorCode.DATA_CREATE_TABLE.getValue()
|
,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage()
|
,Dao.class
|
,"createTable(FieldSetEntity tfs)"
|
,e
|
);
|
}finally{
|
//this.closeConnection(con,ps,null);
|
}
|
}
|
/**
|
* 新增表字段
|
* @param tfs
|
* @return
|
*/
|
public static boolean createTableField(FieldSetEntity tfs) throws BaseException{
|
if(tfs == null || tfs.getString("table_name") ==null || "".equals(tfs.getString("table_name")) ) {
|
throw new BaseException(
|
ErrorCode.DATA_CREATE_TABLE.getValue()
|
,ErrorCode.DATA_CREATE_TABLE.getText()
|
,Dao.class
|
,"createTableField(FieldSetEntity tfs)"
|
);
|
}
|
|
PreparedStatement ps = null;
|
Connection con = null;
|
try{
|
con =ConnectionManager.getConnection();
|
StringBuffer sql=new StringBuffer();
|
sql.append(" ALTER TABLE `").append(tfs.getString("table_name")).append("` ADD `");
|
sql.append(tfs.getString("field_name")).append("` "+tfs.getString("field_type")).append("(");// 类型
|
sql.append(tfs.getString("field_length"));// 字段长度
|
String unit = tfs.getString("field_unit");// 小数位数
|
unit = (unit.equals("0")) ? "" : ("," + unit);
|
sql.append(unit+") ");
|
if (tfs.getString("is_required").equals("1")) {// 0不必填，1必填
|
sql.append("NOT NULL ");
|
}else {
|
sql.append("DEFAULT NULL ");
|
}
|
if (tfs.getString("is_unique").equals("1")) {// 0不唯一，1唯一
|
sql.append("unique ");
|
}
|
if(tfs.getString("field_description") != null && tfs.getString("field_description").length()>0){//字段描述
|
sql.append("COMMENT"+"'"+ tfs.getString("field_description") +"' ");
|
}
|
System.out.println(" DDL:"+sql.toString());
|
ps = con.prepareStatement(sql.toString());
|
ps.execute();
|
return true;
|
}catch(Exception e){
|
throw new BaseException(
|
ErrorCode.DATA_CREATE_TABLE.getValue()
|
,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage()
|
,Dao.class
|
,"createTableField(FieldSetEntity tfs)"
|
,e
|
);
|
}finally{
|
//this.closeConnection(con,ps,null);
|
}
|
}
|
/**
|
* 修改字段
|
* @param tfs 原字段
|
* @param newtfs 修改后的字段
|
* @return
|
* @throws BaseException
|
*/
|
public static boolean updateTableField(FieldSetEntity tfs,FieldSetEntity newtfs) throws BaseException{
|
if(newtfs == null || newtfs.getString("table_name") ==null || "".equals(newtfs.getString("table_name")) ) {
|
throw new BaseException(
|
ErrorCode.DATA_CREATE_TABLE.getValue()
|
,ErrorCode.DATA_CREATE_TABLE.getText()
|
,Dao.class
|
,"updateTableField(FieldSetEntity tfs,FieldSetEntity newtfs)"
|
);
|
}
|
|
PreparedStatement ps = null;
|
Connection con = null;
|
try{
|
con =ConnectionManager.getConnection();
|
StringBuffer sql=new StringBuffer();
|
//alter table test_newtable change newField100 newField1 VARCHAR(32) NOT NULL COMMENT '为空';
|
sql.append("ALTER TABLE `").append(newtfs.getString("table_name")).append("` ");
|
String fieldName = tfs.getString("field_name");//原表名
|
String newFieldName = newtfs.getString("field_name");//新表名
|
String isUnique = tfs.getString("is_unique");//原唯一约束
|
isUnique = isUnique==null?"0":isUnique;
|
String newIsUnique = newtfs.getString("is_unique");//新唯一约束
|
newIsUnique = newIsUnique==null?"0":newIsUnique;
|
String isRequired = tfs.getString("is_required");
|
isRequired = isRequired==null?"0":isRequired;
|
String newTfsisRequired = newtfs.getString("is_required");
|
newTfsisRequired = newTfsisRequired==null?"0":newTfsisRequired;
|
//字段名是否修改
|
if(fieldName.equals(newFieldName)){//字段名相同不用修改
|
sql.append(" MODIFY `").append(fieldName).append("` ");//不修改字段名用MODIFY
|
}else {
|
sql.append(" CHANGE `").append(fieldName).append("` `").append(newFieldName).append("` ");//修改字段名用CHANGE
|
}
|
sql.append(" ").append(newtfs.getString("field_type")).append("("+newtfs.getString("field_length"));// DEFAULT NULL COMMENT '为空';
|
String unit = newtfs.getString("field_unit");// 小数位数
|
unit = (unit.equals("0")) ? "" : ("," + unit);
|
sql.append(unit+") ");
|
if (!isRequired.equals(newTfsisRequired)) {
|
if (newTfsisRequired.equals("1")) {// 0不必填，1必填
|
sql.append("NOT NULL ");//不能为空
|
}else {
|
sql.append("DEFAULT NULL ");//能为空
|
}
|
}
|
if(newtfs.getString("field_description") != null && newtfs.getString("field_description").length()>0){//字段描述
|
sql.append("COMMENT "+"'"+ newtfs.getString("field_description") +"' ");
|
}
|
//ALTER TABLE test_newtable ADD unique(newField1)
|
//ALTER TABLE test_newtable DROP INDEX newField1;
|
System.out.println(" DDL:"+sql.toString());
|
ps = con.prepareStatement(sql.toString());
|
ps.execute();
|
if(isUnique.equals(newIsUnique)){
|
return true;
|
}else {
|
StringBuffer unique=new StringBuffer();
|
if (newIsUnique.equals("1")) {//1唯一 新增唯一约束
|
unique.append(" ALTER TABLE ").append(tfs.getString("table_name")).append(" ADD unique(").append(newFieldName+")");
|
}else{//不唯一 去掉唯一约束
|
unique.append(" ALTER TABLE ").append(tfs.getString("table_name")).append(" DROP INDEX ").append(newFieldName);
|
}
|
System.out.println(" DDL:"+unique.toString());
|
ps = con.prepareStatement(unique.toString());
|
ps.execute();
|
}
|
return true;
|
}catch(Exception e){
|
throw new BaseException(
|
ErrorCode.DATA_CREATE_TABLE.getValue()
|
,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage()
|
,Dao.class
|
,"updateTableField(FieldSetEntity tfs,FieldSetEntity newtfs)"
|
,e
|
);
|
}finally{
|
//this.closeConnection(con,ps,null);
|
}
|
}
|
/**
|
* 删除表或视图
|
* @param tfs
|
* @return
|
* @throws BaseException
|
*/
|
public static boolean deleteTable(FieldSetEntity tfs) throws BaseException{
|
|
if(tfs == null || tfs.getString("table_name") ==null || "".equals(tfs.getString("table_name")) ) {
|
throw new BaseException(
|
ErrorCode.DATA_CREATE_TABLE.getValue()
|
,ErrorCode.DATA_CREATE_TABLE.getText()
|
,Dao.class
|
,"deleteTable(FieldSetEntity tfs)"
|
);
|
}
|
int table_type=tfs.getInteger("table_type");
|
|
PreparedStatement ps = null;
|
Connection con = null;
|
try{
|
if(table_type==2) {
|
//删除视图表 DROP VIEW IF EXISTS stu_glass;
|
con =ConnectionManager.getConnection();
|
StringBuffer delviewsql=new StringBuffer();
|
delviewsql.append(" DROP VIEW IF EXISTS ").append("`").append(tfs.getString("table_name")).append("`");//执行的SQL删除视图语句
|
System.out.println(" DDL:"+delviewsql.toString());
|
ps = con.prepareStatement(delviewsql.toString());
|
ps.execute();
|
return true;
|
}
|
con =ConnectionManager.getConnection();
|
StringBuffer sql=new StringBuffer();
|
sql.append(" DROP TABLE `").append(tfs.getString("table_name")).append("`");
|
System.out.println(" DDL:"+sql.toString());
|
ps = con.prepareStatement(sql.toString());
|
ps.execute();
|
return true;
|
}catch(Exception e){
|
throw new BaseException(
|
ErrorCode.DATA_CREATE_TABLE.getValue()
|
,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage()
|
,Dao.class
|
,"deleteTable(FieldSetEntity tfs)"
|
,e
|
);
|
}finally{
|
//this.closeConnection(con,ps,null);
|
}
|
}
|
/**
|
* 删除表字段
|
* @param tfs
|
* @return
|
* @throws BaseException
|
*/
|
public static boolean deleteTableField(FieldSetEntity tfs) throws BaseException{
|
|
if(tfs == null || tfs.getString("table_name") ==null || "".equals(tfs.getString("table_name")) ) {
|
throw new BaseException(
|
ErrorCode.DATA_CREATE_TABLE.getValue()
|
,ErrorCode.DATA_CREATE_TABLE.getText()
|
,Dao.class
|
,"deleteTableField(FieldSetEntity tfs)"
|
);
|
}
|
PreparedStatement ps = null;
|
Connection con = null;
|
try{
|
con =ConnectionManager.getConnection();
|
StringBuffer sql=new StringBuffer();
|
sql.append(" ALTER TABLE `").append(tfs.getString("table_name")).append("`").append(" DROP `").append(tfs.getString("field_name")).append("`");
|
System.out.println(" DDL:"+sql.toString());
|
ps = con.prepareStatement(sql.toString());
|
ps.execute();
|
return true;
|
}catch(Exception e){
|
throw new BaseException(
|
ErrorCode.DATA_CREATE_TABLE.getValue()
|
,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage()
|
,Dao.class
|
,"deleteTableField(FieldSetEntity tfs)"
|
,e
|
);
|
}finally{
|
//this.closeConnection(con,ps,null);
|
}
|
}
|
/**
|
* 修改表名
|
* @param tfs
|
* @return
|
* @throws BaseException
|
*/
|
public static boolean updateTableName(FieldSetEntity tfs) throws BaseException{
|
|
if(tfs == null || tfs.getString("table_name") ==null || "".equals(tfs.getString("table_name")) ) {
|
throw new BaseException(
|
ErrorCode.DATA_CREATE_TABLE.getValue()
|
,ErrorCode.DATA_CREATE_TABLE.getText()
|
,Dao.class
|
,"deleteTableField(FieldSetEntity tfs)"
|
);
|
}
|
PreparedStatement ps = null;
|
Connection con = null;
|
try{
|
con =ConnectionManager.getConnection();
|
StringBuffer sql=new StringBuffer();
|
sql.append(" RENAME TABLE `").append(tfs.getString("table_name")).append("` TO `").append(tfs.getString("new_table_name")).append("`");
|
System.out.println(" DDL:"+sql.toString());
|
ps = con.prepareStatement(sql.toString());
|
ps.execute();
|
return true;
|
}catch(Exception e){
|
throw new BaseException(
|
ErrorCode.DATA_CREATE_TABLE.getValue()
|
,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage()
|
,Dao.class
|
,"deleteTableField(FieldSetEntity tfs)"
|
,e
|
);
|
}finally{
|
//this.closeConnection(con,ps,null);
|
}
|
}
|
/**
|
* 修改视图sql语句
|
* @param tfs
|
* @return
|
* @throws BaseException
|
*/
|
public static boolean updateViewSql(FieldSetEntity tfs) throws BaseException{
|
//CREATE OR REPLACE VIEW test_tableTwo_v AS SELECT * FROM test_newTable_two;
|
if(tfs == null || tfs.getString("table_name") ==null || "".equals(tfs.getString("table_name")) ) {
|
throw new BaseException(
|
ErrorCode.DATA_CREATE_TABLE.getValue()
|
,ErrorCode.DATA_CREATE_TABLE.getText()
|
,Dao.class
|
,"updateViewSql(FieldSetEntity tfs)"
|
);
|
}
|
PreparedStatement ps = null;
|
Connection con = null;
|
try{
|
con =ConnectionManager.getConnection();
|
StringBuffer sql=new StringBuffer();
|
sql.append(" CREATE OR REPLACE VIEW `").append(tfs.getString("table_name")).append("` AS ( ");
|
sql.append(tfs.getString("create_view_sql")).append(" ); ");//msv_execute_sql 执行的SQL语句
|
System.out.println(" DDL:"+sql.toString());
|
ps = con.prepareStatement(sql.toString());
|
ps.execute();
|
return true;
|
}catch(Exception e){
|
throw new BaseException(
|
ErrorCode.DATA_CREATE_TABLE.getValue()
|
,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage()
|
,Dao.class
|
,"updateViewSql(FieldSetEntity tfs)"
|
,e
|
);
|
}finally{
|
//this.closeConnection(con,ps,null);
|
}
|
}
|
/**
|
* 获取表中所有字段名称
|
* @param sql 表名
|
* @return
|
* @throws BaseException
|
* show full columns from tableName 通过表名获取数据库元数据
|
*/
|
public static DataTableEntity getColumnNames(String sql) throws BaseException {
|
DataTableEntity dataTableEntity = new DataTableEntity();
|
//与数据库的连接
|
PreparedStatement ps = null;
|
Connection con = null;
|
try {
|
con =ConnectionManager.getConnection();
|
//DatabaseMetaData data = con.getMetaData();
|
ps = con.prepareStatement(sql);
|
//结果集元数据
|
ResultSetMetaData rsmd = ps.getMetaData();
|
//ps.executeQuery("show full columns from " + sql);
|
//表列数
|
int size = rsmd.getColumnCount();
|
for (int i = 0; i < size; i++) {
|
FieldSetEntity fieldSetEntity = new FieldSetEntity();
|
//别名
|
fieldSetEntity.setValue("field_name", rsmd.getColumnLabel(i+1));
|
//字段名
|
//System.out.println("字段名"+rsmd.getColumnName(i + 1));
|
//字段类型 field_type
|
fieldSetEntity.setValue("field_type",rsmd.getColumnTypeName(i + 1));
|
//获取指定列的指定列大小。 field_length
|
fieldSetEntity.setValue("field_length",rsmd.getPrecision(i + 1));
|
//获取小数点右侧的指定列的位数。 field_unit
|
fieldSetEntity.setValue("field_unit",rsmd.getScale(i + 1));
|
dataTableEntity.addFieldSetEntity(fieldSetEntity);
|
}
|
|
|
} catch (Exception e) {
|
throw new BaseException(
|
ErrorCode.DATA_CREATE_TABLE.getValue()
|
,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage()
|
,Dao.class
|
,"updateViewSql(FieldSetEntity tfs)"
|
,e
|
);
|
} finally {
|
//this.closeConnection(con,ps,null);
|
}
|
return dataTableEntity;
|
}
|
/**
|
* 判断表是否存在
|
* @param tableName
|
* @return
|
* @throws BaseException
|
* @throws SQLException
|
*/
|
public static boolean isTableExistEntrust(String tableName) throws BaseException{
|
PreparedStatement ps = null;
|
Connection con = null;
|
con = ConnectionManager.getConnection();
|
try{
|
ResultSet rs = con.getMetaData().getTables(null, null, tableName, null);
|
if (rs.next()) {
|
return true;
|
}else {
|
return false;
|
}
|
} catch (Exception e) {
|
throw new BaseException(
|
ErrorCode.DATA_CREATE_TABLE.getValue()
|
,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage()
|
,Dao.class
|
,"updateViewSql(FieldSetEntity tfs)"
|
,e
|
);
|
} finally {
|
//this.closeConnection(con,ps,null);
|
}
|
}
|
}
|
