Friends, many times in organizations, where we work, do not allow access to even necessary developer tools such as local database installation. This could be due to quite valid reasons as well (and sometimes it seems just absurd). In this hibernate in-memory database tutorial, I am giving an example of testing your hibernate code without any database installation.
1. In-memory Database Implementation
1.1. Maven dependency
<dependency> <groupId>hsqldb</groupId> <artifactId>hsqldb</artifactId> <version>1.8.0.10</version> </dependency>
1.2. Get database Connection
For this example, I am using HSQLDB Database for creating and accessing in-memory database through our hibernate code. If you are using plain simple JDBC then you can directly use below statement for accessing the in memory database.
Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:howtodoinjava", "sa", "");
The main thing to notice here is “mem:howtodoinjava“. Here mem indicates that use the in-memory database, in stead of any physical database.
1.3. Default username and password are required
Please note that though you will have to provide the default username “sa” and a blank password to get connected to database, without which you will be getting below exception.
Caused by: java.sql.SQLException: Access is denied at org.hsqldb.jdbc.Util.sqlException(Unknown Source) at org.hsqldb.jdbc.jdbcConnection.<init>(Unknown Source) at org.hsqldb.jdbcDriver.getConnection(Unknown Source) at org.hsqldb.jdbcDriver.connect(Unknown Source) at org.hibernate.engine.jdbc.connections.internal.DriverConnectionCreator.makeConnection(DriverConnectionCreator.java:55) ... 15 more
2. Hibernate inmemory database example
Now go though below sourcecode files for the example of in memory database access using hibernate.
2.1. 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.archive.autodetection">class,hbm</property> <property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property> <property name="hibernate.show_sql">true</property> <property name="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</property> <property name="hibernate.connection.username">sa</property> <property name="hibernate.connection.password"></property> <property name="hibernate.connection.url">jdbc:hsqldb:mem:howtodoinjava</property> <property name="hibernate.hbm2ddl.auto">create</property> <mapping class="com.howtodoinjava.demo.entity.EmployeeEntity"></mapping> </session-factory> </hibernate-configuration>
2.2. HibernateUtil.java
package com.howtodoinjava.demo.util; import org.hibernate.SessionFactory; import org.hibernate.boot.registry.StandardServiceRegistryBuilder; import org.hibernate.cfg.Configuration; import org.hibernate.service.ServiceRegistry; public class HibernateUtil { private static SessionFactory sessionFactory = buildSessionFactory(); private static SessionFactory buildSessionFactory() { try { if (sessionFactory == null) { Configuration configuration = new Configuration().configure(HibernateUtil.class.getResource("/hibernate.cfg.xml")); StandardServiceRegistryBuilder serviceRegistryBuilder = new StandardServiceRegistryBuilder(); serviceRegistryBuilder.applySettings(configuration.getProperties()); ServiceRegistry serviceRegistry = serviceRegistryBuilder.build(); sessionFactory = configuration.buildSessionFactory(serviceRegistry); } return sessionFactory; } catch (Throwable ex) { System.err.println("Initial SessionFactory creation failed." + ex); throw new ExceptionInInitializerError(ex); } } public static SessionFactory getSessionFactory() { return sessionFactory; } public static void shutdown() { getSessionFactory().close(); } }
2.3. EmployeeEntity.java
package com.howtodoinjava.demo.entity; import java.io.Serializable; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.Table; import javax.persistence.UniqueConstraint; @Entity @org.hibernate.annotations.Entity(dynamicUpdate = true) @Table(name = "Employee", uniqueConstraints = {@UniqueConstraint(columnNames = "ID"), @UniqueConstraint(columnNames = "EMAIL")}) public class EmployeeEntity implements Serializable { private static final long serialVersionUID = -1798070786993154676L; @Id @Column(name = "ID", unique = true, nullable = false) private Integer employeeId; @Column(name = "EMAIL", unique = true, nullable = false, length = 100) private String email; @Column(name = "FIRST_NAME", unique = false, nullable = false, length = 100) private String firstName; @Column(name = "LAST_NAME", unique = false, nullable = false, length = 100) private String lastName; public Integer getEmployeeId() { return employeeId; } public void setEmployeeId(Integer employeeId) { this.employeeId = employeeId; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getFirstName() { return firstName; } public void setFirstName(String firstName) { this.firstName = firstName; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } }
Now test above code.
2.4. TestHibernate.java
package com.howtodoinjava.test; import org.hibernate.Session; import com.howtodoinjava.demo.entity.EmployeeEntity; import com.howtodoinjava.demo.util.HibernateUtil; public class TestHibernate { public static void main(String[] args) { Session session = HibernateUtil.getSessionFactory().openSession(); session.beginTransaction(); // Add new Employee object EmployeeEntity emp = new EmployeeEntity(); emp.setEmployeeId(1); emp.setEmail("demo-user@mail.com"); emp.setFirstName("demo"); emp.setLastName("user"); session.save(emp); session.getTransaction().commit(); HibernateUtil.shutdown(); } } Output: Hibernate: drop table Employee if exists Hibernate: create table Employee (ID integer not null, EMAIL varchar(100) not null, FIRST_NAME varchar(100) not null, LAST_NAME varchar(100) not null, primary key (ID)) Hibernate: alter table Employee add constraint UK_ardf0f11mfa6tujs3hflthwdv unique (EMAIL) Hibernate: insert into Employee (EMAIL, FIRST_NAME, LAST_NAME, ID) values (?, ?, ?, ?)
3. Project Structure and Dependencies
Below is the project structure for this example.
Please go through the pom.xml file if you need any help in setting up the maven dependencies for this example.
3.1. pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd; <modelVersion>4.0.0</modelVersion> <groupId>ABC</groupId> <artifactId>ABC</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <build> <sourceDirectory>src</sourceDirectory> <plugins> <plugin> <artifactId>maven-compiler-plugin</artifactId> <version>2.3.2</version> <configuration> <source>1.7</source> <target>1.7</target> </configuration> </plugin> </plugins> </build> <dependencies> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>4.3.6.Final</version> </dependency> <!-- for JPA, use hibernate-entitymanager instead of hibernate-core --> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-entitymanager</artifactId> <version>4.3.6.Final</version> </dependency> <!-- optional --> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-osgi</artifactId> <version>4.3.6.Final</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-envers</artifactId> <version>4.3.6.Final</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-c3p0</artifactId> <version>4.3.6.Final</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-proxool</artifactId> <version>4.3.6.Final</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-infinispan</artifactId> <version>4.3.6.Final</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-ehcache</artifactId> <version>4.3.6.Final</version> </dependency> <dependency> <groupId>antlr</groupId> <artifactId>antlr</artifactId> <version>2.7.6</version> </dependency> <dependency> <groupId>commons-collections</groupId> <artifactId>commons-collections</artifactId> <version>3.1</version> </dependency> <dependency> <groupId>dom4j</groupId> <artifactId>dom4j</artifactId> <version>1.6.1</version> </dependency> <dependency> <groupId>javassist</groupId> <artifactId>javassist</artifactId> <version>3.4.GA</version> </dependency> <dependency> <groupId>javax.transaction</groupId> <artifactId>jta</artifactId> <version>1.1</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> <version>1.5.6</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> <version>1.5.6</version> </dependency> <dependency> <groupId>hsqldb</groupId> <artifactId>hsqldb</artifactId> <version>1.8.0.10</version> </dependency> </dependencies> </project>
That’s all for this quick tutorial about Using In-memory Database With Hibernate.
Happy Learning !!
DtothekK
I believe you may have an erroneous “1” in the password
hibernate.connection.password”>1
When I removed it, it worked.
Thanks none-the-less!
Bludwarf
Thank you DtothekK, I faced the same problem ๐
Roman
here is an error in hibernate.cfg.xml
org.hsqldb.jdbcDriver
The name of class is supposed to start with capital later
Lokesh Gupta
Code provided is correct. Please refer to : http://hsqldb.org/doc/guide/
Rohan
Hello Lokesh,
I am wondering why select query is not working in my case.
I mean when i write code to select all data from the data base or say row,It is just executing the select query but list size is 0.why so? your example is show only insert operation.please give me brief about this to how to do it.
Lokesh Gupta
You must do insert and select.. both in single session. Please confirm if you are doing it in single session.
Bharath kumar
Thanks Lokesh, Its very helpful. I just came to know that, we can use hibernate to connect IM database like any other RDBMS by this tutorial.
I have a couple of questions here.
#1. Do we need to install HSql DB separetly? How ? (Some info links would be helpful :))
#2. If i want to use this HSQL data base(or any other IM db) for my Junits, Do i need to dump all my database objects and testdata into this database? Or any other way i can use existing DB as IM DB?
Why I am asking this is, we are working in a project where most of the business logic reside at DB Procedures. I am thinking in a way how can we make our junits without depends on our database(Oracle).
Thanks in Advance. Please Help.
Lokesh Gupta
1) For in memory usage, you do not need to install HSQLDB. Just include its jar file as I have added through maven.
2) I guess you will need to dump all your data before execution. It’s in-memory so once it’s not in use, it will be destroyed including DB schema.
If you not want to use external database, then recommended practice is to use mocking.
Bharath kumar
Thanks again lokesh.
Do u have any idea, what are all other IM databases which is supporting by hibernate4?
Help me with some tutorial links to explore on Hsql db.
Lokesh Gupta
There is one more database H2 database, I know of. Try googling for hsqldb related content, because that’s what I will do as well ๐
Masum
Thanks for very helpful tutorial. I’m new in Java and Hibernate as well. I managed to compile the code and understood the concept as well. But the problem is that I don’t know how to execute the code to debug some stuff. When I start app as java application in eclipse. I have a long list to of option. But I don’t see my actual test class to run. So How to run it and debug this app? Please see the link I asked same question in the stackoverflow @ https://stackoverflow.com/questions/26563177/using-in-memory-database-with-hibernate-tutorial-how-to-execute
Lokesh Gupta
As this was example only so I was executing it directly from main() method in TestHibernate.java.
andor
hi guys,
i am using Hibernate 4.3.5
i followed the tutorial,
i can see on the logs that the tables are created
but i am having this error on the first insert.
“java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: ADVISORID
did anyone faced the same error ?
thanks
Lokesh Gupta
Tried this solution?
andor
thank you for the reply,
in fact , the problem was a definition for a certain column on my entity
thank you for this great tuto, helped a lot.