2

I am using play with JPA with PostgreSQL and having a problem creating a jpql.

Below is my model code

public static List<Jobseekers> search(String key, String keyvalue) {
        @SuppressWarnings("unchecked")
        List<Jobseekers> empList = JPA.em().createQuery("FROM Jobseekers WHERE :key LIKE :keyvalue").setParameter("key", key).setParameter("keyvalue", "%"+keyvalue+"%").getResultList();
       System.out.println("key is"+key);
        return empList;
    }

so my column name is dynamic in query

The sql that is created is like

select * from jobseekers where 'name' like '%akash%'

where name is the key

The above query gives an empty list because of the appostrophe(') in name column name

My problem is that how to remove appostrophe(') in column name('name') so I can get correct result?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
singhakash
  • 7,891
  • 6
  • 31
  • 65
  • 1
    Your problem isn't how to escape apostrophes, your problem is that a prepared statement with `:key` doesn't handle dynamic SQL the way it handles values. I don't know the answer but you should change the title and possibly the question to reflect this. – asontu Dec 22 '14 at 12:51
  • Maybe **[this is helpful](http://stackoverflow.com/questions/3617687/is-it-possible-to-dynamically-define-column-names-in-hibernate-jpa)**? – asontu Dec 22 '14 at 12:55

1 Answers1

1

You can't parametrize object names. Parameters are only for values. You need to include key in the query itself (which potentially opens you up for SQL injection!).

So you need to do:

List<Jobseekers> empList = JPA.em().createQuery("FROM Jobseekers WHERE " + 
        key + " LIKE :keyvalue")
    .setParameter("keyvalue", "%"+keyvalue+"%")
    .getResultList();

The other option is to use the criteria API to build the query, but that is a lot more complex; and I haven't used it a lot, so I can't provide an example.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197