Use SQLData to read and write custom database types, and use sqldata to read and write custom

Use SQLData to read and write custom database types, and use sqldata to read and write custom

How do I read and write custom types? SQLData is an intuitive solution

Good information was found in the oracle user manual.

Click Open Link

Http://docs.oracle.com/cd/B10501_01/java.920/a96654/oraoot.htm#1039738

There are also detailed examples (the example is good)

Click Open Link

Https://docs.oracle.com/cd/F49540_01/DOC/java.815/a64685/samapp4.htm

However, the original article is too long. I have extracted the key parts about SQLData as follows (you can only read English ),The red part is the instance code I wrote., I created a custom type MyTime consisting of hours and minutes, such

Is

Create or replace type MyTime as OBJECT (h int, m int );

Oracle object types provide support for composite data structures in the database.
JDBC materializes Oracle objects as instances of particle Java classes. Two main
Steps in using JDBC to access Oracle objects are: 1) creating the Java classes
The Oracle objects, and 2) populating these classes. You have two options:
1. Let JDBC materialize the object as a STRUCT.
2. Explicitly specify the mappings between Oracle objects and Java classes. you can define
Your classes to implement either the JDBC standard java. SQL. SQLData interface or the Oracle
Extension oracle. SQL. ORAData interface
If you want to create custom object classes for your Oracle objects, then you must define
Entries in the type map that specify the custom object classes that the drivers will
Instantiate for the corresponding Oracle objects.

Map <String, Class <?> > Map = sample. getTypeMap ();
/* Use the getTypeMap () method of your OracleConnection object to return the connection's
* Type map object.
If the type map in the OracleConnection instance has not been initialized, then the first
Call to getTypeMap () returns an empty map .*/
Map. put ("MYTIME", MyTime. class );
/* Use the type map's put () method to add map entries. The put () method takes two arguments:
* A SQL type name string and an instance of a specified Java class that you want to map.
MyMap. put (sqlTypeName, classObject );
SQL type names in the type map must be all uppercase, because that is how the Oracle
Database stores SQL names.
The sqlTypeName is a string that represents the fully qualified name of the SQL type in
The database. The classObject is the Java class object to which you want to map the SQL
Type. Get the class object with the Class. forName () method, as follows:
MyMap. put (sqlTypeName, Class. forName (className ));
*/
Sample. setTypeMap (map );
/* When you finish adding entries to the map, use the OracleConnection object's setTypeMap ()
* Method to overwrite the connection's existing type map. For example:
Oraconn. setTypeMap (newMap );
In this example, setTypeMap () overwrites the oraconn connection's original map with newMap.
If you do not provide a type map with an appropriate entry when using a getObject () call,
Then the JDBC driver will materialize an Oracle object as an instance of
Oracle. SQL. STRUCT class.
*/

String SQL = "insert into train values (?, ?, ?, ?, ?, ?) "; // The Fifth parameter is MyTime.
PreparedStatement pstmt = sample. prepareStatement (SQL );

/* Use the setObject () method of the prepared statement to bind your Java datatype
* Object to the prepared statement.
Pstmt. setObject (1, emp );
Use the getObject () method to retrieve the employee object. The following code
Assumes that there is a type map entry to map the Oracle object to Java type Employee:
Employee emp = (Employee) ocs. getObject (1 );
*/
Pstmt. setObject (5, mt2 );

The following is the class definition of MyTime.

Static class MyTime implements SQLData
{
/* The SQLData interface defines methods that translate between SQL and Java for Oracle
* Database objects.
* If you create a custom object class that implements SQLData, then you must provide
* ReadSQL () method and a writeSQL () method, as specified by the SQLData interface.


The JDBC driver callyour readSQL () method to read a stream of data values from the database
And populate an instance of your custom object class. Typically, the driver wocould use this
Method as part of an asynchronous leresultset object getObject () call.


Similarly, the JDBC driver CILS your writeSQL () method to write a sequence of data values
From an instance of your custom object class to a stream that can be written to the database.
Typically, the driver wocould use this method as part of an OraclePreparedStatement object
SetObject () call.
*
*
**/
Public int h;
Public int m;
Private String sqlUdt = "MYTIME ";
Public MyTime (int hh, int mm ){
H = hh;
M = mm;
}
/* The SQLInput implementation is an input stream class, an instance of which must be passed
* In to the readSQL () method.
* Each readXXX () method converts SQL data to Java data and returns it into an output parameter
* Of the corresponding Java type. For example, readInt () returns an integer.
* The SQLOutput implementation is an output stream class, an instance of which must be passed
* In to the writeSQL () method. SQLOutput into des a writeXXX () method for each of these Java
* Types. Each writeXXX () method converts Java data to SQL data,
* Taking as input a parameter of the relevant Java type. For example, writeString () wocould take
* As input a string attribute from your Java class.
**/
/* You must implement writeSQL () as follows:


Public void writeSQL (SQLOutput stream) throws SQLException


The writeSQL () method takes as input a SQLOutput stream.


When your Java application CILS setObject (), the JDBC driver creates
A SQLOutput stream object and populates it with data from a custom object
Class instance. When the driver CILS writeSQL (), it passes in this stream parameter.


For each Java datatype that maps to an attribute of the Oracle object, writeSQL () must
Call the appropriate writeXXX () method of the SQLOutput stream that is passed in.


For example, if you are writing to EMPLOYEE objects that have an employee name as
CHAR variable and an employee number as a NUMBER variable, then you must have a writeString ()
Call and a writeInt () call in your writeSQL () method. These methods must be called according
To the order in which attributes appear in the SQL definition of the Oracle object type.


The writeSQL () method then writes the data converted by the writeXXX () methods to
SQLOutput stream so that it can be written to the database once you execute the prepared
Statement.
*
*
*
**/
Public void writeSQL (SQLOutput stream) throws SQLException // Java data to SQL data
{
Stream. writeInt (h );
Stream. writeInt (m );
}
Public String getSQLTypeName () throws SQLException {
Return sqlUdt;
}
/* You must implement readSQL () as follows:


Public void readSQL (SQLInput stream, String SQL _type_name) throws SQLException


The readSQL () method takes as input a SQLInput stream and a string that indicates
The SQL type name of the data (in other words, the name of the Oracle object type,
Such as EMPLOYEE ).


When your Java application CILS getObject (), the JDBC driver creates a SQLInput
Stream object and populates it with data from the database. The driver can also determine
The SQL type name of the data when it reads it from the database. When the driver CILS
ReadSQL (), it passes in these parameters.


For each Java datatype that maps to an attribute of the Oracle object, readSQL () must
Call the appropriate readXXX () method of the SQLInput stream that is passed in.


For example, if you are reading EMPLOYEE objects that have an employee name as a CHAR
Variable and an employee number as a NUMBER variable, you must have a readString () call
And a readInt () call in your readSQL () method. JDBC callthese methods according
The order in which the attributes appear in the SQL definition of the Oracle object type.


The readSQL () method takes the data that the readXXX () methods read and convert, and
Assigns them to the appropriate fields or elements of a custom object class instance .*/
Public void readSQL (SQLInput stream, String typeName) throws SQLException // SQL data to Java data
{
SqlUdt = typeName;
H = stream. readInt ();
M = stream. readInt ();
}
Public String toString (){
String res = h + ":" + m;
Return res;
}
}
}

Attached

Reading SQLData Objects from a Result Set

This section summarizes the steps to read data from an Oracle object into your Java application when you chooseSQLDataImplementation for your custom object class.

These steps assume you have already defined the Oracle object type, created the corresponding custom object class, updated the type map to define the mapping between the Oracle object and the Java class, and defined a statement objectstmt.

1. Query the database to read the Oracle object into a JDBC result set.

   ResultSet rs = stmt.executeQuery("SELECT emp_col FROM personnel");

   ThePERSONNELTable contains one column,EMP_COL, Of SQL typeEMP_OBJECT. This SQL type is defined in the type map to the Java classEmployee.

2. UsegetObject()Method of your result set to populate an instance of your custom object class with data from one row of the result set.getObject()Method returns the user-definedSQLDataObject because the type map contains an entryEmployee.

   if (rs.next())   Employee emp = (Employee)rs.getObject(1);

   Note that if the type map did not have an entry for the object, thengetObject()Wocould returnoracle.sql.STRUCTObject. Cast the output to typeSTRUCT, BecausegetObject()Method signature returns the genericjava.lang.ObjectType.

   if (rs.next())   STRUCT empstruct = (STRUCT)rs.getObject(1);

   ThegetObject()Call triggersreadSQL()AndreadXXX()CallfromSQLDataInterface, as described above.

   Note: If you want to avoid using a type map, then usegetSTRUCT()Method. This method always returnsSTRUCTObject, even if there is a mapping entry in the type map.

3. If you havegetMethods in your custom object class, then use them to read data from your object attributes. For example, ifEMPLOYEEHasEmpName(Employee name) of typeCHAR, AndEmpNum(Employee number) of typeNUMBER, Then providegetEmpName()Method that returns a JavaStringAndgetEmpNum()Method that returns an integer (int). Then invoke them in your Java application, as follows:

   String empname = emp.getEmpName();int empnumber = emp.getEmpNum(); 

   Note: Alternatively, fetch data by using a callable statement object, which also hasgetObject()Method.

Writing Data to an Oracle Object Using a SQLData Implementation

This section describes the steps in writing data to an Oracle object from your Java application when you chooseSQLDataImplementation for your custom object class.

This description assumes you have already defined the Oracle object type, created the corresponding Java class, and updated the type map to define the mapping between the Oracle object and the Java class.

1. If you havesetMethods in your custom object class, then use them to write data from Java variables in your application to attributes of your Java datatype object.

   emp.setEmpName(empname);emp.setEmpNum(empnumber);

   This statement usesempObject andempnameAndempnumberVariables assigned in "Reading SQLData Objects from a Result Set ".

2. Prepare a statement that updates an Oracle object in a row of a database table, as appropriate, using the data provided in your Java datatype object.

   PreparedStatement pstmt = conn.prepareStatement                          ("INSERT INTO PERSONNEL VALUES (?)");

   This assumesconnIs your connection object.

3. UsesetObject()Method of the prepared statement to bind your Java datatype object to the prepared statement.

   pstmt.setObject(1, emp);

4. Execute the statement, which updates the database.

   pstmt.executeUpdate();