使用jdbc连接sql数据库

基本上就是这些步骤：

1. 下载你要连接的数据库的JDBC驱动程序（driver）。通常都是一个jar文件，复制到你的project里面，确保识别这个库就可以了。
2. 加载驱动， 不同的数据库需要不同 JDBC driver。
3. 连接数据库，不同的数据库同样需要不同的JDBC URL。

下面给出连接不同数据库的例子

连接 mini SQL

// Establish a connection to a mSQL database using JDBC. import java.sql.*; class JdbcTest1 { public static void main (String[] args) { try { // Step 1: Load the JDBC driver. Class.forName("com.imaginary.sql.msql.MsqlDriver"); // Step 2: Establish the connection to the database. String url = "jdbc:msql://www.myserver.com:1114/contact_mgr"; Connection conn = DriverManager.getConnection(url,"user1","password"); } catch (Exception e) { System.err.println("Got an exception! "); System.err.println(e.getMessage()); } } }

 

连接Interbase

// Establish a connection to an Interbase database using JDBC and ODBC. import java.sql.*; class JdbcTest1 { public static void main (String[] args) { try { // Step 1: Load the JDBC ODBC driver. Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // Step 2: Establish the connection to the database. String url = "jdbc:odbc:contact_mgr"; Connection conn = DriverManager.getConnection(url,"user1","password"); } catch (Exception e) { System.err.println("Got an exception! "); System.err.println(e.getMessage()); } } }

 

对于其他数据库，参考如下列表

数据库	JDBC URL (JDBC Connection String) JDBC Driver
MySQL	jdbc:mysql://HOST:PORT/DATABASE com.mysql.jdbc.Driver
Postgresql	jdbc:postgresql://HOST:PORT/DATABASE org.postgresql.Driver
SQL Server	jdbc:microsoft:sqlserver://HOST:1433;DatabaseName=DATABASE com.microsoft.jdbc.sqlserver.SQLServerDriver
DB2	jdbc:as400://HOST:PORT/DATABASE; com.ibm.as400.access.AS400JDBCDriver
sqlite	jdbc:sqlite:DATABASE org.sqlite.JDBC
Derby	jdbc:derby:DATABASE org.apache.derby.jdbc.EmbeddedDriver
Oracle	有三种类型的驱动，后面单独列出

 

其中sqlite是嵌入式数据库，所以连接相对简单。下面是一些例子：

MySQL

Class.forName("com.mysql.jdbc.Driver").newInstance(); String url = "jdbc:mysql://HOST/DATABASE"; conn = DriverManager.getConnection(url, "username", "password");

Postgresql

Class.forName("org.postgresql.Driver"); String url = "jdbc:postgresql://HOST/DATABASE"; Connection conn = DriverManager.getConnection(url,"username", "password");

sqlite

Class.forName("org.sqlite.JDBC"); Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");

Oracle

• JDBC Thin Driver (no local SQL*Net installation required/ handy for applets)
• JDBC OCI for writing stand-alone Java applications
• JDBC KPRB driver (default connection) for Java Stored Procedures and Database JSP's.

Thin Driver

import java.sql.*; class Conn { public static void main (String args []) throws SQLException { try { Class.forName ("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger"); // @machineName:port:SID, userid, password Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION"); while (rset.next()) System.out.println (rset.getString(1)); // Print col 1 stmt.close(); } }

我只用过第一个，个人觉得觉得简单的数据库操作基本上够用了，后面的两种没有用过。

 

OCI Driver

import java.sql.*; class dbAccess { public static void main (String args []) throws SQLException { try { Class.forName ("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@hostname_orcl", "scott", "tiger"); // or oci7 @TNSNames_Entry, userid, password Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION"); while (rset.next()) System.out.println (rset.getString(1)); // Print col 1 stmt.close(); } }

 

KPRB Driver

import java.sql.*; class dbAccess { public static void main (String args []) throws SQLException { Connection conn = (new oracle.jdbc.driver.OracleDriver()).defaultConnection(); Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION"); while (rset.next()) System.out.println (rset.getString(1)); // Print col 1 stmt.close(); } }

 

对于C/S架构的数据库，可以将连接的配置写到Java properties文件里面，以方便以后的使用。

 

不同的JDBC驱动，按照它们的实现方法，也分为四个不同的类别：

2. JDBC-ODBC bridge plus ODBC driver: The Java Software bridge product provides JDBC access via ODBC drivers. 
3. Native-API partly-Java driver: This kind of driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, IBM DB2, or other DBMSs. 
4. JDBC-Net pure Java driver: This driver translates JDBC calls into a DBMS-independent net protocol, which is then translated to a DBMS protocol by a server. 
5. Native-protocol pure Java driver: This kind of driver converts JDBC calls directly into the network protocol used by DBMSs.

下表给出了它们之间的异同点：

• Direct -a connection that a JDBC client makes directly to the DBMS server, which may be remote
• Indirect--a connection that a JDBC client makes to a middleware process that acts as a bridge to the DBMS server

  DRIVER CATEGORY	ALL JAVA	NETWORK CONNECTION
  1 - JDBC-ODBC Bridge	No	Direct
  2 - Native API as basis	No	Direct
  3 - JDBC-Net	client Yes server Maybe	Indirect
  4 - Native protocol as basis	Yes	Direct

最后说一下异常和警告处理：

捕获异常并打印

try { // Code that could generate an exception goes here. // If an exception is generated, the catch block below // will print out information about it. } catch(SQLException ex) { System.out.println("/n--- SQLException caught ---/n"); while (ex != null) { System.out.println("Message: " + ex.getMessage ()); System.out.println("SQLState: " + ex.getSQLState ()); System.out.println("ErrorCode: " + ex.getErrorCode ()); ex = ex.getNextException(); System.out.println(""); } }

SQLWarning 对象是 SQLException 子类，它不会中断程序的执行，但是却能够提供很多有价值的信息，尤其是debug的时候。Connection 对象，Statement 对象 (包括PreparedStatement 和CallableStatement 对象),或者ResultSet 对象都有一个 getWarnings 方法。下面是一个列子：

Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("select COF_NAME from COFFEES"); while (rs.next()) { String coffeeName = rs.getString("COF_NAME"); System.out.println("Coffees available at the Coffee Break: "); System.out.println(" " + coffeeName); SQLWarning warning = stmt.getWarnings(); if (warning != null) { System.out.println("/n---Warning---/n"); while (warning != null) { System.out.println("Message: " + warning.getMessage()); System.out.println("SQLState: " + warning.getSQLState()); System.out.print("Vendor error code: "); System.out.println(warning.getErrorCode()); System.out.println(""); warning = warning.getNextWarning(); } } SQLWarning warn = rs.getWarnings(); if (warn != null) { System.out.println("/n---Warning---/n"); while (warn != null) { System.out.println("Message: " + warn.getMessage()); System.out.println("SQLState: " + warn.getSQLState()); System.out.print("Vendor error code: "); System.out.println(warn.getErrorCode()); System.out.println(""); warn = warn.getNextWarning(); } } }

参考：

［1］http://www.devdaily.com/java/edu/pj/pj010024/

［2］http://www.devdaily.com/java/jdbc-connection-string-mysql-postgresql-sqlserver

［3］http://www.orafaq.com/wiki/JDBC

［4］http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/intro.html#1006158

［5］http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html