Search code examples
javamysqldropwizarddynamicqueryjdbi

Dynamic bind select values in MySQL query jdbi

How can i write dynamic SQL query in Jbdi, like in my project customer will ask some details like first name,last name,mobile. so i will read those values into string and my idea was directly append that to SQL query dynamically like 

select first name,last name,mobile from customer

and another user ask only first name then my query will change like 

select first name from customer where customer Id=12345
Solution

  • I am doing something similar for dynamically generated search criteria strings in a Dropwizard application using JDBI. 

    @UseStringTemplate3StatementLocator
public interface ThingieDao {
    @SqlQuery
    @MapResultAsBean
    Iterator<Thingie> search(@Define("criteria") String criteria);
}

    The defined string "criteria" can then be used in the SQL template:

    group ThingieDao;
search(criteria) ::= <<
  SELECT * FROM table_name WHERE <criteria>
>>

    You can use the same technique to insert any string in the SQL, the SELECT column names in your case.

    The variable name in the interface does not matter, it is the @Define annotation string that matters.. Assuming Dropwizard doesn't add anything magic, and it usually doesn't, I guess that should work using plain JDBI as well.