CODEWITHSUNDEEP
javaprepared-statement
pmaingi
pmaingi

Reputation: 121

How to set list in prepared statement

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";

Upvotes: 1

Views: 2664

Answers (2)

David Lavender
David Lavender

Reputation: 8331

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

Upvotes: 1

GerritCap
GerritCap

Reputation: 1626

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...

Upvotes: 2

Related Questions