Putting array of numbers in SQL (WHERE) code

[iNko]

Hey, i got an sql code that puts data from database into my table.

I also got a list array of numbers.

When i write this:
System.out.println(list.get(i)); - It gives me all the values of that array (1,2,3,4 etc).

I want to put this array in my sql code, like this:
"SELECT * FROM Table1 WHERE Id='" + list.get(i) + "'";
This sql code only displays 1 value. I want it to display ALL values, not just one.

If i write:
"SELECT * FROM Table1 WHERE Id='" + list.get(0) + "' OR '" + list.get(1) + '";
It would display 1st and 2nd value.

Why isnt the list.get(i) working, and how do i make it work?

[tamilarasi]

yes u couldn't store array values directly into database. so u will be append the array values into stringbuffer after that u convert string buffer into string.

Now u store this string into ur database. and also compare this string to ur db..

[Tolls]

yes u couldn't store array values directly into database. so u will be append the array values into stringbuffer after that u convert string buffer into string.

Now u store this string into ur database. and also compare this string to ur db..

The OP is not storing an array of values or any such thing.
They are searching for rows whose id matches one of a number of supplied ids.

iNko.
You want to use an IN clause.

SELECT * FROM <your table> WHERE <column> IN (<id>,<id>,<id>)

[iNko]

The OP is not storing an array of values or any such thing.
They are searching for rows whose id matches one of a number of supplied ids.

iNko.
You want to use an IN clause.

SELECT * FROM <your table> WHERE <column> IN (<id>,<id>,<id>)

thank you, ill see if i can make it work :)

Edit:
hmm it still seems to display just one value from database, i think its because my arraylist displays numbers like this:
1
2
3
4..
(in a column)

Can i make it so it would display them in a line? one after each other with a ',' in between them? like this: 1,2,3,4

[tamilarasi]

i think if u want this..

String s={1,2,3,4};

u just compare this string into db

SELECT * FROM <your table> WHERE Id=s.
or if u have any unique id u use that into ur comparison.. i think this will be help to u..

[Tolls]

i think if u want this..

String s={1,2,3,4};

u just compare this string into db

SELECT * FROM <your table> WHERE Id=s.
or if u have any unique id u use that into ur comparison.. i think this will be help to u..

If you don't know what you;re talking about then please don't try and answer the questions.
That there is simply wrong.
You clearly do not know SQL...

[Tolls]

thank you, ill see if i can make it work :)

Edit:
hmm it still seems to display just one value from database, i think its because my arraylist displays numbers like this:
1
2
3
4..
(in a column)

Can i make it so it would display them in a line? one after each other with a ',' in between them? like this: 1,2,3,4

What does your code look like now?

[iNko]

What does your code look like now?

This is the method where i get my ArrayList (its values from another table):

private void arraylistValues(){
    try {
       String sql = "SELECT table2_id FROM Table2;
       pst = conn.prepareStatement(sql);    
       rs = pst.executeQuery(sql); 
       while (rs.next()){
       list.add(rs.getString("table2_id "));
      }  
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
    }   
}

As a test, i write "System.out.print(list.get(i));" and it shows the numbers that i need, so array is working normally i assume.

And this is the method that populates my table:

private void updateTable(){
    try{
       int totalElements = list.size();
       for(int i = 0; int totalElements< 2; i++ ) {
       String sql = "SELECT * FROM Table1 WHERE id IN (" + list.get(i) + ")"; 
       pst = conn.prepareStatement(sql);
       rs = pst.executeQuery(sql);
       jTable1.setModel(DbUtils.resultSetToTableModel(rs));
    } }  
    catch (Exception e){
       JOptionPane.showMessageDialog(null, e);
    }

Note that if in this method in SQL code i write "SELECT * FROM Table1 WHERE id IN (1,2,3,4,5)"; , it would display all 5 values in my jTable, but if i write "IN (" + list.get(i) + ")"; it still only displays 1 last value
Edit: I think i just need somehow change list.get(i), to show numbers like this - 1,2,3,4,5

[tamilarasi]

If you don't know what you;re talking about then please don't try and answer the questions.
That there is simply wrong.
You clearly do not know SQL...

I know about the sql who r u told i didn't know the knowledge of sql..
u go through all the posts on that question..
my method is definately works..
and i am used this method on my projects.

So don't try to others are pools..
So please be speak with genuine..

[Tolls]

You would concatenate the IN clause:

StringBuilder sb = new StringBuilder();
for (String s : list) {
   sb.append(s);
   sb.append(",");
}

Note you'll have to lose the last comma...:)

BUT...if this list of ids is simply the contents of table2, then this is a single query:

SELECT * FROM table1 WHERE id IN (SELECT table2_id FROM table2)

[iNko]

You would concatenate the IN clause:

StringBuilder sb = new StringBuilder();
for (String s : list) {
   sb.append(s);
   sb.append(",");
}

Note you'll have to lose the last comma...:)

BUT...if this list of ids is simply the contents of table2, then this is a single query:

SELECT * FROM table1 WHERE id IN (SELECT table2_id FROM table2)

it finally works omg, thank you very much!