From JDBC to MyBatis: Transforming Low‑Level SQL Calls into a Powerful Persistence Framework

1 Introduction

This article explains how JDBC evolves into MyBatis, emphasizing why JDBC should be encapsulated into a persistence framework and discussing areas where MyBatis can still improve.

2 JDBC Query Implementation

Typical JDBC query involves seven steps:

Load JDBC driver; Establish and obtain a database connection; Create JDBC Statements object; Set input parameters for the SQL statement; Execute the SQL statement and obtain the result set; Transform the result set into a desired format and return it; Release resources (close Connection, Statement, ResultSet).

Example implementation:

public static List<Map<String,Object>> queryForList(){
    Connection connection = null;
    ResultSet rs = null;
    PreparedStatement stmt = null;
    List<Map<String,Object>> resultList = new ArrayList<>();
    try{
        // Load JDBC driver
        Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
        String url = "jdbc:oracle:thin:@localhost:1521:ORACLEDB";
        String user = "trainer";
        String password = "trainer";
        // Get connection
        connection = DriverManager.getConnection(url,user,password);
        String sql = "select * from userinfo where user_id = ? ";
        // Create Statement
        stmt = connection.prepareStatement(sql);
        // Set parameter
        stmt.setString(1, "zhangsan");
        // Execute query
        rs = stmt.executeQuery();
        // Process result set
        ResultSetMetaData rsmd = rs.getMetaData();
        int num = rsmd.getColumnCount();
        while(rs.next()){
            Map<String,Object> map = new HashMap<>();
            for(int i=0;i<num;i++){
                String columnName = rsmd.getColumnName(i+1);
                map.put(columnName, rs.getString(columnName));
            }
            resultList.add(map);
        }
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        try{
            if(rs!=null){ rs.close(); rs=null; }
            if(stmt!=null){ stmt.close(); stmt=null; }
            if(connection!=null){ connection.close(); connection=null; }
        }catch(SQLException e){
            e.printStackTrace();
        }
    }
    return resultList;
}

3 Evolution from JDBC to MyBatis

From the seven JDBC steps, we can identify which steps are candidates for encapsulation to reduce boilerplate code.

3.1 Optimization 1: Connection Acquisition and Release

Frequent opening and closing of connections wastes resources and hurts performance.

Using a connection pool (e.g., DBCP or container‑provided JNDI pool) allows reuse of established connections, reducing the overhead of opening/closing.

By abstracting the DataSource, the concrete implementation (DBCP, JNDI, etc.) can be swapped via configuration.

3.2 Optimization 2: Centralized SQL Management

Embedding SQL directly in Java code leads to poor readability, difficult maintenance, and extra deployment steps.

Store SQL statements centrally in configuration files or a database using key‑value pairs, and retrieve them by key at runtime.

3.3 Optimization 3: Parameter Mapping and Dynamic SQL

When parameters are uncertain (e.g., variable‑length query conditions), static placeholders are insufficient.

Introduce a custom syntax (e.g., #variable# ) and a SQL parser to generate dynamic SQL based on provided parameters, using maps for key‑value bindings.

3.4 Optimization 4: Result Mapping and Caching

After executing a query, the ResultSet must be transformed before resources are released.

Support multiple return types (JavaBean, Map, List) and optionally cache query results using a composite key of SQL + parameters.

3.5 Optimization 5: Eliminating Duplicate SQL

When many SQL statements share fragments, changes become error‑prone.

Modularize reusable SQL snippets and reference them from multiple statements, so updates occur in a single place.

4 Areas for MyBatis Improvement

MyBatis requires explicit SQL for every operation, leading to a large amount of SQL code.

Potential improvement: generate CRUD SQL automatically for single‑table operations by reflecting on JavaBean properties and mapping them to table columns.

5 MyBatis Overall Design

5.1 Interface Layer – Database Interaction Methods

Traditional MyBatis API (provide Statement ID and parameters to SqlSession). Mapper interfaces (method names correspond to XML IDs).

5.1.1 Traditional API

Creates a SqlSession, passes Statement ID and parameters, and performs CRUD operations. This approach is simple but does not follow interface‑oriented programming.

5.1.2 Mapper Interface

Each mapper XML node is abstracted as a Java interface method; parameters and result mappings are defined via annotations or XML.

5.2 Data Processing Layer

The core of MyBatis performs two functions:

Build dynamic SQL from input parameters. Execute SQL and wrap the result set into a List.

5.2.1 Parameter Mapping and Dynamic SQL Generation

MyBatis uses OGNL to construct SQL dynamically based on parameter values.

5.2.2 Executing SQL and Wrapping Results

After dynamic SQL generation, MyBatis executes the statement and converts the ResultSet into List objects, supporting one‑to‑many and many‑to‑one mappings.

5.3 Framework Support Layer

Key responsibilities include transaction management, connection‑pool handling, caching, and flexible SQL configuration (XML or annotations).

5.4 Bootstrap Layer

Configures and starts MyBatis using either XML configuration files or Java API.

5.5 Core Components and Their Relationships

SqlSession : Top‑level API for database interaction. Executor : Core dispatcher that generates SQL, manages cache, and creates JDBC Statements. StatementHandler : Wraps JDBC Statement, sets parameters, and converts ResultSet to List. ParameterHandler : Maps Java parameters to JDBC placeholders. ResultSetHandler : Transforms ResultSet into Java objects. TypeHandler : Handles conversion between Java types and JDBC types. MappedStatement : Represents a single node. SqlSource : Generates BoundSql (final SQL string) from parameters. BoundSql : Holds the generated SQL and parameter mappings. Configuration : Holds all MyBatis configuration.

6 SqlSession Execution Process

SqlSession sqlSession = factory.openSession();
List<Employee> result = sqlSession.selectList("com.louis.mybatis.dao.EmployeesMapper.selectByMinSalary", params);

The process involves:

Creating a SqlSession object.

Calling selectList with a Statement ID and parameters.

Internally, SqlSession retrieves the corresponding MappedStatement from Configuration, delegates to Executor, which generates BoundSql, checks cache, creates a StatementHandler, prepares the JDBC Statement, sets parameters via ParameterHandler, executes the query, and finally uses ResultSetHandler to convert the ResultSet into a List.

public <E> List<E> selectList(String statement, Object parameter) {
    return this.selectList(statement, parameter, RowBounds.DEFAULT);
}

public <E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds) {
    try {
        MappedStatement ms = configuration.getMappedStatement(statement);
        List<E> result = executor.query(ms, wrapCollection(parameter), rowBounds, Executor.NO_RESULT_HANDLER);
        return result;
    } catch (Exception e) {
        throw ExceptionFactory.wrapException("Error querying database.  Cause: "+ e, e);
    } finally {
        ErrorContext.instance().reset();
    }
}

Executor creates a CacheKey, checks local cache, and if missing, calls queryFromDatabase which ultimately invokes StatementHandler.prepare, parameterize, and query to obtain the final List.

6.1 StatementHandler Details

StatementHandler prepares the JDBC Statement, sets parameters, and executes the query.

public List<E> query(Statement statement, ResultHandler resultHandler) throws SQLException {
    PreparedStatement ps = (PreparedStatement) statement;
    ps.execute();
    return resultSetHandler.<E>handleResultSets(ps);
}

ParameterHandler sets each placeholder value using the appropriate TypeHandler.

public void setParameters(PreparedStatement ps) throws SQLException {
    List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
    if (parameterMappings != null) {
        for (int i = 0; i < parameterMappings.size(); i++) {
            ParameterMapping pm = parameterMappings.get(i);
            Object value = ... // resolve from parameterObject or additional parameters
            TypeHandler typeHandler = pm.getTypeHandler();
            JdbcType jdbcType = pm.getJdbcType();
            if (value == null && jdbcType == null) {
                jdbcType = configuration.getJdbcTypeForNull();
            }
            typeHandler.setParameter(ps, i + 1, value, jdbcType);
        }
    }
}

7 MyBatis Initialization Mechanism

7.1 What Initialization Does

MyBatis loads all runtime configuration into a Configuration object, which mirrors the structure of the XML configuration file.

7.2 Building Configuration from XML

SqlSessionFactoryBuilder creates an XMLConfigBuilder, which parses the XML, constructs a Configuration, and then builds a DefaultSqlSessionFactory.

public SqlSessionFactory build(InputStream inputStream) {
    return build(inputStream, null, null);
}

public SqlSessionFactory build(InputStream inputStream, String environment, Properties properties) {
    try {
        XMLConfigBuilder parser = new XMLConfigBuilder(inputStream, environment, properties);
        Configuration config = parser.parse();
        return build(config);
    } catch (Exception e) {
        throw ExceptionFactory.wrapException("Error building SqlSession.", e);
    } finally {
        ErrorContext.instance().reset();
        try { inputStream.close(); } catch (IOException ignored) {}
    }
}

The parser processes child nodes such as properties, typeAliases, plugins, environments, mappers, etc., populating the Configuration object.

Environment Parsing Example

private void environmentsElement(XNode context) throws Exception {
    if (context != null) {
        if (environment == null) {
            environment = context.getStringAttribute("default");
        }
        for (XNode child : context.getChildren()) {
            String id = child.getStringAttribute("id");
            if (isSpecifiedEnvironment(id)) {
                TransactionFactory txFactory = transactionManagerElement(child.evalNode("transactionManager"));
                DataSourceFactory dsFactory = dataSourceElement(child.evalNode("dataSource"));
                DataSource dataSource = dsFactory.getDataSource();
                Environment.Builder envBuilder = new Environment.Builder(id)
                        .transactionFactory(txFactory)
                        .dataSource(dataSource);
                configuration.setEnvironment(envBuilder.build());
            }
        }
    }
}

The Environment class contains a static Builder to simplify construction.

7.3 Manual Configuration via Java API

Developers can instantiate XMLConfigBuilder directly, parse the XML to obtain a Configuration, and then build a SqlSessionFactory:

String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
XMLConfigBuilder parser = new XMLConfigBuilder(inputStream, null, null);
Configuration configuration = parser.parse();
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);
SqlSession sqlSession = sqlSessionFactory.openSession();
List<?> list = sqlSession.selectList("com.foo.bean.BlogMapper.queryAllBlogInfo");

7.4 Design Patterns Used

Builder Pattern

Used for constructing SqlSessionFactory (via SqlSessionFactoryBuilder) and Environment objects, allowing flexible parameter combinations.

Factory Pattern

SqlSessionFactory

itself is a factory that creates SqlSession instances.

Proxy Pattern

MyBatis generates mapper implementations at runtime using dynamic proxies, enabling interface‑based data access.