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 > ####################################################################### > ##############
