|
java連接oracle數(shù)據(jù)庫的各種方法及java在數(shù)據(jù)庫中的含義 java與oracle的接口： 在數(shù)據(jù)庫中運(yùn)行JAVA可以說是ORACLE8i的最令人激動(dòng)的新特性。在你創(chuàng)建的使用ORACLE8i 數(shù)據(jù)庫的應(yīng)用程序中，你可以使用與JAVA有關(guān)的新特征，輕松的將程序發(fā)布到INTERNET或INTRANET上。 Methods for Using Java in ORACLE================================== 大家都知道JAVA在跨平臺(tái)開發(fā)與INTERNET開發(fā)中已經(jīng)比較流行，ORACLE8i及以后的版本中都包含了對(duì)在數(shù)據(jù)庫中運(yùn)行JAVA的擴(kuò)展支持，這里有兩種方法可以使用： JDBC：與ODBC類似, JDBC 提供了一個(gè)驅(qū)動(dòng)接口使你可以在JAVA程序中訪問數(shù)據(jù)庫。注：JDBC驅(qū)動(dòng)內(nèi)嵌在數(shù)據(jù)庫中虛擬機(jī)中。 SQLJ：是一個(gè)JAVA預(yù)編譯器，它可以將內(nèi)嵌的SQL語句轉(zhuǎn)化為JAVA語句.SQLJ的使用與運(yùn)行機(jī)理與其它ORACLE的與編譯器（如Pro*C，Pro*COBOL）類似。實(shí)際上，為了使我們形象的記住SQLJ提供的功能，我們也可以直接將SQLJ改名為Pro*Java。 將JAVA集成到數(shù)據(jù)庫中是雙向的。也就是說你可以在JAVA中調(diào)用SQL與PL/SQL,也可以在SQL與PL/SQL中調(diào)用JAVA。JAVA程序可以直接通過JDBC驅(qū)動(dòng)調(diào)用SQL與PL/SQL，反過來，你也可以在SQL與PL/SQL中直接調(diào)用JAVA。在數(shù)據(jù)庫中，JAVA命名空間直接映射到數(shù)據(jù)庫模式的命名空間中，這樣可以方便JAVA的存取與調(diào)用。數(shù)據(jù)庫同時(shí)提供擴(kuò)展的DDL語句，通過這些語句，你可以象創(chuàng)建一個(gè)存儲(chǔ)過程一樣在數(shù)據(jù)中創(chuàng)建內(nèi)嵌的JAVA程序。 Features of ORACLE JDBC Drivers ================================= 在ORACLE8i中有三種類型的JDBC驅(qū)動(dòng)，他們都使用相同的 syntax, APIs, and Oracle extensions，以使JAVA代碼在robust clients、Web-based Java applets, and Java stored procedures之間保持輕便靈活：三種類型如下： 1．JDBC OCI： 此驅(qū)動(dòng)類似于傳統(tǒng)的ODBC 驅(qū)動(dòng)。因?yàn)樗枰?span lang=EN-US>Oracle Call Interface and Net8，所以它需要在運(yùn)行使用此驅(qū)動(dòng)的JAVA程序的機(jī)器上安裝客戶端軟件 2．JDBC Thin： 這種驅(qū)動(dòng)一般用在運(yùn)行在WEB瀏覽器中的JAVA程序。它不是通過OCI or Net8，而是通過Java sockets進(jìn)行通信 ，因此不需要在使用JDBC Thin的客戶端機(jī)器上安裝客戶端軟件。 3．JDBC KPRB： 這種驅(qū)動(dòng)由直接存儲(chǔ)在數(shù)據(jù)庫中的JAVA程序使用，如Java Stored Procedures 、triggers、Database JSP‘s。It uses the default/ current database session and thus requires no additional database username, password or URL. 如何配置使JAVA可以通過Oracle JDBC Drivers連接到數(shù)據(jù)庫：1.安裝Sun JDK. 2. 修改PATH環(huán)境變量，使其指向JDK的bin目錄 3. 設(shè)置CLASSPATH環(huán)境變量，使其指向正確的JDK的lib及oracle的JDBC接口。 CLASSPATH = ".;????" 3. 運(yùn)行"java –version" ，驗(yàn)證java的版本。 如何在不同的操作系統(tǒng)上根據(jù)接口類型設(shè)置客戶端： 對(duì)JDBC THIN接口： 在windows與unix下的設(shè)置方法一樣： 1．根據(jù)jdk的版本，只需要將classesxx.zip拷貝到指定的目錄，不需要安裝Oracle Client。在裝完數(shù)據(jù)庫后，該文件會(huì)在ORACLE_HOME/jdbc/lib目錄下。2．設(shè)置CLASSPATH，使其包含上面的classesxx.zip 3．根據(jù)需要，拷貝oracle的其它zip文件并設(shè)置CLASSPATH 對(duì)JDBC OCI接口： Fow Windows： 1．安裝Oracle Client. 2．根據(jù)jdk的版本，設(shè)置CLASSPATH，使其包含正確的classesxx.zip 3．根據(jù)需要設(shè)置CLASSPATH，使其指向Oracle的其它zip文件 4．設(shè)置PATH，使其包含ORACLE_HOME\bin目錄 For unix： 1．安裝Oracle Client. 2．根據(jù)jdk的版本，設(shè)置CLASSPATH，使其包含正確的classesxx.zip 3．根據(jù)需要設(shè)置CLASSPATH，使其指向Oracle的其它zip文件 4．設(shè)置LD_LIBRARY_PATH，使其包含ORACLE_HOME/lib目錄 備注： classesxx.zip一般在ORACLE_HOME\jdbc\lib目錄下。 在ORACLE_HOME\jdbc\lib目錄下的與Oracle JDBC Drives驅(qū)動(dòng)有關(guān)的文件的解釋： - classes12.zip Classes for use with JDK 1.2.x. It contains the JDBC driver classes except classes necessary for NLS support in Object and Collection types. - nls_charset12.zip NLS classes for use with JDK 1.2.x. It contains classes necessary for NLS support in Object and Collection types. - classes12_g.zip Same as classes12.zip, except that classes were compiled with "javac -g". JDBC連接數(shù)據(jù)庫的語法： JDBC THIN: Code: [Copy to clipboard] Connection conn= DriverManager.getConnection ("jdbc:oracle:thin:@dlsun511:1521:ora1","scott","tiger" | | | machine(ip@) : port# : sid JDBC OCI: Code: [Copy to clipboard] Connection conn= DriverManager.getConnection ("jdbc:oracle:oci8[9]:@RAC","scott","tiger" | Net Service JDBC THIN與JDBC THIN對(duì)比： 相同之處： The JDBC Thin, JDBC OCI, and JDBC Server drivers all provide the same functionality. They all support the following standards and features: * JDBC 2.0 * Partial JDBC 3.0 (in JDBC driver version 9.2) * the same syntax and APIs * the same Oracle extensions 至于不同之處是一個(gè)表格，不好上傳，大家自己總結(jié)吧！！ 主要是JDBC OCI 接口比JDBC THIN接口效率高！ How does one connect with the JDBC Thin Driver? The the JDBC thin driver provides the only way to access Oracle from the Web (applets). It is smaller and slower than the OCI drivers. import java.sql.*; Code: [Copy to clipboard] class dbAccess { public static void main (String args []) throws SQLException { DriverManager.registerDriver ( new oracle.jdbc.driver.OracleDriver() Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@dbhost:1521:ORA1", "scott", "tiger" // @machine 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(); } } How does one connect with the JDBC OCI Driver? One must have Net8 (SQL*Net) installed and working before attempting to use one of the OCI drivers. Code: [Copy to clipboard] 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:@ORA1", "scott", "tiger" // or oci9 @Service, 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(); } } How does one connect with the JDBC KPRB Driver? One can obtain a handle to the default or current connection (KPRB driver) by calling the OracleDriver.defaultConenction() method. Please note that you do not need to specify a database URL, username or password as you are already connected to a database session. Remember not to close the default connection. Closing the default connection might throw an exception in future releases of Oracle. import java.sql.*; Code: [Copy to clipboard] 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(); } } 與JAVA有關(guān)的初始化參數(shù)：====================================== Executing initjvm.sql also highlights some new initsid.ora parameters that are used to support Java in your Oracle8i database. These parameters, their descriptions, and the settings required for running initjvm.sql, are all shown in the following list: SHARED_POOL_SIZE? Defines the size of your shared pool in bytes. This should be set to at least 50MB to run initjvm.sql. JAVA_POOL_SIZE? Defines the size of the Java pool, a new area of the SGA in Oracle8i used to store shared Java objects. This should be set to 50MB when running initjvm.sql, but can be as low as 20MB for normal use of Java stored procedures. JAVA_SOFT_SESSIONSPACE_LIMIT? Identifies a soft limit on memory used by Java in a session. The default is 1MB. If this limit is exceeded, a warning is written to the ALERT log. JAVA_MAX_SESSIONSPACE_SIZE? Identifies the maximum amount of memory that can be used by a Java procedure; the default is 4GB. When the limit set by this parameter is exceeded, the executing Java procedure is killed by Oracle8i automatically. 如果將JAVA程序存放在數(shù)據(jù)庫中，并運(yùn)行存儲(chǔ)在數(shù)據(jù)庫中的JAVA程序，則數(shù)據(jù)庫中會(huì)啟用JAVA的虛擬機(jī)，為了保證JAVA虛擬機(jī)有效的運(yùn)行，你需要設(shè)置上面介紹的參數(shù)。 如何將一個(gè)JAVA程序裝載到數(shù)據(jù)庫并且發(fā)布出去？=================================== 就像前面說得，java程序或類可以被存儲(chǔ)到數(shù)據(jù)庫中，作為PL/SQL的替換或補(bǔ)充。Java可以被用來作為數(shù)據(jù)庫的觸發(fā)器、存儲(chǔ)過程、函數(shù)、對(duì)象的成員函數(shù)。在按照下面的過程開發(fā)完java存儲(chǔ)過程后，就可以從SQL或PL/SQL中調(diào)用JAVA存儲(chǔ)過程，就像調(diào)用普通的PL/SQL過程一樣。下面的代碼描述了如何在SQL*PLUS中開發(fā)和使用一個(gè) 輸出"Hello, World" 的JAVA程序的例子： 1. Write the Java program using a Java development environment like Jdeveloper or JBuilder. 2. Load the Java program into Oracle8i using either the create or replace java source command, or with the LOADJAVA utility. 3. Publish your Java procedure to SQL. This step identifies your Java procedure to SQL and PL/SQL by exposing the procedure entry point, mapping datatypes in Java to PL/SQL or SQL, and indicating parameter-passing between Java and PL/SQL or SQL. （1）編寫java程序 ---可以直接在SQL*PLUS中創(chuàng)建JAVA的源文件，當(dāng)然如果有已經(jīng)編譯好的java class,則可以直接跳過這一步，直接到將java程序發(fā)布出去這一步 SQL> -- first, create the Java source code SQL> create or replace java source named "Hello" as public class Hello { static public String Message(String name) { return "Hello, " + name; } } / Java created. （2）發(fā)布java程序 SQL> -- Now, publish it to SQL SQL> create or replace function hello (name VARCHAR2) return VARCHAR2 as language java name ‘Hello.Message (java.lang.String) return java.lang.String‘; Function created. （3）使用發(fā)布的JAVA程序 SQL> -- Now, you can use the Java procedure from a SQL statement SQL> select hello(‘world!‘) from dual; HELLO(‘world!‘) --------------- Hello world! --- hello函數(shù)在8i中不支持中文，9i中支持。如： SQL> select hello(‘你好!‘) from dual; HELLO(‘你好!‘) ------------------ Hello, 你好! 至于其它的例子，大家可以看ORACLE_HOME/jdbc/demo.zip文件，該文件中有利用JDBC OCI與JDBC THIN接口的各種例子。 |
|
|
