This article is the knowledge about entity class and table mapping problems in mybatis brought to you by the editor. Learning this tutorial can quickly help us solve the conflict problem of different field names and entity class attribute names. Friends who need it, let’s take a look!
1. Prepare the tables and data to be used 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. Define entity classes
package me.gacl.domain;/*** @author gacl* 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 name in the Order entity class and the field name in the orders table are different private int id; //id===>order_idprivate String orderNo; //orderNo===>order_noprivate float price; //price===>order_pricepublic 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;}@Overridepublic String toString() {return "Order [id=" + id + ", orderNo=" + orderNo + ", price=" + price+ "]";}}3. Write test code
3.1. Write an SQL xml mapping file
1. Create an orderMapper.xml file. The content of orderMapper.xml is 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. The value of the namespace is conventionally set to the package name + sql mapping file name, so that the value of the namespace can be guaranteed to be unique
For example, namespace="me.gacl.mapping.orderMapper" is me.gacl.mapping (package name) + orderMapper (orderMapper.xml file to remove the suffix)
--><mapper namespace="me.gacl.mapping.orderMapper"><!-- Get an order object based on the id query. Using this query cannot query the result we want. This is mainly because the attribute name of the entity class does not correspond to the field name of the database, so the corresponding record cannot be queried--><select id="getOrderById" parameterType="int" resultType="me.gacl.domain.Order">select * from orders where order_id=#{id}</select><!-- Get an order object based on the id query. Using this query, we can normally query the result we want. This is because we will give the query a alias with the same alias as the entity class attribute name, so that the attribute name of the entity class and the field name in the query result can correspond one by one--><select id="selectOrder" parameterType="int" resultType="me.gacl.domain.Order">select order_id id, order_no orderNo,order_price price from orders where order_id=#{id}</select><!-- Get an order object based on the id query. Using this query, we can query the result we want normally. This is because we map the one-to-one correspondence between the entity class attribute name and the field name of the table through <resultMap>--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- column="order_id"/><!-- Use result attribute to map non-primary key fields --><result property="orderNo" column="order_no"/><result property="price" column="order_price"/><result property="price" column="order_price"/><resultMap></mapper>2. Register the orderMapper.xml mapping file in the conf.xml file
<mappers> <!-- Register the orderMapper.xml file. The orderMapper.xml is located in the package me.gacl.mapping, so the resource is written as me/gacl/mapping/orderMapper.xml--><mapper resource="me/gacl/mapping/orderMapper.xml"/></mappers>
3.2. Write unit test code
package me.gacl.test;import me.gacl.domain.Order;import me.gacl.util.MyBatisUtil;import org.apache.ibatis.session.SqlSession;import org.junit.Test;public class Test2 {@Testpublic void testGetOrderById(){SqlSession sqlSession = MyBatisUtil.getSqlSession();/*** The identification string for mapping sql, * me.gacl.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 = "me.gacl.mapping.orderMapper.getOrderById";//Mapping the SQL identification string//Execute the query operation and automatically encapsulate the query result into an Order object and return the Order order = sqlSession.selectOne(statement,1);//Query records with id 1 in the orders table//After using SqlSession execute SQL, you need to close SqlSessionsqlSession.close(); System.out.println(order);//Print result: null, that is, no corresponding record was found}@Testpublic void testGetOrderById2(){SqlSession sqlSession = MyBatisUtil.getSqlSession();/*** Map SQL Identification String, * me.gacl.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 by using the id attribute value of the select tag*/String statement = "me.gacl.mapping.orderMapper.selectOrder";//Mapping the sql identity string//Execute the query operation and automatically encapsulate the query result into an Order object and return the Order order = sqlSession.selectOne(statement,1);//Query the record with id 1 in the orders table//After executing SQL using SqlSession, you need to close SqlSessionsqlSession.close(); System.out.println(order);//Print result: Order [id=1, orderNo=aaaa, price=23.0]}@Testpublic void testGetOrderById3(){SqlSession sqlSession = MyBatisUtil.getSqlSession();/*** Map SQL Identification String, * me.gacl.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, the SQL to be executed can be found*/String statement = "me.gacl.mapping.orderMapper.selectOrderResultMap";//Mapping the sql identity string//Execute query operation, and automatically encapsulate the query result into an Order object and return Order order = sqlSession.selectOne(statement,1);//Query the record with id 1 in the orders table//After using SqlSession to execute SQL, you need to close SqlSessionsqlSession.close(); System.out.println(order);//Print result: Order [id=1, orderNo=aaaa, price=23.0]}}Results of the execution of unit tests:
1. The testGetOrderById method returns a null after executing the query.
2. After the testGetOrderById2 method and testGetOrderById3 method execute the query, you can get the desired result normally.
4. Summary
The above test code demonstrates the problem that when the attribute name in the entity class and the field name in the table are inconsistent, the corresponding results cannot be queried when using MyBatis for query operations, and two methods are adopted for the problem:
Solution 1: Define the alias of the field name in the query SQL statement, so that the alias of the field name are consistent with the attribute name of the entity class, so that the field name of the table can correspond one by one to the attribute name of the entity class. This method solves the mapping relationship between the field name and the attribute name by defining alias in the SQL statement.
Solution 2: Map the one-to-one correspondence between field names and entity class attribute names by mapping. This method uses the solution provided by MyBatis to solve the mapping relationship between field names and attribute names.