当试图在JPQL查询使用SQL函数时，如果Hibernate无法识别就不能分析这个JPQL语句，那么就导致exceptions抛错。例如，Hibernate无法识别MySQL的concat_ws函数。
这个应用程序是一个基于Hibernate 5.3的Spring Boot应用程序，它通过通过MetadataBuilderContributor注册concat_ws函数 ，通过metadata_builder_contributor属性通知Hibernate。这里既使用@Query又使用了EntityManager，你可以看到两个用例。
关键点：
1. 使用Hibernate 5.3（或者，准确地说，5.2.18）（例如，使用Spring Boot 2.1.0.RELEASE）
2. 实现MetadataBuilderContributor并注册concat_wsMySQL功能
public class SqlFunctionsMetadataBuilderContributor
implements MetadataBuilderContributor {
@Override
public void contribute(
MetadataBuilder metadataBuilder) {
metadataBuilder.applySqlFunction(
"concat_ws",
new SQLFunctionTemplate(
StandardBasicTypes.STRING,
"concat_ws(' ', ?1, ?2, ?3, ?4)"
)
);
}
}
|
3. 在application.properties，设置spring.jpa.properties.hibernate.metadata_builder_contributor指出MetadataBuilderContributor的实施子类：
spring.datasource.url=jdbc:mysql://localhost:3306/db_royalty?createDatabaseIfNotExist=true
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.datasource.initialization-mode=always
spring.datasource.platform=mysql
spring.jpa.properties.hibernate.metadata_builder_contributor=com.jpa.SqlFunctionsMetadataBuilderContributor
|
DAO EntityManager 实现：
@Repository
@Transactional
public class Dao<T, ID extends Serializable> implements GenericDao<T, ID> {
@PersistenceContext
private EntityManager entityManager;
@Override
public String fetchNameAndAmount(String symbol, Instant instant) {
return (String) entityManager.createQuery(
"select concat_ws(r.name, :symbol, r.amount, :instant) from Royalty r WHERE r.id = 1"
)
.setParameter("symbol", symbol)
.setParameter("instant", instant)
.getSingleResult();
}
}
|
@Query实现：
@Repository
public interface RoyaltyRepository extends JpaRepository<Royalty, Long> {
@Query(value = "select concat_ws(r.name, ?1, r.amount, ?2) from Royalty r WHERE r.id = 1")
String fetchNameAndAmount(String symbol, Instant instant);
}
|
调用：
@Service
public class RoyaltyService {
private final Dao dao;
private final RoyaltyRepository royaltyRepository;
public RoyaltyService(Dao dao, RoyaltyRepository royaltyRepository) {
this.dao = dao;
this.royaltyRepository = royaltyRepository;
}
public String nameAndAmount() {
return royaltyRepository.fetchNameAndAmount("$", Instant.now());
// or, via EntityManager from Dao class
//return dao.fetchNameAndAmount("$", Instant.now());
}
}
|
源代码可以在这里找到。