Developing databases with SQLJ

Data | database

Overview of the SQLJ

Today's enterprise-class applications typically run on multiple database platforms, such as Oracle, DB2, Sybase, SQL Server, Informix, and so on. In this environment, the migration and maintainability of code is becoming more and more important. Porting code from one database platform to another is very complex and time-consuming, as database vendors use their own proprietary process languages (for example, Oracle uses Pl/sql, and Sybase and SQL Server use Transact-SQL).

In recent years, Oracle developers have been using pl/sql| (A language that provides a process extensions section to relational Database language SQL) to build complex systems that manage a large number of data arrays. Unfortunately, stored procedures written with Pl/sql can only be run on an Oracle database. But SQL developers have a powerful tool for writing code that can be easily ported to other databases-that's Java, because it's a popular development language for Cross-platform development and the power of the international Internet. Java promises a unified, portable application development solution that can be implemented at simple, low-cost it infrastructures, so the main development tool vendors and device providers support Java. Leading software vendors, such as Oracle and IBM, have integrated Java virtual machines (JVMS) into their databases and other application platforms. Oracle database management Systems has introduced extended support functionality for Java in Oracle 8i. In Oracle, there are two basic ways to use Java:

JDBC: Like ODBC, it provides a driver based interface that allows access to Oracle databases from Java applications.

SQLJ: This is the result of a number of vendors working together, a new language that provides support for static SQL in Java code. Theoretically, it provides a greater degree of programmer productivity than JDBC.

Integrating JVMs in Oracle 8i database servers is one of the most important technological innovations introduced by Oracle in recent years. Oracle, the integrated JVM, called Jserver (also known as the Aurora JVM), supports two different program design models:

and SQL integration, allowing users to write traditional database stored procedures, functions, and triggers using Java.

The transactional server platform for distributed Java components, called Enterprise JavaBeans, allows programmers to develop reusable server-side application components.

In this article, I'm mainly talking about SQL and Java integration in Oracle 8i. That is, Java can invoke SQL and Pl/sql,pl/sql and SQL, or it can invoke Java. Java programs invoke them using the JDBC driver's SQL and Pl/sql replicas, and the JDBC driver is embedded in the JVM architecture that resides in the Oracle 8i database. On the other hand, from SQL and Pl/sql to Java,oracle 8i offers two features. In Oracle 8i, the Java namespace is mapped to a database schema, and easy dependencies allow Java to be saved in the database. Oracle 8i also offers extended data definition Language (data Definition language DDL), such as the Create PROCEDURE as Java command, so Java code is easily embedded in Oracle 8i.

What is SQLJ?

SQLJ is a language that allows static SQL statements to be embedded in the Java program in text form. When writing a SQLJ application, we write a Java program and then embed the SQL statements in it by following certain standard rules that define how SQL statements are written to Java programs (see Oracle 8i SQLJ Development Guide and reference for details, You can find it on the Documentation Library CD.
Next, we run a SQLJ translator that converts the SQLJ program into a standard Java program by replacing the embedded structured query with the call to invoke the Sqlj Run-time library. The generated Java program can be compiled using any standard Java compiler (for example, Javac) and can then be used in conjunction with the database. The SQLJ runtime environment consists of a thin (i.e., no additional overhead) SQLJ Runtime Library, that is, a JDBC driver that invokes the corresponding database (Oracle, DB2, and so on) in a pure Java implementation.

SQLJ is similar to the implementation of other embedded structured query languages, such as Oracle Pro * C (SQL embedded in the C locale). The purpose of SQLJ language design is to help Java-based programmers build database applications. SQLJ is an ISO and ANSI standard, which is developed and supported by the leading database and software vendors, including Oracle database management systems, International Business machines, American game company, Informix, and Compaq Inc. All of these companies collaborate to develop compatible SQLJ translators to implement the use of different databases.

Advantages of SQLJ

A compiled SQLJ application is a standard Java program that can be run in any three-component environment: The JVM, the SQLJ Runtime Library, and the JDBC driver.

It provides the following benefits:

Compact Code SQLJ Code is more compact and error-free than JDBC code. The syntax and semantics are checked at compile time. The SQLJ compiler provides type checking and pattern object checking to find errors such as syntax errors or omissions or misspellings in SQL statements, which are performed during compilation rather than during the run. Therefore, programs written using SQLJ are more robust than those written using JDBC.

Multi-Vendor Interoperability SQLJ syntax is developed and supported by major software vendors. Because the SQLJ program accesses the database using Run-time JDBC calls, SQLJ can access any database server that the JDBC driver can implement.

Flexible deployment because the SQLJ Runtime library is a java-based program, SQLJ applications can be configured in any JDBC configuration environment, such as thin client, middle tier, or database server.

Vendor-specific custom SQLJ supports vendor specific product features and extensions through subsequent Java bytecode customization. It can be used to improve the performance of the SQL query language, using the performance or functional extensions provided by the vendor, without considering how the SQLJ program changes, as well as debugging and running records.
　

Development steps for using SQLJ

Here's what you need to do to develop and run an SQLJ application:

Compile the SQLJ source file using the SQLJ compiler. This step generates a Java file that invokes the SQLJ runtime, and a binary SQLJ description file--including information about static SQL statements that exist in SQLJ source files. Compiling Java code with Java compiler. After the editing is complete, the resulting profile is customized with specific database data types, extensions, and feature performance.

Run the application, using the SQLJ Runtime Library and the JDBC driver for a specific database. For example, if your master. Sqlj file defines class MyClass, then the source filename must be MYCLASS.SQLJ. The compiler generates the Myclass.java source file, and then compiles the program to generate the Myclass.class class file. Moreover, both the translator and the compiler generate Profile-key classes, Myclass_sjprofilekeys.class. The translator names an iterator class and a connection context class based on how you declare them. For example, if you declare an iterator, Myiter will generate a Myiter.class class file.

The following are the prerequisites for configuring SQLJ:

SQLJ Run-time Program Library

JDBC drivers, such as: Jdbc/odbc Bridge, Oracle JDBC/OCI driver, Oracle thin JDBC driver, DB2 JDBC driver, and so on.

The JVM that the SQLJ program will execute

Now let's compare sqlj and JDBC, and compare sqlj/jdbc and Pl/sql.

SQLJ and JDBC

The purpose of SQLJ development is to improve the dynamic JDBC SQL Structured Query Language model and the static SQL Structured Query Language model. Unlike the ODBC and JDBC dynamic models, static models provide strongly typed application translation time checks. These require not only compile-time validation of SQL syntax and type compatibility of host variables used by SQL statements, but the correctness of the query itself is related to the definition of tables, views, stored procedures, and so on in the database model. Because all SQL statements are compiled, the SQLJ can be used as a better intermediate medium for performance.

SQLJ Code and JDBC code

For SQL statements with input parameters, the Sqlj class is usually shorter than the equivalent dynamic SQL statement (JDBC) call, because SQLJ uses host variables to pass parameters to the SQL statement, and when JDBC requires a separate statement to bind each parameter, retrieve each result. The following is a SQLJ code fragment for a SELECT statement:
　

String VName; int vsalary; String Vjob; Java.sql.Timestamp vdate;
...
#sql {SELECT ename, Sal
Into:vname,: Vsalary
From EMP
WHERE Job =: vjob and HireDate =: vdate};

The following are the JDBC code fragments for the same SELECT statement:

String VName; int vsalary; String Vjob; Java.sql.Timestamp vdate;
...
PreparedStatement stmt = connection.preparestatement (
"Select Ename, Sal" +
"Into:vname,: vsalary" +
"From EMP" +
"WHERE Job =: vjob and HireDate =: Vdate");

Stmt.setstring (1, vjob);
Stmt.settimestamp (2, vdate);

ResultSet rs = Stmt.executequery ();
Rs.next ();

VName = rs.getstring (1);
Vsalary = Rs.getint (2);

Rs.close ();

As we can see, embedding SQL statements directly in a Java program can generate more concise and readable code than JDBC. As a result, SQLJ reduces development time and maintenance costs when Java applications have access to the database. SQLJ programs can easily interact with JDBC code in the same source file to make dynamic SQL statement calls, or you can use PL/SQL statement blocks in SQLJ statements to accomplish this. In addition, Oracle 9i adds the ability to directly support dynamic SQL in SQLJ code.

Java (SQLJ and JDBC) compared to pl/sql in Oracle databases:

Java, which is used in Oracle database applications, is not yet an alternative to pl/sql. Java and pl/sql complement each other, and Java (SQLJ/JDBC) has the following advantages over Pl/sql: Java can provide important performance benefits, and Java stored procedures are 5 to 100 times times faster, depending largely on the mathematical operators and data types used in the program. In theory, Java stored procedures can easily be converted into stored procedures running on other databases. Java programs can be configured on any layer of a complex application: On the client side, on the application server in the middle tier, or in the database server itself. Java (SQLJ/JDBC) also "shares" the Pl/sql's shortcomings: Pl/sql is tightly integrated with Oracle database servers, and Oracle has been improving pl/sql for nearly 20 years, Java was introduced to Oracle 8i only in 1998. The Pl/sql data type is equivalent to the Oracle local data type, so no data type conversions are required. On the other hand, JDBC proposes to insert a common layer between Java Code and SQL statements, and SQLJ is another layer. Pl/sql works better than Java because it is database-centric: Pl/sql stored procedures are 1.5 times times faster than Java programs (for OLTP online transactions) to 2.5 times times (for batch processing). In addition, Java programs use more CPU resources than pl/sql. The increase in CPU overhead may be due to a longer coding process and additional data type conversions from Oracle to Java.

A solution that achieves the best results

Oracle provides an ideal environment for leveraging the benefits of both the Pl/sql and Java languages. On the one hand, pl/sql programs can invoke SQLJ and JDBC stored procedures, allowing you to build component-based EJB and CORBA applications. The existing Java class libraries can be easily exploited and consolidated into the database code development process by using the Pl/sql invocation specification. On the other hand, Java programs can invoke pl/sql stored procedures, functions, and anonymous program blocks through JDBC or SQLJ. I want to give you a detailed description, SQLJ, to provide syntax for invoking the following:

Stored procedure: Invoke the Updatesalary program using the call operator:

#sql {call Updatesalary};
Function: <0} {0>to call the GetName () function using the values operator: <}0{> using the values operator to invoke GetName () functions: <0}
{0>string name; <}0{>string name;<0}
#sql {name = {VALUES GetName ()};
or by using the SET operator:
String name;
#sql {set:name = GetName ()};
{0>anonymous pl/sql blocks: <}0{> Anonymous PL/SQL program block: <0}
#sql {[DECLARE ...] BEGIN ... End; };

It is important to note that the factors that influence the decision of what language to use are determined not only by the effect of execution-more accurately, a major factor in the current application development process, but also by programmer productivity, expert advice and portability. Fortunately for database developers, you can easily mix Java (SQLJ and JDBC) with a pl/sql stored program into a database to achieve a solution that achieves the best results, without having to choose between several languages.

SQLJ language elements

SQLJ is a developing industry standard language that allows you to easily migrate to other Java-enabled database platforms by allowing you to use Oracle stored procedures that are independent of database code. Knowing the context of the connection, the iterator, the executable statement, and the main expression, you can use some of the actual SQLJ statements into a real-world application.
　

In the previous article, I described what SQLJ was, compared Pl/sql and JDBC, and studied the benefits of SQLJ. In this article, I'll look at the basics of the SQLJ programming language so that you can use SQLJ in real-world applications. The SQLJ program is a Java program that uses the rules of embedded Structured Query language statements, starting with a #_sql tag and ending with a semicolon. There are two class SQLJ statements: declarations and executable statements. A declaration statement declares a connection context and an iterator. The connection context is used to establish a database connection, and the iterator is used to store the result set returned by the SQL query, and the executable statement executes the embedded Structured query statement and the PL/SQL program block. Because the SQLJ program will be translated and then run through JDBC, any SQLJ statements supported by the JDBC driver may be embedded in a SQLJ executable statement. An executable statement may contain a primary expression, exchanging information between Java programs and the database through Java variables.

Oracle JDBC Driver

Oracle provides the following JDBC drivers:

The client-side thin driver is a 100%-pure Java driver for clients that do not have Oracle installed. Oracle recommends using small applications. When a Java small application is running, it can be downloaded to the browser.

OCI drivers (OCI8 and OCI7) are clients that are used to install Oracle client programs. The Oracle JDBC OCI driver accesses the database directly from Java by invoking the Oracle Invoke interface (OCI), providing maximum compatibility with different versions of Oracle7,oracle8 and 8i. These drivers require an Oracle client to install NET8. The server-side thin driver provides the same functions as the client thin driver, but runs within an Oracle database and accesses a remote database. This is useful for accessing a remote Oracle server from a mid-level Oracle server, or, more simply, accessing another Oracle server from one tier, such as accessing an Oracle server from a Java stored procedure or EJB.

Server-side internal drivers, called KPRB (Kernel program Bundled), provide support for any Java code that runs within an Oracle database for the purpose of executing SQL operations. The server-side internal driver allows the Jserver JVM to communicate directly with the SQL engine. This is a default JDBC driver for running SQLJ code on an Oracle 8i/9i server, which is used as a stored procedure, a stored function, a trigger, an EJB, or a CORBA object. The KPRB JDBC driver is very lightweight but efficient and can be particularly perfect in Oracle Jserver. This is the driver we are going to use to write the SQLJ stored procedure. Let's look at the SQLJ element of the description: The connection context, the iterator, the executable statement, and the main expression.

The connection context is used for a single connection, and you can use an instance of the Defaultcontext class and specify the database URL, username, and password when constructing the Defaultcontext object. This is the easiest way to use the Connect () method of the Oracle.sqlj.runtime.Oracle class provided by Oracle Company. In this case, we will use the JDBC thin driver and username "Scott" and password "Tiger" to connect to the database on the MyServer server through port 1521, where the SID of the database is ORCL:
Oracle.connect ("Jdbc:oracle:thin@myserver:1521:orcl", "Scott", "Tiger"); it creates an instance of the Defaultcontext class and takes it as the default connection. Does not necessarily need to use the Defaultcontext instance directly to do anything. For multiple connections, you can create and use a helper instance of the Defaultcontext class by using the Oracle.getconnection () method. In this example, you will use the Oracle OCI8 driver to use MYSERVER_ORCL as the Oracle service name and create a ORCL instance in the Tnsnames.ora file:

Defaultcontext MyContext1 = oracle.getconnection
("Jdbc:oracle:oci8@myserver_orcl", "Scott", "Tiger");
Defaultcontext myContext2 = oracle.getconnection
("Jdbc:oracle:oci8@myserver_orcl", "Tom", "Bear");

This code creates two connection context instances, all two of which use the same Oracle OCI8 driver, but different patterns. You can perform SQL operations in two different modes by using a method that specifies the connection mode for each statement.

#sql [MYCONTEXT1] {SQL statement};
...
#sql [MYCONTEXT2] {SQL statement};

At the end of this program, we need to close the connection in the finally clause and the TRY/CATCH statement block.

Finally
{
#sql [MyContext1] {commit};
Mycontext1.close ();

#sql [myContext2] {commit};
Mycontext2.close ();
}
...

Iterative Program

In the SQLJ program, the result set returned by the SQL query can be used as an iterator object to hold the data. An iterator object is an instance of an iterator class and is conceptually similar to a pl/sql cursor. We must perform the following five steps to use an iterator to process the data returned by the SQL query.

Declares an iterator class.

Declares an iterator object from an iterator class.
Use the SELECT statement to add an iterator object.

Reads data from an iterator object.

Closes an iterator object.

There are two types of iterator classes:

A named iterator in which Java variable types and iterator column names must be specified.

Location iterator, in which only Java variable types that are used to retrieve columns from the database need to be specified.

Named iterator: A named iterator declaration specifies the column accessor names and their Java types.

Let's demonstrate the five steps of using this routine, and in this case we want to retrieve the Ename,job and HireDate columns in the Employee table EMP to find employees with a salary greater than 1500.

To declare an iterator class:

#sql iterator Empiteratorclass (string ename, String Job, Timestamp hiredate);

We use the Java string class to represent the ename and job columns because it is compatible with Oracle VARCHAR2 database types.

The Java.sql.Timestamp type is used for hiredate columns (Oracle date types), because the Java.sql.Date type can save only the year, date and time information, not the hours, minutes, and seconds as Java.sql.Timestamp.

To declare an iterator object from an iterator class:

Empiteratorclass Empiterator;

Use the SQL SELECT statement to add an iterator object.

The following SQLJ statements add the contents of the Ename,job and HireDate columns of the EMP table to the Empiterator object:

int salary = 1500;
#sql Empiterator = {
Select Ename, Job, HireDate
From EMP
where Sal >: Salary
};

We also declare the main variable salary, which is used in the WHERE clause to identify what data to return from the EMP table.

Remember, the name of the database column returned through the SQL query must correspond to the name of the iterator column defined in the first step. Reads data from an iterator object. Because the iterator object may contain multiple rows, we need to use a loop to access each row of data, just as we read data from a PL/SQL cursor. A named iterator implements a next () method that allows you to move between the data of an iterator object. In addition, SQLJ provides a way to retrieve the list of iterative programs that are worth accessing. The following code prints out the employee's name, title, and hire date in a loop.

while (Empiterator.next ()) {
System.out.println ("Name:" + empiterator.ename ());
System.out.println ("Job:" + empiterator.job ());
System.out.println ("Hire Date:" +
Empiterator.hiredate (). toString ());
}
Empiterator.close ();

Close iterator object: <0}
Empiterator.close ();

The program segment, joined from steps 2nd through 5th, demonstrates the Listemployees () method that uses the named iterator and the Empsalary parameter. Position iterator: In contrast to a named iterator, a location iterator simply specifies the number and type of columns, not their names. Column data can be accessed only by location, via traditional fetch ... into syntax. In addition to using the FETCH statement, you need to use the position iterator method Endfetch () to detect the end condition and jump out of the loop. However, you must check the conditions between accessing the data that you need to obtain.

The following are the same five steps that demonstrate how to use a location iterator:

To declare an iterator class:

#sql iterator Empiteratorclass (String, String, Timestamp);

Declares an iterator object from an iterator class. You must also declare the primary variables that are necessary for the data obtained from the iterator object.

Empiteratorclass Empiterator;
String name = NULL;
String job = null;
Timestamp hiredate = null;

To add an iterator object by using an SQL SELECT statement:

int salary = 1500;
#sql Empiterator = {
Select Ename, Job, HireDate
From EMP
where Sal >: Salary
};

To read data from an iterator object into host variables:

while (true) {
#sql {fetch:empiterator into:name,: Job,: HireDate};
if (Empiterator.endfetch ()) {
Break
}

System.out.println ("Name:" + name);
System.out.println ("Job:" + job);
System.out.println ("Hire Date:" + hiredate (). toString ());
}

Close an Iterator object

Empiterator.close ();

The combination of steps 2nd through 5th demonstrates the use of the Listemployees () method of position iterators and empsalary parameters. It is visible that the position iterator object uses syntax much like the syntax of the PL/SQL cursor. Both the named iterator and the location iterator perform essentially the same functions: they all save the results of the SQL query, and there are probably a lot of rows of data as to what kind of iterative program to use depends on the situation or on the basis of their preference, from a performance point of view, they produce the same results.

Executable statement

The executable SQLJ statement contains a static SQL operation within a pair of curly braces. There are two possible executable statements, which are determined by whether the SQL returns a value. The following is an example of an embedded SQL statement that does not return a value, and it creates a composite index on the ename and Sal columns of the EMP table:
#sql {CREATE index emp_ename_sal on EMP (ename, SAL)};

If an Embedded SQL statement returns a value, you need to use a host variable to specify where the result should be placed. In this example, the call to the Pl/sql function Getsalary returns the salary of an employee with an employee number of empno 7900. You can use values or set operators to invoke a function;

int salary;
int empNo = 7900;
#sql salary = {VALUES getsalary (: EmpNo}};
Or
#sql {set:salary = Getsalary (: empNo)};

Host-expression

In the above example, we can see that host variables allow the SQLJ program to exchange information between the database and the Java program. They are any variables declared in the Java program. Host variables are embedded in the SQLJ statement, called host expressions. Host expressions bind host variables to SQLJ executable statements, and they may also include Java array elements, object properties, or Java functions. SQLJ is responsible for moving data back and forth between the SQL and Java environments. All standard JDBC Types-like boolean,byte,short,int,string,byte [],double,float,java.sql.date, and so on. -All are valid host expressions in SQLJ. In addition, Oracle's SQLJ translators support the use of Oracle database types, such as Rowid,clob,blob and object, and ref types. In this article, I discussed the types of SQLJ objects that are necessary to write actual SQLJ code: Connection context, naming and location iterators, executable statements, and host expressions. In a future article, I want to write a server-side SQLJ program, compile it, and then configure it into Oracle Jserver and compare it pl/sql the corresponding program for performance comparisons.

Code Snippet 1
public static void Listemployees (String empsalary)
Throws SQLException {
Empiteratorclass Empiterator;
Integer salary = new Integer (empsalary);
try {
#sql Empiterator = {
Select Ename, Job, HireDate
From EMP
where Sal >: Salary
};
while (Empiterator.next ()) {
System.out.println ("Name:" + empiterator.ename ());
System.out.println ("Job:" + empiterator.job ());
System.out.println ("Hire Date:" +
Empiterator.hiredate (). toString ());
}
Empiterator.close ();
catch (SQLException e) {
System.err.println ("SQLException" + e);
System.exit (1);
}
}
Code Snippet 2
public static void Listemployees (String empsalary)
Throws SQLException {
Empiteratorclass Empiterator;
Integer salary = new Integer (empsalary);
/* Host variable */
String name = NULL;
String job = null;
Timestamp hiredate = null;
try {
#sql Empiterator = {
Select Ename, Job, HireDate
From EMP
where Sal >: Salary
};
while (true) {
#sql {fetch:empiterator into:name,: Job,: HireDate};
if (Empiterator.endfetch ()) {
Break
}

System.out.println ("Name:" + name);
System.out.println ("Job:" + job);
System.out.println ("Hire Date:" + hiredate (). toString ());
}
Empiterator.close ();
catch (SQLException e) {
System.err.println ("SQLException" + e);
System.exit (1);
}
}