Overview

Skill Level: Intermediate

Assumes familiarity with DB2 and Java application development using Maven

Hibernate is an open source Java persistence framework for relational databases with a database-agnostic interface. Hibernate Spatial is a component which adds the capabilities to work with spatial data.

Ingredients


With this recipe you will populate DB2 tables with spatial data and run a sample Java program that queries sample data using the Hibernate Spatial API.

Step-by-step

  1. Setup DB2 and sample data

    1. Note: this recipe uses Hibernate on Windows connecting to a local DB2 database. The process is similar for Linux and MacOS environments. Change the database connection values in the examples to your database name and userid.
    2. Download and unzip the DB2 sample data and programs to a convenient directory. Change to the data directory.
    3. Open a DB2 command window where you can execute SQL statements. If you are using a remote database, make sure it is cataloged locally. You can use the scripts catalog-luw.sql or catalog-zos.sql as appropriate and make changes for the remote server location. (Use catalog-luw.sql for DashDB)

      You can execute the script with a command like:
      db2 -tvf catalog-luw.sql

    4. Import sample spatial for banks, customers and San Jose streets.
      – Modify the scripts import-luw.sql or import-zos.sql as appropriate to change the userid in the stored procedure. (The scripts contain descriptions of each of the steps and any modifications needed.
      – Execute the script with a command like:
      db2 -tvf import-luw.sql
      – Check that there were no processing errors
    5. Verify the imported data with the SQL statement (change “osuser” to your connection userid):
      db2 select name, street, varchar(db2gse.st_astext(geom),32) from osuser.banks
      which should return the name, street address and location of the Meridian and San Carlos branches
  2. Setup development environment

    • Hibernate and this tutorial are based on the Java language and require a Java 8 or a later SDK installed.
    • Due to the large number of dependencies, the Maven build tool is used to download pre-requisite libraries and to build the recipe source code. Download Maven from the link listed above and install in your environment.
    • Change to the Hibernate directory of the downloaded DB2 sample data and programs. Build the recipe with the command:
      mvn install
  3. Run sample program

    • From the same directory where the recipe was built, run the sample with a command like:
      mvn exec:java -Dexec.mainClass=”recipe.HSDB2″ -Dexec.args=”<operation>”
      where <operation> is one of:
      – listnear –
      lists customers within 2000 meters of the Meridian bank branch.
      – listwindow –
      lists customers withing a specified geographic window
      – listsome –
      selects all customers in the table and lists the first 10 customers

    • Try changing the queries in HSDB2.java¬†to use a different distance or query window. Make sure to run mvn install each time before executing the program.

Join The Discussion