Thursday, May 05, 2005

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.

6 Comments:

Blogger delaney816eddie said...

damn good blog, check out mine http://juicyfruiter.blogspot.com, comments always welcome!

10:36 PM  
Blogger db2 said...

Nice info
Regards,
Jack
http://db2examples.googlepages.com/

8:51 PM  
Blogger Daniel Cruz said...

I like this blog is fantastic, is really good written. Congratulation. Do you want to see something more? Read it...: Costa Rica is a country with a extremely sense of freedom. The landscapes are for much the most green in whole center america.The chances of investement are way to high, the average of Americans, European and people of the entire planet who is buying here is up in the sky !!!
Great investment opportunity in Costa Rica: condos, costa rica real estate, costa rica property. Visit us for more info at: http://www.jaco-bay.com/

8:10 AM  
Blogger Marcellg said...

Good information I work from home we actually an apartment at Polaris World but its still great to see people sharing code and helping each other out.

6:01 AM  
Blogger db2 said...

It was really useful
Thanks,

http://db2examples.googlepages.com

7:14 PM  
Blogger Alex said...

En mi computador hay muchos programas interesantes. Pero uno de ellas pude asistir me y posible socorà con otros parecidas situaciones - reparar archivo de access.

7:56 AM  

Post a Comment

<< Home