querying a database with an arraylist

Question

I want to query a database using an arraylist value. It should return a value from the database the same as one of my arraylist element if it exists. I am trying something like this, but it is not working

ArrayList<String>  list  =  new ArrayList<String>();
list.add("boy");
list.add("girl");
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("url", "root", "");
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("Select * from table where keyword= (list)");

For multiple values in the where clause see: w3schools.com/sql/sql_in.asp. In your case "select * from table where keyword in ('boy', 'girl');" — Micha Wiedenmann
– Micha Wiedenmann, Commented May 18, 2013 at 11:05

cmbaxter · Accepted Answer · 2013-05-18 11:11:48Z

1

You could try something like this:

  ArrayList<String>  list  =  new ArrayList<String>();
  list.add("boy");
  list.add("girl");
  Class.forName("com.mysql.jdbc.Driver");
  Connection con = DriverManager.getConnection("url", "root", "");
  Statement st = con.createStatement();
  StringBuilder sb = new StringBuilder("Select * from table where keyword IN (");
  boolean added = false;
  for(String s:list){
    if (added){
      sb.append(",");
    }      
    sb.append("'");
    sb.append(s);
    sb.append("'");
    added = true;
  }
  sb.append(")");
  ResultSet rs = st.executeQuery(sb.toString());

answered May 18, 2013 at 11:11

cmbaxter

35.5k4 gold badges88 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

christopher · Accepted Answer · 2013-05-18 11:07:59Z

0

This can be solved using the in key word.

String query = "SELECT * FROM table WHERE keyword in (";

for(String s : list)
{
    query += "'" + list + "',";
}

query += ")";

SQL Injection

You should probably escape the list entry in order to be save against SQL injection attacks. –

answered May 18, 2013 at 11:07

christopher

27.4k6 gold badges59 silver badges90 bronze badges

1 Comment

Micha Wiedenmann Over a year ago

You should probably escape the list entry in order to be save against SQL injection attacks.

Himanshu Bhardwaj · Accepted Answer · 2013-05-18 11:08:43Z

First of all the query you are forming is not correct. You should be using IN clause, something like:

ResultSet rs = st.executeQuery("Select * from table where keyword IN (*comma-separated-value-from-list*)");

The only thing that is more required is converting the list into comma-separated-values, you can do something like:

String[] val = list.toArray(new String[list.size()]);
StringBuilder csvStr = new StringBuilder();
for(int i=0;i<val.length;i++){
    if (val.length > 1 && i !=0) {
        csvStr.append(",");
    }
    csvStr.append ("'").append(val[i]).append("'");
}
System.out.println(csvStr);

And use this csvStr in the query instead:

ResultSet rs = st.executeQuery("Select * from table where keyword IN ("+csvStr.toString()+")");

Joachim Isaksson · Accepted Answer · 2013-05-18 12:31:33Z

You could use PreparedStatement to avoid all escaping of the SQL and security issues with that.

To do that, you build a string with question marks as a placeholder, and then add the parameters to the PreparedStatement instead of the string;

String statement = "SELECT * FROM table WHERE keyword " + getIn(list.size());
PreparedStatement preparedStatement = con.prepareStatement(statement);
for(int i=0; i<list.size(); i++)
    preparedStatement.setString(i+1, list.get(i));
ResultSet result = preparedStatement.executeQuery();

...where getIn is a function returning the IN part with question marks that is suitable for a PreparedStatement;

static String getIn(int numParams) {
    StringBuilder builder = new StringBuilder("IN (?");
    for(int i=1; i<numParams; i++)
        builder.append(",?");
    builder.append(")");
    return builder.toString();
}

Collectives™ on Stack Overflow

querying a database with an arraylist

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related