ID mapping diffs between PostgreSQL and SQL Server

Marcos Silva Pereira · **Posted:** Wed Nov 07, 2007 5:49 pm

Hibernate version: 3.2.0

Name and version of the database you are using:
PostgreSQL 8.1.0
SQL Server 2000

Hello, I am working in a project where we must support both PostgreSQL and SQL Server. Currently, we have a issue with id generator strategy basically because:

1. Our PostgreSQL schema is legacy
2. It uses serial type to id generation
3. SQL Server uses identity to id generation

So, the only difference between PostgreSQL and SQL Server is in id mapping:

PostgreSQL:

Code:

<id name="id">
   <generator class="sequence">
      <param name="sequence">sequence_name</param>
   </generator>
</id>

SQL Server:

Code:

<id name="id">
   <generator class="native" />
</id>

I'm thinking about extends PostgreSQL dialect to deals with serial (which must handle with schema export, I guess). So, my questions are:
1. What is the best way to deals with these differences?
2. Someone could provide a path showing how to extends PostgreSQL dialect to handle with serial type?

Kind Regards

Marcos Silva Pereira · **Posted:** Thu Nov 08, 2007 5:36 pm

Well, I have implemented two classes to solve this problem:

1. A subclass of org.hibernate.id.SequenceGenerator that sets SEQUENCE property to use the sequence generated by serial:

Code:

package com.provider.hibernate.id;

import org.hibernate.MappingException;
import org.hibernate.dialect.Dialect;
import org.hibernate.id.SequenceGenerator;
import org.hibernate.mapping.Table;
import org.hibernate.type.Type;

import java.util.Properties;

public class SerialIdentifierGenerator extends SequenceGenerator {

    public void configure(Type type, Properties params, Dialect dialect) throws MappingException {

        String schemaName = params.getProperty(SCHEMA);
        String catalogName = params.getProperty(CATALOG);
        String tableName = params.getProperty(TABLE);
        String pkName = params.getProperty(PK);

        String sequenceName = tableName + "_" + pkName + "_seq";

        sequenceName = Table.qualify(catalogName, schemaName, sequenceName);

        params.setProperty(SEQUENCE, sequenceName);

        super.configure(type, params, dialect);

    }
}

I create a simple test case to it:

Code:

package com.provider.hibernate.id.test;

import com.provider.hibernate.id.SerialIdentifierGenerator;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.PostgreSQLDialect;
import org.hibernate.type.LongType;

import static org.hibernate.id.PersistentIdentifierGenerator.*;
import static org.junit.Assert.assertEquals;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.Properties;

public class SerialIdentifierGeneratorTest {

    SerialIdentifierGenerator generator;
    Dialect dialect;

    @Before
    public void initGenerator() {

        Properties properties = new Properties();
        properties.setProperty(SCHEMA, "schema");
        properties.setProperty(TABLE, "mytable");
        properties.setProperty(PK, "id");

        generator = new SerialIdentifierGenerator();
        dialect = new PostgreSQLDialect();

        generator.configure(new LongType(), properties, dialect);
        
    }

    @Test
    public void verifyIfGeneratorIsConfigured() {

        String expectedSequenceName = "schema.mytable_id_seq";

        String sequenceName = generator.getSequenceName();

        assertEquals(expectedSequenceName, sequenceName);
        assertEquals(expectedSequenceName, generator.generatorKey());
        assertEquals("create sequence " + expectedSequenceName, generator.sqlCreateStrings(dialect)[0]);
        assertEquals("drop sequence " + expectedSequenceName, generator.sqlDropStrings(dialect)[0]);

    }

    @After
    public void destroyGenerator() {
        this.generator = null;
    }

    
}

2. I also subclass PostgreSQLDialect:

Code:

package com.provider.hibernate;

import org.hibernate.dialect.PostgreSQLDialect;
import com.provider.hibernate.id.SerialIdentifierGenerator;

public class PostgreSQLSerialDialect extends PostgreSQLDialect {
    public Class getNativeIdentifierGeneratorClass() {
        return SerialIdentifierGenerator.class;
    }
}

So, I use the following mappings to validate the classes:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping schema="tests">
    <class name="com.provider.hibernate.test.Parent">
        <id name="id">
            <generator class="native" />
        </id>
        <property name="email"/>
        <property name="name"/>
        <set name="children" cascade="all" inverse="true">
            <key column="parent"/>
            <one-to-many class="com.provider.hibernate.test.Child" />
        </set>
    </class>
</hibernate-mapping>

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping schema="tests">
    <class name="com.provider.hibernate.test.Child">
        <id name="id">
            <generator class="native" />
        </id>
        <property name="age"/>
    </class>
</hibernate-mapping>

When I save an instance of Parent with three Children, the following sql statements are executed:

Code:

Hibernate: select nextval ('testes.Parent_id_seq')
Hibernate: select nextval ('testes.Child_id_seq')
Hibernate: select nextval ('testes.Child_id_seq')
Hibernate: select nextval ('testes.Child_id_seq')
Hibernate: insert into testes.Parent (email, name, id) values (?, ?, ?)
Hibernate: insert into testes.Child (age, id) values (?, ?)
Hibernate: insert into testes.Child (age, id) values (?, ?)
Hibernate: insert into testes.Child (age, id) values (?, ?)

It is interesting create a jira issue to submit the codes?

Kind Regards