ORACLE数据库连接

ORACLE数据库连接 ORACLE数据库连接([5]62-78/347-511/1009-1040、[2]565-593) 典型应用结构(C/S和B/S) 应用 DB WEB 浏览器 服务器 服务器 服务器 客户 应用 DB WEB 浏览器 服务器 服务器 服务器 客户 传输:HTTP、SMTP、IIOP、HTTPs 通信:TCP/IP、SPX/IPX、IBM LU6.2、Novell、DECnet 客户端 计算机 应用系统网络连接 客户机 浏览器 WEB服务器 应用服务器 DB服务器 (安装驱动) (下载驱动) (安装驱动) (安装驱动) (安装驱动) ODBC 客户机 (C/S) JDBC (安装驱动) OLE DB ODBC 浏览器 (JDBC Thin (B/S) (B/S) JDBC (下载驱动) Driver) OLE DB ODBC WEB服务器 (HTTP) (B/S) (C/S) JDBC (安装驱动) OLE DB ODBC 应用服务器 (C/S) (B/S) (C/S) (C/S) JDBC (安装驱动) OLE DB ODBC ODBC ODBC ODBC ODBC DB服务器 JDBC JDBC JDBC JDBC JDBC (安装驱动) OLE DB OLE DB OLE DB OLE DB OLE DB 1 数据库连接 ODBC连接 数据库服务器 RDBMS 原始接口 ODBC 非RDBMS VSAM/ISAM等 Web服务器 Email等 客户机 浏览器 Video/Audio/Image等 VSAM/ISAM:虚拟顺序存取法/索引顺序存取法 OLE DB连接 数据库服务器 RDBMS 原始接口 ODBC 非RDBMS VSAM/ISAM等 OLE DB OLE DB Email等 Web服务器 Video/Audio/Image等 浏览器 客户机 2 ADO/OLE DB连接 数据库服务器 RDBMS 原始接口 ODBC 非RDBMS VSAM/ISAM等 ADO/OLE DB ADO/OLE DB Email等 Web服务器 Video/Audio/Image等 客户机 浏览器 连接软件层次结构 应用程序客户端(Application、ASP、JSP、Applet、Servlet、JavaBean、EJB等) ADO或 ODBC驱动JDBC驱动 程序管理器 OO4O 程序管理 器 OLE DB/ODBC桥 JDBC/ODBC桥 OLE DB JDBC ODBC 驱动程序 驱动程序 驱动程序 OCI(Oracle Call Interface)(OCI C Library、Java Sockets) Net8(网络连接与数据传输:传输与通信协议) Oracle 服务器(SQL Engine、PL/SQL Engine、Java Engine、KPRB C Library) ORACLE数据库连接的软件层次 3 , ADO(AxtiveX Data Objects)Microsoft驱动程序的关键组件，可用于访问存储在任意 地方的数据(如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现在组装在单一的可安装组件内，即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) for 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 that runs inside the target server (that is, inside the Oracle server that it must access) JDBC Thin Driver Java Sockets Java Engine SQL Engine Server Side Thin Driver JDBC Server Side PL/SQL Engine JDBC OCI Driver Internal Driver OCI C Library KPRB C Library Driver-Database Architecture Common Features of Oracle JDBC Drivers The server-side and client-side Oracle JDBC drivers provide the same basic functionality. They all support the following standards and features: 4 , 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 Oracle extensions , full support for multi-threaded applications Notes: , 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.0.2 is no longer supported. JDBC Thin Driver The 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 stateless, 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 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 runs 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. JDBC OCI Drivers The JDBC OCI drivers (OCI8 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. 5 The OCI drivers, written in a combination of Java and C, convert JDBC invocations 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 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 Driver The Oracle JDBC server-side Thin driver offers the same functionality as -side Thin driver, but runs inside an Oracle database and the client 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 as from any Java stored procedure or Enterprise JavaBean There 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 Driver The 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. This driver allows the Java virtual machine (JVM) to communicate directly with the SQL engine. 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. 6 Choosing the Appropriate Driver Consider the 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 choose 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 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.) 应用编程举例(参考:[5]12/13/14/25章，在线文档) 主机/终端模式(Pro *C) 例:sample1.pc #include #include /* Define constants for VARCHAR lengths.*/ #define UNAME_LEN 20 #define PWD_LEN 40 /* Declare variables. */ VARCHAR username[UNAME_LEN]; varchar password[PWD_LEN]; /* Define a host variables for the output values of a SELECT statement.*/ VARCHAR emp_name[UNAME_LEN]; float salary; float commission; /* Input host variable. */ int emp_number; 7 /* Include the SQL Communications Area.*/ #include /* Declare error handling function. */ void sql_error(); main() { char temp_char[32]; /* Copy the username into the VARCHAR. */ strncpy((char *) username.arr, "SCOTT", UNAME_LEN); username.len = strlen((char *) username.arr); /* Copy the password into 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 :username IDENTIFIED BY :password; printf("\nConnected to ORACLE as user: %s\n", username.arr); /* Loop, selecting individual employee's 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 (emp_number == 0) break; EXEC SQL SELECT ename, sal, NVL(comm, 0) INTO :emp_name,:salary,:commission FROM EMP WHERE EMPNO = :emp_number; /* 其它处理 */ } if (emp_number == 0) break; printf("\nNot a valid employee number - try again.\n"); 8 } /* Disconnect from ORACLE. */ EXEC SQL COMMIT WORK RELEASE; exit(0); } void sql_error(msg) char *msg; { char err_msg[128]; int buf_len, msg_len; EXEC SQL WHENEVER SQLERROR CONTINUE; printf("\n%s\n", msg); buf_len = sizeof (err_msg); sqlglm(err_msg, &buf_len, &msg_len); printf("%.*s\n", msg_len, err_msg); EXEC SQL ROLLBACK RELEASE; exit(1); } 客户机/服务器模式(Delphi) Datasource配置(手工配置、程序配置) Datasource使用(集中于数据模块、分散于功能模块;使用别名) 客户/服务器和浏览器/服务器模式(JDBC:四种驱动器) 例:SimpleStatement package jdbc.SimpleStatement; import java.sql.*; import DriverData; /* A class to demonstrate the use of a non-scrollable, * non-updateable Statement. This code will compile under either * JDBC 1.0 or JDBC 2.0 */ public class SimpleStatement { Connection connection; Statement statement; ResultSet resultSet; //constructor 9 SimpleStatement() throws ClassNotFoundException, SQLException { Class.forName("oracle.jdbc.driver.OracleDriver"); connection = DriverManager.getConnection(DriverData.url,DriverData.user,DriverData.passw); statement = connection.createStatement(); } /** Use the 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' should be zero System.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); System.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', 'Joe')"; int rows = statement.executeUpdate(sql); //return value from inserting a row should be 1 System.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) " + "values (10001, 'O''Grady', 'Bridget')"; rows = statement.executeUpdate(sql); System.out.println("Inserted " + rows + " row"); 10 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("Inserted " + 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 = "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.getString(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 { connection.commit(); } void rollback() throws SQLException { connection.rollback(); } void close() throws SQLException { if (resultSet != null) { resultSet.close(); } if (statement != null) { statement.close(); } if (connection != null) { connection.close(); } } public static void main(String[] args) { SimpleStatement ss = null; try { ss = new SimpleStatement(); ss.disableCommit(); ss.createTable(); ss.insertData(); ss.checkData(); 11 ss.commit(); } 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 Applet 1)using host:port:sid syntax String connString="jdbc:oracle:thin:@prodHost:1521:ORCL"; conn = DriverManager.getConnection(connString, "scott", "tiger"); 2)using TNS keyword-value syntax String connString = "jdbc:oracle:thin:@(description=(address_list= (address=(protocol=tcp)(port=1521)(host=prodHost))) (connect_data=(sid=ORCL)))"; conn = DriverManager.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=1521)))" + "(source_route=yes)" + "(connect_data=(sid=orcl)))", "scott", "tiger"); String 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"); 12