java - 在 Hibernate 中使用 MySQL GET_LOCK 函数执行查询

Question

我的应用程序使用 Hibernate 作为 ORM。我正在尝试在我的应用程序中执行一些 mysql 锁定功能，例如 GET_LOCK、IS_FREE_LOCK、RELEASE_LOCK ( https://dev.mysql.com/doc/refman/5.6/en/locking-functions.html )。

但是我遇到了以下问题:

Hibernate: SELECT COALESCE(GET_LOCK(?, ?), 0)
 WARN [org.hibernate.util.JDBCExceptionReporter,main] (JDBCExceptionReporter.java:77) - SQL Error: 90022, SQLState: 90022
ERROR [org.hibernate.util.JDBCExceptionReporter,main] (JDBCExceptionReporter.java:78) - Function "GET_LOCK" not found; SQL statement:
SELECT COALESCE(GET_LOCK(?, ?), 0) [90022-186]
Tests run: 2, Failures: 0, Errors: 1, Skipped: 1, Time elapsed: 5.626 sec <<< FAILURE! - in com.lock.dao.LockDaoTest
testGetLock(com.lock.dao.LockDaoTest)  Time elapsed: 5.397 sec  <<< ERROR!
org.hibernate.exception.GenericJDBCException: could not execute query
    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:2341)
    at org.h2.command.Parser.readFunction(Parser.java:2393)
    at org.h2.command.Parser.readTerm(Parser.java:2727)
    at org.h2.command.Parser.readFactor(Parser.java:2259)
    at org.h2.command.Parser.readSum(Parser.java:2246)
    at org.h2.command.Parser.readConcat(Parser.java:2216)
    at org.h2.command.Parser.readCondition(Parser.java:2066)
    at org.h2.command.Parser.readAnd(Parser.java:2038)
    at org.h2.command.Parser.readExpression(Parser.java:2030)
    at org.h2.command.Parser.readFunction(Parser.java:2543)
    at org.h2.command.Parser.readTerm(Parser.java:2727)
    at org.h2.command.Parser.readFactor(Parser.java:2259)
    at org.h2.command.Parser.readSum(Parser.java:2246)
    at org.h2.command.Parser.readConcat(Parser.java:2216)
    at org.h2.command.Parser.readCondition(Parser.java:2066)
    at org.h2.command.Parser.readAnd(Parser.java:2038)
    at org.h2.command.Parser.readExpression(Parser.java:2030)
    at org.h2.command.Parser.parseSelectSimpleSelectPart(Parser.java:1942)
    at org.h2.command.Parser.parseSelectSimple(Parser.java:1974)
    at org.h2.command.Parser.parseSelectSub(Parser.java:1868)
    at org.h2.command.Parser.parseSelectUnion(Parser.java:1689)
    at org.h2.command.Parser.parseSelect(Parser.java:1677)
    at org.h2.command.Parser.parsePrepared(Parser.java:433)
    at org.h2.command.Parser.parse(Parser.java:305)
    at org.h2.command.Parser.parse(Parser.java:277)
    at org.h2.command.Parser.prepareCommand(Parser.java:242)
    at org.h2.engine.Session.prepareLocal(Session.java:446)
    at org.h2.engine.Session.prepareCommand(Session.java:388)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1189)
    at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:72)
    at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:277)
    at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505)
    at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423)
    at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)
    at org.hibernate.loader.Loader.doQuery(Loader.java:673)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
    at org.hibernate.loader.Loader.doList(Loader.java:2213)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
    at org.hibernate.loader.Loader.list(Loader.java:2099)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
    at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
    at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
    at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
    at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:811)
    at com.distributed.lock.manager.ApplicationLockDao.getLock(ApplicationLockDao.java:41)
    at com.distributed.lock.manager.ApplicationLockDao$$FastClassByCGLIB$$860b7963.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:698)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:631)

这是我的 Dao 代码:

            Query sqlQuery = getSession().createSQLQuery("SELECT COALESCE(GET_LOCK(:lock, :timeout), 0)");
            sqlQuery.setString("lock", lock);
            sqlQuery.setInteger("timeout",1));

            Integer lockAcquired = (Integer) sqlQuery.uniqueResult();

我不确定如何使用 Hibernate 执行锁定功能。任何帮助/指示表示赞赏。

提前致谢。

Answer 1

我发现了问题。我试图使用 H2 数据库进行单元测试，而不是使用 hibernate。引用:https://proj.goldencode.com/issues/2347

删除 H2 依赖并直接使用 mysql 后，我只需更改返回类型即可成功运行测试:

Query sqlQuery = getSession().createSQLQuery("SELECT COALESCE(GET_LOCK(:lock, :timeout), 0)");
            sqlQuery.setString("lock", lock);
            sqlQuery.setInteger("timeout",1));

            BigInteger lockAcquired = (BigInteger) sqlQuery.uniqueResult();