Re: Object Question

From: VC <boston103_at_hotmail.com>
Date: Sun, 18 Apr 2004 20:24:19 GMT
Message-ID: <TtBgc.21639$0b4.26886@attbi_s51>

Hello Daniel,

Pls. see below

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1082310156.645035_at_yasure...
> Thomas Kyte wrote:
> > Daniel Morgan <damorgan_at_x.washington.edu> wrote in message
news:<1082237735.832467_at_yasure>...
> >
> >>In the database I do the following:
> >>
> >>CREATE OR REPLACE TYPE rectangle_t AS OBJECT (
> >>h NUMBER,
> >>w NUMBER,
> >>x NUMBER,
> >>y NUMBER);
> >>/
> >>
> >>CREATE TABLE rectable (
> >>rectangle_id NUMBER(3),
> >>rectangle rectangle_t);
> >>
> >>Now the question. Does anyone have a simple example built with
> >>JDeveloper or any other Java framework that can select, insert,
> >>update, and delete this all the time treating it as an object?
> >>
> >>And, of course, would you mind sharing it for my class.
> >>
> >>Thanks.
> >
> >
> > One word:
> >
> > Jpublisher
> >
> >

http://download-west.oracle.com/docs/cd/B10501_01/java.920/a96658/toc.htm
>
> Exactly what I was looking for. Thanks.

You need to implement mapping between the Java class and the Oracle type "manually" (that's what JPublisher does ). The code is not pretty, but jpub does essentially the same:

The "Rectangle" class:

import java.sql.*;
public class Rectangle implements SQLData{

        // Another place where the Oracle type has to be specified in addition to the mapping in the main program.

        //  It's unclear why but Oracle somehow cannot work without it.
        private String sql_type = "HR.RECTANGLE_T";
        // The object variables
        public float h;
        public float w;
        public float x;
        public float y;

        // You need to implement all these "methods in order to read/write

the object:

        public String getSQLTypeName() throws SQLException { return sql_type; }

        public void readSQL(SQLInput stream, String typeName) throws
SQLException       {
          sql_type = typeName;
          h = stream.readFloat();
          w = stream.readFloat();
          x = stream.readFloat();
          y = stream.readFloat();
        }
        public void writeSQL(SQLOutput stream) throws SQLException {
                stream.writeFloat(h);
                stream.writeFloat(w);
                stream.writeFloat(x);
                stream.writeFloat(y);
        }

}

---

The main program:

import java.sql.*;
import java.util.*;
public class testobject {

        public static void main(String[] args) throws Exception {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                String url = "jdbc:oracle:thin:@localhost:1521:orcl1";
                Connection conn = DriverManager.getConnection(url, "hr",

"hr");

                // Create mapping between the Oracle type and the Java class
                Map map = new HashMap();
                map.put("HR.RECTANGLE_T", Class.forName("Rectangle"));
                conn.setTypeMap(map);

                // Create a new rectangle
                Rectangle r = new Rectangle();
                r.h = 10.1F;
                r.w = 11.2F;
                r.x = 15.79F;
                r.y = 17F;

                // Insert the object
                PreparedStatement pstmt1 = conn.prepareStatement
                                                                  ("insert
into rectable values(?, ?)");
                pstmt1.setInt(1, 12);
                pstmt1.setObject(2, r);
                pstmt1.executeUpdate();

                // Select and display the object
                PreparedStatement pstmt2 = conn.
                  prepareStatement("select rectangle from rectable where
rectangle_id = ?");
                pstmt2.setInt(1, 12);

                ResultSet rs = pstmt2.executeQuery();
                while (rs.next()) {
                  r = (Rectangle)rs.getObject(1);
                  System.out.println(r.w);
                  System.out.println(r.h);
                  System.out.println(r.x);
                  System.out.println(r.y);
                }

                // Delete it
                PreparedStatement pstmt3 = conn.
                  prepareStatement("delete from rectable where rectangle_id
= ?");
                pstmt3.setInt(1, 12);
                pstmt3.executeUpdate();

                conn.close();
        }

}

==

Output:

11.2
10.1
15.79
17.0
11.2
10.1
15.79
17.0

Rgds. Received on Sun Apr 18 2004 - 15:24:19 CDT