I have a Spring/Hibernate application that sets an encryption key in to the transaction session as a local parameter using a CustomTransactionManager
package com.trial.tical.configuration;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Primary;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.stereotype.Component;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.support.DefaultTransactionStatus;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.PersistenceContext;
@Component
@Primary
@Qualifier(value = "transactionManager")
public class CustomTransactionManager extends JpaTransactionManager {
@PersistenceContext
private EntityManager entityManager;
@Value("${encrypt.key}")
private String encryptKey;
@Override
protected void prepareSynchronization(DefaultTransactionStatus status, TransactionDefinition definition) {
super.prepareSynchronization(status, definition);
if (status.isNewTransaction()) {
final String query = "SET LOCAL encrypt.key=" + encryptKey;
entityManager.createNativeQuery(query).executeUpdate();
}
}
public void setEntityManager(EntityManager em) {
final String query = "SET encrypt.key=" + encryptKey;
em.createNativeQuery(query).executeUpdate();
}
public CustomTransactionManager() {
super();
// TODO Auto-generated constructor stub
}
public CustomTransactionManager(EntityManagerFactory emf) {
super(emf);
// TODO Auto-generated constructor stub
}
}
This works successfully across the application except for when using the FullTextEntityManager class (required for implementing a fuzzy search of Person title/name etc).
@PersistenceContext
private EntityManager entityManager;
@Autowired
private EntityManagerFactory emf;
@Transactional
public List<PersonDetail> fuzzySearch(String title, String initials, String forename, String middlename, String surname) throws InterruptedException {
org.hibernate.search.jpa.Search.getFullTextEntityManager(entityManager);
fullTextEntityManager.createIndexer().startAndWait();
final QueryBuilder b = fullTextEntityManager.getSearchFactory()
.buildQueryBuilder().forEntity( PersonDetail.class ).get();
BooleanQuery.Builder boolQuery = new BooleanQuery.Builder();
if(!"".equals(title)) {
System.err.println("in title");
boolQuery.add(b.keyword().onField("title").matching(title.toLowerCase()).createQuery(),Occur.MUST);
}
etc.
org.apache.lucene.search.Query luceneQuery = boolQuery.build();
org.hibernate.search.jpa.FullTextQuery jpaQuery
= fullTextEntityManager.createFullTextQuery(luceneQuery, PersonDetail.class);
List<PersonDetail> result = jpaQuery.getResultList(); //return a list of managed objects
return result;
}
The PersonDetail Entity is as follows
package com.trial.tical.model;
import java.io.Serializable;
import javax.persistence.*;
import org.apache.lucene.analysis.core.WhitespaceAnalyzer;
import org.hibernate.annotations.ColumnTransformer;
import org.hibernate.annotations.Immutable;
import org.hibernate.search.annotations.Analyze;
import org.hibernate.search.annotations.Analyzer;
import org.hibernate.search.annotations.Field;
import org.hibernate.search.annotations.Fields;
import org.hibernate.search.annotations.Indexed;
import org.hibernate.annotations.Cache;
import org.hibernate.annotations.CacheConcurrencyStrategy;
import java.sql.Timestamp;
@Entity
@Immutable
@Table(name="person_details")
@NamedQueries({
@NamedQuery(name="PersonDetail.findAll", query="SELECT p FROM PersonDetail p")})
@Cacheable
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
@Indexed
public class PersonDetail implements Serializable {
private static final long serialVersionUID = 1L;
@Field
private String title;
@ColumnTransformer(read = "pgp_sym_decrypt(initials::bytea,current_setting('encrypt.key'))")
@Column(name="initials")
@Field
private String initials;
@ColumnTransformer(read = "pgp_sym_decrypt(forename::bytea,current_setting('encrypt.key'))")
@Field
private String forename;
@ColumnTransformer(read = "pgp_sym_decrypt(middlename::bytea,current_setting('encrypt.key'))")
@Field
private String middlename;
etc.
This is the error
2024-09-04 13:05:41.375 WARN 23604 --- [ entityloader-4] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42704
2024-09-04 13:05:41.375 ERROR 23604 --- [ entityloader-4] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: unrecognized configuration parameter "encrypt.key"
2024-09-04 13:05:41.381 ERROR 23604 --- [ entityloader-4] o.h.s.exception.impl.LogErrorHandler : HSEARCH000058: HSEARCH000212: An exception occurred while the MassIndexer was transforming identifiers to Lucene Documents
org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.loader.Loader.getResultSet(Loader.java:2304) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2057) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2019) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.loader.Loader.doQuery(Loader.java:948) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2850) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2832) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2664) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.loader.Loader.list(Loader.java:2659) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:109) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1877) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:370) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.search.batchindexing.impl.IdentifierConsumerDocumentProducer.loadList(IdentifierConsumerDocumentProducer.java:174) ~[hibernate-search-orm-5.11.7.Final.jar:5.11.7.Final]
at org.hibernate.search.batchindexing.impl.IdentifierConsumerDocumentProducer.loadAllFromQueue(IdentifierConsumerDocumentProducer.java:140) ~[hibernate-search-orm-5.11.7.Final.jar:5.11.7.Final]
at org.hibernate.search.batchindexing.impl.IdentifierConsumerDocumentProducer.run(IdentifierConsumerDocumentProducer.java:120) ~[hibernate-search-orm-5.11.7.Final.jar:5.11.7.Final]
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) ~[na:na]
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[na:na]
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130) ~[na:na]
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630) ~[na:na]
at java.base/java.lang.Thread.run(Thread.java:832) ~[na:na]
Caused by: org.postgresql.util.PSQLException: ERROR: unrecognized configuration parameter "encrypt.key"
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553) ~[postgresql-42.2.18.jar:42.2.18]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285) ~[postgresql-42.2.18.jar:42.2.18]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323) ~[postgresql-42.2.18.jar:42.2.18]
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473) ~[postgresql-42.2.18.jar:42.2.18]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393) ~[postgresql-42.2.18.jar:42.2.18]
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164) ~[postgresql-42.2.18.jar:42.2.18]
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114) ~[postgresql-42.2.18.jar:42.2.18]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-3.4.5.jar:na]
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.5.jar:na]
at jdk.internal.reflect.GeneratedMethodAccessor194.invoke(Unknown Source) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:564) ~[na:na]
at net.ttddyy.dsproxy.proxy.PreparedStatementProxyLogic.invoke(PreparedStatementProxyLogic.java:171) ~[datasource-proxy-1.4.1.jar:na]
at net.ttddyy.dsproxy.proxy.jdk.PreparedStatementInvocationHandler.invoke(PreparedStatementInvocationHandler.java:32) ~[datasource-proxy-1.4.1.jar:na]
at com.sun.proxy.$Proxy238.executeQuery(Unknown Source) ~[na:na]
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
Could anyone explain why the local parameter cannot be found here but is when eg. using a Specification/Criteria Builder on the same entity