158.Oracle数据库SQL开发之 JAVA——JDBC扩展
欢迎转载，转载请标明出处：blog.csdn.net/notbaron/ar…\
ORACLE对JDBC的扩展允许访问ORACLE提供的所有数据类型和ORACLE特有的性能扩展。
ORACLE公司提供了两个JDBC扩展包：
l Oracle.sql包含支持所有oracle数据库类型的类
l Oracle.jdbc包含支持访问oracle数据库的接口
1. Oracle.sql包
Oracle.sql包包含支持所有ORACLE数据库类型的类。使用该包中定义的类的对象存储数据库值比使用正常JAVA对象效率要高。扩展了oracle.sql.Datum类。
Oracle.sql.NUMBER类与数据库INTEGER和NUMBER类型兼容，可用于表示最大精度为38位的数字。
Oracle.sql.CHAR类与数据库的CHAR、VARCHAR2、NCHAR和NVARCHAR2类型兼容。
创建oracle.sql.CHAR对象时，必须遵照下列两个步骤：
l 创建一个oracle.sql.CharacterSet对象，包含想要使用的字符集
l 使用oracle.sql.CharacterSet对象创建一个oracle.sql.CHAR对象
Oracle.sql.DATE类兼容数据库DATE类型。
Oracle.sql.ROWID类兼容数据库ROWID类型。
2. Oracle.jdbc包
使用oracle.sql对象，oracle.jdbc包的类和接口允许读取和写入数据库中的列值。
OraclePreparedStatement接口实现java.sql.PreparedStatement.
OracleResultSet接口实现java.sql.Result,而且包含可以处理oracle.sql对象的get方法。
3. 示例程序3
3.1 代码如下：
import java.sql.*;
// import the Oracle JDBCextension packages
import oracle.sql.*;
import oracle.jdbc.*;
publicclassBasicExample3 {
publicstaticvoidmain (Stringargs[]) {
try{
// register the Oracle JDBC drivers
DriverManager.registerDriver(
new oracle.jdbc.OracleDriver()
);
// EDIT AS NECESSARY TO CONNECT TO YOURDATABASE
// create a Connection object, and connectto the database
// as the store user using the Oracle JDBCThin driver
Connection myConnection = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.1.201:1521/pdb1",
"store",
"store_password"
);
// disable auto-commit mode
myConnection.setAutoCommit(false);
// create an oracle.sql.NUMBER object
oracle.sql.NUMBER customerId = neworacle.sql.NUMBER(6);
intcustomerIdInt=customerId.intValue();
System.out.println("customerIdInt = " +customerIdInt);
// create two oracle.sql.CHAR objects
oracle.sql.CharacterSet myCharSet = CharacterSet.make(CharacterSet.US7ASCII_CHARSET);
oracle.sql.CHAR firstName = neworacle.sql.CHAR("Jason",myCharSet);
String firstNameString= firstName.stringValue();
System.out.println("firstNameString = " +firstNameString);
oracle.sql.CHAR lastName = neworacle.sql.CHAR("Price",myCharSet);
System.out.println("lastName = " +lastName);
oracle.sql.DATE dob = neworacle.sql.DATE("1969-02-22 13:54:12");
String dobString= dob.stringValue();
System.out.println("dobString = " +dobString);
// create an OraclePreparedStatement object
OraclePreparedStatement myPrepStatement =
(OraclePreparedStatement) myConnection.prepareStatement(
"INSERTINTO customers " +
"(customer_id, first_name, last_name, dob, phone) VALUES (" +
"?, ?, ?, ?, ?" +
")"
);
// bind the objects to theOraclePreparedStatement using the
// appropriate set methods
myPrepStatement.setNUMBER(1,customerId);
myPrepStatement.setCHAR(2,firstName);
myPrepStatement.setCHAR(3,lastName);
myPrepStatement.setDATE(4,dob);
// set the phone column to NULL
myPrepStatement.setNull(5,OracleTypes.CHAR);
// run the PreparedStatement
myPrepStatement.execute();
System.out.println("Added row to customers table");
// retrieve the ROWID, customer_id,first_name, last_name,dob, and
// phone columns for this new row using anOracleResultSet
// object
Statement myStatement = myConnection.createStatement();
OracleResultSet customerResultSet =
(OracleResultSet) myStatement.executeQuery(
"SELECT ROWID, customer_id, first_name, last_name, dob, phone " +
"FROM customers " +
"WHERE customer_id = 6"
);
System.out.println("Retrieved row from customerstable");
// declare an oracle.sql.ROWID object tostore the ROWID, and
// an oracle.sql.CHAR object to store thephone column
oracle.sql.ROWID rowid;
oracle.sql.CHAR phone = neworacle.sql.CHAR("",myCharSet);
// display the column values for row usingthe
// get methods to read the values
while(customerResultSet.next()){
rowid = customerResultSet.getROWID("ROWID");
customerId = customerResultSet.getNUMBER("customer_id");
firstName = customerResultSet.getCHAR("first_name");
lastName = customerResultSet.getCHAR("last_name");
dob = customerResultSet.getDATE("dob");
phone = customerResultSet.getCHAR("phone");
System.out.println("rowid = " +rowid.stringValue());
System.out.println("customerId = " +customerId.stringValue());
System.out.println("firstName = " +firstName);
System.out.println("lastName = " +lastName);
System.out.println("dob = " +dob.stringValue());
System.out.println("phone = " +phone);
} // end of while loop
// close the OracleResultSet object usingthe close() method
customerResultSet.close();
// rollback the changes made to thedatabase
myConnection.rollback();
// close the other JDBC objects
myPrepStatement.close();
myConnection.close();
} catch(SQLExceptione) {
System.out.println("Error code = " +e.getErrorCode());
System.out.println("Error message = " +e.getMessage());
System.out.println("SQL state = " +e.getSQLState());
e.printStackTrace();
}
} // end of main()
}
3.2 输出结果如下：
customerIdInt = 6
firstNameString = Jason
lastName = Price
dobString = 1969-02-22 13:54:12
Added row to customers table
Retrieved row from customers table
rowid = AAAWkLAAKAAAAGHAAA
customerId = 6
firstName = Jason
lastName = Price
dob = 1969-02-22 13:54:12
phone = null
