JDBC Best Practices are basically JDBC Coding Best Practices so that programmer can easily decide on what cases should use which JDBC API, what steps need to follow to improved writing reliable code and improved JDBC application performance.

Below are my recommendation for JDBC Best Practices and JDBC Coding Best Practices which will significantly affect performance of JAVA application.

1. Use Suitable JDBC Driver

   There are 4 types of JDBC drivers in JDBC and it can directly affect the performance of your application DAO layer. It is recommended to always use latest JDBC drivers if available and prefer type 4 native JDBC Drivers.

2. Use DataSource Connection Pooling if Possible

   Connection pooling is the process where we maintain cache of database connections so that reused whenever request comes to connect with database. Connection Pooling reduces database hits and improves application performance significantly. Database hit is a very costly operation and as much as possible you must try to avoid it.

3. Use Prepared Statement for DML

   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.

4. Use Statement for DDL

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

5. Avoid SQL injection

   PreparedStatement prevents SQL injection, because text for all the parameter values is escaped.

6. Use Batch Statement

   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.

7. Use Column Name in Select Query

   Rather than using queries like “select * from COMPANY”, you must must specify column name which you want to fetch from database like this  “select ID, NAME from COMPANY”.So that In case database table column sequence and count change will not any impact on code.

8. Use Stored procedure and functions

   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.

9. Use Transaction Management 

   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.

10. Close Resources

    Many databases platforms allocate resources to servicing statements, prepared statements and connections for period of time if these objects are not closed after use will reach to max limit and can through errors. For Example: In Oracle possible to get “max cursors exceeded” if not closed Statement or PreparedStatement. We can also get “connection have reached max limit” if not closed Connections.