4

I have lots of column in my DB and don't want to write custom queries for all of them so consider the below method runs for every column name dynamicaly which i give as column_name. Is something like this possible or are there any other way to do that?

      @Query("select #column_name from Item Where #column_name like %?2% ")
      List<Item> getUniqueColumn(String column_name,String column_text);

By the way in spring documantation this case not mentioned.

Neil Stockton
  • 11,383
  • 3
  • 34
  • 29
C.T
  • 151
  • 2
  • 9
  • Not clear what you want... Please add details – nhouser9 Feb 10 '17 at 06:01
  • I edited my question; I have lots of column in my DB and don't want to write custom queries for all of them so consider the above method runs for every column name dynamicaly, is it possible? – C.T Feb 10 '17 at 06:04
  • use Eclipse to auto-generate your Entities for you – Scary Wombat Feb 10 '17 at 06:09
  • @C.T. I am not at all an expert in this, so there may be a much better way, but as long as you don't expect your columns to change all that often you could just maintain a config file (or even another db table) with column names and pull from that to build each query – nhouser9 Feb 10 '17 at 06:13
  • @ScaryWombat How? – C.T Feb 10 '17 at 06:15
  • see [link](https://www.eclipse.org/webtools/dali/docs/3.2/user_guide/tasks006.htm) I am sure that you could have searched for this – Scary Wombat Feb 10 '17 at 06:16

1 Answers1

4

You can only pass values that you are expecting as parameters to your HQL queries. You can't pass column or table names.

Hibernate is basically working here with a PreparedStatement, and a statement cannot be prepared where the table / columns being queried for are not known yet.

You would have to write some String replacement logic or build your query with the Criteria API

List<Object> getUniqueColumn(String column_name,String column_text){
        StringBuilder query = new StringBuilder();
        query.append("select #column_name ");
        query.append("from Item ");
        query.append("where #column_name like %?1%");

        query = query.toString("#column_name", column_name);

        session.createQuery(query).setString(1, column_text).list();
}

Also remember that what you are doing here is a projection and you will not get a List of Item but a List of Objects.

Maciej Kowalski
  • 25,605
  • 12
  • 54
  • 63
  • 1
    Using spring data specifications, it is possible to 'pass' column names as arguments. I post an example here, https://stackoverflow.com/questions/47407043/spring-data-jpa-pass-column-name-and-value-as-parameters/48492077#48492077 – Georgios Syngouroglou Jan 28 '18 at 22:36