How to set list in prepared statement

Question

How can i set list in prepared statement(statement.setString(1, productCode); ). See below my code snippet.

Thanks

public static List<Message> listAllWonBids(List<Message> productCode,
    Connection connection) {

    List<Message> winners = new ArrayList<Message>();

    String sql = "SELECT b.`id`, b.`msisdn` as msisdn ,b.`productname` as productname, b.`productcode` as productcode, max(b.`amount`) as amount FROM  "
        + TableNames.SAVEDBIDSTABLE
        + " b where productcode = ? "
        + " group by amount order by productcode, amount  desc limit 1";

    PreparedStatement statement = null;
    ResultSet resultSet = null;

    try {
        LOGGER.info(sql);

        if (connection == null || connection.isClosed())
            connection = DBConnection.getConnection();

        statement = connection.prepareStatement(sql);

        **statement.setString(1, productCode);** 

        resultSet = statement.executeQuery();

NOTE: productCode is coming from another list shown below

public static List<Message> allProductCode(Connection connection) {
    List<Message> productcodes = new ArrayList<Message>();

    PreparedStatement statement = null;
    ResultSet resultSet = null;

    String sql = "SELECT `productCode` FROM " + TableNames.AUCTIONTABLE1
        + " WHERE date(`endDate`) = curdate() order by `id` desc";

The answers in this question might help: http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives — David Lavender, Mar 02 '14 at 11:05

score 2 · Answer 1 · edited Mar 02 '14 at 11:07

2

That is not possible, either you generate a where clause programmatically using the IN

"where productcode in (" + listToStringEncoding + ")"

or you loop over the list and call the statement multiple times.

Other possibility is to join the 2 statements...

edited Mar 02 '14 at 11:07

Daniel F. Thornton

3,687
2
28
41

answered Mar 02 '14 at 10:59

GerritCap

1,606
10
9

Thanks gerritCap, let me try as above. – pmaingi Mar 02 '14 at 11:06

David Lavender · Accepted Answer · 2014-03-02T11:11:01.077

1

You could combine those two queries. Something like:

String sql = "SELECT b.`id`, b.`msisdn` as msisdn ,b.`productname` as productname, b.`productcode` as productcode, max(b.`amount`) as amount FROM  " 
+ TableNames.SAVEDBIDSTABLE + " b where productcode in (SELECT `productCode` FROM " 
+ TableNames.AUCTIONTABLE1 + " WHERE date(`endDate`) = curdate() order by `id` desc) group by amount order by productcode, amount  desc limit 1";

Then you wouldn't need any parameters

edited Mar 02 '14 at 11:11

answered Mar 02 '14 at 11:05

David Lavender

8,021
3
35
55

Thanks mr spoon, the above query works but lists all product code. I would like to get the max amount per productcode, thats why i limit by 1(meaning max per product code). Maybe you have a different way to achieve this. Thanks alot – pmaingi Mar 02 '14 at 11:18

How to set list in prepared statement

2 Answers2