/*
* Hibernate, Relational Persistence for Idiomatic Java
*
* Copyright (c) 2010, Red Hat Inc. or third-party contributors as
* indicated by the @author tags or express copyright attribution
* statements applied by the authors. All third-party contributions are
* distributed under license by Red Hat Inc.
*
* This copyrighted material is made available to anyone wishing to use, modify,
* copy, or redistribute it subject to the terms and conditions of the GNU
* Lesser General Public License, as published by the Free Software Foundation.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
* or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License
* for more details.
*
* You should have received a copy of the GNU Lesser General Public License
* along with this distribution; if not, write to:
* Free Software Foundation, Inc.
* 51 Franklin Street, Fifth Floor
* Boston, MA 02110-1301 USA
*/
package org.hibernate.dialect;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
//import org.hibernate.JDBCException;
import org.hibernate.cfg.AvailableSettings;
//import org.hibernate.exception.LockTimeoutException;
//import org.hibernate.exception.spi.SQLExceptionConversionDelegate;
//import org.hibernate.internal.util.JdbcExceptionHelper;
//import org.hibernate.type.StandardBasicTypes;
//import org.hibernate.type.descriptor.sql.SmallIntTypeDescriptor;
//import org.hibernate.type.descriptor.sql.SqlTypeDescriptor;
/**
* An SQL dialect for DB2.
*
* @author Gavin King
*/
public class DB2Dialect extends Dialect {
// private final UniqueDelegate uniqueDelegate;
/**
* Constructs a DB2Dialect
*/
public DB2Dialect() {
super();
registerColumnType( Types.BIT, "smallint" );
registerColumnType( Types.BIGINT, "bigint" );
registerColumnType( Types.SMALLINT, "smallint" );
registerColumnType( Types.TINYINT, "smallint" );
registerColumnType( Types.INTEGER, "integer" );
registerColumnType( Types.CHAR, "char(1)" );
registerColumnType( Types.VARCHAR, "varchar($l)" );
registerColumnType( Types.FLOAT, "float" );
registerColumnType( Types.DOUBLE, "double" );
registerColumnType( Types.DATE, "date" );
registerColumnType( Types.TIME, "time" );
registerColumnType( Types.TIMESTAMP, "timestamp" );
registerColumnType( Types.VARBINARY, "varchar($l) for bit data" );
registerColumnType( Types.NUMERIC, "numeric($p,$s)" );
registerColumnType( Types.BLOB, "blob($l)" );
registerColumnType( Types.CLOB, "clob($l)" );
registerColumnType( Types.LONGVARCHAR, "long varchar" );
registerColumnType( Types.LONGVARBINARY, "long varchar for bit data" );
registerColumnType( Types.BINARY, "varchar($l) for bit data" );
registerColumnType( Types.BINARY, 254, "char($l) for bit data" );
registerColumnType( Types.BOOLEAN, "smallint" );
registerKeyword( "current" );
registerKeyword( "date" );
registerKeyword( "time" );
registerKeyword( "timestamp" );
registerKeyword( "fetch" );
registerKeyword( "first" );
registerKeyword( "rows" );
registerKeyword( "only" );
getDefaultProperties().setProperty( AvailableSettings.STATEMENT_BATCH_SIZE, NO_BATCH );
// uniqueDelegate = new DB2UniqueDelegate( this );
}
@Override
public String getLowercaseFunction() {
return "lcase";
}
@Override
public String getAddColumnString() {
return "add column";
}
@Override
public boolean dropConstraints() {
return false;
}
@Override
public boolean supportsIdentityColumns() {
return true;
}
@Override
public String getIdentitySelectString() {
return "values identity_val_local()";
}
@Override
public String getIdentityColumnString() {
return "generated by default as identity";
}
@Override
public String getIdentityInsertString() {
return "default";
}
@Override
public String getSequenceNextValString(String sequenceName) {
return "values nextval for " + sequenceName;
}
@Override
public String getCreateSequenceString(String sequenceName) {
return "create sequence " + sequenceName;
}
@Override
public String getDropSequenceString(String sequenceName) {
return "drop sequence " + sequenceName + " restrict";
}
@Override
public boolean supportsSequences() {
return true;
}
@Override
public boolean supportsPooledSequences() {
return true;
}
@Override
public String getQuerySequencesString() {
return "select seqname from sysibm.syssequences";
}
@Override
@SuppressWarnings("deprecation")
public boolean supportsLimit() {
return true;
}
@Override
@SuppressWarnings("deprecation")
public boolean supportsVariableLimit() {
return false;
}
@Override
@SuppressWarnings("deprecation")
public String getLimitString(String sql, int offset, int limit) {
if ( offset == 0 ) {
return sql + " fetch first " + limit + " rows only";
}
//nest the main query in an outer select
return "select * from ( select inner2_.*, rownumber() over(order by order of inner2_) as rownumber_ from ( "
+ sql + " fetch first " + limit + " rows only ) as inner2_ ) as inner1_ where rownumber_ > "
+ offset + " order by rownumber_";
}
/**
* {@inheritDoc}
* <p/>
*
* DB2 does have a one-based offset, however this was actually already handled in the limit string building
* (the '?+1' bit). To not mess up inheritors, I'll leave that part alone and not touch the offset here.
*/
@Override
@SuppressWarnings("deprecation")
public int convertToFirstRowValue(int zeroBasedFirstResult) {
return zeroBasedFirstResult;
}
@Override
@SuppressWarnings("deprecation")
public String getForUpdateString() {
return " for read only with rs use and keep update locks";
}
@Override
@SuppressWarnings("deprecation")
public boolean useMaxForLimit() {
return true;
}
@Override
public boolean supportsOuterJoinForUpdate() {
return false;
}
@Override
public boolean supportsExistsInSelect() {
return false;
}
@Override
public boolean supportsLockTimeouts() {
//as far as I know, DB2 doesn't support this
return false;
}
@Override
public String getSelectClauseNullString(int sqlType) {
String literal;
switch ( sqlType ) {
case Types.VARCHAR:
case Types.CHAR:
literal = "'x'";
break;
case Types.DATE:
literal = "'2000-1-1'";
break;
case Types.TIMESTAMP:
literal = "'2000-1-1 00:00:00'";
break;
case Types.TIME:
literal = "'00:00:00'";
break;
default:
literal = "0";
}
return "nullif(" + literal + ',' + literal + ')';
}
@Override
public boolean supportsUnionAll() {
return true;
}
@Override
public int registerResultSetOutParameter(CallableStatement statement, int col) throws SQLException {
return col;
}
@Override
public ResultSet getResultSet(CallableStatement ps) throws SQLException {
boolean isResultSet = ps.execute();
// This assumes you will want to ignore any update counts
while ( !isResultSet && ps.getUpdateCount() != -1 ) {
isResultSet = ps.getMoreResults();
}
return ps.getResultSet();
}
@Override
public boolean supportsCommentOn() {
return true;
}
@Override
public boolean supportsTemporaryTables() {
return true;
}
@Override
public String getCreateTemporaryTableString() {
return "declare global temporary table";
}
@Override
public String getCreateTemporaryTablePostfix() {
return "not logged";
}
@Override
public String generateTemporaryTableName(String baseTableName) {
return "session." + super.generateTemporaryTableName( baseTableName );
}
@Override
public boolean supportsCurrentTimestampSelection() {
return true;
}
@Override
public String getCurrentTimestampSelectString() {
return "values current timestamp";
}
@Override
public boolean isCurrentTimestampSelectStringCallable() {
return false;
}
/**
* {@inheritDoc}
* <p/>
* NOTE : DB2 is know to support parameters in the <tt>SELECT</tt> clause, but only in casted form
* (see {@link #requiresCastingOfParametersInSelectClause()}).
*/
@Override
public boolean supportsParametersInInsertSelect() {
return true;
}
/**
* {@inheritDoc}
* <p/>
* DB2 in fact does require that parameters appearing in the select clause be wrapped in cast() calls
* to tell the DB parser the type of the select value.
*/
@Override
public boolean requiresCastingOfParametersInSelectClause() {
return true;
}
@Override
public boolean supportsResultSetPositionQueryMethodsOnForwardOnlyCursor() {
return false;
}
@Override
public String getCrossJoinSeparator() {
//DB2 v9.1 doesn't support 'cross join' syntax
return ", ";
}
// Overridden informational metadata ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
@Override
public boolean supportsEmptyInList() {
return false;
}
@Override
public boolean supportsLobValueChangePropogation() {
return false;
}
@Override
public boolean doesReadCommittedCauseWritersToBlockReaders() {
return true;
}
@Override
public boolean supportsTupleDistinctCounts() {
return false;
}
// @Override
// protected SqlTypeDescriptor getSqlTypeDescriptorOverride(int sqlCode) {
// return sqlCode == Types.BOOLEAN ? SmallIntTypeDescriptor.INSTANCE : super.getSqlTypeDescriptorOverride( sqlCode );
// }
//
// @Override
// public SQLExceptionConversionDelegate buildSQLExceptionConversionDelegate() {
// return new SQLExceptionConversionDelegate() {
// @Override
// public JDBCException convert(SQLException sqlException, String message, String sql) {
// final String sqlState = JdbcExceptionHelper.extractSqlState( sqlException );
// final int errorCode = JdbcExceptionHelper.extractErrorCode( sqlException );
//
// if( -952 == errorCode && "57014".equals( sqlState )){
// throw new LockTimeoutException( message, sqlException, sql );
// }
// return null;
// }
// };
// }
@Override
public String getNotExpression( String expression ) {
return "not (" + expression + ")";
}
}