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());
}
}
}