Java JDBC Best practices
JDBC Best Practices are some coding practices that Java programmers should follow while writing JDBC code. As discussed in how to connect to the Oracle database from Java, JDBC API is used to connect and interact with a Database management System. We have touched on some of the JDBC best practices in our last article 4 JDBC Performance tips, On which we have discussed simple tips to improve the performance of Java applications with the database. By using JDBC you can execute DDL, DML, and Stored Procedures. JDBC Best practices are probably the most significant set of coding practices in Java because it significantly affects the performance of Java application.
I have seen substantial performance gain by simply following common JDBC best practices like running queries with auto-commit mode disable. One of the queries which we used in our example of the JDBC Batch update was taking almost 30 seconds to finish with auto-commit mode enabled but it just took under one second with auto-commit mode disable and using explicit commit.
This JDBC tutorial is a collection of such practices that help you to write better JDBC code and in most cases result in improved performance.
I have seen substantial performance gain by simply following common JDBC best practices like running queries with auto-commit mode disable. One of the queries which we used in our example of the JDBC Batch update was taking almost 30 seconds to finish with auto-commit mode enabled but it just took under one second with auto-commit mode disable and using explicit commit.
This JDBC tutorial is a collection of such practices that help you to write better JDBC code and in most cases result in improved performance.
10 JDBC Best practices in Java
Here is my list of top 10 JDBC best practices in Java which helps to avoid potential error, to get better performance, and helps to write robust Java database connection code.1. JDBC Best Practice #1: Use PreparedStatement
This is by far the most popular JDBC practices suggested by everyone who has worked in JDBC API in Java. Indeed PreparedStatement deserve that admiration because of useful services it provides like prevention from SQL injection, Precompiled SQL queries and use of bind variables as discussed in why Use PreparedStatement in Java
2. JDBC Best Practice #2: Use ConnectionPool
ConnectionPool as JDBC best practice has already gained recognition and it even becomes standard nowadays. Several frameworks provide inbuilt connection Pool facility like Database Connection Pool in Spring, DBCP and if you are running in a managed environment like J2EE Application Server like WAS or JBOSS, Server will provide Connection Pool facility.
Rational behind this JDBC best practices is that Creating JDBC connection take relatively longer time which can increase overall response time, by caching JDBC connection in pool application can immediately access database.
Rational behind this JDBC best practices is that Creating JDBC connection take relatively longer time which can increase overall response time, by caching JDBC connection in pool application can immediately access database.
3. JDBC Best Practice #3: Disable auto-commit mode
This is one of those JDBC best practices which provided substantial performance gain in our JDBC batch update example. Its recommended to run SQL query with auto commit mode disable. Rational behind this JDBC best practice is that with auto commit mode disabled you can group SQL Statement in one transaction while in case of auto commit mode every SQL statement runs in its own transaction and committed as soon as it finishes. So always run queries with auto commit mode disabled
4. JDBC Best Practice #4: Use JDBC Batch Update
This is another JDBC best practice which is very popular. JDBC API provides addBatch() method to add SQL queries into batch and executeBatch() to send batch queries for execution. Rational behind this JDBC best practices is that, JDBC batch update potentially reduce number of database roundtrip which result in significant performance gain. So always Use JDBC batch update for insertion and update queries.
5. JDBC Best Practice #5: Access ResultSet using column name to avoid invalidColumIndexError
JDBC API allows to access data returned by SELECT query using ResultSet, which can further be accessed using either column name or column index. This JDBC best practice suggest using column name over column index in order to avoid InvalidColumnIndexException which comes if the index of the column is incorrect, most common of them is 0, since ResultSet column Index starts from 1, zero is invalid.
Also you don't need to change your JDBC access code if the order of column changed in SELECT SQL query, which is a major maintenance gain and a robust way to write JDBC code. Some Java programmer may argue that accessing column using an index is faster than the name, which is true but if you look in terms of maintenance, robustness and readability, I prefer accessing column using the name in ResultSet Iterator.
Also you don't need to change your JDBC access code if the order of column changed in SELECT SQL query, which is a major maintenance gain and a robust way to write JDBC code. Some Java programmer may argue that accessing column using an index is faster than the name, which is true but if you look in terms of maintenance, robustness and readability, I prefer accessing column using the name in ResultSet Iterator.
6. JDBC Best Practice #6: Use Bind variables instead of String concatenation
In JDBC Best Practice #1 we have suggested using PreparedStatement in Java because of better performance. But performance can only be improved if you use bind variables denoted by ? or place holders. which allows the database to run the same query with different parameters. This JDBC best practices also result in better performance and also provide protection against SQL injection.
7. JDBC Best Practice #7: Always close Statement, PreparedStatement, and Connection.
Nothing new on this JDBC Best practice. Its common Java coding practice to close any resource in finally block as soon as you are done with that. JDBC Connection and other JDBC classes are a costly resources and should be closed in finally block to ensure the release of connection even in case of any SQLException. From Java 7 onwards you can use Automatic Resource Management (ARM) Block to close resources automatically.
8. JDBC Best Practice #8: Choose suitable JDBC driver for your application
There are 4 types of JDBC drivers in Java and it can directly affect the performance of the DAO layer. always use the latest JDBC Driver if available and prefer type 4 native JDBC Drivers.
9. JDBC Best Practice #9: Use standard SQL statements and avoid using DB specific queries until necessary
This is another JDBC best practice in Java that ensures writing portable code. Since most of the JDBC code is filled up with SQL query it's easy to start using Database specific features which may be present in MySQL but not in Oracle etc. By using ANSI SQL or by not using DB-specific SQL you ensure a minimal change in your DAO layer in case you switch to another database.
10. JDBC Best Practice #10: Use correct getXXX() method
This is the last JDBC best practice in this article which suggests using correct getter while getting data from ResultSet to avoid data conversion even though JDBC allows us to get any data type using getString()or getObject().
That's all on JDBC best practices for Java Programmer, I am sure there are many more JDBC best practices around but these are the most common practices which I can think of. let us know if you are familiar with any other JDBC best practices.
Other Development Best practices article from Javarevisited blog
15 comments :
Great Article! Will Apache Tomcat provide you with connection pool facility out of the box?
11. Avoid coding directly to JDBC; it's easy to screw up any of the above. Instead, use an ORM library like Hibernate or a SQL mapping framework like MyBatis.
well, I think that these should not be considered as the "best" practice but indeed as the "normal" practice. I will consider as a toy all the software that breaks one of these rules. In addition, I don't think it does not have anymore sense using plain JDBC, since JPA is now a consolidated and stable technology.
12. Completely disregard #11 for anything non-trivial and performance critical.
@Kevin
Tomcat supports JDBC connection pool since long. Read the following to learn more
http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html
http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html
I was looking for some Java best practices when I landed up on this article. This is awesome!!. I have been using JDBC from couple of months but not aware of many of these JDBC best practices.Your blog is best to learn from basics to best practices , keep it up dude.
About #9, how often do you need to change databases? In my 15+ years of experience, it's much more common to change front end code than it is to change the underlying platform. Data is forever, front-end (and middle tier) technologies come and go.
I would argue the opposite of #9, namely that you should know your database and take advantage of its best features. Why would an experienced developer expect to gain anything from a database swap when they aren't using their current database's full potential? It's like pushing a bicycle instead of riding it.
100% agree with all of the tipps. However managing resources correctly is essential when using JDBC. Thats why I created a bunch of helper classes like https://github.com/scireum/sirius/blob/develop/web/src/sirius/web/jdbc/JDBCQuery.java for SIRIUS (http://sirius-lib.net). Might be helpful for others as well. It provides a super thin layer above JDBC giving you all of the performance you need while providing connection pooling and idiot proof resource management.
You can add few more best practices on JDBC resource management e.g. Apart from closing statements by calling their close() method, you can also call various free() methods to release more expensive resources e.g. Blob, Clob and Array. Blob, Clob, and NClob Java objects remain valid for at least the duration of the transaction in which they are created. This could potentially result in an application running out of resources during a long running transaction. In fact, any Array, Blob, Clob whose scope is entirely controlled within a local block should have its free() method called in a finally block to release respective resources. By the way you can only do this if you are using JDBC 4.0 released in Java 1.6, in lower version, you still depends on mercy of JDBC drivers and Garbage collector.
One of the less common JDBC best practices I have seen is making your DAO class name identical as Table name, for example if you have Customer table than your DAO class should be called CustomerDAO, similarly if you have Product table than you can name it ProductDAO. In a case where your table name is more complicated and picking up as Java class name doesn't make sense, then consider declaring a public static final String TABLE = "ABC_XYZ", this makes easy to correlated which table a particular DAO class is connecting. Ideally you should know table name by just looking name of DAO class, but in worst case it must contain such field at top of the class.
It is good practices for JDBC Beginners Only because now-a-days Developers are not using Planin JDBC. They are used to JPA's. Anyway good article and keep going ...!
For those that say nobody uses JDBC because of JPA or Hibernate: some processings are bulk loading or display result query on the screen. And use an ORM for those cases is like kill a fly with a bazooka.
About the #9, it'd be sad not use the Oracle hints when you work with their databases.
"12. Completely disregard #11 for anything non-trivial and performance critical."
100% agreed. If you're database reches a certain complexity (and it will if you work as a professional in real projects), you are screwed regarding performance and overhead. If you have a small database with a few tables and a few ten thousand rows, enjoy.
"JDBC Best Practice #9: Use standard SQL statement and avoid using db specific query until necessary'" is not the best practice, but the worst practice. Unless you absolutely have to develop a portable application (and even in this case ORM may be a better solution).
Post a Comment