What is the best approach using JDBC for parameter

Question

This question already has an answer here:

• PreparedStatement IN clause alternatives? 27 answers

Say that I have a query of the form

SELECT * FROM MYTABLE WHERE MYCOL in (?)

And I want to parameterize the arguments to in.

Is there a straightforward way to do this in Java with JDBC, in a way that could work on multiple databases without modifying the SQL itself?

The closest question I've found had to do with C#, I'm wondering if there is something different for Java/JDBC.

Answer 1

I got the answer from docs.spring(19.7.3)

The SQL standard allows for selecting rows based on an expression that includes a variable list of values. A typical example would be select * from T_ACTOR where id in (1, 2, 3). This variable list is not directly supported for prepared statements by the JDBC standard; you cannot declare a variable number of placeholders. You need a number of variations with the desired number of placeholders prepared, or you need to generate the SQL string dynamically once you know how many placeholders are required. The named parameter support provided in the NamedParameterJdbcTemplate and JdbcTemplate takes the latter approach. Pass in the values as a java.util.List of primitive objects. This list will be used to insert the required placeholders and pass in the values during the statement execution.

Hope this can help you.

Answer 2

See my trial and It success,It is said that the list size has potential limitation. List l = Arrays.asList(new Integer[]{12496,12497,12498,12499}); Map param = Collections.singletonMap("goodsid",l);

    NamedParameterJdbcTemplate  namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());
    String sql = "SELECT bg.goodsid FROM beiker_goods bg WHERE bg.goodsid in(:goodsid)";
    List<Long> list = namedParameterJdbcTemplate.queryForList(sql, param2, Long.class);

Answer 3

There are different alternative approaches that we can use.

1. Execute Single Queries - slow and not recommended
2. Using Stored Procedure - database specific
3. Creating PreparedStatement Query dynamically - good performance but loose benefits of caching and needs recompilation
4. Using NULL in PreparedStatement Query - I think this is a good approach with optimal performance.

Check more details about these here.

Answer 4

sormula makes this simple (see Example 4):

ArrayList<Integer> partNumbers = new ArrayList<Integer>();
partNumbers.add(999);
partNumbers.add(777);
partNumbers.add(1234);

// set up
Database database = new Database(getConnection());
Table<Inventory> inventoryTable = database.getTable(Inventory.class);

// select operation for list "...WHERE PARTNUMBER IN (?, ?, ?)..."
for (Inventory inventory: inventoryTable.
    selectAllWhere("partNumberIn", partNumbers))    
{
    System.out.println(inventory.getPartNumber());
}

Answer 5

I solved this by constructing the SQL string with as many ? as I have values to look for.

SELECT * FROM MYTABLE WHERE MYCOL in (?,?,?,?)

First I searched for an array type I can pass into the statement, but all JDBC array types are vendor specific. So I stayed with the multiple ?.

Answer 6

The standard way to do this is (if you are using Spring JDBC) is to use the org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate class.

Using this class, it is possible to define a List as your SQL parameter and use the NamedParameterJdbcTemplate to replace a named parameter. For example:

public List<MyObject> getDatabaseObjects(List<String> params) {
    NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    String sql = "select * from my_table where my_col in (:params)";
    List<MyObject> result = jdbcTemplate.query(sql, Collections.singletonMap("params", params), myRowMapper);
    return result;
}