Is switching to BMP my only option here?
you can try JBossQL, declared SQL or just providing your own finder implementation within CMP bean as well, depending what it is that you need to do in SQL
I need this to work on a variety of app servers so I would prefer not to use JBossQL, and it is my understanding that declared SQL has the same limitation.
Can you point me to a reference on implementing a custom finder for a CMP bean, and is this a JBoss thing or do all app servers support it?
All of the above are specific to JBoss.
EJBQL is really quite limited and I don't know how feasible it is to use it to build full scale applications. The only option (in J2EE) besides EJBQL is to revert back to BMP, which is not a very good option.
Quite frankly, I'd rather recommend you use the small additions that are provided by JBOSSQL and then if you actually need to port to another server, worry about converting your beans to BMP or to other app server specific query extensions.
So, back to my original question. What are the implications of hand writing some JDBC which looks up the primary keys and then doing a findByPK with that key? This would only be used for cases where EJBQL does not work.
I can't see any reason why this would be a problem with the container.
Shouldn't be a problem.