db2 bigint java_DB2Dialect.java

//$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";

}

}