search

stazz / java-sql-generator

A framework to generate syntactically correct SQL statements in a typesafe, easy, and uniform way (without StringBuilders).
49 stars 28 forks source link

LIMIT clause support outside of PgSQL interface #11

Closed leejaywu closed 12 years ago

leejaywu commented 12 years ago

Seems odd that LIMIT would only be within the pgsql API, given that it's hardly the only database to support it -- e.g. MySQL has supported both LIMIT and OFFSET for years, and not having LIMIT support can result in some ridiculously inefficient queries.

stazz commented 12 years ago

LIMIT and OFFSET clauses are not supported in SQL standard (at least the one I was working with when creating this framework, on http://savage.net.au/SQL/sql-99.bnf.html , and apparently it is not in 2003 either). Therefore they are not included in basic implementation. However, I will add support for LIMIT & OFFSET for MySQL soon.

eskatos commented 12 years ago

Isn't the SQL:2008 standard keywords FETCH & OFFSET and LIMIT the one that was added by vendors? Plus the LIMIT syntax is not the same across the different databases that support LIMIT.

Supporting FETCH/OFFSET in DefaultVendor and add LIMIT implementations per vendor that support it would be a better bet IMA.

stazz commented 12 years ago

eskatos: Your suggestion sounds very good, I will take a look into 2008 standard and add support for these clauses. :)

stevenschlansker commented 12 years ago

+1 vote on getting this implemented :)

stazz commented 12 years ago

I've pushed the commits for this issue. I will mvn deploy the 0.3-SNAPSHOT in a few days, if no bugs are discovered. The methods for adding FETCH/OFFSET are in SimpleQueryBuilder and QuerySpecificationBuilder.

EDIT: Also notice the legacy switches in MySQLVendor and PostgreSQLVendor. I've added them for convenience.

eskatos commented 12 years ago

Awesome Stan! ;-)

stazz commented 12 years ago

Closing this issue, since I've deployed the snapshot to public maven repo.