Database

Database configuration, JPA entities, and data persistence in EduTec Backend

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

Version: 8.0.19

Status: 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.

H2 (In-Memory)

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

MariaDB

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

Oracle

Version: ojdbc8 21.5.0.0

Use 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

Microsoft SQL Server

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 table

Effect: 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 name

Default: 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 Configuration

The 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

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.

spring.jpa.show-sql

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.

Hibernate Dialect

Purpose: SQL dialect for your database vendor

MySQL 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.

HikariCP Settings

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

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

Liquibase

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

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

2. Add indexes for frequently queried columns:

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

3. Use appropriate field types:

   • Long for IDs (not Integer)
   • LocalDateTime for timestamps (not Date)
   • BigDecimal for currency (not double)

4. Add timestamps for auditing:

   @CreatedDate
   private LocalDateTime createdAt;
   
   @LastModifiedDate
   private LocalDateTime updatedAt;
   

Performance Optimization

1. 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
   

2. Use batch inserts:

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

3. 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