JDBC Versus SQLJ Sample Code
来源:互联网 发布:淘宝联盟三段式pid 编辑:程序博客网 时间:2024/06/05 20:05
--from http://download.oracle.com/docs/cd/B10500_01/java.920/a96655/overview.htm#1004180
This section presents a side-by-side comparison of two versions of the same sample code--one version written in JDBC and the other in SQLJ. The objective of this section is to point out the differences in coding requirements between SQLJ and JDBC.
The particulars of SQLJ statements and features used here are described later in this manual, but this example is still useful here to give you a general idea in comparing and contrasting SQLJ and JDBC. You can look at it again when you are more familiar with SQLJ concepts and features.
In the sample, two methods are defined: getEmployeeAddress()
, which selects from a table and returns an employee's address based on the employee's number, and updateAddress()
, which takes the retrieved address, calls a stored procedure, and returns the updated address to the database.
In both versions of the sample code, the following assumptions are made:
- A SQL script (not shown here) has been run to create the schema in the database and populate the tables. Both versions of the sample code refer to objects and tables created by this script.
- A PL/SQL stored function
UPDATE_ADDRESS()
exists, and updates a given address. - The
Connection
object (for JDBC) and default connection context (for SQLJ) have been created previously by the caller. - Exceptions are handled by the caller.
- The value of the address argument (
addr
) passed to theupdateAddress()
method can be null.
Note:The JDBC and SQLJ versions of the sample code are only partial samples and cannot run independently. There is no
main()
method in either.
JDBC Version of the Sample Code
Following is the JDBC version of the sample code, which defines methods to retrieve an employee's address from the database, update the address, and return it to the database. Note that the to-do items in the comment lines indicate where you might want to add additional code to increase the usefulness of the code sample.
import java.sql.*;
import oracle.jdbc.*;
/**
This is what we have to do in JDBC
**/
public class SimpleDemoJDBC // line 7
{
//TO DO: make a main that calls this
public Address getEmployeeAddress(int empno, Connection conn)
throws SQLException // line 13
{
Address addr;
PreparedStatement pstmt = // line 16
conn.prepareStatement("SELECT office_addr FROM employees" +
" WHERE empnumber = ?");
pstmt.setInt(1, empno);
OracleResultSet rs = (OracleResultSet)pstmt.executeQuery();
rs.next(); // line 21
//TO DO: what if false (result set contains no data)?
addr = (Address)rs.getORAData(1, Address.getORADataFactory());
//TO DO: what if additional rows?
rs.close(); // line 25
pstmt.close();
return addr; // line 27
}
public Address updateAddress(Address addr, Connection conn)
throws SQLException // line 30
{
OracleCallableStatement cstmt = (OracleCallableStatement)
conn.prepareCall("{ ? = call UPDATE_ADDRESS(?) }"); //line 34
cstmt.registerOutParameter(1, Address._SQL_TYPECODE, Address._SQL_NAME);
// line 36
if (addr == null) {
cstmt.setNull(2, Address._SQL_TYPECODE, Address._SQL_NAME);
} else {
cstmt.setORAData(2, addr);
}
cstmt.executeUpdate(); // line 43
addr = (Address)cstmt.getORAData(1, Address.getORADataFactory());
cstmt.close(); // line 45
return addr;
}
}
Line 12:
In the getEmployeeAddress()
method definition, you must pass the connection object to the method definition explicitly.
Lines 16-20:
Prepare a statement that selects an employee's address from the EMPLOYEES
table, based on the employee number. The employee number is represented by a marker variable, which is set with the setInt()
method. Note that because the prepared statement does not recognize "INTO
" syntax, you must provide your own code to populate the address (addr
) variable. Because the prepared statement is returning a custom object, cast the output to an Oracle result set.
Lines 21-23:
Because the Oracle result set contains a custom object of type Address
, use the getORAData()
method to retrieve it. The Address
class can be created by JPublisher. The getORAData()
method requires a "factory" object that it can use to create additional custom objects (additional Address
objects in this case) as it retrieves the data to populate them. Use the static factory method Address.getORADataFactory()
to materialize an Address
factory object for the getORAData()
method to use.
Because getORAData()
returns a Datum
, cast the output to an Address
object.
Note that the routine assumes a one-row result set. The to-do items in the comment statements indicate that you must write additional code for the cases where the result set contains either no rows or more than one row.
Lines 25-27:
Close the result set and prepared statement objects, then return the addr
variable.
Line 29:
In the updateAddress()
definition, you must pass the connection object and the Address
object explicitly.
The updateAddress()
method passes an address object (Address
) to the database for update, then fetches it back. The actual updating of the address is performed by the stored function UPDATE_ADDRESS()
. (The code for this function is not provided in this example.)
Line 33-43:
Prepare an Oracle callable statement that takes an address object (Address
) and passes it to the UPDATE_ADDRESS()
stored procedure. To register an object as an output parameter, you must know the SQL type code and SQL type name of the object.
Before passing the address object (addr
) as an input parameter, the program must determine whether addr
has a value or is null. Depending on the value of addr
, the program calls different setter methods. If addr
is null, the program calls setNull()
; if addr
has a value, the program calls setORAData()
.
Line 44:
Fetch the return result addr
. Because the Oracle callable statement returns a custom object of type Address
, use the getORAData()
method to retrieve it. The Address
class can be created by JPublisher. The getORAData()
method requires you to use the factory method Address.getORADataFactory
to materialize an instance of an Address
object. Because getORAData()
returns a Datum
object, cast the output to an Address
object.
Lines 45, 46:
Close the Oracle callable statement, then return the addr
variable.
Coding Requirements of the JDBC Version
Note the following coding requirements for the JDBC version of the sample code:
- The
getEmployeeAddress()
andupdateAddress()
definitions must explicitly include the connection object. - Long SQL strings must be concatenated with the SQL concatenation character ("+").
- You must explicitly manage resources. For example, close result set and statement objects.
- You must cast datatypes as needed.
- You must know the
_SQL_TYPECODE
and_SQL_NAME
values of the factory object and any objects that you are registering as output parameters. - Null data must be explicitly processed.
- Host variables must be represented by parameter markers in callable and prepared statements.
- If you want to reuse statement objects, for example if you want to repeatedly call
getEmployeeAddress()
andupdateAddress()
, then you must code this appropriately. Both Oracle SQLJ and Oracle JDBC support statement caching.
Maintaining JDBC Programs
JDBC programs are potentially expensive to maintain. For example, in the above code sample, if you add another WHERE
clause, then you must change the SELECT
string. If you append another host variable, then you must increment the index of the other host variables by one. A simple change to one line in a JDBC program might require changes in several other areas of the program.
SQLJ Version of the Sample Code
Following is the SQLJ version of the sample code that defines methods to retrieve an employee's address from the database, update the address, and return it to the database.
import java.sql.*;
/**
This is what we have to do in SQLJ
**/
public class SimpleDemoSQLJ // line 6
{
//TO DO: make a main that calls this
public Address getEmployeeAddress(int empno) // line 10
throws SQLException
{
Address addr; // line 13
#sql { SELECT office_addr INTO :addr FROM employees
WHERE empnumber = :empno };
return addr;
}
// line 18
public Address updateAddress(Address addr)
throws SQLException
{
#sql addr = { VALUES(UPDATE_ADDRESS(:addr)) }; // line 22
return addr;
}
}
Line 10:
The getEmployeeAddress()
method does not require an explicit connection object. SQLJ can use a default connection context instance, which would have been initialized previously somewhere in the application.
Lines 13-15:
The getEmployeeAddress()
method retrieves an employee address according to employee number. Use standard SQLJ SELECT
INTO
syntax to select an employee's address from the employee table if the employee number matches the one (empno
) passed in to getEmployeeAddress()
. This requires a declaration of the Address object (addr
) that will receive the data. The empno
and addr
variables are used as input host variables.
Line 16:
The getEmployeeAddress()
method returns the addr
object.
Line 19:
The updateAddress()
method also uses the default connection context instance.
Lines 19-22:
The address is passed to the updateAddress()
method, which passes it to the database. The database updates it and passes it back. The actual updating of the address is performed by the UPDATE_ADDRESS()
stored function. (The code for this function is not shown here.) Use standard SQLJ function-call syntax to receive the address object (addr
) output by UPDATE_ADDRESS()
.
Line 23:
The updateAddress()
method returns the addr
object.
Coding Requirements of the SQLJ Version
Note the following coding requirements (and lack of requirements) for the SQLJ version of the sample code:
- An explicit connection is not required--SQLJ can use a default connection context that has been initialized previously in the application.
- No datatype casting is required.
- SQLJ does not require knowledge of
_SQL_TYPECODE
,_SQL_NAME
, or factories. - Null data is processed implicitly.
- No explicit code for resource management (for closing statements or results sets, for example) is required.
- SQLJ embeds host variables, in contrast to JDBC, which uses parameter markers.
- String concatenation for long SQL statements is not required.
- You do not have to register output parameters.
- SQLJ syntax is simpler. For example,
SELECT INTO
statements are supported and OBDC-style escapes are not used. - You do not have to implement your own statement cache. By default, SQLJ will automatically cache
#sql
statements. This results in improved performance, for example, if you repeatedly callgetEmployeeAddress()
andupdateAddress()
.
Alternative Deployment Scenarios
Although this manual mainly discusses writing for client-side SQLJ applications, you may find it useful to run SQLJ code in the following scenarios:
- from an applet
- in the server (optionally running the SQLJ translator in the server as well)
- against Oracle9i Lite
Running SQLJ in Applets
Because the SQLJ runtime is pure Java, you can use SQLJ source code in applets as well as applications. There are, however, a few considerations, as discussed below.
For applet issues that apply more generally to the Oracle JDBC drivers, see the Oracle9i JDBC Developer's Guide and Reference, which includes discussion of firewalls and security issues as well.
General Development and Deployment Considerations
The following general considerations apply to the use of Oracle SQLJ applets.
- You must package all the SQLJ runtime packages with your applet:
sqlj.runtime
sqlj.runtime.ref
sqlj.runtime.profile
sqlj.runtime.profile.ref
sqlj.runtime.erroras well as the following if you used Oracle customization (for ISO code generation):
oracle.sqlj.runtime
oracle.sqlj.runtime.errorThese classes are included with your Oracle installation in one of several runtime libraries in the
[Oracle_Home]/lib
directory. (See "Requirements for Using Oracle SQLJ".) - You must specify a pure Java JDBC driver, such as the Oracle JDBC Thin driver, for your database connection.
- You must explicitly specify a connection context instance for each SQLJ executable statement in an applet. This is a requirement because you could conceivably run two SQLJ applets in a single browser and, thus, in the same JVM. (For information about connections, see "Connection Considerations".)
- The default translator setting
-codegen=oracle
generates Oracle-specific code. This will eliminate the use of Java reflection at runtime, thereby increasing portability across different browser environments. For information about the-codegen
option, see "Code Generation (-codegen)". For general information about Oracle-specific code generation, see "Oracle-Specific Code Generation (No Profiles)".
- JDBC Versus SQLJ Sample Code
- sample code
- JDK 1.5 Sample Code
- Events Sample Code(1)
- Events Sample Code(2)
- logic font sample code
- MetaWeblogAPI C# Code Sample
- JavaScript Sample Code
- XMl - xerces code sample
- java concurrent code sample
- Sample CRC Code
- sample code of webview
- Spring AOP Sample Code
- sample transaction code
- File Output sample code
- Thread sample code
- MKMapView Sample Code
- !!!C++ sample code
- 关于算法的一些想法
- 孙强:IT治理需六方结合
- 不知如何走的立交桥(西直门交警一文背景)
- 《给初学者的Windows Vista的补遗手册》之050
- 中国开源众生相-也谈“中国人的开源”
- JDBC Versus SQLJ Sample Code
- 程序员必知必会之 word 篇
- SUSE Linux 10、whitefin、Fedora Core Linux 6桌面效果大比拼
- Python 3.0 抢“鲜”体验
- Xen:使用虚拟 Linux 来测试应用程序
- 《给初学者的Windows Vista的补遗手册》之045
- SaaS专题(二):Salesforce将成为按需世界的微软吗?
- 美日经济恶化 中国软件离岸外包扩张将提速
- AOP联盟的API简介