Shashi Velur’s blog

I like using surrogate keys for most of the database designs for the following reasons:

1. Simplifies design and may improve efficiency
2. Maintainability: Change to the meaning of the natural key could change the table structures when using natural keys
3. Natural key values may be recycled

Here is a partial list of issues I have encountered when using Hibernate with composite keys on a legacy database:

1. The documentation (or lack there-of) for Composite Keys usage poses big problems. But, the source code really helps .
2. Inserts/Updates are slightly inefficient when compared to surrogate keys if there is no versioning strategy implemented.
3. Cannot have relationships (with CRUD operations) mapped in hibernate for each of the keys in the composite primary key. A read-only collection mapping is possible by mapping the relationship with the same column (that is part of the Composite Key) and making insert=”false” and update=”false”.
4. It is impossible to determine if the object to be saved is a new one or an existing one when using “assigned” composite keys. Since the keys are “assigned”, the primary key is not null and so it is hard to determine if this is a new object without making another call in a new transaction to the DB. This becomes very cumbersome.
5. The Hibernate reference documentation uses various phrases like “discourage it for serious applications” for certain types of Composite-id mappings (embedded composite identifier). There are several issues with using key-many-to-one mapping in the Composite-id elements, etc.
6. In older versions of Hibernate (before 3.3.x), you can’t use an IdentifierGenerator to generate composite keys. The suggested approach was to use UserType or CompsiteUserType. A lot of people choose to use “assigned” strategy to avoid the complications with custom types. I think this is resolved in the latest Hibernate version. Again, the documentation here is almost non-existent. In order to make this work in my code, I had debug through the Hibernate source code and also use the test case code attached in this Hibernate CR.

Technorati Tags: Composite keys, Composite-id, Hibernate

I am a big fan of the Hibernate’s direct field access functionality. I am not sure why ”property” access is the default instead of “field”. Direct field access allows me to control the number of getters/setters that are actually needed from the DAO Client’s perspective.

I think using property access helps enforce better encapsulation of the data in the mapped/persistent classes. I have seen that most developers just use public getters and setters for all of the properties in the mapped class.

This creates problems for collections, especially. I seldom expose getters and setters for collections. I always use a combination of “addXXX(Obj)”, “removeXXX(Obj)” and “clearXXX()” (depending upon the need) instead of “get/setXXX”. This is for efficiency (as Hibernate uses a custom collection class) and better encapsulation.

The only situations where I do not use the direct field access are:

• If the DB schema data type does not directly map to the mapped attribute in Java where a translation is required. This is mostly true for Legacy Database schemas and also where custom introducing custom SQL type mapping is not an option perhaps due to inconsistent use of the same data type in legacy applications.
• If there are dependent (child) tables that have composite primary keys (which include the primary key of the parent table). In this case, for saving a new entity I would need to intercept the setter of the primary key of the parent table to set the appropriate fields in the child tables.
• If I use lazy initialization, I would use property access for the primary key. This allows us to call persistentObj.getId() without completely initializing the proxy. This is applicable only for the “Id” field.
• If there is any other need to intercept the getters/setters for whatever reasons (validation, logging, debugging, etc.). This is very unlikely.

Technorati Tags: direct field access, encapsulation, Hibernate

I think it is very hard to build any enterprise application without using at least a little bit of straight Jdbc. Even if an application uses Hibernate heavily, there will be situations where using straight Jdbc makes most sense.

We use HibernateDaoSupport (using GenericDAO pattern) and JdbcDaoSupport Spring classes as the base classes for the HibernateDAOs and JdbcDAOs respectively. 

Here are some of the things to be aware of when we mix these 2 techniques for the CRUD operations within the same transaction:

• The HibernateDaoSupport beans and the JdbcSupportBeans should be wired with the same DataSource instance.
• Use HibernateTransactionManager, which auto-detects the DataSource used by Hibernate and transparently uses Hibernate transactions as JDBC transactions for that DataSource (using ThreadLocal connections).
• When using CMT or using declarative transactions using Spring AOP (Spring Transaction interceptors), the connections must be explicitly closed by the Jdbc DAOs. Otherwise, this leads to connection leaks. See Hibernate JavaDocs for Session.connection() method for more info. My favorite way to avoid this problem is to use Spring JdbcTemplate inside the Jdbc DAOs.
• Explicit flush on the Hibernate session may be required wherever Jdbc calls need to read the updates made through the hibernate objects within the same transaction.
• Explicit invalidation or refresh of the Hibernate Session is needed whenever updates are made through Jdbc.
• Hibernate by default uses Optimistic locking. So, the retry logic needs to be implemented in appropriate places.

As you can see it can get very tricky to synchronize straight Jdbc and Hibernate updates. So, I would try to use straight Jdbc only for bulk reads and reads that require complex joins, if possible.

Technorati Tags: Hibernate, Hibernate Transactions, Straight Jdbc

There are many real-world scenarios where Hibernate can be introduced on a legacy system that could be using straight JDBC for various reasons.

Also, there could be some pre-existing custom Java types to Database type mapping in the legacy application. Hibernate has it’s default mappings for each database excapsulated in the appropriate SQLDialect classes.

Fortunately, there is a very simple way to customize/override these SQL Types to Database types mappings by extending the SQLDialect class implementation corresponding to the Database in question.

The org.hibernate.dialect.SQLDialect abstract class has these following methods that are provided for overloading the SQL Types to Database types mapping. Also, there is a method to override the standard Database function name mappings:

• protected void registerColumnType(int code, int capacity, String name)
• protected void registerColumnType(int code, String name)
• protected void registerFunction(String name, SQLFunction function)

Here is an example of the usage of one of the above methods to override a mapping of a double (Java SQL type) to a “real” MS SQLServer type.

package com.x.y.common.dao;
import java.sql.Types;
import org.hibernate.dialect.SQLServerDialect;
public class MyMSSQLServerDialect extends SQLServerDialect {
  public MyMSSQLServerDialect() {
    super();
    //Override the default mappings for MS SQLServer
    registerColumnType( Types.DOUBLE, "real" );
  }
}

Also, there is a way to add custom value types in Hibernate by implementing either org.hibernate.UserType or org.hibernate.CompositeUserType.

Technorati Tags: custom Types mapping, Hibernate, Java SQL Types

I am working on a product that uses Hibernate that required DB (schema and data) migration, like most Enterprise Software.

Hibernate’s schema create/update feature (using hibernate.hbm2ddl.auto property) has it’s limitations. Also, certain complex DB migration requirements will definitely require custom coding.

So, I had to set hibernate.hbm2ddl.auto=validate due to the following known limitations with this Hibernate feature:

1. Cannot drop a table or a column
2. Cannot change a constraint on a column
3. Cannot add a column with a not-null constraint to an existing table

The possible approaches/alternatives for DB migration that I could think-of were:

1. Build a custom DB migration framework that uses separate SQL(DDL&DML) scripts and/or ETL scripts (Java/Ruby/Python/Groovy scripts) - flexible, efficient, could become unmanageable.
2. ETLM - lot of work required, process decoupled from the application, custom ETLM process for every upgrade.
3. Third party DB Upgrade/Change management tools:
   • Liquibase - XML based (Not very useful for most applications that do not maintain DB schema in XML)
   • AutoPatch
   • DBMigrate (Is this production-ready?)

The other design decision was to make the application “smart” so that it:

• Maintains the version and executed script info. so that the same old scripts are not executed again for every upgrade.
• Performs the necessary incremental upgrades based on the version number of the newly installed upgrade (read from the Jar manifest files) and the last installed version (read from the DB Version table).
• Requires no effort from the end-user of the application for upgrading the application.

I chose approach #1 listed above. The following section details the framework for Database Data and Schema Migration:

• This will require at least 2 new tables, “Version” and “MigrationScript” tables to hold the last upgraded version and the set of all migration scripts successfully executed so far.
• Before making any changes to the Schema or data for migration, the Database needs to be locked. It will be unlocked after all the migration changes are done.
• The main application Jars will have a Manifest entry that indicate the version of the Software that is installed. So, the application needs to migrate the Database from the version table in the database to the installed version and apply the necessary scripts.
• Designed File and Directory naming conventions for the script names:
  • File extensions:
    • SQL DDL –> .ddl
    • SQL DML –> .dml
    • Java/Ruby/Python/Groovy ETL Scripts –> .txt (This text file will have the fully qualified Java Class name or the name of the Ruby/Python/Groovy script to be executed. The scripts themselves are assumed to be on the classpath either directly or inside a Jar file).
  • File and Directory Naming conventions: The directories (that hold the migration script names) are based on the Major versions of the Software and the target database. There will be a “common” directory for all database-agnostic scripts (typically simple DML SQLs). The Files in those directories are named using a 4-digit number prefix that will help sorting. So, the DB migration module in the application will execute the scripts after sorting the scripts in alphabetical order. The number prefix gives the flexibility to name the script whatever the developer decides (for example, 0001_create_tables_for_baseline.ddl). This provides the ability to mix all kinds of the above scripts (DDL, DML & ETLM scripts) which makes this approach very flexible and powerful.
• All of the scripts are packaged in a Jar (or a Zip) file and deployed such that it is available in the classpath (as a resource).
• The DB migration module shall update the db version table with the new version/revision number and migration scripts table with all the successfully executed script names.
• The Java Class names specified in the Java ETLM script will have to implement a “Migrator” interface which will be called from within the DB migration framework. This is necessary for complex migrations.
• The framework will also need to take into consideration of the “Dev” builds vs “Production” builds. The “Dev” builds (typically executed from an Eclipse launcher) may not have the correct Manifest entries. So, the build version is set to ”-1″. The “Production”/QA builds will be run using ANT which will have the right Version info.

One of the major problems with this above approach is that the Developers will have to check-in the Scripts (if DB-specific) for all of the supported databases separately (in to separate directories). I created an ANT task that generates the DDL from a given hibernate mapping configuration. This serves as a starting point for new tables. On the flip side, this forces the developers to understand the tables that they are creating and also force them to make decisions. Also, testing migration scripts for several databases can be challenging, but, a continuous integration build setup for each DB should take care of this problem.

Next, I shall blog about the implementation to force Hibernate to use certain Database mappings and override the default Hibernate SQL type mappings.

Technorati Tags: Database Migration, Database upgrade design, DB migration, Hibernate