Order by clause with Parameter binding

Hibernate ORM
1

I’m trying to set order by clause as a parameter binding in Native query.

But the results not ordered. But the query is executed using JPA.

String getTargetDetailsQry = "select MTH_TGT_PFT_DLR_COD from"
		 + VimmsConstants.SCHEMA_NAME + ".TRN_DLR_MTH_TGT inner join "+ VimmsConstants.SCHEMA_NAME + ".MST_DLR on MTH_TGT_PFT_DLR_COD = PFT_DLR_COD inner join "+ VimmsConstants.SCHEMA_NAME + ".MST_EVL_PRM on EVL_PRM_ID = MTH_TGT_EVL_PRM_ID where MTH_TGT_PRG_ID in(:progId) and MTH_TGT_EVL_PRM_ID in(:evlprmId) ";

// Order the results by respective column in ascending or descending order

		if (!sortBy.isEmpty()) {
			if (VimmsConstants.SORT_ORDER_ASC.equalsIgnoreCase(sortOrder)) {
				getTargetDetailsQry += " order by :sortBy " +VimmsConstants.SORT_ORDER_ASC;
			} else if (VimmsConstants.SORT_ORDER_DESC.equalsIgnoreCase(sortOrder)) {
				getTargetDetailsQry += " order by :sortBy " + VimmsConstants.SORT_ORDER_DESC;
			}
		}

Query query = getEntityManager().createNativeQuery(getTargetDetailsQry);
query.setParameter("sortBy", sortBy);

Any help would be appreciated.

Thanks in advance

2

Concatenating JPQL query tokens is very risky and can lead to SQL Injection attacks. Your application can be easily compromised if you do that.

You need to use Criteria API or jOOQ if you want to build queries dynamically.

More, you can’t pass SQL tokens as PreparedStatement bind parameters. Again, you need to use Criteria API which works for SELECT, UPDATE or DELETE statements.

1 Like