It seems that no current JPA implementation is able to paginate the result using Apache Derby 10.5 OFFSET/FETCH mechanism. So javax.persistence.Query setFirstResult and setMaxResults don’t really translate into proper pagination with “OFFSET/FETCH” TopLink/EclipseLink org.eclipse.persistence.platform.database.DerbyPlatform Hibernate org.hibernate.dialect.DerbyDialect OpenJPA. org.apache.openjpa.jdbc.sql.DerbyDictionary
I really want to talk about JTable, Beans Binding and JPA pagination but I think I need to write about JTable and JPA pagination first. So I will take the Beans binding stuff in another post. By the way, choose wisely your JPA Provider/DB Provider combination, as some combinations will not give you any real paginations at all. For example, neither OpenJPA, Hibernate or TopLink/EclipseLink seems to support Apache Derby pagination (OFFSET/FETCH). The example here uses Derby and TopLink which is a bad example because the JPA pagination doesn’t get translated to SQL command for pagination. So if you really want proper pagination you should use other combination like Hibernate JPA/HSQLDB. ...
It’s not easy to get information on derby keys once you created them. I was looking for a command like “SHOW CREATE TABLE ” but no luck. I realized that the answer should lay in SYS tables. After googling a while I found the following bit of wisdom: The following query will give you the UNIQUE constraints on a table: select c.constraintname, c.constraintid from sys.systables t, sys.sysconstraints c where t.tablename = ‘FOO’ and t.tableid = c.tableid and c.type = ‘U’; ...
I just realized that to be able to open/share the same Derby database from two different web applications running in the same Tomcat instance (same JVM) you’ll need to put derby.jar in the $TOMCAT_HOME/common/lib and remove it from your applications WEB-INF/lib. I got the clue from this RIFE web page the jarfiles you need are derby.jar and derbytools.jar . Due to classloader peculiarities, don’t copy them to your application’s web/WEB-INF/lib/ subdirectory, or to Tomcat’s shared/lib/ directory. Tomcat’s common/lib/ directory works, and probably common/endorsed/ does too. ...