Unable to connect H2 DB with Hibernate

Question

I am trying to create a small H2 in-memory database while using hibernate. But the table is not dispplayed in the H2 UI. I'm not sure if I need to include anything else in the configuration.

Here is the code: Below are dependencies added for hibernate and h2 DB.

pom.xml

<dependency>  
    <groupId>org.hibernate</groupId>  
    <artifactId>hibernate-core</artifactId>  
    <version>5.3.1.Final</version>  
</dependency> 

<dependency>  
    <groupId>com.h2database</groupId>  
    <artifactId>h2</artifactId>  
    <scope>runtime</scope>
    <version>1.4.200</version> 

</dependency>  

This is the configuration XML which has all the H2 DB details.

hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>  
<!DOCTYPE hibernate-configuration PUBLIC  
        "-//Hibernate/Hibernate Configuration DTD 5.3//EN"  
        "http://www.hibernate.org/dtd/hibernate-configuration-5.3.dtd">  

<hibernate-configuration>
    <session-factory>
        <property name="connection.driver_class">org.h2.Driver</property>
        <property name="connection.url">jdbc:h2:mem:test;DB_CLOSE_DELAY=-1</property>
        <property name="dialect">org.hibernate.dialect.H2Dialect</property>
        <property name="show_sql">true</property>
        <property name="hbm2ddl.auto">create</property>
        <mapping class="HiberDemo.Employee"/>
    </session-factory>
</hibernate-configuration>

This creates the table along with the other columns.

Employee.java

package HiberDemo;

import javax.persistence.Entity;  
import javax.persistence.Id;  
import javax.persistence.Table;  

@Entity  
@Table(name= "emp500")   
public class Employee {    

@Id   
private int id;    
private String firstName,lastName;    

public int getId() {    
    return id;    
}    
public void setId(int id) {    
    this.id = id;    
}    
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;    
}    
}

This is to store some data into the table.

StoreData.java

package HiberDemo;

import java.sql.DriverManager;
import java.sql.SQLException;

import org.hibernate.Session;    
import org.hibernate.SessionFactory;    
import org.hibernate.Transaction;  
import org.hibernate.boot.Metadata;  
import org.hibernate.boot.MetadataSources;  
import org.hibernate.boot.registry.StandardServiceRegistry;  
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;

import com.sun.corba.se.pept.transport.Connection;  


public class StoreData {    
public static void main(String[] args) throws SQLException {    


    StandardServiceRegistry ssr = new StandardServiceRegistryBuilder().configure("hibernate.cfg.xml").build();  
    Metadata meta = new MetadataSources(ssr).getMetadataBuilder().build();  

SessionFactory factory = meta.getSessionFactoryBuilder().build();  
Session session = factory.openSession();  
Transaction t = session.beginTransaction();   



    Employee e1=new Employee();    
    e1.setId(101);    
    e1.setFirstName("abc");    
    e1.setLastName("xyz");    

    session.save(e1);  
    t.commit();  
    System.out.println("successfully saved");    
    factory.close();  
    session.close();   
   // conn.close();

}    
} 

This is the H2 DB UI where the table I have created is not displayed.

Answer 1

1. You must add the following two dependencies in your pom.xml

`

<dependency>
     <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>       
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>runtime</scope>
    </dependency>

`

2. Then enable H2 console by adding the following line in application.properties file

spring.h2.console.enabled=true

3. After that run your application and open Rest endpoint "/h2-console"

Like: http://localhost:9090/h2-console/

4. Now copy your JDBC URL from the console

Like: Database available at 'jdbc:h2:mem:b61c56f6-93f3-4324-8b4a-4ead06f669d1'

5. Copy and paste in you H2 console, then click on 'Connect'

---

You can also configure manually everything by adding the following configuration in your application.properties file:

#---------------------------------DATABASE CONFIG-----------------------

spring.h2.console.enabled=true

spring.datasource.url=jdbc:h2:mem:person_db
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=root
spring.datasource.password=root

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

#---------------------------------------------------------------

Answer 2

you may need to add the following lines to the application.properties file.

spring.datasource.url= jdbc:h2:mem:test

Answer 3

This is the default login to the H2 database. Confirm if your details match

Change testdb to test in the screenshot above

Answer 4

Check logs if temporary database is created. If so url is consoled in logs:

2020-05-28 12:20:01.280 INFO 26579 --- [ restartedMain] o.s.b.a.h2.H2ConsoleAutoConfiguration : H2 console available at '/h2-console'. Database available at 'jdbc:h2:mem:b9994036-2f8e-4d00-8d7d-012a270661fa'

If you try connecting using jdbc:h2:mem:b9994036-2f8e-4d00-8d7d-012a270661fa, test connection works and tables are also shown:

Answer 5

JPA dependency is missing. So, add below dependency.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

Answer 6

You might need to downgrade your version and use the below

<dependency>
     <groupId> com.h2database</groupId>
     <artifactId>h2</artifactId>
     <version>1.4.193</version>
 </dependency>

Answer 7

I had the same problem. solved after couple of days :) these 3 ways work same as each other.

1. downgrade your H2 dependency. if there isn't any version mentioned maven gets the latest so you might want to explicitly mention any version below 1.4.195 as below:

<dependency>
     <groupId> com.h2database</groupId>
     <artifactId>h2</artifactId>
     <version>1.4.193</version>
 </dependency>

2. in your application properties write the full name of the database you want to connect to. spring.datasource.url=jdbc:h2:mem:desiredName and in console use: jdbc:h2:mem:desiredName as name

3. by reading logs use the generated name. it differs time to time. H2 console available at '/h2'. Database available at 'jdbc:h2:mem:desiredName' this may have 16 letters after if no name is specified in application properties.

have fun.