Skip to main content

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.

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 ?
Anonymous said…
that worked really .. thank u :)

Popular posts from this blog

Using HyperSQL (HSQLDB)

HSQLDB is a portable RDBMS implemented in pure java. It can be embedded with your application as well as can be used separately. It is very a small database that supports almost all features of the standard database system. It comes with small jar file that can be found in lib folder. The HSQLDB jar package is located in the /lib directory of the ZIP package and contains several components and programs. Core components of jar file are : HyperSQL RDBMS Engine (HSQLDB), HyperSQL JDBC Driver, Database Manager, and Sql Tool. Installing and Using Download: download latest release of HyperSQL database from http://hsqldb.org website and extract it. You will see following contents. Here "bin" directory contains some batch files those can be used to run a swing based GUI tool. You can use runManagerSwing.bat to connect to database, but database must be on before running it. Directory lib contains File hsqldb.jar . It is the database to be used by you. Running database First

How to handle values from dynamically generated elements in web page using struts2

Some time you will see the form containing the button " Add More " . This facility is provided for the user to get the values for unknown number of repeating for some information. for example when you are asking to get the projects details from user, you need to put the option to add the more project for the user since you don't known how many projects user have. In the HTML form, you repeat the particular section to get the multiple values for those elements. In Html page , you can put the option to add new row of elements or text fields by writing the java script or using JQuery API. Now, the question is that how to capture the values of dynamically generated text fields on the server. Using the servlet programming you can get the values by using getParameters() method that resultants the array of the parameter having the same name. But this limit you to naming the text fields in the HTML form. To ally this approach, you have to take the same name for t

In Process Mode of HSQLDB in web application.

If you want to use the database into your web application, you can use the HSQLDB in In_Process mode. In this mode, you can embed the HSQLDB into your web application and it runs as a part of your web application programm in the same JVM. In this mode, the database does not open any port to connect to the application on the hosing machine and you don't need to configure anything to access it. Database is not expposed to other application and can not be accessed from any dabase tools like dbVisualizer etc. In this mode ,database will be unknown from any other person except you. But in the 1.8.0 version, you can use Server intance for external as well as in process access.  To close the databse, you can issue SHUTDOWN command as an SQL query.   In the in-process mode, database starts from JDBC with the associated databse file provided through  connection URL. for example   DriverManager.getConnection("jdbc:hsqldb:mydatabase","SA","");   Here myd