Top 10 JDBC Best Practices for Java Programmer Read more:

转载 2013年12月04日 18:57:50


Top 10 JDBC Best Practices for Java Programmer
Java JDBC Best practices
JDBC Best Practices are some coding practices which Java programmer should follow while writing JDBC code. As discussed in how to connect to Oracle database from Java, JDBC API is used to connect and interact with a Database management System.  We have touched some of the JDBC best practices in our last article 4 JDBC Performance tips, On which we have discussed simple tips to improve performance of Java application with database. By using JDBC you can execute DDL, DML and Stored Procedures. JDBC Best practices is probably most significant set of coding practices in Java because it significantly affect 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 query which we used in our example of JDBC Batch update was taking almost 30 second 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 collection of such practices which help you to write better JDBC code and in most cases result in improved performance.
10 JDBC Best pratices in Java

JDBC Best practices Java coding databaseHere 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.

JDBC Best Practice #1: Use PreparedStatement
This is by far 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

JDBC Best Practice #2: Use ConnectionPool
ConnectionPool as JDBC best practice has already gained recognition and it even become standard now days. Several framework provides in built connection Pool facility like Database Connection Pool in Spring, DBCP and if you are running in managed environment like J2EE Application Server e.g. 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.

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

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.

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 index of 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 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 index is faster than name, which is true but if you look in terms of maintenance, robustness and readability, I prefer accessing column using name in ResultSet Iterator.

JDBC Best Practice #6: Use Bind variables instead of String concatenation
In JDBC Best Practice #1 we have suggest to use 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 database to run same query with different parameter. This JDBC best practices also result in better performance and also provide protection against SQL injection.

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 costly resource and should be closed in finally block to ensure 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.

JDBC Best Practice #8: Choose suitable JDBC driver for your application
There are 4 typs of JDBC driver in Java and it can directly affect the performance of DAO layer. always use latest JDBC Driver if available and prefer type 4 native JDBC Drivers.

JDBC Best Practice #9: Use standard SQL statement and avoid using db specific query until necessary
This is another JDBC best practice in Java which ensures writing portable code. Since most of JDBC code is filled up with SQL query its easy to start using Database specific feature which may present in MySQL but not in Oracle etc. By using ANSI SQL or by not using DB specific SQL you ensure minimal change in your DAO layer in case you switch to another database.

JDBC Best Practice #10: Use correct getXXX() method
This is the last JDBC best practice in this article which suggest using correct getter while getting data from ResultSet to avoid data conversion even though JDBC allows 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 most common practices which I can think of. let us know if you are familiar with any other JDBC best practice.


线程中start方法与run方法的区别-java教程 在线程中,如果start方法依次调用run方法,为什么我们会选择去调用start方法?或者在java线程中调用start方法与run方法...
  • u010953266
  • u010953266
  • 2015年06月18日 12:24
  • 4689

<<Big Data: Principles and Best Practices of Scalable Realtime Data Systems>>读书笔记

Chapter 1 A New Paradigm for Big Data 1.1 How this Book is structured focus on principles of big dat...
  • kiwi_coder
  • kiwi_coder
  • 2015年10月22日 20:23
  • 2105


JDBC和Ibatis中的Date,Time,Timestamp处理 November 25th, 2010西坪 Leave a commentGo to comments 在此前,遇到过使用I...
  • keti_xuetangyi
  • keti_xuetangyi
  • 2012年11月14日 17:49
  • 5846

10 best practices from top coders at Google, Pinterest & more

At VentureBeat’s DevBeatconference next week, we’re doing things a little differently. You won’t be ...
  • lalala003
  • lalala003
  • 2013年12月04日 09:03
  • 405

C++ Best Practice

C++把很多细节都仍给了程序员来处理,因此,C++有更多的注意事项,和一条条的决窍、技巧。 * 每个头文件都得解决anti-reinclude的问题 * 一般来说,library的header要inc...
  • kzjay
  • kzjay
  • 2010年01月25日 00:18
  • 463

最佳实践(Best Practices)

设计细颗粒度的持久类并且使用来实现映射。 使用一个Address持久类来封装 street, suburb, state, postcode. 这将有利于代码重用和简化代码重构(refactoring...
  • liyong1115
  • liyong1115
  • 2008年02月01日 12:37
  • 303


工具: site-1.10.5 beyond compare_xpgod 目录: trunk:主干,是日常开发进行的地方。 branches:分支。一些阶段性的release版本,这些版本是...
  • u010244958
  • u010244958
  • 2015年06月29日 15:31
  • 469

Dynamic-Link Library Best Practices

  • cosmoslife
  • cosmoslife
  • 2014年08月10日 23:29
  • 580

Top 10 Best Practices for Jenkins Pipeline Plugin

The Jenkins Pipeline plugin is a game changer for Jenkins users. Based on a Domain Specific Language...
  • yyw794
  • yyw794
  • 2017年09月13日 08:45
  • 126

Effective Java 2.0_中英文对照_第一章

Effective Java
  • Quincuntial
  • Quincuntial
  • 2016年09月17日 22:05
  • 1228
您举报文章:Top 10 JDBC Best Practices for Java Programmer Read more: