I’m trying to implement a custom filter on a JPAContainer. Right now I’m still using version 1.x of the container, but I can switch to 2.x if necessary. It appears that the filtering is done at the database layer, so I was able to make a simple filter work just by using a % character as a wild card. But I would like to be able to filter using more powerful regular expressions - I was hoping to use the java Regex/Pattern classes so I could filter something like "aaa.*bbb.[0-9]
" or something like that. So my question is - is that really possible with the JPAContainer or not? If so, what do I need to do to make it work? Any sample code lying around that someone could point me at?
As far as I know, not really no. JPAContainer builds queries using the Criteria API, but neither the Criteria API nor JPQL support regular expressions.
Notice that filtering in JPAContainer is not done in Java code but as a “WHERE” expression in the JPQL query, which is translated into an SQL query by the JPA implementation and database driver. Many SQL databases do support regexp extensions to SQL WHERE expressions. To make such queries, you would need to use a native query with createNativeQuerty() in EntityManager. But that is not supported in JPAContainer.
Well, it’s actually a matter of the entity provider. Extending and customizing the built-in providers to use native queries would probably be somewhat hard because of the extensive use of Criteria API in them.
Actually, JPAContainer 1.0 did not use Criteria API (which is JPA 2.0 stuff) but made JPQL queries. Making native queries instead would probably have been much easier with that.