Running Hibernate and JPA with FireBird

 

This is dedicated to anyone who is going to try to run Hibernate (possibly with JPA) with FireBird. This article was written, when I was using Hiberante 3.5.6 to connect to FireBird 2.1 and 2.5 things might have changed since then. I filed an issue to include my improvements in Hibernate: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5430

To connect to FireBird server, you use JayBird (I use JayBird 2.1) You have to have all the right DLLs accesible to your application, as described on the firebird help and setup the right JDBC connection string.  This is described well on different places on the Internet. The only catch is that if you get error messages like these:

org.firebirdsql.jdbc.FBSQLException: The result set is closed

you have to add a parameter ?defaultResultSetHoldable=True to your connection string. This was inspired by http://jasperforge.org/plugins/espforum/view.php?group_id=83&forumid=101&topicid=36481 It is to be noted, that setting this parameter makes some operations painfully slow.

First thing to notice, is that, unless your application is quite simple, Hibernate and Firebird won’t work with default settings. There are two things, you might encounter:

1. The default FirebirdDialect does not support temporary tables
   
2. Firebird is limited to 30 chars per identifier, which Hibernate does not honor (and JPA especially can generate quite a long indentifier, if you don’t name your classes very short)
   
   

We’ll deal with both of them.

1. SUPPORT FOR TEMPORARY TABLES

To support temporary tables (support added in FireBird 2.1), you need to subclass FirebirdDialect as follows:

import org.hibernate.dialect.FirebirdDialect;
/**
 * A simple extension of {@link FirebirdDialect} to make use of Firebird 2.1
 * support for temporary tables.
 * @author Martin Cerny
 */
public class Firebird21Dialect extends FirebirdDialect{
    @Override
    public boolean dropTemporaryTableAfterUse() {
        /*
         * The table should be deleted, because its contents is set to survive commit.
         * Data surviving commit seems to be a prudent choice
         */
        return true;
    }
    @Override
    public String getCreateTemporaryTablePostfix() {
        /*
         * The table preserves rows on commit - this seems to be a prudent choice
         * but forces dropTemporaryTableAfterUse to be set to true
         */
        return " ON COMMIT PRESERVE ROWS";
    }
    @Override
    public String getCreateTemporaryTableString() {
        return "CREATE GLOBAL TEMPORARY TABLE ";
    }
    @Override
    public boolean supportsTemporaryTables() {
        return true;
    }
}

Now pass this dialect to your connection as usual.

2. Limiting identifier length

To limit the length of identifiers generated for the database you need to create your own ComponentNamingStrategy. I decided to create a generic strategy, that delegates the actual naming to some base strategy and only shortens the identifiers, that are too long. The shortening is done such that first four letters of every word are preserved (if possible). This makes the shortened identifiers quite readable. If that is not enough, characters are left out from the middle of the identifier. To avoid collision, a simple two-digit hex hash is appended to the end of the identifier. However it does not (and no naming strategy can) avoid all collisions, there will always be strings that result in the same shortened identifier. But it works well, if you don’t name your classes and fields insanely. The class is as follows:

import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import org.hibernate.cfg.NamingStrategy;
/**
 * A naming strategy that wraps another naming strategy, but makes all the
 * identifiers created shrink to specified length limit. This is done in two phases:
 * <br>
 * 1) The identifier is split into "words" - either by camel case or other separators -
 * those words are then shortened to {@link #minimalWordLength} starting from the first word,
 * until the identifier is short enough.
 * 2) If preceding step does not make the identifier short enough, characters in
 * the middle of the identifier are removed and replaced by {@link #ellipsis} to make
 * the identifier short enough.
 * <br><br>
 * The naming strategy does not guarantee that the shortened identifiers won't conflict.
 * (which is theoretically impossible]
 * It just does a nice try.
 * @author Martin Cerny
 */
public class GenericLengthLimitedNamingStrategy implements NamingStrategy {
    /**
     * The length limit imposed on db identifiers
     */
    private int lengthLimit;
    private boolean addHash = true;
    /**
     * Default value of the length limit
     */
    public static final int defaultLimit = 30;
    /**
     * When truncating the identifier in the middle, those characters are used
     * to notate the place where the charachters were removed
     */
    public static final String ellipsis = "__";
    private final Logger logger = Logger.getLogger(getClass());
    private NamingStrategy baseStrategy;
    /**
     * Create the strategy copying given base strategy with given limit
     * @param lengthLimit
     */
    public GenericLengthLimitedNamingStrategy(NamingStrategy strategy, int lengthLimit) {
        this.lengthLimit = lengthLimit;
        baseStrategy = strategy;
    }
    /**
     * Create the strategy copying given base strategy with the {@link #defaultLimit}
     */
    public GenericLengthLimitedNamingStrategy(NamingStrategy strategy) {
        this.lengthLimit = defaultLimit;
        baseStrategy = strategy;
    }
    public GenericLengthLimitedNamingStrategy(int lengthLimit, boolean addHash, NamingStrategy baseStrategy) {
        this.lengthLimit = lengthLimit;
        this.addHash = addHash;
        this.baseStrategy = baseStrategy;
    }
    /*
     * Following two methods were taken from http://www.java2s.com/Code/Java/Data-Type/SplitsaStringbyCharactertypeasreturnedbyjavalangCharactergetTypechar.htm
     * and are to be accompanied by the followin notice:
     * Licensed to the Apache Software Foundation (ASF) under one or more
     * contributor license agreements.  See the NOTICE file distributed with
     * this work for additional information regarding copyright ownership.
     * The ASF licenses this file to You under the Apache License, Version 2.0
     * (the "License"); you may not use this file except in compliance with
     * the License.  You may obtain a copy of the License at
     *
     *      http://www.apache.org/licenses/LICENSE-2.0
     *
     * Unless required by applicable law or agreed to in writing, software
     * distributed under the License is distributed on an "AS IS" BASIS,
     * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
     * See the License for the specific language governing permissions and
     * limitations under the License.
     */
    /**
     * <p>Splits a String by Character type as returned by
     * <code>java.lang.Character.getType(char)</code>. Groups of contiguous
     * characters of the same type are returned as complete tokens, with the
     * following exception: the character of type
     * <code>Character.UPPERCASE_LETTER</code>, if any, immediately
     * preceding a token of type <code>Character.LOWERCASE_LETTER</code>
     * will belong to the following token rather than to the preceding, if any,
     * <code>Character.UPPERCASE_LETTER</code> token.
     * <pre>
     * StringUtils.splitByCharacterTypeCamelCase(null)         = null
     * StringUtils.splitByCharacterTypeCamelCase("")           = []
     * StringUtils.splitByCharacterTypeCamelCase("ab de fg")   = ["ab", " ", "de", " ", "fg"]
     * StringUtils.splitByCharacterTypeCamelCase("ab   de fg") = ["ab", "   ", "de", " ", "fg"]
     * StringUtils.splitByCharacterTypeCamelCase("ab:cd:ef")   = ["ab", ":", "cd", ":", "ef"]
     * StringUtils.splitByCharacterTypeCamelCase("number5")    = ["number", "5"]
     * StringUtils.splitByCharacterTypeCamelCase("fooBar")     = ["foo", "Bar"]
     * StringUtils.splitByCharacterTypeCamelCase("foo200Bar")  = ["foo", "200", "Bar"]
     * StringUtils.splitByCharacterTypeCamelCase("ASFRules")   = ["ASF", "Rules"]
     * </pre>
     * @param str the String to split, may be <code>null</code>
     * @return an array of parsed Strings, <code>null</code> if null String input
     * @since 2.4
     */
    public static String[] splitByCharacterTypeCamelCase(String str) {
        return splitByCharacterType(str, true);
    }
    /**
     * <p>
     * Splits a String by Character type as returned by
     * <code>java.lang.Character.getType(char)</code>. Groups of contiguous
     * characters of the same type are returned as complete tokens, with the
     * following exception: if <code>camelCase</code> is <code>true</code>,
     * the character of type <code>Character.UPPERCASE_LETTER</code>, if any,
     * immediately preceding a token of type
     * <code>Character.LOWERCASE_LETTER</code> will belong to the following
     * token rather than to the preceding, if any,
     * <code>Character.UPPERCASE_LETTER</code> token.
     *
     * @param str
     *          the String to split, may be <code>null</code>
     * @param camelCase
     *          whether to use so-called "camel-case" for letter types
     * @return an array of parsed Strings, <code>null</code> if null String
     *         input
     * @since 2.4
     */
    private static String[] splitByCharacterType(String str, boolean camelCase) {
        if (str == null) {
            return null;
        }
        if (str.length() == 0) {
            return new String[0];
        }
        char[] c = str.toCharArray();
        List list = new ArrayList();
        int tokenStart = 0;
        int currentType = Character.getType(c[tokenStart]);
        for (int pos = tokenStart + 1; pos < c.length; pos++) {
            int type = Character.getType(c[pos]);
            if (type == currentType) {
                continue;
            }
            if (camelCase && type == Character.LOWERCASE_LETTER
                    && currentType == Character.UPPERCASE_LETTER) {
                int newTokenStart = pos - 1;
                if (newTokenStart != tokenStart) {
                    list.add(new String(c, tokenStart, newTokenStart - tokenStart));
                    tokenStart = newTokenStart;
                }
            } else {
                list.add(new String(c, tokenStart, pos - tokenStart));
                tokenStart = pos;
            }
            currentType = type;
        }
        list.add(new String(c, tokenStart, c.length - tokenStart));
        return (String[]) list.toArray(new String[list.size()]);
    }
    /**
     * Does the step 2) of above mentioned shortening process - ie. takes enough
     * characters from the middle of the string and replaces them with {@link #ellipsis}
     * @param source
     * @param requiredLength
     * @return
     */
    protected String removeMiddle(String source, int requiredLength){
        if(source.length() <= requiredLength){
            return source;
        } else {
            int charsToRemove = source.length() - requiredLength + ellipsis.length();
            int charsLeftOnBothSides = (source.length() - charsToRemove) / 2;
            return source.substring(0,charsLeftOnBothSides) + ellipsis + source.substring(source.length() - charsLeftOnBothSides, source.length());
        }
    }
    /**
     * Minimal number of characters for word shortening
     */
    public static final int minimalWordLength = 4;
    /**
     * Shortens individual "words" (separated by camel case and other delimiters)
     * to {@link #minimalWordLength} until the string is short enough
     * @param source
     * @param requiredLength
     * @return
     */
    protected String shortenWordsInString(String source, int requiredLength){
            String[] parts = splitByCharacterTypeCamelCase(source);
            
            if(parts.length <= 1){
                return(source);
            }
            int totalCharsRemoved = 0;
            for(int i = 0; i < parts.length; i++){
                if(source.length() - totalCharsRemoved <= requiredLength){
                    break;
                }
                if(parts[i].length() > minimalWordLength){
                    totalCharsRemoved += parts[i].length() - minimalWordLength;
                    parts[i] = parts[i].substring(0,minimalWordLength);
                }
            }
            StringBuilder shortenedStringBuilder = new StringBuilder();
            for(int i = 0; i < parts.length; i++){
                shortenedStringBuilder.append(parts[i]);
            }
            return(shortenedStringBuilder.toString());
    }
    protected String getHash(String s){
        int charSum = 0;
        for(char c : s.toCharArray()){
            charSum += c;
        }
        return(Integer.toHexString(charSum % 255));
    }
    protected int getHashLength(){
        return 2;
    }
    /**
     * Shortens given string to fit in the length limit
     * @param unshortened
     * @return
     */
    protected String shortenString(String unshortened){
        int lengthLimitWithoutHash = lengthLimit;
        if(addHash){
            lengthLimitWithoutHash -= getHashLength();
        }
        if(unshortened.length() <= lengthLimit){
            return unshortened;
        } else {
            String shortened = shortenWordsInString(unshortened, lengthLimitWithoutHash);
            if(shortened.length() > lengthLimitWithoutHash){
                shortened = removeMiddle(unshortened, lengthLimitWithoutHash);
            }
            if(addHash){
                shortened = shortened + getHash(unshortened);
            }
            logger.debug("Shortened DB identifier " + unshortened + " to " + shortened);
            return shortened;
        }
    }
    /**
     * Delegates the call to {@link #baseStrategy} and shortens the result
     * @param ownerEntity
     * @param ownerEntityTable
     * @param associatedEntity
     * @param associatedEntityTable
     * @param propertyName
     * @return
     */
    @Override
    public String collectionTableName(String ownerEntity, String ownerEntityTable, String associatedEntity, String associatedEntityTable, String propertyName) {
        return shortenString(baseStrategy.collectionTableName(ownerEntity, ownerEntityTable, associatedEntity, associatedEntityTable, propertyName));
    }
    /**
     * Delegates the call to {@link #baseStrategy} and shortens the result
     * @param propertyName
     * @param propertyEntityName
     * @param propertyTableName
     * @param referencedColumnName
     * @return
     */
    @Override
    public String foreignKeyColumnName(String propertyName, String propertyEntityName, String propertyTableName, String referencedColumnName) {
        return shortenString(baseStrategy.foreignKeyColumnName(propertyName, propertyEntityName, propertyTableName, referencedColumnName));
    }
    /**
     * Delegates the call to {@link #baseStrategy} and shortens the result
     * @param columnName
     * @param propertyName
     * @param referencedColumn
     * @return
     */
    @Override
    public String logicalCollectionColumnName(String columnName, String propertyName, String referencedColumn) {
        return shortenString(baseStrategy.logicalCollectionColumnName(columnName, propertyName, referencedColumn));
    }
    /**
     * Delegates the call to {@link #baseStrategy} and shortens the result
     * @param tableName
     * @param ownerEntityTable
     * @param associatedEntityTable
     * @param propertyName
     * @return
     */
    @Override
    public String logicalCollectionTableName(String tableName, String ownerEntityTable, String associatedEntityTable, String propertyName) {
        return shortenString(baseStrategy.logicalCollectionTableName(tableName, ownerEntityTable, associatedEntityTable, propertyName));
    }
    /**
     * Delegates the call to {@link #baseStrategy} and shortens the result
     * @param columnName
     * @param propertyName
     * @return
     */
    @Override
    public String logicalColumnName(String columnName, String propertyName) {        
        return shortenString(baseStrategy.logicalColumnName(columnName, propertyName));
    }
    /**
     * Delegates the call to {@link #baseStrategy} and shortens the result
     * @param propertyName
     * @return
     */
    @Override
    public String propertyToColumnName(String propertyName) {
        return shortenString(baseStrategy.propertyToColumnName(propertyName));
    }
    /**
     * Delegates the call to {@link #baseStrategy} and shortens the result
     * @param className
     * @return
     */
    @Override
    public String classToTableName(String className) {
        return shortenString(baseStrategy.classToTableName(className));
    }
    /**
     * Delegates the call to {@link #baseStrategy} and shortens the result
     * @param columnName
     * @return
     */
    @Override
    public String columnName(String columnName) {
        return shortenString(baseStrategy.columnName(columnName));
    }
    /**
     * Delegates the call to {@link #baseStrategy} and shortens the result
     * @param joinedColumn
     * @param joinedTable
     * @return
     */
    @Override
    public String joinKeyColumnName(String joinedColumn, String joinedTable) {
        return shortenString(baseStrategy.joinKeyColumnName(joinedColumn, joinedTable));
    }
    /**
     * Delegates the call to {@link #baseStrategy} and shortens the result
     * @param tableName
     * @return
     */
    @Override
    public String tableName(String tableName) {
        return shortenString(baseStrategy.tableName(tableName));
    }
}

Now a typical use would be to delegate to ComponentSafeNamingStrategy:

import org.hibernate.cfg.DefaultComponentSafeNamingStrategy;
/**
 * An implementation of {@link GenericLengthLimitedNamingStrategy} using {@link DefaultComponentSafeNamingStrategy}
 * @author Martin Cerny
 */
public class LengthLimitedComponentSafeNamingStrategy extends GenericLengthLimitedNamingStrategy {
    public LengthLimitedComponentSafeNamingStrategy() {
        super(new DefaultComponentSafeNamingStrategy());
    }
}

You then pass that naming strategy to your connection and here we go – it works. We are now running a mid-sized application in production for few weeks with this setup and have not experienced any further problems.