I have a sql table named reporting_general and I want to use a complex native sql query in which I used SQL Aliases , for that purpose I implemented JPA Projection to map the columns, so I made an interface for that purpose, but I am getting this error.

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
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-j-8.0.31.jar:8.0.31]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-j-8.0.31.jar:8.0.31]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916) ~[mysql-connector-j-8.0.31.jar:8.0.31]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:972) ~[mysql-connector-j-8.0.31.jar:8.0.31]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-4.0.3.jar:na]
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-4.0.3.jar:na]
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57) ~[hibernate-core-5.6.14.Final.jar:5.6.14.Final]

Here is My Postman

SQL Table

Entity class

@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;
      }

Interface used for JPA Projection (the atributes of this class are the aliases I've used in the query)

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

Repository class

    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 List<ActiveAccountReport> getActiveAccountReportFilters(
            LocalDateTime startDate,
            LocalDateTime endDate,
            List<Character> channel,
            Pageable pagable);
}

Service Class

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

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

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

Controller Class

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

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

    }

CodePudding user response：

You missed space before FROM clause

CodePudding user response：

If you want to use Paging and native queries you must provide a countQuery.

Example:

@Query(value="select * from author a where a.last_name= ?1", 
        countQuery = "select count(id) from author a where a.last_name= ?1", 
        nativeQuery = true)
Page<Author> getAuthorsByLastName(String lastname, Pageable page);