[hibernate-commits] Hibernate SVN: r10249 - in trunk/Hibernate3/src/org/hibernate/dialect: . function

Author: steve.ebersole(a)jboss.com Date: 2006-08-11 15:37:17 -0400 (Fri, 11 Aug 2006) New Revision: 10249 Added: trunk/Hibernate3/src/org/hibernate/dialect/function/AnsiTrimEmulationFunction.java Modified: trunk/Hibernate3/src/org/hibernate/dialect/DB2Dialect.java Log: HHH-1949 : ANSI SQL trim() function on DB2 and friends Modified: trunk/Hibernate3/src/org/hibernate/dialect/DB2Dialect.java =================================================================== --- trunk/Hibernate3/src/org/hibernate/dialect/DB2Dialect.java 2006-08-11 18:50:44 UTC (rev 10248) +++ trunk/Hibernate3/src/org/hibernate/dialect/DB2Dialect.java 2006-08-11 19:37:17 UTC (rev 10249) @@ -12,6 +12,7 @@ import org.hibernate.dialect.function.SQLFunctionTemplate; import org.hibernate.dialect.function.StandardSQLFunction; import org.hibernate.dialect.function.VarArgsSQLFunction; +import org.hibernate.dialect.function.AnsiTrimEmulationFunction; /** * An SQL dialect for DB2. @@ -115,9 +116,9 @@ registerFunction( "posstr", new StandardSQLFunction( "posstr", Hibernate.INTEGER ) ); registerFunction( "substring", new StandardSQLFunction( "substr", Hibernate.STRING ) ); - registerFunction( "trim", new SQLFunctionTemplate( Hibernate.INTEGER, "ltrim(rtrim(?1))" ) ); registerFunction( "bit_length", new SQLFunctionTemplate( Hibernate.INTEGER, "length(?1)*8" ) ); - + registerFunction( "trim", new AnsiTrimEmulationFunction() ); + registerFunction( "concat", new VarArgsSQLFunction(Hibernate.STRING, "", "||", "") ); registerFunction( "str", new SQLFunctionTemplate( Hibernate.STRING, "rtrim(char(?1))" ) ); @@ -130,14 +131,14 @@ registerKeyword("first"); registerKeyword("rows"); registerKeyword("only"); - + getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE, NO_BATCH); } public String getLowercaseFunction() { return "lcase"; } - + public String getAddColumnString() { return "add column"; } @@ -198,7 +199,7 @@ } return pagingSelect.toString(); }*/ - + /** * Render the <tt>rownumber() over ( .... ) as rownumber_,</tt> * bit, that goes in the select list @@ -208,25 +209,25 @@ .append("rownumber() over("); int orderByIndex = sql.toLowerCase().indexOf("order by"); - + if ( orderByIndex>0 && !hasDistinct(sql) ) { rownumber.append( sql.substring(orderByIndex) ); } - + rownumber.append(") as rownumber_,"); - + return rownumber.toString(); } public String getLimitString(String sql, boolean hasOffset) { - + int startOfSelect = sql.toLowerCase().indexOf("select"); - + StringBuffer pagingSelect = new StringBuffer( 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 list - + if ( hasDistinct(sql) ) { pagingSelect.append(" row_.* from ( ") //add another (inner) nested select .append( sql.substring(startOfSelect) ) //add the main query @@ -235,9 +236,9 @@ else { pagingSelect.append( sql.substring( startOfSelect + 6 ) ); //add the main query } - + pagingSelect.append(" ) as temp_ where rownumber_ "); - + //add the restriction to the outer select if (hasOffset) { pagingSelect.append("between ?+1 and ?"); @@ -245,14 +246,14 @@ else { pagingSelect.append("<= ?"); } - + return pagingSelect.toString(); } private static boolean hasDistinct(String sql) { return sql.toLowerCase().indexOf("select distinct")>=0; } - + public String getForUpdateString() { return " for read only with rs"; } @@ -260,11 +261,11 @@ public boolean useMaxForLimit() { return true; } - + public boolean supportsOuterJoinForUpdate() { return false; } - + public boolean supportsNotNullUnique() { return false; } @@ -290,7 +291,7 @@ } return "nullif(" + literal + ',' + literal + ')'; } - + public static void main(String[] args) { System.out.println( new DB2Dialect().getLimitString("/*foo*/ select * from foos", true) ); System.out.println( new DB2Dialect().getLimitString("/*foo*/ select distinct * from foos", true) ); @@ -301,18 +302,18 @@ public boolean supportsUnionAll() { return true; } - + public int registerResultSetOutParameter(CallableStatement statement, int col) throws SQLException { return col; } - + public ResultSet getResultSet(CallableStatement ps) throws SQLException { - boolean isResultSet = ps.execute(); + boolean isResultSet = ps.execute(); // This assumes you will want to ignore any update counts - while (!isResultSet && ps.getUpdateCount() != -1) { - isResultSet = ps.getMoreResults(); - } - ResultSet rs = ps.getResultSet(); + while (!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 closed return rs; @@ -321,7 +322,7 @@ public boolean supportsCommentOn() { return true; } - + public boolean supportsTemporaryTables() { return true; } Added: trunk/Hibernate3/src/org/hibernate/dialect/function/AnsiTrimEmulationFunction.java =================================================================== --- trunk/Hibernate3/src/org/hibernate/dialect/function/AnsiTrimEmulationFunction.java 2006-08-11 18:50:44 UTC (rev 10248) +++ trunk/Hibernate3/src/org/hibernate/dialect/function/AnsiTrimEmulationFunction.java 2006-08-11 19:37:17 UTC (rev 10249) @@ -0,0 +1,146 @@ +package org.hibernate.dialect.function; + +import org.hibernate.Hibernate; +import org.hibernate.QueryException; +import org.hibernate.engine.Mapping; +import org.hibernate.engine.SessionFactoryImplementor; +import org.hibernate.type.Type; + +import java.util.List; +import java.util.ArrayList; + +/** + * A {@link SQLFunction} implementation that emulates the ANSI SQL trim function + * on dialects which do not support the full definition. However, this function + * definition does assume the availability of ltrim, rtrim, and replace functions + * which it uses in various combinations to emulate the desired ANSI trim() + * functionality. + * + * @author Steve Ebersole + */ +public class AnsiTrimEmulationFunction implements SQLFunction { + + private static final SQLFunction LEADING_SPACE_TRIM = new SQLFunctionTemplate( Hibernate.STRING, "ltrim( ?1 )"); + private static final SQLFunction TRAILING_SPACE_TRIM = new SQLFunctionTemplate( Hibernate.STRING, "rtrim( ?1 )"); + private static final SQLFunction BOTH_SPACE_TRIM = new SQLFunctionTemplate( Hibernate.STRING, "ltrim( rtrim( ?1 ) )"); + private static final SQLFunction BOTH_SPACE_TRIM_FROM = new SQLFunctionTemplate( Hibernate.STRING, "ltrim( rtrim( ?2 ) )"); + + private static final SQLFunction LEADING_TRIM = new SQLFunctionTemplate( Hibernate.STRING, "replace( replace( rtrim( replace( replace( ?1, ' ', '${space}$' ), ?2, ' ' ) ), ' ', ?2 ), '${space}$', ' ' )" ); + private static final SQLFunction TRAILING_TRIM = new SQLFunctionTemplate( Hibernate.STRING, "replace( replace( ltrim( replace( replace( ?1, ' ', '${space}$' ), ?2, ' ' ) ), ' ', ?2 ), '${space}$', ' ' )" ); + private static final SQLFunction BOTH_TRIM = new SQLFunctionTemplate( Hibernate.STRING, "replace( replace( ltrim( rtrim( replace( replace( ?1, ' ', '${space}$' ), ?2, ' ' ) ) ), ' ', ?2 ), '${space}$', ' ' )" ); + + public Type getReturnType(Type columnType, Mapping mapping) throws QueryException { + return Hibernate.STRING; + } + + public boolean hasArguments() { + return true; + } + + public boolean hasParenthesesIfNoArguments() { + return false; + } + + public String render(List args, SessionFactoryImplementor factory) throws QueryException { + // according to both the ANSI-SQL and EJB3 specs, trim can either take + // exactly one parameter or a variable number of parameters between 1 and 4. + // from the SQL spec: + // + // <trim function> ::= + // TRIM <left paren> <trim operands> <right paren> + // + // <trim operands> ::= + // [ [ <trim specification> ] [ <trim character> ] FROM ] <trim source> + // + // <trim specification> ::= + // LEADING + // | TRAILING + // | BOTH + // + // If only <trim specification> is omitted, BOTH is assumed; + // if <trim character> is omitted, space is assumed + if ( args.size() == 1 ) { + // we have the form: trim(trimSource) + // so we trim leading and trailing spaces + return BOTH_SPACE_TRIM.render( args, factory ); + } + else if ( "from".equalsIgnoreCase( ( String ) args.get( 0 ) ) ) { + // we have the form: trim(from trimSource). + // This is functionally equivalent to trim(trimSource) + return BOTH_SPACE_TRIM_FROM.render( args, factory ); + } + else { + // otherwise, a trim-specification and/or a trim-character + // have been specified; we need to decide which options + // are present and "do the right thing" + boolean leading = true; // should leading trim-characters be trimmed? + boolean trailing = true; // should trailing trim-characters be trimmed? + String trimCharacter = null; // the trim-character + String trimSource = null; // the trim-source + + // potentialTrimCharacterArgIndex = 1 assumes that a + // trim-specification has been specified. we handle the + // exception to that explicitly + int potentialTrimCharacterArgIndex = 1; + String firstArg = ( String ) args.get( 0 ); + if ( "leading".equalsIgnoreCase( firstArg ) ) { + trailing = false; + } + else if ( "trailing".equalsIgnoreCase( firstArg ) ) { + leading = false; + } + else if ( "both".equalsIgnoreCase( firstArg ) ) { + } + else { + potentialTrimCharacterArgIndex = 0; + } + + String potentialTrimCharacter = ( String ) args.get( potentialTrimCharacterArgIndex ); + if ( "from".equalsIgnoreCase( potentialTrimCharacter ) ) { + trimCharacter = "' '"; + trimSource = ( String ) args.get( potentialTrimCharacterArgIndex + 1 ); + } + else if ( potentialTrimCharacterArgIndex + 1 >= args.size() ) { + trimCharacter = "' '"; + trimSource = potentialTrimCharacter; + } + else { + trimCharacter = potentialTrimCharacter; + if ( "from".equalsIgnoreCase( ( String ) args.get( potentialTrimCharacterArgIndex + 1 ) ) ) { + trimSource = ( String ) args.get( potentialTrimCharacterArgIndex + 2 ); + } + else { + trimSource = ( String ) args.get( potentialTrimCharacterArgIndex + 1 ); + } + } + + List argsToUse = null; + argsToUse = new ArrayList(); + argsToUse.add( trimSource ); + argsToUse.add( trimCharacter ); + + if ( trimCharacter.equals( "' '" ) ) { + if ( leading && trailing ) { + return BOTH_SPACE_TRIM.render( argsToUse, factory ); + } + else if ( leading ) { + return LEADING_SPACE_TRIM.render( argsToUse, factory ); + } + else { + return TRAILING_SPACE_TRIM.render( argsToUse, factory ); + } + } + else { + if ( leading && trailing ) { + return BOTH_TRIM.render( argsToUse, factory ); + } + else if ( leading ) { + return LEADING_TRIM.render( argsToUse, factory ); + } + else { + return TRAILING_TRIM.render( argsToUse, factory ); + } + } + } + } +}