sf.database.dialect.db.H2Dialect 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.dialect.DBDialect;
import sf.database.dialect.DBProperty;
import sf.database.dialect.Keywords;
import sf.database.meta.ColumnMapping;
import sf.database.meta.TableMapping;
import sf.database.support.DBMS;
import sf.database.util.DBUtils;
import sf.jooq.JooqSupportDatabase;
import sf.querydsl.QueryDSLSupportDatabase;
import sf.tools.JavaTypeUtils;
import sf.tools.StringUtils;
import sf.tools.SystemUtils;

import javax.persistence.PersistenceException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * h2
 * @see PostgreSqlDialect
 */
public class H2Dialect extends DBDialect {
    @Override
    public String getName() {
        return DBMS.h2.name();
    }

    @Override
    public int getNumber() {
        return DBMS.h2.getNumber();
    }

    public H2Dialect() {
        this.keywords.addAll(Keywords.H2);

        features.add(Feature.ONE_COLUMN_IN_SINGLE_DDL);

        setProperty(DBProperty.ADD_COLUMN, "ADD COLUMN");
        setProperty(DBProperty.MODIFY_COLUMN, "ALTER COLUMN");
        setProperty(DBProperty.DROP_COLUMN, "DROP COLUMN");
        setProperty(DBProperty.CHECK_SQL, "select 1");
        setProperty(DBProperty.SEQUENCE_FETCH, "select nextval('%s')");
        setProperty(DBProperty.WRAP_FOR_KEYWORD, "\"\"");
        setProperty(DBProperty.GET_IDENTITY_FUNCTION, "SELECT currval('%tableName%_%columnName%_seq')");
        setProperty(DBProperty.NVL_FUNCTION, "ifnull(%1$s,%2$s)");
    }

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

    @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.VARCHAR: {
                type = "VARCHAR(" + length + ")";
                break;
            }
            case Types.TINYINT: {
                type = "SMALLINT";
                break;
            }
            case Types.SMALLINT: {
                type = "SMALLINT";
                break;
            }
            case Types.INTEGER: {
                type = "INT";
                break;
            }
            case Types.BIGINT: {
                type = "BIGINT";
                break;
            }
            //使用默认的
//            case Types.FLOAT: {
//                type = "real";
//                break;
//            }
//            case Types.DOUBLE:{
//                type = "double precision";
//                break;
//            }
            case Types.BOOLEAN: {
                type = "BOOLEAN";
                break;
            }
            case Types.DECIMAL: {
                // BigDecimal
                if (scale > 0 && precision > 0) {
                    type = "DECIMAL(" + precision + "," + scale + ")";
                } else {
                    //postgresql支持无限制
                    type = "DECIMAL";
                }
                break;
            }
            case Types.NUMERIC: {
                if (precision > 0) {
                    type = "NUMERIC(" + precision + "," + scale + ")";
                } else {
                    //postgresql支持无限制
                    type = "NUMERIC";
                }
                break;
            }
            case Types.DATE:
                type = "DATE";
                break;
            case Types.TIME:
                type = "TIME";
                break;
            case Types.TIMESTAMP:
                type = "TIMESTAMP";
                break;
            case Types.BLOB:
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                type = "BYTEA";
                break;
            case Types.CLOB:
            case Types.LONGVARCHAR:
            case Types.LONGNVARCHAR: {
                type = "TEXT";
                break;
            }
            default:
                type = super.evalFieldType(mf);
                break;
        }
        return type;
    }

    @Override
    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()));
            // 自增主键特殊形式关键字
            if (cm.isPk() && cm.getGv() != null && JavaTypeUtils.isNumberClass(cm.getClz())) {
                sb.append(" SERIAL");
            } else {
                // 普通字段
                sb.append(' ').append(evalFieldDefinition(cm));
            }
            delimiter = true;
        }
        // 创建主键
        List pks = en.getPkFields();
        if (!pks.isEmpty()) {
            sb.append(',').append(SystemUtils.lineSeparator);
            sb.append(String.format("CONSTRAINT pk_%s PRIMARY KEY (",
                    en.getTableName().replace('.', '_').replace('"', '_')));
            boolean f = false;
            for (ColumnMapping pk : pks) {
                sb.append(f ? "," : "").append(wrapKeyword(pk.getRawColumnName()));
                f = true;
            }
            sb.append(") ").append(SystemUtils.lineSeparator);
            sb.append(SystemUtils.lineSeparator).append(" ");
        }

        // 结束表字段设置
        sb.append(')');
        // 设置特殊引擎
//		if (en.hasMeta(META_ENGINE)) {
//			sb.append(" ENGINE=" + en.getMeta(META_ENGINE));
//		}
        // 默认采用 UTF-8 编码

        sb.append(" ");
        // 表名注释

        List sqls = new ArrayList<>();
        // 执行创建语句
        sqls.add(sb.toString());
        // 创建索引
        sqls.addAll(createIndexSql(en));
        //创建约束
        sqls.addAll(createUniqueSql(en));
        // 添加注释(表注释与字段注释)
        sqls.addAll(addCommentSql(en));

        execute(sqls, conn);

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


        return true;
    }

    @Override
    public String sqlTableDrop(String table) {
        return String.format("drop table if exists %s;", wrapKeyword(table));
    }

    @Override
    public String sqlTableRename(String oldName, String newName) {
        return String.format("alter table  %s rename to %s;", wrapKeyword(oldName), wrapKeyword(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) {
        String sql = String.format("alter table %s add column %s", wrapKeyword(table), column_definition);
        if (supportsColumnPosition() && column_position != null) {
            sql = sql + " " + column_position;
        }
        return sql;
    }

    @Override
    public String sqlColumnModify(String table, String column_definition, String column_position) {
        String sql = String.format("alter table %s alter column %s", wrapKeyword(table), column_definition);
        if (supportsColumnPosition() && column_position != null) {
            sql = sql + " " + column_position;
        }
        return sql;
    }

    @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) {
            return sql.append(" limit ").append(limit).append(" offset ").append(offset);
        } else {
            return sql.append(" limit ").append(limit);
        }
    }

    @Override
    public boolean supportsColumnPosition() {
        return true;
    }

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

    @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();
    }

    public List getIndexNames(TableMapping en, Connection conn) {
        List names = new ArrayList();
        String showIndexs = "SELECT * FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = ?";
        PreparedStatement ppstat = null;
        ResultSet rest = null;
        try {
            ppstat = conn.prepareStatement(showIndexs);
            rest = ppstat.executeQuery();
            while (rest.next()) {
                String index = rest.getString(5);
                names.add(index);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtils.closeQuietly(rest);
            DBUtils.closeQuietly(ppstat);
        }
        return names;
    }

    @Override
    public long getColumnAutoIncreamentValue(ColumnMapping mapping, Connection conn) {
        String tableName = mapping.getMeta().getTableName().toLowerCase();
        String sql = String.format("SELECT IDENTITY() as %s from %s", wrapKeyword(mapping.getLowerColumnName()), wrapKeyword(tableName));
        try {
            Statement st = conn.createStatement();
            ResultSet rs = null;
            try {
                rs = st.executeQuery(sql);
                rs.next();
                return rs.getLong(1);
            } finally {
                DBUtils.closeQuietly(rs);
                DBUtils.closeQuietly(st);
            }
        } catch (SQLException e) {
            throw new PersistenceException(e);
        }
    }

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

    @Override
    public String getJooqDialect() {
        return JooqSupportDatabase.H2;
    }
}