JDBC Performance Tuning
1.0 Introduction
2.0 Auto-Commit
3.0 Closing JDBC objects
4.0 Optimization with Statement
4.1 Choose right Statement interface
4.2 Do batch update
4.3 Do batch retrieval using Statement
4.4 Close Statement when finished
5.0 Optimization with ResultSet
5.1 Do batch retrieval using ResultSet
5.2 Setup proper direction of processing rows
5.3 Use proper getxxx() methods
5.4 Close ResultSet when finished
6.0 Optimization with SQL Query
1.0 Introduction
Performance is usually considered an issue at the end of a development cycle when it should really be considered from the start. Often, a task called “performance tuning” is done after the coding is complete, and the end user of a program complains about how long it takes the program to complete a particular task.
Java Database Connectivity (JDBC) is used extensively by many Java applications. Getting database connection is very expensive. If your application requires database connections that are repeatedly opened and closed, this can become a significant performance issue. Now let us see how best we can optimize by clearing the bottlenecks and increase the performance.
2.0 Control transaction- Auto-Commit
In general, transaction represents one unit of work or bunch of code in the program that executes in it’s entirety or none at all. To be precise, it is all or no work. In JDBC, transaction is a set of one or more Statements that execute as a single unit.
public interface Connection {
boolean getAutoCommit();
void setAutoCommit(boolean autocommit);
void commit();
void rollback();
}
JDBC’s default mechanism for transactions:
By default in JDBC transaction starts and commits after each statement’s execution on a connection. That is the AutoCommit mode is true. Programmer need not write a commit() method explicitly after each statement.
Obviously this default mechanism gives good facility for programmers if they want to execute a single statement. But it gives poor performance when multiple statements on a connection are to be executed because commit is issued after each statement by default, that in turn reduces performance by issuing unnecessary commits. The remedy is to flip it back to AutoCommit mode as false and issue commit() method after a set of statements execute, this is called as batch transaction. Use rollback() in catch block to rollback the transaction whenever an exception occurs in your program. The following code illustrates the batch transaction approach.
private static void updateRecordToTable() throws SQLException {
Connection dbConnection = null;
PreparedStatement pStmt1 = null;
PreparedStatement pStmt2 = null;
String updateEmployeSQL = "UPDATE EMPLOYEE SET USERNAME = ? WHERE USER_ID = ?";
String updateTableSQL = "UPDATE ACCOUNT SET SALARY = ? WHERE USER_ID = ?";
try {
dbConnection = getDBConnection();
dbConnection.setAutoCommit(false);
pStmt1 = dbConnection.prepareStatement(updateEmployeSQL);
pStmt1.setString(1, "Azarudin");
pStmt1.setInt(2, 2001);
pStmt1.executeUpdate();
pStmt2 = dbConnection.prepareStatement(updateTableSQL);
pStmt2.setInt(1, 23000);
pStmt2.setInt(2, 2001);
pStmt2.executeUpdate();
dbConnection.commit();
dbConnection.setAutoCommit(true);
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if (pStmt1 != null) {
pStmt1.close();
}
if (pStmt2 != null) {
pStmt2.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
This batch transaction gives good performance by reducing commit calls after each statement’s execution.
3.0 Explicitly closing JDBC objects
Oracle JDBC objects such as Connection, ResultSet, PreparedStatement andCallableStatement do not have finalizer methods. If you do not explicitly call the close() method for each of these objects as soon as you are done wit them, your programs are likely to run out of resources.
Close JDBC objects in finally block. In addition of closing, assign null value afterwards, so that it will be immediately eligible for garbage collection. Doing these things will keep your programs nice and tidy.
4.0 Optimization with Statement
Statement interface represents SQL query and execution and they provide number of methods and constants to work with queries. They also provide some methods to fine tune performance. Programmer may overlook these fine tuning methods that result in poor performance. The following are the tips to improve performance by using statement interfaces
1. Choose the right Statement interface
2. Do batch update
3. Do batch retrieval using Statement
4. Close Statement when finished
4.1 Choose right Statement interface
There are three types of Statement interfaces in JDBC to represent the SQL query and execute that query, they are Statement, PreparedStatement and CallableStatement.
Statement is used for static SQL statement with no input and output parameters, PreparedStatement is used for dynamic SQL statement with input parameters and CallableStatement is used for dynamic SQL satement with both input and output parameters, but PreparedStatement and CallableStatement can be used for static SQL statements as well. CallableStatement is mainly meant for executing stored procedures.
PreparedStatement gives better performance when compared to Statement because it is pre-parsed and pre-compiled by the database once for the first time and then onwards it reuses the parsed and compiled statement. Because of this feature, it significantly improves performance when a statement executes repeatedly, It reduces the overload incurred by parsing and compiling.
CallableStatement gives better performance when compared to PreparedStatement and Statement when there is a requirement for single request to process multiple complex statements. It parses and stores the stored procedures in the database and does all the work at database itself that in turn improves performance. But we loose java portability and we have to depend up on database specific stored procedures.
4.2 Do batch update
You can send multiple queries to the database at a time using batch update feature of statement objects this reduces the number of JDBC calls and improves performance. Here is an example of how you can do batch update.
statement.addBatch("Sql query1");
statement.addBatch("Sql query2");
statement.addBatch("Sql query3");
statement.executeBatch();Example Program:
// Hear is an example to show you how to insert few records in batch process, via JDBC PreparedStatement.
dbConnection.setAutoCommit(false);//commit trasaction manually String insertTableSQL = "INSERT INTO EMPLOYEE" + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES" + "(?,?,?,?)"; PreparedStatement = dbConnection.prepareStatement(insertTableSQL); preparedStatement.setInt(1, 789); preparedStatement.setString(2, "Azarudin"); preparedStatement.setString(3, "system"); preparedStatement.setTimestamp(4, getCurrentTimeStamp()); preparedStatement.addBatch(); preparedStatement.setInt(1, 790); preparedStatement.setString(2, "Nilafar"); preparedStatement.setString(3, "system"); preparedStatement.setTimestamp(4, getCurrentTimeStamp()); preparedStatement.addBatch(); preparedStatement.executeBatch(); dbConnection.commit();
All three types of statements have these methods to do batch update.
4.3 Do batch retrieval using Statement
You can get the default number of rows that is provided by the driver. You can improve performance by increasing number of rows to be fetched at a time from database using setFetchSize() method of the statement object.
Initially find the default size by using
Statement.getFetchSize(); and then set the size as per your requirement
Statement.setFetchSize(30);
Here it retrieves 30 rows at a time for all result sets of this statement.
4.4 Close Statement when finished
Close Statement object as soon as you finish working with that; it explicitly gives a chance to garbage collector to recollect memory as early as possible which in turn effects performance as in the following.
Statement.close();
5.0 Optimization with ResultSet
ResultSet interface represents data that contains the results of executing a select SQL Query and it provides a number of methods and constants to work with this data. It also provides methods to fine tune retrieval of data to improve performance. The following are the fine tuning tips to improve performance by using ResultSet interface.
1. Do batch retrieval using ResultSet
2. Set up proper direction for processing the rows
3. Use proper get methods
4. Close ResultSet when finished
5.1 Do batch retrieval using ResultSet
This is discussed earlier; anyhow repeated again.ResultSet interface also provides batch retrieval facility like Statement as mentioned above. It overrides the Statement behaviour. Initially find the default size by using ResultSet.getFetchSize(); and then set the size as per requirement
ResultSet.setFetchSize(50);
This feature significantly improves performance when you are dealing with retrieval of large number of rows like search functionality.
5.2 Setup proper direction of processing rows
ResultSet has the capability of setting the direction in which you want to process the results, it has three constants for this purpose, they are
FETCH_FORWARD, FETCH_REVERSE, FETCH_UNKNOWN
Initially find the direction by using
ResultSet.getFetchDirection(); and then set the direction accordingly
ResultSet.setFetchDirection(FETCH_REVERSE);
5.3 Use proper getxxx() methods
ResultSet interface provides a lot of getxxx() methods to get and convert database data types to java data types and is flexible in converting non feasible data types. For example,
getString(String columnName) returns java String object. columnName is recommended to be a VARCHAR OR CHAR type of database but it can also be a NUMERIC, DATE etc.
If you give non recommended parameters, it needs to cast it to proper java data type that is expensive. For example consider that you select a product’s id from huge database which returns millions of records from search functionality, it needs to convert all these records that is very expensive. So always use proper getxxx() methods according to JDBC recommendations. That numeric field data should retrieved as getInt() and varchar is to be retrieved as getString()etc.
5.4 Close ResultSet when finished
Close ResultSet object as soon as you finish working with ResultSet object even though Statement object closes the ResultSet object implicitly when it closes, closing ResultSet explicitly gives chance to garbage collector to recollect memory as early as possible because ResultSet object may occupy lot of memory depending on query.
ResultSet.close();
6.0 Optimization with SQL Query
This is one of the areas where programmers generally make a mistake If you give a query like:
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("select * from employee where name='Azar'");The returned result set contains all the columns data. you may not need all the column data and want only salary for Azar.
The better query is “select salary from employee where name=’Azar’”
It returns the required data and reduces unnecessary data retrieval.
Read More
Captcha validation in Java web applications
Captcha images are used to ensure that data are submitted by a human being and not by some kind of spam robot.
In this tutorial we will see how to use SimpleCaptcha API for generating image and audio captcha security codes.
 |
|
As a first step,we need to configure simpleCaptcha servlet in the web.xml:
<servlet> <servlet-name>StickyCaptcha</servlet-name> <servlet-class>nl.captcha.servlet.StickyCaptchaServlet</servlet-class> <init-param> <param-name>width</param-name> <param-value>250</param-value> </init-param> <init-param> <param-name>height</param-name> <param-value>75</param-value> </init-param> </servlet> <servlet-mapping> <servlet-name>StickyCaptcha</servlet-name> <url-pattern>/Captcha.png</url-pattern> </servlet-mapping>
Suppose now, that we want to use captcha in a registration form and we process user input in a servlet – SimpleCaptchaServlet- .The registration form could be:
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <title>Simple Captcha</title> </head> <body> <form action="SimpleCaptchaServlet"> Name:<input type="text" name="name"><br /> Surname:<input type="text" name="surname"><br /> Name:<input type="text" name="username"><br /> Password:<input type="password" name="password"><br /> Email:<input type="text" name="email"><br /> <img src="/Captcha.png" /> <input type="text" name="captchaAnswer" /> <input type="submit" value="Submit" /> </form> </body> </html>
In order now to validate that the above form is submitted by a human and not a computer is to compare the code that user has entered with the generated security code that is stored as session attribute by SimpleCaptcha servlet.
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import nl.captcha.*;
public class SimpleCaptchaServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public SimpleCaptchaTestServlet() {
super();
}
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
}
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
// get Captcha code from session
Captcha captcha = (Captcha) request.getSession().getAttribute(
Captcha.NAME);
// get security code submitted by user
String captchaAnswer = request.getParameter("captchaAnswer");
// compare security codes
if (!captcha.isCorrect(captchaAnswer)) {
// further process goes here
}
}
}
Read More
Why final variable in for-each loop does not act final?
public class EnhancedForLoop {
public static void main(String[] args) {
String[] alpha = { "A", "B", "C", "D" };
for (final String str : alpha) {
System.out.println(str);
}
}
}
Since the String str is delcared as final, this code should not compile. However, this is working fine. Why?
The enhanced for-loop as shown above is actually just as the following code:
public class EnhancedForLoop {
public static void main(String[] args) {
String[] alpha = { "A", "B", "C", "D" };
for (int i = 0; i < alpha.length; i++) {
final String str = alpha[i];
System.out.println(str);
}
}
}
That is, str is a local variable (local to the for-loop) and during each iteration of the loop a new different final local variable is created.
Read More
How to Retrieve Automatically Generated Keys in JDBC?
When we are inserting a record into the database table and the primary key is an auto-increment or auto-generated key, then the insert query will generate it dynamically. The below example shows how to get this key after insert statement. After perfoming executeUpdate() method on PreparedStatement, call getGeneratedKeys() method on PreparedStatement.
The getGeneratedKeys() provide a standard way to make auto-generated or identity column values available to an application that is updating a database table without a requiring a query and a second round-trip to the server. SQL Server allows only a single auto increment column per table.
The ResultSet that is returned by getGeneratedKeys method will have only one column, with the returned column name of GENERATED_KEYS.
If generated keys are requested on a table that has no auto increment column, the JDBC driver will return a null result set.
When you insert rows by executeUpdate or execute an INSERT statement or an INSERT within SELECT statement, you need to indicate that you will want to retrieve automatically generated key values. You do that by setting a flag in a Connection.prepareStatement, Statement.executeUpdate, or Statement.execute method call. The statement that is executed must be an INSERT statement or an INSERT within SELECT statement. Otherwise, the JDBC driver ignores the parameter that sets the flag.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCGetAutoIncKeys {
private static final String DBURL = "jdbc:oracle:thin:@<hostname>:<port num>:<DB name>";
private static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
static {
try {
Class.forName(DBDRIVER).newInstance();
}
catch (Exception e) {
e.printStackTrace();
}
}
private static Connection getConnection() {
Connection connection = null;
try {
connection = DriverManager.getConnection(DBURL, "USER", "Password");
}
catch (Exception e) {
e.printStackTrace();
}
return connection;
}
public static void main(String[] arg) {
PreparedStatement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
conn = getConnection();
stmt = conn.prepareStatement("DROP TABLE IF EXISTS EMPLOYEE");
stmt.executeUpdate();
stmt.executeUpdate("CREATE TABLE EMPLOYEE ("
+ "EMP_ID INT NOT NULL AUTO_INCREMENT, "
+ "name VARCHAR(64), dept VARCHAR(64),"
+ " salary INT, PRIMARY KEY (EMP_ID))");
String query = "INSERT INTO EMPLOYEE (name, dept, salary) values (?,?,?)";
stmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
stmt.setString(1, "Mahi");
stmt.setString(2, "Automobile");
stmt.setInt(3, 1000000);
stmt.executeUpdate();
rs = stmt.getGeneratedKeys();
if (rs != null && rs.next()) {
System.out.println("Generated Emp Id: " + rs.getInt(1));
}
rs.close();
}
catch (Exception e) {
e.printStackTrace();
}
finally {
if (rs != null) {
try
{
rs.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
if (stmt != null) {
try
{
stmt.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
}
}
Read More
PL/SQL Constants
As the name implies a constant is a value used in a PL/SQL Block that remains unchanged throughout the program. A constant is a user-defined literal value. You can declare a constant and use it instead of actual value.
General Syntax to declare a constant is:
constant_name CONSTANT datatype := VALUE;
- constant_name is the name of the constant i.e. similar to a variable name.
- The word CONSTANT is a reserved word and ensures that the value does not change.
- VALUE - It is a value which must be assigned to a constant when it is declared. You cannot assign a value later.
For example, to declare value of PI, you can write code as follows:
DECLARE
pi CONSTANT number := 3.141592654;
You must assign a value to a constant at the time you declare it. If you do not assign a value to a constant while declaring it and try to assign a value in the execution section, you will get a error. If you execute the below Pl/SQL block you will get error.
DECLARE
pi CONSTANT number(2);
BEGIN
pi := 3;
dbms_output.put_line (pi);
END;
Read More