On JDBC and MyBatis

1 JDBC

1.1 Case Code

package core;

import java.sql.*;

/**
 * JDBC Case class.
 *
 * @author Li Chengpeng
 */
public class JDBC {
    public static void main(String[] args) {
        // Define Connection Objects
        Connection connection = null;
        // Define precompiled statement objects
        PreparedStatement preparedStatement = null;
        // Define result set objects
        ResultSet resultSet = null;
        try {
            // Loading database driver
            Class.forName("com.mysql.jdbc.Driver");
            // Accessing database connections through database drivers
            connection = DriverManager.
                    getConnection("jdbc:mysql://localhost:3306/jdbc?characterEncoding=utf-8", "root", "root");
            // Define and initialize SQL statements
            String sql = "select * from user where id = ?";
            // Create precompiled statement objects
            preparedStatement = connection.prepareStatement(sql);
            // Setting Query Parameters
            preparedStatement.setString(1, "1");
            // Send the SQL to the database and execute the query to get the query result set.
            resultSet = preparedStatement.executeQuery();
            // Traversing the query result set
            while (resultSet.next()) {
                // Output query results
                System.out.println("id = " + resultSet.getString("id"));
                System.out.println("name = " + resultSet.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally { // Releasing resources
            if (resultSet != null) {
                try {
                    // Close the result set object
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (preparedStatement != null) {
                try {
                    // Close the precompiled statement object
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    // Close the connection object
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

1.2 Problem Analysis

The JDBC code above has the following problems:

• Frequent opening and closing of database connections seriously affect the performance of the database.
• Hard coding exists in the code. Whenever the requirement changes, the source code needs to be modified and recompiled, which makes the system difficult to maintain. Hard coding embodies:
  • Query conditions in SQL statements (String SQL = select * from user where id =?);). If the requirement changes to query according to the user's primary key and user name, then the source code needs to be modified.
  • The parameter position and value (preparedStatement.setString(1, "1"); when setting query parameters. If the SQL statement changes due to changes in requirements, the original assignment statement may be wrong and the source code needs to be modified.
  • The column name specified when retrieving the query results (resultSet.getString("name"); If the field name of the data table changes, the source code needs to be modified.
• The process of setting query parameters is tedious. Because in the process of setting query parameters, it is necessary to set different values for placeholders at different locations in query statements.
• The process of obtaining query results is tedious. Because in the process of obtaining query results, we need to traverse the query result set, and in the process of traversing, we need to get its value according to the column name of the data table.

2 MyBatis

MyBatis solves its programming problems by encapsulating JDBC. There are five main technical solutions:

• Database connection pool. We can configure the database connection pool in MyBatis's global configuration file SqlMapConfig.xml and use it to manage database connections, thus avoiding the waste of resources caused by frequent creation and release of database connections.
• SQL configuration file. MyBatis separates the SQL statement from the Java source file and places the SQL statement in the configuration file Mapper.xml. If there is a need to modify the SQL statement, then only the configuration file needs to be modified. The maintenance cost of modifying the configuration file is lower than that of modifying the Java source file, because restarting the system after modifying the configuration file can take effect, but if the modification is a Java source file, it needs five steps: modification, compilation, packaging, publication and restart.
• Dynamic SQL statements. MyBatis uses dynamic SQL technology to solve the problem of SQL hard coding in JDBC programming.
• Input mapping. MyBatis maps Java objects to SQL statements by input mapping technology. By using this Java object, the query parameter values can be extracted by the SQL statements, which simplifies the process of setting query parameters in JDBC.
• Output mapping. MyBatis maps the execution results of SQL to Java objects through output mapping technology, eliminating the process of retrieving result sets in JDBC programming.