Mybatis returns results

Time:2021-12-29

Automatic mapping

Mybatis will get the column name returned in the result and find the attribute with the same name in the Java class (ignoring case). This means that if a column name in the database is foundIDColumns and in Java classesidProperty, mybatis willIDValue assigned toidProperties.

Generally, database columns are named with words composed of uppercase letters, and words are separated by underscores; Java attributes generally follow the hump naming convention. To enable automatic mapping between these two naming methods, you need tomapUnderscoreToCamelCaseSet to true.

<settings>
    <!-- Enable automatic hump naming rule mapping if the database field is user_ Email, mybatis will automatically map it to the useremail attribute in the Java Bean -- >
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

resultType

Set the return result type through resulttype. The basic data type is similar to POJO. Only POJO is demonstrated below.

Returns a single or multiple

  1. adoptusernameQuery individual users

The interface is defined as follows

public User getUserByUserName(String userName);

Mapper corresponding to the interface The XML definition is as follows

<select id="getUserByUserName"
        parameterType="String"
        resultType="com.example.mybatis.entity.User">
    select
    id, username, user_email userEmail, user_city userCity, age
    from user
    where username = #{username}
</select>
  1. adoptusernameQuery multiple users

The interface is defined as follows

public List<User> getUserListByUserName(String userName);

Mapper corresponding to the interface The XML definition is as follows

<select id="getUserListByUserName"
        parameterType="String"
        resultType="com.example.mybatis.entity.User">
    select
    id, username, user_email userEmail, user_city userCity, age
    from user
    where username = #{username}
</select>

Sometimes you need to encapsulate the returned list in a map and use a field of the entity class as the key of the map

The interface is defined as follows

@MapKey("id")
public Map<String, User> getUserListByUserName(String userName);

Mapper corresponding to the interface The XML definition is as follows

<select id="getUserListByUserName"
        parameterType="String"
        resultType="map">
    select
    id, username, user_email userEmail, user_city userCity, age
    from user
    where username = #{username}
</select>

resultMap

Sometimes the hump naming rules can’t map to the JavaBean properties we want, so we can use resultmap to realize high-level result set mapping.

For example, there is a user table with ID and user_ email、user_ There is one of the three fields of cityuserVOEntity classes have the following properties

public class UserVO {
    private Integer id;
    private String email;
    private String city;
    //Omit the get and set methods
}

Now you need to use mybatis to query the user information and map it touserVOEntity class

The interface is defined as follows

public UserVO getUserInfoById(Integer id);

Mapper corresponding to the interface The XML definition is as follows

<resultMap id="userVO" type="com.example.mybatis.entity.UserVO">
    <!--
        Specifies the encapsulation rule for the primary key class
        ID defines the primary key, and there will be some optimization at the bottom
        Column specifies the column of the data path
        Property specifies the corresponding JavaBean property
        -->
    <id column="id" property="id" />
    <!-- Defines the encapsulation of common columns -- >
    <result column="user_email" property="email" />
    <result column="user_city" property="city" />
</resultMap>
<select id="getUserInfoById" resultMap="userVO">
    select * from user where id = #{id}
</select>

Multi table Association query

In actual development, it is impossible to operate only on a single table, but it must involve multi table Association query. There are three kinds of relationships between data tables: one-to-one relationship, one to many relationship and many to many relationship.

one-on-one

A student corresponds to a class, and a class can correspond to multiple students.

CREATE TABLE `t_classes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `t_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(11) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cid` (`cid`),
  CONSTRAINT `student_ibfk` FOREIGN KEY (`cid`) REFERENCES `t_classes` (`id`)
);

Entity class

public class Classes {
    private Integer id;
    private String name;
    private List<Student> students;
}

public class Student {
    private Integer id;
    private String name;
    private Classes classes;
}

Query the student information through the student ID, and query the student class information together.

The interface is defined as follows

public Student getStudentById(Integer id);

Mapper corresponding to the interface The XML definition is as follows

<resultMap id="studentResultMap" type="Student">
    <id column="sid" property="id" />
    <result column="sname" property="name" />
    <!--  Mapping classes attribute -- >
    <association property="classes" javaType="com.example.mybatis.entity.Classes">
        <id column="cid" property="id" />
        <result column="cname" property="name" />
    </association>
</resultMap>
<select id="getStudentById" resultMap="studentResultMap">
    select s.id sid,s.name sname, c.id cid, c.name cname
    from t_student s , t_classes c
    where s.cid = c.id
    and s.id = #{id}
</select>

Modify the above query statement into step-by-step query and delayed query

<resultMap id="studentResultMap" type="com.example.mybatis.entity.Student">
    <id column="id" property="id" />
    <result column="name" property="name" />
    <!--
        Map classes property
        Property: mapped to a property in a JavaBean
        Column: Specifies the method to pass the value of a column to the select
        Select: the current attribute of the table name is the result of calling the method specified in select
                If you are calling a method in the current mapper, just fill in the ID of the method to be called
                If you are calling methods in other mappers, you need to fill in com xxx. mapper. XxxMapper. getClassesById
        Fetchtype: lazy - > lazy loading, eager - > all loading, 
        If lazy loading mode is set, the classes data will be loaded from the database only when the classes property is obtained
        Javatype: mapping entity class
        -->
    <association property="classes"
                 column="cid"
                 select="getClassesById"
                 javaType="com.example.mybatis.entity.Classes"
                 fetchType="lazy">
        <id column="id" property="id" />
        <result column="name" property="name" />
    </association>
</resultMap>
<select id="getStudentById" resultMap="studentResultMap">
    select s.id,s.name,s.cid
    from t_student s
    where s.id = #{id}
</select>
<select id="getClassesById" resultType="com.example.mybatis.entity.Classes">
    select c.id,c.name from t_classes c where c.id = #{id}
</select>

One to many

Now turn to query classes and query all students in the cascade together.

The interface is defined as follows

public Classes getClassesById(Integer id);

Mapper corresponding to the interface The XML definition is as follows

<resultMap id="classesResultMap" type="com.example.mybatis.entity.Classes">
    <id column="cid" property="id" />
    <result column="cname" property="name" />
    <collection property="students" ofType="com.example.mybatis.entity.Student">
        <id column="sid" property="id" />
        <result column="sname" property="name"/>
    </collection>
</resultMap>
<select id="getClassesById" resultMap="classesResultMap">
    select s.id sid,s.name sname, c.id cid, c.name cname
    from t_student s , t_classes c
    where s.cid = c.id
    and c.id = #{id}
</select>

It should be noted that the association tag maps the entity class by setting the javatype attribute, and the collection tag maps the entity class by setting the oftype attribute.

Modify the above query into step-by-step query and delayed query

<resultMap id="classesResultMap" type="com.example.mybatis.entity.Classes">
    <id column="cid" property="id" />
    <result column="cname" property="name" />
    <!--
            The column here only passes one field. If you want to pass in multiple fields, you can write this
            column="{key1=column1, key2=column2}"
            It is equivalent to encapsulating into a map. The current query can be written as column = "{id = CID}"
        -->
    <collection
                fetchType="lazy"
                property="students"
                select="getStudentByClassesId"
                column="cid">
    </collection>
</resultMap>
<select id="getClassesById" resultMap="classesResultMap">
    select c.id cid, c.name cname
    from t_classes c
    where c.id = #{id}
</select>
<select id="getStudentByClassesId" resultType="com.example.mybatis.entity.Student">
    select s.id , s.name from t_student s where s.cid = #{id}
</select>

Many to many

Many to many is actually a two-way one to many relationship, so both sides use the collection tag to set the cascade, which is similar to the one to many above.

This work adoptsCC agreement, reprint must indicate the author and the link to this article