Create Project

Need MySQL connector dependency:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.29</version>
</dependency>

Create Data

CREATE TABLE `user_info` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Write Code

public class MyBatisTest {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://172.16.1.130:3306/wzk-mybatis?characterEncoding=utf-8", "hive", "hive@wzk.icu");
            String sql = "SELECT * FROM user_info WHERE username=?";
            statement = connection.prepareStatement(sql);
            statement.setString(1, "wzk");
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                int age = resultSet.getInt("age");
                System.out.println("id: " + id + ", username: " + username + ", age: " + age);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try { resultSet.close(); } catch (Exception e) { e.printStackTrace(); }
            }
            if (statement != null) {
                try { statement.close(); } catch (Exception e) { e.printStackTrace(); }
            }
            if (connection != null) {
                try { connection.close(); } catch (Exception e) { e.printStackTrace(); }
            }
        }
    }
}

Test Run

Output:

id: 1, username: wzk, age: 18
Process finished with exit code 0

Problem Summary

Traditional Java JDBC database development has the following disadvantages and problems:

Code Complexity and Redundancy

  • Manual connection management: Developers need to manually manage database connections, prone to connection leaks
  • Repeated code: Executing SQL queries requires writing a lot of similar code
  • Verbose exception handling: Each operation needs to handle SQLException

Lack of Flexibility

  • Hard-coded SQL: SQL statements directly embedded in Java code, causing tight coupling of business logic
  • Strong database dependency: SQL syntax differences between databases affect portability

Difficult Transaction Management

  • Developers need to manually manage transaction commit and rollback
  • JDBC cannot handle distributed transactions across multiple databases

Performance Optimization Difficulty

  • Lack of connection pool support
  • Cannot cache query results

Security Issues

  • Vulnerable to SQL injection attacks
  • Sensitive data leakage risk

Low Development Efficiency

  • Need to understand a lot of low-level details
  • Code is difficult to reuse and extend

Debugging and Maintenance Difficulties

  • Hard to locate errors
  • Weak log support

Solutions

  • Spring JDBC Template: Simplify JDBC development
  • ORM frameworks: Such as Hibernate, MyBatis
  • Connection pool technology: Such as HikariCP
  • Framework transaction management

Custom Framework Design

User Side

Core configuration files:

  • sqlMapConfig.xml: Stores database connection information, imports mapper.xml
  • mapper.xml: SQL statement configuration information

Framework Side

Read Configuration

  • Configuration: Stores database information, Map<identifier, Mapper>
  • MappedStatement: SQL statement, Statement type, input/output parameter Java types

Parse Configuration

Create SqlSessionFactoryBuilder class, use dom4j to parse configuration files

SqlSessionFactory

Method: openSession(), gets SqlSession interface implementation class instance

SqlSession Interface and Implementation Class

Encapsulates CRUD methods:

  • selectList - Query all
  • selectOne - Query single

Design Patterns

  • Builder design pattern
  • Factory pattern
  • Proxy pattern