如何在JPQL查询中执行具有多个参数的SQL函数?

19-02-24 banq
         

当试图在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());
    }
}

源代码可以在这里找到。