Java基础系列之二：Java JDBC一瞥

[先声明一个，Java工作以来接触的少，所以估计有不少谬误；另外，这是在公司写的一篇JDBC介绍，所以用的是英文，懒得把它翻回来了，并非是有意用英文。]

A glimpse of JDBC

 

1. What is JDBC?

JDBC stands for java database connectivity, a set of java API which enables you to execute SQL command and interact with relational database. It’s pretty much like the ODBC but java language dependent. 

For the developer used to work with C#, it’s quite similar to the ADO.NET, and each of them has its strong point.

 

2. How to use JDBC?

2.1. JDBC driver

Download the JDBC driver from corresponding database company official website, for instance Mysql JDBC driver can be found here:

http://dev.mysql.com/downloads/connector/j/5.1.html 

And below is the Oracle JDBC driver:

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_112010.html 

And then, add this JDBC driver jar file to your java project (build pathà Librariesà add external JARs).

 

2.2. Connect and retrieve data from database

Take Mysql as an example, first, import the packages: 

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

 Code snippet: 

Class.forName(“com.mysql.jdbc.Driver”); //pass the driver string to the forName() method
String userName=“root”;
String userPwd=“12321”;
//dbUrl: pretty much like the connection string in ADO.NET
//and here the ‘localhost’ is the host name, IP address is acceptable
//3306 is the port number which mysql database listens
//test is the database name
String dbUrl=“jdbc:mysql://localhost:3306/test”;
Connection conn = DriverManager.getConnection(dbUrl,username,userPwd);
Statement stmt = conn.createStatement();
//ResultSet is the most important class in JDBC and has the similar behavior to DataReader in ADO.NET 
ResultSet rs = stmt.executeQuery("SELECT id,name FROM prog_lang");
while (rs.next())
{
                int id = rs.getInt("id");
                String name = rs.getString("name");
                System.out.println(id + "  " + name);
}

 For Oracle database, the driver string should be “oracle.jdbc.driver.OracleDriver”, and the database url should be something like “jdbc:oracle:thin:@localhost:1521:sid”.

 

2.3. Is there anything like .NET Datatable in JDBC?

For those used to work with ADO.NET, you may prefer Datatable to DataReader because it’s disconnected and easy to use.  In JDBC, maybe RowSet is what you’re looking for. 

Import the packages: 

import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.*; 

Code snippet: 

 

ResultSet res = stmt.executeQuery("SELECT id,name FROM prog_lang");
CachedRowSet crs = new CachedRowSetImpl();
crs.populate(res);//fill the RowSet with a ResultSet
res.close();
crs.first();//move to the first row
//update the data in memory
//first parameter is the index of column, one-based, not zero-based.
crs.updateString(2, crs.getString(2) + "x"); 
crs.updateRow();
crs.acceptChanges(conn);//update the database

 Except to use the populate() method, here’s another way to fill a RowSet: 

Connection conn = ……;
CachedRowSet crs = new CachedRowSetImpl();
crs.setCommand("SELECT id,name FROM prog_lang");
crs.setTableName("prog_lang");
crs.execute(conn);

  

2.4. PreparedStatement

PrepareStatement is introduced for performance reason mostly, for example in the below code snippet: 

 

Connection conn = ……;
PreparedStatement stmt = conn.prepareStatement("SELECT id,name FROM prog_lang where name=?");
stmt.setString(1, "vb");
rs = stmt.executeQuery();
while (rs.next())
{
      ……
}
//
stmt.setString(1, "perl");
rs = stmt.executeQuery();
while (rs.next())
{
      ……
}

 Database has a mechanism called  ‘soft parse’, that means when database encounters a same SQL statement, it doesn’t parse it again but reuse the previous one, the condition to ‘soft parse’ is the 2 SQL statements should be TOTALLY same.

 In the code snippet above, even though the statement is executed twice, the database parses it only one single time, this is important when a SQL statement with different where condition is executed many time, because the SQL parsing is really time consuming.

 

2.5. Handle Oracle reference cursor

Oracle uses reference cursor when a store procedure has to return a result set: 

Create or replace procedure get_prog_lang(pReturn out sys_refcursor) is

begin

    open pReturn for select * from prog_lang;

end;

 To handle the reference cursor in java, see below:

Connection conn = ……;
CallableStatement stmt = conn.prepareCall("BEGIN get_prog_lang(?); END;");
stmt.registerOutParameter(1, OracleTypes.CURSOR);
stmt.execute();
ResultSet rs = ((OracleCallableStatement) stmt).getCursor(1);

  

2.6. Handle result set from Mysql store procedure

Different from Oracle, Mysql can return result set directly in a store procedure: 

Create procedure get_prog_lang()

begin

    select * from prog_lang;

end;

 In java code:

Connection conn = ……;
CallableStatement stmt = conn.prepareCall("call get_prog_lang(); ");
stmt.execute();
ResultSet rs = stmt.getResultSet();