Entity Loading with HQL?

jason.s · **Joined:** Wed Jan 04, 2006 12:48 pm **Posts:** 8

I am currently using MySQL 4.1 and Hibernate 3.0 to persist some data involving the Spatial types (Geometry object).

My question isn't a specific error but how to attack the problem; I've been going at it different ways but keep running into blocks.

In my Java classes I store the geometry information as strings (WKT format), but I want to take advantage of the functions and indexing that MySQL provides to help reduce the size of my query result sets, so I want to store the information as a MySQL Geometry object.

So I thought to have my Java property be text, and have hibernate translate it with the MySQL functions to convert it from and to text as necessary. To do this I thought to use custom SQL:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping default-lazy="false">
   <class name="MASSValue" table="massvalue">
        <id name="id" column="id" type="long">
            <generator class="identity"/>
        </id>
        <joined-subclass name="MASSGeometry" table="massvalue_geometry">
         <key column="massValueId"/>
         <property name="simpleValue" column="simpleValue" type="text"/>
         <loader query-ref="massgeometry_select" />
         <sql-insert>INSERT INTO massvalue_geometry (simpleValue, massValueId) VALUES (GeomFromText(?), ?)</sql-insert>
         <sql-update>UPDATE massvalue_geometry SET simpleValue=GeomFromText(?) WHERE massValueId=?</sql-update>
         <sql-delete>DELETE FROM massvalue_geometry WHERE massValueId=?</sql-delete>
      </joined-subclass>
   </class>
   <sql-query name="massgeometry_select" cacheable="false" callable="false">
      <return alias="g" class="MASSGeometry" lock-mode="upgrade"/>
      <return-property name="simpleValue" column="simpleValue"/>
      <return-property name="massValueId" column="massValueId"/>
      SELECT AsText(simpleValue) AS {g.simpleValue}, massValueId AS {g.massValueId} FROM massvalue_geometry WHERE massValueId=? FOR UPDATE
   </sql-query>
</hibernate-mapping>

The inserts work wonderfully, but everything else doesn't. It fails when it loads the object from the database and tries to instantiate a MASSGeometry because the object isn't being passed through the AsText() function, it's coming straight across as a binary object and the setter chokes because it is expecting a string. After some hunting and searching on the forums I discovered that the named query is never getting called because I am using HQL for my query.

In the docs it says I can use my own HQL for entity loading, but I cannot find an example of that anywhere.

If I use a named HQL query instead of an SQL query for the entity loading, will I be able to use the named HQL query to load the entities during HQL queries (ie s.createQuery(query).list();)? I don't want to have to rewrite all my HQL queries into SQL queries, they're generated dynamically.

Or is there an easier way to approach this (getting Geometry objects from MySQL columns into a Java object)? I looked at UserType but it doesn't seem to match what I need (what would I declare the Type as?).

Sorry for the length, I hope I'm being clear enough. Thanks in advance.

david · **Posted:** Tue May 09, 2006 8:41 pm

If I understand your problem correctly then a UserType should be what you are after. I can be created so that it stores as the native geo type and the Java side is a String in your WKT format. I suggest you have another look at it.

jason.s · **Joined:** Wed Jan 04, 2006 12:48 pm **Posts:** 8

david wrote:

If I understand your problem correctly then a UserType should be what you are after. I can be created so that it stores as the native geo type and the Java side is a String in your WKT format. I suggest you have another look at it.

Thanks for your reply!

Alright, I will give it another go tomorrow.. The intial difficulty that came up however as I was browsing the DoubleStringType.java test code:

Quote:

private static final int[] TYPES = { Types.VARCHAR, Types.VARCHAR };

The Types.whatever would be the type on the MySQL side though wouldn't it (in my case Geometry)? Use Types.OTHER? Or use Types.VARCHAR and somehow have the proper conversion methods called at the MySQL level?

I haven't looked very deep into UserTypes yet though, I will do so. I'm still new to Hibernate :)

I did find that the geometries are stored in a WKB (binary) format in MySQL, so I wonder if I could treat them as a BLOB and then translate the binary into whatever format I want (WKT, or a JTS Geometry) within the UserType?

david · **Posted:** Tue May 09, 2006 8:58 pm

You will be using JDBC level API for the database access level so you should be using the MySQL JDBC driver types for the object you are accessing. Then you can convert it to what ever Java type is possible for the POJO side of the user type.

jason.s · **Joined:** Wed Jan 04, 2006 12:48 pm **Posts:** 8

david wrote:

You will be using JDBC level API for the database access level so you should be using the MySQL JDBC driver types for the object you are accessing. Then you can convert it to what ever Java type is possible for the POJO side of the user type.

Heh, whatever my knowledge level is you just went one step past it :) I checked the docs for Connector/J and they don't mention a Geometry type, but a quick peek at the code and it seems they treat a Geometry the same as a BLOB. So I guess I would read in the BLOB into the UserType class and the convert it with the WKB reader in JTS. Or even use the JTS Geometry object in my POJO, there's no business reason to keep it a string.

I'll take a look at the demo code as well as the Hibernate books in Safari with regards to UserTypes and try and foil it out.

Thanks again.

jason.s · **Joined:** Wed Jan 04, 2006 12:48 pm **Posts:** 8

Just an update to help out anyone else that may need it...

I ended up doing it without resorting to a UserType. Though my app is now pretty tightly coupled to the MySQL database that's an acceptable compromise at this point.

To do this I changed the hbm file to treat the Geometry column as a BLOB. Then in my POJO I use the java.sql.Blob type.

The blob in MySQL is actually the SRID (ID for the geometry) integer followed by the Geometry in WKB (Well Known Binary) format.

To read in the geometry, I converted the Blob to a byte array and stripped off the first four bytes (the SRID integer which I'm not using) and then used the WKBReader in JTS 1.7 to read the binary into a JST Geometry. Then I can do whatever I need with it (convert it to WKT, compare it to another geometry, etc etc).

Getting it in back out to the database was a bit more difficult. In my application users send geometries as WKT. So I convert the WKT to a JTS Geometry and then write it out to a byte array with the WKBWriter. After putting the SRID back on it didn't work...

After some trial and (mostly) error, I realized the data in MySQL is stored in the opposite endian as the WKBWriter from JTS defaults to. Fortunately the WKBWriter has a parameter in the constructor which allows for the switching of the endian used. So switch that and it goes back out into MySQL as a Blob fine.

So now I can use the MBR functions in HQL on the Geometry columns in MySQL and have the proper indexes on to help with the queries.

mindgutter · **Joined:** Fri Mar 18, 2005 3:23 pm **Posts:** 7

Jason,
Any way you can send me the code for the blog manipulation? As it sounds as a time consuming excercise.

I solved the same problem by using an Interceptor, but your solution sounds more elegant.

Code:

package bah.metadata.hibernate;

import java.util.HashMap;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class MetadataInterceptor extends EmptyInterceptor {
   private static final long serialVersionUID = -2149963919888842054L;
   private Log log = LogFactory.getLog(MetadataInterceptor.class);

   /*
    * (non-Javadoc)
    * 
    * @see org.hibernate.Interceptor#onPrepareStatement(java.lang.String)
    *      Handles MySQL GIS TYPES if new columns are added add them to the
    *      GIS_COLUMNS array
    */

   public static Map<String, String> storedSql = new HashMap<String, String>();

   public static String[] GIS_COLUMNS = { "location", "coverage" };

   public static String[] EXCEPTION_FLAGS = { "Within", "Contains" };

   @Override
   public String onPrepareStatement(String sql) {
      String newSql = sql;
      

      String foundSql = storedSql.get(sql);
      if (foundSql != null) {
         newSql = foundSql;
      } else {
         int tokenCounter = 0;
         //ignore clauses
         Map<String,String> clauses = new HashMap<String,String>();
         for (String keyWord: EXCEPTION_FLAGS){
            if (StringUtils.contains(newSql,keyWord)){
               String token = StringUtils.substringBetween(newSql, keyWord + "(", ")");
               token = keyWord + "(" + token +")";
               String key = "token" + tokenCounter++;
               clauses.put(key, token);
               newSql = StringUtils.replace(newSql, token, key);
            }
         }
         
         if (StringUtils.contains(sql, "select") || StringUtils.contains(sql, "Select") || StringUtils.contains(sql, "SELECT")) {
            for (String field : GIS_COLUMNS) {
               if (StringUtils.contains(newSql, field)) {
                  String tableName = StringUtils.substringAfterLast(
                        StringUtils.substringBeforeLast(newSql, "."
                              + field), " ");
                  String fullField = tableName + "." + field;
                  newSql = StringUtils.replace(newSql, fullField,
                        "AsText(" + fullField + ")");
               }
            }
         } else if (StringUtils.contains(sql, "insert") || StringUtils.contains(sql, "Insert") || StringUtils.contains(sql, "INSERT")) {
            for (String field : GIS_COLUMNS) {
               if (StringUtils.contains(newSql, field)) {
                  int pos = StringUtils.countMatches(StringUtils
                        .substringBefore(newSql, field), ",");
                  String[] args = StringUtils.split(newSql, ",");
                  args[2 * pos] = "GeomFromText(?)";
                  if (2 * pos == args.length - 1) {
                     args[2 * pos] += ")";
                  }
                  newSql = StringUtils.join(args, ",");
               }
            }
         } else if (StringUtils.contains(sql, "update") || StringUtils.contains(sql, "Update") || StringUtils.contains(sql, "UPDATE")) {
            for (String field : GIS_COLUMNS) {
               if (StringUtils.contains(newSql, field)) {
                  newSql = StringUtils.replace(newSql, field + "=?",
                        field + " = GeomFromText(?)");
               }
            }
         }
         
         for (int i = 0; i < tokenCounter; i++){
            String key = "token" + i;
            newSql = StringUtils.replace(newSql, key, clauses.get(key));
         }
         
         storedSql.put(sql, newSql);
         log.debug(newSql);
      }
      
      return newSql;
   }
}

jason.s · **Joined:** Wed Jan 04, 2006 12:48 pm **Posts:** 8

mindgutter wrote:

Jason,
Any way you can send me the code for the blog manipulation? As it sounds as a time consuming excercise.

Unfortunately I can't give out any code at this point.. though really my previous post sums it up.

Basically when I want to convert a wkt string to a blob to store in the database, I:

1. Create a new Geometry object for that string
2. Create a new byte array from that using the WKBWriter object in JTS
3. Create another byte array with 4 bytes padding at the front for the SRID (which I leave at 0).
4. Create a new javax.sql.rowset.serial.SerialBlob from that byte array.

That's it, when hibernate saves the object with the Blob property it will be written properly to the database so that MySQL can use it.

max · **Posted:** Mon Oct 16, 2006 6:07 am

I still don't get why you are trying to manipulate the sql instead of using custom types and/or custom dialect functions ?!

jason.s · **Joined:** Wed Jan 04, 2006 12:48 pm **Posts:** 8

max wrote:

I still don't get why you are trying to manipulate the sql instead of using custom types and/or custom dialect functions ?!

I didn't/don't understand enough about custom user types to accomplish the goal, and couldn't find enough documentation to bridge the gap in my understanding. I just recall at the time running into a roadblock every way I tried to approach it and this was the only solution I could find that actually accomplished my goal of having the data communicate back and forth as the native binary.

At some point I would like to revisit this so I can make it completely database agnostic (as I realize this couples my code tightly with MySQL).

max · **Posted:** Mon Oct 16, 2006 12:36 pm

and has some expensive string manipulation going too

jason.s · **Joined:** Wed Jan 04, 2006 12:48 pm **Posts:** 8

max wrote:

and has some expensive string manipulation going too

True, though I don't really do much with the strings beyond converting them to binary and storing them; I wanted to offload the processing of geometry objects from the app server to the database (ie do a query to find all objects within a geometry).

If you know of a good example of a custom data type being coded it would be appreciated. I would need a custom dialect as well wouldn't I since I'm using a binary data type that isn't defined in Java as well as functions that aren't defined in the MySQL Dialects?

max · **Posted:** Mon Oct 16, 2006 2:42 pm

there are examples in all the docs and unittests for how to write custom usertypes.

jason.s · **Joined:** Wed Jan 04, 2006 12:48 pm **Posts:** 8

max wrote:

there are examples in all the docs and unittests for how to write custom usertypes.

Alright I will have to take another look when the time comes.. I did go through the docs and such last time and just remember running into a lot of points where I didn't know where to go last time I tried it, though I didn't look into the unit tests.