Spring Boot中带别名的原生查询分页报错排查

问题背景 #

我有一个名为reporting_general的SQL表，使用包含SQL别名的复杂原生SQL查询，通过JPA Projection映射列，但执行时触发MySQL语法错误，报错信息如下：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REPORTING_GENERAL WHERE REPORTING_GENERAL.ID > 0 AND CHANNEL in ('A', 'C') AND ' at line 1

相关代码如下：

Entity类 #

@Entity
@Table(name = "reporting_general")
@Data
public class ReportingGeneral implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public int id;
    @Column(name = "transaction_name")
    public String transactionName;
    public String username;
    @Column(name = "contact_number")
    public String contactNumber;
    public String segment;
    @Column(name = "user_type")
    public String userType;
    @Column(name = "primary_key")
    public String primaryKey;
    public String channel;
    @Column(name = "response_code")
    public String ResponseCode;
    @Column(name = "request_time")
    public Date requestTime;
    @Column(name = "response_time")
    public Date responseTime;
}

JPA Projection接口 #

public interface ActiveAccountReport {
    String getUserName();
    String getContactNumber();
    String getPrimaryKey();
    String getMinRequestTime();
    String getMaxRequestTime();
    String getSuccess();
    String getFailed();
    String getTotalHits();
    String getChannel();
}

Repository类 #

public interface ReportingGenRepo extends JpaRepository<ReportingGeneral, Integer> {

    @Query(value = "SELECT REPORTING_GENERAL.USERNAME AS userName, " +
            "ANY_VALUE (REPORTING_GENERAL.CONTACT_NUMBER ) AS contactNumber,ANY_VALUE (REPORTING_GENERAL.PRIMARY_KEY) AS primaryKey," +
            "MIN( REQUEST_TIME ) AS minRequestTime ,MAX( REQUEST_TIME ) AS maxRequestTime, " +
            "COUNT(IF ( RESPONSE_CODE = '1', 1, NULL )) AS success,COUNT(IF " +
            "( RESPONSE_CODE != '1', 1, NULL )) AS failed,COUNT(*) AS totalHits,CHANNEL as channel" +
            " FROM REPORTING_GENERAL WHERE " +
            " REPORTING_GENERAL.ID > 0 AND CHANNEL in ?3 AND (REPORTING_GENERAL.REQUEST_TIME  BETWEEN ?1 AND ?2)" +
            "GROUP BY channel, username", nativeQuery = true)
    public Page<ActiveAccountReport> getActiveAccountReportFilters(
            LocalDateTime startDate,
            LocalDateTime endDate,
            List<Character> channel,
            Pageable pagable);
}

Service类 #

@Service
public class ReportingGenService {
    @Autowired
    private ReportingGenRepo reportingGenRepo;

  public Page<ActiveAccountReport> paginatedActiveAccountReports(ActiveAccountRequest activeAccountRequest,
                                                   Integer page,Integer size) {

 Pageable pageable = PageRequest.of(page,size);
 Page<ActiveAccountReport> activeAccountReports  =  reportingGenRepo.getActiveAccountReportFilters(activeAccountRequest.getStartDate(),
                activeAccountRequest.getEndDate(),activeAccountRequest.getChannel(),pageable);
        return activeAccountReports;
    }}

Controller类 #

@RestController
@RequestMapping("/repo")
public class ReportingGenController {
    @Autowired
    private ReportingGenService reportingGenService;

    @GetMapping("/get")
    public Page<ActiveAccountReport> findAll(@RequestBody ActiveAccountRequest activeAccountRequest,
                            @RequestParam("page") Integer page, @RequestParam("size") Integer size){
        return reportingGenService.paginatedActiveAccountReports(activeAccountRequest,page,size);

    }
}

错误原因分析 #

1. JPA原生SQL分页的语法冲突：当使用Page作为返回类型时，Spring Data JPA会自动在原生SQL末尾拼接分页语句（如LIMIT ... OFFSET ...），同时会自动生成count查询用于统计总数。但你的查询包含GROUP BY和ANY_VALUE等聚合函数，JPA自动生成的count查询会出现语法错误，最终导致原查询的语法被破坏。
2. 参数类型不匹配：channel参数定义为List<Character>，但数据库中channel是字符串类型，参数绑定可能引发隐式类型转换问题，进一步加剧语法错误。

解决方法 #

方法1：手动处理分页（推荐） #

拆分数据查询与总数查询，手动组装Page对象，避免JPA自动拼接的语法冲突：

步骤1：修改Repository方法

public interface ReportingGenRepo extends JpaRepository<ReportingGeneral, Integer> {
    // 数据查询：返回List，手动传入分页参数
    @Query(value = "SELECT USERNAME AS userName, " +
            "ANY_VALUE(CONTACT_NUMBER) AS contactNumber, ANY_VALUE(PRIMARY_KEY) AS primaryKey, " +
            "MIN(REQUEST_TIME) AS minRequestTime, MAX(REQUEST_TIME) AS maxRequestTime, " +
            "COUNT(IF(RESPONSE_CODE = '1', 1, NULL)) AS success, " +
            "COUNT(IF(RESPONSE_CODE != '1', 1, NULL)) AS failed, " +
            "COUNT(*) AS totalHits, CHANNEL as channel " +
            "FROM reporting_general WHERE " +
            "ID > 0 AND CHANNEL IN ?3 AND REQUEST_TIME BETWEEN ?1 AND ?2 " +
            "GROUP BY channel, username " +
            "LIMIT ?4 OFFSET ?5", nativeQuery = true)
    List<ActiveAccountReport> getActiveAccountReportData(
            LocalDateTime startDate,
            LocalDateTime endDate,
            List<String> channel,
            int limit,
            int offset);

    // 总数查询：统计符合条件的分组数量
    @Query(value = "SELECT COUNT(DISTINCT CONCAT(channel, '_', username)) FROM reporting_general " +
            "WHERE ID > 0 AND CHANNEL IN ?3 AND REQUEST_TIME BETWEEN ?1 AND ?2", nativeQuery = true)
    long countActiveAccountReportTotal(
            LocalDateTime startDate,
            LocalDateTime endDate,
            List<String> channel);
}

步骤2：修改Service层逻辑

@Service
public class ReportingGenService {
    @Autowired
    private ReportingGenRepo reportingGenRepo;

    public Page<ActiveAccountReport> paginatedActiveAccountReports(ActiveAccountRequest activeAccountRequest, Integer page, Integer size) {
        int offset = page * size;
        // 查询数据列表
        List<ActiveAccountReport> content = reportingGenRepo.getActiveAccountReportData(
                activeAccountRequest.getStartDate(),
                activeAccountRequest.getEndDate(),
                activeAccountRequest.getChannel(),
                size,
                offset);
        // 查询总数
        long total = reportingGenRepo.countActiveAccountReportTotal(
                activeAccountRequest.getStartDate(),
                activeAccountRequest.getEndDate(),
                activeAccountRequest.getChannel());
        // 手动组装Page对象
        return new PageImpl<>(content, PageRequest.of(page, size), total);
    }
}

额外修正点 #

• 将List<Character>改为List<String>：匹配数据库channel字段的字符串类型，避免参数绑定错误。
• 简化SQL表名前缀：移除查询中冗余的REPORTING_GENERAL.前缀，因为仅查询单表，语法更简洁。

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