Results 1 to 4 of 4
  1. #1
    learnjava32 is offline Member
    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0

    Default query with multiple IN parameters

    Hello all,
    A beginner in Java although have couple of years using Stata(statistical software) and its odbc. I will run a query which asks users to input user_id.
    select distinct doc_id from documents where user_id=b. The user has to input b. Based on that, the query will generate some integers like this

    doc_id
    2
    3
    4

    Next I need to transform the column values horizontally separated by comma. In Stata, I would do something called levelsof. So, I have now
    2,3,4

    I need to take 2,3,4 and put it in my next final query like this..
    select count(*) from readership where source_id in (2,3,4)

    Can this be done in Java?? Any help will be highly appreciated...

    Thanks..

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,450
    Rep Power
    19

    Default Re: query with multiple IN parameters

    This is purely a SQL query, not a Java thing.
    A single one at that.

    Java Code:
    SELECT count(*)
    FROM readership
    WHERE source_id IN
       (SELECT distinct doc_id etc etc)
    This assumes you don't need the intermediate step.
    Please do not ask for code as refusal often offends.

  3. #3
    learnjava32 is offline Member
    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0

    Default Re: query with multiple IN parameters

    Thanks.. but unfortunately each query has to be performed on different database. So, basically I need to get the distinct doc_id from one database and use that to get counts from another database. Well, basically what I am looking for is the way to do using JDBC api. I see that you could do this if I wanted a placeholder for each IN parameter, I could use ("?").
    select count(*) from readership where source_id=?
    psmt.setLong(1,1234)

    My question is what is the method if I need a placeholder for multiple integers like
    select count(*) from readership where source_id in (1,2,3,4,6) // not a single integer represented by "?" but bunch of integers separated by comma. Please let me know if you need more detail. Thanks.

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,450
    Rep Power
    19

    Default Re: query with multiple IN parameters

    Assuming the number of ids in the IN clause is fairly limited (Oracle, for example, has a limit of around 1000), then the usual technique is to either concatenate the IN clause (if they are likely to vary a lot), which makes each query unique, or to build the IN clause for a PreparedStatement something along the lines of:
    Java Code:
    for (each id) {
       if (firstId) {
          preparedStatementString.append("?");  
       } else {
          preparedStatementString.append(",?");  
       }
    }
    then assign them:
    Java Code:
    for (each id) {
       ps.setInt(<loopIndex>, id);
    }
    Note, of course, that the above is not copy/paste material.
    Please do not ask for code as refusal often offends.

Similar Threads

  1. Multiple SELECT Query issue
    By WishBone101 in forum New To Java
    Replies: 2
    Last Post: 06-19-2012, 10:41 AM
  2. Java JDBC - Display Multiple Query Results in one GUI
    By nov072008 in forum AWT / Swing
    Replies: 5
    Last Post: 10-06-2011, 05:03 PM
  3. Replies: 4
    Last Post: 10-01-2010, 10:52 AM
  4. problem with display of query parameters
    By anirudh4uhere in forum JavaServer Pages (JSP) and JSTL
    Replies: 3
    Last Post: 05-12-2010, 12:38 PM
  5. Query with 1 record vs. multiple records
    By anderma8 in forum JDBC
    Replies: 5
    Last Post: 07-09-2007, 11:33 PM

Posting Permissions

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