How to use PreparedStatements with the Persistence Manager?


ebenzacar@...
 
Edited

I have a few pieces of code that use native PreparedStatements, but am having trouble using them in conjunction with the PersistenceManager.  The current approach is:


Connection conn = (Connection)persistenceManager.getDataStoreConnection.getNativeConnection();
PreparedStatement stm = conn.prepareStatement("delete from Athletes where id=?");
stm.setLong(
1, 12345);
stm.execute();
stm.close();
conn.close();


While this works, the problem I have is that when I try to continue to use the PM afterwards, or access any Objects retrieved prior to this snippet of code, I get the following error message:

org.datanucleus.exceptions.NucleusUserException: The Connection was acquired by the developer and must be closed before using the persistence API.
        at org.datanucleus.store.connection.ConnectionManagerImpl.getManagedConnection(ConnectionManagerImpl.java:310)
        at org.datanucleus.store.connection.ConnectionManagerImpl.allocateManagedConnection(ConnectionManagerImpl.java:349)
        at org.datanucleus.store.connection.ConnectionManagerImpl.getConnection(ConnectionManagerImpl.java:213)
        at org.datanucleus.store.connection.ConnectionManager.getConnection(ConnectionManager.java:62)
        at org.datanucleus.store.rdbms.scostore.JoinSetStore.iterator(JoinSetStore.java:888)
        at org.datanucleus.store.types.wrappers.backed.Set.loadFromStore(Set.java:292)
        at org.datanucleus.store.types.wrappers.backed.Set.iterator(Set.java:468)


I have managed to track the issue down to fact that the NativeConnection is being closed instead of the DataStoreConnection being closed.

Does JDO spec identify/specify the requirements/contract for using PreparedStatements?  Or is this part of the DN implementation only?  Are there any examples that I can review to help identify best practices for using PreparedStatements within DataNucleus (I couldn't find anything in the datanucleus/samples-jdo repo)?  I did find a snippet of code on the DN Persistence pages but I'm not sure if I need to close the native connection as well or not.

If, instead of using the PM.getDataSource().getNativeConnection(), I were to use the PMF to get a brand new PM and new NativeConnection, if I only close the native connection (ie: nativeConnection.close()), will that cause a memory leak?  Am I always required to close the JDOConnection as well?

Thanks,

Eric


Andy
 
Edited

The JDO spec is public, as is the JDO TCK which will have tests. Also this test.


ebenzacar@...
 

Thanks for the link.  I had tried a github search for PreparedStatement on the samples repo, but hadn't thought of searching for tests.  I had not realized that the JDOConnection could simply be cast to a Connection; that resolves my issue cleanly.  

Thanks,

Eric