Java JDBC best practices


In this tutorial we will learn JDBC best practices in java with example.


1. It is very common JDBC best practice to use Connection pooling in java. Connection pooling is the process where we maintain cache of database connections.



Why we need Connection Pooling? or why to use Connection Pooling?
Database Connections maintained in cache can be reused whenever request comes to connect with database. So, Connection Pooling reduces databse hits and improves application performance significantly. You must remember that database hit is a very costly operation and as much as possible you must try to avoid it.


2. It is very important JDBC best practice. PreparedStatement is used for executing a precompiled SQL statement.. java.sql.PreparedStatement suitable for executing DML commands -  SELECT, INSERT, UPDATE and DELETE. PreparedStatement is faster as compared to Statement because it is used for executing precompiled SQL statement. Hence,  same SQL query can be executed repeatedly in PreparedStatement.


3. But, Statement is used for executing a static SQL statement. java.sql.Statement is suitable for executing DDL commands - CREATE, drop, alter and truncate.

4. You must follow this JDBC best practice. Avoid SQL injection in JDBC java
PreparedStatement prevents SQL injection, because text for all the parameter values is escaped. Example >
prepStmt = con.prepareStatement("select * from EMPLOYEE where ID=? ");
prepStmt.setInt(1, 8);

While, Statement enforces SQL injection, because we end up using query formed using concatenated SQL strings. Example >
String s1= "select * from EMPLOYEE where id = ";
int i1 = 2 ;
stmt.executeQuery(s1 + String.valueOf(i1));

Here comes one very important question, are PreparedStatement vulnerable to SQL injections?
YES, when we use concatenated SQL strings rather than using input as a parameter for preparedStatement.

PreparedStatement makes code more readable and understandable - We need not to write concatenated SQL strings, we can use queries and pass different parameters at runtime using setter methods.


5. It is another very important JDBC best practice. Using batch statements in jdbc java -

Batch statement sends multiple requests from java to database in one just one call while without batch statements multiple requests will be in sent in multiple (one by one) calls to the database.

About addBatch() method >
PreparedStatement extends Statement and inherits all methods from Statement and additionally adds addBatch() method.
addBatch()  method - adds a set of parameters to the PreparedStatement object's batch of commands.
For details on using batch statements in jdbc java : PreparedStatement BATCH - using executeUpdate methods - INSERT, UPDATE and DELETE


6. Jdbc best practice of specifying column name in select query - Rather than using queries like "select * from EMPLOYEE", you must must specify column name which you want to fetch from database like this  "select ID, NAME from EMPLOYEE".
But, what’s the advantage of specifying column name rather than using select * from table.

Assume your table has 100 columns and you have to use only 2 column, then you will unnecessarily fetch data of other columns which in turn will degrade your application's performance and also you will end up wasting precious memory.


7. This JDBC best practice is in continuation to above discussed point. Rather than specifying column index we must use the column name to avoid java.sql.SQLException: Invalid column index and Invalid column name Exceptions.

Example - Let’s say out SQL query is "select ID, NAME from EMPLOYEE"

Than rather than specifying column index
resultSet.getInt(1) and resultSet.getString(2));

We must specify column name
resultSet.getInt("ID") and resultSet.getString("NAME"));





8. It is good JDBC practice to write as much business logic as much as possible in Stored Procedure or Functions as compared to writing it down in java class.
Because that reduces databse hits and improves application performance significantly. You must remember that database hit is a very costly operation and you must try to avoid it as much as possible.

Read : Execute database STORED PROCEDURE - IN parameter, OUT parameter and IN OUT parameter || call FUNCTION from java - >



9. JDBC Transactions - Another very important JDBC best practice of using connection.setAutoCommit(false), connection.commit() and connection.rollback().

We can set autocommit mode of connection to false using connection.setAutoCommit(false) and then accordingly use connection.commit() or connection.rollback().

If any transaction fails in between then rollback the transaction by calling con.rollback(), commit the transaction by using con.commit() only if it went successful.

Example -

Let's say we have to update salary of two employees, and salary of both employees must be updated simultaneously in database.

And let's say salary of first employee is updated successfully. But, if anything goes wrong in updating salary of second employee than any updation done to first employee's salary will be rolled back.

For more read : JDBC Transactions - setting connection.setAutoCommit(false), commit and rollback(TCL command) in java



10. Here is another JDBC best practice - You must ensure that you close all the JDBC Statement, PreparedStatement, CallableStatement , ResultSet and Connections in java to avoid ora-01000 maximum open cursors exceeded java.sql.SQLException in java. You must always close all the above mentioned objects in finally block in java because finally block is always executed irrespective of exception is thrown or not by java code.
Example of closing PreparedStatement, ResultSet and Connections in finally block in java-

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class JdbcBestPracticeExampleInJava {
   public static void main(String... arg) {
          Connection con = null;
          PreparedStatement prepStmt = null;
          ResultSet rs = null;                 
          try {
                 // Logic ..
          } catch (ClassNotFoundException e) {
                 e.printStackTrace();
          } catch (SQLException e) {
                 e.printStackTrace();
          }
          finally{
                 try {
                       if(rs!=null) rs.close(); //close resultSet
                       if(prepStmt!=null) prepStmt.close(); //close PreparedStatement
                       if(con!=null) con.close(); // close connection
                 } catch (SQLException e) {
                       e.printStackTrace();
                 }
          }
   }
}





So, in this tutorial we learned important and best JDBC(Java database connectivity) practices in java with examples.


Labels: Core Java JDBC
eEdit
Must read for you :