org.hibernate.dialect.Oracle9Dialect.java Source code

Java tutorial

Introduction

Here is the source code for org.hibernate.dialect.Oracle9Dialect.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 org.hibernate.cfg.Environment;
import org.hibernate.dialect.function.NoArgSQLFunction;
import org.hibernate.dialect.function.NvlFunction;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.dialect.function.VarArgsSQLFunction;
import org.hibernate.exception.spi.TemplatedViolatedConstraintNameExtracter;
import org.hibernate.exception.spi.ViolatedConstraintNameExtracter;
import org.hibernate.hql.spi.id.IdTableSupportStandardImpl;
import org.hibernate.hql.spi.id.MultiTableBulkIdStrategy;
import org.hibernate.hql.spi.id.global.GlobalTemporaryTableBulkIdStrategy;
import org.hibernate.hql.spi.id.local.AfterUseAction;
import org.hibernate.internal.CoreMessageLogger;
import org.hibernate.internal.util.JdbcExceptionHelper;
import org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorOracleDatabaseImpl;
import org.hibernate.tool.schema.extract.spi.SequenceInformationExtractor;
import org.hibernate.type.StandardBasicTypes;
import org.jboss.logging.Logger;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Locale;

/**
 * An SQL dialect for Oracle 9 (uses ANSI-style syntax where possible).
 *
 * @author Gavin King
 * @author David Channon
 *
 * @deprecated Use either Oracle9iDialect or Oracle10gDialect instead
 */
@SuppressWarnings("deprecation")
@Deprecated
public class Oracle9Dialect extends Dialect {

    private static final int PARAM_LIST_SIZE_LIMIT = 1000;

    private static final CoreMessageLogger LOG = Logger.getMessageLogger(CoreMessageLogger.class,
            Oracle9Dialect.class.getName());

    /**
     * Constructs a Oracle9Dialect
     */
    public Oracle9Dialect() {
        super();
        LOG.deprecatedOracle9Dialect();
        registerColumnType(Types.BIT, "number(1,0)");
        registerColumnType(Types.BIGINT, "number(19,0)");
        registerColumnType(Types.SMALLINT, "number(5,0)");
        registerColumnType(Types.TINYINT, "number(3,0)");
        registerColumnType(Types.INTEGER, "number(10,0)");
        registerColumnType(Types.CHAR, "char(1 char)");
        registerColumnType(Types.VARCHAR, 4000, "varchar2($l char)");
        registerColumnType(Types.VARCHAR, "long");
        registerColumnType(Types.FLOAT, "float");
        registerColumnType(Types.DOUBLE, "double precision");
        registerColumnType(Types.DATE, "date");
        registerColumnType(Types.TIME, "date");
        registerColumnType(Types.TIMESTAMP, "timestamp");
        registerColumnType(Types.VARBINARY, 2000, "raw($l)");
        registerColumnType(Types.VARBINARY, "long raw");
        registerColumnType(Types.NUMERIC, "number($p,$s)");
        registerColumnType(Types.DECIMAL, "number($p,$s)");
        registerColumnType(Types.BLOB, "blob");
        registerColumnType(Types.CLOB, "clob");

        // Oracle driver reports to support getGeneratedKeys(), but they only
        // support the version taking an array of the names of the columns to
        // be returned (via its RETURNING clause).  No other driver seems to
        // support this overloaded version.
        getDefaultProperties().setProperty(Environment.USE_GET_GENERATED_KEYS, "false");
        getDefaultProperties().setProperty(Environment.USE_STREAMS_FOR_BINARY, "true");
        getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE, DEFAULT_BATCH_SIZE);
        getDefaultProperties().setProperty(Environment.BATCH_VERSIONED_DATA, "false");

        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("cosh", new StandardSQLFunction("cosh", StandardBasicTypes.DOUBLE));
        registerFunction("exp", new StandardSQLFunction("exp", StandardBasicTypes.DOUBLE));
        registerFunction("ln", new StandardSQLFunction("ln", StandardBasicTypes.DOUBLE));
        registerFunction("sin", new StandardSQLFunction("sin", StandardBasicTypes.DOUBLE));
        registerFunction("sinh", new StandardSQLFunction("sinh", StandardBasicTypes.DOUBLE));
        registerFunction("stddev", new StandardSQLFunction("stddev", StandardBasicTypes.DOUBLE));
        registerFunction("sqrt", new StandardSQLFunction("sqrt", StandardBasicTypes.DOUBLE));
        registerFunction("tan", new StandardSQLFunction("tan", StandardBasicTypes.DOUBLE));
        registerFunction("tanh", new StandardSQLFunction("tanh", StandardBasicTypes.DOUBLE));
        registerFunction("variance", new StandardSQLFunction("variance", StandardBasicTypes.DOUBLE));

        registerFunction("round", new StandardSQLFunction("round"));
        registerFunction("trunc", new StandardSQLFunction("trunc"));
        registerFunction("ceil", new StandardSQLFunction("ceil"));
        registerFunction("floor", new StandardSQLFunction("floor"));

        registerFunction("chr", new StandardSQLFunction("chr", StandardBasicTypes.CHARACTER));
        registerFunction("initcap", new StandardSQLFunction("initcap"));
        registerFunction("lower", new StandardSQLFunction("lower"));
        registerFunction("ltrim", new StandardSQLFunction("ltrim"));
        registerFunction("rtrim", new StandardSQLFunction("rtrim"));
        registerFunction("soundex", new StandardSQLFunction("soundex"));
        registerFunction("upper", new StandardSQLFunction("upper"));
        registerFunction("ascii", new StandardSQLFunction("ascii", StandardBasicTypes.INTEGER));

        registerFunction("to_char", new StandardSQLFunction("to_char", StandardBasicTypes.STRING));
        registerFunction("to_date", new StandardSQLFunction("to_date", StandardBasicTypes.TIMESTAMP));

        registerFunction("current_date", new NoArgSQLFunction("current_date", StandardBasicTypes.DATE, false));
        registerFunction("current_time", new NoArgSQLFunction("current_timestamp", StandardBasicTypes.TIME, false));
        registerFunction("current_timestamp",
                new NoArgSQLFunction("current_timestamp", StandardBasicTypes.TIMESTAMP, false));

        registerFunction("last_day", new StandardSQLFunction("last_day", StandardBasicTypes.DATE));
        registerFunction("sysdate", new NoArgSQLFunction("sysdate", StandardBasicTypes.DATE, false));
        registerFunction("systimestamp", new NoArgSQLFunction("systimestamp", StandardBasicTypes.TIMESTAMP, false));
        registerFunction("uid", new NoArgSQLFunction("uid", StandardBasicTypes.INTEGER, false));
        registerFunction("user", new NoArgSQLFunction("user", StandardBasicTypes.STRING, false));

        registerFunction("rowid", new NoArgSQLFunction("rowid", StandardBasicTypes.LONG, false));
        registerFunction("rownum", new NoArgSQLFunction("rownum", StandardBasicTypes.LONG, false));

        // Multi-param string dialect functions...
        registerFunction("concat", new VarArgsSQLFunction(StandardBasicTypes.STRING, "", "||", ""));
        registerFunction("instr", new StandardSQLFunction("instr", StandardBasicTypes.INTEGER));
        registerFunction("instrb", new StandardSQLFunction("instrb", StandardBasicTypes.INTEGER));
        registerFunction("lpad", new StandardSQLFunction("lpad", StandardBasicTypes.STRING));
        registerFunction("replace", new StandardSQLFunction("replace", StandardBasicTypes.STRING));
        registerFunction("rpad", new StandardSQLFunction("rpad", StandardBasicTypes.STRING));
        registerFunction("substr", new StandardSQLFunction("substr", StandardBasicTypes.STRING));
        registerFunction("substrb", new StandardSQLFunction("substrb", StandardBasicTypes.STRING));
        registerFunction("translate", new StandardSQLFunction("translate", StandardBasicTypes.STRING));

        registerFunction("substring", new StandardSQLFunction("substr", StandardBasicTypes.STRING));
        registerFunction("locate", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "instr(?2,?1)"));
        registerFunction("bit_length", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "vsize(?1)*8"));
        registerFunction("coalesce", new NvlFunction());

        // Multi-param numeric dialect functions...
        registerFunction("atan2", new StandardSQLFunction("atan2", StandardBasicTypes.FLOAT));
        registerFunction("log", new StandardSQLFunction("log", StandardBasicTypes.INTEGER));
        registerFunction("mod", new StandardSQLFunction("mod", StandardBasicTypes.INTEGER));
        registerFunction("nvl", new StandardSQLFunction("nvl"));
        registerFunction("nvl2", new StandardSQLFunction("nvl2"));
        registerFunction("power", new StandardSQLFunction("power", StandardBasicTypes.FLOAT));

        // Multi-param date dialect functions...
        registerFunction("add_months", new StandardSQLFunction("add_months", StandardBasicTypes.DATE));
        registerFunction("months_between", new StandardSQLFunction("months_between", StandardBasicTypes.FLOAT));
        registerFunction("next_day", new StandardSQLFunction("next_day", StandardBasicTypes.DATE));

        registerFunction("str", new StandardSQLFunction("to_char", StandardBasicTypes.STRING));
    }

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

    @Override
    public String getSequenceNextValString(String sequenceName) {
        return "select " + getSelectSequenceNextValString(sequenceName) + " from dual";
    }

    @Override
    public String getSelectSequenceNextValString(String sequenceName) {
        return sequenceName + ".nextval";
    }

    @Override
    public String getCreateSequenceString(String sequenceName) {
        //starts with 1, implicitly
        return "create sequence " + sequenceName;
    }

    @Override
    public String getDropSequenceString(String sequenceName) {
        return "drop sequence " + sequenceName;
    }

    @Override
    public String getCascadeConstraintsString() {
        return " cascade constraints";
    }

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

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

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

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

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

    @Override
    public String getLimitString(String sql, boolean hasOffset) {

        sql = sql.trim();
        boolean isForUpdate = false;
        if (sql.toLowerCase(Locale.ROOT).endsWith(" for update")) {
            sql = sql.substring(0, sql.length() - 11);
            isForUpdate = true;
        }

        final StringBuilder pagingSelect = new StringBuilder(sql.length() + 100);
        if (hasOffset) {
            pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
        } else {
            pagingSelect.append("select * from ( ");
        }
        pagingSelect.append(sql);
        if (hasOffset) {
            pagingSelect.append(" ) row_ where rownum <= ?) where rownum_ > ?");
        } else {
            pagingSelect.append(" ) where rownum <= ?");
        }

        if (isForUpdate) {
            pagingSelect.append(" for update");
        }

        return pagingSelect.toString();
    }

    @Override
    public String getForUpdateString(String aliases) {
        return getForUpdateString() + " of " + aliases;
    }

    @Override
    public String getForUpdateNowaitString(String aliases) {
        return getForUpdateString() + " of " + aliases + " nowait";
    }

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

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

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

    @Override
    public String getQuerySequencesString() {
        return "select * from all_sequences";
    }

    public SequenceInformationExtractor getSequenceInformationExtractor() {
        return SequenceInformationExtractorOracleDatabaseImpl.INSTANCE;
    }

    @Override
    public String getSelectGUIDString() {
        return "select rawtohex(sys_guid()) from dual";
    }

    @Override
    public ViolatedConstraintNameExtracter getViolatedConstraintNameExtracter() {
        return EXTRACTER;
    }

    private static final ViolatedConstraintNameExtracter EXTRACTER = new TemplatedViolatedConstraintNameExtracter() {
        @Override
        protected String doExtractConstraintName(SQLException sqle) throws NumberFormatException {
            final int errorCode = JdbcExceptionHelper.extractErrorCode(sqle);
            if (errorCode == 1 || errorCode == 2291 || errorCode == 2292) {
                return extractUsingTemplate("constraint (", ") violated", sqle.getMessage());
            } else if (errorCode == 1400) {
                // simple nullability constraint
                return null;
            } else {
                return null;
            }
        }
    };

    @Override
    public int registerResultSetOutParameter(java.sql.CallableStatement statement, int col) throws SQLException {
        //   register the type of the out param - an Oracle specific type
        statement.registerOutParameter(col, OracleTypesHelper.INSTANCE.getOracleCursorTypeSqlType());
        col++;
        return col;
    }

    @Override
    public ResultSet getResultSet(CallableStatement ps) throws SQLException {
        ps.execute();
        return (ResultSet) ps.getObject(1);
    }

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

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

    @Override
    public MultiTableBulkIdStrategy getDefaultMultiTableBulkIdStrategy() {
        return new GlobalTemporaryTableBulkIdStrategy(new IdTableSupportStandardImpl() {
            @Override
            public String generateIdTableName(String baseName) {
                final String name = super.generateIdTableName(baseName);
                return name.length() > 30 ? name.substring(0, 30) : name;
            }

            @Override
            public String getCreateIdTableCommand() {
                return "create global temporary table";
            }

            @Override
            public String getCreateIdTableStatementOptions() {
                return "on commit delete rows";
            }
        }, AfterUseAction.CLEAN);
    }

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

    @Override
    public String getCurrentTimestampSelectString() {
        return "select systimestamp from dual";
    }

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

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

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

    @Override
    public int getInExpressionCountLimit() {
        return PARAM_LIST_SIZE_LIMIT;
    }

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

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

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