Java access to spatial databases

Document Sample
Java access to spatial databases Powered By Docstoc
					Java access to spatial databases

                     Daniel Wirz
     Department of Geography – University of Zurich
            mailto:wirz@geo.unizh.ch

                   January 30st, 2004
Java access to spatial databases                                            Overview

                                     Overview
• Java Primer: A very short introduction to some of Javas features and to the core
    object-oriented terminology


• Java and Databases: JDBC Java Database Connectivity

• Acessing Oracle Spatial data using Oracle Spatial’s Java API . . .

   This presentation shares the first two parts with the lecture ”Dynamic Web
Publishing with Java and Oracle” which additionally presents Web Application
Architectures in detail.




     Daniel Wirz c 2004                                                           1
Java access to spatial databases                                  Part 1 – Java Primer

                                   Java Primer
• This is not an extensive Java-Tutorial! More Information can be found here:
     – Java API Documentation, Oracle Java Documentation, etc.
       http://www.geo.unizh.ch/oai/links.html
     – The source for Java Technology: http://java.sun.com
     – Java Programming – OAI Courseware
       /share/oracle_doc/oai_courseware/javaprog3/studpdf1.pdf
       /share/oracle_doc/oai_courseware/javaprog3/studpdf2.pdf
     – Develop Database Applications with Java: – OAI Courseware
       /share/oracle_doc/oai_courseware/devdba/part1/studpdf.pdf
       /share/oracle_doc/oai_courseware/devdba/part2/studpdf.pdf
     – Create Dynamic Web Content with Java: – OAI Courseware
       /share/oracle_doc/oai_courseware/dynweb/studpdf.pdf


     Daniel Wirz c 2004                                                             2
Java access to spatial databases                   Part 1 – Java Primer

                                   Java Features
• Java is . . .
     –   simple!
     –   object-oriented!
     –   interpreted!
     –   distributed!
     –   robust!
     –   secure!
     –   portable!
     –   fast!
     –   multi-threaded!
     –   dynamic!
     –   ...


     Daniel Wirz c 2004                                              3
Java access to spatial databases                                              Part 1 – Java Primer

                                             Java is simple


                                   Stephen Hawking – ”My goal is simple.
                              It is complete understanding of the universe,
                              why it is as it is and why it exists at all.”

Remark. Simplicity is a matter of perception.




     Daniel Wirz c 2004                                                                         4
Java access to spatial databases                                                               Part 1 – Java Primer

• Java is simple?
     – The Java-Language is simple!
         No pointer-artihmetic, preprocessor; multiple inheritance, or operator overloading.

         Complexity in the API rather than the language.
     –   GarbageCollection - There’s nothing easier than to forget!
     –   Java builds on earlier programming languages, avoids many of their
         intricacies and retains most of their power: Syntax from C, C++; semantics
         from Pascal, Modula, Oberon; Patterns from Smalltalk.
     –   There are simpler languages than Java but they are not as versatile.
     –   Javas strength is not coding simple things but promoting feasible solutions
         for inherently complicated application domains:
         Networking, Multithreading, Transactional Middleware etc.




     Daniel Wirz c 2004                                                                                          5
Java access to spatial databases                                               Part 1 – Java Primer




                                   Remark. Java is no magic bullet. In order to
                                   get things done one still has to do them.




                                      Thomas Edison – ”Opportunity is missed
                                   by most people because it is dressed in
                                   overalls and looks like work.”




     Daniel Wirz c 2004                                                                          6
Java access to spatial databases                                   Part 1 – Java Primer

                                   Java is object-oriented 1/2




• Object-oriented Terminology
     –   Objects are Instances of Classes
     –   An Object encapsulates internal data-structures and private Methods
     –   An Object can call public Methods of other Objects
     –   The public Methods of a Class form its Interface

     Daniel Wirz c 2004                                                              7
Java access to spatial databases                                   Part 1 – Java Primer

                                   Java is object-oriented 2/2

     Polymorphism

Definition. Polymorphism is the characteristic of being able to assign a different
meaning or usage to something in different contexts - specifically, to allow an
entity such as a variable, a method, or an object to have more than one form.

        Douglas Adams – ”Some say that the universe is made so, that when
    we are about to understand it, it changes into something even more
    incomprehensible. And then there are those who say that this has already
    happened.”




     Daniel Wirz c 2004                                                              8
Java access to spatial databases                                         Part 1 – Java Primer




• Polymorphism in Java
     – All complex java types are specializations of java.lang.Object.
         i.e. Collections store Objects

     – Even ’unrelated’ Classes can implement the same independently declared
       Interface

     Daniel Wirz c 2004                                                                    9
Java access to spatial databases                                           Part 1 – Java Primer

     – Clients of these Classes do not have to distinguish between different
       Implementations of the same Interface: The ’BirdController’ does not
       have to know about birds that cannot fly! If he instructs a Goose-Instance
       to move, it will fly. If he does the same for a Penguin-Instance. . . What happens?




     Daniel Wirz c 2004                                                                     10
Java access to spatial databases                                   Part 1 – Java Primer

                                   Java is distributed 1/2
     There is comprehensive support for communication in the Java API.

• JavaTM 2 Platform, Standard Edition, v 1.3.1 API


    java.net Low-Level socket communication and http
    java.rmi Remote Method Invocation
    javax.rmi RMI-IIOP
    javax.naming JNDITM Java Naming and Directory Interface
    org.omg.CORBA Mapping of the OMG CORBA APIs to the Java language.




     Daniel Wirz c 2004                                                             11
Java access to spatial databases                             Part 1 – Java Primer

                                   Java is distributed 2/2
• JavaTM 2 Platform, Enterprise Edition, v 1.3 API


    javax.ejb EJB Enterprise Java Beans
    javax.jms JMS Java Message Service
    javax.mail eMail
    javax.servlet Servlets




     Daniel Wirz c 2004                                                       12
Java access to spatial databases                                   Part 1 – Java Primer

                                   Java is interpreted
       Ambrose (Gwinnett) Bierce, The Devil’s Dictionary – ”Interpreter, n.:
    One who enables two persons of different languages to understand each
    other by repeating to each what it would have been to the interpreter’s
    advantage for the other to have said.”

• Some Facts...
     – javac compiler generates Bytecode
     – Java Virtual Machine (java, or jre) interprets Bytecode
     – Optional Just-In-Time Compiler (jit) translates Bytecode to Native
       code at runtime

Remark. In spite of being an interpreted language Java is strongly typed! Most
other interpreted languages and even C/C++ are weakly typed!

     Daniel Wirz c 2004                                                             13
Java access to spatial databases                                              Part 1 – Java Primer

                                   Java is robust and safe 1/2


                                        Frank Crane – ”You may be deceived
                                    if you trust too much, but you will live in
                                    torment if you do not trust enough!”

• Java allows to adjust the level of security to the specific needs. Its default
  settings are rather on the paranoid side (i.e. for Applets).
     –   Customizeable Security Manager
     –   Bytecode verifier provides low-level security against tampered Bytecode.
     –   javac Compiler enforces strong typing
     –   Exception Handling is built into the language



     Daniel Wirz c 2004                                                                        14
Java access to spatial databases                                 Part 1 – Java Primer

Remark. In concept and theory Java is probably one of the the safest
programming languages available.       In practice however, many current
implementations of Virtual Machines do not accomplish these high expectations
due to security leaks!




     Daniel Wirz c 2004                                                           15
Java access to spatial databases                                   Part 1 – Java Primer

                                   Java is robust and safe 2/2
     An example for exception handling (Bruce Eckel – ”Thinking in Java”).

      try {
         // The guarded region: Dangerous activities
         // that might throw Exceptions
      } catch(AnException a1) {
         // Handler for situation A
      } catch(AnotherStrangeException b1) {
         // Handler for situation B
      } catch(Exception c1) {
         // Handler for all other Exceptions
      } finally {
         // Activities that happen every time
      }

     Daniel Wirz c 2004                                                             16
Java access to spatial databases                                    Part 1 – Java Primer

                            Why exception handling is so valuable
        Capt. Edward A. Murphy – ”Anything that can go wrong will go wrong.
    If anything simply cannot go wrong, it will anyway.”

        Douglas Adams, Mostly Harmless – ”The major difference between a
    thing that might go wrong and a thing that cannot possibly go wrong, is
    that, when a thing that cannot possibly go wrong, goes wrong, it usually
    turns out to be impossible to repair.”

        Douglas Adams, Mostly Harmless – ”A common mistake people make
    when trying to design something completly foolproof is to underestimate
    the ingenuity of complete fools.”



     Daniel Wirz c 2004                                                              17
Java access to spatial databases                              Part 1 – Java Primer

                                   Java is portable
           Sun – ”Write once – Run anywhere!”
           Anonymous – ”Write once – Debug everywhere!”

• Java virtual machines are available on. . .
    mainframes and servers Sun Solaris, HP/UX, AIX, AS/400, MVS. . .
    workstations Most Unix flavours (including Linux and MacOS X), Windows
      NT, 2000 . . . (XP ???)
    desktops Windows 95/98, Mac OS 9, BeOS. . .
    handhelds PalmOS
    embedded devices J2ME on coffee machines, mobile phones, toasters,
      washing machines. . .
      Javas predecessor OAK was dedicated primarily to embedded devices.


     Daniel Wirz c 2004                                                        18
Java access to spatial databases                                      Part 1 – Java Primer

                                   Java is fast
       Woody Allen – ”It is impossible to travel faster than the speed of light,
    and certainly not desirable, as one’s hat keeps blowing off.”

• The notion of Java being fast is probably the most controversial statement,
  but keep in mind. . .
     – Even though Java-Bytecode execution is much slower than running highly
       optimized, natively compiled C or C++ code, it is still one of the fastest
       interpreted languages.
     – Early implementations of Java used to be up to 20 times slower than
       natively compiled C-code. This gap has been reduced significantly with the
       introduction of Java 2 Platform v. 1.3.
     – Much of Java’s perceived slowness can be attributed to deficient
       implementations of virtual machines built into common Web-Browsers and

     Daniel Wirz c 2004                                                                19
Java access to spatial databases                                     Part 1 – Java Primer

       to the unefficient Applet-Architecture in general. These problems are limited
       to Applets and do not occur in Servlets or other Java-Applications.
     – Just-In-Time compilers typically increase Java’s execution speed about
       threefold.
     – Java generally has a sufficient Deployment-Performance and an excellent
       Development-Performance




     Daniel Wirz c 2004                                                               20
Java access to spatial databases                                                   Part 1 – Java Primer

                                       Java is multi-threaded
                                       F. Scott Fitzgerald – ”The test of a first
                                   rate intelligence is the ability to hold two
                                   opposed ideas in the mind at the same time
                                   and still retain the ability to function.”

• Multi-threading is a key feature built into the Java language:
     – Class java.lang.Thread implements interface java.lang.Runnable
     – Keyword synchronized ensures ’single thread model’

⇒ Java servlets can handle multiple http requests simultaneously and without
forking new os-processes!


     Daniel Wirz c 2004                                                                             21
Java access to spatial databases                                             Part 1 – Java Primer

class PrimeThread extends Thread {
   long minPrime;
   PrimeThread(long minPrime) {
      this.minPrime = minPrime;
   }
   public void run() { //compute primes larger than minPrime
              . . .
   }
} // end of class PrimeThread

                             . . . and this is how PrimeThread is used:

PrimeThread p = new PrimeThread(143);
p.start();

                      . . . the client algorithm will continue immediatly!

     Daniel Wirz c 2004                                                                       22
Java access to spatial databases                     Part 1 – Java Primer

                                   Java is dynamic
• Dynamic loading of required classes.

• Introspection, Reflection

• late binding

• Polymorphism




     Daniel Wirz c 2004                                               23
Java access to spatial databases                                 Part 1 – Java Primer

                                   Java in practice
• Types of Java bytecode binaries – .class files
    Applets Client-side web-applications: init(), paint()
    Applications Standalone applications: main()
    Servlets Server-side web-applications: doPost(), doGet()
    JavaBeans In-process components
    Enterprise Java Beans Interprocess, distributed components




     Daniel Wirz c 2004                                                           24
Java access to spatial databases                                         Part 1 – Java Primer

• Organizing code
    Packages Namespace, hierarchical structure (directories), inverse domain
       name system
    .zip files Recursively compressed content of directories, i.e. packages
    .jar files Like .zip files but including a Manifest file.
    .war files Web Archives: Like .jar files but with predefined internal structure
       for server-side web-applications.
    .ear files Enterprise Archives: Like .jar files but with predefined internal
       structure for complex J2EE applications.
    PATH environment variable Must include bin directory of javac, java etc.
    CLASSPATH environment variable Must include startpoint of packages
       (up to, but not including, top-level package-directory). Instead of directories
       it is common practice to include .jar and/or .zip files (not their
       directories!).


     Daniel Wirz c 2004                                                                   25
Java access to spatial databases                                      Part 1 – Java Primer

                                     javadoc
     A source-code level API documentation system;
     /** This is a javadoc comment */

• Comments must immediatly preceed method or class to be documented!

• All non-trivial public methods should be documented

• Return value of these methods should be documented: @returns

• All parameters of these methods should be documented: @param

• Private methods are usually not documented with javadoc unless option
  ’-private’ is used (. . . there’s no need to document ’blackbox-internals’. . . )

     Daniel Wirz c 2004                                                                26
Java access to spatial databases                           Part 2 – Java And Databases

                                   Java And Databases




                                        JDBC 1.x
                                   JDBC 2.x Key features
                                          SQLJ




     Daniel Wirz c 2004                                                            27
Java access to spatial databases                               Part 2 – Java And Databases

                                     JDBC




• JDBC is a standard interface for accessing relational databases (and any other
  tabular data!) from Java.

• The JDBC class library is part of the JDK, package java.sql.

• JDBC is used by client applications, applets, servlets, EJB, and Java stored
  procedures.

     Daniel Wirz c 2004                                                                28
Java access to spatial databases                  Part 2 – Java And Databases

                                   JDBC – Steps




     Daniel Wirz c 2004                                                   29
Java access to spatial databases                            Part 2 – Java And Databases

                                   JDBC – Step 1: Connect




     Daniel Wirz c 2004                                                             30
Java access to spatial databases                    Part 2 – Java And Databases

• Register the driver:
  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
  Old method: Class.forName("oracle.jdbc.driverOracleDriver")

• Connect to the database:

    Connection conn = DriverManager.getConnection(
       "jdbc:oracle:thin:@myhost:1521:orcl", "scott", "tiger");




     Daniel Wirz c 2004                                                     31
Java access to spatial databases                                 Part 2 – Java And Databases

                                   JDBC – Driver
• A Driver. . .
     – . . . is an interpreter that translates vendor-neutral JDBC method calls to
       vendor-specific database commands
     – . . . implements interfaces in java.sql
     – . . . can also provide a vendor’s extensions to the JDBC standard

• Types of Drivers
    Thin Driver 100% pure Java implementation: The only option for Applets
    OCI Client Drivers C and Java, optimized, local installation.
    Server-Side Driver Runs inside the DB, used by Java stored procedures
    JDBC-ODBC Bridge Translates JDBC into ODBC-calls
    Oracle Lite Driver Oracle Lite database connection

     Daniel Wirz c 2004                                                                  32
Java access to spatial databases                       Part 2 – Java And Databases

                                   JDBC – URLs




   JDBC uses a URL (Uniform Resource Locator) to identify the database
connection.

Thin driver jdbc:oracle:thin:@<host>:<port>:<SID>

OCI driver jdbc:oracle:oci8:@<TNSNAMES entry>

     Daniel Wirz c 2004                                                        33
Java access to spatial databases                          Part 2 – Java And Databases

                                   JDBC – Step 2: Query




     Daniel Wirz c 2004                                                           34
Java access to spatial databases                            Part 2 – Java And Databases

• A Statement object sends the SQL statement to the database.

• An active connection is required to create a JDBC statement

• Statement has three methods to execute a SQL statement:
     – executeQuery() for QUERY statements
     – executeUpdate() for INSERT, UPDATE,             DELETE,         or     DDL
       statements
     – execute() for either type of statement




     Daniel Wirz c 2004                                                             35
Java access to spatial databases   Part 2 – Java And Databases




     Daniel Wirz c 2004                                    36
Java access to spatial databases                              Part 2 – Java And Databases

                              JDBC – Step 3: Process the Results




     Daniel Wirz c 2004                                                               37
Java access to spatial databases                               Part 2 – Java And Databases

                                   JDBC – ResultSet
• JDBC returns the results of a query in a ResultSet object.

• A ResultSet maintains a cursor pointing to its current row of data.

• Use next() to step through the result set row by row.

• getString(), getInt(), and so on assign each value to a Java variable.

while (rset.next()) {
  String title = rset.getString("TITLE");
  String year = rset.getString("YEAR");
  ... // Process or display the data
}

     Daniel Wirz c 2004                                                                38
Java access to spatial databases                                 Part 2 – Java And Databases

                                   JDBC – Handling null-Values
    Just like in the (Oracle) Database any Java Complex-Type might have a null
value: Any declared but unassigned object-instance initially is null. So there is
a natural match between Database-Types and Complex Java Types.
But Java primitive types cannot be null!

while (rset.next()) {
  String year = rset.getString("YEAR");
  if (rset.wasNull()) {
    // Handle null value
  }
}




     Daniel Wirz c 2004                                                                  39
Java access to spatial databases                             Part 2 – Java And Databases

                             JDBC – Step 4: Close the connection




     Daniel Wirz c 2004                                                              40
Java access to spatial databases                  Part 2 – Java And Databases

try {
  ...
} catch (Exception thisAndThat) {
...
} finally {
  try {
    con.close();
  } catch (SQLException ex) {
    System.err.println(
  "Oops! SQLException while closing the Connection");
  }
}




     Daniel Wirz c 2004                                                   41
Java access to spatial databases                                     Part 2 – Java And Databases

                                JDBC – Obtain Database Metadata
• Get the DatabaseMetaData object:
  DatabaseMetaData dbmd = conn.getMetaData();

• Use the objects methods to get the metadata:

    String            s1    =      dbmd.getURL();
    String            s2    =      dbmd.getSQLKeywords();
    boolean           b1    =      dbmd.supportsTransactions();
    boolean           b2    =      dbmd.supportsSelectForUpdate();




     Daniel Wirz c 2004                                                                      42
Java access to spatial databases                            Part 2 – Java And Databases

                              JDBC – Obtain ResultSet Metadata
  java.sql.ResultSetMetaData provides following metadata:
Number of columns, Column type, Column name . . .

ResultSetMetaData rsmd = rset.getMetaData();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
  String colname = rsmd.getColumnName(i);
  int coltype = rsmd.getColumnType(i);
  ..
}




     Daniel Wirz c 2004                                                             43
Java access to spatial databases                             Part 2 – Java And Databases

                                   JDBC – Transactions
• The server-side driver does not support autocommit mode.

• With other drivers:
     – New connections are in autocommit mode.
     – Use con.setAutoCommit(false) to turn autocommit off.

• To control transactions when not in autocommit mode:
     – con.commit(): Commit a transaction
     – con.rollback(): Roll back a transaction




     Daniel Wirz c 2004                                                              44
Java access to spatial databases                            Part 2 – Java And Databases

                       JDBC – Prepared and Callable Statements
PreparedStatement Multiple execution of the same precompiled SQL-
  Statement. Accepts ’bind-variables’ (?) as placeholders:

    PreparedStatement pstmt = conn.prepareStatement(
      "select STATUS from ACME_RENTALS where RENTAL_ID = ?");
    pstmt.setInt(1, rentalid);
    pstmt.executeQuery();

CallableStatement Holds parameters for calling stored procedures.




     Daniel Wirz c 2004                                                             45
Java access to spatial databases                           Part 2 – Java And Databases

                                   JDBC 2.x Key features
java.sql JDBC 2.0
    batch updates Group multiple UPDATE, DELETE, or INSERT statements
      into a single ”batch”
    result set enhancements Scrollable and Updatable Result Sets
    custom fetch size Specify the number of rows fetched with each database
      round trip for a query

javax.sql JDBC 2.0 Standard Extension API
    use of JNDI Obtain database connections
    connection pooling Connection pool data sources: pooled connections
      remain open during use by a series of logical connections.
    connection caching Create pooled connections as needed

     Daniel Wirz c 2004                                                            46
Java access to spatial databases                            Part 2 – Java And Databases

                                        SQLJ
• SQLJ is a standard for embedding static SQL statements in Java code.

• The Oracle SQLJ translator translates SQLJ files to Java JDBC code.

• The SQLJ translator checks for:
     – SQL syntax errors
     – Incorrect assumption of table structures
     – Java/SQL type mismatch




     Daniel Wirz c 2004                                                             47
Java access to spatial databases                      Part 3 – Acessing Oracle Spatial data from Java

                           Acessing Oracle Spatial data from Java
    Oracle provides a Java-API dedicated to accessing Oracle Spatial data. It
allows access to and processing of geometry objects. The library’s geometry
interface hierarchy extends the OGC simple feature geometry model by handling
curvilinear in addition to linear geometry objects.


• oracle.sdoapi API Packages
  http://www.geo.unizh.ch/oai/spatialdb/sdoapi.pdf
  http://www.geo.unizh.ch/oai/spatialdb/sdoapi/apidoc/index.html
    oracle.sdoapi.adapter Interface GeometryAdapter
      provides an adapter framework and a number of available geometry adapters,
      through which geometry conversions are performed.
    oracle.sdoapi.geom Point, LineString, Polygon, ...
      manages geometry creation. through the geometry factory interface

     Daniel Wirz c 2004                                                                           48
Java access to spatial databases                    Part 3 – Acessing Oracle Spatial data from Java

      (GeometryFactory).
    oracle.sdoapi.sref Spatial referencing manages coordinate systems creation,
      transformation and persistence.
      Supported types of coordinate systems: geographic, projected, and non-
      earth
    oracle.sdoapi.util GeometryMetaData




     Daniel Wirz c 2004                                                                         49
Java access to spatial databases                       Part 3 – Acessing Oracle Spatial data from Java

                                   Oracle Spatial sample code
• Oracle Spatial sample code
    SampleNewToSDO Creates geometry objects and inserts them into a
      specified Oracle Spatial table.
    SampleSDOtoWKT Reads geometry objects from an Oracle Spatial table
      and converts them into WKT (well-known text) strings.
    SampleSDOtoXML Reads geometry objects from an Oracle Spatial table
      and converts them into XML
    SampleShapefileToSDO Reads geometry objects from a shapefile and inserts
      them into a specified Oracle Spatial table
    SampleWKTtoSDO Reads OGC-WKT strings and converts them into Oracle
      Spatial geometry objects.
    SampleXMLtoGeometry Constructs geometry objects from an XML file.
    SampleShiftAllGeometries Reads geometry objects from the source table,

     Daniel Wirz c 2004                                                                            50
Java access to spatial databases                      Part 3 – Acessing Oracle Spatial data from Java

      shifts them all to new locations, and then writes the results into the target
      table.
    SampleTransforms Reads geometry objects from the source table, transforms
      them to a new spatial reference system, and then writes the results into the
      target table.
    SampleOperators Calls geospatial relationshipservices and operators.




     Daniel Wirz c 2004                                                                           51
Java access to spatial databases                   Part 3 – Acessing Oracle Spatial data from Java

                                   The End
       Douglas Adams, So Long, And Thanks For All The Fish –
    ”God’s final Message to his Creation: We Apologise For The Inconvenience”




     Daniel Wirz c 2004                                                                        52