How to use MS-Access with Hibernate



Hibernate ORM framework does not provide any approch to connect to MS Access database. Unfortunately, Access is not supported officially by Hibernate. Hibernate does not specifies that What value we should specify for hibernate and what dialect in Hibernate properties/configuration we should use. There is no specific dialect that suppport the MS Access database through ODBC-JDBC bridge. There are some venders where you can find MS Access dialect like hxtt ("http://www.hxtt.com/hibernate.html").
But there is a way to connect to MS Access database in the hibenrate. I don't know where it works perfectly or not, but I did an experiment using SQLServerDialect to connect msaccess database file. It works with some warning in my demo application. Let see the following example.

Entity bean
package entities;
import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class Employee {
    private int id;
    private String name;
    private String empCode;
@Id
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmpCode() {
        return empCode;
    }

    public void setEmpCode(String empCode) {
        this.empCode = empCode;
    }
}
Mainclass

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;

import entities.Employee;

public class InsertData {
    public static void main(String[] args) {
        SessionFactory sessions =new AnnotationConfiguration().configure().buildSessionFactory();
            Session session = sessions.openSession();
        session.beginTransaction();
        Employee emp = new Employee();
        emp.setId(6);
        emp.setName("gggggg");
        emp.setEmpCode("22423desed");
        session.save(emp);
        session.getTransaction().commit();
    }
}

hibernate.cfg.xml


<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>
        <property name="hibernate.connection.driver_class">sun.jdbc.odbc.JdbcOdbcDriver</property>
        <property name="hibernate.connection.url">jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=E:/db3.mdb</property>
        <property name="connection.username"></property>
        <property name="connection.password"></property>
        <property name="connection.pool_size">1</property>
        <property name="current_session_context_class">thread</property>
        <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
        <property name="show_sql">true</property>
        <property name="hbm2ddl.auto">update</property>
        <mapping class="entities.Employee"/>
    </session-factory>
</hibernate-configuration>


In this configuration file, I have used sun.jdbc.odbc.JdbcOdbcDriver as driver and jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=E:/db3.mdb as connecting URL . This is simple JDBC-ODBC bridge connection type configuration and works with MS Access 2007. You may see some warning and error in execution but hibernate maps data to tables as it work with other databases. I don't recommend it you use it in you project without proper testing becuase of warnings.

4 comments:

Sunny Singh said...

i could not fully understand your hibernate.cfg.xml.


Have u tried hibernate with access using netbeans?????????????

Román said...

Hey, this is interesting! Have you tested this workaround with an extended scenario? I would like to know if this works with many-to-one relationships.
Otherwise, can you suggest another alternative? I need to build a small familiar application, but I don't want to deal with data migration in case we need to move it from one computer to other.
Thanks in advance!
Román

Quentin T said...

Thanks for your good idea.

But I have a fatal error when I try to reverse engineering my MS ACCESS 2007 Database :

org.hibernate.exception.GenericJDBCException: Error while reading primary key meta data for

`c:/test1.mdb`.TableTest1
Error while reading primary key meta data for `c:/test1.mdb`.TableTest1
java.sql.SQLException:

[Microsoft]
[Gestionnaire de pilotes ODBC] Le pilote ne prend pas cette fonction en charge

[Microsoft][Gestionnaire de pilotes ODBC] Le pilote ne prend pas cette fonction en charge
java.sql.SQLException:

[Microsoft][Gestionnaire de pilotes ODBC] Le pilote ne prend pas cette fonction en charge

[Microsoft][Gestionnaire de pilotes ODBC] Le pilote ne prend pas cette fonction en charge


On a very simple database, It's impossible for me to do the reverse engineering cause of the primary key erreor.

You have an explication on it ?

Quentin T said...

Hello, I want to apply your tricks of your blog (hibernate with MS-ACCESS)

But I have a fatal error when I do the reverse engineering action :

Exception while generating code

Reason: org.hibernate.exception.GenericJDBCException: Error while reading primary key meta data for ...

Do you have already encountered this problem ?

Popular Posts