Java MYSQL select with checkbox

Question

Possible Duplicate:
PreparedStatement IN clause alternatives?

I want to select from MYSQL based on checkbox selection. (Checkbox has ID's, and the select from database should select where ID= ? -> the IDs which are selected)

I organize it with MVC architecture:

• in jsp is my form (which is dynamic, so there can be more)

    <input type="checkbox" name="checkboxes" value="1">XY
    <input type="checkbox" name="checkboxes" value="2">XY
    <input type="checkbox" name="checkboxes" value="3">XY
  

• in servlet I get the values

    String[] catids  = request.getParameterValues("checkboxes");
  
    //Forward to bean -> doesnt work because catids is like an array
    FrageBean f=FragenBean.getRandomQuestionByCategory(catids);
  

• My bean is:

  public static FrageBean getRandomQuestionByCategory(String catids) {
  ArrayList<Integer> alleFragenIds = new ArrayList<Integer>();
  
  DatabaseMetaData dbmd;
  Statement sql;
  Connection db = null;
  
  
  try {
      Class.forName("com.mysql.jdbc.Driver");
  } catch (ClassNotFoundException ex) {
      System.out.println("driver not found");
  }
  try {
      db = DriverManager
              .getConnection("jdbc:mysql://localhost:3306/xyz?"
              + "user=root&password=xyz");
      dbmd = db.getMetaData();
  
      System.out.println("Connected with: " + dbmd.getUserName() + " | "
              + "Connection to: " + dbmd.getDatabaseProductName() + " " + dbmd.getDatabaseProductVersion() + " successful.\n");
      sql = db.createStatement();
  } catch (SQLException ex) {
      System.out.println("Error: " + ex);
  }
  
  try {
  
      String query = "select id from quest where quest_id=?";
      PreparedStatement prest = db.prepareStatement( query);
  
      prest.setString(1, catids);
  
  
      ResultSet rs = prest.executeQuery();
      ArrayList fragenliste = new ArrayList();
      while (rs.next()) {
          int fragenid = rs.getInt("id");
          alleFragenIds.add(fragenid);
  
      }
  
      rs.close();
      db.close();
  
  
  } catch (SQLException ex) {
      System.out.println("Error: " + ex);
  }
  
  Random r = new Random();
  int frageIndex = r.nextInt(alleFragenIds.size());
   System.out.println("Contents of al: " + alleFragenIds);
  return new FrageBean(alleFragenIds.get(frageIndex));
  
  }
  

At the moment it worked only for one id (Because I had only one choice). How can I do it with the checkboxes? How is it possible to forward "catids" to bean and to create the mysql-query dynamic? (select id from fragen where kategorie_id= [ALL CHECKED BOXES] )

Thanks in advance!!!!

Answer 1

Create below method in your class. Put proper driver clas and url connection and pass the catids as an string array.

Assumption : I am assuming column "quest_id" is of type int in table quest.

 public  void displayRecords(String[] catids)
{
    try
    {

        String queryStart = "select id from quest where quest_id in ( ";
        String queryMiddle = "";
        String prefix = "";
        String queryEnd = " )";
        String query = "";

        if (catids != null && catids.length > 0)
        {
            for (String id : catids)
            {
                queryMiddle = queryMiddle + prefix + id;
                prefix = ",";
            }

            query = queryStart + queryMiddle + queryEnd;
            System.out.println(query);

            Class.forName("DriverClass....................");
            Connection conn = DriverManager.getConnection("connection url ..................");
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next())
            {
                int fragenid = rs.getInt("id");
                System.out.println("ID = " + fragenid);
            }
        }
    }
    catch (Exception e)
    {
        e.printStackTrace();
    }
}

Answer 2

@RaisAlam's answer is dangerous as it is vulnerable to SQL injection. Here's a fixed version:

public  void displayRecords(String[] catids) {
    try {
        String queryStart = "select id from quest where quest_id in ( ";
        String queryMiddle = "";
        String prefix = "";
        String queryEnd = " )";
        String query = "";

        if (catids != null && catids.length > 0) {
            for (String id : catids) {
                queryMiddle = queryMiddle + prefix + "?";
                prefix = ",";
            }
            query = queryStart + queryMiddle + queryEnd;
            System.out.println(query);
            Class.forName(....);
            Connection conn = DriverManager.getConnection(....);
            PreparedStatement stmt = conn.createPreparedStatement(query);
            int i = 1;
            for (String id : catids) {
                stmt.setInt(i++, Integer.parseInt(id));
            }
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                int fragenid = rs.getInt("id");
                System.out.println("ID = " + fragenid);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Using a PreparedStatement with a placeholder for each parameter is the "best practice" way to avoid SQL injection.