0

How do I translate this SQL statement into a single PreparedStatement?

SELECT id, length, shipping_no, design_no, color_no, bale_no
FROM stocks
WHERE (shipping_no, design_no, bale_no) = ANY(VALUES('search_ship-1', 'des-1', 1), ('search_ship-2', 'des-2', 26));

Ideally something like this:

SELECT id, length, shipping_no, design_no, color_no, bale_no
FROM stocks 
WHERE (shipping_no, design_no, bale_no) = ANY(VALUES(?, ?, ?));

PreparedStatement st = ...;
for (...)
    st.setString(1, ...);
    st.setString(2, ...);
    st.setInt(3, ...);

    st.addBatch();
st.executeBatch();
Dula
  • 1,404
  • 1
  • 14
  • 29
  • You need one `set()` call per `?` placeholder. – Tim Biegeleisen Mar 12 '22 at 08:33
  • But `executeBatch()` does not return a `ResultSet`? – Dula Mar 12 '22 at 08:40
  • 1
    Batching is used for SQL/DML statements, not for looping query parameters, read more: https://stackoverflow.com/questions/7899543/can-preparedstatement-addbatch-be-used-for-select-queries . You have to create a single query (or multiple but put in a single statement if you want to process multiple result sets) containing all parameters in advance: if you have 3 columns per an element, and the number of elements is 10, you have to create a query made for 30 `?` parameters and set them all from 1 to 30, and then invoke `executeQuery` to obtain the result sets. – terrorrussia-keeps-killing Mar 12 '22 at 09:28
  • You cannot use `executeBatch` for this. You will need to add as many parameters as you want to set, and then use `executeQuery`. – Mark Rotteveel Mar 12 '22 at 13:32

0 Answers0