Project address: Portal

In normal development, the field names in our tables and the attribute names of the corresponding entity classes are not necessarily identical. Let's show how to resolve the conflict between the field names and the entity class attribute names in this case.

I. Preparing tables and data for demonstration

CREATE TABLE orders(
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(20), 
    order_price FLOAT
);
INSERT INTO orders(order_no, order_price) VALUES('aaaa', 23);
INSERT INTO orders(order_no, order_price) VALUES('bbbb', 33);
INSERT INTO orders(order_no, order_price) VALUES('cccc', 22);

2. Defining Entity Classes

package com.shi.mybatis;

/**
 * @author AFinalStone
 * Define the entity class corresponding to the orders table
 */
public class Order {
    /**
     * 
    CREATE TABLE orders(
        order_id INT PRIMARY KEY AUTO_INCREMENT,
        order_no VARCHAR(20), 
        order_price FLOAT
    );
     */

    //The attribute names in the Order entity class are different from the field names in the orders table
    private int id;                //id===>order_id
    private String orderNo;        //orderNo===>order_no
    private float price;        //price===>order_price

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getOrderNo() {
        return orderNo;
    }

    public void setOrderNo(String orderNo) {
        this.orderNo = orderNo;
    }

    public float getPrice() {
        return price;
    }

    public void setPrice(float price) {
        this.price = price;
    }

    @Override
    public String toString() {
        return "Order [id=" + id + ", orderNo=" + orderNo + ", price=" + price+ "]";
    }
}

3. Writing test code

3.1. Writing xml Mapping file of SQL

• 1. Create an orderMapper.xml file. The contents of orderMapper.xml are as follows:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<! - Specify a unique namespace for this mapper, and the value of namespace is customarily set to the package name + sql mapping file name, so that the value of namespace is guaranteed to be unique.
For example, namespace="com.shi.mapping.orderMapper" is com. shi. mapping (package name) +orderMapper(orderMapper.xml file removes suffix)
 -->
<mapper namespace="com.shi.mapping.orderMapper">

    <!--
        According to the id query, we get an order object. Using this query, we can't find the desired result.
        This is mainly because the attribute names of entity classes do not correspond to the field names of databases, so the corresponding records can not be queried.
     -->
    <select id="getOrderById" parameterType="int"
            resultType="Order">
        select * from orders where order_id=#{id}
    </select>

    <!--
        According to the id query, we get an order object. Using this query, we can normally query the results we want.
        This is because we have aliased the field names of the query with the property names of the entity class, so that the property names of the entity class can correspond to the field names in the query results one by one.
     -->
    <select id="selectOrder" parameterType="int"
            resultType="Order">
        select order_id id, order_no orderNo,order_price price from orders where order_id=#{id}
    </select>

    <!--
    According to the id query, we get an order object. Using this query, we can normally query the results we want.
    This is because we map the one-to-one correspondence between attribute names of entity classes and field names of tables through <resultMap>.
    <select id="selectOrderResultMap" parameterType="int" resultMap="orderResultMap">
        select * from orders where order_id=#{id}
    </select>
    < resu lt Map > Maps the corresponding relationship between attribute names of entity classes and field names of tables.
    <resultMap type="Order" id="orderResultMap">
        <! -- Mapping primary key fields with id attributes - >
        <id property="id" column="order_id"/>
        <! -- Mapping non-primary key fields with resu lt attributes - >
        <result property="orderNo" column="order_no"/>
        <result property="price" column="order_price"/>
    </resultMap>

</mapper>

• 2. Register orderMapper.xml mapping file in conf.xml file

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <! - Refer to the db.properties configuration file - >.
    <properties resource="db.properties"/>

    <! - Don't put type Aliases in the head or tail, otherwise they will report an error - > Do not put type Aliases in the head or tail.
    <typeAliases>
        <!--     <typeAlias type="com.shi.mybatis.User" alias="UserModel"/>-->
        <package name="com.shi.mybatis"/>
    </typeAliases>

    <!--
        Development: development model
        work: work mode
     -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <! - Configure database connection information - >
            <dataSource type="POOLED">
                <! -- The value attribute value refers to the value configured in the db.properties configuration file - >.
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${name}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>


    <mappers>
        <! - Register the userMapper.xml file.
        userMapper.xml is located under the package com.shi.mapping, so resource is written as com/shi/mapping/orderMapper.xml.xml-->.
        <mapper resource="com/shi/mapping/orderMapper.xml"/>
    </mappers>


</configuration>

3.2. Writing unit test code

package com.shi.mybatis;


import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class TestOrder {

    @Test
    public void testGetOrderById(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        /**
         * Mapping sql's identifier string,
         * com.shi.mapping.orderMapper Is the value of the namespace attribute of the mapper tag in the orderMapper.xml file.
         * getOrderById Is the id attribute value of the select tag, through the id attribute value of the select tag, you can find the SQL to be executed.
         */
        String statement = "com.shi.mapping.orderMapper.getOrderById";//Identity String Mapping sql
        //Execute the query operation and automatically encapsulate the query results into an Order object to return
        Order order = sqlSession.selectOne(statement,1);//Query the record with id 1 in the orders table
        //You need to close SqlSession after executing SQL with SqlSession
        sqlSession.close();
        System.out.println(order);//Print result: null, that is, no corresponding record was queried.
    }

    @Test
    public void testGetOrderById2(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        /**
         * Mapping sql's identifier string,
         * com.shi.mapping.orderMapper Is the value of the namespace attribute of the mapper tag in the orderMapper.xml file.
         * selectOrder Is the id attribute value of the select tag, through the id attribute value of the select tag, you can find the SQL to be executed.
         */
        String statement = "com.shi.mapping.orderMapper.selectOrder";//Identity String Mapping sql
        //Execute the query operation and automatically encapsulate the query results into an Order object to return
        Order order = sqlSession.selectOne(statement,1);//Query the record with id 1 in the orders table
        //You need to close SqlSession after executing SQL with SqlSession
        sqlSession.close();
        System.out.println(order);//Print result: Order [id=1, orderNo=aaaa, price=23.0]
    }

    @Test
    public void testGetOrderById3(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        /**
         * Mapping sql's identifier string,
         * com.shi.mapping.orderMapper Is the value of the namespace attribute of the mapper tag in the orderMapper.xml file.
         * selectOrderResultMap Is the id attribute value of the select tag, through the id attribute value of the select tag, you can find the SQL to be executed.
         */
        String statement = "com.shi.mapping.orderMapper.selectOrderResultMap";//Identity String Mapping sql
        //Execute the query operation and automatically encapsulate the query results into an Order object to return
        Order order = sqlSession.selectOne(statement,1);//Query the record with id 1 in the orders table
        //You need to close SqlSession after executing SQL with SqlSession
        sqlSession.close();
        System.out.println(order);//Print result: Order [id=1, orderNo=aaaa, price=23.0]
    }
}

Results of unit testing:

1. The testGetOrderById method returns a null after executing the query.

2. TesGetOrderById2 method and testGetOrderById3 method can normally get the desired results after executing the query.

IV. SUMMARY

The test code above demonstrates that when the attribute names and the field names in the entity class are inconsistent, MyBatis is not able to query the corresponding results when querying, and two methods are adopted to solve the problem:

Solution 1: By defining aliases of field names in query sql statements, aliases of field names and attribute names of entity classes can be aligned, so that field names of tables and attribute names of entity classes can be matched one by one. This method solves the mapping relationship between field names and attribute names by defining aliases in sql statements.

Solution 2: Mapping the one-to-one correspondence between field names and entity class attribute names. This method uses the solution provided by MyBatis to solve the mapping relationship between field names and attribute names.