Hibernate 3 with Microsoft ms access 2000 odbc

Hibernate 3 with Microsoft ms access 2000 odbc

MS access is a kind of low feature DB to be found on most windoz PC's today.

My view is that to check out a few features of hibernate one need not have oracle, db2 or other such high end db.

To know what is hibernate please visit www.hibernate.org .

Now for using Hibernate, one must set up certain mandatory properties in a file generally called hibernate.properties file.

One of such a property is dialect : hibernate.dialect

There are several out of the box dialects given by hibernate that support Oracle, Db2 , MySQL etc.

However there is no out of the box support for MS Access.

What hibernate has though is an exellent plugglabe architechture, that allows users to specify new dialects by subclassing from org.hibernate.dialect.Dialect class.

So i tried to write up a class called MSAccessDialect.

The main issues i faced were mostly with Inserts and identitiy columns.

To know what an identity column in hibernate means please see here:-
http://www.hibernate.org/hib_docs/reference/en/html/mapping.html


I have got it working with Hibernate with Microsoft ms access odbc, including inserts and reads with identity.
I am using hibernate 3.0 , JDK 1.4 and Microsoft ms access 200.

Here is the dialect class:-

package org.hibernate.dialect;

import java.sql.Types;

import org.hibernate.cfg.Environment;

/**
* @author Suchak.Jani
*/
public class MSAccessDialect extends Dialect {
public MSAccessDialect() {
super();
registerColumnType( Types.BIT, "BIT" );
registerColumnType( Types.BIGINT, "INTEGER" );
registerColumnType( Types.SMALLINT, "SMALLINT" );
registerColumnType( Types.TINYINT, "BYTE" );
registerColumnType( Types.INTEGER, "INTEGER" );
registerColumnType( Types.CHAR, "VARCHAR(1)" );
registerColumnType( Types.VARCHAR, "VARCHAR($l)" );
registerColumnType( Types.FLOAT, "DOUBLE" );
registerColumnType( Types.DOUBLE, "DOUBLE" );
registerColumnType( Types.DATE, "DATETIME" );
registerColumnType( Types.TIME, "DATETIME" );
registerColumnType( Types.TIMESTAMP, "DATETIME" );
registerColumnType( Types.VARBINARY, "VARBINARY($l)" );
registerColumnType( Types.NUMERIC, "NUMERIC" );

getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE,NO_BATCH);
}

public String getIdentityColumnString() {
//return " counter ";
return "not null auto_number";
}

public String getIdentitySelectString() {
return "select @@IDENTITY";
}
}



Here is a test xml file which works :-

<hibernate-mapping>
<class name="com.suchak.hibernatetest.domainobjects.Person" table="Person">
<id name="ID" column="ID" >
<generator class="identity"/>
</id>
<property name="firstName">
<column name="FirstName"/>
</property>
<property name="lastName">
<column name="LastName"/>
</property>
</class>
</hibernate-mapping>

The above ID is an Interger in java and Autonumber in Microsoft ms access

Also it goes without saying that there is a person object. You can create it looking at the xml above.

Note:- There are problems of still not being able to use Long with the above but that is due to issues of the ms odbc driver. It has nothing to do with Hibernate.