mb2297
asked on
PreparedStatements over Servlet
Hello all,
I'm using a Servlet to access MySQL with JDBC. Most of my queries are stored inside the servlet and executed as PreparedStatements. However, I'm not sure how best to store and execute them.
For example, the Servlet now looks something like this:
public class TestServlet extends HttpServlet {
String myStmtSQL = "Select * from TestTable as TestQuery";
public String processRequest() {
//define a mysql connection
Connection con = null;
//try to connect
try {
Class.forName("com.mysql.j
con = DriverManager.getConnectio
if(!con.isClosed()) {
System.out.println("connec
}
} catch(Exception e) {
System.err.println("could not connect to mysql: " + e.getMessage());
}
//now con is defined, we can create the statements
PreparedStatement myStmt = con.prepareStatement(myStm
//and execute them
myStmt.execute();
//get string from resultset etc.
//now disconnect from mysql...
//etc.
}
}
I'm storing the query as a string within the Servlet because the connection con is defined until someone calls processRequest(). Is this the right way of doing things? Servlets don't have a constructor, so I have to put the connection inside some method.
In addition, when is TestQuery still available as a Query? If my understanding is correct, the purpose of a PreparedStatement is that it only needs to get compiled once, and after that is available for faster processing. Does using them via a Servlet mean they're just recompiled every time someone makes a request?
Hope that all makes sense - my understanding of this topic is pretty shakey.
Thanks,
Matt.
I'm using a Servlet to access MySQL with JDBC. Most of my queries are stored inside the servlet and executed as PreparedStatements. However, I'm not sure how best to store and execute them.
For example, the Servlet now looks something like this:
public class TestServlet extends HttpServlet {
String myStmtSQL = "Select * from TestTable as TestQuery";
public String processRequest() {
//define a mysql connection
Connection con = null;
//try to connect
try {
Class.forName("com.mysql.j
con = DriverManager.getConnectio
if(!con.isClosed()) {
System.out.println("connec
}
} catch(Exception e) {
System.err.println("could not connect to mysql: " + e.getMessage());
}
//now con is defined, we can create the statements
PreparedStatement myStmt = con.prepareStatement(myStm
//and execute them
myStmt.execute();
//get string from resultset etc.
//now disconnect from mysql...
//etc.
}
}
I'm storing the query as a string within the Servlet because the connection con is defined until someone calls processRequest(). Is this the right way of doing things? Servlets don't have a constructor, so I have to put the connection inside some method.
In addition, when is TestQuery still available as a Query? If my understanding is correct, the purpose of a PreparedStatement is that it only needs to get compiled once, and after that is available for faster processing. Does using them via a Servlet mean they're just recompiled every time someone makes a request?
Hope that all makes sense - my understanding of this topic is pretty shakey.
Thanks,
Matt.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Todd,
I now have a JDBC DataSource setup working. At the moment, I embed the queries in Strings within the Servlet, and then create a PreparedStatement from that String once I've got a connection from the DataSource.
However, this doesn't seem like the correct way of doing things. Isn't there a way I can compile the PreparedStatements once, when the Servlet initializes, and then have each 'connection' use them? At the moment, it seems to recompile them every time someone connects.
The reason I did it the way I have, is that a PreparedStatement takes a Connection object in it's constructor - this seems to force the method that I've used, and therefore undermine the point of PreparedStatements in the first place. Am I confused somehow?
Thanks,
Matt.
I now have a JDBC DataSource setup working. At the moment, I embed the queries in Strings within the Servlet, and then create a PreparedStatement from that String once I've got a connection from the DataSource.
However, this doesn't seem like the correct way of doing things. Isn't there a way I can compile the PreparedStatements once, when the Servlet initializes, and then have each 'connection' use them? At the moment, it seems to recompile them every time someone connects.
The reason I did it the way I have, is that a PreparedStatement takes a Connection object in it's constructor - this seems to force the method that I've used, and therefore undermine the point of PreparedStatements in the first place. Am I confused somehow?
Thanks,
Matt.
It really is a client-side Prepared Statement you are using - not a server-side Prepared Statement. At the MySQL server level, the query is going to be parsed again each time it is executed, regardless of whether you use the same Java PreparedStatement object or not. Reusing a single PreparedStatement will likely have some (small) performance benefits at the Java application layer (not at the database layer), but is really only useful in the context where you have to perform many similar queries within a single process. It really is not good for increasing performance across invocations of a servlet, which is what I think you are talking about here.
So, what good are Prepared Statements? The security they offer is key.
So, what good are Prepared Statements? The security they offer is key.
ASKER
Yes ok, I see your point.
I have another problem, which I had assumed could be rectified by using PreparedStatements in the correct way, but since I'm already doing that I guess I'll have to solve it some other way.
I think the problem stems from not closing or purging the PreparedStatements correctly. Following is an overview of the setup and problem - it looks daunting but I don't think it will be too hard to solve:
The Servlet accepts an XML document, which defines a number of PreparedStatements to run and their parameters. The Servlet parses this XML file and builds the PreparedStatements appropriately, and then adds them to a List.
One of the PreparedStatements has a variable number of parameters (anywhere from 1 to 51), so it just has a long list of ?s, and I fill in the ones that need values. All the remaining ones get given a value which is unfindable in the database (in this case, it's set to the integer -1).
Another function iterates over the list of PreparedStatements, executing them and parsing the results into another XML document which is returned to the caller. I call PreparedStatement.close() at the same time as I call ResultSet.close(), when the query has completed.
This seems to work ok the first time it gets called. But in subsequent calls the database query doesn't return what it should. A couple of examples:
PreparedStatement:
SELECT * FROM (SELECT JOBS.JOB_ID, @rowid := COALESCE( @rowid +1, 0 ) WHERE TECHNIQUE=? AS rowid FROM JOBS) AS myquery WHERE rowid IN (?,?,?,?,?,?,?,?,?,?,?,?,?
Call 1 on tech1 creates:
SELECT * FROM (SELECT JOBS.JOB_ID, @rowid := COALESCE( @rowid +1, 0 ) WHERE TECHNIQUE='tech1' AS rowid FROM JOBS) AS myquery WHERE rowid IN (0,1,2,3,4,5,6,7,8,9,-1,-1
When this runs, it brings back the 1st 10 rows from jobs with TECHNIQUE = tech1, which is correct.
Call 2 on tech1 creates:
SELECT * FROM (SELECT JOBS.JOB_ID, @rowid := COALESCE( @rowid +1, 0 ) WHERE TECHNIQUE='tech1' AS rowid FROM JOBS) AS myquery WHERE rowid IN (10,11,12,13,14,15,16,17,1
If I run call 2 before call 1, this works as it should. If I run it afterwards, I get no records in the RecordSet (while(RecordSet.next() never runs). Similarly, if I run call 1 after call 2, call 1 comes back with no records in the RecordSet.
If I choose a technique with less than 10 jobs entries, I get a more enlightening response. Say there are 6 entries in JOBS with TECHNIQUE = 'tech2'
Call 1 on tech2 creates:
SELECT * FROM (SELECT JOBS.JOB_ID, @rowid := COALESCE( @rowid +1, 0 ) WHERE TECHNIQUE='tech2' AS rowid FROM JOBS) AS myquery WHERE rowid IN (0,1,2,3,4,5,6,7,8,9,-1,-1
This call correctly brings back the 1st 6 results from JOBS with TECHNIQUE = tech2 . Now if I run Call 1 on tech1, I get results 7-10 from JOBS with TECHNIQUE = 'tech1'.
If I run any calls after this, I get nothing. So basically I get 10 'slots' in my PreparedStatement, and once I've used them all I get nothing. This seems to me like the PreparedStatement is not getting refreshed between calls. What I am doing wrong?
Thanks in advance,
Matt.
I have another problem, which I had assumed could be rectified by using PreparedStatements in the correct way, but since I'm already doing that I guess I'll have to solve it some other way.
I think the problem stems from not closing or purging the PreparedStatements correctly. Following is an overview of the setup and problem - it looks daunting but I don't think it will be too hard to solve:
The Servlet accepts an XML document, which defines a number of PreparedStatements to run and their parameters. The Servlet parses this XML file and builds the PreparedStatements appropriately, and then adds them to a List.
One of the PreparedStatements has a variable number of parameters (anywhere from 1 to 51), so it just has a long list of ?s, and I fill in the ones that need values. All the remaining ones get given a value which is unfindable in the database (in this case, it's set to the integer -1).
Another function iterates over the list of PreparedStatements, executing them and parsing the results into another XML document which is returned to the caller. I call PreparedStatement.close() at the same time as I call ResultSet.close(), when the query has completed.
This seems to work ok the first time it gets called. But in subsequent calls the database query doesn't return what it should. A couple of examples:
PreparedStatement:
SELECT * FROM (SELECT JOBS.JOB_ID, @rowid := COALESCE( @rowid +1, 0 ) WHERE TECHNIQUE=? AS rowid FROM JOBS) AS myquery WHERE rowid IN (?,?,?,?,?,?,?,?,?,?,?,?,?
Call 1 on tech1 creates:
SELECT * FROM (SELECT JOBS.JOB_ID, @rowid := COALESCE( @rowid +1, 0 ) WHERE TECHNIQUE='tech1' AS rowid FROM JOBS) AS myquery WHERE rowid IN (0,1,2,3,4,5,6,7,8,9,-1,-1
When this runs, it brings back the 1st 10 rows from jobs with TECHNIQUE = tech1, which is correct.
Call 2 on tech1 creates:
SELECT * FROM (SELECT JOBS.JOB_ID, @rowid := COALESCE( @rowid +1, 0 ) WHERE TECHNIQUE='tech1' AS rowid FROM JOBS) AS myquery WHERE rowid IN (10,11,12,13,14,15,16,17,1
If I run call 2 before call 1, this works as it should. If I run it afterwards, I get no records in the RecordSet (while(RecordSet.next() never runs). Similarly, if I run call 1 after call 2, call 1 comes back with no records in the RecordSet.
If I choose a technique with less than 10 jobs entries, I get a more enlightening response. Say there are 6 entries in JOBS with TECHNIQUE = 'tech2'
Call 1 on tech2 creates:
SELECT * FROM (SELECT JOBS.JOB_ID, @rowid := COALESCE( @rowid +1, 0 ) WHERE TECHNIQUE='tech2' AS rowid FROM JOBS) AS myquery WHERE rowid IN (0,1,2,3,4,5,6,7,8,9,-1,-1
This call correctly brings back the 1st 6 results from JOBS with TECHNIQUE = tech2 . Now if I run Call 1 on tech1, I get results 7-10 from JOBS with TECHNIQUE = 'tech1'.
If I run any calls after this, I get nothing. So basically I get 10 'slots' in my PreparedStatement, and once I've used them all I get nothing. This seems to me like the PreparedStatement is not getting refreshed between calls. What I am doing wrong?
Thanks in advance,
Matt.
ASKER
I found the problem with the above, but have forgotten what it was - something dumb from my end.
Thanks for the JNDI info - works like a charm.
Thanks for the JNDI info - works like a charm.
ASKER
Thanks for the reply. I'm using Tomcat and the JNDI support is there.
I understand the concept of connection pooling, but I'm struggling a little to wrap my head around an 'application scope' DataSource. I think I really need an example of using one with Servlets. In addition, what's the difference between 'DataSource' and JNDI Lookup? I looked up JNDI and came up with a bunch of results for 'JNDI DataSources', which doesn't really help differentiate them.
I understand the security implications of using PreparedStatements. My existing, naive, solution just passes SQL as a String from the client to the server, and security was my main motivation to move it server-side.
Regarding your server- vs driver-side arguement: doesn't the sample servlet above use server-side preparedstatements? I know they are created from strings, but there is no client involvement. If there was, it would be using the proper (secure) method, whereby the preparedstatement reads something like:
"SELECT * FROM products WHERE productid = ?"
When I'd use statement.setInt(1,someval
If you know of an example of Servlets using an 'application scope' (JNDI)DataSource, then I'd really appreciate the link.
Thanks,
Matt.