ioannis cherouvim

When generating the database schema using the hbm2ddl tools, foreign keys are being created for every relation. You can enhance the schema by using the foreign-key attribute to specify your own name for a particular foreign key.

But, what happens when you don’t want a foreign key generated?

There is an undocumented behavior of the foreign-key attribute, and that is to specify foreign-key=”none”. hbm2ddl will not create an FK for a relation which has such an attribute. The hibernate documentation and the two bibles Hibernate in Action and Java Persistence with Hibernate state absolutely nothing about this feature. I found it after hardcore googling in the following hibernate changelog:

Changes in version 2.1.9 (xx.x.xxxx)
------------------------------------
* foreign-key="none" can be used to disable generation of a foreign key.

Later on, I found another blog mentioning this behavior: http://blog.xebia.com/2007/02/05/let-hibernate-connect-your-world/

The most logical question now is “why don’t you want an FK?”. That depends on the system you are building. Sometimes you might have a table in your application which will be CRUDed from another system, which you do not control. You may want to be able to keep references (ids) to entities which may be deleted. foreign-key=”none” together with not-found=”ignore” can solve these kind of problems.

I’m fond of optimization. When I code or design my database schema I try to avoid waisting CPU cycles or storage space (at least without a good reason). So when my domain class has the following field:

private boolean active; // determines whether this Person is active

I will let hibernate and the MySQL5InnoDBDialect choose what is most appropriate:

<property name="active" not-null="true" />

In that case it will generate a BIT:

...
active bit not null,
...

The problem

So far so good…
…until you read the blog post called “Why you should not use BIT columns in MySQL” by Xaprb.
Another serious deficiency is the fact that a database dump will not export bit data as “0″ or “1″. Depending on the tool used to dump and the MySQL server version you may find one of the following:

INSERT INTO `person` VALUES (1,"foo","\\0");
INSERT INTO `person` VALUES (2,"bar","☺");

or

INSERT INTO `person` VALUES (1,"foo"," ");
INSERT INTO `person` VALUES (2,"bar"," ");

The third field is of datatype BIT. Row number 1 is false and row number 2 is true. The problem with that is that some MySQL client tools cannot import such things. It gives you an ERROR 1064 (42000) at line 23: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1

Solution #1

Hand edit the sql script and change all false bits to 0 and all true bits to 1, the script can be imported like a charm.

Solution #2

Extend MySQL5InnoDBDialect and make all BITs rendered as TinyInt(1). The code is very simple:

package com.foo.hibernate;

import java.sql.Types;
import org.hibernate.dialect.MySQL5InnoDBDialect;

public class MySQL5InnoDBDialectBitFixed extends MySQL5InnoDBDialect {

  public MySQL5InnoDBDialectBitFixed() {
    super();
    registerColumnType(Types.BIT, "tinyint(1)");
  }

}

Now when using the MySQL5InnoDBDialectBitFixed dialect, hbm2ddl will generate:

...
active tinyint(1) not null,
...

Until we get better 5.x MySQL versions, with better BIT support, this plan should do the job nicely.

Good luck

Hibernate offers org.hibernate.dialect.SQLServerDialect as the dialect for SQL Server. When generating the database schema, using hbm2ddl, the string type columns do not support native characters. So the following mapping:

<property name="title" length="128" />

will produce the following SQL:

...
title varchar(128) null,
...

By extending the org.hibernate.dialect.SQLServerDialect we can achieve the generation of NCHAR, NVARCHAR, and NTEXT columns instead of CHAR, VARCHAR and TEXT.

package com.foo.hibernate;

import java.sql.Types;
import org.hibernate.dialect.SQLServerDialect;

public class SQLServerNativeDialect extends SQLServerDialect{

  public SQLServerNativeDialect() {
    super();
    registerColumnType(Types.CHAR, "nchar(1)");
    registerColumnType(Types.VARCHAR, "nvarchar($l)");
    registerColumnType(Types.LONGVARCHAR, "nvarchar($l)");
    registerColumnType(Types.CLOB, "ntext");
  }

}

All we need to do now is plug this dialect in our hibernate configuration:

<property name="hibernate.dialect">
  com.foo.hibernate.SQLServerNativeDialect
</property>

Related hibernate forums thread: http://forum.hibernate.org/viewtopic.php?t=972518
Related API method: http://www.hibernate.org/hib_docs/v3/api/org/hibernate/dialect/Dialect.html

You are building a webapp. You want database connectivity. You want pooling (because its an expensive resource). You start building your own database connection pool. STOP!!!

Who told you that you can do it well? Why did you hack your own connection pool implementation, which is seriously broken, spawns thousands of threads and turns the server into a miserable piece of shit that needs restart every 24h?

Wrong choice my friend. Next time do us all a favour and use one of the following:

• DBCP
• c3p0
• proxool

And don’t forget: The standard idiom for releasing a connection is to close (return) the connection in a finally block.

Connection con = getPooledConnectionFromSomewhere();
try {
  // do stuff with connection
} catch (SQLException e) {
  // handle problems
} finally {
  con.close();
}

Of course, closing the connection can throw an SQLException, but it’s up to you on how you will handle it.

Good luck

You are building a webapp and you want database connection pooling. Your container can help you manage this javax.sql.DataSource by configuring it and exposing it through the JNDI tree.

Containers usually come with Jakarta Commons DBCP out of the box. In order to use it edit the context.xml file of your webapp and set your datasource there.

<?xml version="1.0" encoding="UTF-8"?>
 <Context path="/foo-app">

 <!-- TOMCAT 5.5.xx DESCRIPTOR -->
 <Resource name="foo"
  auth="Container"
  type="javax.sql.DataSource"
  maxActive="1"
  maxIdle="1"
  maxWait="3000"
  username="user"
  password="pass"
  driverClassName="net.sourceforge.jtds.jdbc.Driver"
  url="jdbc:jtds:sqlserver://localhost:1433;DatabaseName=foobar;charset=utf8"
 />

 <!-- TOMCAT 5.0.28 DESCRIPTOR -->
 <!--
  <Resource name="foo" type="javax.sql.DataSource"/>
  <ResourceParams name="foo">
   <parameter><name>maxActive</name><value>1</value></parameter>
   <parameter><name>maxIdle</name><value>1</value></parameter>
   <parameter><name>maxWait</name><value>3000</value></parameter>
   <parameter><name>username</name><value>user</value></parameter>
   <parameter><name>password</name><value>pass</value></parameter>
   <parameter><name>driverClassName</name><value>net.sourceforge.jtds.jdbc.Driver</value></parameter>
   <parameter><name>url</name><value>jdbc:jtds:sqlserver://localhost:1433;DatabaseName=foobar;charset=utf8</value></parameter>
  </ResourceParams>
  -->

</Context>

Tomcat 5.0.xx and 5.5.xx uses different xml syntax for most of it’s configuration. Here I’m presenting both with 5.0.xx’s block commented out.

So, you’ve got your app called “foo-app”. Next time you’ll deploy it, tomcat will copy context.xml to ${catalina.home}/conf/localhost/foo-app.xml where from it will be reading the configuration on each context or container initialization. Your Datasource is called “foo”.
In your java code now, what you need to get a reference to “foo” is:

Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/foo");

And then something along the lines:

Connection con = ds.getConnection();

Happy coding.