import java.sql.*;

import oracle.sdoapi.OraSpatialManager;

import oracle.sdoapi.geom.*;

import oracle.sdoapi.adapter.*;

import oracle.sdoapi.sref.*;   

import oracle.sql.STRUCT;   

 

    /**

     * This example is based on Example3

     */

    public class Example6 {

        public static void main(String args[]) {

            Example6 example = new Example6();

           example.run();

       }

   

       private void run() {

           Connection con = null;

           ResultSet result = null;

           try {

               con = openConnection();

               publishData(con);

               result = searchAllTuples(con);

               showMetaDataOfResultSet(result);

               showResultSet(result, con);

           } catch (SQLException e) {

               System.err.println("Errors occurs when communicating with the database server: " + e.getMessage());

           } catch (ClassNotFoundException e) {

               System.err.println("Cannot find the database driver");

           } finally {

               // Never forget to close database connection

               closeConnection(con);

           }

       }

   

       /**

        * This method will first clean up the table and then

        * populate it with new data.

        * @param con

        * @throws SQLException

        */

       private void publishData(Connection con) throws SQLException {

           Statement stmt = con.createStatement();

   

           System.out.println("Deleting previous tuples ...");

           stmt.executeUpdate("delete from spatial_test");

   

           System.out.println("Inserting Data ...");

           stmt.executeUpdate( "insert into spatial_test values ( 1, MDSYS.SDO_GEOMETRY" + "( 2001, NULL, MDSYS.SDO_POINT_TYPE(12, 14, NULL), NULL, NULL) ) " );

           stmt.executeUpdate( "insert into spatial_test values ( 2, MDSYS.SDO_GEOMETRY" + "( 2001, NULL, MDSYS.SDO_POINT_TYPE(3, 5, NULL), NULL, NULL) ) " );

           stmt.executeUpdate( "insert into spatial_test values ( 3, MDSYS.SDO_GEOMETRY" + "( 2001, NULL, MDSYS.SDO_POINT_TYPE(-1, -5, NULL), NULL, NULL) ) " );

           stmt.close();

          System.out.println();

       }

   

       private ResultSet searchAllTuples(Connection con) throws SQLException {

           Statement stmt = con.createStatement();

           return stmt.executeQuery("SELECT * FROM spatial_test");

       }

   

       private void showMetaDataOfResultSet(ResultSet result) throws SQLException {

           ResultSetMetaData meta = result.getMetaData();

           for (int col = 1; col <= meta.getColumnCount(); col++) {

               System.out.println("Column: " + meta.getColumnName(col) +

                                  ",\t Type: " + meta.getColumnTypeName(col));

           }

            System.out.println();

       }

 

           

       private void showResultSet(ResultSet result, Connection con) throws SQLException

      {

              STRUCT point;                     //Structure to handle Geometry Objects

              Geometry geom;       //Structure to handle Geometry Objects

               try

              {

                      // shows result of the query

                      ResultSetMetaData meta = result.getMetaData();

 

                     System.out.println("\n ** Showing all Tuples ** " );

                     int tupleCount=1;

 

                    GeometryAdapter sdoAdapter =

       OraSpatialManager.getGeometryAdapter("SDO", "9",STRUCT.class, null, null, con);

 

                   while( result.next() )

                  {

                      System.out.print( "Tuple " + tupleCount++ + " : " );

 

                      int Point_ID = result.getInt( 1 );

                      System.out.print( "\"" + Point_ID + "\"," );

 

                      point = (STRUCT)result.getObject(2);

                     geom = sdoAdapter.importGeometry( point );

                     if ( (geom instanceof oracle.sdoapi.geom.Point) )

                    {

                          oracle.sdoapi.geom.Point point0 = (oracle.sdoapi.geom.Point) geom;

                          double X = point0.getX();

                          double Y = point0.getY();

                          System.out.print( "\"(X = " + X + ", Y = " + Y + ")\"" );

                     }

 

                     System.out.println();

              }

          }

          catch( Exception e )

         { System.out.println(" Error : " + e.toString() ); }

 

         System.out.println();

 

       }

   

       /**

        *

        * @return a database connection

        * @throws java.sql.SQLException when there is an error when trying to connect database

        * @throws ClassNotFoundException when the database driver is not found.

        */

       private Connection openConnection() throws SQLException, ClassNotFoundException

        {

           // Load the Oracle database driver

            DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

   

           /*

           Here is the information needed when connecting to a database

           server. These values are now hard-coded in the program. In

           general, they should be stored in some configuration file and

           read at run time.

           */

           String host = "shams.usc.edu";

           String port = "1521";

           String dbName = "cs585";

           String userName = "temp";

           String password = "temp585";

   

          // Construct the JDBC URL

          String dbURL = "jdbc:oracle:thin:@" + host + ":" + port + ":" + dbName;

          return DriverManager.getConnection(dbURL, userName, password);

                         

      }

  

      /**

       * Close the database connection

       * @param con

       */

      private void closeConnection(Connection con) {

          try {

              con.close();

          } catch (SQLException e) {

              System.err.println("Cannot close connection: " + e.getMessage());

          }

      }

  }