How to create a dynamic query using EOD SQL?

Question

Hello guys.

This should be fairly simple, though I can't seem to find a single example. I want to create a query looking like this:

SELECT column_name FROM table_name WHERE column_name IN (value1,value2,...)

As an option I could append OR-clauses to the end of the query.

The code I have written so far keeps blowing up with a Nullpointer:

@Select(sql = "select storename from broadcastrecipient where storecity in (?{1})")
public List<String> getStoresForCities(List<String> theCities) throws SQLException;

Thanks in advance. //Abean

NOTE: I forgot to add some info about my env: PostGres 8.3, Java 1.6 and EOD SQL 0.9.

---

Thank you Jason. For those who like to know, the query looks something like this:

    @Select(sql = "select distinct(storename) from broadcastrecipient where storecity = any (?{1})", disconnected=true)
    public List<String> getStoresForCities(String[] theCities) throws SQLException;

And I also needed to implement a TypeMapper class to map SQL-array to Java array.

Answer 1

I would suggest looking at EoD SQL 2.0: https://eodsql.dev.java.net/ Then take a look at the QueryTool.select() method which is designed exactly for this situation.

EoD SQL 0.9 has no functionality to cope with this sort of query built in.

A really ugly hack is to create a temp table, and populate it with your array data. Then run the query as:

@Select("SELECT storename FROM broadcastrecipient WHERE storecity IN (SELECT * FROM tmp_cities)")
public List<String> getStoresForCurrentCities();