com.centit.support.database.ddl.GeneralDDLOperations Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of centit-database Show documentation
数据库操作通用方法和函数，从以前的util包中分离出来，并且整合了部分sys-module中的函数
There is a newer version: 5.3.2302
package com.centit.support.database.ddl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.ImmutablePair;
import org.apache.commons.lang3.tuple.Pair;

import com.centit.support.database.DatabaseAccess;
import com.centit.support.database.QueryUtils;
import com.centit.support.database.metadata.SimpleTableField;
import com.centit.support.database.metadata.TableField;
import com.centit.support.database.metadata.TableInfo;

public abstract class GeneralDDLOperations implements DDLOperations {

	
	protected Connection conn;
	
	public GeneralDDLOperations(){
		
	}
	
	public GeneralDDLOperations(Connection conn) {
		this.conn = conn;
	}
	
	public void setConnect(Connection conn) {
		this.conn = conn;
	}
	
	/**
	 * checkLabelName 判断一个字符串是否符合标识符，是否可以作为字段名或者表名
	 * @param seq CharSequence
	 * @return boolean
	 */
	public static final boolean checkLabelName(final CharSequence seq) {
        if (seq == null || seq.length() == 0) {
            return false;
        }
        final int strLen = seq.length();
        char c = seq.charAt(0);
        if( c != '_' &&
				( c < 'a' || c >'z') &&
				( c < 'A' || c > 'Z')
			 )
        	return false;
        	
        for (int i = 1; i < strLen; i++) {
        	 c = seq.charAt(i);
        	 if( c != '_' &&
     				( c < 'a' || c >'z') &&
     				( c < 'A' || c > 'Z')  &&
     				( c < '0' || c > '9')
     			 )
             	return false;
        }
        return true;
	}
	
	/**
	 * 返回格式检查结果
	 * @param tableInfo 表
	 * @return Pair
	 */
	public static final Pair checkTableWellDefined(final TableInfo tableInfo) {
		if(! checkLabelName(tableInfo.getTableName()))
			return new ImmutablePair(-1,"表名"+tableInfo.getTableName()+"格式不正确！");
		
		for(TableField field : tableInfo.getColumns()){			
			if(! checkLabelName(field.getColumnName()))
				return new ImmutablePair(-2,"字段名"+field.getColumnName()+"格式不正确！");
			if(StringUtils.isBlank(field.getColumnType()))
				return new ImmutablePair(-3,"字段"+field.getColumnName()+"没有指定类型！");
		}
		
		if( tableInfo.getPkColumns()==null || tableInfo.getPkColumns().size()==0)
			return new ImmutablePair(-4,"没有定义主键！");;			
		
        return new ImmutablePair(0,"ok！");
	}
	
	/**
	 * 创建序列 
 	 * 用表来模拟sequence 
	 * create table simulate_sequence (seqname varchar(100) not null primary key, 
	 * currvalue integer, increment integer);
	 *
	 * @param sequenceName 序列
	 * @return sql语句
	 */
	@Override
	public String makeCreateSequenceSql(final String sequenceName){
		return "INSERT INTO simulate_sequence (seqname, currvalue , increment)"
				+ " VALUES ("+ QueryUtils.buildStringForQuery(sequenceName)+", 0, 1)";
	}	
	
	@Override
	public String makeCreateTableSql(final TableInfo tableInfo){
		StringBuilder sbCreate = new StringBuilder("create table ");
		sbCreate.append(tableInfo.getTableName()).append(" (");
		appendColumnsSQL(tableInfo, sbCreate);
		appendPkSql(tableInfo, sbCreate);	
		sbCreate.append(")");	
		return sbCreate.toString();		
	}
	
	protected void appendPkSql(final TableInfo tableInfo, StringBuilder sbCreate) {		
		if (tableInfo.getPkColumns() !=null && tableInfo.getPkColumns().size()>0){
			sbCreate.append(" constraint "); 
			if (StringUtils.isBlank(tableInfo.getPkName())){
			   sbCreate.append("pk_"+tableInfo.getTableName()); 	
			}else{
				sbCreate.append(tableInfo.getPkName()); 
			}		
			sbCreate.append(" primary key ");
			appendPkColumnSql(tableInfo, sbCreate);			
		}
	}
	
	protected void appendPkColumnSql(final TableInfo tableInfo, StringBuilder sbCreate) {
		sbCreate.append("(");
		int i=0;
		for(String pkfield : tableInfo.getPkColumns()){
			if(i>0)
				sbCreate.append(", ");
			sbCreate.append(pkfield);
			i++;
		}
		sbCreate.append(")");
	}

	protected void appendColumnsSQL(final TableInfo tableInfo, StringBuilder sbCreate) {
		for(TableField field : tableInfo.getColumns()){
			appendColumnSQL(field,sbCreate);
			if(StringUtils.isNotBlank(field.getDefaultValue()))
				sbCreate.append(" default ").append(field.getDefaultValue());
			sbCreate.append(",");
		}
	}

	protected void appendColumnSQL(final TableField field,StringBuilder sbCreate) {
		sbCreate.append(field.getColumnName())
			.append(" ").append(field.getColumnType());
		//StringUtils.equalsIgnoreCase(str1, str2)
		if("varchar".equalsIgnoreCase(field.getColumnType())|| "varchar2".equalsIgnoreCase(field.getColumnType())){
			if(field.getMaxLength()>0)
				sbCreate.append("(").append(field.getMaxLength()).append(")");
			else
				sbCreate.append("(64)");
		}else if("number".equalsIgnoreCase(field.getColumnType())|| "decimal".equalsIgnoreCase(field.getColumnType())){
			if(field.getPrecision()>0)
				sbCreate.append("(").append(field.getPrecision());
			else
				sbCreate.append("(").append(24);
			if(field.getScale()>0)
				sbCreate.append(",").append(field.getScale());
			sbCreate.append(")");
		}			
		if(field.isMandatory())
			sbCreate.append(" not null");
	}

	@Override
	public String makeDropTableSql(final String tableCode){
		return "drop table " + tableCode;
	}

	@Override
	public String makeAddColumnSql(final String tableCode, final TableField column){
		StringBuilder sbsql = new StringBuilder("alter table ");
		sbsql.append(tableCode);
		sbsql.append(" add column ");
		appendColumnSQL(column,sbsql);
		return sbsql.toString();
	}

	@Override
	public String makeModifyColumnSql(final String tableCode, final TableField column){
		StringBuilder sbsql = new StringBuilder("alter table ");
		sbsql.append(tableCode);
		sbsql.append(" modify ");
		appendColumnSQL(column,sbsql);		
		return sbsql.toString();		
	}

	@Override
	public String makeDropColumnSql(final String tableCode, final String columnCode){
		return  "alter table " + tableCode +" drop COLUMN " + columnCode;
		
	}

	@Override
	public String makeRenameColumnSql(final String tableCode, final String columnCode, TableField column){
		StringBuilder sbsql = new StringBuilder("alter table ");
		sbsql.append(tableCode);
		sbsql.append(" rename COLUMN ");
		sbsql.append(columnCode);
		sbsql.append(" to ");
		sbsql.append(column.getColumnName());
		return  sbsql.toString();	
	}

	@Override
	public List makeReconfigurationColumnSqls(final String tableCode, final String columnCode, final TableField column){
		List sqls = new ArrayList();
		SimpleTableField tempColumn = new SimpleTableField();
		tempColumn.setColumnName(columnCode+"_1");
		sqls.add(makeRenameColumnSql(tableCode, columnCode, tempColumn));
		sqls.add(makeAddColumnSql(tableCode, column));
		sqls.add("update tableCode set "+ column.getColumnName() + " = " + columnCode);	
		sqls.add(makeDropColumnSql(tableCode, columnCode+"_1"));
		return sqls;
	}

	@Override
	public void createSequence(final String sequenceName)throws SQLException{
		DatabaseAccess.doExecuteSql(conn, makeCreateSequenceSql(sequenceName));
	}
	
	@Override
	public void createTable(TableInfo tableInfo) throws SQLException {
		DatabaseAccess.doExecuteSql(conn, makeCreateTableSql(tableInfo));
	}

	@Override
	public void dropTable(String tableCode) throws SQLException {
		DatabaseAccess.doExecuteSql(conn, makeDropTableSql(tableCode));
	}

	@Override
	public void addColumn(String tableCode, TableField column) throws SQLException {
		DatabaseAccess.doExecuteSql(conn, makeAddColumnSql(tableCode,column));
	}

	@Override
	public void modifyColumn(String tableCode, TableField column) throws SQLException {
		DatabaseAccess.doExecuteSql(conn, makeModifyColumnSql(tableCode,column));
	}

	@Override
	public void dropColumn(String tableCode, String columnCode) throws SQLException {
		DatabaseAccess.doExecuteSql(conn, makeDropColumnSql(tableCode,columnCode));
	}

	@Override
	public void renameColumn(String tableCode, String columnCode, TableField column) throws SQLException {
		DatabaseAccess.doExecuteSql(conn, makeRenameColumnSql(tableCode,columnCode,column));
	}

	@Override
	public void reconfigurationColumn(String tableCode, String columnCode, TableField column) throws SQLException {
		List sqList = makeReconfigurationColumnSqls(tableCode, columnCode, column);
		if(sqList==null)
			return;
		for(String sql:sqList){
			DatabaseAccess.doExecuteSql(conn, sql);
		}
	}

}