Introduction
The JDBC interface enables Java programmers to access Mimer SQL data sources and the Mimer JDBC implementation exposes a rich set of features to a Java programmer. Like most other interfaces, APIs or languages for that matter, JDBC often offers more than one way of implementing things. For optimal performance, you need to select the correct implementation depending on your requirements.
This article describes the benefits of using PreparedStatement rather than Statement objects.
For more information on the Mimer JDBC driver, please see the Mimer JDBC Driver Guide.
Avoid Compilations
Compiling SQL-queries is expensive. First of all, SQL is a complex language and this makes it hard to parse. Also, each compilation involves checking that the user has access to the tables and columns used in the query. Before the compilation is completed, the query is optimized. The compiler needs to find the optimal search path for queries and statements in general.
The nature of the Statement object is to compile once for each execution, while the PreparedStatement object compile is assigned a statement at object creation which is compiled only once.
Parameter Markers Enable Statement Reuse
Since PreparedStatement objects are compiled once and executed many times, you need a way of applying parameters to the statement. You can do this by using parameter markers.
In the following example, the question mark indicates the position of a parameter, and its value is supplied at execution time:
select salary from salaries where name=?
The Mimer SQL compiler stores compiled statements in a pool, the statements are reused whenever the compiler preprocessor can determine that an identical statement exists in the reuse pool. By using parameter markers, the number of statements decreases and the possibility of reuse increases. Compare this to the Statement object case where several different SQL-statements are compiled.
For example:
select SALARY from SALARIES where NAME=’Bob’ select SALARY from SALARIES where NAME=’Arnold’ select SALARY from SALARIES where NAME=’John’
Concatenating Parameters
Let’s say you want to search the SALARIES table for Bob’s salary. If you use Statement objects, you need to construct an SQL-string with the name Bob.
For example:
class myClass {
long getSalary(Statement stmt,String name) {
String sql;
long salary;
sql = “select SALARY from SALARIES where NAME=’”+name+”’”;
ResultSet rs = stmt.executeQuery(sql);
rs.next();
salary = rs.getLong(1);
rs.close();
return salary;
}
}
With a PreparedStatement, you don’t have to bother with string concatenation. Neither do you have to bother with SQL escape characters or quotes in the name that would invalidate the SQL-statement.
The following is an example of a PreparedStatement:
class myClass {
PreparedStatement pstmt;
myClass() {
pstmt = con.prepareStatement(“select SALARY from SALARIES where NAME=?”);
}
long getSalary(String name) {
pstmt.setString(1,name);
ResultSet rs = pstmt.executeQuery();
rs.next();
salary = rs.getLong(1);
rs.close();
return salary;
}
}
Another advantage of the PreparedStatement solution is that it avoids unnecessary string concatenations. For performance reasons, Java programmers should avoid string concatenations whenever possible since they involve allocating memory and creating new objects.
Security Issues
Take another look at the examples above. In the Statement object example there is no check of the validity of the name input parameter. A malicious user might be able to submit a name that includes valid SQL-statement syntax. Entering the name Bob’ or ’’=’ could open up the entire salary table to the user.
When inserting parameters into SQL-strings, you must make sure that the inserted parameters follow a strict syntax. In the case above, no quotes are allowed. If quotes in the name are a requirement, for example O’Brien, you have to be even more careful.
Using PreparedStatement objects, you don’t have these worries. The parameter data is sent separately to the server. At the server, the parameter data is sent directly as input data to the compiled statement.
No Performance Penalties
Performance doesn’t suffer when you use PreparedStatements, even for the simplest SQL-statements. The Mimer SQL server must compile statements either way. Parameter data (if any) must be transferred and processed, either as strings inserted into the SQL-statement, or separately. In some situations, Statement objects will perform as well as PreparedStatement objects but, in the majority of situations, PreparedStatement objects will perform better – as well as being easier for you.
So, in which situations can you expect Statement objects to perform as well as PreparedStatement objects?
- When users are allowed to enter ad-hoc queries directly.
- When the application builds new SQL-statements over and over again. OLAP-cube implementations are good examples of this.
- When short and uncomplicated queries without parameter data, for example:
select MYCOLUMN from MYTABLE - In data definition language (DDL) statements.
Conclusion
Use PreparedStatement objects whenever possible.
