Skip to main content

​
Overview

The User Management System supports two database configurations:
  • H2 for development (in-memory)
  • MySQL for production (persistent)

​
Development Database (H2)

The development profile uses an in-memory H2 database that’s perfect for testing and development.

​
Configuration

application-dev.properties
# H2 Database Configuration
spring.datasource.url=jdbc:h2:mem:usermanagementdb
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

# JPA Configuration
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true

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

# SQL Initialization
spring.sql.init.mode=always
spring.jpa.defer-datasource-initialization=true

​
H2 Console

The H2 console is available at http://localhost:8080/h2-console when running in development mode.
Connection Settings:
FieldValue
JDBC URLjdbc:h2:mem:usermanagementdb
Usernamesa
Password(leave empty)
Driver Classorg.h2.Driver

​
Schema Creation

With spring.jpa.hibernate.ddl-auto=create-drop, the database schema is:
  • Created automatically on application startup
  • Dropped when the application shuts down
Never use create-drop in production! All data will be lost on application restart.

​
Production Database (MySQL)

The production profile uses MySQL for persistent data storage.

​
Configuration

application-prod.properties
# MySQL Database Configuration
spring.datasource.url=jdbc:mysql://localhost:3306/user_management
spring.datasource.username=root
spring.datasource.password=admin

# JPA Configuration
spring.jpa.hibernate.ddl-auto=update

​
Connection Settings

spring.datasource.url=jdbc:mysql://localhost:3306/user_management
spring.datasource.username=root
spring.datasource.password=admin

​
Schema Management

In production, spring.jpa.hibernate.ddl-auto=update is used:
  • Updates the schema automatically based on entity changes
  • Preserves existing data
  • Adds new columns and tables as needed
For production deployments, consider using database migration tools like Flyway or Liquibase for better control over schema changes.

​
Data Initialization

​
Development Data (data.sql)

The development profile automatically loads sample data from data.sql:
data.sql
-- Sample users for development environment
INSERT INTO users (username, email, password, role) VALUES
('admin-user', '[email protected]', '$2a$10$CuLvGxp8sG8l3z.29J8OMuq0mLp8M91UiMXe/6uahhetUQle5YYmy', 'ROLE_ADMIN'),
('normal-user', '[email protected]', '$2a$10$zAjODzgBZhCuL80b1OT51.jpYuOB8WmwqZ/u9ls4Xf7r0.7Vh9.jy', 'ROLE_USER');
Sample Credentials: 
{
  "username": "admin-user",
  "password": "admin123",
  "email": "[email protected]",
  "role": "ROLE_ADMIN"
}
The data.sql file only runs in development mode. Production databases should be initialized manually or through migration scripts.

​
Initialization Configuration

spring.sql.init.mode=always
spring.jpa.defer-datasource-initialization=true
  • spring.sql.init.mode=always - Executes data.sql on startup
  • spring.jpa.defer-datasource-initialization=true - Ensures schema is created before data insertion

​
MySQL Setup

​
Create Database

CREATE DATABASE user_management;
CREATE USER 'ums_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON user_management.* TO 'ums_user'@'localhost';
FLUSH PRIVILEGES;

​
Connection Pool Configuration

For production, configure connection pooling: 
# HikariCP Configuration
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=20000
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=1200000

​
Environment Variables

Override database settings using environment variables: 
export SPRING_DATASOURCE_URL=jdbc:h2:mem:testdb
export SPRING_DATASOURCE_USERNAME=sa
export SPRING_DATASOURCE_PASSWORD=

​
Troubleshooting

​
Common Issues

Check:
  1. MySQL service is running: sudo systemctl status mysql
  2. Database exists: SHOW DATABASES;
  3. User has permissions: SHOW GRANTS FOR 'username'@'localhost';
  4. Firewall allows port 3306
  5. Connection URL is correct
Verify:
  1. Development profile is active: spring.profiles.active=dev
  2. H2 console is enabled: spring.console.enabled=true
  3. Path is configured: spring.console.path=/h2-console
  4. Accessing correct URL: http://localhost:8080/h2-console
Check ddl-auto setting:
  • Development: create-drop recreates schema on restart
  • Production: update only adds new changes
  • Use validate to only validate schema without changes

​
Best Practices

  1. Never hardcode passwords - Use environment variables or secrets management
  2. Use connection pooling - Configure HikariCP for production
  3. Enable SSL - Use encrypted connections in production
  4. Regular backups - Implement automated backup strategy for MySQL
  5. Monitor connections - Track connection pool metrics
  6. Schema migrations - Use Flyway/Liquibase for production schema management

Build docs developers (and LLMs) love

Get started for freeTalk to us