native query issues

Question

I'd like to create a criteria query, where I can set different entity properties and different values depending on method args.

public List<Customer> searchBar(String entityProperty, String value) {

    String nativeQuery = "SELECT * FROM devices WHERE customer_id IN (SELECT customers.id FROM customers WHERE ? = ?)";
    Query query = session.createNativeQuery(nativeQuery);
    query.setParameter(1, entityProperty);
    query.setParameter(2, value);
    return query.getResultList();

you can assume that:

String entityProperty = "phoneNumber"
   String value = "222222222"

when I try this way, I get an empty result, but if I hardcode entityProperty on nativeQuery statement it works as it should:

String nativeQuery = "SELECT * FROM devices WHERE customer_id IN (SELECT customers.id FROM customers WHERE phoneNumber = ?)";
Query query = session.createNativeQuery(nativeQuery);
query.setParameter(1, value);
return query.getResultList();

thanks for your time :)

score 0 · Accepted Answer · answered Dec 29 '18 at 18:29

That setParameter is used for set the value for a column but not for the column name. Use general Java to compose the column name dynamically .Somethings like below , you should get the idea :

public List<Customer> searchBar(String entityProperty, String value) {

    String nativeQuery = String.format("SELECT * FROM devices WHERE customer_id IN (SELECT customers.id FROM customers WHERE %s = ?)",entityProperty);
    Query query = session.createNativeQuery(nativeQuery);
    query.setParameter(1, value);
    return query.getResultList();
}

native query issues

1 Answers1