Managing Unmapped Tables with Hibernate

Written by RyanStewart

February 3rd, 2010 at 11:30 pm

Posted in Uncategorized

Tagged with

With 6 comments

There’s an underused feature of Hibernate that I’ve been using recently called “auxiliary database objects“. With these guys, you can finally let Hibernate manage all of your schema creation and deletion. No more using Hibernate to generate mapped tables and then coming behind with another tool to finish the job.

A good example of where this is useful is in the fairly common case of using Quartz for scheduling with a JDBC job store. Quartz provides a SQL script for generating its tables. Before auxiliary database objects, the most common way to get the Quartz tables into the database was to include a separate *.sql file in your build and run it using Ant’s sql task or some other SQL executor. Setting it up this way means there are two steps to either creating or dropping your schema: the Hibernate schema export and the Ant task. That ties this mechanism specifically to your build, eliminating a very useful feature of Hibernate.

With auxiliary database objects, you can include the Quartz create and drop scripts directly in your Hibernate mappings and have Hibernate run those scripts along with its normal schema export. Then there’s only one step to creating and dropping your schema, and more importantly, Hibernate is completely in control of the schema. This gives you the immense benefit of being able to set the SessionFactory’s “hbm2ddl.auto” property to “create” and run an in-memory database–like the awesome H2 database–for development and unit testing, and the schema will be generated for you on startup. This is what you lose if you have scripts outside of Hibernate.

Auxiliary database objects have actually been around for a few years, but it only made it into a general availability release a year and a half ago with version 3.2.6.GA. Below are a couple of simple examples on how to use them.

Besides Quartz tables, I’ve used auxiliary database objects to make a fake “dual” table. It’s common to use the “select * from dual” query on Oracle databases as a validation query for your connection pool. When you run a different database for development, like H2 or MySQL, you’ll have to manually add a dual table so that your validation queries won’t fail. Here are the two ways that you can do that using Hibernate’s auxiliary database objects:

First, you can embed the SQL directly in the mapping file:

<hibernate-mapping>
    <database-object>
        <create>
            create table dual (x int);
        </create>
        <drop>
            drop table dual;
        </drop>
        <dialect-scope name="org.hibernate.dialect..." />
    </database-object>
</hibernate-mapping>

Note that you can have zero to many of the dialect-scope elements, indicating what database platforms this database object applies to. Listing zero dialects means apply to all databases.

The second way to do it is to implement the AuxiliaryDatabaseObject interface. To make it simple, use the AbstractAuxiliaryDatabaseObject subclass:

public class SampleObject extends AbstractAuxiliaryDatabaseObject {
    public String sqlCreateString(...) {
        return "create table dual (x int)";
    }

    public String sqlDropString(...) {
        return "drop table dual";
    }
}

Then you have to specify your class in your mapping file, like so:

<database-object>
    <definition class="rds.hibernate.AuxDBObjectTest" />
    <dialect-scope name="org.hibernate.dialect..." />
</database-object>

The examples should be pretty self explanatory. Create statements are run after Hibernate’s own generated create statements, and drop statements are run after Hibernate’s generated drop statements. I’ll make one additional recommendation: while it’s technically possible to put multiple SQL statements separated by semicolons in a single create or drop block, it’s best to only put one. There are two reasons for this. First, multiple statements in a single block won’t work with Oracle, so if any of your target environments are Oracle, that’s right out. The second reason is that Hibernate runs each create and/or drop using one JDBC Statement, so if you pack 100 create statements into one create block, and one of them fails, then none of the other 99 will take affect, either. If, on the other hand, you use one statement per block, you guarantee that all statements will run, and only the ones that fail will not take effect.

That’s all! It’s very handy to have all your extra SQL managed by Hibernate. All you have to do is point Hibernate at a database, tell it to create your schema, and all your tables, views, stored procedures, etc. will appear.

Share

Related Posts

Comments

6 Responses to “Managing Unmapped Tables with Hibernate”

  1. Eric Millinder on February 18th, 2010 10:48 pm

    These are also handy when your classes are mapped to views.

  2. download call of duty 5 on September 10th, 2014 8:39 am

    Also, if you could make your own map pack consisting of 4 maps from any of the titles,
    what would they be. It’s always a great thing to talk about how you not only met, but exceeded the expectations of your employer.
    If you limit yourself to performing the same tasks over and over again, you’re bound to become complacent.

  3. league of angels hack on September 11th, 2014 7:55 am

    249 BA, 15 HR, 63 RBI, 53 Runs, 0 SB) Injuries (and perhaps
    other things- he did come out of the Texas organization) have
    robbed him of all value. Malamud writes,
    “In the locker room Pop asked Roy to explain why he thought the cover had come off the ball. Abortion is the legalization of killing a child while that child is in the early developmental stages, period.

    My website: league of angels
    hack

  4. dead trigger 2 cheats on September 16th, 2014 6:48 pm

    Two are right next to the door, and one is on a patrol outside.
    Star Wars: The Force Unleashed is the latest offering in the Star
    Wars video game saga. With initial grumblings about Dante’s
    appearance aside, Ninja Theory seems to provide an interesting
    alternative universe to the Capcom series.

    my web blog :: dead trigger 2 cheats

  5. SEO on September 18th, 2014 1:07 am

    Normally I do not read post on blogs, but I wish to say that this write-up very forced me to check out and do so!
    Your writing style has been amazed me. Thank you, very nice article.

  6. how do annuities work on September 19th, 2014 3:58 am

    I don’t even know how I ended up here, but I thought this post was good.

    I do not know who you are but certainly you are going to a famous blogger if you are not already ;) Cheers!

Leave a Reply