0

I got problem in coding JPA query against my MySQL table.

The table structure is shown as below:

mysql> desc t_product_purchase;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| oid             | bigint(20)   | NO   | PRI | NULL    |       |
| consumer_id     | bigint(20)   | NO   |     | NULL    |       |
| number          | bigint(20)   | YES  |     | NULL    |       |
| pay_time        | datetime     | YES  |     | NULL    |       |
| payment         | float        | YES  |     | NULL    |       |
| price           | float        | YES  |     | NULL    |       |
| product_num_iid | bigint(20)   | NO   |     | NULL    |       |
| seller_id       | bigint(20)   | NO   | MUL | NULL    |       |
| product_title   | varchar(255) | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

The following SQL got executed as expected.

select m.product_num_iid, sum(m.payment) as payment, sum(m.number) as nbr from t_product_purchase m where m.seller_id = 247475251 and m.pay_time >= 0  group by m.product_num_iid order by payment desc limit 0, 10

The payment can be ordered correctly.

In my spring-boot JPA repository interface: @Transactional @Repository public interface ProductPurchaseMeasurementRepository extends JpaRepository {

@Query(value = "select m.product_num_iid, sum(m.payment) as payment, sum(m.number) as nbr from t_product_purchase m where m.seller_id = ?1 and m.pay_time >= ?2 and m.pay_time < ?3 group by m.product_num_iid order by ?4 desc limit ?5, ?6", nativeQuery = true)
public List findRankedProductPaymentOrCountBySellerIdWithinPayTime(long sellerId, Date startPayTime, Date endPayTime, String orderBy, long offset, long size);

}

The parameter orderBy will be passed with value "payment". But I can't get ordered records in the returned value of the method.

Anybody got idea why the repository method is not working as expected? Thanks.

user2663484
  • 11
  • 1
  • 4
  • enable show sql and check the query sent to DB – Saravana Nov 02 '15 at 13:08
  • 1
    Placeholder in `ORDER BY ?4` is interpreted as a literal, not column name which is why it's not working. See [this post](http://stackoverflow.com/questions/26068614/repository-order-by-in-native-query-not-working) for more details. – Bohuslav Burghardt Nov 02 '15 at 13:08
  • @BohuslavBurghardt - thanks. The post you provided is really helpful. I will experiment it. – user2663484 Nov 02 '15 at 20:54

2 Answers2

0

Unsupported, input parameters are only allowed in WHERE and HAVING blocks and parameters for the ORDER BY clause can not be used.

solution

order by 
case when 'a' = :a then ec.ISACTIVE else 1  end asc,
case when 'a' != :a then ec.ISACTIVE else 1  end desc

the following solution is better:

  • Have as much named queries as possible sort orders
  • Concatenate the ordering string to the query string
  • Use criteria queries

Hibernate Named Query Order By parameter

DEV-Jacol
  • 567
  • 3
  • 10
0

Your ?4 parameter is String, but framework binds it not the String value..Order by syntax is column name not the String (varchar) ! If you want to use dynamic limit and ordering, its best to use PagingAndSortingRepository. Spring Data JPA does not currently support dynamic sorting for native queries, because it would have to manipulate the actual query declared, which it cannot do reliably for native SQL. Try to use JPQL query.Example:

 @Query("select u from User u where u.lastname like ?1%")
  List<User> findByAndSort(String lastname, Sort sort);

Call this method: 

repo.findByAndSort("lannister", new Sort(new Sort.Order(Direction.ASC, "lastName")));
Seymur Asadov
  • 612
  • 5
  • 19