Results 1 to 11 of 11
  1. #1
    iNko is offline Member
    Join Date
    Dec 2012
    Posts
    55
    Rep Power
    0

    Default Putting array of numbers in SQL (WHERE) code

    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?

  2. #2
    tamilarasi is offline Member
    Join Date
    Nov 2012
    Location
    India
    Posts
    70
    Rep Power
    0

    Default Re: Putting array of numbers in SQL (WHERE) code

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

  3. #3
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

    Default Re: Putting array of numbers in SQL (WHERE) code

    Quote Originally Posted by tamilarasi View Post
    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.
    Java Code:
    SELECT * FROM <your table> WHERE <column> IN (<id>,<id>,<id>)
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  4. #4
    iNko is offline Member
    Join Date
    Dec 2012
    Posts
    55
    Rep Power
    0

    Default Re: Putting array of numbers in SQL (WHERE) code

    Quote Originally Posted by Tolls View Post
    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.
    Java Code:
    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
    Last edited by iNko; 12-10-2012 at 03:28 PM.

  5. #5
    tamilarasi is offline Member
    Join Date
    Nov 2012
    Location
    India
    Posts
    70
    Rep Power
    0

    Default Re: Putting array of numbers in SQL (WHERE) code

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

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

    Default Re: Putting array of numbers in SQL (WHERE) code

    Quote Originally Posted by tamilarasi View Post
    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...
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  7. #7
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

    Default Re: Putting array of numbers in SQL (WHERE) code

    Quote Originally Posted by iNko View Post
    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?
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  8. #8
    iNko is offline Member
    Join Date
    Dec 2012
    Posts
    55
    Rep Power
    0

    Default Re: Putting array of numbers in SQL (WHERE) code

    Quote Originally Posted by Tolls View Post
    What does your code look like now?
    This is the method where i get my ArrayList (its values from another table):
    Java Code:
    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:
    Java Code:
    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
    Last edited by iNko; 12-10-2012 at 04:14 PM.

  9. #9
    tamilarasi is offline Member
    Join Date
    Nov 2012
    Location
    India
    Posts
    70
    Rep Power
    0

    Default Re: Putting array of numbers in SQL (WHERE) code

    Quote Originally Posted by Tolls View Post
    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..

  10. #10
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

    Default Re: Putting array of numbers in SQL (WHERE) code

    You would concatenate the IN clause:
    Java Code:
    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:
    Java Code:
    SELECT * FROM table1 WHERE id IN (SELECT table2_id FROM table2)
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  11. #11
    iNko is offline Member
    Join Date
    Dec 2012
    Posts
    55
    Rep Power
    0

    Default Re: Putting array of numbers in SQL (WHERE) code

    Quote Originally Posted by Tolls View Post
    You would concatenate the IN clause:
    Java Code:
    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:
    Java Code:
    SELECT * FROM table1 WHERE id IN (SELECT table2_id FROM table2)
    it finally works omg, thank you very much!

Similar Threads

  1. Replies: 1
    Last Post: 07-26-2012, 10:48 PM
  2. Replies: 2
    Last Post: 04-16-2012, 01:56 AM
  3. Replies: 22
    Last Post: 02-05-2012, 11:46 PM
  4. I need help with putting a logo on my code
    By MikeJ39 in forum New To Java
    Replies: 13
    Last Post: 10-22-2011, 02:10 AM
  5. Putting code together.
    By newbee in forum New To Java
    Replies: 3
    Last Post: 04-17-2008, 04:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •