org.hibernate.dialect.MySQLDialect.java Source code

Java tutorial

Introduction

Here is the source code for org.hibernate.dialect.MySQLDialect.java

Source

/*
 * Hibernate, Relational Persistence for Idiomatic Java
 *
 * License: GNU Lesser General Public License (LGPL), version 2.1 or later.
 * See the lgpl.txt file in the root directory or <http://www.gnu.org/licenses/lgpl-2.1.html>.
 */
package org.hibernate.dialect;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.hibernate.JDBCException;
import org.hibernate.NullPrecedence;
import org.hibernate.PessimisticLockException;
import org.hibernate.boot.TempTableDdlTransactionHandling;
import org.hibernate.cfg.Environment;
import org.hibernate.dialect.function.NoArgSQLFunction;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.dialect.identity.IdentityColumnSupport;
import org.hibernate.dialect.identity.MySQLIdentityColumnSupport;
import org.hibernate.dialect.pagination.AbstractLimitHandler;
import org.hibernate.dialect.pagination.LimitHandler;
import org.hibernate.dialect.pagination.LimitHelper;
import org.hibernate.dialect.unique.MySQLUniqueDelegate;
import org.hibernate.dialect.unique.UniqueDelegate;
import org.hibernate.engine.spi.RowSelection;
import org.hibernate.exception.LockAcquisitionException;
import org.hibernate.exception.LockTimeoutException;
import org.hibernate.exception.spi.SQLExceptionConversionDelegate;
import org.hibernate.hql.spi.id.IdTableSupportStandardImpl;
import org.hibernate.hql.spi.id.MultiTableBulkIdStrategy;
import org.hibernate.hql.spi.id.local.AfterUseAction;
import org.hibernate.hql.spi.id.local.LocalTemporaryTableBulkIdStrategy;
import org.hibernate.internal.util.JdbcExceptionHelper;
import org.hibernate.mapping.Column;
import org.hibernate.type.StandardBasicTypes;

/**
 * An SQL dialect for MySQL (prior to 5.x).
 *
 * @author Gavin King
 */
@SuppressWarnings("deprecation")
public class MySQLDialect extends Dialect {

    private static final Pattern ESCAPE_PATTERN = Pattern.compile("\\", Pattern.LITERAL);
    public static final String ESCAPE_PATTERN_REPLACEMENT = Matcher.quoteReplacement("\\\\");
    private final UniqueDelegate uniqueDelegate;
    private final MySQLStorageEngine storageEngine;

    private static final LimitHandler LIMIT_HANDLER = new AbstractLimitHandler() {
        @Override
        public String processSql(String sql, RowSelection selection) {
            final boolean hasOffset = LimitHelper.hasFirstRow(selection);
            return sql + (hasOffset ? " limit ?, ?" : " limit ?");
        }

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

    /**
     * Constructs a MySQLDialect
     */
    public MySQLDialect() {
        super();

        String storageEngine = Environment.getProperties().getProperty(Environment.STORAGE_ENGINE);
        if (storageEngine == null) {
            this.storageEngine = getDefaultMySQLStorageEngine();
        } else if ("innodb".equals(storageEngine.toLowerCase())) {
            this.storageEngine = InnoDBStorageEngine.INSTANCE;
        } else if ("myisam".equals(storageEngine.toLowerCase())) {
            this.storageEngine = MyISAMStorageEngine.INSTANCE;
        } else {
            throw new UnsupportedOperationException("The storage engine '" + storageEngine + "' is not supported!");
        }

        registerColumnType(Types.BIT, "bit");
        registerColumnType(Types.BIGINT, "bigint");
        registerColumnType(Types.SMALLINT, "smallint");
        registerColumnType(Types.TINYINT, "tinyint");
        registerColumnType(Types.INTEGER, "integer");
        registerColumnType(Types.CHAR, "char(1)");
        registerColumnType(Types.FLOAT, "float");
        registerColumnType(Types.DOUBLE, "double precision");
        registerColumnType(Types.BOOLEAN, "bit"); // HHH-6935
        registerColumnType(Types.DATE, "date");
        registerColumnType(Types.TIME, "time");
        registerColumnType(Types.TIMESTAMP, "datetime");
        registerColumnType(Types.VARBINARY, "longblob");
        registerColumnType(Types.VARBINARY, 16777215, "mediumblob");
        registerColumnType(Types.VARBINARY, 65535, "blob");
        registerColumnType(Types.VARBINARY, 255, "tinyblob");
        registerColumnType(Types.BINARY, "binary($l)");
        registerColumnType(Types.LONGVARBINARY, "longblob");
        registerColumnType(Types.LONGVARBINARY, 16777215, "mediumblob");
        registerColumnType(Types.NUMERIC, "decimal($p,$s)");
        registerColumnType(Types.BLOB, "longblob");
        //      registerColumnType( Types.BLOB, 16777215, "mediumblob" );
        //      registerColumnType( Types.BLOB, 65535, "blob" );
        registerColumnType(Types.CLOB, "longtext");
        registerColumnType(Types.NCLOB, "longtext");
        //      registerColumnType( Types.CLOB, 16777215, "mediumtext" );
        //      registerColumnType( Types.CLOB, 65535, "text" );
        registerVarcharTypes();

        registerFunction("ascii", new StandardSQLFunction("ascii", StandardBasicTypes.INTEGER));
        registerFunction("bin", new StandardSQLFunction("bin", StandardBasicTypes.STRING));
        registerFunction("char_length", new StandardSQLFunction("char_length", StandardBasicTypes.LONG));
        registerFunction("character_length", new StandardSQLFunction("character_length", StandardBasicTypes.LONG));
        registerFunction("lcase", new StandardSQLFunction("lcase"));
        registerFunction("lower", new StandardSQLFunction("lower"));
        registerFunction("ltrim", new StandardSQLFunction("ltrim"));
        registerFunction("ord", new StandardSQLFunction("ord", StandardBasicTypes.INTEGER));
        registerFunction("quote", new StandardSQLFunction("quote"));
        registerFunction("reverse", new StandardSQLFunction("reverse"));
        registerFunction("rtrim", new StandardSQLFunction("rtrim"));
        registerFunction("soundex", new StandardSQLFunction("soundex"));
        registerFunction("space", new StandardSQLFunction("space", StandardBasicTypes.STRING));
        registerFunction("ucase", new StandardSQLFunction("ucase"));
        registerFunction("upper", new StandardSQLFunction("upper"));
        registerFunction("unhex", new StandardSQLFunction("unhex", StandardBasicTypes.STRING));

        registerFunction("abs", new StandardSQLFunction("abs"));
        registerFunction("sign", new StandardSQLFunction("sign", StandardBasicTypes.INTEGER));

        registerFunction("acos", new StandardSQLFunction("acos", StandardBasicTypes.DOUBLE));
        registerFunction("asin", new StandardSQLFunction("asin", StandardBasicTypes.DOUBLE));
        registerFunction("atan", new StandardSQLFunction("atan", StandardBasicTypes.DOUBLE));
        registerFunction("cos", new StandardSQLFunction("cos", StandardBasicTypes.DOUBLE));
        registerFunction("cot", new StandardSQLFunction("cot", StandardBasicTypes.DOUBLE));
        registerFunction("crc32", new StandardSQLFunction("crc32", StandardBasicTypes.LONG));
        registerFunction("exp", new StandardSQLFunction("exp", StandardBasicTypes.DOUBLE));
        registerFunction("ln", new StandardSQLFunction("ln", StandardBasicTypes.DOUBLE));
        registerFunction("log", new StandardSQLFunction("log", StandardBasicTypes.DOUBLE));
        registerFunction("log2", new StandardSQLFunction("log2", StandardBasicTypes.DOUBLE));
        registerFunction("log10", new StandardSQLFunction("log10", StandardBasicTypes.DOUBLE));
        registerFunction("pi", new NoArgSQLFunction("pi", StandardBasicTypes.DOUBLE));
        registerFunction("rand", new NoArgSQLFunction("rand", StandardBasicTypes.DOUBLE));
        registerFunction("sin", new StandardSQLFunction("sin", StandardBasicTypes.DOUBLE));
        registerFunction("sqrt", new StandardSQLFunction("sqrt", StandardBasicTypes.DOUBLE));
        registerFunction("stddev", new StandardSQLFunction("std", StandardBasicTypes.DOUBLE));
        registerFunction("tan", new StandardSQLFunction("tan", StandardBasicTypes.DOUBLE));

        registerFunction("radians", new StandardSQLFunction("radians", StandardBasicTypes.DOUBLE));
        registerFunction("degrees", new StandardSQLFunction("degrees", StandardBasicTypes.DOUBLE));

        registerFunction("ceiling", new StandardSQLFunction("ceiling", StandardBasicTypes.INTEGER));
        registerFunction("ceil", new StandardSQLFunction("ceil", StandardBasicTypes.INTEGER));
        registerFunction("floor", new StandardSQLFunction("floor", StandardBasicTypes.INTEGER));
        registerFunction("round", new StandardSQLFunction("round"));

        registerFunction("datediff", new StandardSQLFunction("datediff", StandardBasicTypes.INTEGER));
        registerFunction("timediff", new StandardSQLFunction("timediff", StandardBasicTypes.TIME));
        registerFunction("date_format", new StandardSQLFunction("date_format", StandardBasicTypes.STRING));

        registerFunction("curdate", new NoArgSQLFunction("curdate", StandardBasicTypes.DATE));
        registerFunction("curtime", new NoArgSQLFunction("curtime", StandardBasicTypes.TIME));
        registerFunction("current_date", new NoArgSQLFunction("current_date", StandardBasicTypes.DATE, false));
        registerFunction("current_time", new NoArgSQLFunction("current_time", StandardBasicTypes.TIME, false));
        registerFunction("current_timestamp",
                new NoArgSQLFunction("current_timestamp", StandardBasicTypes.TIMESTAMP, false));
        registerFunction("date", new StandardSQLFunction("date", StandardBasicTypes.DATE));
        registerFunction("day", new StandardSQLFunction("day", StandardBasicTypes.INTEGER));
        registerFunction("dayofmonth", new StandardSQLFunction("dayofmonth", StandardBasicTypes.INTEGER));
        registerFunction("dayname", new StandardSQLFunction("dayname", StandardBasicTypes.STRING));
        registerFunction("dayofweek", new StandardSQLFunction("dayofweek", StandardBasicTypes.INTEGER));
        registerFunction("dayofyear", new StandardSQLFunction("dayofyear", StandardBasicTypes.INTEGER));
        registerFunction("from_days", new StandardSQLFunction("from_days", StandardBasicTypes.DATE));
        registerFunction("from_unixtime", new StandardSQLFunction("from_unixtime", StandardBasicTypes.TIMESTAMP));
        registerFunction("hour", new StandardSQLFunction("hour", StandardBasicTypes.INTEGER));
        registerFunction("last_day", new StandardSQLFunction("last_day", StandardBasicTypes.DATE));
        registerFunction("localtime", new NoArgSQLFunction("localtime", StandardBasicTypes.TIMESTAMP));
        registerFunction("localtimestamp", new NoArgSQLFunction("localtimestamp", StandardBasicTypes.TIMESTAMP));
        registerFunction("microseconds", new StandardSQLFunction("microseconds", StandardBasicTypes.INTEGER));
        registerFunction("minute", new StandardSQLFunction("minute", StandardBasicTypes.INTEGER));
        registerFunction("month", new StandardSQLFunction("month", StandardBasicTypes.INTEGER));
        registerFunction("monthname", new StandardSQLFunction("monthname", StandardBasicTypes.STRING));
        registerFunction("now", new NoArgSQLFunction("now", StandardBasicTypes.TIMESTAMP));
        registerFunction("quarter", new StandardSQLFunction("quarter", StandardBasicTypes.INTEGER));
        registerFunction("second", new StandardSQLFunction("second", StandardBasicTypes.INTEGER));
        registerFunction("sec_to_time", new StandardSQLFunction("sec_to_time", StandardBasicTypes.TIME));
        registerFunction("sysdate", new NoArgSQLFunction("sysdate", StandardBasicTypes.TIMESTAMP));
        registerFunction("time", new StandardSQLFunction("time", StandardBasicTypes.TIME));
        registerFunction("timestamp", new StandardSQLFunction("timestamp", StandardBasicTypes.TIMESTAMP));
        registerFunction("time_to_sec", new StandardSQLFunction("time_to_sec", StandardBasicTypes.INTEGER));
        registerFunction("to_days", new StandardSQLFunction("to_days", StandardBasicTypes.LONG));
        registerFunction("unix_timestamp", new StandardSQLFunction("unix_timestamp", StandardBasicTypes.LONG));
        registerFunction("utc_date", new NoArgSQLFunction("utc_date", StandardBasicTypes.STRING));
        registerFunction("utc_time", new NoArgSQLFunction("utc_time", StandardBasicTypes.STRING));
        registerFunction("utc_timestamp", new NoArgSQLFunction("utc_timestamp", StandardBasicTypes.STRING));
        registerFunction("week", new StandardSQLFunction("week", StandardBasicTypes.INTEGER));
        registerFunction("weekday", new StandardSQLFunction("weekday", StandardBasicTypes.INTEGER));
        registerFunction("weekofyear", new StandardSQLFunction("weekofyear", StandardBasicTypes.INTEGER));
        registerFunction("year", new StandardSQLFunction("year", StandardBasicTypes.INTEGER));
        registerFunction("yearweek", new StandardSQLFunction("yearweek", StandardBasicTypes.INTEGER));

        registerFunction("hex", new StandardSQLFunction("hex", StandardBasicTypes.STRING));
        registerFunction("oct", new StandardSQLFunction("oct", StandardBasicTypes.STRING));

        registerFunction("octet_length", new StandardSQLFunction("octet_length", StandardBasicTypes.LONG));
        registerFunction("bit_length", new StandardSQLFunction("bit_length", StandardBasicTypes.LONG));

        registerFunction("bit_count", new StandardSQLFunction("bit_count", StandardBasicTypes.LONG));
        registerFunction("encrypt", new StandardSQLFunction("encrypt", StandardBasicTypes.STRING));
        registerFunction("md5", new StandardSQLFunction("md5", StandardBasicTypes.STRING));
        registerFunction("sha1", new StandardSQLFunction("sha1", StandardBasicTypes.STRING));
        registerFunction("sha", new StandardSQLFunction("sha", StandardBasicTypes.STRING));

        registerFunction("concat", new StandardSQLFunction("concat", StandardBasicTypes.STRING));

        getDefaultProperties().setProperty(Environment.MAX_FETCH_DEPTH, "2");
        getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE, DEFAULT_BATCH_SIZE);

        uniqueDelegate = new MySQLUniqueDelegate(this);
    }

    protected void registerVarcharTypes() {
        registerColumnType(Types.VARCHAR, "longtext");
        //      registerColumnType( Types.VARCHAR, 16777215, "mediumtext" );
        //      registerColumnType( Types.VARCHAR, 65535, "text" );
        registerColumnType(Types.VARCHAR, 255, "varchar($l)");
        registerColumnType(Types.LONGVARCHAR, "longtext");
    }

    @Override
    public String getAddColumnString() {
        return "add column";
    }

    @Override
    public boolean qualifyIndexName() {
        return false;
    }

    @Override
    public String getAddForeignKeyConstraintString(String constraintName, String[] foreignKey,
            String referencedTable, String[] primaryKey, boolean referencesPrimaryKey) {
        final String cols = String.join(", ", foreignKey);
        final String referencedCols = String.join(", ", primaryKey);
        return String.format(" add constraint %s foreign key (%s) references %s (%s)", constraintName, cols,
                referencedTable, referencedCols);
    }

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

    @Override
    public String getDropForeignKeyString() {
        return " drop foreign key ";
    }

    @Override
    public LimitHandler getLimitHandler() {
        return LIMIT_HANDLER;
    }

    @Override
    public String getLimitString(String sql, boolean hasOffset) {
        return sql + (hasOffset ? " limit ?, ?" : " limit ?");
    }

    @Override
    public char closeQuote() {
        return '`';
    }

    @Override
    public char openQuote() {
        return '`';
    }

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

    @Override
    public String[] getCreateCatalogCommand(String catalogName) {
        return new String[] { "create database " + catalogName };
    }

    @Override
    public String[] getDropCatalogCommand(String catalogName) {
        return new String[] { "drop database " + catalogName };
    }

    @Override
    public boolean canCreateSchema() {
        return false;
    }

    @Override
    public String[] getCreateSchemaCommand(String schemaName) {
        throw new UnsupportedOperationException(
                "MySQL does not support dropping creating/dropping schemas in the JDBC sense");
    }

    @Override
    public String[] getDropSchemaCommand(String schemaName) {
        throw new UnsupportedOperationException(
                "MySQL does not support dropping creating/dropping schemas in the JDBC sense");
    }

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

    @Override
    public String getSelectGUIDString() {
        return "select uuid()";
    }

    @Override
    public String getTableComment(String comment) {
        return " comment='" + comment + "'";
    }

    @Override
    public String getColumnComment(String comment) {
        return " comment '" + comment + "'";
    }

    @Override
    public MultiTableBulkIdStrategy getDefaultMultiTableBulkIdStrategy() {
        return new LocalTemporaryTableBulkIdStrategy(new IdTableSupportStandardImpl() {
            @Override
            public String getCreateIdTableCommand() {
                return "create temporary table if not exists";
            }

            @Override
            public String getDropIdTableCommand() {
                return "drop temporary table";
            }
        }, AfterUseAction.DROP, TempTableDdlTransactionHandling.NONE);
    }

    @Override
    public String getCastTypeName(int code) {
        switch (code) {
        case Types.BOOLEAN:
            return "char";
        case Types.INTEGER:
        case Types.BIGINT:
        case Types.SMALLINT:
            return smallIntegerCastTarget();
        case Types.FLOAT:
        case Types.REAL: {
            return floatingPointNumberCastTarget();
        }
        case Types.NUMERIC:
            return fixedPointNumberCastTarget();
        case Types.VARCHAR:
            return "char";
        case Types.VARBINARY:
            return "binary";
        default:
            return super.getCastTypeName(code);
        }
    }

    /**
     * Determine the cast target for {@link Types#INTEGER}, {@link Types#BIGINT} and {@link Types#SMALLINT}
     *
     * @return The proper cast target type.
     */
    protected String smallIntegerCastTarget() {
        return "signed";
    }

    /**
     * Determine the cast target for {@link Types#FLOAT} and {@link Types#REAL} (DOUBLE)
     *
     * @return The proper cast target type.
     */
    protected String floatingPointNumberCastTarget() {
        // MySQL does not allow casting to DOUBLE nor FLOAT, so we have to cast these as DECIMAL.
        // MariaDB does allow casting to DOUBLE, although not FLOAT.
        return fixedPointNumberCastTarget();
    }

    /**
     * Determine the cast target for {@link Types#NUMERIC}
     *
     * @return The proper cast target type.
     */
    protected String fixedPointNumberCastTarget() {
        // NOTE : the precision/scale are somewhat arbitrary choices, but MySQL/MariaDB
        // effectively require *some* values
        return "decimal(" + Column.DEFAULT_PRECISION + "," + Column.DEFAULT_SCALE + ")";
    }

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

    @Override
    public boolean isCurrentTimestampSelectStringCallable() {
        return false;
    }

    @Override
    public String getCurrentTimestampSelectString() {
        return "select now()";
    }

    @Override
    public int registerResultSetOutParameter(CallableStatement statement, int col) throws SQLException {
        return col;
    }

    @Override
    public ResultSet getResultSet(CallableStatement ps) throws SQLException {
        boolean isResultSet = ps.execute();
        while (!isResultSet && ps.getUpdateCount() != -1) {
            isResultSet = ps.getMoreResults();
        }
        return ps.getResultSet();
    }

    @Override
    public UniqueDelegate getUniqueDelegate() {
        return uniqueDelegate;
    }

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

    @Override
    public String renderOrderByElement(String expression, String collation, String order, NullPrecedence nulls) {
        final StringBuilder orderByElement = new StringBuilder();
        if (nulls != NullPrecedence.NONE) {
            // Workaround for NULLS FIRST / LAST support.
            orderByElement.append("case when ").append(expression).append(" is null then ");
            if (nulls == NullPrecedence.FIRST) {
                orderByElement.append("0 else 1");
            } else {
                orderByElement.append("1 else 0");
            }
            orderByElement.append(" end, ");
        }
        // Nulls precedence has already been handled so passing NONE value.
        orderByElement.append(super.renderOrderByElement(expression, collation, order, NullPrecedence.NONE));
        return orderByElement.toString();
    }

    // locking support

    @Override
    public String getForUpdateString() {
        return " for update";
    }

    @Override
    public String getWriteLockString(int timeout) {
        return " for update";
    }

    @Override
    public String getReadLockString(int timeout) {
        return " lock in share mode";
    }

    // Overridden informational metadata ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    @Override
    public boolean supportsEmptyInList() {
        return false;
    }

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

    @Override
    public boolean supportsLobValueChangePropogation() {
        // note: at least my local MySQL 5.1 install shows this not working...
        return false;
    }

    @Override
    public boolean supportsSubqueryOnMutatingTable() {
        return false;
    }

    @Override
    public boolean supportsLockTimeouts() {
        // yes, we do handle "lock timeout" conditions in the exception conversion delegate,
        // but that's a hardcoded lock timeout period across the whole entire MySQL database.
        // MySQL does not support specifying lock timeouts as part of the SQL statement, which is really
        // what this meta method is asking.
        return false;
    }

    @Override
    public SQLExceptionConversionDelegate buildSQLExceptionConversionDelegate() {
        return new SQLExceptionConversionDelegate() {
            @Override
            public JDBCException convert(SQLException sqlException, String message, String sql) {
                switch (sqlException.getErrorCode()) {
                case 1205:
                case 3572: {
                    return new PessimisticLockException(message, sqlException, sql);
                }
                case 1207:
                case 1206: {
                    return new LockAcquisitionException(message, sqlException, sql);
                }
                }

                final String sqlState = JdbcExceptionHelper.extractSqlState(sqlException);

                if ("41000".equals(sqlState)) {
                    return new LockTimeoutException(message, sqlException, sql);
                }

                if ("40001".equals(sqlState)) {
                    return new LockAcquisitionException(message, sqlException, sql);
                }

                return null;
            }
        };
    }

    @Override
    public String getNotExpression(String expression) {
        return "not (" + expression + ")";
    }

    @Override
    public IdentityColumnSupport getIdentityColumnSupport() {
        return new MySQLIdentityColumnSupport();
    }

    @Override
    public boolean isJdbcLogWarningsEnabledByDefault() {
        return false;
    }

    @Override
    public boolean supportsCascadeDelete() {
        return storageEngine.supportsCascadeDelete();
    }

    @Override
    public String getTableTypeString() {
        return storageEngine.getTableTypeString(getEngineKeyword());
    }

    protected String getEngineKeyword() {
        return "type";
    }

    @Override
    public boolean hasSelfReferentialForeignKeyBug() {
        return storageEngine.hasSelfReferentialForeignKeyBug();
    }

    @Override
    public boolean dropConstraints() {
        return storageEngine.dropConstraints();
    }

    protected MySQLStorageEngine getDefaultMySQLStorageEngine() {
        return MyISAMStorageEngine.INSTANCE;
    }

    @Override
    protected String escapeLiteral(String literal) {
        return ESCAPE_PATTERN.matcher(super.escapeLiteral(literal)).replaceAll(ESCAPE_PATTERN_REPLACEMENT);
    }

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

}