用SQLData读写数据库自定义类型

时间：2022-06-14 16:20:37

如何读写自定义类型？SQLData是个很直观的解决办法

在oracle使用手册上找到了很好的资料

点击打开链接

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

还有详细例子（例子很好）

点击打开链接

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

但原文太长，我精炼提取了关于SQLData的关键部分如下（大家只能读英文了），红色部分是实例代码，我创建了一个自定义类型MyTime由小时和分组成，比如13点

55分就是13:55

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 particular Java classes. Two main 

           steps in using JDBC to access Oracle objects are: 1) creating the Java classes for 

           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 to.

          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 the

          oracle.sql.STRUCT class.       

        */

 String sql = "insert into train values(?, ?, ?, ?, ?, ?)";//第五个参数是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);

下面是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 a 

   * readSQL() method and a writeSQL() method, as specified by the SQLData interface.





       The JDBC driver calls your readSQL() method to read a stream of data values from the database

        and populate an instance of your custom object class. Typically, the driver would use this

         method as part of an OracleResultSet object getObject() call.





       Similarly, the JDBC driver calls 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 would 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 includes 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() would 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 calls setObject(), the JDBC driver creates 

       a SQLOutput stream object and populates it with data from a custom object 

       class instance. When the driver calls 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 a 

       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 the 

       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 calls 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 calls

       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 calls these methods according to

       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;

      }

   }

}

附上

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 choose the SQLData implementation 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 object stmt.

  1. Query the database to read the Oracle object into a JDBC result set. 
    ResultSet rs = stmt.executeQuery("SELECT emp_col FROM personnel");

    The PERSONNEL table contains one column, EMP_COL, of SQL type EMP_OBJECT. This SQL type is defined in the type map to map to the Java class Employee.

  2. Use the getObject() method of your result set to populate an instance of your custom object class with data from one row of the result
    set. The getObject() method returns the user-defined SQLData object because the type map contains an entry for Employee. 
    if (rs.next())
    
   Employee emp = (Employee)rs.getObject(1);

    Note that if the type map did not have an entry for the object, then getObject() would return an oracle.sql.STRUCT object. Cast the output to type STRUCT, because the getObject() method signature
    returns the generic java.lang.Object type.

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

    The getObject() call triggers readSQL() and readXXX() calls from the SQLData interface, as described above.

    Note:

    If you want to avoid using a type map, then use the getSTRUCT() method. This method always returns a STRUCT object, even if there is a mapping entry in the type map.
  3. If you have get methods in your custom object class, then use them to read data from your object attributes. For example, if EMPLOYEE has
    an EmpName (employee name) of type CHAR, and an EmpNum (employee number) of type NUMBER, then provide a getEmpName() method that returns a Java String and a getEmpNum() 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 has a getObject() 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 choose the SQLData implementation 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 have set methods 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 uses the emp object and the empname and empnumber variables 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 assumes conn is your connection object.

  3. Use the setObject() 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();

相关文章