Mybatis query statement result set summary

Simple query -resultType

Data preparation

Table Student

<! -- Construction sentences: --> create table TEST.STUDENT ( SNO varchar(3) not null, SNAME varchar(4) not null, SSEX varchar(2) not null, SBIRTHDAY datetime null, CLASS varchar(5) null )Copy the code

<! Public class Student {private String SNO; private String SNAME; private String SSEX; private Date SBIRTHDAY; private String CLASS; <! - get andsetMethods -- - >... }Copy the code

example

According to the returned data types, there are roughly divided into basic data types, Javabeans and Maps. Although the number of returned result rows can be single or multiple, and the corresponding interface return type is collection or single object, the value of resultType is the same in the XML mapping file.

1. Specify field – underlying data type

Interface class:

<! --> String querySingleStudent(); <! List<String> queryAllStudent();Copy the code

Mapper file:

<! --> <select id="querySingleStudent" resultType="string"> SELECT SNAME FROM TEST.STUDENT LIMIT 1 </select> <! -- multiple result sets --> <select id="queryAllStudent" resultType="string">
    SELECT SNAME FROM TEST.STUDENT
</select>
Copy the code

2. Map, commonly known as hashMap

Interface class:

<! -- Single result set --> Map<String, Object> queryStudentMap(); <! -- Multiple result sets --> List<Map<String, Object>> queryAllStudentMap();Copy the code

Mapper file:

<! --> <select id="queryStudentMap" resultType="hashmap"> SELECT SNAME FROM TEST.STUDENT LIMIT 1 </select> <! -- multiple result sets --> <select id="queryAllStudentMap" resultType="hashmap">
    SELECT SNAME FROM TEST.STUDENT
</select>
Copy the code

Among them:

• Hashmap is short, or you can use the full name java.util. hashmap
• By default, setter methods for mapping objects are not added when the result set value is NULL (put for map objects). This behavior can be set in the mybatis-config.xml configuration file<setting name="callSettersOnNulls" value="true"/>Override the default Settings.

3. JavaBean

Interface class:

<! --> Student querySingleStudentBean(); <! --> List<Student> queryAllStudentBean();Copy the code

Mapper file:

<! --> <select id="queryStudentMap" resultType="student"> SELECT SNAME FROM TEST.STUDENT LIMIT 1 </select> <! -- multiple result sets --> <select id="queryAllStudentMap" resultType="student">
    SELECT SNAME FROM TEST.STUDENT
</select>
Copy the code

• ResultType =”student” specifies the student. Java alias, which can also be a fully qualified name. The alias is set in the mybatis-config.xml configuration file:

<typeAliases>
    <typeAlias type="com.bean.Student" alias="student"/>
    ...
</typeAliases>
Copy the code

However, if there are many JavaBean files and you don’t want to specify them individually, you can also set myBatis automatic scanning using the Package tag, alias is lowercase of the class name.

<typeAliases>
    <package name="Package name"/>
</typeAliases>
Copy the code

Complex query resultMap

For general queries, resultType is sufficient. For multi-table queries, for example, a resultMap is required.

Database field and Java data type mapping relationships

The database field type jdbcType and Java data types do not correspond one to one, and differ from database type to database type. Mybatis uses TypeHandler as a mapping between the two. This is fine in most cases, but not all cases can be covered. In special cases, you can customize the mapping using resultMap.

For example, the database LongVarchar field type corresponds to the Java String type. However, in DB2 databases, query fields of type LongVarchar are identified in Mybatis as jdbcType BLOB. There are two ways around this. The first is to CAST the field to a VARCHAR(length) type in SQL. The other is to use resultMap:

<resultMap id="resultMapDemo" type="" autoMapping="true">
    <result property="" column="" jdbcType="VARCHAR" />
</resultMap>

<select id="demoID" resultMap="resultMapDemo">... <select>Copy the code

1. <select>Tag using a resultMap to specify the return collection. Notice resultMap and resultType cannot be used together
2. <resultMap>The label
   • The ID and SELECT tags specify the mapping
   • Type is the fully qualified name or alias of the return type as resultType
   • AutoMapping, where the purpose is to modify only one field, automatically adopts the auto-complete mapping
3. <result>The label
   • Property is the Java variable name
   • Column is the database field name
   • JdbcType is specified here as VARCHAR

id

In mybatis documentation, it is pointed out that not using ID will cause performance degradation, so it is recommended to use ID tags for primary key fields. However, if there is no primary key, myBatis will take the name of the field you provided, even if autoMapping is used, when you provide only some fields in ResultMap instead of all fields. Therefore, when using resultMap, the optimal choice is:

1. If the table has a primary key, it is specified using the ID tag
2. If the table does not have a primary key, the mapping relationship between fields is not configured and the autoMapping attribute is used for automatic mapping. Or list all the fields without autoMapping.

Associated query of multiple tables

In the example of resultType, only one table is involved. What if multiple tables are involved in associated query? We can simply map all columns to the key values of the HashMap.

But HashMap is not a good domain model. Your program is more likely to use Javabeans or POJOs (Plain Old Java Objects) as the domain model.

Therefore, JavaBean is used as the domain model. Add a Score table

<! Create table SCORE (SNO varchar(3) not null, CNO varchar(5) not null, DEGREE decimal(10, 1) not null)Copy the code

<! Public class Score {private String SNO; private String CNO; private Double DEGREE; <! - get andsetMethods -- - >... }Copy the code

One-to-one relationship

The one-to-many relationship here is that two table fields correspond one to one, and a student’s grade in a particular course is uniquely determined. Labels are used in a resultMap in a one-to-one case.

Add field Score to student.java

<! --Student.java--> private Score score; public ScoregetScore() {
    return score;
}
public void setScore(Score score) {
    this.score = score;
}
Copy the code

There are two use cases. The first is a nested query, where fields from the result set of the previous SQL query are passed as parameters to the next SQL query. The second case is nested result sets, where two tables do associated queries that map result sets to multiple JavaBean files.

• Nested query

<resultMap id="allstudentResultMap" type="student"> <! -- Specify the second SELECT statement, and the field passed --> <association property="score" column="sno" select="queryScore"/> </resultMap> <! Select * from (select * from (select * from (select * from)))"queryAllStudent" resultMap="allstudentResultMap"> select SNO,SNAME from test.STUDENT </select> <! Select * from (select id= 0)"queryScore" resultType="score">
    select degree from test.SCORE
    where sno = #{sno}
</select>
Copy the code

In the label

1. Property refers to the new score field in Student.java.
2. Column specifies the field to be passed as a parameter to the next query SQL. Note that mybatis simply replaces #{parameter} with a placeholder for a single field. And then executeresultSet.getString(columnName)No parameter matching is performed, so any character in #{} can be used in the second SQL; If you need to pass multiple fields, usecolumn = " {prop1=col1,prop2=col2} ", in which case it is passed as a parameter object.
3. Select specifies the next SELECT statement

It is also important to note that this nested query is not friendly to large result sets and column names, and there is an N+1 problem, because the next SQL will run N times to loop the query, using an associative query is more appropriate. Also can open mybatis lazy query function, nested SQL is not executed sequentially, but in the use of time will execute the next SQL. For example, student.getScore().getsno () will execute queryScore SQL. This function is disabled by default. You need to set it in the configuration file

<! --mybatis-config.xml--> <setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
Copy the code

You can also set fetchType = “lazy” in the tag to enable lazy loading, which overrides the global parameter Settings.

• Nested result sets

For multi-table associated queries, aliases are generally used in SQL to avoid field name duplication. All Mybatis needs to do is map aliases to JavaBean properties correctly.

<! <resultMap ID ="associationDemoMap" type="student" autoMapping="true">
    <association property="score" javaType="score">
        <result property="SNO" column="SC_SNO"/>
    </association>
</resultMap>

<select id="queryStudentScore" resultMap="associationDemoMap">
    SELECT SNAME, SSEX, CLASS, ST.SNO, SC.SNO AS SC_SNO
    FROM test.student st INNER JOIN test.score sc 
    ON st.sno = sc.sno
    where CNO = ' '3-105.;
</select>
Copy the code

The mapping between table column names and attributes is specified by setting labels. However, if there are many columnPrefix fields, you need to specify them one by one. The label provides the columnPrefix attribute to specify the prefix of the alias, so that the resultMap can be reused

<resultMap id="associationDemoMap" type="student" autoMapping="true"> <! --columnPrefix Specifies the prefix of the alias --> <association property="score" resultMap="anotherMap" columnPrefix="SC_"/> </resultMap> <! --> <resultMap ID ="anotherMap" type="score" autoMapping="true">
</resultMap>
Copy the code

One-to-many relationship

In addition to the one-to-one relationship, there is also a one-to-many relationship. For example, here, one Student corresponds to scores of multiple courses. For one-to-many mapping, use labels in a resultMap. First you need to adjust the relationship between the two tables in the JavaBean file.

<! --Student.java--> private List<Score> score; public List<Score>getScore() {
    return score;
}
public void setScore(List<Score> score) {
    this.score = score;
}
Copy the code

Take nested result sets as an example

<resultMap id="collectionDemoMap" type="student" autoMapping="true"> <! --> <collection property="score" ofType="score">
        <result property="SNO" column="SC_SNO"/>
    </collection>
</resultMap>

<select id="queryStudentScore" resultMap="collectionDemoMap">
    SELECT SNAME,SSEX,CLASS,ST.SNO,SC.SNO AS SC_SNO
    FROM test.student st INNER JOIN test.score sc
    ON st.sno = sc.sno
</select>
Copy the code

Note that the association attribute ofType is used to represent the types in the List collection. The other attributes are used the same way as association.