Thinking in 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 .