Few basic questions related to servlets

[abctech]

Helo experts,

I'm working on my first ever web application and I have some basic questions related to servlets if I may,

I have 5-6 Java statements and preparedstateme nts in my servlet to execute a number of MySQL queries for different tables, do I need to open a new connection to the database for each query or can I just create one connection and use it for all the queries?

Also, if I'm using a preparedstateme nt and resultset for a particular query, then after closing them both can I again reuse them for another query in the same piece of code or is it better to create a new statement/preparedstateme nt object for every different query and close all the resultsets and statements etc in the end of the doGet()/doPost() method?

I've been told to not declare a preparedstateme nt or a statement as global since that has something to do with multithreading, hence I create and use them in the doPost() or doGet(). What is the reason behind not declaring them as global if that program is going to be accessed by multiple users? Is it because the global copy will be shared by all or something else?

Does opening multiple connections on a database in the same program and using many statements/preparedstateme nts affect the performance in any way?

Why is it a good practice to close all the opened connections in destroy()? Will they still remain open even if the user closes the browser window(webpage) to which this servlet is linked?

[r035198x]

Naturally the nature of these questions may require you to read a text. Even then you'll find whole books devoted to these things so whatever responses we are going to give here will not be complete.

I have 5-6 Java statements and preparedstateme nts in my servlet to execute a number of MySQL queries for different tables, do I need to open a new connection to the database for each query or can I just create one connection and use it for all the queries?

You do not need to open a connection for each statement. Just use one connection

Also, if I'm using a preparedstateme nt and resultset for a particular query, then after closing them both can I again reuse them for another query in the same piece of code or is it better to create a new statement/preparedstateme nt object for every different query and close all the resultsets and statements etc in the end of the doGet()/doPost() method?

Create a prepared statement for each different query. You cannot create more than one resultsets. You can only have one resultset so you have to reuse that resultset.

Does opening multiple connections on a database in the same program and using many statements/preparedstateme nts affect the performance in any way?

Naturally perfomance is reduced when multiple connections are open. The extent of the effects depend on the database being used and in some cases the OS. Using many statements has just about as much effect as having more String declarations in a program. It is the connection to the database and the queries that are really of effect.

Why is it a good practice to close all the opened connections in destroy()? Will they still remain open even if the user closes the browser window(webpage) to which this servlet is linked?

Closing a page linked to a servlet does not do anything to the servlet unless the page submits on close. Destroy is called when the application is being stopped or when the server is stopped so the connections are closed on application exit rather than on page exit because we can have some other pages that might still be open and may want to connect to the database. If we had closed the connections on closing one of the pages, then we would have to create another connection everytime which is not efficient.

[abctech]

Thank you so much for your detailed response r035198x , it clears a lot of my doubts.

One thing though, you mentioned

Create a prepared statement for each different query. You cannot create more than one resultsets. You can only have one resultset so you have to reuse that resultset..

I dint quite understand this part, I'm posting a few lines of my program.Can you please tell me if I'm using the ResultSet in the right way?

[Please note that this is just how my code looks in general, I couldn't post the original code due to some work policies]

doPost()
{
   try
   {
       String save = null;
       save = request.getParameter("Save"); 
 
       String update = null;
       update = request.getParameter("Update"); 
 
       String delete = null;
       delete = request.getParameter("Delete"); 
 
       PreparedStatement pst = con.prepareStatement(selectBrID);
       pst.setString(1,a);
       ResultSet rs = pst.executeQuery();
 
       while(rs.next())
       {
    //some code 
       }
       rs.close();
       pst.close();
 
       if(save.equals(“Save”))//if user hits 'Save' on page execute this 'if'.
       {
          PreparedStatement pst1 = con.prepareStatement(selectName);
          pst1.setString(1,Name);
          ResultSet rs1 = pst1.executeQuery();
 
          PreparedStatement pst2 = con.prepareStatement(selectUsername);
          pst2.setString(1,Username);
          ResultSet rs2 = pst2.executeQuery();
 
          if(rs1.next() != rs1.isLast())
          {
            pw.println(“This Name already exists”);  
          }
          else if(rs2.next() != rs2.isLast())
          {
           pw.println(“This Username already exists”);
          }
          else
          {
           PreparedStatement pst3 = con.prepareStatement(insertStr);
           pst3.setString(1,Name);
           pst3.setString(2,Username);
           pst3.executeUpdate();    
          }
          rs1.close();
          pst1.close();
          rs2.close();
          pst2.close();
          pst3.close();
 
      }//’Save’ over
 
      if(update.equals(“Update”))//if user hits 'Update' on page execute this 'if'.  
      {
         //some PreparedStatements and ResultSets 
         if()
         {
            //Again some PreparedStatements and ResultSets 
         }
         else if()
         {
           //Again some PreparedStatements and ResultSets  
         }
         else
         {
            PreparedStatement pst5 = con.prepareStatement(updateStr);
            pst5.setString(1,Name);
            pst5.setString(2,Username);
            pst5.executeUpdate();        
         }
         //closing all the resultsets and pst’s.
      }//’Update’ over
 
      if(delete.equals(“Delete”))//if user hits 'Delete' on page execute this 'if'.
      {
         PreparedStatement pst6 = con.prepareStatement(deleteStr);
         pst6.setLong(1,UID);
         pst6.executeUpdate();    
         pst6.close();
      }//’Delete’ over
   }
   catch(Exception e)
   {
       System.out.println(e);
   }
 }//doPost() over

The a, Name, Username strings are coming from the webpage. And depending on what button the user has clicked(Save/Delete/Update) the control goes into the appropriate 'if' and executes the queries there. I have created and used just one Connection(con) as you suggested but can I reuse the Prepared statements again since at one time the control shall enter & execute just one of the 3 main-if's.

And what about the Result sets, I'm creating a different one for every PreparedStateme nt, is it incorrect? Can you please tell me a little about how a Result set works?

[r035198x]

Thank you so much for your detailed response r035198x , it clears a lot of my doubts.

One thing though, you mentioned

I dint quite understand this part, I'm posting a few lines of my program.Can you please tell me if I'm using the ResultSet in the right way?

[Please note that this is just how my code looks in general, I couldn't post the original code due to some work policies]

doPost()
{
try
{
String save = null;
save = request.getParameter("Save"); 
 
String update = null;
update = request.getParameter("Update"); 
 
String delete = null;
delete = request.getParameter("Delete"); 
 
PreparedStatement pst = con.prepareStatement(selectBrID);
pst.setString(1,a);
ResultSet rs = pst.executeQuery();
 
while(rs.next())
{
    //some code 
}
rs.close();
pst.close();
 
if(save.equals(“Save”))//if user hits 'Save' on page execute this 'if'.
{
PreparedStatement pst1 = con.prepareStatement(selectName);
pst1.setString(1,Name);
ResultSet rs1 = pst1.executeQuery();
 
PreparedStatement pst2 = con.prepareStatement(selectUsername);
pst2.setString(1,Username);
ResultSet rs2 = pst2.executeQuery();
 
if(rs1.next() != rs1.isLast())
{
pw.println(“This Name already exists”); 
}
else if(rs2.next() != rs2.isLast())
{
pw.println(“This Username already exists”);
}
else
{
PreparedStatement pst3 = con.prepareStatement(insertStr);
pst3.setString(1,Name);
pst3.setString(2,Username);
pst3.executeUpdate();    
}
rs1.close();
pst1.close();
rs2.close();
pst2.close();
pst3.close();
 
}//’Save’ over
 
if(update.equals(“Update”))//if user hits 'Update' on page execute this 'if'. 
{
//some PreparedStatements and ResultSets 
if()
{
//Again some PreparedStatements and ResultSets 
}
else if()
{
//Again some PreparedStatements and ResultSets 
}
else
{
PreparedStatement pst5 = con.prepareStatement(updateStr);
pst5.setString(1,Name);
pst5.setString(2,Username);
pst5.executeUpdate();        
}
//closing all the resultsets and pst’s.
}//’Update’ over
 
if(delete.equals(“Delete”))//if user hits 'Delete' on page execute this 'if'.
{
PreparedStatement pst6 = con.prepareStatement(deleteStr);
pst6.setLong(1,UID);
pst6.executeUpdate();    
pst6.close();
}//’Delete’ over
}
catch(Exception e)
{
System.out.println(e);
}
}//doPost() over

The a, Name, Username strings are coming from the webpage. And depending on what button the user has clicked(Save/Delete/Update) the control goes into the appropriate 'if' and executes the queries there. I have created and used just one Connection(con) as you suggested but can I reuse the Prepared statements again since at one time the control shall enter & execute just one of the 3 main-if's.

And what about the Result sets, I'm creating a different one for every PreparedStateme nt, is it incorrect? Can you please tell me a little about how a Result set works?

This way should probably work. What I meant was you cannot open two resultsets at the same time. But better is declaring one resultset object before the try and intitializing it to null, then instantiate it differently for each of the if statements so that you use only one resultset variable.

You should close your connections and resultsets in the finally close so you are guaranteed they will be closed

May I say also that

 String save = null; 
save = request.getParameter("Save"); 
 
 

is the same as

 String save = request.getParameter("Save"); 

[abctech]

Thanks for all your suggestions, I incorporated them and its working perfectly.

Earlier I was getting a lot of null pointer exceptions and I couldn't locate them hence I explicitly declared all objects as null ,thats why I was saying

String save = null; 
save = request.getParameter("Save"); 

I've made it String save = request.getPara meter("Save"); now.Thanks.

you cannot open two resultsets at the same time

Can't open two resultset objects on the same table or any two resultset objects at one time?

I have some nested loops in which I need to fire some queries on multiple tables thus opening multiple resultsets, i.e something as foll -

if (s1.equals("something"))
{                    
    for (int j=0;j<s16.length;j++)
    {
    PreparedStatement pst1 = con.prepareStatement(selectStr);
    pst1.setString(1,Name);
    ResultSet rs1 = pst1.executeQuery();
    while (rs1.next())
    {
                     long a = rs1.getLong(1);
                     int b = rs11.getInt(2);
 
                     PreparedStatement pst2 =con.prepareStatement(select1Str);
                     pst2.setLong(1,a);
                     pst2.setInt(2,b);
                     ResultSet rs2  = pst2.executeQuery();
                     while(rs2.next())
                     {    
                          //some code
                     }    
    }
   }                        
}//if over    

Will opening rs1 and rs2 on the same/different table at the same time create any discrepancies in the output?

Also can I declare the PreparedStateme nt too outside the try-block and intialize it to null and then reuse the same in each of my 3 main-if's just like you'd suggested regarding the result sets?

[r035198x]

Thanks for all your suggestions, I incorporated them and its working perfectly.

Earlier I was getting a lot of null pointer exceptions and I couldn't locate them hence I explicitly declared all objects as null ,thats why I was saying

String save = null; 
save = request.getParameter("Save"); 

I've made it String save = request.getPara meter("Save"); now.Thanks.


Can't open two resultset objects on the same table or any two resultset objects at one time?

I have some nested loops in which I need to fire some queries on multiple tables thus opening multiple resultsets, i.e something as foll -

if (s1.equals("something"))
{                    
for (int j=0;j<s16.length;j++)
{
    PreparedStatement pst1 = con.prepareStatement(selectStr);
    pst1.setString(1,Name);
    ResultSet rs1 = pst1.executeQuery();
    while (rs1.next())
    {
long a = rs1.getLong(1);
int b = rs11.getInt(2);
 
PreparedStatement pst2 =con.prepareStatement(select1Str);
pst2.setLong(1,a);
pst2.setInt(2,b);
ResultSet rs2 = pst2.executeQuery();
while(rs2.next())
{    
//some code
}    
    }
}                        
}//if over    

Will opening rs1 and rs2 on the same/different table at the same time create any discrepancies in the output?

Also can I declare the PreparedStateme nt too outside the try-block and intialize it to null and then reuse the same in each of my 3 main-if's just like you'd suggested regarding the result sets?

That will probably not work. You will get something like IllegalStateExc eption. Are you not able to change your design so that you have only one resulset open at a time?

[abctech]

Are you not able to change your design so that you have only one resulset open at a time?

Well, I don’t really understand how I can work around my code to avoid opening more than one resultsets at a time since as you can see in my previous post based on every single record of rs1 the query is fired and a fresh rs2 is obtained every time, then one by one the records of rs2 are retrieved and processed.

Just so you know rs1 is representing a different table and rs2 a different one, will using them in the above way still be incorrect?

Another portion of my program requires opening 2 resultsets on the same table at the same time, it’s like this:-

String selectNameStr = “Select * from abc where Name = ?;” ;
String selectUsernameStr = “Select * from abc where Username = ?;” ;
 
PreparedStatement pst1 = con.prepareStatement(selectNameStr,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
pst1.setString(1,FullName);
ResultSet rs1 = pst1.executeQuery();
 
PreparedStatement pst2 = con.prepareStatement(selectUsernameStr,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);                    
pst2.setString(1,Username);    
ResultSet rs2  = pst2.executeQuery();
 
if (rs1.next() != rs1.isLast()) 
{
     //some code
}
else if (rs2.next() != rs2.isLast())
{
      //some code
}
else
{
      PreparedStatement pst3 = con.prepareStatement(updateStatementStr);
      // some code and some more queries on table ‘abc’ .
}

Will this code too give erroneous output since again rs1 and rs2 etc are fetching records from the same table ‘abc’ at one time?

My program is incomplete and very vast and I still have to compile it, but I want to take care that I approach it in the correct manner so that later I don’t have to spend too much time rectifying it. All your assistance much appreciated r035198x.

[r035198x]

Well, I don’t really understand how I can work around my code to avoid opening more than one resultsets at a time since as you can see in my previous post based on every single record of rs1 the query is fired and a fresh rs2 is obtained every time, then one by one the records of rs2 are retrieved and processed.

Just so you know rs1 is representing a different table and rs2 a different one, will using them in the above way still be incorrect?

Another portion of my program requires opening 2 resultsets on the same table at the same time, it’s like this:-

String selectNameStr = “Select * from abc where Name = ?;” ;
String selectUsernameStr = “Select * from abc where Username = ?;” ;
 
PreparedStatement pst1 = con.prepareStatement(selectNameStr,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
pst1.setString(1,FullName);
ResultSet rs1 = pst1.executeQuery();
 
PreparedStatement pst2 = con.prepareStatement(selectUsernameStr,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);                    
pst2.setString(1,Username);    
ResultSet rs2 = pst2.executeQuery();
 
if (rs1.next() != rs1.isLast()) 
{
//some code
}
else if (rs2.next() != rs2.isLast())
{
//some code
}
else
{
PreparedStatement pst3 = con.prepareStatement(updateStatementStr);
// some code and some more queries on table ‘abc’ .
}

Will this code too give erroneous output since again rs1 and rs2 etc are fetching records from the same table ‘abc’ at one time?

My program is incomplete and very vast and I still have to compile it, but I want to take care that I approach it in the correct manner so that later I don’t have to spend too much time rectifying it. All your assistance much appreciated r035198x.

You better create arraylists to hold the data temporarily because opening two resultsets at the same time will not work It does not matter if they have data from different tables.

[abctech]

You better create arraylists to hold the data temporarily because opening two resultsets at the same time will not work It does not matter if they have data from different tables.

Okay,
I will do that then.Thanks a lot for all your inputs !!!

[r035198x]

Okay,
I will do that then.Thanks a lot for all your inputs !!!

Would be easier if you have classes for each of the tables you are getting data from, then you can retrieve the data into objects of that class and strore the objects themselves in the arraylist.