Monday, January 8, 2018

Hibernate exception using match/against sql

*UPDATE*
I finally found the real reason and is quite the facepalm. The keyword "MATCH" is MySql specific. It is not part of the more general Sql syntax. Oops.
----

Apologies that this is going to be a fairly brief post, maybe someday I'll have time to come back and flesh out the details a bit more. But I think this is better than me forgetting to post at all and having this lost in my notes. Hope this helps someone else out.

The problem I've been struggling with is using the "MATCH" sql keyword in hibernate. Specifically, it is failing in my unit test.Below I list the 2 configurations that did not work (A & B) and the full stacktrace. The configuration that does work is to use the mysql driver.

tl;dr: Use MySQL for the database

Code:
final String queryString = "SELECT * FROM Course WHERE MATCH (name) AGAINST ('complex' IN BOOLEAN MODE)";
final Session session = sessionFactory.openSession();
session.beginTransaction();
NativeQuery query = session.createNativeQuery(queryString, Course.class);
List results = query.getResultList();
session.getTransaction().commit();
session.close();

Configuration A:
connection.driver_class = org.h2.Driver
connection.url = dbc:h2:mem:db1;DB_CLOSE_DELAY=-1;MVCC=TRUE
connection.username = root
connection.password = "" 
dialect = org.hibernate.dialect.H2Dialect
hbm2ddl.auto = create

Exception A:
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not prepare statement
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:147)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1423)
at org.hibernate.query.Query.getResultList(Query.java:146)
at org.catalyst.courses.entities.HibernateTest.searchCoursesQuery(HibernateTest.java:207)
at org.catalyst.courses.entities.CourseTest.testNamedQueries(CourseTest.java:444)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.hibernate.exception.GenericJDBCException: could not prepare statement
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1940)
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.SessionImpl.listCustomQuery(SessionImpl.java:2160)
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)
... 28 more
Caused by: org.h2.jdbc.JdbcSQLException: Function "MATCH" not found; SQL statement:
SELECT * FROM Course WHERE MATCH (name) AGAINST ('complex' IN BOOLEAN MODE) [90022-194]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:179)
at org.h2.message.DbException.get(DbException.java:155)
at org.h2.command.Parser.readJavaFunction(Parser.java:2471)
at org.h2.command.Parser.readFunction(Parser.java:2523)
at org.h2.command.Parser.readTerm(Parser.java:2860)
at org.h2.command.Parser.readFactor(Parser.java:2374)
at org.h2.command.Parser.readSum(Parser.java:2361)
at org.h2.command.Parser.readConcat(Parser.java:2331)
at org.h2.command.Parser.readCondition(Parser.java:2166)
at org.h2.command.Parser.readAnd(Parser.java:2138)
at org.h2.command.Parser.readExpression(Parser.java:2130)
at org.h2.command.Parser.parseSelectSimple(Parser.java:2087)
at org.h2.command.Parser.parseSelectSub(Parser.java:1928)
at org.h2.command.Parser.parseSelectUnion(Parser.java:1746)
at org.h2.command.Parser.parseSelect(Parser.java:1734)
at org.h2.command.Parser.parsePrepared(Parser.java:447)
at org.h2.command.Parser.parse(Parser.java:319)
at org.h2.command.Parser.parse(Parser.java:291)
at org.h2.command.Parser.prepareCommand(Parser.java:256)
at org.h2.engine.Session.prepareLocal(Session.java:564)
at org.h2.engine.Session.prepareCommand(Session.java:505)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1204)
at org.h2.jdbc.JdbcPreparedStatement.(JdbcPreparedStatement.java:73)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:288)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172)

... 43 more

Root cause A:
MATCH() function is NOT supported by H2 (http://h2-database.66688.n3.nabble.com/MySQL-Compatibility-Mode-Fulltext-MATCH-function-td4033152.html)

Configuration B:

connection.driver_class = org.hsqldb.jdbcDriver
connection.url = jdbc:hsqldb:mem:testdb
connection.username = root
connection.password = "" 
dialect = org.hibernate.dialect.HSQLDialect
hbm2ddl.auto = create

Exception B:
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not prepare statement
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:147)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1423)
at org.hibernate.query.Query.getResultList(Query.java:146)
at org.catalyst.courses.entities.HibernateTest.searchCoursesQuery(HibernateTest.java:207)
at org.catalyst.courses.entities.CourseTest.testNamedQueries(CourseTest.java:444)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1940)
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.SessionImpl.listCustomQuery(SessionImpl.java:2160)
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)
... 28 more
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: MATCH in statement [SELECT * FROM Course WHERE MATCH (name) AGAINST ('complex' IN BOOLEAN MODE)]
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172)
... 43 more
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: MATCH
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.ParserDQL.readColumnOrFunctionExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadSimpleValueExpressionPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanPrimaryOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanTestOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanFactorOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanTermOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanValueExpression(Unknown Source)
at org.hsqldb.ParserDQL.readWhereGroupHaving(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
at org.hsqldb.StatementManager.compile(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)

... 47 more

Root Cause B:
:: shrug :: No clue. I've gone cross-eyed trying to figure this out, this should be supported by HSQL. The documentation says it's supported, but I can't figure out what the heck. The only thing I can think is maybe I have the magic SQL incantation incorrect. I didn't want to futz with the query string too much because ultimately the underlying database will be a MySQL database.

No comments:

Post a Comment