//$Id: DB2Dialect.java,v 1.38 2005/12/08 02:41:15 oneovthafew Exp $packageorg.hibernate.dialect;importjava.sql.CallableStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Types;importorg.hibernate.Hibernate;importorg.hibernate.cfg.Environment;importorg.hibernate.dialect.function.NoArgSQLFunction;importorg.hibernate.dialect.function.SQLFunctionTemplate;importorg.hibernate.dialect.function.StandardSQLFunction;importorg.hibernate.dialect.function.VarArgsSQLFunction;/*** An SQL dialect for DB2.
*@authorGavin King*/publicclassDB2DialectextendsDialect {publicDB2Dialect() {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)");
registerFunction("abs",newStandardSQLFunction("abs") );
registerFunction("absval",newStandardSQLFunction("absval") );
registerFunction("sign",newStandardSQLFunction("sign", Hibernate.INTEGER) );
registerFunction("ceiling",newStandardSQLFunction("ceiling") );
registerFunction("ceil",newStandardSQLFunction("ceil") );
registerFunction("floor",newStandardSQLFunction("floor") );
registerFunction("round",newStandardSQLFunction("round") );
registerFunction("acos",newStandardSQLFunction("acos", Hibernate.DOUBLE) );
registerFunction("asin",newStandardSQLFunction("asin", Hibernate.DOUBLE) );
registerFunction("atan",newStandardSQLFunction("atan", Hibernate.DOUBLE) );
registerFunction("cos",newStandardSQLFunction("cos", Hibernate.DOUBLE) );
registerFunction("cot",newStandardSQLFunction("cot", Hibernate.DOUBLE) );
registerFunction("degrees",newStandardSQLFunction("degrees", Hibernate.DOUBLE) );
registerFunction("exp",newStandardSQLFunction("exp", Hibernate.DOUBLE) );
registerFunction("float",newStandardSQLFunction("float", Hibernate.DOUBLE) );
registerFunction("hex",newStandardSQLFunction("hex", Hibernate.STRING) );
registerFunction("ln",newStandardSQLFunction("ln", Hibernate.DOUBLE) );
registerFunction("log",newStandardSQLFunction("log", Hibernate.DOUBLE) );
registerFunction("log10",newStandardSQLFunction("log10", Hibernate.DOUBLE) );
registerFunction("radians",newStandardSQLFunction("radians", Hibernate.DOUBLE) );
registerFunction("rand",newNoArgSQLFunction("rand", Hibernate.DOUBLE) );
registerFunction("sin",newStandardSQLFunction("sin", Hibernate.DOUBLE) );
registerFunction("soundex",newStandardSQLFunction("soundex", Hibernate.STRING) );
registerFunction("sqrt",newStandardSQLFunction("sqrt", Hibernate.DOUBLE) );
registerFunction("stddev",newStandardSQLFunction("stddev", Hibernate.DOUBLE) );
registerFunction("tan",newStandardSQLFunction("tan", Hibernate.DOUBLE) );
registerFunction("variance",newStandardSQLFunction("variance", Hibernate.DOUBLE) );
registerFunction("julian_day",newStandardSQLFunction("julian_day", Hibernate.INTEGER) );
registerFunction("microsecond",newStandardSQLFunction("microsecond", Hibernate.INTEGER) );
registerFunction("midnight_seconds",newStandardSQLFunction("midnight_seconds", Hibernate.INTEGER) );
registerFunction("minute",newStandardSQLFunction("minute", Hibernate.INTEGER) );
registerFunction("month",newStandardSQLFunction("month", Hibernate.INTEGER) );
registerFunction("monthname",newStandardSQLFunction("monthname", Hibernate.STRING) );
registerFunction("quarter",newStandardSQLFunction("quarter", Hibernate.INTEGER) );
registerFunction("hour",newStandardSQLFunction("hour", Hibernate.INTEGER) );
registerFunction("second",newStandardSQLFunction("second", Hibernate.INTEGER) );
registerFunction("current_date",newNoArgSQLFunction("current date", Hibernate.DATE,false) );
registerFunction("date",newStandardSQLFunction("date", Hibernate.DATE) );
registerFunction("day",newStandardSQLFunction("day", Hibernate.INTEGER) );
registerFunction("dayname",newStandardSQLFunction("dayname", Hibernate.STRING) );
registerFunction("dayofweek",newStandardSQLFunction("dayofweek", Hibernate.INTEGER) );
registerFunction("dayofweek_iso",newStandardSQLFunction("dayofweek_iso", Hibernate.INTEGER) );
registerFunction("dayofyear",newStandardSQLFunction("dayofyear", Hibernate.INTEGER) );
registerFunction("days",newStandardSQLFunction("days", Hibernate.LONG) );
registerFunction("current_time",newNoArgSQLFunction("current time", Hibernate.TIME,false) );
registerFunction("time",newStandardSQLFunction("time", Hibernate.TIME) );
registerFunction("current_timestamp",newNoArgSQLFunction("current timestamp", Hibernate.TIMESTAMP,false) );
registerFunction("timestamp",newStandardSQLFunction("timestamp", Hibernate.TIMESTAMP) );
registerFunction("timestamp_iso",newStandardSQLFunction("timestamp_iso", Hibernate.TIMESTAMP) );
registerFunction("week",newStandardSQLFunction("week", Hibernate.INTEGER) );
registerFunction("week_iso",newStandardSQLFunction("week_iso", Hibernate.INTEGER) );
registerFunction("year",newStandardSQLFunction("year", Hibernate.INTEGER) );
registerFunction("double",newStandardSQLFunction("double", Hibernate.DOUBLE) );
registerFunction("varchar",newStandardSQLFunction("varchar", Hibernate.STRING) );
registerFunction("real",newStandardSQLFunction("real", Hibernate.FLOAT) );
registerFunction("bigint",newStandardSQLFunction("bigint", Hibernate.LONG) );
registerFunction("char",newStandardSQLFunction("char", Hibernate.CHARACTER) );
registerFunction("integer",newStandardSQLFunction("integer", Hibernate.INTEGER) );
registerFunction("smallint",newStandardSQLFunction("smallint", Hibernate.SHORT) );
registerFunction("digits",newStandardSQLFunction("digits", Hibernate.STRING) );
registerFunction("chr",newStandardSQLFunction("chr", Hibernate.CHARACTER) );
registerFunction("upper",newStandardSQLFunction("upper") );
registerFunction("lower",newStandardSQLFunction("lower") );
registerFunction("ucase",newStandardSQLFunction("ucase") );
registerFunction("lcase",newStandardSQLFunction("lcase") );
registerFunction("length",newStandardSQLFunction("length", Hibernate.LONG) );
registerFunction("ltrim",newStandardSQLFunction("ltrim") );
registerFunction("rtrim",newStandardSQLFunction("rtrim") );
registerFunction("substr",newStandardSQLFunction("substr", Hibernate.STRING ) );
registerFunction("posstr",newStandardSQLFunction("posstr", Hibernate.INTEGER ) );
registerFunction("substring",newStandardSQLFunction("substr", Hibernate.STRING ) );
registerFunction("trim",newSQLFunctionTemplate( Hibernate.INTEGER,"ltrim(rtrim(?1))") );
registerFunction("bit_length",newSQLFunctionTemplate( Hibernate.INTEGER,"length(?1)*8") );
registerFunction("concat",newVarArgsSQLFunction(Hibernate.STRING,"","||","") );
registerFunction("str",newSQLFunctionTemplate( Hibernate.STRING,"rtrim(char(?1))") );
registerKeyword("current");
registerKeyword("date");
registerKeyword("time");
registerKeyword("timestamp");
registerKeyword("fetch");
registerKeyword("first");
registerKeyword("rows");
registerKeyword("only");
getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE, NO_BATCH);
}publicString getLowercaseFunction() {return"lcase";
}publicString getAddColumnString() {return"add column";
}publicbooleandropConstraints() {returnfalse;
}publicbooleansupportsIdentityColumns() {returntrue;
}publicString getIdentitySelectString() {return"values identity_val_local()";
}publicString getIdentityColumnString() {return"generated by default as identity";//not null
(start with 1) is implicit}publicString getIdentityInsertString() {return"default";
}publicString getSequenceNextValString(String sequenceName) {return"values nextval for"+sequenceName;
}publicString getCreateSequenceString(String sequenceName) {return"create sequence"+sequenceName;
}publicString getDropSequenceString(String sequenceName) {return"drop sequence"+sequenceName+"restrict";
}publicbooleansupportsSequences() {returntrue;
}publicString getQuerySequencesString() {return"select seqname from sysibm.syssequences";
}publicbooleansupportsLimit() {returntrue;
}/*public String getLimitString(String sql, boolean hasOffset) {
StringBuffer rownumber = new StringBuffer(50)
.append(" rownumber() over(");
int orderByIndex = sql.toLowerCase().indexOf("order by");
if (orderByIndex>0) rownumber.append( sql.substring(orderByIndex) );
rownumber.append(") as row_,");
StringBuffer pagingSelect = new StringBuffer( sql.length()+100 )
.append("select * from ( ")
.append(sql)
.insert( getAfterSelectInsertPoint(sql)+16, rownumber.toString() )
.append(" ) as temp_ where row_ ");
if (hasOffset) {
pagingSelect.append("between ?+1 and ?");
}
else {
pagingSelect.append("<= ?");
}
return pagingSelect.toString();
}*//*** Render the rownumber() over (
. ) as rownumber_,
* bit, that goes in the select list*/privateString getRowNumber(String sql) {
StringBuffer rownumber=newStringBuffer(50)
.append("rownumber() over(");intorderByIndex=sql.toLowerCase().indexOf("order by");if( orderByIndex>0&&!hasDistinct(sql) ) {
rownumber.append( sql.substring(orderByIndex) );
}
rownumber.append(") as rownumber_,");returnrownumber.toString();
}publicString getLimitString(String sql,booleanhasOffset) {intstartOfSelect=sql.toLowerCase().indexOf("select");
StringBuffer pagingSelect=newStringBuffer( sql.length()+100)
.append( sql.substring(0, startOfSelect) )//add the comment.append("select * from ( select")//nest the main query in an outer select.append( getRowNumber(sql) );//add the rownnumber bit into the outer query select listif( hasDistinct(sql) ) {
pagingSelect.append("row_.* from (")//add another (inner) nested select.append( sql.substring(startOfSelect) )//add the main query.append(") as row_");//close off the inner nested select}else{
pagingSelect.append( sql.substring( startOfSelect+6) );//add the main query}
pagingSelect.append(") as temp_ where rownumber_");//add the restriction to the outer selectif(hasOffset) {
pagingSelect.append("between ?+1 and ?");
}else{
pagingSelect.append("<= ?");
}returnpagingSelect.toString();
}privatestaticbooleanhasDistinct(String sql) {returnsql.toLowerCase().indexOf("select distinct")>=0;
}publicString getForUpdateString() {return"for read only with rs";
}publicbooleanuseMaxForLimit() {returntrue;
}publicbooleansupportsOuterJoinForUpdate() {returnfalse;
}publicbooleansupportsNotNullUnique() {returnfalse;
}publicString getSelectClauseNullString(intsqlType) {
String literal;switch(sqlType) {caseTypes.VARCHAR:caseTypes.CHAR:
literal="'x'";break;caseTypes.DATE:
literal="'2000-1-1'";break;caseTypes.TIMESTAMP:
literal="'2000-1-1 00:00:00'";break;caseTypes.TIME:
literal="'00:00:00'";break;default:
literal="0";
}return"nullif("+literal+','+literal+')';
}publicstaticvoidmain(String[] args) {
System.out.println(newDB2Dialect().getLimitString("/*foo*/ select * from foos",true) );
System.out.println(newDB2Dialect().getLimitString("/*foo*/ select distinct * from foos",true) );
System.out.println(newDB2Dialect().getLimitString("/*foo*/ select * from foos foo order by foo.bar, foo.baz",true) );
System.out.println(newDB2Dialect().getLimitString("/*foo*/ select distinct * from foos foo order by foo.bar, foo.baz",true) );
}publicbooleansupportsUnionAll() {returntrue;
}publicintregisterResultSetOutParameter(CallableStatement statement,intcol)throwsSQLException {returncol;
}publicResultSet getResultSet(CallableStatement ps)throwsSQLException {booleanisResultSet=ps.execute();//This assumes you will want to ignore any update countswhile(!isResultSet&&ps.getUpdateCount()!=-1) {
isResultSet=ps.getMoreResults();
}
ResultSet rs=ps.getResultSet();//You may still have other ResultSets or update counts left to process here//but you can't do it now or the ResultSet you just got will be closedreturnrs;
}publicbooleansupportsCommentOn() {returntrue;
}publicbooleansupportsTemporaryTables() {returntrue;
}publicString getCreateTemporaryTableString() {return"declare global temporary table";
}publicString getCreateTemporaryTablePostfix() {return"not logged";
}publicString generateTemporaryTableName(String baseTableName) {return"session."+super.generateTemporaryTableName(baseTableName);
}publicbooleansupportsCurrentTimestampSelection() {returntrue;
}publicString getCurrentTimestampSelectString() {return"values current timestamp";
}publicbooleanisCurrentTimestampSelectStringCallable() {returnfalse;
}publicbooleansupportsParametersInInsertSelect() {//DB2 known to not support parameters within the select//clause of an SQL INSERT
SELECT
statementreturnfalse;
}publicString getCurrentTimestampSQLFunctionName() {return"sysdate";
}
}