cn.tenfell.plugins.dbgenerate.utils.SqlUtils Maven / Gradle / Ivy
package cn.tenfell.plugins.dbgenerate.utils;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.util.StrUtil;
import cn.tenfell.plugins.dbgenerate.entity.ActionEnum;
import cn.tenfell.plugins.dbgenerate.entity.ColumnProp;
import cn.tenfell.plugins.dbgenerate.entity.TableColumnProp;
import lombok.experimental.UtilityClass;
import org.hibernate.dialect.Dialect;
import org.hibernate.mapping.Column;
import org.hibernate.mapping.PrimaryKey;
import org.hibernate.mapping.Table;
import java.util.*;
@UtilityClass
public class SqlUtils {
public static String catalog;
public static String schema;
public static String sqlCreateString(Dialect dialect, Table table, String defaultCatalog, String defaultSchema){
StringBuilder buf = new StringBuilder( table.hasPrimaryKey() ? dialect.getCreateTableString() : dialect.getCreateMultisetTableString() )
.append( ' ' )
.append( table.getQualifiedName( dialect, defaultCatalog, defaultSchema ) )
.append( " (" );
Iterator iter = table.getColumnIterator();
while ( iter.hasNext() ) {
Column col = (Column) iter.next();
buf.append( col.getQuotedName( dialect ) ).append( ' ' );
buf.append( col.getSqlType( dialect, null) );
String defaultValue = col.getDefaultValue();
if ( defaultValue != null ) {
buf.append( " default " ).append( defaultValue );
}
if ( col.isNullable() ) {
buf.append( dialect.getNullColumnString() );
}
else {
buf.append( " not null" );
}
String columnComment = col.getComment();
if ( columnComment != null ) {
buf.append( dialect.getColumnComment( columnComment ) );
}
if ( iter.hasNext() ) {
buf.append( ", " );
}
}
if ( table.hasPrimaryKey() ) {
buf.append( ", " )
.append( table.getPrimaryKey().sqlConstraintString( dialect ) );
}
buf.append( dialect.getUniqueDelegate().getTableCreationUniqueConstraintsFragment( table ) );
buf.append( ')' );
String comment = table.getComment();
if ( comment != null ) {
buf.append( dialect.getTableComment( comment ) );
}
return buf.append( dialect.getTableTypeString() ).toString();
}
public static List sqlAlterStrings(Dialect dialect, Table table, String defaultCatalog, String defaultSchema, Map action){
String tableName = table.getQualifiedName( dialect, defaultCatalog, defaultSchema );
StringBuilder root = new StringBuilder();
root.append(dialect.getAlterTableString(tableName)).append( ' ' );
Iterator columns = table.getColumnIterator();
List list = new ArrayList<>();
while(columns.hasNext()){
Column column = columns.next();
StringBuilder child = new StringBuilder().append(root);
ActionEnum actionEnum = action.get(column.getName());
Assert.notNull(actionEnum,"不存在的操作类型");
if(actionEnum == ActionEnum.DROP){
//删除
child.append("drop column ");
}else if(actionEnum == ActionEnum.MODIFY){
//修改
child.append("modify column ");
}else{
//新增
child.append(dialect.getAddColumnString()).append(" ");
}
child.append(column.getQuotedName(dialect)).append(" ");
if(actionEnum == ActionEnum.MODIFY || actionEnum == ActionEnum.ADD){
child.append( column.getSqlType(dialect , null ) );
String defaultValue = column.getDefaultValue();
if ( defaultValue != null ) {
child.append( " default " ).append( defaultValue );
}
if ( column.isNullable() ) {
child.append( dialect.getNullColumnString() );
}else {
child.append( " not null" );
}
String columnComment = column.getComment();
if ( columnComment != null ) {
child.append( dialect.getColumnComment( columnComment ) );
}
}
list.add(child.toString());
}
if(table.hasPrimaryKey() ) {
StringBuilder child = new StringBuilder().append(root);
child.append("drop primary key" );
list.add(child.toString());
child = new StringBuilder().append(root);
child.append("add " )
.append( table.getPrimaryKey().sqlConstraintString( dialect ) );
list.add(child.toString());
}
return list;
}
public static List getCommitSql(List tables,Integer type){
List sql = new ArrayList<>();
for(String table:tables){
TableColumnProp bean = DocUtils.allDataByBeans.get(table);
TableColumnProp db = DocUtils.allDataByDb.get(table);
if(bean.getTable() == null || StrUtil.isBlank(bean.getTable().getTableName()) || bean.getColumns() == null || bean.getColumns().size() == 0){
continue;
}
boolean isUpdate = false;
if(db.getTable() != null && StrUtil.isNotBlank(db.getTable().getTableName())){
isUpdate = true;
}
Set keys = new HashSet<>();
keys.addAll(bean.getColumns().keySet());
keys.addAll(db.getColumns().keySet());
Table t = new Table();
t.setComment(bean.getTable().getComment());
t.setName(bean.getTable().getTableName());
Map action = new HashMap<>();
List pris = new ArrayList<>();
for(String key:keys){
ColumnProp bcp = bean.getColumns().get(key);
ColumnProp dcp = db.getColumns().get(key);
if(bcp != null && dcp != null){
if(checkFilter(bcp,dcp)){
//属性完全一样的跳过
continue;
}
}else if(bcp == null && dcp == null){
//两属性均不存在的跳过(理论不存在此情况)
continue;
}
if(bcp == null && type == 1){
//bean不存在db存在,但是是增量同步跳过
continue;
}
ColumnProp tempProp = null;
if(bcp != null){
tempProp = bcp;
}else{
tempProp = dcp;
}
Column column = new Column();
column.setNullable(tempProp.isNullIs());
column.setName(tempProp.getColumn());
column.setComment(tempProp.getComment());
column.setLength(tempProp.getLength());
column.setPrecision(tempProp.getLength());
column.setScale(tempProp.getDecimal());
MyValue value = new MyValue(DocUtils.getTypeByHibernateName(tempProp.getType()));
column.setValue(value);
if(bcp != null){
if(bcp.isPrimaryIs()){
pris.add(column);
}
//新增,修改
if(dcp == null){
//新增
action.put(column.getName(),ActionEnum.ADD);
}else{
//修改
action.put(column.getName(),ActionEnum.MODIFY);
}
}else{
//删除
action.put(column.getName(),ActionEnum.DROP);
}
t.addColumn(column);
}
if(pris.size()>0){
PrimaryKey primaryKey = new PrimaryKey(t);
for(Column column:pris){
primaryKey.addColumn(column);
}
t.setPrimaryKey(primaryKey);
}
if(isUpdate){
//更新表
List alertSqls = sqlAlterStrings(DocUtils.sqlDialect,t,catalog,schema,action);
sql.addAll(alertSqls);
}else{
//创建表
String createSql = sqlCreateString(DocUtils.sqlDialect,t,catalog,schema);
sql.add(createSql);
}
}
return sql;
}
private static boolean checkFilter(ColumnProp bcp,ColumnProp dcp){
if(!StrUtil.equals(bcp.getColumn(),dcp.getColumn())){
return false;
}
if(!StrUtil.equals(bcp.getComment(),dcp.getComment())){
return false;
}
if(!StrUtil.equals(bcp.getType(),dcp.getType())){
return false;
}
if(bcp.getLength() != dcp.getLength()){
return false;
}
if(bcp.getDecimal() != dcp.getDecimal()){
return false;
}
if(bcp.isPrimaryIs() != dcp.isPrimaryIs()){
return false;
}
if(bcp.isNullIs() != dcp.isNullIs()){
return false;
}
return true;
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy