What is MyBatis?

MyBatis is a semi-ORM (Object-Relational Mapping) framework that encapsulates JDBC internally. When using MyBatis, developers only need to focus on SQL statements themselves, without spending effort on handling complex processes like loading drivers, creating connections, and creating statements. Programmers write native SQL directly, allowing precise control over SQL execution performance with high flexibility.

MyBatis can use XML or annotations to configure and map native information, converting POJOs (Plain Old Java Objects) into database records, avoiding almost all JDBC code and manual parameter setting and result set retrieval.

Through XML files or annotations, various statements to be executed are configured, and the final SQL statement is generated by mapping Java objects with dynamic parameters in statements. Finally, the MyBatis framework executes the SQL and maps the results to Java objects for return.

Summary: MyBatis is a persistence layer framework that allows custom SQL, stored procedures, and advanced mapping.

Advantages of MyBatis

1. Based on SQL statement programming, quite flexible, without affecting the application or existing database design. SQL is written in XML, decoupling SQL from program code, making it easy to manage uniformly. Provides XML tags supporting dynamic SQL statements and reusability.
2. Compared to JDBC, it reduces code by more than 50%, eliminates a large amount of redundant JDBC code, and doesn't require manual connection opening and closing.
3. Good compatibility with various databases (since MyBatis uses JDBC to connect to databases, it supports all databases that JDBC supports).
4. Can integrate well with Spring.
5. Provides mapping tags supporting ORM field relationship mapping between objects and databases; provides object relationship mapping tags supporting object relationship component maintenance.

Disadvantages of MyBatis

1. The workload of writing SQL statements is large, especially when there are many fields and associated tables, which requires certain skills from developers in writing SQL statements.
2. SQL statements depend on the database, resulting in poor database portability and making it difficult to switch databases arbitrarily.

When to Use MyBatis

1. MyBatis focuses on SQL itself and is a flexible enough DAO layer solution.
2. For projects with high performance requirements or frequently changing requirements, such as Internet projects, MyBatis will be a good choice.

Difference between #{} and ${}

When processing #{}, MyBatis replaces the #{} in SQL with ? and uses PreparedStatement's set methods to assign values.

When processing ${}, MyBatis simply replaces ${} with the variable value.

Using #{} can effectively prevent SQL injection and improve system security.

Handling Mismatched Entity Property Names and Table Column Names

Method 1: Define field aliases in SQL statements

<select id="selectOrder" parameterType="int" resultType="com.example.domain.Order">
    SELECT order_id AS id, order_no AS orderno, order_price AS price 
    FROM orders 
    WHERE order_id = #{id}
</select>

Method 2: Use resultMap to map field names and entity properties

<select id="getOrder" parameterType="int" resultMap="orderResultMap">
    SELECT * FROM orders WHERE order_id = #{id}
</select>

<resultMap type="com.example.domain.Order" id="orderResultMap">
    <!-- Use id attribute to map primary key field -->
    <id property="id" column="order_id">
    
    <!-- Use result attribute to map non-primary key fields -->
    <result property="orderno" column="order_no"/>
    <result property="price" column="order_price"/>
</resultMap>

Writing LIKE Statements for Fuzzy Queries

Method 1: Add SQL wildcards in Java code

String wildcardName = "%smi%";
List<Name> names = mapper.selectLike(wildcardName);

<select id="selectLike">
    SELECT * FROM foo WHERE bar LIKE #{value}
</select>

Method 2: Concatenate wildcards in SQL statements (may cause SQL injection)

String wildcardName = "smi";
List<Name> names = mapper.selectLike(wildcardName);

<select id="selectLike">
    SELECT * FROM foo WHERE bar LIKE "%"#{value}"%"
</select>

How DAO Interfaces Work

1. DAO interface is the Mapper interface

• The fully qualified name of the interface is the value of the namespace in the mapping file.
• The method name of the interface is the id value of the Mapper's Statement in the mapping file.
• The parameters in the interface method are the parameters passed to the SQL.

2. Mapper interface has no implementation class

When calling interface methods, the fully qualified name of the interface + method name is used as the key to uniquely locate a MapperStatement. In MyBatis, each , , , tag is parsed into a MapperStatement object.

Note: Methods in Mapper interfaces cannot be overloaded because they use the fully qualified name + method name strategy for saving and finding.

3. Mapper interface works through JDK dynamic proxy

During runtime, MyBatis uses JDK dynamic proxy to generate a proxy object for the Mapper interface. The proxy object intercepts interface methods, executes the SQL represented by the MapperStatement, and returns the SQL execution results.

MyBatis Pagination Principle

1. MyBatis uses the RowBounds object for pagination, or directly writes SQL to implement pagination, or uses MyBatis pagination plugins.
2. The principle of pagination plugins: implement interfaces provided by MyBatis, implement custom plugins, and intercept the SQL to be executed in the plugin's interception method, then rewrite the SQL.

Original SQL:
SELECT * FROM student

Intercepted and rewritten SQL:
SELECT * FROM (SELECT * FROM student) t LIMIT 0, 10

MyBatis Mapping Approaches

1. The first approach is to use tags to define the mapping relationship between database column names and object properties one by one.
2. The second approach is to use the alias function of SQL columns, writing the column alias as the object property name.

With the mapping relationship between column names and property names, MyBatis creates objects through reflection and uses reflection to assign values to object properties one by one and return them. Properties that cannot find a mapping relationship cannot be assigned values.

Requirements for Using MyBatis Mapper Interfaces

1. The method name of the Mapper interface must be the same as the id of each SQL defined in mapper.xml.
2. The input parameter type of the Mapper interface method must be the same as the parameterType type of each SQL defined in mapper.xml.
3. The output parameter type of the Mapper interface method must be the same as the resultType type of each SQL defined in mapper.xml.
4. The namespace in the Mapper.xml file is the class path of the Mapper interface.

Difference Between resultType and resultMap

1. When the class name matches the database, you can directly set the resultType parameter to the Pojo class.
2. If they are different, you need to set resultMap to convert result names and Pojo names.

Passing Multiple Parameters in Mappers

1. Pass parameters directly in the method, and use #{0} #{1} in the XML file to get them.
2. Use the @param ennotation: this way you can directly get values in the XML file through #{name}.

MyBatis Executor

1. SimpleExecutor: Every time an update or select is executed, a Statement object is opened and immediately closed after use.
2. ReuseExecutor: When executing update or select, it uses SQL as the key to find the Statement object. If it exists, it's used; if not, it's created. After use, the Statement object is not closed but placed in a Map.
3. BatchExecutor: Performs batch operations to improve performance.

Dynamic SQL in MyBatis

Dynamic SQL in MyBatis is usually implemented through nodes, using OGNL syntax. However, to write complete statements, it must be combined with , nodes.

• The node inserts "WHERE" if the contained nodes have content; otherwise, it doesn't insert.
• The node is used to determine if the dynamic statement starts with "AND" or "OR", and automatically removes this "AND" or "OR".

MyBatis provides 9 dynamic SQL tags: trim|where|set|foreach|if|choose|when|otherwise|bind.

The execution principle is to use OGNL to calculate the value of expressions from the SQL parameter object, and dynamically concatenate SQL based on the expression values to complete the dynamic SQL function.

MyBatis Association Queries and Implementation Methods

MyBatis can not only execute one-to-one and one-to-many association queries, but also many-to-one and many-to-many queries. Many-to-one queries are essentially one-to-one queries, which only require changing selectOne() to selectList(); many-to-many queries are essentially one-to-many queries, which only require changing selectOne() to selectList().

There are two implementation methods for association object queries:

1. Send a separate SQL to query the associated object, assign it to the main object, and return the main object.
2. Use nested queries, which means using join queries. Part of the columns are attribute values of object A, and the other part is attribute values of the associated object B. The advantage is that only one SQL query is needed to query both the main object and its associated objects.

Relationship Between XML Mapping Files and MyBatis Internal Data Structures

MyBatis encapsulates all XML configuration information into an All-In-One heavyweight Configuration object.

• In the XML mapping file, the tag is parsed into a ParameterMap object, and each of its sub-elements is parsed into a ParameterMapping object.
• The tag is parsed into a ResultMap object, and each of its sub-elements is parsed into a ResultMapping object.
• Each , , , tag is parsed into a MappedStatement object, and the SQL within the tag is parsed into a BoundSql object.

Interface Binding in MyBatis and Its Implementation Methods

Interface mapping in MyBatis means defining any interface in MyBatis and binding the methods in the interface to SQL statements. We can directly call interface methods, which gives us more flexible choices and settings than the methods provided by SqlSession.

There are two implementation methods for interface binding:

1. Binding through annotations: adding @Select, @Update and other annotations on interface methods, which contain SQL statements to bind.
2. Binding through SQL written in XML: in this case, the namespace in the XML mapping file must be the full path name of the interface. When SQL statements are simple, use annotation binding; when SQL statements are complex, use XML binding, which is generally more common.

MyBatis Caching

Purpose: Improve application query efficiency. Search order: if second-level cache is enabled, search from the second-level cache; if not enabled, search from the first-level cache (Map).

First-level Cache

• Characteristics: Same session (SqlSession), enabled by default, memory-based storage, data is cleared when the session is closed or when clear cache methods are explicitly called.

Second-level Cache

• Characteristics: Same namespace, process cache, memory + disk based, disabled by default, needs to be manually enabled. Classes used for second-level cache need to implement the Serializable interface (used to save object state).

Summary: The first-level cache is placed in the session, wich is available by default. The second-level cache is placed in its namespace, which is not opened by default. Classes used for the second-level cache need to implement the Serializable interface and can be configured in its mapping file.

MyBatis Lazy Loading

1. MyBatis only supports lazy loading for association associated objects and collection associated collection objects. Association refers to one-to-one, and collection refers to one-to-many queries. In the MyBatis configuration file, you can configure whether to enable lazy loading (lazyLoadingEnabled=true|false).
2. Its principle is to use CGLIB to create a proxy object for the target object. When calling the target method, it enters the interceptor method. For example, when calling a.getB().getName(), the interceptor invoke() method finds that a.getB() is null, so it sends a pre-saved SQL query to query the associated B object, queries B, then calls a.setB(b), so the b property of the a object has a value, and then completes the call to a.getB().getName(). This is the basic principle of lazy loading.

MyBatis Basic Workflow

1. Read configuration files, wich contain database connection information and Mapper mapping files or Mapper package paths.
2. With this information, SqlSessionFactory can be created. The lifecycle of SqlSessionFactory is program-level: it is established when the program runs and disappears when the program ends.
3. SqlSessionFactory establishes SqlSession for executing SQL statements. SqlSession is process-level: it is established in a method and should be closed when the method ends.
4. When users use methods configured in mapper.xml files, MyBatis first parses dynamic SQL tags into database SQL statement forms, encapsulates them into MapperStatement objects, then injects SQL into the database for execution through the executor, and returns results.
5. The returned results are mapped and packaged into Java objects.

Implementing One-to-Many Operations in MyBatis

There are joint queries and nested queries.

1. Joint Query

Several tables are queried together in one query, which can be completed by configuring the tag in the resultMap to configure the one-to-many class.

2. Nested Query

First query one table, then query another table based on the foreign key ID in the results of this table. This is also configured through , but the query for the other table is configured through the node.

Implementing One-to-One Operations in MyBatis

Joint Query

Several tables are queried together in one query, which can be completed by configuring the node in the resultMap to configure the one-to-one class.

Nested Query

First query one table, then query another table based on the foreign key ID in the results of this table. This is also configured through , but the query for the other table is configured through the select attribute.