• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2513
  • Last Modified:

jsp-- prepareStatement("SELECT...") and executeQuery("select ..")

Hi, experts,
If I'm right, basiclly there are two ways to do a sql query.


One is :
Connection sqlConn = null;
Statement selectStmt  = null;
ResultSet sqlRst = null;

sqlRst = selectStmt.executeQuery("select filePath from fileTable");
-----------------------------
another one is :
PreparedStatement selectStmt  = null;

selectStmt=sqlConn.prepareStatement("SELECT filePath from fileTable");
sqlRst=selectStmt.executeQuery();

what's the pros and cons for these two methods

Thanks for your attention.


0
lilyyan
Asked:
lilyyan
  • 3
  • 3
  • 2
  • +2
2 Solutions
 
objectsCommented:
for that type of statement there is no advantage to using a preparedstatement.
0
 
koppchaCommented:
Ok
When you want to execute same query with different values that is where you can find the advantage of the prepared statements.
you can see this example from java site.Here they want to execute the same SQL but with different values so you can achieve this by prepared statements.

String updateString = "update COFFEES " +
                  "set SALES = ? where COF_NAME like ?";
updateSales = con.prepareStatement(updateString);
int [] salesForWeek = {175, 150, 60, 155, 90};
String [] coffees = {"Colombian", "French_Roast", "Espresso",
                 "Colombian_Decaf", "French_Roast_Decaf"};
int len = coffees.length;
for(int i = 0; i < len; i++) {
            updateSales.setInt(1, salesForWeek[i]);
            updateSales.setString(2, coffees[i]);
            updateSales.executeUpdate();
      }
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jessegivyCommented:
koppcha got it right, just thought I'd add that if you use the prepared statement it seems to handle most types automatically which is a fairly huge plus.  for instance I have found that since all variables that are coming from a web page in the request are strings and if you use the setString method it automatically inserts the single quotes in your statement.  On the other hand if the parameter is an integer that came from a string it has no problem recognizing this and doesn't insert the quotes, which would throw an error.

Pretty swift!!!
0
 
lilyyanAuthor Commented:
Hi koppcha

Is there something missing in your above code?

for(int i = 0; i < len; i++) {
          updateSales = con.prepareStatement(updateString); // the line should be here ?
          updateSales.setInt(1, salesForWeek[i]);
          updateSales.setString(2, coffees[i]);
          updateSales.executeUpdate();
     }

--------------------
Also I'm guessing I can use same method in Statement:

Statement updateSales=null;
Connection sqlConn = null;

updateSales =sqlConn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY);

for(int i = 0; i < len; i++) {
                updateSales.executeQuery("update COFFEES " +
                "set SALES =" + salesForWeek[i] +  "where COF_NAME like" +\"+coffees[i] +\"  );
             
}

Is this correct?  

0
 
lilyyanAuthor Commented:
update :

for(int i = 0; i < len; i++) {
                updateSales.executeQuery("update COFFEES " +
                "set SALES =" + salesForWeek[i] +  "where COF_NAME like" +"\""+coffees[i] +"\""  );
             
}

0
 
koppchaCommented:
>Is there something missing in your above code?

for(int i = 0; i < len; i++) {
          updateSales = con.prepareStatement(updateString); // the line should be here ?
          updateSales.setInt(1, salesForWeek[i]);
          updateSales.setString(2, coffees[i]);
          updateSales.executeUpdate();
     }
You do not have to keep the prepare statement in the loop.The syntax of the prepare statement is cheched only once after that every time you plug in the values and keep execute the same query with new values.By keep that in loop you are preparing the same statement again and again which is not needed.

>updateSales =sqlConn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY);

for(int i = 0; i < len; i++) {
                updateSales.executeQuery("update COFFEES " +
                "set SALES =" + salesForWeek[i] +  "where COF_NAME like" +\"+coffees[i] +\"  );
             
}

you can do this but some times you may get in to problems when you have single or double quotes in the strings by themselves which will be taken care in case of place holders.This form is just like any other SQL statement.If you consider the performance in case of preparedstatement sttement syntax is checked once and in some databases query plan is stored in the cache so that next time when you keep value in place holders(? places) it will use the same query plan but in the form you mentioned everytime the query executes it has to first check the syntax,parse ,build the query plan and then execute which clearly hinders the performance.

0
 
KuldeepchaturvediCommented:
basically your code with preparedStatement wd be written as

updateSales = con.prepareStatement(update COFFEES set SALES =? where COF_NAME like ?"); // the line should be here ?
for(int i = 0; i < len; i++) {
               updateSales.setInt(1, salesForWeek[i]);
          updateSales.setString(2, coffees[i]);
          updateSales.executeUpdate();
     }
0
 
lilyyanAuthor Commented:
Thanks for your all attention. I got some idea.
0
 
objectsCommented:
no worries :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now