Results 1 to 6 of 6
  1. #1
    markw8500's Avatar
    markw8500 is offline Senior Member
    Join Date
    Jul 2008
    Location
    Pennsylvania, USA
    Posts
    136
    Rep Power
    0

    Default [SOLVED] Concrrent Queries with One Statement/Connection...

    Is there any way to have concurrent qureies with only one statement/connection... My database is Postgres and in my Java application I have 2 threads in which they each query the database with the same statement and connection... I want them to query concurrently, but no matter what I try, they will both start concurrently, but the second query waits until the first one is done...

    Here is my code:

    (variable "st" is my Statement)

    Java Code:
    new Thread(new Runnable() {
    
                public void run() {
    
                    try {
    
    
                        qry = "select partnumber from " + schema + ".allinventory limit " + 5000 + "";
    
    
                        System.out.println("Hello from the Query_1...");
                        ResultSet rs = st.executeQuery(qry);
    
    
                        while (rs.next()) {
    
                            getCol1().addElement(rs.getString(1));
    
    
                        //System.out.println(rs.getString(1));
    
                        }
    
                        System.err.println("Query_1 is done...");
                        done1 = true;
    
    
                    } catch (SQLException sqle) {
                        System.out.println(sqle);
                    }
    
    
    
    
                //throw new UnsupportedOperationException("Not supported yet.");
                }
            }).start();
    
    new Thread(new Runnable() {
    
                public void run() {
    
                    try {
    
                        System.out.println("Hello from the Query_2...");
                        qry = "select description from " + schema + ".allinventory limit " + 50+ "";
    
    
                        
                        ResultSet rs =st.executeQuery(qry);
                        
    
    
                        while (rs.next()) {
    
                            getCol2().addElement(rs.getString("description"));
    
    
                        //System.out.println(rs.getString("description"));
    
                        }
    
                        //sqlCon.setCon(con)
                        System.err.println("Query_2 is done...");
                        done2 = true;
    
    
                    } catch (SQLException sqle) {
                        System.out.println(sqle);
                    }
    
    
    
    
                //throw new UnsupportedOperationException("Not supported yet.");
                }
            }).start();
    Now the way my queries are set up the second query should fininsh first since its limit is 50 and the first query is 5000...

    Any Suggestions...

  2. #2
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    You might get away with using the same Connection instance, but even attempting to use the same statement is a recipe for disaster. There is absolutely no reason why you cannot create a second Statement instance (and don't even begin to tell me that the Statement is an instance variable, as that would be your first error, fix that), and almost no reason why you cannot create a second Connection instance.

  3. #3
    markw8500's Avatar
    markw8500 is offline Senior Member
    Join Date
    Jul 2008
    Location
    Pennsylvania, USA
    Posts
    136
    Rep Power
    0

    Default

    Calling the Statement a variable and not a instance was a test... ;) Just kidding...

    I tried using different statements with the same connection but it responded the same way... I was successful with a method that established a connection executed the query returned the resultSet, then disconnected... But I am conserned about performance... The more users I have the more the connections will be connecting and disconnecting...

    What do you think?

  4. #4
    fishtoprecords's Avatar
    fishtoprecords is offline Senior Member
    Join Date
    Jun 2008
    Posts
    571
    Rep Power
    7

    Default

    That's why god invented connection pools. Share the pool between threads, get and give them up using the usual tools.

    Nearly all DBMS packages support multiple simultaneous access, let the server do its work, and don't worry about it.

    You do want to minimize the "database clanks" in your application. Expect that as your load grows, you will have a separate sever for your DBMS, and thus you will make a network transfer for each function, two actually, one to, and one to return the results. Don't do this clanking any more than you must.

  5. #5
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Quote Originally Posted by fishtoprecords View Post
    You do want to minimize the "database clanks" in your application. Expect that as your load grows, you will have a separate sever for your DBMS, and thus you will make a network transfer for each function, two actually, one to, and one to return the results. Don't do this clanking any more than you must.
    If, by "reduce database clanks" you mean one of the following:

    1. Use a connection pool so that connections are created beforehand, rather than on the fly.

    2. Don't retreive a resultset, parse it, then place another query based on the results when a subquery and/or join could do the work.

    3. Something along the lines as above.

    Then I agree, wholeheartedly.

    If you mean by sharing statements, or something similar (except connections, which can usually be used simultaneaously, although a pool would prevent you having to do this, as sharing a connection can have side effects, as in with the use of commit and rollback when simultaneous update transactions are taking place), then I whole-heartedly disagree.

    But I believe you meant one the first few examples, just clarifying the statement for the OP and others.

  6. #6
    markw8500's Avatar
    markw8500 is offline Senior Member
    Join Date
    Jul 2008
    Location
    Pennsylvania, USA
    Posts
    136
    Rep Power
    0

Similar Threads

  1. help me need to understand queries
    By hossainsadd in forum JDBC
    Replies: 1
    Last Post: 05-26-2008, 01:02 AM
  2. queries regarding SWT
    By ravian in forum New To Java
    Replies: 0
    Last Post: 11-26-2007, 04:06 PM
  3. some queries
    By Feng in forum New To Java
    Replies: 7
    Last Post: 11-24-2007, 09:10 PM
  4. Statement or Prepared Statement ?
    By paty in forum JDBC
    Replies: 3
    Last Post: 08-01-2007, 05:45 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
  •