Hello Friends !
I am using same statement object for executing various queries in to the program.
should i use same statement object for various queries or should i use different Statement
object for that.
we are using JDBC 2.0 and connection through system DSN. Is it sufficient for
concurrent database access
Between getConnection() and connection.close() in the same function, you can reuse a statement provided you close the statement and close the redulstSet (but dont close the connection) before reusing it .
However, you might consider putting each query in its own function (with its own getConnection() and close connection ) rather than perform all the queries in one function. It makes the code easier to understand.
However, if the various queries need to be put into the same transaction (updating, inserting) , put them in the same function,
Here's an example of a transaction using more than one query. You can remove the transaction logic if you dont need a transaction
public ArrayList myFunction(){
Connection conn=null;
PreparedStatement pstmt1= null;
ResultSet resultSet=null;
ArrayList list1;
try{
list1=new ArrayList();
conn= dataSource.getConnection();
conn.setAutoCommit(false);
pstmt1= conn.prepareStatement();
resultSet= pstmt1.executeQuery();
while(resultSet.next()){
arrayList.add(resultSet.getString("lastName");
}
//close statement and resultSet before reuse
if(pstmt1!=null)
pstmt1.close();
if(resultSet!=null)
resultSet.close()
pstmt1= conn.prepareStatement();
resultSet= pstmt1.executeUpdate();
conn.commit();
return arrayList;
} catch (SqlException e){
if(conn!=null)
conn.rollback();
e.printStackTrace();
} finally {
if(conn!=null)
conn.setAutoCommit(true);//must ensure this is done first because
//following lines might throw an error
if(rsultSet!=null)
resultSet.close();
if(pstmt1!=null)
pstmt1.close();
if(conn!=null)
conn.close();
}
}
}
Thanks George for replying
I have implemented cod you have given, it is working fine.
I am developing J2EE web based application along with JDBC 2.0 and more than 100 users accessing application Simultaneously.
Is JDBC 2.0 is caters to such kind of Application ? coz after few days count of users would b more than 400.
Lets say it takes 10milliSeconds from the opening of a connection in your function to closing the connection to complete that function's processing. If you have 400 users using your web page, most will spend 99.999% of the time looking at data on form rather than spending every second clicking the query or update button. Whats the probablity of all 400 clicking the query button all within the same 10milliSecond window of time? Virtually non existant. At most maybe 3(?) might click the button at the same 10milliSec interval. Therefore your connection pool should be set with a minimum of 3 size. Of course, if other applications use the same connection pool (the one tomcat provides via a dataSource tag in context.xml), then the pool size will have to be a bit larger since you may have 4000 users on line using all the programs.
George, please make sure you enclose posted code within code tags, see here: http://forum.java.sun.com/help.jspa?sec=formatting
A few comments:
1. Don't return concrete collection classes from your public functions, better to return the List interface, that way you can safely go ahead and change ArrayList to e.g. LinkedList without having to worry about fixing other code.
2. Your PreparedStatement doesn't query for anything and is thus a rather bad example.
3. conn.setAutoCommit(true) is not needed, have already commited everything.
4. You should wrap each of the x.close() statements in their own try/catch block so that each is guaranteed to be executed.
