Database

Overview

EduTec Backend uses Spring Data JPA with Hibernate as the ORM (Object-Relational Mapping) provider. The application supports multiple database systems, with MySQL 8.0.19 as the primary production database.

The flexible database architecture allows development with H2 (in-memory) while deploying to MySQL, Oracle, MariaDB, or SQL Server in production without code changes.

Supported Databases

The project includes JDBC drivers for multiple database systems:

MySQL
H2 (In-Memory)
MariaDB
Oracle
Microsoft SQL Server

Version: 8.0.19Status: Primary production database

<!-- pom.xml:78-82 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.19</version>
</dependency>

Connection URL Pattern:

spring.datasource.url=jdbc:mysql://localhost:3306/edutec
spring.datasource.username=root
spring.datasource.password=yourpassword
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

MySQL 8.0+ requires the cj package in the driver class name. The older com.mysql.jdbc.Driver is deprecated.

Scope: Runtime (development/testing)Use Case: Fast local development without external database setup

<!-- pom.xml:58-61 -->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

Configuration Example:

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driver-class-name=org.h2.Driver
spring.h2.console.enabled=true

Access H2 console at: http://localhost:8080/h2-console

Status: MySQL-compatible alternative

<!-- pom.xml:83-87 -->
<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <scope>runtime</scope>
</dependency>

Connection URL Pattern:

spring.datasource.url=jdbc:mariadb://localhost:3306/edutec
spring.datasource.driver-class-name=org.mariadb.jdbc.Driver

Version: ojdbc8 21.5.0.0Use Case: Enterprise deployments

<!-- pom.xml:68-72 -->
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>21.5.0.0</version>
</dependency>

Connection URL Pattern:

spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xe
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

Scope: Runtime

<!-- pom.xml:62-66 -->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <scope>runtime</scope>
</dependency>

Connection URL Pattern:

spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=edutec
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver

JPA Configuration

The application uses Spring Data JPA for database operations:

<!-- pom.xml:26-29 -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

This starter includes:

Spring Data JPA - Repository abstraction layer
Hibernate Core - JPA implementation
Spring Transaction Management - Declarative transaction support
JDBC Connection Pooling - HikariCP (default in Spring Boot 2.x)

Entity Model: User

The User entity represents the core authentication and user management table.

Entity Definition

// src/main/java/com/tecmilenio/edutec/model/User.java:1-25
package com.tecmilenio.edutec.model;

import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;

import javax.persistence.*;

@Entity
@Table(name = "usuarios")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {

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

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

    @Column(nullable = false)
    private String password;
}

Annotations Explained

@Entity

Purpose: Marks this class as a JPA entity that maps to a database tableEffect: Hibernate will manage instances of this class and persist them to the database

@Entity
public class User { }

@Table(name = 'usuarios')

Purpose: Specifies the database table nameDefault: Without this annotation, the table name would be user (class name in lowercase)Custom Name: Maps to the usuarios table in the database

@Table(name = "usuarios")

Using a Spanish table name (usuarios) indicates this may be for a Spanish-speaking user base or legacy database naming conventions.

Lombok Annotations

@Data - Generates:

Getters for all fields
Setters for all non-final fields
toString() method
equals() and hashCode() methods
Required arguments constructor

@NoArgsConstructor - Generates a no-argument constructor (required by JPA)@AllArgsConstructor - Generates a constructor with all fields as parameters

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User { }

These Lombok annotations dramatically reduce boilerplate code. Without them, you would need to manually write ~50 lines of getters, setters, and other methods.

@Id and @GeneratedValue

@Id - Marks this field as the primary key@GeneratedValue(strategy = GenerationType.IDENTITY):

Delegates primary key generation to the database
Uses AUTO_INCREMENT (MySQL/MariaDB) or IDENTITY columns (SQL Server)
Database generates the ID when inserting new records

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

Alternative Strategies:

AUTO - JPA provider chooses the strategy
SEQUENCE - Uses database sequences (Oracle, PostgreSQL)
TABLE - Uses a separate table to generate IDs
UUID - Generates UUIDs (requires custom generator)

@Column Constraints

Username Field:

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

nullable = false: NOT NULL constraint - username is required
unique = true: UNIQUE constraint - no duplicate usernames

Password Field:

@Column(nullable = false)
private String password;

nullable = false: Password is required

The password field stores plain text in the current implementation. Always hash passwords using BCrypt before storing them in production.

Generated Database Schema

Hibernate will generate the following SQL DDL:

CREATE TABLE usuarios (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL
);

CREATE INDEX idx_username ON usuarios(username);

Column Types:

id: BIGINT (maps from Java Long)
username: VARCHAR(255) (default String length)
password: VARCHAR(255)

Hibernate automatically creates an index on the username column due to the unique = true constraint, optimizing login queries.

Hibernate Configuration

The project includes several Hibernate extensions and utilities:

Hibernate Core Dependencies

Hibernate Agroal (5.4.30.Final)

Purpose: Connection pool integration

<!-- pom.xml:131-135 -->
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-agroal</artifactId>
    <version>5.4.30.Final</version>
    <type>pom</type>
</dependency>

Agroal is a modern connection pool that provides:

Fast connection acquisition
Leak detection
Connection validation
Metrics and monitoring

Hibernate Search (5.11.8.Final)

Purpose: Full-text search capabilities using Apache Lucene

<!-- pom.xml:137-141 -->
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-search-orm</artifactId>
    <version>5.11.8.Final</version>
</dependency>

Use Cases:

Search users by name or email
Autocomplete functionality
Advanced text queries (fuzzy matching, wildcards)

Example Usage:

@Entity
@Indexed
public class User {
    @Field(analyze = Analyze.YES)
    private String username;
}

Hibernate Validator (7.0.1.Final)

Purpose: Bean validation (JSR 380 implementation)

<!-- pom.xml:143-147 -->
<dependency>
    <groupId>org.hibernate.validator</groupId>
    <artifactId>hibernate-validator</artifactId>
    <version>7.0.1.Final</version>
</dependency>

Example Validations:

public class User {
    @NotBlank
    @Size(min = 3, max = 50)
    private String username;
    
    @Email
    private String email;
    
    @Pattern(regexp = "^(?=.*[0-9])(?=.*[a-z])(?=.*[A-Z]).{8,}$")
    private String password;
}

Hibernate Envers (5.5.0.Final)

Purpose: Entity auditing and versioning

<!-- pom.xml:149-153 -->
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-envers</artifactId>
    <version>5.5.0.Final</version>
</dependency>

Spring Data Integration:

<!-- pom.xml:155-159 -->
<dependency>
    <groupId>org.springframework.data</groupId>
    <artifactId>spring-data-envers</artifactId>
    <version>2.5.1</version>
</dependency>

Features:

Track all changes to entities
Query historical data
Audit trail compliance
Rollback capability

Enable Auditing:

@Entity
@Audited
public class User {
    // Envers will create a usuarios_AUD table automatically
}

Envers creates shadow audit tables (e.g., usuarios_AUD) that store historical versions of each entity, perfect for compliance requirements.

Database Configuration

Current configuration in application.properties:

# src/main/resources/application.properties:1
spring.application.name=edutec

Missing Database ConfigurationThe application.properties file only contains the application name. You must add database connection properties before running the application:

# Database Configuration
spring.datasource.url=jdbc:mysql://localhost:3306/edutec
spring.datasource.username=root
spring.datasource.password=yourpassword
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# JPA/Hibernate Properties
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect
spring.jpa.properties.hibernate.format_sql=true

# Connection Pool Settings
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000

Configuration Properties Explained

spring.jpa.hibernate.ddl-auto
spring.jpa.show-sql
Hibernate Dialect
HikariCP Settings

Controls automatic schema management:

none - No automatic schema operations
validate - Validate schema matches entities (safe for production)
update - Update schema to match entities (adds columns/tables, never drops)
create - Drop and recreate schema on startup
create-drop - Create on startup, drop on shutdown

Use validate or none in production with proper database migration tools like Flyway or Liquibase.

Purpose: Log SQL statements to console

spring.jpa.show-sql=true

Output Example:

Hibernate: select user0_.id, user0_.username, user0_.password 
           from usuarios user0_ 
           where user0_.username=?

Useful for development and debugging, but disable in production for performance.

Purpose: SQL dialect for your database vendorMySQL 8:

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

Other Dialects:

org.hibernate.dialect.MariaDBDialect
org.hibernate.dialect.Oracle12cDialect
org.hibernate.dialect.SQLServer2012Dialect
org.hibernate.dialect.H2Dialect

Hibernate usually auto-detects the dialect, but explicit configuration ensures correct SQL generation.

Purpose: Connection pool configuration (HikariCP is the default in Spring Boot 2.x)

spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000

Recommended Values:

maximum-pool-size: (CPU cores * 2) + effective_spindle_count
minimum-idle: 5-10 connections
connection-timeout: 30 seconds

Data Access Pattern

While not shown in the current codebase, typical Spring Data JPA usage would include:

Repository Interface

package com.tecmilenio.edutec.repository;

import com.tecmilenio.edutec.model.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.Optional;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    Optional<User> findByUsername(String username);
    boolean existsByUsername(String username);
}

Spring Data JPA automatically implements:

save(User user) - Insert or update
findById(Long id) - Find by primary key
findAll() - Get all users
deleteById(Long id) - Delete by primary key
count() - Count total users

Custom query methods:

findByUsername(String username) - Find user by username
existsByUsername(String username) - Check if username exists

Spring Data JPA derives the SQL query from the method name. No implementation code needed!

Service Layer Example

@Service
public class UserService {
    @Autowired
    private UserRepository userRepository;
    
    @Autowired
    private PasswordEncoder passwordEncoder;
    
    @Transactional
    public User createUser(String username, String rawPassword) {
        if (userRepository.existsByUsername(username)) {
            throw new DuplicateUserException("Username already exists");
        }
        
        User user = new User();
        user.setUsername(username);
        user.setPassword(passwordEncoder.encode(rawPassword));
        
        return userRepository.save(user);
    }
    
    public User findByUsername(String username) {
        return userRepository.findByUsername(username)
            .orElseThrow(() -> new UserNotFoundException("User not found"));
    }
}

Migration Strategy

Development Phase

For development, use Hibernate’s automatic schema generation:

spring.jpa.hibernate.ddl-auto=update

Production Deployment

For production, use database migration tools:

Flyway
Liquibase

Add to pom.xml:

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

Create migrations in src/main/resources/db/migration/:

-- V1__create_usuarios_table.sql
CREATE TABLE usuarios (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL
);

Configure:

spring.jpa.hibernate.ddl-auto=validate
spring.flyway.enabled=true

Add to pom.xml:

<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
</dependency>

Create changelog in src/main/resources/db/changelog/:

<!-- db-changelog-master.xml -->
<databaseChangeLog>
    <changeSet id="1" author="developer">
        <createTable tableName="usuarios">
            <column name="id" type="BIGINT" autoIncrement="true">
                <constraints primaryKey="true"/>
            </column>
            <column name="username" type="VARCHAR(255)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="password" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
        </createTable>
    </changeSet>
</databaseChangeLog>

Never use spring.jpa.hibernate.ddl-auto=update or create in production. Always use proper migration tools for schema changes.

Best Practices

Entity Design

Always use @Column(nullable = false) for required fields - Database constraints are the last line of defense

Add indexes for frequently queried columns:

@Table(name = "usuarios", indexes = {
    @Index(name = "idx_username", columnList = "username"),
    @Index(name = "idx_email", columnList = "email")
})

Use appropriate field types:
- Long for IDs (not Integer)
- LocalDateTime for timestamps (not Date)
- BigDecimal for currency (not double)

Add timestamps for auditing:

@CreatedDate
private LocalDateTime createdAt;

@LastModifiedDate
private LocalDateTime updatedAt;

Performance Optimization

Enable second-level cache:

spring.jpa.properties.hibernate.cache.use_second_level_cache=true
spring.jpa.properties.hibernate.cache.region.factory_class=org.hibernate.cache.jcache.JCacheRegionFactory

Use batch inserts:

spring.jpa.properties.hibernate.jdbc.batch_size=20
spring.jpa.properties.hibernate.order_inserts=true

Configure connection pool properly:
- Match pool size to your workload
- Monitor connection usage
- Set appropriate timeouts

Next Steps

Authentication - Integrate user authentication with database validation
Architecture - Understand how the repository layer fits into the overall system
API Reference - Explore endpoints that interact with the database

Get Started

Core Concepts

Development

Overview

Supported Databases

JPA Configuration

Entity Model: User

Entity Definition

Annotations Explained

Generated Database Schema

Hibernate Configuration

Hibernate Core Dependencies

Database Configuration

Configuration Properties Explained

Data Access Pattern

Repository Interface

Service Layer Example

Migration Strategy

Development Phase

Production Deployment

Best Practices

Entity Design

Performance Optimization

Next Steps

Build docs developers (and LLMs) love

Get Started

Core Concepts

Development

Documentation Index

​Overview

​Supported Databases

​JPA Configuration

​Entity Model: User

​Entity Definition

​Annotations Explained

​Generated Database Schema

​Hibernate Configuration

​Hibernate Core Dependencies

​Database Configuration

​Configuration Properties Explained

​Data Access Pattern

​Repository Interface

​Service Layer Example

​Migration Strategy

​Development Phase

​Production Deployment

​Best Practices

​Entity Design

​Performance Optimization

​Next Steps

Build docs developers (and LLMs) love

Overview

Supported Databases

JPA Configuration

Entity Model: User

Entity Definition

Annotations Explained

Generated Database Schema

Hibernate Configuration

Hibernate Core Dependencies

Database Configuration

Configuration Properties Explained

Data Access Pattern

Repository Interface

Service Layer Example

Migration Strategy

Development Phase

Production Deployment

Best Practices

Entity Design

Performance Optimization

Next Steps