In a usual enterprise application, there may be a variety of types of databases, and interconnect between these heterogeneous databases is often a bottleneck for an application. There are many ways to solve, and one is to use the bridge through the application, in the heterogeneous database; the second is the solution provided by the database manufacturer, such as the Link Server, Oracle of SQL Server; 3 is through the database Application extension interface, such as Oracle's PL / SQL allows access to external databases through Java.
This article focuses on the third point. Take Oracle to connect SQL Server as an example to explore how to write PL / SQL access to SQL Server using Java, this is especially meaningful for Oracle applications that need to access SQL Server under non-Windows platforms.
This article is configured as follows.
First, hardware and operating system
Dell Intel Xeon Server Windows2000 Server
Second, the database environment
Oracle 8.1.7
Third, installation
To make Oracle support Java, you must install the JServer component on Oracle. When installing Oracle software, if you choose a typical installation, JServer is installed by default if you select a custom installation, remember to select the JServer component. Another way to manually install JServer. Method is to enter the / javaVM / install directory in the Oracle installation directory, perform initjvm.sql as SYS users, this script will configure a Java run environment for the database, will Basic Java Class libraries are loaded into the database. It is recommended to read the /javavm/readme.txt file before performing this script, including the description of the necessary modifications to some configurations of the database before installation, and the processing description of the script performs an error.
The script execution time is about 10 minutes, and if the failure can be re-executed.
After the script is executed, you can see more than JServer with the DBA Studio tool.
Fourth, authority gives
To execute a Java stored procedure in Oracle, you need a JavauserPriv role. This role is automatically created after installing JServer, assigns this role to the use account of each Oracle.
5. Write the Java stored procedure
1. Write a Java class on the local machine.
Public Class Test
{
Public static void main (string args [])
{
System.out.Println ("Hello this is a java procedure);
}
}
2. Upload the write Java class file to the database with the LoadJava instruction.
For example: c: /> loadjava -u test / oracle @ mytest -o -v -r d: /test.java
Of course, in addition to upload .java file, you can also upload .class / .jar / .properties file, uploaded files exist in the form of Object in the database.
If there is a change, it can be re-uploaded to overwrite the previous version.
3, generate a statement interface
Uploaded Java classes need to be published as an interface that can be called, which can be Store Procesure / TRRIGGER / FUNCTION.
SQL> Create or Replace Procedure Test_JAVA
As Language Java
Name 'Test.main (java.lang.string [])';
/
4, test
SQL> SET ServerOutput on size 5000
SQL> Call dbms_java.set_output (5000); call is completed.
SQL> Execute Test_java;
Hello this is a java procedure
The PL / SQL process has been successfully completed.
SQL> Call test_java ();
Hello this is a java procedure
The call is completed.
5, the above is a simple Java class. In fact, if you want to access the non-Oracle database in Oracle, only
To implement access non-Oracle databases in the Java class.
Public static string getstringfomsql () {
String strresult = "";
Connection conn = NULL;
Statement Stmt = NULL;
ResultSet RS = NULL;
Try {
Class.Forname ("com.inet.tds.tdsdriver). NewInstance ();
String Url = "JDBC: inetdae7: 10.24.09.192: 1433? Database = pubs;
// Pubs for your database
String User = "sa";
String password = "123";
CONN = DriverManager.getConnection (URL, User, Password);
String SQL = "SELECT TOP 1 DTYPE, DNAME from Test";
Stmt = conn.createstatement (ResultSet.Type_Scroll_Sensitive, ResultSet.concur_updatable);
RS = stmt.executequery (SQL);
IF (rs.next ()) {
Strresult = "The result from sql server is: dtype->" rs.getstring ("DTYPE") "DNAME->" rs.getstring ("DNAME");
}
} catch (instantiationException e) {
// Todo Auto-Generated Catch Block
E.PrintStackTrace ();
Strresult = "Operator Fail in InstantiationException";
} catch (IllegaCcessException E) {
// Todo Auto-Generated Catch Block
E.PrintStackTrace ();
Strresult = "operator fail in illegaCcessException";
} catch (classnotfoundexception e) {
// Todo Auto-Generated Catch Block
E.PrintStackTrace ();
Strresult = "Operator Fail In ClassNotFoundException";
} catch (sqlexception e) {
// Todo Auto-Generated Catch Block
E.PrintStackTrace ();
Strresult = "Operator Fail In Sqlexception";
}
Finally {
Try {
Rs.close ();
CONN.CLOSE ();
} catch (sqlexception e1) {
E1.PrintStackTrace ();
}
}
Return Strresult;
}
It is worth noting that through Java storage access SQL Server, if a result set is returned, the Oracle version below 9i is no corresponding data type can be converted, and a way to change is either returns a string, or will return The result is stored in the table in Oracle. This is to facilitate programs that cannot directly access SQL Server under those non-Windows platforms.
6. External program calls Java stored procedures
The method of calling the Java stored procedure is the same as the method of calling a general stored procedure.
