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

如何读写自定义类型？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的关键部份以下（大家只能读英文了），红色部份是我写的实例代码，我创建了1个自定义类型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(?, ?, ?, ?, ?, ?)";//第5个参数是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();

生活不易，码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠