Setting Up a Maven Project

Begin by creating a new Maven project in your IDE. Configure the project with the following details:

• GroupId: com.example.persistence
• ArtifactId: PersistenceDemo
• Version: 1.0-SNAPSHOT

Adding MySQL Dependencies

In your pom.xml file, include the MySQL connector dependency:

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.28</version>
    </dependency>
</dependencies>

Database Setup

Create a database named 'persistence_db' using your preferred database management tool:

CREATE DATABASE IF NOT EXISTS `persistence_db` 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

Creating a User Table

Define a table to store user information:

DROP TABLE IF EXISTS users;
CREATE TABLE users (
    user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) DEFAULT NULL,
    pass_hash VARCHAR(100) DEFAULT NULL,
    full_name VARCHAR(100) DEFAULT NULL,
    years INT DEFAULT NULL,
    gender TINYINT DEFAULT NULL,
    birth_date DATE DEFAULT NULL,
    creation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Inserting Sample Data

Populate the table with some test records:

INSERT INTO users (username, pass_hash, full_name, years, gender, birth_date) 
VALUES ('john_doe', 'secure_hash', 'John Doe', 28, 1, '1995-04-15');
INSERT INTO users (username, pass_hash, full_name, years, gender, birth_date) 
VALUES ('jane_smith', 'secure_hash', 'Jane Smith', 32, 0, '1991-07-22');

User Entity Class

Create a Java class to represent user data:

import java.util.Date;

public class User {
    private Integer userId;
    private String username;
    private String passHash;
    private String fullName;
    private Integer years;
    private Integer gender;
    private Date birthDate;
    private Date creationTime;
    private Date updateTime;

    // Getters and setters
    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getUsername() {
        return username;
    }

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

    public String getPassHash() {
        return passHash;
    }

    public void setPassHash(String passHash) {
        this.passHash = passHash;
    }

    public String getFullName() {
        return fullName;
    }

    public void setFullName(String fullName) {
        this.fullName = fullName;
    }

    public Integer getYears() {
        return years;
    }

    public void setYears(Integer years) {
        this.years = years;
    }

    public Integer getGender() {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }

    public Date getBirthDate() {
        return birthDate;
    }

    public void setBirthDate(Date birthDate) {
        this.birthDate = birthDate;
    }

    public Date getCreationTime() {
        return creationTime;
    }

    public void setCreationTime(Date creationTime) {
        this.creationTime = creationTime;
    }

    public Date getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }

    @Override
    public String toString() {
        return "User{" +
                "userId=" + userId +
                ", username='" + username + '\'' +
                ", fullName='" + fullName + '\'' +
                ", years=" + years +
                '}';
    }
}

JDBC Implementation Example

Create a class to demonstrate basic JDBC operations:

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JdbcExample {
    public static void main(String[] args) {
        Connection dbConnection = null;
        PreparedStatement queryStatement = null;
        ResultSet results = null;
        
        try {
            // Register JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            
            // Establish connection
            String connectionUrl = "jdbc:mysql://localhost:3306/persistence_db?useSSL=false";
            String dbUser = "admin";
            String dbPassword = "password";
            
            dbConnection = DriverManager.getConnection(connectionUrl, dbUser, dbPassword);
            
            // Prepare query with parameter
            String sqlQuery = "SELECT * FROM users WHERE years > ?";
            queryStatement = dbConnection.prepareStatement(sqlQuery);
            queryStatement.setInt(1, 25);
            
            // Execute query
            results = queryStatement.executeQuery();
            
            // Process results
            List<User> userList = new ArrayList<>();
            while (results.next()) {
                User user = new User();
                user.setUserId(results.getInt("user_id"));
                user.setUsername(results.getString("username"));
                user.setPassHash(results.getString("pass_hash"));
                user.setFullName(results.getString("full_name"));
                user.setYears(results.getInt("years"));
                user.setGender(results.getInt("gender"));
                user.setBirthDate(results.getDate("birth_date"));
                user.setCreationTime(results.getTimestamp("creation_time"));
                user.setUpdateTime(results.getTimestamp("update_time"));
                
                userList.add(user);
            }
            
            System.out.println(userList);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // Clean up resources
            try {
                if (results != null) results.close();
                if (queryStatement != null) queryStatement.close();
                if (dbConnection != null) dbConnection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Drawbacks of Pure JDBC

Excessive Boilerplate Code

JDBC requires developers to write repetitive code for every database operation. This includes loading drivers, establishing connections, creating statements, executing queries, processing result sets, and closing resources. Only a small portion of this code actually relates to the specific business logic, while the majority consists of standard database interaction patterns.

Maintenance Challenges

When SQL statements are embedded directly in Java code, they become difficult to maintain and debug. Any changes to the database schema require modifications to the Java code, which can lead to errors and increase development time. This tight coupling between the application logic and database structure makes the system less flexible.

Complex Dynamic SQL Handling

Building dynamic SQL queries with JDBC requires extensive string manipulation and conditional logic. For applications with complex filtering requirements, this can result in messy code that's hard to read and maintain. Developers must manually handle parameter binding and SQL injection prevension, which increases the risk of security vulnerabilities.

ORM Framework Comparison

Hibernate

Hibernate is a full-featured Object-Relational Mapping (ORM) framework that automates database interactions through entity objects. It abstracts away SQL entirely, allowing developers to work with Java objects while the framework handles the database communication.

Advantages:

• Eliminates most boilerplate JDBC code
• Database-agnostic code that works across different database systems
• Rapid development for standard CRUD operations
• Built-in caching and performance optimization features

Disadvantages:

• Limited control over generated SQL
• Steeper learning curve for advanced features
• Complex configurations for optimal performance
• Less efficient for complex queries with specific optimization needs

Spring JdbcTemplate

Spring's JdbcTemplate provides a middle ground between raw JDBC and full ORM solutions. It simplifies JDBC operations by handling resource management and basic exception translation while still allowing direct SQL control.

Advantages:

• Reduces boilerplate code compared to raw JDBC
• Maintains direct control over SQL statements
• Integrates seamlessly with other Spring Framework features
• Simple learning curve for developers familiar with JDBC

Disadvantages:

• Limited support for dynamic SQL construction
• Still requires manual result set mapping
• Less abstraction than full ORM solutions

MyBatis

MyBatis is a persistence framework that strikes a balance between JDBC and full ORM solutions. It maps SQL statements to Java methods while allowing developers to retain full control over the SQL.

Advantages:

• Complete control over SQL execution and optimization
• Powerful dynamic SQL capabilities through XML or annotations
• Simpler configuration than Hibernate
• Better performance for complex queries
• Clear separation between SQL and Java code

Disadvantages:

• Requires SQL knowledge
• More manual work than full ORM solutions
• Database-specific SQL may reduce portability

MyBatis is particularly popular in enterprise environments where SQL optimization is critical and development teams prefer to maintain explicit control over database interactions.