1、1ORACLE 数据库连接（5 62-78/347-511/1009-1040、2565-593）典型应用结构（C/S 和 B/S）客户机（安装驱动）浏览器（下载驱动）WEB 服务器（安装驱动）应用服务器（安装驱动）DB 服务器（安装驱动）客户机（安装驱动） (C/S)ODBCJDBCOLE DB浏览器（下载驱动） (JDBC Thin Driver) (B/S) (B/S)ODBCJDBCOLE DBWEB 服务器（安装驱动） (B/S) （HTTP） (C/S)ODBCJDBCOLE DB应用服务器（安装驱动） (C/S) (B/S) (C/S) (C/S)ODBCJDBCOLE DBDB
2、 服务器（安装驱动）ODBCJDBCOLE DBODBCJDBCOLE DBODBCJDBCOLE DBODBCJDBCOLE DBODBCJDBCOLE DBDB服务器应用服务器WEB服务器浏览器客户DB服务器应用服务器WEB服务器客户端计算机浏览器客户应用系统网络连接传输：HTTP、SMTP、IIOP、HTTPs通信：TCP/IP 、 SPX/IPX、IBM LU6.2、Novell、DECnet2数据库连接ODBC 连接VSAM/ISAM：虚拟顺序存取法/ 索引顺序存取法OLE DB 连接客户机 浏览器原始接口数据库服务器Web 服务器ODBCRDBMS非 RDBMSVSAM/ISAM
3、等Email 等Video/Audio/Image等客户机 浏览器原始接口数据库服务器OLE DBODBCRDBMS非 RDBMSVSAM/ISAM 等Email 等Video/Audio/Image等OLE DBWeb 服务器3ADO/OLE DB 连接连接软件层次结构应用程序客户端（Application、ASP、JSP、Applet、Servlet 、JavaBean、EJB等）ADO 或OO4OOLE DB驱动程序OCI（ Oracle Call Interface） （OCI C Library 、Java Sockets）Net8（网络连接与数据传输：传输协议与通信协议）Oracl
4、e 服务器（SQL Engine、PL/SQL Engine、Java Engine 、KPRB C Library）ODBC驱动程序JDBC驱动程序OLE DB/ODBC桥ODBC 驱动程序管理器JDBC 驱动程序管理器JDBC/ODBC 桥ORACLE 数据库连接的软件层次客户机 浏览器原始接口数据库服务器ADO/OLE DBODBCRDBMS非 RDBMSVSAM/ISAM 等Email 等Video/Audio/Image等ADO/OLE DBWeb 服务器4 ADO（AxtiveX Data Objects）Microsoft 驱动程序的关键组件，可用于访问存储在任意地方的数据（如
5、Oracle、SQL Server 、文本文件、Exchange、LDAP 等 ） OO4O（Oracle Objects for OLE）Oracle 的一个专用 COM 层，VB 和 C+可以用 OO4O与 Oracle 通信，提供类似 ADO 的功能 ADO 是 Microsoft 许多数据访问策略的修订和合并的结果，是 DAO（Data Access Objects）和 RDO（Remote Data Objects）的继承者。ADO 与 OLE DB 之间的关系类似RDO 与 ODBC 之间的关系，RDO 是 ODBC API 上的瘦包装器 ADO、OLE DB 和 ODBC 现在组
6、装在单一的可安装组件内，即 MDAC（Microsoft Data Access Components） ，目前版本为 ADO2.6（参考：ADO2.6 Programmers Reference, Wrox,ISBN 1-861004-63-x）Overview of the Oracle JDBC Drivers Thin driver, a 100% Java driver for client-side use without an Oracle installation, particularly with applets OCI drivers (OCI8 and OCI7) fo
7、r client-side use with an Oracle client installation Server-side Thin driver, which is functionally the same as the client-side Thin driver, but is for code that runs inside an Oracle server and needs to access a remote server, including middle-tier scenarios Server-side internal driver for code tha
8、t runs inside the target server (that is, inside the Oracle server that it must access) Driver-Database ArchitectureCommon Features of Oracle JDBC DriversJDBC Thin DriverJava SocketsJDBC OCI DriverOCI C LibrarySQL EnginePL/SQL EngineJava EngineServer Side Thin DriverJDBC Server SideInternal DriverKP
9、RB C Library5The server-side and client-side Oracle JDBC drivers provide the same basic functionality. They all support the following standards and features: either JDK 1.2.x / JDBC 2.0 or JDK 1.1.x / JDBC 1.22 (with Oracle extensions for JDBC 2.0 functionality) the same syntax and APIs the same Ora
10、cle extensions full support for multi-threaded applicationsNotes: The server-side internal driver supports only JDK 1.2.x. Most JDBC 2.0 functionality, including that for objects, arrays, and LOBs, is available in a JDK 1.1.x environment through Oracle extensions. Starting with release 8.1.6, JDK 1.
11、0.2 is no longer supported.JDBC Thin DriverThe Oracle JDBC Thin driver is a 100% pure Java, Type IV driver. It is targeted for Oracle JDBC applets but can be used for applications as well.For applets it can be downloaded into a browser along with the Java applet being run. The HTTP protocol is state
12、less, but the Thin driver is not. The initial HTTP request to download the applet and the Thin driver is stateless. Once the Thin driver establishes the database connection, the communication between the browser and the database is stateful and in a two-tier configuration.The JDBC Thin driver allows
13、 a direct connection to the database by providing an implementation of TCP/IP that emulates Net8 and TTC (Two Task Common: the wire protocol used by OCI) on top of Java sockets. Both of these protocols are lightweight implementation versions of their counterparts on the server. The Net8 protocol run
14、s over TCP/IP only.The driver supports only TCP/IP protocol and requires a TNS listener on the TCP/IP sockets from the database server.Note:When the JDBC Thin driver is used with an applet, the client browser must have the capability to support Java sockets.6JDBC OCI DriversThe JDBC OCI drivers (OCI
15、8 for Oracle8/8i and OCI7 for Oracle7) are Type II drivers targeted for client-server Java applications programmers. They require an Oracle client installation, so are Oracle platform-specific and are not suitable for applets.The OCI drivers, written in a combination of Java and C, convert JDBC invo
16、cations to calls to the Oracle Call Interface (OCI), using native methods to call C entry points. These calls are then sent over Net8 to the Oracle database server.These drivers use the OCI libraries, C-entry points, Net8, CORE libraries, and other necessary files on the client machine on which they
17、 are installed.The OCI drivers provide the highest compatibility with the different Oracle 7, 8, and 8i versions. They also support all installed Net8 adapters, including IPC, named pipes, TCP/IP, and IPX/SPX.JDBC Server-Side Thin DriverThe Oracle JDBC server-side Thin driver offers the same functio
18、nality as the client-side Thin driver, but runs inside an Oracle database and accesses a remote database.This is especially useful in two situations: to access a remote Oracle server from an Oracle server acting as a middle tier more generally, to access one Oracle server from inside another, such a
19、s from any Java stored procedure or Enterprise JavaBeanThere is no difference in your code between using the Thin driver from a client application or from inside a server.Note:Statement cancel() and setQueryTimeout() methods are not supported by the server-side Thin driver.JDBC Server-Side Internal
20、DriverThe Oracle JDBC server-side internal driver supports any Java code that runs inside an Oracle database, such as in a Java stored procedures or Enterprise JavaBean, and must access the same database. 7This driver allows the Java virtual machine (JVM) to communicate directly with the SQL engine.
21、The server-side internal driver, the JVM, the database, the KPRB (server-side) C library, and the SQL engine all run within the same address space, so the issue of network round trips is irrelevant. The programs access the SQL engine by using function calls.Choosing the Appropriate DriverConsider th
22、e following when choosing which JDBC driver to use for your application or applet: If you are writing an applet, you must use the JDBC Thin driver. JDBC OCI-based driver classes will not work inside a Web browser, because they call native (C language) methods. If you want maximum portability, then c
23、hoose the JDBC Thin driver. You can connect to an Oracle server from either an application or an applet using the JDBC Thin driver. If you are writing a client application for an Oracle client environment and need maximum performance, then choose the JDBC OCI driver. For code that runs in an Oracle
24、server acting as a middle tier, use the server-side Thin driver. If your code will run inside the target Oracle server, then use the JDBC server-side internal driver to access that server. (You can also access remote servers using the server-side Thin driver.)应用编程举例（参考：512/13/14/25 章，在线文档）主机/终端模式（Pr
25、o *C）例：sample1.pc#include #include /* Define constants for VARCHAR lengths.*/#define UNAME_LEN 20#define PWD_LEN 40/* Declare variables.*/VARCHAR usernameUNAME_LEN;varchar passwordPWD_LEN;8/*Define a host variables for the output values of a SELECT statement.*/VARCHAR emp_nameUNAME_LEN;float salary;
26、float commission;/* Input host variable. */int emp_number;/* Include the SQL Communications Area.*/#include /* Declare error handling function. */void sql_error();main()char temp_char32;/* Copy the username into the VARCHAR.*/strncpy(char *) username.arr, “SCOTT“, UNAME_LEN);username.len = strlen(ch
27、ar *) username.arr);/* Copy the passwordinto the VARCHAR.*/strncpy(char *) password.arr, “TIGER“, PWD_LEN);password.len = strlen(char *) password.arr);/* Register sql_error() as the error handler. */EXEC SQL WHENEVER SQLERROR DO sql_error(“ORACLE error-n“);/* Connect to ORACLE.*/EXEC SQL CONNECT :us
28、ername IDENTIFIED BY :password;printf(“nConnected to ORACLE as user: %sn“, username.arr);/* Loop, selecting individual employees results */for (;)EXEC SQL WHENEVER NOT FOUND DO break;for (;)emp_number = 0;printf(“nEnter employee number (0 to quit): “);gets(temp_char);emp_number = atoi(temp_char);if
29、(emp_number = 0)9break;EXEC SQL SELECT ename, sal, NVL(comm, 0)INTO :emp_name,:salary,:commissionFROM EMPWHERE EMPNO = :emp_number;/* 其它处理*/if (emp_number = 0) break;printf(“nNot a valid employee number - try again.n“);/* Disconnect from ORACLE. */EXEC SQL COMMIT WORK RELEASE;exit(0);void sql_error(
30、msg)char *msg;char err_msg128;int buf_len, msg_len;EXEC SQL WHENEVER SQLERROR CONTINUE;printf(“n%sn“, msg);buf_len = sizeof (err_msg);sqlglm(err_msg, printf(“%.*sn“, msg_len, err_msg);EXEC SQL ROLLBACK RELEASE;exit(1);客户机/服务器模式（Delphi）Datasource 配置（手工配置、程序配置）Datasource 使用（集中于数据模块、分散于功能模块；使用别名）客户/服务器
31、和浏览器/服务器模式（JDBC：四种驱动器）例：SimpleStatementpackage jdbc.SimpleStatement;import java.sql.*;import DriverData;/* A class to demonstrate the use of a non-scrollable,10* non-updateable Statement. This code will compile under either* JDBC 1.0 or JDBC 2.0*/public class SimpleStatement Connection connection;St
32、atement statement;ResultSet resultSet;/constructorSimpleStatement() throws ClassNotFoundException, SQLException Class.forName(“oracle.jdbc.driver.OracleDriver“);connection = DriverManager.getConnection(DriverData.url,DriverData.user,DriverData.passw);statement = connection.createStatement();/* Use t
33、he executeUpdate method to create two tables */void createTable() throws SQLException String sql = “create table patients (“ +“patient_id number(9) not null primary key,“ +“surname varchar(50),“ +“given_name varchar(50)“ +“)“;int rows = statement.executeUpdate(sql);/return value from a create table
34、should be zeroSystem.out.println(“create table statement return value is “ + rows);System.out.println(“Table patients created“);sql = “create table hospital_stays (“ +“patient_id references patients(patient_id),“ +“admit_date date,“ +“discharge_date date“ +“)“;rows = statement.executeUpdate(sql);Sys
35、tem.out.println(“create table statement return value is “ + rows);System.out.println(“Table hospital_stays created“);/* Use the executeUpdate method to insert data */void insertData() throws SQLException String sql = “insert into patients (patient_id, surname, given_name) “ +“values (10000, Smith, J
36、oe)“;int rows = statement.executeUpdate(sql);/return value from inserting a row should be 111System.out.println(“Inserted “ + rows + “ row“);/How does one insert a String with an embedded apostrophe ()?/Use two apostrophes like this:sql = “insert into patients (patient_id, surname, given_name) “ +“v
37、alues (10001, OGrady, Bridget)“;rows = statement.executeUpdate(sql);System.out.println(“Inserted “ + rows + “ row“);sql = “insert into hospital_stays (patient_id, admit_date, discharge_date) “ +“values (10000, 10-Aug-2000, 13-Aug-2000)“;rows = statement.executeUpdate(sql);System.out.println(“Inserte
38、d “ + rows + “ row“);sql = “insert into hospital_stays (patient_id, admit_date) “ +“values (10001, 11-Sep-2000)“;rows = statement.executeUpdate(sql);System.out.println(“Inserted “ + rows + “ row“);/* Use the executeQuery method to query the table */void checkData() throws SQLException String sql = “
39、select p.patient_id, p.given_name, p.surname, “ +“h.admit_date, h.discharge_date “ +“from patients p, hospital_stays h “ +“where h.patient_id = p.patient_id“;resultSet = statement.executeQuery(sql);while (resultSet.next() System.out.print(resultSet.getInt(1) + “ “);System.out.print(resultSet.getStri
40、ng(2) + “ “);System.out.print(resultSet.getString(3) + “ “);System.out.print(resultSet.getDate(4) + “ “);System.out.println(resultSet.getDate(5) + “ “);void disableCommit() throws SQLException connection.setAutoCommit(false); void commit() throws SQLException mit(); void rollback() throws SQLExcepti
41、on connection.rollback(); void close() throws SQLException if (resultSet != null) resultSet.close(); if (statement != null) statement.close(); if (connection != null) connection.close(); 12public static void main(String args) SimpleStatement ss = null;try ss = new SimpleStatement();ss.disableCommit(
42、);ss.createTable();ss.insertData();ss.checkData();mit(); catch (Exception e) e.printStackTrace();try ss.rollback(); catch (Exception e2) e2.printStackTrace(); finally try ss.close(); catch (Exception e2) e2.printStackTrace(); Connecting to the Database through the Applet1）using host:port:sid syntaxS
43、tring connString=“jdbc:oracle:thin:prodHost:1521:ORCL“;conn = DriverManager.getConnection(connString, “scott“, “tiger“);2）using TNS keyword-value syntaxString connString = “jdbc:oracle:thin:(description=(address_list=(address=(protocol=tcp)(port=1521)(host=prodHost)(connect_data=(sid=ORCL)“;conn = D
44、riverManager.getConnection(connString, “scott“, “tiger“);3）using TNS keyword-value syntax(different host)Connection conn =DriverManager.getConnection (“jdbc:oracle:thin:“ +“(description=(address_list=“ +“(address=(protocol=tcp)(host=webHost)(port=1610)“ +“(address=(protocol=tcp)(host=oraHost)(port=1
45、521)“ +“(source_route=yes)“ +“(connect_data=(sid=orcl)“, “scott“, “tiger“);13String connString = “jdbc:oracle:thin:(description=(address_list=(address=(protocol=tcp)(port=1610)(host=webHost)(address=(protocol=tcp)(port=1521)(host=oraHost)(connect_data=(sid=orcl)(source_route=yes)“;Connection conn = DriverManager.getConnection(connString, “scott“, “tiger“);
