原生SQL分页查询报错：unexpected token: ON 求助

问题场景 #

使用Spring Data JPA实现原生SQL分页查询时出现报错，代码如下：

@Repository
public interface Aaaa extends PagingAndSortingRepository<TxnDealerInventoryItem, Long> {

    @Query(value = "SELECT EM.PART_NO, EM.PART_NAME FROM TXN_DEALER_INVENTORY_ITEM E INNER JOIN MST_PRODUCT EM ON E.PRODUCT_ID = EM.PRODUCT_ID WHERE E.ACCOUNT_ID= :accountId AND EM.ALLOW_SERIAL_NUM = :isSerialized ORDER BY ?#{#pageable}",
            countQuery = "SELECT COUNT(*) FROM TXN_DEALER_INVENTORY_ITEM E INNER JOIN MST_PRODUCT EM ON E.PRODUCT_ID = EM.PRODUCT_ID WHERE E.ACCOUNT_ID= :accountId AND EM.ALLOW_SERIAL_NUM = :isSerialized",
            nativeQuery = true)
    Page<Object[]> getNonSerializedDeviceList(@Param("accountId") Long accountId, @Param("isSerialized") String isSerialized, Pageable pageable);
}

报错信息 #

1. 执行时Hibernate解析countQuery报错：

line 1:76: unexpected token: ON

2. 简化countQuery为基础语句后，报错：

org.hibernate.hql.internal.ast.QuerySyntaxException: TXN_DEALER_INVENTORY_ITEM is not mapped [SELECT COUNT(*) FROM TXN_DEALER_INVENTORY_ITEM E ]

使用框架版本：

• Spring 4.3.30.RELEASE
• Spring Data JPA 1.11.23.RELEASE
• Hibernate 4.2.18.Final

报错原因 #

在Spring Data JPA 1.11.x版本中，@Query注解的nativeQuery=true仅对value属性生效，countQuery会默认被当作HQL解析，而非原生SQL：

• HQL不支持SQL中ON关键字的显式连接语法，因此触发第一个报错；
• HQL要求使用实体类名而非数据库表名，因此提示表未映射，触发第二个报错。

解决方案 #

方案1：将countQuery改为HQL格式 #

基于实体类关联关系编写HQL的count查询，替换原有的原生SQL countQuery：

@Repository
public interface Aaaa extends PagingAndSortingRepository<TxnDealerInventoryItem, Long> {

    @Query(value = "SELECT EM.PART_NO, EM.PART_NAME FROM TXN_DEALER_INVENTORY_ITEM E INNER JOIN MST_PRODUCT EM ON E.PRODUCT_ID = EM.PRODUCT_ID WHERE E.ACCOUNT_ID= :accountId AND EM.ALLOW_SERIAL_NUM = :isSerialized ORDER BY ?#{#pageable}",
            countQuery = "SELECT COUNT(e) FROM TxnDealerInventoryItem e JOIN e.product em WHERE e.accountId = :accountId AND em.allowSerialNum = :isSerialized",
            nativeQuery = true)
    Page<Object[]> getNonSerializedDeviceList(@Param("accountId") Long accountId, @Param("isSerialized") String isSerialized, Pageable pageable);
}

说明：

• TxnDealerInventoryItem是实体类名，对应数据库表TXN_DEALER_INVENTORY_ITEM；
• e.product是实体类中定义的关联属性，对应数据库中PRODUCT_ID的外键关联；
• accountId、allowSerialNum是实体类的属性名，而非数据库字段名。

方案2：手动实现分页逻辑 #

在Service层分别调用列表查询和count查询，手动构造Page对象：

1. 在Repository中新增count查询方法：

@Repository
public interface Aaaa extends PagingAndSortingRepository<TxnDealerInventoryItem, Long> {

    @Query(value = "SELECT EM.PART_NO, EM.PART_NAME FROM TXN_DEALER_INVENTORY_ITEM E INNER JOIN MST_PRODUCT EM ON E.PRODUCT_ID = EM.PRODUCT_ID WHERE E.ACCOUNT_ID= :accountId AND EM.ALLOW_SERIAL_NUM = :isSerialized ORDER BY ?#{#pageable}",
            nativeQuery = true)
    List<Object[]> getNonSerializedDeviceList(@Param("accountId") Long accountId, @Param("isSerialized") String isSerialized, Pageable pageable);

    @Query(value = "SELECT COUNT(*) FROM TXN_DEALER_INVENTORY_ITEM E INNER JOIN MST_PRODUCT EM ON E.PRODUCT_ID = EM.PRODUCT_ID WHERE E.ACCOUNT_ID= :accountId AND EM.ALLOW_SERIAL_NUM = :isSerialized",
            nativeQuery = true)
    Long getNonSerializedDeviceCount(@Param("accountId") Long accountId, @Param("isSerialized") String isSerialized);
}

2. 在Service层构造Page对象：

@Service
public class YourService {
    @Autowired
    private Aaaa aaaa;

    public Page<Object[]> getNonSerializedDeviceList(Long accountId, String isSerialized, Pageable pageable) {
        List<Object[]> list = aaaa.getNonSerializedDeviceList(accountId, isSerialized, pageable);
        Long count = aaaa.getNonSerializedDeviceCount(accountId, isSerialized);
        return new PageImpl<>(list, pageable, count);
    }
}

方案3：嵌套子查询强制countQuery为原生SQL #

将原countQuery嵌套为子查询，部分版本的Spring Data会强制将其作为原生SQL解析（兼容性需测试，优先推荐方案1或2）：

@Repository
public interface Aaaa extends PagingAndSortingRepository<TxnDealerInventoryItem, Long> {

    @Query(value = "SELECT EM.PART_NO, EM.PART_NAME FROM TXN_DEALER_INVENTORY_ITEM E INNER JOIN MST_PRODUCT EM ON E.PRODUCT_ID = EM.PRODUCT_ID WHERE E.ACCOUNT_ID= :accountId AND EM.ALLOW_SERIAL_NUM = :isSerialized ORDER BY ?#{#pageable}",
            countQuery = "SELECT COUNT(*) FROM (SELECT EM.PART_NO FROM TXN_DEALER_INVENTORY_ITEM E INNER JOIN MST_PRODUCT EM ON E.PRODUCT_ID = EM.PRODUCT_ID WHERE E.ACCOUNT_ID= :accountId AND EM.ALLOW_SERIAL_NUM = :isSerialized) AS temp",
            nativeQuery = true)
    Page<Object[]> getNonSerializedDeviceList(@Param("accountId") Long accountId, @Param("isSerialized") String isSerialized, Pageable pageable);
}

内容的提问来源于stack exchange，提问作者Kandy