JAVA - MySQL an ArrayList

Question

I have query from MySQl

String sqlSearch = "SELECT * from Item"

and method with ArrayList

  public static ArrayList<String> checkNo() throws SQLException {

    ArrayList<String> no= new ArrayList<String>();

    DbManager db = new DbManager();
    db.connect();
    String sql = "SELECT * from Category where id = " + idUser + " ";

    Statement stmt = db.connect().createStatement();
    ResultSet rs = stmt.executeQuery(sql);

    while (rs.next()) {

        no.add(rs.getString("nameCategory"));

    }
    db.disconnect();
    return no;
}

And I get from specific user his category in arraylist, but how to pass this category to sqlSearch???

  SELECT * from Item where category ????
  data from ArrayList

Any idea?

Using prepared statements is recommended. docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html — dsharew
– dsharew, Commented Apr 7, 2017 at 12:53

Stefano Zanini · Accepted Answer · 2017-04-07 12:50:53Z

1

You could loop through the ArrayList items and build a String to use in the where clause like this

String inCondition = "";
boolean first = true;
ArrayList<String> categories = checkNo();

for(String cat : categories){
    if(first){
        first = false;
    } else {
        inCondition += ",";
    }
    inCondition += cat;
}

String sqlSearch = "SELECT * from Item where category in (" + inCondition + ")";

answered Apr 7, 2017 at 12:50

Stefano Zanini

5,9362 gold badges15 silver badges33 bronze badges

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

Comments

Mehmood Memon · Accepted Answer · 2017-04-07 12:52:03Z

0

Your checkNo() method return an ArrayList. It looks like this ArrayList will have only one item, get that item from the ArrayList and pass it to the query like this.

String category = list.get(i);    
"Select * from Item where category = "+category;

answered Apr 7, 2017 at 12:52

Mehmood Memon

1,1592 gold badges14 silver badges20 bronze badges

2 Comments

Stefano Zanini Over a year ago

The assumption that the category will be only one is a bit strong, with such few information about the tables involved... isn't it?

Mehmood Memon Over a year ago

I made that assumption thinking that the above query will return only 1 result because of where clause matching with 'id'

Hema · Accepted Answer · 2017-04-07 12:55:42Z

0

As your query is not clear am giving you a sample code,jus try it

if (count > 0) {
        for (int i = 0; i < count; i++) {
            ColumnName= no.get(i).getColumnName();
            }
        getItemQuery = "FROM Item where category in (" +ColumnName + ")";
    }

answered Apr 7, 2017 at 12:55

Hema

9861 gold badge16 silver badges38 bronze badges

Comments

nafas · Accepted Answer · 2017-04-07 12:55:46Z

0

There are many ways of doing this,

one way is two combine two queries:

SELECT * from Item where category in (SELECT nameCategory from Category where id = idUser )

as suggested its better to use prepared statement.

answered Apr 7, 2017 at 12:55

nafas

5,4533 gold badges34 silver badges59 bronze badges

Collectives™ on Stack Overflow

JAVA - MySQL an ArrayList

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related