sf.database.dialect.db.SqlServerDialect Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of sorm Show documentation
java jpa tool for spring
The newest version!
package sf.database.dialect.db;


import sf.core.DBField;
import sf.database.dbinfo.Feature;
import sf.database.dbinfo.SequenceInfo;
import sf.database.dialect.DBDialect;
import sf.database.dialect.DBProperty;
import sf.database.jdbc.sql.Crud;
import sf.database.meta.ColumnMapping;
import sf.database.meta.TableMapping;
import sf.database.support.DBMS;
import sf.querydsl.QueryDSLSupportDatabase;
import sf.tools.JavaTypeUtils;
import sf.tools.StringUtils;
import sf.tools.SystemUtils;

import java.sql.Connection;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * sqlserver
 */
public class SqlServerDialect extends DBDialect {
    @Override
    public String getName() {
        return DBMS.sqlserver.name();
    }

    @Override
    public int getNumber() {
        return DBMS.sqlserver.getNumber();
    }
    // private static String COMMENT_TABLE =
    // "EXECUTE sp_updateextendedproperty N'Description', '$tableComment', N'user', N'dbo', N'table', N'$table', NULL, NULL";

    private static String COMMENT_COLUMN = "EXECUTE sp_addextendedproperty N'MS_Description', '%3$s', N'user', N'dbo', N'table', N'%1$s', N'column', N'%2$s'";


    public SqlServerDialect() {
        features.add(Feature.COLUMN_DEF_ALLOW_NULL);
        features.add(Feature.CONCAT_IS_ADD);
        features.add(Feature.NOT_SUPPORT_KEYWORD_DEFAULT);
        features.add(Feature.BATCH_GENERATED_KEY_BY_FUNCTION);
        features.add(Feature.SUPPORT_IDENTITY);
        features.add(Feature.SUPPORT_SQLSERVER_COMMENT); //sqlserver 的注释语法比较独特
        // features.add(Feature.NO_BIND_FOR_INSERT);
        // features.add(Feature.NO_BIND_FOR_SELECT);

        setProperty(DBProperty.ADD_COLUMN, "ADD");
        setProperty(DBProperty.MODIFY_COLUMN, "ALTER COLUMN");
        setProperty(DBProperty.DROP_COLUMN, "DROP COLUMN");
        setProperty(DBProperty.ADD_CONSTRAINT, "ADD CONSTRAINT");
        setProperty(DBProperty.CHECK_SQL, "select 1");
        setProperty(DBProperty.GET_IDENTITY_FUNCTION, "SELECT @@IDENTITY");
        setProperty(DBProperty.WRAP_FOR_KEYWORD, "[]");
        setProperty(DBProperty.DROP_INDEX_TABLE_PATTERN, "%2$s.%1$s");
        setProperty(DBProperty.NVL_FUNCTION, "isnull(%1$s,%2$s)");
    }

    @Override
    protected String unwrapper(String name) {
        return StringUtils.replaceEach(name, new String[]{"[", "]"}, new String[]{"", ""});
    }

    @Override
    public String getDefaultSchema() {
        return "dbo";
    }

    @Override
    public String getSqlStatmentSeparator() {
        return "go" + SystemUtils.lineSeparator;
    }

    @Override
    public String sqlTableDrop(String table) {
        String sql = "if exists (select * from dbo.sysobjects where id=object_id(N'%s') and objectproperty(id,N'IsUserTable')=1) drop table %s;";
        table = wrapKeyword(table);
        return String.format(sql, table, table);
    }

    @Override
    public String sqlTableRename(String oldName, String newName) {
        oldName = wrapKeyword(oldName);
        newName = wrapKeyword(newName);
        return "alter table  " + oldName + " rename to " + newName + ";";
    }

    @Override
    public String sqlColumnRename(String table, String oldColumnName, String newColumnName) {
        return null;
    }

    @Override
    public String sqlColumnAdd(String table, String column_definition, String column_position) {
        return String.format("alter table %s add %s;", wrapKeyword(table), column_definition);
    }

    @Override
    public String sqlColumnModify(String table, String column_definition, String column_position) {
        return String.format("alter table %s alter column %s;", wrapKeyword(table), column_definition);
    }

    @Override
    public String sqlColumnDrop(String table, String column) {
        return String.format("alter table %s drop column %s;", wrapKeyword(table), wrapKeyword(column));
    }

    @Override
    public StringBuilder sqlPageList(StringBuilder sql, long offset, int limit) {
        if (offset == 0) {
            sql.insert(0, "select top " + limit + " * from (").append(") as temp");
        } else {
            throw new UnsupportedOperationException("not support,please use you own count sql and page sql!");
        }
        return sql;
    }

    @Override
    public String getHibernateDialect() {
        return "org.hibernate.dialect.SQLServerDialect";
    }

    public boolean createEntity(Connection conn, TableMapping en) {
        StringBuilder sb = new StringBuilder("CREATE TABLE " + wrapKeyword(en.getTableName()) + "(");
        // 创建字段
        boolean delimiter = false;
        for (Map.Entry entry : en.getSchemaMap().entrySet()) {
            ColumnMapping cm = entry.getValue();
            if (cm.getJpaTransient() != null) {
                continue;
            }
            sb.append(delimiter ? "," : "").append(SystemUtils.lineSeparator);
            sb.append(wrapKeyword(cm.getRawColumnName())).append(' ').append(evalFieldDefinition(cm));
            // 自增主键特殊形式关键字
            if (cm.isPk() && cm.getGv() != null) {
                if (JavaTypeUtils.isNumberClass(cm.getClz())) {
                    sb.append(" IDENTITY(1,1) ");
                }
            }
            delimiter = true;
        }
        // 创建主键
        List pks = en.getPkFields();
        if (!pks.isEmpty()) {
            sb.append(",").append(SystemUtils.lineSeparator);
            sb.append("PRIMARY KEY (");
            boolean f = false;
            for (ColumnMapping pk : pks) {
                sb.append(f ? "," : "").append(wrapKeyword(pk.getRawColumnName()));
                f = true;
            }
            sb.append(") ").append(SystemUtils.lineSeparator);
        }
        // 结束表字段设置
        sb.append(')');

        List sqls = new ArrayList<>();
        sqls.add(sb.toString());

        // // 处理非主键unique
        // for (MappingField mf : en.getMappingFields()) {
        // if(!mf.isPk())
        // continue;
        // String sql =
        // gSQL("alter table ${T} add constraint unique_key_${F} unique (${F});",
        // en.getTableName(),mf.getColumnName());
        // sqls.add(Sqls.create(sql));
        // }
        // 处理AutoIncreasement

        // 创建索引
        sqls.addAll(createIndexSql(en));

        //创建约束
        sqls.addAll(createUniqueSql(en));

        // 添加注释(表注释与字段注释)
        sqls.addAll(addCommentSql(en));
        // 执行创建语句
        execute(sqls, conn);

        // 创建关联表
        createRelation(conn, en);

        return true;
    }

    @Override
    public String evalFieldType(ColumnMapping mf) {
        String type = "";
        int length = getColumnLength(mf);
        int scale = getColumnScale(mf);
        int precision = getColumnPrecision(mf);
        switch (mf.getSqlType()) {
            case Types.CHAR: {
                type = "CHAR(" + length + ")";
                break;
            }
            case Types.VARCHAR: {
                type = "VARCHAR(" + length + ")";
                break;
            }
            case Types.TINYINT: {
                type = "TINYINT";
                break;
            }
            case Types.SMALLINT: {
                type = "SMALLINT";
                break;
            }
            case Types.INTEGER: {
                // 用户自定义了宽度
                if (length > 0 && length <= 38) {
                    type = "NUMERIC(" + length + ")";
                } else {
                    // 用数据库的默认宽度
                    type = "INT";
                }
                break;
            }
            case Types.BIGINT: {
                type = "BIGINT";
                break;
            }
            case Types.FLOAT: {
                // 用户自定义了精度
                if (precision > 0 && scale > 0) {
                    type = "DECIMAL(" + precision + "," + scale + ")";
                } else {
                    type = "FLOAT";
                }
                break;
            }
            case Types.REAL:
                type = "REAL";
                break;
            case Types.DOUBLE:
                // 用默认精度
                type = "DECIMAL(15,10)";
                break;
            case Types.BIT:
            case Types.BOOLEAN: {
                type = "BIT";
                break;
            }
            case Types.DECIMAL: {
                // BigDecimal
                if (scale > 0 && precision > 0) {
                    type = "DECIMAL(" + precision + "," + scale + ")";
                } else {
                    throw new UnsupportedOperationException("Unsupport type!");
                }
                break;
            }
            case Types.NUMERIC: {
                if (precision > 0) {
                    type = "NUMERIC(" + precision + "," + scale + ")";
                } else {
                    type = "NUMERIC(38,0)";
                }
                break;
            }
            case Types.DATE:
            case Types.TIME:
            case Types.TIMESTAMP:
                type = "DATETIME";
                break;
            case Types.TIME_WITH_TIMEZONE:
            case Types.TIMESTAMP_WITH_TIMEZONE:
                type = "DATETIMEOFFSET";//该特性必须是2008及以上
                break;
            case Types.BLOB:
            case Types.BINARY:
                type = "BINARY";
                break;
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                type = "VARBINARY(max)";
                break;
            case Types.CLOB:
            case Types.LONGVARCHAR:
            case Types.LONGNVARCHAR: {
                type = "TEXT";
                break;
            }
            default:
                type = super.evalFieldType(mf);
                break;
        }
        return type;
    }

    @Override
    public boolean dropEntity(Connection conn, TableMapping en) {
        return super.dropEntity(conn, en);
    }

    @Override
    public List addCommentSql(TableMapping en) {
        // TODO 表注释 SQLServer2005中貌似不行
        // 字段注释
        List sqls = new ArrayList();
        if (!en.getSchemaMap().isEmpty()) {

            for (ColumnMapping mf : en.getSchemas()) {
                if (StringUtils.isNotBlank(mf.getComment())) {
                    //table,column,columnComment
                    //表名和字段名不能使用[]包括.
                    String s = addColumnCommnetSql(unwrapper(en.getTableName()), unwrapper(mf.getRawColumnName()), mf.getComment());
                    sqls.add(s);
                }
            }
        }
        return sqls;
    }

    @Override
    public String addColumnCommnetSql(String tableName, String columnName, String comment) {
        return String.format(COMMENT_COLUMN, tableName, columnName, comment);
    }

    @Override
    public String uniqueSql(String tableName, String name, String[] columnNames) {
        StringBuilder sb = new StringBuilder();
        sb.append("alter table ").append(wrapKeyword(tableName)).append(" add constraint ").append(name).append(" unique(");
        for (int i = 0; i < columnNames.length; i++) {
            String column = columnNames[i];
            sb.append(i > 0 ? "," : "").append(wrapKeyword(column));
        }
        sb.append(")");
        return sb.toString();
    }

    @Override
    public List getSequenceInfo(Connection conn, String schema, String seqName) {
        schema = StringUtils.isBlank(schema) ? "%" : schema;
        seqName = StringUtils.isBlank(seqName) ? "%" : seqName;
        String sql = "SELECT CONVERT(varchar,seq.name),CONVERT(int,seq.cache_size),"
                + "CONVERT(bigint,seq.current_value),"
                + "CONVERT(bigint,seq.minimum_value),"
                + "CONVERT(bigint,seq.start_value),"
                + "CONVERT(int,seq.increment),"
                + "CONVERT(varchar,m.name) as schema_name from sys.sequences seq, sys.schemas m WHERE seq.SCHEMA_ID=m.SCHEMA_ID "
                + "AND seq.name LIKE ? AND m.name LIKE ?";
        List result = new ArrayList<>();
        Crud.getInstance().getCrudSql().selectResultSet(conn, (rs) -> {
            while (rs.next()) {
                SequenceInfo seq = new SequenceInfo();
                seq.setCatalog(null);
                seq.setSchema(rs.getString(1));
                seq.setName(rs.getString(2));
                seq.setMinValue(rs.getLong(3));
                //	seq.setMaxValue(rs.getLong(4));
                seq.setStep(rs.getInt(5));
                seq.setCacheSize(rs.getInt(6));
                seq.setCurrentValue(rs.getLong(7));
                result.add(seq);
            }
            return null;
        }, sql, schema, seqName);
        return result;
    }

    @Override
    public String getQueryDslDialect() {
        return QueryDSLSupportDatabase.SQLServer;
    }

    @Override
    public String getJooqDialect() {
        return null;
    }
}