- 浏览: 236320 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
akka_li:
学习了!但是为什么后续的没有了?!
EJB4:RMI和RMI-IIOP -
springaop_springmvc:
apache lucene开源框架demo使用实例教程源代码下 ...
Lucene学习笔记(一)Lucene入门实例 -
qepipnu:
求solr 客户端 jar包
Solr学习笔记(三)Solr客户端开发实例 -
zhangbc:
是这问题,赞!
Oracle Start Up 2 Oracle 框架构件、启动、解决一个问题 -
feilian09:
查询 select hibernate jdbc 那个效率快
Hibernate,JDBC性能探讨
This blog will beas on a series of examples to account for each viewpoint which will list below.
I. Overview of JDBC
JDBC defines how a Java program can communicate with a database. JDBC API provides two packages they are java.sql and javax.sql . By using JDBC API, you can connect virtually any database, send SQL queries to the database and process the results. JDBC API provides interfaces and classes to work with databases. Connection interface encapsulates database connection functionality, Statement interface encapsulates SQL query representation and execution functionality and ResultSet interface encapsulates retrieving data which comes from execution of SQL query using Statement. The following are the basic steps to write a JDBC program:
1. Import java.sql and javax.sql packages
2. Load JDBC driver
3. Establish connection to the database using Connection interface
4. Create a Statement by passing SQL query
5. Execute the Statement
6. Retrieve results by using ResultSet interface
7. Close Statement and Connection
Code Demo:
public class JDBCTest { public static void main(String[] args) throws SQLException { Connection conn = null; Statement stmt = null; ResultSet r = null; final String driver = "oracle.jdbc.OracleDriver"; final String url = "jdbc:oracle:thin:@//192.168.1.107:1521/oracl"; final String user = "IPCUSER"; final String passwd = "tibco"; try { Class c = Class.forName(driver); Driver d = (Driver) c.newInstance(); DriverManager.registerDriver(d); conn = DriverManager.getConnection(url, user, passwd); stmt = conn.createStatement(); r = stmt.executeQuery("SELECT USER_TABLES.TABLE_NAME FROM USER_TABLES"); while(r.next()) { System.out.println(r.getString(1)); } } catch (Exception e) { e.printStackTrace(); }finally { r.close(); stmt.close(); conn.close(); } } }
Demo Code will extrct all table name of current user
II. Does "Connection.setAutoCommit(false)" mean "begin a transaction?"
A transaction is simply an operation that is irreversibly completed. By default, JDBC connections start in autocommit mode. This means that every executed statement is treated as a separate transaction. There are occasions where this default behavior is undesirable. Having every statement commited to the database can reduce performance. In addition, a set of statements may have dependencies such that previous statements should be undone if a succeeding statement fails. This is why the JDBC API provides the
Connection.setAutoCommit() method.
You can turn off the default autocommit behavior by calling this method with an argument of false.
All statements executed afterward will not take effect until you call commit().
Therefore, the first call of setAutoCommit(false), and each call of commit() implicitly mark the start of a transaction. Transactions can be undone before they are committed by calling rollback(). You will typically rollback a transaction when one of its constituent statements fails
III. ROWNUM Pseudocolumn
1. rownum is a pseudo column. It numbers the records in a result set. The first record that meets the where criteria in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum.
2. Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:
select * from migrationproclog where rownum > 1
The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.
IV. Query with index is 50 times faster than witout index
I will give a test statistic to demostrate this viewpoint. In this test there are more then 1 000 000 data exists in table ORDERS, I have query the 1 000 000th row two times, the first time without index in num column, and the second times create index in num column, as following:
Query SQL Statement:
SELECT num, extract(data, '/') FROM ORDERS WHERE num = 1000000
Create Index Statement:
create index orders_num_index on orders(num);
Query Time:
without index Time: 178953 milliseconds with index Time: 3297 milliseconds
So if our application need query database frequently we should consider create index to ehance query performance.
V. Use pseudo ROWNUM to implement Pagination should be careful
It's really frequent that we need to page query, for example the following demo show query Orders rows from 1 000 000 to 1 500 000, usually we can achieve this goal through rownum, I will give my Pagination SQL statement and the query time directly:
SELECT r, target FROM (SELECT ROWNUM r, extract(data, '/') target FROM orders ) WHERE r >= 1 AND r < 500000 Time: 218 milliseconds SELECT r, target FROM (SELECT ROWNUM r, extract(data, '/') target FROM orders ) WHERE r >= 500000 AND r < 1000000 Time: 513610 milliseconds SELECT r, target FROM (SELECT ROWNUM r, extract(data, '/') target FROM orders ) WHERE r >= 1000000 AND r < 1500000 Time: 1429547 milliseconds
SELECT r, extract(data, '/') FROM (SELECT ROWNUM r, data FROM orders ) WHERE r >= 1 AND r < 500000 Time: 235 milliseconds SELECT r, extract(data, '/') FROM (SELECT ROWNUM r, data FROM orders ) WHERE r >= 500000 AND r < 1000000 Time: 79578 milliseconds SELECT r, extract(data, '/') FROM (SELECT ROWNUM r, data FROM orders ) WHERE r >= 1000000 AND r < 1500000 Time: 130015 milliseconds
SELECT r, extract(data, '/') FROM (SELECT ROWNUM r, data FROM orders WHERE ROWNUM < 500000) WHERE r >= 1 Time: 203 milliseconds SELECT r, extract(data, '/') FROM (SELECT ROWNUM r, data FROM orders WHERE ROWNUM < 1000000) WHERE r >= 500000 Time: 73204 milliseconds SELECT r, extract(data, '/') FROM (SELECT ROWNUM r, data FROM orders WHERE ROWNUM < 1500000) WHERE r >= 1000000 Time: 124391 milliseconds
The above sql statement illustrate 3 different pagination methods and the query time is very different, so we should be careful when we use ROWNUM to implement Pagination .
发表评论
-
Java Nio Usage Demo & A example of use selectableChannel
2011-12-06 21:42 3618Primary purpose of this blog is ... -
JDK Source Code & java.nio
2011-11-10 23:26 2319java.nio is very useful and a p ... -
Oracle - Add Exist Validation Before Create Table
2011-11-07 13:49 1406Usually we need to check the ta ... -
JDK Source Code & java.rmi.server.RMISocketFactory
2011-10-31 23:14 2116Today's Source code analysing s ... -
Oracle - An Managing Lob examples
2011-10-30 17:28 1384I met a request: I need to read ... -
JMX Architecture & "Hello Word" the JMX way
2011-10-25 20:07 1748JMX Architecture Overview: JMX ... -
PL/SQL Studing Notes
2011-10-20 21:48 1328As an extension of native ... -
一组Linux命令
2011-09-29 13:09 1427今天做测试发现Oracle服务器磁盘使用率达到100%导致Or ... -
Jboss-eap-5.1 Messaging
2011-08-02 21:50 2403This section I will concertate ... -
Oracle Reference Exception Gallery
2011-07-05 22:28 1589The Following Exception is real ... -
SQL Studying Note I - Join
2011-07-02 10:28 1183SQL Join SQL Join are u ... -
EJB Security & JAAS Demo
2011-05-21 19:39 1578PROLOGUE: When deploying ... -
Jboss Reference Exception Gallery
2011-04-27 14:08 28541. Unable to locate a login con ... -
Windows批处理脚步实例-创建Oracle数据库用户并向该用户添加数据
2010-12-11 09:25 3398Windows批处理使用方便、 ... -
draft
2010-11-03 15:46 0System.out.println(TestUtil.cla ... -
Oracle starting up 5: Oracle 10g在WINDOWS服务中有5个Oracle服务项及解决与服务项相关的几个问题
2010-10-10 17:25 2295在Windows下安装Oracle 10g,安装完后在WIND ... -
Oracle starting up 5: Oracle数据库基础(续)
2010-10-07 14:15 0通用函数; 通用函数用于任何类型数据(包括空值) ... -
Oracle starting up 4: Oracle 10g 客户端 enterprise manager console 消失问题
2010-10-06 15:20 4265在家装了台服务器,因为家里电脑不行,所以服务器和客 ... -
Oracle Start Up 3:Oracle数据库基础
2010-10-05 20:17 21321. 创建TableSpace、用户及给用户分派权限 ... -
Oracle Start Up 2 Oracle 框架构件、启动、解决一个问题
2010-10-02 14:07 34664Warming Up: 本文 ...
相关推荐
15.7.3 JDBC API为何如何复杂 15.8 远程方法 15.8.1 远程接口概念 15.8.2 远程接口的实施 15.8.3 创建根与干 15.8.4 使用远程对象 15.8.5 RMI的替选方案 15.9 总结 15.10 练习 第16章 设计范式 16.1 范式的概念 ...
15.7.3 JDBC API为何如何复杂 15.8 远程方法 15.8.1 远程接口概念 15.8.2 远程接口的实施 15.8.3 创建根与干 15.8.4 使用远程对象 15.8.5 RMI的替选方案 15.9 总结 15.10 练习 第16章 设计范式 16.1 范式的概念 ...
15.7.3 JDBC API为何如何复杂 15.8 远程方法 15.8.1 远程接口概念 15.8.2 远程接口的实施 15.8.3 创建根与干 15.8.4 使用远程对象 15.8.5 RMI的替选方案 15.9 总结 15.10 练习 第16章 设计范式 16.1 范式的概念 ...
JAVA_Thinking in Java(中文版 由yyc,spirit整理).chm ------------------------------------------------- 本教程由yyc,spirit整理 ------------------------------------------------- “Thinking in Java...
“Thinking in Java”详细目录 写在前面的话 引言 1. 前提 2. Java的学习 3. 目标 4. 联机文档 5. 章节 6. 练习 7. 多媒体CD-ROM 8. 源代码 9. 编码样式 10. Java版本 11. 课程和培训 12. 错误 13. 封面设计 ...
《Thinking in Java》- 强烈推荐* O’reilly的Java编程基础系列 - 参考* 二、进阶 《Java Cook Book》- 非常推荐* (包含了Java编程的Tips,适合当做手册来查阅) 《O’reilly-Java IO》- 推荐* (包含Java IO...
preliminary thinking 246 ■ Choosing a learning algorithm to train the model 247 ■ Improving performance of the color-fill classifier 250 13.6 Summary 254 14 Training a classifier 255 14.1 Extracting...
JDBC 事务抽象(Transactions) DAO 支持(DAO Support) O/R映射(O/R Mapping) XML 编列(XML Marshalling) Web 技术(Web) Web Servlet 技术栈 Spring MVC WebSocket SockJS Web Reactive 技术栈 Spring WebFlux ...
但无论如何,《Thinking In Java》绝对不应该作为你入门Java的第一本书籍! 记住,网络上学习Java的资源很多,但基本不够系统,拥有一本Java入门书籍,是你系统学习Java的基础。 2)准备Java学习环境。 准备你的学习...
使用类再生的两个方式:组合(new)和继承(extends),这个已经在 thinking in java中提到过. 设计模式之 Proxy(代理) 以 Jive 为例,剖析代理模式在用户级别授权机制上的应用 设计模式之 Facade(门面?) 可扩展的使用...