QueryException in annotation importer plug-in

[Nicklas Nordborg]

The annotation importer can fail with:

net.sf.basedb.core.BaseException: org.hibernate.QueryException: Named parameter does not appear in Query: listOfIds_1 [SELECT "id", "value"
                        FROM "IntegerValues"
                        WHERE "id" IN (?)]
        at TestJob.test_execute(TestJob.java:113)
        at TestAnnotationFlatFileImporter.test_all(TestAnnotationFlatFileImporter.java:93)
        at TestAnnotationFlatFileImporter.main(TestAnnotationFlatFileImporter.java:52)
Caused by: net.sf.basedb.core.BaseException: org.hibernate.QueryException: Named parameter does not appear in Query: listOfIds_1 [SELECT "id", "value"
                        FROM "IntegerValues"
                        WHERE "id" IN (?)]
        at net.sf.basedb.core.HibernateUtil.loadList(HibernateUtil.java:2094)
        at net.sf.basedb.core.AnnotationBatcher.setCurrentItem(AnnotationBatcher.java:634)
        at net.sf.basedb.plugins.AnnotationFlatFileImporter$NewAnnotations.setNewAnnotations(AnnotationFlatFileImporter.java:1409)
        at net.sf.basedb.plugins.AnnotationFlatFileImporter.end(AnnotationFlatFileImporter.java:958)
        at net.sf.basedb.plugins.AbstractFlatFileImporter.doImport(AbstractFlatFileImporter.java:739)
        at net.sf.basedb.plugins.AnnotationFlatFileImporter.doImport(AnnotationFlatFileImporter.java:651)
        at net.sf.basedb.plugins.AbstractFlatFileImporter.run(AbstractFlatFileImporter.java:451)
        at net.sf.basedb.core.PluginExecutionRequest.invoke(PluginExecutionRequest.java:117)
        at TestJob.test_execute(TestJob.java:97)
        ... 2 more
Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: Named parameter does not appear in Query: listOfIds_1 [SELECT "id", "value"
                        FROM "IntegerValues"
                        WHERE "id" IN (?)]
        at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:131)
        at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155)
        at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1423)
        at net.sf.basedb.core.HibernateUtil.loadList(HibernateUtil.java:2090)
        ... 10 more
Caused by: org.hibernate.QueryException: Named parameter does not appear in Query: listOfIds_1 [SELECT "id", "value"
                        FROM "IntegerValues"
                        WHERE "id" IN (?)]
        at org.hibernate.loader.custom.CustomLoader.getNamedParameterLocs(CustomLoader.java:463)
        at org.hibernate.loader.Loader.bindNamedParameters(Loader.java:2091)
        at org.hibernate.loader.Loader.bindParameterValues(Loader.java:2022)
        at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1956)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1909)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1887)
        at org.hibernate.loader.Loader.doQuery(Loader.java:932)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
        at org.hibernate.loader.Loader.doList(Loader.java:2615)
        at org.hibernate.loader.Loader.doList(Loader.java:2598)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2430)
        at org.hibernate.loader.Loader.list(Loader.java:2425)
        at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335)
        at org.hibernate.internal.StatelessSessionImpl.listCustomQuery(StatelessSessionImpl.java:574)
        at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:992)
        at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:148)
        at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414)
        ... 11 more

The error will typically happen when trying to update or create multiple annotations on multiple items where some of the annotations only exists on some of the items.

An analysis of the error and code inside Hibernate indicates that the root cause is that Hibernate is generating a query that depends on the number of existing annotations but fails to adjust the number of query parameters. For example:

SELECT "id", "value" FROM "IntegerValues" WHERE "id" IN (?, ?)

if an item has two annotations. If the next sample only has a single annotation a similar query is generated:

SELECT "id", "value" FROM "IntegerValues" WHERE "id" IN (?)

The problem is that the number of parameters are cached the first time the query is used and this causes an exception when the second query is executed since it only has a single parameter instead of two.

Ideally, it would be nice if this could be fixed in Hibernate, but in the meantime we need a way to workaround the issue.

Note! The same problem may appear in other parts of BASE as well. Everywhere we use NativeQuery.setParameterList() more than once on the same query.

[Nicklas Nordborg]

(In [7463]) References #2110: QueryException in annotation importer plug-in

Added a test case in the TestAnnotationFlatFileImporter test.

Running the same import twice will trigger the error so now we have a way to test and check if this problem comes back in the future.

[Nicklas Nordborg]

(In [7464]) References #2110: QueryException in annotation importer plug-in

Added NativeQueryWrapper which is our own wrapper around Hibernate native queries. The intention is that we should be able to intercept calls to setParameterList(...) methods and fix the underlying query parameters. A simple test has already been made using the java reflektion API and it seems to work, but needs to be cleaned up before being committed.

[Nicklas Nordborg]

(In [7465]) References #2110: QueryException in annotation importer plug-in

Implemented a method for removing old "synthetic" parameters that Hibernate generates under the hood (​https://github.com/hibernate/hibernate-orm/blob/5.2/hibernate-core/src/main/java/org/hibernate/query/internal/QueryParameterBindingsImpl.java#L525).

The method uses the Java reflektion API to get access to a private variable inside an internal Hibernate class (QueryParameterBindingsImpl.parameterBindingMap). This solution may stop working if Hibernate is updated. The TestAnnotationFlatFileImporter is expected to fail in that case.