《2022年ORACLE数据库连接 .pdf》由会员分享，可在线阅读，更多相关《2022年ORACLE数据库连接 .pdf（12页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、1 ORACLE数据库连接（562-78 /347-511/ 1009-1040 、2565-593 ）典型应用结构（ C/S和 B/S）客户机（安装驱动）浏览器（下载驱动）WEB 服务器（安装驱动）应用服务器（安装驱动）DB 服务器（安装驱动）客户机（安装驱动）(C/S) ODBC JDBC OLE DB 浏览器（下载驱动）(JDBC Thin Driver) (B/S) (B/S) ODBC JDBC OLE DB WEB 服务器（安装驱动）(B/S) （HTTP）(C/S) ODBC JDBC OLE DB 应用服务器（安装驱动）(C/S) (B/S) (C/S) (C/S) ODBC
2、JDBC OLE DB DB 服务器（安装驱动）ODBC JDBC OLE DB ODBC JDBC OLE DB ODBC JDBC OLE DB ODBC JDBC OLE DB ODBC JDBC OLE DB DB 服务器应用服务器WEB 服务器浏览器客户DB 服务器应用服务器WEB 服务器客户端计算机浏览器客户应用系统网络连接传输： HTTP 、SMTP、IIOP、HTTPs 通信：TCP/IP、SPX/IPX 、IBM LU6.2 、Novell 、DECnet 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精
3、心整理 - - - - - - - 第 1 页，共 12 页 - - - - - - - - - 2 数据库连接ODBC 连接VSAM/ISAM：虚拟顺序存取法/索引顺序存取法OLE DB连接客户机浏览器原始接口数据库服务器Web服务器ODBC RDBMS 非 RDBMS VSAM/ISAM等Email 等Video/Audio/Image 等客户机浏览器原始接口数据库服务器OLE DB ODBC RDBMS 非 RDBMS VSAM/ISAM等Email 等Video/Audio/Image 等OLE DB Web服务器名师资料总结 - - -精品资料欢迎下载 - - - - - - - -
4、 - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 12 页 - - - - - - - - - 3 ADO/OLE DB 连接连接软件层次结构应用程序客户端（Application 、ASP、JSP、Applet、Servlet、JavaBean、EJB 等）ADO 或OO4O OLE DB 驱动程序OCI （Oracle Call Interface ） （OCI C Library、Java Sockets ）Net8（网络连接与数据传输：传输协议与通信协议）Oracle 服务器（ SQL Engine、PL/SQL Engine 、Jav
5、a Engine、KPRB C Library ）ODBC 驱动程序JDBC 驱动程序OLE DB/ODBC桥ODBC 驱动程序管理器JDBC 驱动程 序 管 理JDBC/ODBC 桥ORACLE数据库连接的软件层次客户机浏览器原始接口数据库服务器ADO/OLE DB ODBC RDBMS 非 RDBMS VSAM/ISAM等Email 等Video/Audio/Image 等ADO/OLE DB Web 服务器名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 12 页 -
6、 - - - - - - - - 4 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）的继承者。
7、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 DriversThin driver, a 100% Java driver for client-side use without
8、 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
9、 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) Driver-Database ArchitectureCommon Features of Oracle JDBC DriversThe server-side and client-side Oracle JDBC drivers provide the s
10、ame basic functionality. They all support the following standards and features: JDBC Thin Driver Java Sockets JDBC OCI Driver OCI C Library SQL Engine PL/SQL Engine Java Engine Server Side Thin Driver JDBC Server Side Internal Driver KPRB C Library 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - -
11、- - - 名师精心整理 - - - - - - - 第 4 页，共 12 页 - - - - - - - - - 5 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
12、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 DriverThe Oracle JDBC Thin driver is a 100% pure Java, Type
13、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 d
14、river 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 Net
15、8 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
16、 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 DriversThe JDBC OCI drivers (OCI8 for Oracle8/8i and OCI7 for Oracle7) are Type II drivers targeted for client-server Java ap
17、plications programmers. They require an Oracle client installation, so are Oracle platform-specific and are not suitable for applets. 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 12 页 - - - - - - - - - 6 The OCI drivers, written in a combination of Java and C
18、, 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 m
19、achine 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 DriverThe Oracle JDBC server-side Thin driver o
20、ffers the same functionality 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
21、 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. JD
22、BC Server-Side Internal 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. This driver allows the Java virtual machine (JVM) to communicate direct
23、ly 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. 名师资料总结 - - -精品资料欢
24、迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 12 页 - - - - - - - - - 7 Choosing the Appropriate DriverConsider 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 d
25、river 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 appl
26、ication 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 drive
27、r to access that server. (You can also access remote servers using the server-side Thin driver.) 应用编程举例（参考： 512/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 userna
28、meUNAME_LEN; varchar passwordPWD_LEN; /* Define a host variables for the output values of a SELECT statement.*/ VARCHAR emp_nameUNAME_LEN; float salary; float commission; /* Input host variable. */ int emp_number; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共
29、12 页 - - - - - - - - - 8 /* 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(char *) username.arr); /* Cop
30、y 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 :
31、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 (emp_number = 0)
32、 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 页，共 12 页
33、 - - - - - - - - - 9 /* Disconnect from ORACLE. */ EXEC SQL COMMIT WORK RELEASE; exit(0); void sql_error(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, &buf_len, &msg_len); printf(%.*sn, msg_l
34、en, 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-scroll
35、able, * 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 页，共 12 页 - - -
36、- - - - - - 10 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
37、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(cre
38、ate 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 retu
39、rn 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); /r
40、eturn 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, OGrady, Bridget); rows = statement.execu
41、teUpdate(sql); System.out.println(Inserted + rows + row); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 12 页 - - - - - - - - - 11 sql = insert into hospital_stays (patient_id, admit_date, discharge_date) + values (10000, 10-Aug-2000, 13-Aug-2000); rows = stat
42、ement.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 chec
43、kData() 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) + );
44、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 mit(); void ro
45、llback() 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 SimpleStat
46、ement(); ss.disableCommit(); ss.createTable(); ss.insertData(); ss.checkData(); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 12 页 - - - - - - - - - 12 mit(); catch (Exception e) e.printStackTrace(); try ss.rollback(); catch (Exception e2) e2.printStackTrace(
47、); finally try ss.close(); catch (Exception e2) e2.printStackTrace(); Connecting to the Database through the Applet1）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
48、 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:or
49、acle: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 页，共 12 页 - - - - - - - - -