package com.product.org.admin.util; import com.product.core.spring.context.SpringMVCContextHolder; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import com.product.core.config.ErrorCode; import com.product.org.admin.config.CmnConst; import com.product.core.connection.ConnectionManager; import com.product.core.dao.support.Dao; import com.product.core.entity.DataTableEntity; import com.product.core.entity.FieldMetaEntity; import com.product.core.entity.FieldSetEntity; import com.product.core.exception.BaseException; /** * Copyright © LX-BASE * @Title: DatabaseUtil * @Project: LX-BASE-SERVER * @Date: 2020-05-29 11:42:06 * @Author: Xin.Luo * @Description: 数据库工具类 */ public class DatabaseUtil { private DatabaseUtil(){} /** * 创建表 * @param tfs * @return * @throws SQLException */ public static boolean createTable(FieldSetEntity tfs) throws BaseException{ if(tfs == null || tfs.getString(CmnConst.TABLE_NAME) ==null || "".equals(tfs.getString(CmnConst.TABLE_NAME)) ) { throw new BaseException( ErrorCode.DATA_CREATE_TABLE.getValue() ,ErrorCode.DATA_CREATE_TABLE.getText() ,Dao.class ,"createTable(FieldSetEntity tfs)" ); } PreparedStatement ps = null; Connection con = null; try{ int table_type=tfs.getInteger("table_type"); con =ConnectionManager.getConnection(); if(table_type==2) { //创建视图表 StringBuilder viewsql=new StringBuilder(); viewsql.append(" create view `").append(tfs.getString(CmnConst.TABLE_NAME)).append("` as "); viewsql.append(tfs.getString("view_sql"));//msv_execute_sql 执行的SQL语句 SpringMVCContextHolder.getSystemLogger().info(" DDL:"+viewsql.toString()); ps = con.prepareStatement(viewsql.toString()); ps.execute(); return true; } StringBuilder sql=new StringBuilder(); sql.append(" CREATE TABLE `").append(tfs.getString(CmnConst.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("' "); SpringMVCContextHolder.getSystemLogger().info(" 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{ closePreparedStatement(ps); } } /** * 新增表字段 * @param tfs * @return * @throws SQLException */ public static boolean createTableField(FieldSetEntity tfs) throws BaseException{ if(tfs == null || tfs.getString(CmnConst.TABLE_NAME) ==null || "".equals(tfs.getString(CmnConst.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(); StringBuilder sql=new StringBuilder(); sql.append(" ALTER TABLE `").append(tfs.getString(CmnConst.TABLE_NAME)).append("` ADD `"); sql.append(tfs.getString(CmnConst.FIELD_NAME)).append("` "+tfs.getString(CmnConst.FIELD_TYPE)); if(tfs.getString(CmnConst.FIELD_TYPE).indexOf("text") == -1){//非大文本 sql.append("(");// 类型 sql.append(tfs.getString(CmnConst.FIELD_LENGTH));// 字段长度 String unit = tfs.getString(CmnConst.FIELD_UNIT);// 小数位数 unit = (unit.equals("0")) ? "" : ("," + unit); sql.append(unit+") "); } if (tfs.getString(CmnConst.IS_REQUIRED).equals("1")) {// 0不必填，1必填 sql.append(" NOT NULL "); }else { sql.append(" DEFAULT NULL "); } if (tfs.getString(CmnConst.IS_UNIQUE).equals("1")) {// 0不唯一，1唯一 sql.append("unique "); } if(tfs.getString(CmnConst.FIELD_DESCRIPTION) != null && tfs.getString(CmnConst.FIELD_DESCRIPTION).length()>0){//字段描述 sql.append(" COMMENT"+"'"+ tfs.getString(CmnConst.FIELD_DESCRIPTION) +"' "); } SpringMVCContextHolder.getSystemLogger().info(" 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{ closePreparedStatement(ps); } } /** * 修改字段 * @param tfs 原字段 * @param newtfs 修改后的字段 * @return * @throws BaseException */ public static boolean updateTableField(FieldSetEntity tfs,FieldSetEntity newtfs) throws BaseException{ if(newtfs == null || newtfs.getString(CmnConst.TABLE_NAME) ==null || "".equals(newtfs.getString(CmnConst.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(); con.setAutoCommit(false); StringBuilder sql=new StringBuilder(); sql.append("ALTER TABLE `").append(newtfs.getString(CmnConst.TABLE_NAME)).append("` "); String fieldName = tfs.getString(CmnConst.FIELD_NAME);//原表名 String newFieldName = newtfs.getString(CmnConst.FIELD_NAME);//新表名 String isUnique = tfs.getString(CmnConst.IS_UNIQUE);//原唯一约束 isUnique = isUnique==null?"0":isUnique; String newIsUnique = newtfs.getString(CmnConst.IS_UNIQUE);//新唯一约束 newIsUnique = newIsUnique==null?"0":newIsUnique; String isRequired = tfs.getString(CmnConst.IS_REQUIRED); isRequired = isRequired==null?"0":isRequired; String newTfsisRequired = newtfs.getString(CmnConst.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 } // "DEFAULT NULL COMMENT '为空'"; sql.append(" ").append(newtfs.getString(CmnConst.FIELD_TYPE)); if(newtfs.getString(CmnConst.FIELD_TYPE).indexOf("text") == -1){//非大文本 sql.append("("+newtfs.getString(CmnConst.FIELD_LENGTH)); String unit = newtfs.getString(CmnConst.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(CmnConst.FIELD_DESCRIPTION) != null && newtfs.getString(CmnConst.FIELD_DESCRIPTION).length()>0){//字段描述 sql.append(" COMMENT "+"'"+ newtfs.getString(CmnConst.FIELD_DESCRIPTION) +"' "); } //ALTER TABLE test_newtable ADD unique(newField1) SpringMVCContextHolder.getSystemLogger().info(" DDL:"+sql.toString()); ps = con.prepareStatement(sql.toString()); ps.execute(); if(isUnique.equals(newIsUnique)){ return true; }else { StringBuilder unique=new StringBuilder(); if (newIsUnique.equals("1")) {//1唯一 新增唯一约束 unique.append(" ALTER TABLE ").append(tfs.getString(CmnConst.TABLE_NAME)).append(" ADD unique(").append(newFieldName+")"); }else{//不唯一 去掉唯一约束 unique.append(" ALTER TABLE ").append(tfs.getString(CmnConst.TABLE_NAME)).append(" DROP INDEX ").append(newFieldName); } SpringMVCContextHolder.getSystemLogger().info(" DDL:"+unique.toString()); ps = con.prepareStatement(unique.toString()); ps.execute(); } return true; }catch(Exception e){ rollback(con); throw new BaseException( ErrorCode.DATA_CREATE_TABLE.getValue() ,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage() ,Dao.class ,"updateTableField(FieldSetEntity tfs,FieldSetEntity newtfs)" ,e ); }finally{ closePreparedStatement(ps); } } /** * 删除表或视图 * @param tfs * @return * @throws BaseException */ public static boolean deleteTable(FieldSetEntity tfs) throws BaseException{ if(tfs == null || tfs.getString(CmnConst.TABLE_NAME) ==null || "".equals(tfs.getString(CmnConst.TABLE_NAME)) ) { throw new BaseException( ErrorCode.DATA_CREATE_TABLE.getValue() ,ErrorCode.DATA_CREATE_TABLE.getText() ,Dao.class ,"deleteTable(FieldSetEntity tfs)" ); } PreparedStatement ps = null; Connection con = null; try{ int table_type=tfs.getInteger("table_type"); if(table_type==2) { //删除视图表 "DROP VIEW IF EXISTS stu_glass;" con =ConnectionManager.getConnection(); con.setAutoCommit(false); StringBuilder delviewsql=new StringBuilder(); delviewsql.append(" DROP VIEW IF EXISTS ").append("`").append(tfs.getString(CmnConst.TABLE_NAME)).append("`");//执行的SQL删除视图语句 SpringMVCContextHolder.getSystemLogger().info(" DDL:"+delviewsql.toString()); ps = con.prepareStatement(delviewsql.toString()); ps.execute(); return true; } con =ConnectionManager.getConnection(); StringBuilder sql=new StringBuilder(); sql.append(" DROP TABLE `").append(tfs.getString(CmnConst.TABLE_NAME)).append("`"); SpringMVCContextHolder.getSystemLogger().info(" DDL:"+sql.toString()); ps = con.prepareStatement(sql.toString()); ps.execute(); return true; }catch(Exception e){ rollback(con); throw new BaseException( ErrorCode.DATA_CREATE_TABLE.getValue() ,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage() ,Dao.class ,"deleteTable(FieldSetEntity tfs)" ,e ); }finally{ closePreparedStatement(ps); } } /** * 删除表字段 * @param tfs * @return * @throws BaseException */ public static boolean deleteTableField(FieldSetEntity tfs) throws BaseException{ if(tfs == null || tfs.getString(CmnConst.TABLE_NAME) ==null || "".equals(tfs.getString(CmnConst.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(); StringBuilder sql=new StringBuilder(); sql.append(" ALTER TABLE `").append(tfs.getString(CmnConst.TABLE_NAME)).append("`").append(" DROP `").append(tfs.getString(CmnConst.FIELD_NAME)).append("`"); SpringMVCContextHolder.getSystemLogger().info(" 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{ closePreparedStatement(ps); } } /** * 修改表名 * @param tfs * @return * @throws BaseException */ public static boolean updateTableName(FieldSetEntity tfs) throws BaseException{ if(tfs == null || tfs.getString(CmnConst.TABLE_NAME) ==null || "".equals(tfs.getString(CmnConst.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(); StringBuilder sql=new StringBuilder(); sql.append(" RENAME TABLE `").append(tfs.getString(CmnConst.TABLE_NAME)).append("` TO `").append(tfs.getString("new_table_name")).append("`"); SpringMVCContextHolder.getSystemLogger().info(" 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{ closePreparedStatement(ps); } } /** * 修改视图sql语句 * @param tfs * @return * @throws BaseException */ public static boolean updateViewSql(FieldSetEntity tfs) throws BaseException{ if(tfs == null || tfs.getString(CmnConst.TABLE_NAME) ==null || "".equals(tfs.getString(CmnConst.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(); StringBuilder sql=new StringBuilder(); sql.append(" CREATE OR REPLACE VIEW `").append(tfs.getString(CmnConst.TABLE_NAME)).append("` AS "); sql.append(tfs.getString("view_sql"));//msv_execute_sql 执行的SQL语句 SpringMVCContextHolder.getSystemLogger().info(" 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{ closePreparedStatement(ps); } } /** * 获取表中所有字段名称 * @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(); ps = con.prepareStatement(sql); //结果集元数据 ResultSetMetaData rsmd = ps.getMetaData(); //表列数 int size = rsmd.getColumnCount(); for (int i = 1; i <= size; i++) { FieldSetEntity fieldSetEntity = new FieldSetEntity(); FieldMetaEntity f = new FieldMetaEntity(); f.setTableName(new Object[]{"product_sys_datamodel_field"}); fieldSetEntity.setMeta(f); fieldSetEntity.setValue("related_table", rsmd.getTableName(i));//获取表名 //别名 fieldSetEntity.setValue(CmnConst.FIELD_NAME, rsmd.getColumnLabel(i)); //字段类型 field_type fieldSetEntity.setValue(CmnConst.FIELD_TYPE,rsmd.getColumnTypeName(i)); //获取指定列的指定列大小。 field_length fieldSetEntity.setValue(CmnConst.FIELD_LENGTH,rsmd.getPrecision(i)); //获取小数点右侧的指定列的位数。 field_unit fieldSetEntity.setValue(CmnConst.FIELD_UNIT,rsmd.getScale(i)); dataTableEntity.addFieldSetEntity(fieldSetEntity); } } catch (Exception e) { throw new BaseException( ErrorCode.DATA_INITIAL_SQL.getValue() ,ErrorCode.DATA_INITIAL_SQL.getText()+e.getMessage() ,Dao.class ,"updateViewSql(FieldSetEntity tfs)" ,e ); } finally { closePreparedStatement(ps); } return dataTableEntity; } /** * 判断表是否存在 * @param tableName * @return * @throws BaseException * @throws SQLException */ public static boolean isTableExistEntrust(String tableName) throws BaseException{ Connection con = null; ResultSet rs = null; try{ con = ConnectionManager.getConnection(); 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 { closeResultSet(rs); } } /** * 创建存储过程 * @param procedureSql * @param procedureName * @throws BaseException */ public static boolean runProcedure(String procedureSql,String procedureName) throws BaseException { //与数据库的连接 PreparedStatement ps = null; PreparedStatement ps2 = null; Connection con = null; String queryDrop = " DROP PROCEDURE IF EXISTS "+procedureName; try { con =ConnectionManager.getConnection(); con.setAutoCommit(false); ps = con.prepareStatement(queryDrop); ps.execute(); } catch (Exception e) { rollback(con); throw new BaseException( ErrorCode.DATA_CREATE_TABLE.getValue() ,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage() ,Dao.class ,"runProcedure(String procedureSql,String ProcedureName)" ,e ); } try { con =ConnectionManager.getConnection(); ps2 = con.prepareStatement(procedureSql); ps2.executeUpdate(); } catch (Exception e) { throw new BaseException( ErrorCode.DATA_INITIAL_SQL.getValue() ,ErrorCode.DATA_INITIAL_SQL.getText()+e.getMessage() ,Dao.class ,"runProcedure(String procedureSql,String ProcedureName)" ,e ); } finally { closePreparedStatement(ps); } return true; } /** * 删除存储过程 * @param procedure_name * @return * @throws BaseException */ public static boolean deleteProcedure(String procedure_name) throws BaseException{ //与数据库的连接 PreparedStatement ps = null; Connection con = null; String queryDrop = " DROP PROCEDURE IF EXISTS "+procedure_name; try { con =ConnectionManager.getConnection(); ps = con.prepareStatement(queryDrop); ps.execute(); } catch (Exception e) { throw new BaseException( ErrorCode.DATA_CREATE_TABLE.getValue() ,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage() ,Dao.class ,"runProcedure(String procedureSql,String ProcedureName)" ,e ); } finally { closePreparedStatement(ps); } return true; } /** * 关闭PreparedStatement * @param ps * @throws BaseException */ public static void closePreparedStatement(PreparedStatement ps)throws BaseException{ try { if(ps != null){ ps.close(); } } catch (SQLException e) { e.printStackTrace(); throw new BaseException( "closePreparedStatement(PreparedStatement ps)" ,e.getMessage() ); } } /** * 关闭ResultSet * @param rs * @throws BaseException */ public static void closeResultSet(ResultSet rs)throws BaseException{ try { if(rs != null){ rs.close(); } } catch (SQLException e) { e.printStackTrace(); throw new BaseException( "closeResultSet(ResultSet ps)" ,e.getMessage() ); } } /** * 回退 * @param con * @throws BaseException */ public static void rollback(Connection con) throws BaseException{ try { if(con != null){ con.rollback(); } }catch (SQLException e) { e.printStackTrace(); throw new BaseException( "rollback(Connection con)" ,e.getMessage() ); } } }