Thursday, January 28, 2010

How to connect Hibernate with Microsoft Access


1. Go to Control Panel
2. Go to Administrative Tools
3. Go to ODBC Data sources
4. Create a DSN (say mydb), give the path to .mdb file.
5. Get hibernate.jar

6. Extract contents of access.zip and get Access_JDBC30.jar

7. Create a new Java Project in NetBeans 6.7 or greater. and give the above 2 jars in classpath of the project





8. Create the employee class inside demo package with the following contents

package demo;

import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.Id;

/**
*
* @author Yogi
*/
@Entity
public class Employee implements Serializable {
    @Id
    int empId;
    String empIbu;
    String empName;
    int empSalary;


    public Employee() {
    }
    public String toString()
    {
        return empName;
    }
    public Employee(int empId, String empIbu, String empName, int empSalary) {
        this.empId = empId;
        this.empSalary = empSalary;
        this.empName = empName;
        this.empIbu = empIbu;
    }

    public String getEmpIbu() {
        return empIbu;
    }

    public void setEmpIbu(String empIbu) {
        this.empIbu = empIbu;
    }

    public int getEmpId() {
        return empId;
    }

    public void setEmpId(int empId) {
        this.empId = empId;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

    public int getEmpSalary() {
       return empSalary;
    }

    public void setEmpSalary(int empSalary) {
        this.empSalary = empSalary;
    }

}

9. Create hibernate.cfg.xml in the root directory of the application (outside all packages)

<?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.connection.driver_class">com.hxtt.sql.access.AccessDriver</property>
<property name="hibernate.connection.url">jdbc:odbc:mydb</property>
<property name="hibernate.dialect">com.hxtt.support.hibernate.HxttAccessDialect</property>
<!-- Please note, here the value update is not working with MS-Access
Although it should work theoretically, but practically, the value 
update is able to create the table for the first time 
and after that it fails every time -->
<property name="hibernate.hbm2ddl.auto">create</property>
<property name="hibernate.show_sql">true</property>
<mapping class="demo.Employee"/>
</session-factory>
</hibernate-configuration>

10. Create EmployeeClient.java file inside client package (Though not mandatory)

/**
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

package client;

import demo.Employee;
import java.util.Iterator;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.classic.Session;

/**
*
* @author Yogi
*/
public class EmployeeClient {
    public static void main(String args[])
    {
        /* Here we have used AnnotationConfiguration because, we have used Annotations in         
         * Employee class to map the class fields with the table fields.
         * If Employee.hbm.xml is used for this mapping then here we would have used  
         * Configuration (which we normally do) 
         */

        SessionFactory factory=new AnnotationConfiguration().configure().buildSessionFactory();
        Session session = factory.openSession();
        Transaction tx=null;
        try
        {

            Criteria c = session.createCriteria(Employee.class);
            List l = c.list();
            Iterator it = l.iterator();
            while(it.hasNext())
            {
                System.out.println(it.next());
            }
//          Employee e = new Employee(234, "IVS", "Vinay", 6500);
//          tx = session.beginTransaction();
//          session.save(e);
//          tx.commit();
//          tx=null;
        }
        catch(HibernateException ex)
        {
            System.out.println("Exception occured : " + ex.getMessage());
        }
        finally
        {
            System.out.println("Closing the session");
            session.close();
        }
    }
}


Please note the text in red in EmployeeClient class.
Here we have used AnnotationConfiguration because, we have used Annotations in Employee class to map the class fields with the table fields.
If Employee.hbm.xml is used for this mapping then here we would have used Configuration (which we normally do)
SessionFactory factory=new Configuration().configure().buildSessionFactory();

4 comments:

  1. While fetching the values with Microsoft Access, Though it is able to fecth the values correctly, But still it is giving exceptions on the console.

    java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented.


    Still I am not able to figure out the reason for this.

    ReplyDelete
  2. I am using MS-Access 2003. Haven't tried it with Office 2007.

    ReplyDelete
  3. Hibernate works only with Relational databases . Since MS-Access is not a Relational database so hibernate will not work with MS-Access.

    Go through https://www.hibernate.org/80.html this as well for the complete databases support with Hibernate.



    Thanks And Regards,
    Jitendra Singh

    ReplyDelete
  4. Thank you very much, the example with jar files helped me a lot. Thank you once again.

    ReplyDelete