文档章节

JDBC十佳实践

YuanyuanL
 YuanyuanL
发布于 2014/09/18 17:28
字数 1315
阅读 37
收藏 0

Java程序员的JDBC十佳实践

Java JDBC 最佳实践

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.

    

Java中的JDBC十佳实践

 以下就是JDBC十佳实践列表,有助于避免潜在的错误,获得 更好的性能 并且有助于编写健壮的Java数据库连接代码。


JDBC最佳实践1: 使用 PreparedStatement

这是迄今为止 最流行的 JDBC实践,所有探究过 JDBC API的人都会建议使用它。事实上   PreparedStatement 是实至名归的,因为它提供了很有用的服务,比如:   阻止SQL 注入, 预编译 SQL 查询,以及使用变量绑定( 这些在 为何在java中使用 PreparedStatement  都有说明)。


JDBC最佳实践2: 使用 ConnectionPool

ConnectionPool作为   JDBC最佳实践已经获得了广泛的认可,现如今连接池甚至成了标配。 一些框架已经提供了创建连接池的功能,比如: Database Connection Pool in Spring, DBCP ,并且如果程序运行在托管环境中,比如: J2EE Application ServerWAS 或者JBOSS。Server将提供连接池功能。 该JDBC最佳实践的合理性在于: 创建 JDBC 连接花费的时间相对较长,这也就使得整体的响应时间增长;通过将 JDBC 连接缓存到池中,应用程序可以迅速地访问数据库。


JDBC最佳实践3:禁止自动提交事务模式

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最佳实践4: 使用 JDBC 批量 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最佳实践5: 使用列名访问ResultSet,以避免 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最佳实践6: 使用变量绑定而不是String串联

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最佳实践7: 始终要记得关闭Statement, PreparedStatement 和 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最佳实践8: 为应用程序选择合适的JDBC 驱动

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最佳实践9: 使用标准的SQL 语句,避免使用特定于数据库的查询语句,除非情况必要

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最佳实践10: 使用获取数据的正确 getXXX() 方法

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.



英文原文: http://javarevisited.blogspot.com/2012/08/top-10-jdbc-best-practices-for-java.html#ixzz3Der8c8MQ


个人补充:对海量数据的获取使用TYPE_FORWARD_ONLY的ResultSet以避免内存溢出问题。

http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#absolute(int)


本文转载自:http://javarevisited.blogspot.com/2012/08/top-10-jdbc-best-practices-for-java.html#ixzz3Der8c8MQ

共有 人打赏支持
YuanyuanL

YuanyuanL

粉丝 153
博文 325
码字总数 190992
作品 0
济南
部门经理
私信 提问
【小马哥】Spring Boot系列讲座

这里为大家推荐一个不错的Spring Boot系列讲座,讲师介绍如下: 小马哥,阿里巴巴技术专家,从事十余年Java EE 开发,国内微服务技术讲师。目前主要负责微服务技术推广、架构设计、基础设施、...

杜琪
2018/03/02
0
0
十个JDBC的最佳实践

JDBC是Java为多种关系型数据库提供的统一的访问接口,以下是我长期使用JDBC总结的十个最佳实践。 JDBC最佳实践1:使用PrearedStatement 任何一个使用过JDBC的Java程序员几乎都知道这个,Pre...

王振威
2012/09/08
6.3K
12
十个JDBC的最佳实践

JDBC是Java为多种关系型数据库提供的统一的访问接口,以下是我长期使用JDBC总结的十个最佳实践。 JDBC最佳实践1:使用PrearedStatement 任何一个使用过JDBC的Java程序员几乎都知道这个,Pre...

王振威
2012/11/26
1K
4
【小马哥】Spring Cloud系列讲座

这里为大家推荐一个不错的Spring Cloud系列讲座,讲师介绍如下: 小马哥,阿里巴巴技术专家,从事十余年Java EE 开发,国内微服务技术讲师。目前主要负责微服务技术推广、架构设计、基础设施...

杜琪
2018/03/02
0
0
java 7 入门书籍

一、Java从入门到精通 《Java从入门到精通(第3版)》从初学者角度出发,通过通俗易懂的语言、丰富多彩的实例,详细介绍了使用Java语言进行程序开发需要掌握的知识。 《Java从入门到精通(第3版...

modernizr
2014/05/15
6.9K
9

没有更多内容

加载失败,请刷新页面

加载更多

OSChina 周六乱弹 —— 世界的源代码

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 @小鱼丁 :#今日歌曲推荐# 分享Jason Mraz的单曲《Prettiest Friend (Demo)》: 《Prettiest Friend (Demo)》- Jason Mraz 手机党少年们想听歌...

小小编辑
今天
95
5
java框架学习日志-13(Mybatis基本概念和简单的例子)

在mybatis初次学习Mybatis的时候,遇到了很多问题,虽然阿里云的视频有教学,但是视频教学所使用的软件和我自己使用的软件不用,我自己用的数据库是oracle数据库,开发环境是idea。而且视频中...

白话
今天
11
0
Java基础:String、StringBuffer和StringBuilder的区别

1 String String:字符串常量,字符串长度不可变。Java中String是immutable(不可变)的。 String类的包含如下定义: /** The value is used for character storage. */private final cha...

watermelon11
今天
6
0
mogodb服务

部署MongoDB 官网: https://www.mongodb.com/download-center/community 创建mongo数据目录 mkdir /data/mongodb 二进制部署 wget -c https://fastdl.mongodb.org/linux/mongodb-linux-x8......

以谁为师
昨天
5
0
大神教你Debian GNU/Linux 9.7 “Stretch” Live和安装镜像开放下载

Debian项目团队于昨天发布了Debian GNU/Linux 9 "Stretch" 的第7个维护版本更新,重点修复了APT软件管理器中存在的安全漏洞。在敦促每位用户尽快升级系统的同时,Debian团队还发布了Debian ...

linux-tao
昨天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部