这篇文章作为基础，放在常用代码里面。平时需要写JDBC的时候，可以翻出来当范例。
Official doc: http://docs.oracle.com/javase/tutorial/jdbc/basics/connecting.html
以下是4种常用的数据库的JDBC访问URL和Driver：
##MySql - default port is 3306database.url=jdbc:mysql://: / database.driver=com.mysql.jdbc.Driver##db2 - default port is 50000 database.url=jdbc:db2:// : / database.driver=com.ibm.db2.jcc.DB2Driver ##oracle - default port is 1521database.url=jdbc:oracle:thin:@ : :orcldatabase.driver=oracle.jdbc.driver.OracleDriver##sqlserver - default port is 1433database.url=jdbc:sqlserver:// : ;databaseName= database.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
下面使用的常用类为DriverManager, Connection, Statement, PreparedStatement, Savepoint, ResultSet.
如果一个语句重复使用，那就用PreparedStatement, 它和Statement不同的是，PreparedStatement可以使得数据库对语句预编译并缓存，以后每次使用则不再需要编译，提高了性能。这里编译包含语句解析，纠错，计算Access Plan等等。PreparedStatement也比Statement更加易读易维护。它还支持batch错作。
Savepoint可以对一个Transaction进行分割。当一个Transaction失败，不一定全部回滚，可以回滚到前面某一个Savepoint。
Connection的属性中包含了对Transaction的操作，也包含了对数据的读写策略。可以使用setXXXX方法对其进行设置。
此外一个重要操作，在本例中没有体现。这里提一下，connection.getMetaData()。 MetaData中含有数据库的版本信息以及其对某些特殊resultset的支持。 比如types，concurrency，holdability。
下面是一个范例。整个逻辑是这样子的：
建表 - commit
插入3行 - commit 打印全表 - commit 更新全表 - commit 打印全表 - commit 删除一行 - commit 打印全表 - commit 删除一行 - savepoint 删除一行 - rollback(savepoint) 打印全表 - commit 销毁表 - commitpublic class JDBCTest { private Connection connection = null; private static JDBCTest provider = new JDBCTest(); public static void main(String[] args) throws Exception { Connection conn = provider.generateConnection(); try { provider.createTable(conn); provider.insert(conn); provider.query(conn); provider.update(conn); provider.query(conn); provider.delete(conn, true, 3); provider.query(conn); provider.delete(conn, false, 1); Savepoint sp = conn.setSavepoint(); provider.delete(conn, false, 2); conn.rollback(sp); conn.commit(); provider.query(conn); conn.commit(); } finally { provider.dropTable(conn); conn.close(); } } private synchronized Connection generateConnection() throws Exception { Class.forName("com.ibm.db2.jcc.DB2Driver"); String url = "jdbc:db2://localhost:50000/FORUM"; Properties props = new Properties(); props.setProperty("user", "db2admin"); props.setProperty("password", "Passw0rd"); if (this.connection == null) { this.connection = DriverManager.getConnection(url, props); } this.connection .setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); this.connection.setAutoCommit(false); return this.connection; } private void createTable(Connection conn) throws SQLException { try { StringBuffer sb = new StringBuffer() .append(" CREATE TABLE TEST ( ") .append(" ID INTEGER NOT NULL, ") .append(" NAME CHAR(36) NOT NULL ") .append(" ) "); Statement s = conn.createStatement(); s.execute(sb.toString()); conn.commit(); } catch (SQLException e) { conn.rollback(); throw e; } } private void insert(Connection conn) throws SQLException { PreparedStatement ps = null; try { StringBuffer sb = new StringBuffer() .append(" INSERT INTO TEST VALUES ( ?, ?) "); ps = conn.prepareStatement(sb.toString()); ps.setInt(1, 1); ps.setString(2, "Bill"); ps.addBatch(); ps.setInt(1, 2); ps.setString(2, "Joey"); ps.addBatch(); ps.setInt(1, 3); ps.setString(2, "Kent"); ps.addBatch(); ps.executeBatch(); conn.commit(); } catch (SQLException e) { conn.rollback(); throw e; } finally { try { ps.close(); } catch (Exception e) { } } } private void update(Connection conn) throws SQLException { PreparedStatement ps = null; try { StringBuffer sb = new StringBuffer() .append(" UPDATE TEST SET NAME=? WHERE ID=? "); ps = conn.prepareStatement(sb.toString()); ps.setInt(2, 1); ps.setString(1, "Bill Update"); ps.addBatch(); ps.setInt(2, 2); ps.setString(1, "Joey update"); ps.addBatch(); ps.setInt(2, 3); ps.setString(1, "Kent update"); ps.addBatch(); ps.executeBatch(); conn.commit(); } catch (SQLException e) { conn.rollback(); throw e; } finally { try { ps.close(); } catch (Exception e) { } } } private void delete(Connection conn, boolean commite, int row) throws SQLException { PreparedStatement ps = null; try { StringBuffer sb = new StringBuffer() .append("DELETE FROM TEST WHERE ID=?"); ps = conn.prepareStatement(sb.toString()); ps.setInt(1, row); ps.execute(); if (commite) { conn.commit(); } } catch (SQLException e) { conn.rollback(); throw e; } finally { try { ps.close(); } catch (Exception e) { } } } private void query(Connection conn) { PreparedStatement ps = null; try { StringBuffer sb = new StringBuffer() .append(" SELECT ID, NAME FROM TEST "); StringBuffer output = new StringBuffer().append("Result:"); ps = conn.prepareStatement(sb.toString()); ResultSet rs = ps.executeQuery(); while (rs.next()) { output.append("[ID=").append(rs.getInt(1)).append(","); output.append("NAME=").append(rs.getString(2)).append("]"); } System.out.println(output); } catch (SQLException e) { e.printStackTrace(); } finally { try { conn.commit(); ps.close(); } catch (Exception e) { } } } private void dropTable(Connection conn) throws SQLException { try { StringBuffer sb = new StringBuffer() .append(" DROP TABLE TEST "); Statement s = conn.createStatement(); s.execute(sb.toString()); conn.commit(); } catch (SQLException e) { conn.rollback(); throw e; } }}