H2 Console Works But No Tables? Troubleshooting Spring Boot Database Setup

Your Spring Boot app starts, H2 Console connects, but... where are the tables? Here's how to test if your H2 database is working and fix the most common issue: missing tables.

The Problem

✅ App starts successfully
✅ Logs show: "HikariPool-1 - Start completed"
✅ H2 Console accessible at http://localhost:8082/h2-console
✅ Can connect to database
❌ No tables in database!

What's happening? Hibernate isn't creating your tables.

Let's diagnose and fix it.

---

5 Ways to Test H2 Configuration

Test 1: Check Startup Logs

Run your app:

./localResources/start-app.sh
# or
mvn spring-boot:run

Look for these success messages:

✅ HikariPool-1 - Starting...
✅ HikariPool-1 - Start completed.

What this means: Database connection pool initialized successfully.

Also look for:

✅ Hibernate:
    create table users (
       id bigint generated by default as identity,
        username varchar(255),
        email varchar(255),
        password varchar(255),
        primary key (id)
    )

What this means: Hibernate is creating tables from your entities.

Bad signs (errors):

❌ Caused by: org.h2.jdbc.JdbcSQLNonTransientConnectionException:
   Database may be already in use
❌ java.sql.SQLException: Database 'ecommerce_db' not found
❌ HikariPool-1 - Exception during pool initialization

---

Test 2: Access H2 Console (Visual Verification)

Start your app, then open:

http://localhost:8082/h2-console

Login credentials:

Field	Value
JDBC URL	jdbc:h2:file:./data/ecommerce_db
User Name	sa
Password	(leave blank)

Click "Connect"

If Connection Succeeds ✅

You'll see the H2 SQL interface.

Run this query to check for tables:

SELECT * FROM INFORMATION_SCHEMA.TABLES;

Expected result (tables exist):

TABLE_NAME
----------
USERS
PRODUCTS
ORDERS

Problem (no tables):

TABLE_NAME
----------
(empty - no results)

→ Continue to troubleshooting section below

If Connection Fails ❌

Error: "Database not found"

• Check JDBC URL matches application.properties
• Check database file exists: ls data/

Error: "Wrong user name or password"

• Username should be sa
• Password should be blank

---

Test 3: Check Database File Created

Command:

ls -la data/

Expected output:

drwxr-xr-x   4 user  staff   128 Oct 19 10:30 .
drwxr-xr-x  15 user  staff   480 Oct 19 10:30 ..
-rw-r--r--   1 user  staff  8192 Oct 19 10:35 ecommerce_db.mv.db  ✅
-rw-r--r--   1 user  staff   256 Oct 19 10:35 ecommerce_db.trace.db

Key file: ecommerce_db.mv.db (main database file)

If file doesn't exist:

• App might not be starting correctly
• Check application.properties for correct path
• Check file permissions on data/ directory

---

Test 4: Test with an Endpoint

Create a test controller:

@RestController
@RequestMapping("/api/test")
public class TestController {

    @Autowired
    private UserRepository userRepository;

    @GetMapping("/db")
    public String testDatabase() {
        long count = userRepository.count();
        return "Database connected! User count: " + count;
    }
}

Test it:

curl http://localhost:8082/api/test/db

Success:

Database connected! User count: 0

Failure:

500 Internal Server Error
Could not open JPA EntityManager for transaction

---

Test 5: Verify Maven Dependencies

Check H2 is in your dependencies:

mvn dependency:tree | grep h2

Expected output:

[INFO] +- com.h2database:h2:jar:2.2.224:runtime

If missing:

<!-- Add to pom.xml -->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

---

Troubleshooting: No Tables Created

The Most Common Issue: Missing @Entity

Symptoms:

• ✅ App starts fine
• ✅ Can connect to H2 Console
• ❌ No tables in database
• ❌ No "create table" logs

Cause: Your entity classes are missing @Entity annotation or Hibernate can't find them.

Step 1: Find Your Entity Classes

find . -name "User.java" -type f

Expected location:

./src/main/java/com/ecommerce/app/entity/User.java

Step 2: Verify @Entity Annotation

Check all entity files:

grep -r "@Entity" src/main/java/com/ecommerce/app/entity/

Expected output:

src/main/java/com/ecommerce/app/entity/User.java:@Entity
src/main/java/com/ecommerce/app/entity/Product.java:@Entity

If no output: Your entities are missing @Entity!

Step 3: Fix Your Entity Class

❌ Wrong (Missing Annotations):

// User.java
package com.ecommerce.app.entity;

public class User {  // Missing @Entity!
    private Long id;
    private String username;
    private String email;

    // getters/setters
}

✅ Correct (With Annotations):

// User.java
package com.ecommerce.app.entity;

import jakarta.persistence.*;

@Entity  // ← Required!
@Table(name = "users")  // Optional but recommended
public class User {

    @Id  // ← Required for primary key
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String username;
    private String email;
    private String password;

    // Getters and setters
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }

    public String getUsername() { return username; }
    public void setUsername(String username) { this.username = username; }

    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }

    public String getPassword() { return password; }
    public void setPassword(String password) { this.password = password; }
}

Step 4: Restart the App

Stop the app:

./localResources/stop-app.sh
# or
pkill -f "spring-boot"

Start it again:

./localResources/start-app.sh
# or
mvn spring-boot:run

Watch the logs for:

Hibernate:
    create table users (
       id bigint generated by default as identity,
        email varchar(255),
        password varchar(255),
        username varchar(255),
        primary key (id)
    )

✅ Tables are now being created!

Step 5: Verify Tables in H2 Console

Go to: http://localhost:8082/h2-console

Run:

SHOW TABLES;

Expected result:

TABLE_NAME
----------
USERS      ✅

Check table structure:

DESCRIBE users;

Or:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'USERS';

Expected output:

COLUMN_NAME    | DATA_TYPE  | IS_NULLABLE
---------------|------------|------------
ID             | BIGINT     | NO
USERNAME       | VARCHAR    | YES
EMAIL          | VARCHAR    | YES
PASSWORD       | VARCHAR    | YES

---

Common Issues and Solutions

Issue 1: "Database may be already in use"

Error:

org.h2.jdbc.JdbcSQLNonTransientConnectionException:
Database may be already in use

Cause: Multiple app instances running

Solution:

# Find all Java processes
ps aux | grep java

# Kill them
pkill -9 -f "spring-boot"

# Restart
./localResources/start-app.sh

---

Issue 2: Tables Not Updating

Problem: You added a field to your entity, but the column isn't appearing in the database.

Check ddl-auto setting:

# application.properties
spring.jpa.hibernate.ddl-auto=update  # ✅ Should be "update"

If set to none or validate:

• none → Hibernate doesn't touch schema
• validate → Only checks if schema matches

Change to update:

spring.jpa.hibernate.ddl-auto=update

Restart app to apply changes.

---

Issue 3: Entity Not in Correct Package

Problem: Entity is in wrong package, Spring Boot can't find it.

Spring Boot scans packages under:

com.ecommerce.app.*  (if your @SpringBootApplication is in com.ecommerce.app)

Check your main application class:

package com.ecommerce.app;  // ← Base package

@SpringBootApplication
public class EcommerceApplication {
    public static void main(String[] args) {
        SpringApplication.run(EcommerceApplication.class, args);
    }
}

Entities must be in:

com.ecommerce.app.entity  ✅
com.ecommerce.app.model   ✅
com.ecommerce.entity      ❌ Outside base package!
entity                    ❌ No package!

Solution: Move entities to correct package:

src/main/java/com/ecommerce/app/entity/User.java

---

Issue 4: Wrong Import for @Entity

❌ Wrong (Old JPA):

import javax.persistence.Entity;  // Old Java EE

✅ Correct (Jakarta EE):

import jakarta.persistence.Entity;  // Spring Boot 3+

If using Spring Boot 3+, use jakarta.*

Check your Spring Boot version:

grep "spring-boot-starter-parent" pom.xml

Spring Boot Version	Import Package
2.x	javax.persistence.*
3.x	jakarta.persistence.*

---

Issue 5: Database File Permission Issues

Error:

java.io.IOException: Could not create file ./data/ecommerce_db.mv.db

Solution:

# Create data directory with proper permissions
mkdir -p data
chmod 755 data

# Or run from IDE/terminal with correct permissions

---

Quick Verification Checklist

✅ H2 Configuration Working

• Logs show HikariPool-1 - Start completed
• Logs show Hibernate: create table...
• Database file exists: ls data/ecommerce_db.mv.db
• H2 Console accessible at http://localhost:8082/h2-console
• Can connect with credentials: sa / blank password
• SHOW TABLES; returns table names
• Tables have correct columns

✅ Entity Classes Correct

• Located in correct package: com.ecommerce.app.entity
• Have @Entity annotation
• Have @Id on primary key field
• Have @GeneratedValue for auto-increment IDs
• Use correct import: jakarta.persistence.* (Spring Boot 3+)
• Have getters and setters

✅ Configuration Correct

• application.properties has database URL
• spring.jpa.hibernate.ddl-auto=update
• spring.h2.console.enabled=true
• H2 dependency in pom.xml

---

The Complete Working Setup

application.properties

# Database Configuration
spring.datasource.url=jdbc:h2:file:./data/ecommerce_db
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

# JPA/Hibernate Configuration
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

# H2 Console Configuration
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console

# Server Configuration
server.port=8082

pom.xml (Dependencies)

<dependencies>
    <!-- Spring Boot Starter Data JPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <!-- H2 Database -->
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>runtime</scope>
    </dependency>

    <!-- Spring Boot Starter Web -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
</dependencies>

Entity Class

package com.ecommerce.app.entity;

import jakarta.persistence.*;

@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true)
    private String username;

    @Column(nullable = false, unique = true)
    private String email;

    @Column(nullable = false)
    private String password;

    // Constructors
    public User() {}

    public User(String username, String email, String password) {
        this.username = username;
        this.email = email;
        this.password = password;
    }

    // Getters and Setters
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }

    public String getUsername() { return username; }
    public void setUsername(String username) { this.username = username; }

    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }

    public String getPassword() { return password; }
    public void setPassword(String password) { this.password = password; }
}

---

Testing Your Setup (Step by Step)

Step 1: Start the Application

mvn spring-boot:run

Step 2: Check Logs

Look for:

✅ HikariPool-1 - Start completed
✅ Hibernate: create table users (...)
✅ Tomcat started on port(s): 8082

Step 3: Access H2 Console

URL: http://localhost:8082/h2-console

Login:

• JDBC URL: jdbc:h2:file:./data/ecommerce_db
• User: sa
• Password: (blank)

Step 4: Verify Tables

SHOW TABLES;

Should see:

USERS

Step 5: Check Table Structure

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'USERS';

Step 6: Insert Test Data

INSERT INTO USERS (username, email, password)
VALUES ('john', 'john@email.com', 'password123');

SELECT * FROM USERS;

Should see:

ID | USERNAME | EMAIL           | PASSWORD
---|----------|-----------------|------------
1  | john     | john@email.com  | password123

✅ Everything working!

---

Summary

Quick Fix for "No Tables Created"

# 1. Check entity has @Entity
grep "@Entity" src/main/java/com/ecommerce/app/entity/User.java

# 2. If missing, add annotations
# (See "Fix Your Entity Class" section above)

# 3. Restart app
pkill -f spring-boot
mvn spring-boot:run

# 4. Check logs for "create table"
# Look for Hibernate SQL statements

# 5. Verify in H2 Console
# http://localhost:8082/h2-console
# Run: SHOW TABLES;

The Key Points

1. HikariPool started = Database connection works ✅
2. Can connect to H2 Console = Database accessible ✅
3. No tables = Missing @Entity annotations ❌
4. Fix: Add @Entity and @Id to your entity classes
5. Restart app to create tables

---

Remember: With ddl-auto=update, Hibernate automatically creates tables from your @Entity classes. No SQL scripts needed! Just make sure your entities are properly annotated. 🎯

Tags: #spring-boot #h2 #database #troubleshooting #hibernate #jpa