Rainer, Thanks for this info. This works well for Oracle. I would like to document this on the wiki for future reference until a proper limit/offset implementation can be implemented.
---Jaco -----Original Message----- From: Rainer Döbele [mailto:[email protected]] Sent: Tuesday, January 19, 2010 10:27 PM To: [email protected] Subject: re: Rowlimits: OFFSET and LIMIT Hi, If you have not found out already, the simplest way to implement the row number expression is: DBColumnExpr ROW_NUM = new DBFuncExpr(EMP.PHONE_NUMBER, "ROW_NUMBER() OVER (ORDER BY ?)", null, null, false, DataType.INTEGER); cmd.select(ROW_NUM); cmd.where(ROW_NUM.isBetween(51, 100)); You may also use that in a subquery, by wrapping it with a DBQuery and then obtaining the Row Numer by using findQueryColumn(...) DBQuery q = new DBQuery(cmd); DBColumnExpr Q_RN = q.findQueryColumn(ROW_NUM); Since not all databases support ROW_NUM(), I am not quite sure how to best integtate it. When providing a feature people expect it to work for all databases and they are dissappointed when the the a runtime exeption just because their database does not. The other option is to implement it only for a particular database (as e.g. with Oracle's connect by prior in DBCommandOracle) but then people may not find it and it's inconvient since it requires a type cast. The same applies to Limit and Offset. But thanks everyone for your links, they are very helpful. Regards Rainer Jaco van Tonder wrote: > RE: Rowlimits: OFFSET and LIMIT > > I too, am a bit cautious of using addSql() (SQL injection comes to > mind). :P > > McKinley: Here is what I found. Seems like Oracle and MSSQL is pretty > close to each other with regards to the window functions (OVER...). > http://www.troels.arvin.dk/db/rdbms/#select-limit-offset > > I just had a look at the hibernate dialect for Oracle and it seems that > they are implementing the LIMIT and OFFSET using the Window Function > method. > http://viewvc.jboss.org/cgi- > bin/viewvc.cgi/hibernate/core/trunk/core/src/main/java/org/hibernate/di > alect/Oracle9Dialect.java?view=markup > Look at the getLimitString() method. > > MSSQL uses the TOP syntax: > http://viewvc.jboss.org/cgi- > bin/viewvc.cgi/hibernate/core/trunk/core/src/main/java/org/hibernate/di > alect/SQLServerDialect.java?view=markup > > PostgreSQL/MySQL uses the LIMIT OFFSET method: > http://viewvc.jboss.org/cgi- > bin/viewvc.cgi/hibernate/core/trunk/core/src/main/java/org/hibernate/di > alect/PostgreSQLDialect.java?view=markup > > Wondering how difficult it would be to "port" that functionality? > > ---Jaco > > > -----Original Message----- > From: McKinley [mailto:[email protected]] > Sent: Tuesday, January 19, 2010 5:06 PM > To: [email protected] > Subject: Re: Rowlimits: OFFSET and LIMIT > > I am afraid that an addSql method would quickly turn into an > addSqlInjection method if you catch my meaning. All the pieces are > there already to support ROW_NUMBER OVER (ORDER BY ...). They just > need to be abstracted from their current use as necessary and > integrated into the function support. > > In fact, I have to make use of ROW_NUMBER OVER (ORDER BY ...) in SQL > Server 2005 for an upcoming project. I'll implement it for Oracle too > if someone can show me the necessary Oracle documentation. > > I'll create it as a subclass of DBFuncExpr with an internal > ArrayList<DBOrderByInfo> with all the usual orderBy and clearOrderBy > methods. The orderBy will be housed in some sort of "over" object. > "over" will also house the partitionBy and clearPartitionBy methods. I > will probably *not* throw an error upon SQL generation if the > partitionBy contains a column not in the FROM clause unless anyone can > tell me that it is not safe. Your DBMS will catch the error. Also, I > will not do any checks on orderBy for the same if you combine this > with a ranking function. Again, the DBMS will catch the error and it > is within your control to conditionally modify the ROW_NUMBER function > partionBy and orderBy and the FROM to make sure your query will > actually run. > > I'll submit the patch to the issue track on Saturday and post a link > to this thread. > > Thanks, > > McKinley > > On Tue, Jan 19, 2010 at 2:36 PM, Vladimir Tsanev <[email protected]> > wrote: > > Another approach to 2 might be to extend DBExpr - and implement > addSql > > method. > > But I do not know what should be the implementation of > addReferencedColumns > > (the other abstract method). > ####################################################################### > ############## > Attention: > The information contained in this message and or attachments is > intended > only for the person or entity to which it is addressed and may contain > confidential and/or privileged material. Any review, retransmission, > dissemination or other use of, or taking of any action in reliance > upon, > this information by persons or entities other than the intended > recipient > is prohibited. If you received this in error, please contact the sender > and > delete the material from any system and destroy any copies. > > Thank You. > ####################################################################### > ############## > > ####################################################################### > ############## > This e-mail message has been scanned for Viruses and Content and > cleared > by MailMarshal > ####################################################################### > ############## ##################################################################################### Attention: The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. Thank You. ##################################################################################### ##################################################################################### This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal #####################################################################################
