Hibernate DDL Export and SQL Length

Warning! Long story ahead… One of the great features of Hibernate is what it calls “…roundtrip engineering” where you can begin with one artifact (a POJO, a database table, a Hibernate mapping file) and produce all the others from it. You can start with hibernate mapping files (.hbm) that describe your objects and then generate basic POJO’s and your database schema (called DDM in Hibernate) using the Code Generation tools. You can also use Middlegen to generate your Hibernate Mapping Files directly from your database schema. Finally, you can produce mapping files from your compiled Java classes or from Java source with XDoclet markup (using HibernateDocletTask) which you could then use to produce DDM, which is what I spent time doing yesterday using Ant.

This is a new project, so I only had a couple Java classes that needed the Hibernate XDoclet tags added. I noticed yesterday that Cedric Beust had released a JavaDoc Tag Plug-in for Eclipse that ships with a definition file for Hibernate, which made it easy to get started with the Hibernate tags (download it, unzip to the Eclipse ‘plugins’ folder and restart Eclipse). A super simple example class looks like this:

package com.mycompany;
import java.io.Serializable;
/**
* @hibernate.class table="dog"
*/
public class Dog extends BaseBean implements Serializable {
  private String name;
  private String type;
  public Dog() { }
  /**
   * @hibernate.property name="name" type="string"
   * @hibernate.column name="name" length="255" not-null="true"
   * @return
   */
  public String getName() {
    return name;
  }
  /**
   * @hibernate.property name="type" type="string"
   * @hibernate.column name="type" length="255" not-null="true"
   * @return
   */
  public String getType() {
    return type;
  }
  public void setName(String name) {
    name = name;
  }
  public void setType(String type) {
    type = type;
  }
}

After writing the class, the rest of your work is in Ant, which in turn uses XDoclet and the tools provided by Hibernate. You’ll need to download and extract XDoclet to your system; I added a property to my Ant build file that specifies the location of XDoclet:
<property name="xdoclet.home" value="C:\xdoclet-1.2.1"/>
I read other people who recommended adding XDoclet to the /lib/ directory of your Ant installation. After doing either of the above, you’ll need to define the HibernateDocletTask in Ant:

<taskdef name="hibernatedoclet"
  classname="xdoclet.modules.hibernate.HibernateDocletTask">
  <classpath refid="xdoclet.classpath"/>
</taskdef>

and then you can use it inside a <target>:

<hibernatedoclet
  destdir="WEB-INF/classes"
  excludedtags="@version,@author,@todo"
  force="true"
  mergedir="WEB-INF/classes"
  verbose="false">
  <fileset dir="src">
    <include name="com/mycompany/*.java"/>
  </fileset>
  <hibernate version="2.0"/>
</hibernatedoclet>

In the above example I have the Hibernate mapping files being generated to /WEB-INF/classes from the Java source filesthat live in the /src/com/mycompany directory. However, this leaves you having to manually update the master Hibernate configuration file (hibernate.cfg.xml) with the references to the generated mapping files. The Hibernate site shows how you can generate the configuration file using BeanShell, but it’s easier than that. The latest version of XDoclet includes a task (HibernateCfgSubTask) that does it for you. Simple add this block of code:

<hibernatecfg
  dataSource="java:comp/env/jdbc/pets"
  showSql="false"
  dialect="net.sf.hibernate.dialect.SQLServerDialect"
  destDir="WEB-INF/classes" />

right after the <hibernate version="2.0" /> in the above example. In this example I’m using JNDI to look up a datasource called “pets”, I’m using the SQL Server Hibernate dialect, and I want the task to look in WEB-INF/classes for the Hibernate mapping files.

Wait! We’re not done yet! Because we now have Hibernate mapping files, we can generate a SQL script that will create the appropriate tables in the database. Again, the task needs to be defined:

<taskdef name="schemaexport" classname="net.sf.hibernate.tool.hbm2ddl.SchemaExportTask">
  <classpath refid="compile.classpath"/>
</taskdef>

and then we can run the task:

<schemaexport
  properties="src/hibernate.properties"
  quiet="yes" text="true" drop="false" delimiter=";"
  output="WEB-INF/classes/installation.sql">
  <fileset dir="WEB-INF/classes" includes="**/*.hbm.xml"/>
</schemaexport>

The task requires that you specify a Hibernate dialect using a properties file (why not add dialect as an attribute like the Hibernatecfg task?) and then can either write the generated SQL to a file or can run the generated SQL against a database.

Anyway, all of this does lead up to a point. One of the puzzling things that I ran into was that I ended up specifying the type of the SQL column in my POJO using the @hibernate.column tag like this:

@hibernate.column name="label" length="255" sql-type="nvarchar"

If you specify a sql-type then the length attribute is not passed through to the generated SQL, which leaves you with SQL that looks like this:

create table dog (
  ...
  name VARCHAR,
  type VARCHAR,
  ...
);

I’m not sure if this is by design or by accident, but Hibernate is open source, so it’s easy to find a work around. The offending code is in the net.sf.hibernate.mapping.Column class and the net.sf.hibernate.mapping.Table class. The Table class is responsible for looping over every column defined in the mapping file and then calls the getSqlType() method on the Column. The Column instance is then responsible for either a) guessing at the SQL type (if type is not defined) or b) returning the defined SQL type. In (a), Hibernate actually does return a length; it guesses that because the property is of type string, that the column should be of SQL type ‘varchar’ and that the length should be 255 characters. But in (b), instead of returning the type and then the length, it only returns type, which in SQL Server means that you’ll end up with all your varchar columns being 1 character in length. The workaround is to either go route (a) and let Hibernate guess what the column type and length should be or (b) you can specify length as part of the type. For instance:

@hibernate.column name="type" sql-type="nvarchar (50)"

which creates a nvarchar column of 50 characters. I know this syntax will work on MySQL as well as SQL Server, I’m not sure about the other database platforms that Hibernate supports. But the bigger question: bug or feature?

9 thoughts on “Hibernate DDL Export and SQL Length”

  1. I’d say it’s a feature. If you want to specify the type, you’ll probably want to be exact.

    I’ve used the sql-type mostly to omit the length: just “varchar” will convince PostgreSQL to create column that can grow as big as it needs to be.

  2. Pingback: Electronic Thumb
  3. Perhaps a silly question, but did you set the database dialect for hibernate for sql server? I think it’s one of those property file settings…It might explain why you’re getting such generic sql stuff, if you didn’t set it.

  4. hi Will,

    Yeah, read it again, you’ll notice that I mention the properties file:

    “… task requires that you specify a Hibernate dialect using a properties file.

    AJ

  5. Hi Aron!

    Thanks for the tip about how to autoupdate hibernate.cfg.xml.

    Now to my Ant+Ecplise problem.

    1) XDoclet (HibernateDoclet)
    2) SchemaExport
    3) Execute DDL

    My problem is that the mapping files aren’t refreshed beteen 2 & 3. Therefore I must always execute these tasks one by one (with Eclipse refresh enabled on HibernateDoclet).

    I would like to simply execute the CREATEDB task (depends on SCHEMAEXPORT that depends on XDOCLET) but I cant get the mapping files to be refreshed when doing so.

    Am I dumb?

  6. 聚氨酯 手机图片/手机铃声下载 风机 手机图片铃声下载 手机铃声下载-图片下载-动画下载 变压器 焚烧炉 动画下载/手机铃声下载 钢格板 钢格板 活性炭 苏州网站建设 薄膜蒸发器 投影机 过滤器|液位计|金属软管|鹤管|快速接头|视镜 肝癌-肝硬化 中药粉碎机 饲料机械 帆布 孔隙水压力计 钢筋计 手机铃声下载 手机铃声下载 土工格栅-钢塑土工格栅 透水管 手机图片下载 飞机票 设计 动画图片下载 圆珠笔 手机铃声下载 上海笔记本电脑维修 灌装机 手机特效铃声下载 打包机、缠绕机、真空包装机 婚庆 电磁阀 机票 全自动麻将桌 涂料 复印机 测斜管 喷嘴-加湿器 手机图片下载 手机动画下载 劈裂注浆管 塑料盲沟 手机铃声下载 手机铃声下载 手机铃声下载 电炉 手机铃声下载 喷涂机-高压喷涂机 手机图片铃声下载 手机铃声下载/mp3/mmf 手机图片/图片下载 发电机 手机铃声下载/彩铃 纱线、雪尼尔 手机特效铃声下载 条码打印机 手机铃声/图片下载 动画图片下载 手机和弦铃声/和弦铃声下载 和弦铃声下载 成功 手机和弦铃声下载 上海塑料机械 自动门 散文 生物 数据采集器|视频信号产生器 收藏 散热器-铝合金 焚烧炉 手机铃声下载/手机图片下载 手机铃声下载/手机彩铃 手机铃声下载/和弦/特效 彩信手机图片下载 特效铃声下载 电炉 水处理 环保设备 薄膜蒸发器、溶剂回收蒸馏馏 心理 五金工具 干燥设备 手机和弦铃声下载 彩信/手机铃声铃声 手机特效铃声下载 手机铃声图片下载 手机铃声下载/图片下载 手机铃声下载/动画下载 对讲机 策划 手机图片/手机铃声下载 风机

  7. Hi, I need to create database programmatically , pls suggest me some tweaks for that in hibernate(without any cfg files)!!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>