When we use JDBC to access the database, in addition to writing our own SQL, we must also operate connection, statement and resultset, which are actually auxiliary classes. Not only that, accessing different tables, but also writing a lot of the same code, which is cumbersome and boring.
After using Mybatis, you only need to provide your own SQL statements. Other tasks, such as connection establishment, Statement, JDBC related exception handling, etc., are left to Mybatis. Those repetitive tasks are also done by Mybatis. We only need to focus on the operation level of addition, deletion, modification and query, and encapsulate the technical details in places we can't see.
Today, we mainly talk about table Association query. Mybatis uses association and collection tags to implement Association query
Mybatis is one-on-one, using the association tag
Mybatis one to many, using the collection tag
Let's take the User and Role tables as an example
<mapper namespace="XX.SysUserMapper">
<!--user User table mapping-->
<resultMap type="SysUser" id="SysUserResult">
<id property="userId" column="user_id" />
<result property="deptId" column="dept_id" />
<result property="userName" column="user_name" />
<result property="roleId" column="role_id" />
<result property="status" column="status" />
<association property="dept" column="dept_id" javaType="SysDept" resultMap="deptResult" />
<collection property="roles" javaType="java.util.List" resultMap="RoleResult" />
</resultMap>
<!--dept Department table mapping-->
<resultMap id="deptResult" type="SysDept">
<id property="deptId" column="dept_id" />
<result property="parentId" column="parent_id" />
<result property="deptName" column="dept_name" />
<result property="status" column="dept_status" />
</resultMap>
<!--role Permission table mapping-->
<resultMap id="RoleResult" type="SysRole">
<id property="roleId" column="role_id" />
<result property="roleName" column="role_name" />
<result property="status" column="role_status" />
</resultMap>
<sql id="selectUserVo">
select u.user_id, u.dept_id, u.user_name, u.nick_name,
d.dept_id, d.parent_id, d.dept_name, d.status as dept_status,
r.role_id, r.role_name, r.status as role_status
from sys_user u
left join sys_dept d on u.dept_id = d.dept_id
left join sys_role r on r.role_id = u.role_id
</sql>
<select id="selectUserList" parameterType="SysUser" resultMap="SysUserResult">
<if test="userName != null and userName != ''">
AND u.user_name like concat(concat('%',#{userName}),'%')
</if>
<if test="status != null and status != ''">
AND u.status = #{status}
</if>
<if test="deptId != null and deptId != 0">
AND (u.dept_id = #{deptId} OR u.dept_id IN ( SELECT t.dept_id FROM sys_dept t WHERE FIND_IN_SET(#{deptId}, ancestors) <![CDATA[ <> ]]> 0 ))
</if>
</select>
</mapper> Let's explain it here first
1, resultMap, id, type tag
type: corresponds to our entity class, full pathname
id: can be understood as alias
id: unique identifier (usually mapping primary key). This id value is used for the reference of the select element attribute
column: corresponds to the field name in our database table
Property: corresponds to the property of our entity class. For example, the property userName in user should correspond to the name in the database table user.
result: identify some simple properties, where column property represents the field name of the database, and property represents that the queried field name is mapped to a property of the entity class
<select id="selectUserList" parameterType="SysUser" resultMap="SysUserResult">
<if test="userName != null and userName != ''">
AND u.user_name like concat(concat('%',#{userName}),'%')
</if>
</select>2, parameterType: incoming parameter of MyBatis
1. Two kinds
(1) basic data type: int,string,long,Date
(2) complex data types: class and Map
2. Gets the value in the parameter
(1) basic data type: #{parameter} gets the value in the parameter
(2) complex data type: #{attribute name}, and #{key} in map
3. Instance
3.1 basic data type cases
<sql id="Base_Column_List" >
id, car_dept_name, car_maker_name, icon,car_maker_py,hot_type
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
select
<include refid="Base_Column_List" />
from common_car_make
where id = #{id,jdbcType=BIGINT}
</select>3.2 complex type -- map type
<select id="queryCarMakerList" resultMap="BaseResultMap" parameterType="java.util.Map">
select
<include refid="Base_Column_List" />
from common_car_make cm
where 1=1
<if test="id != null">
and cm.id = #{id,jdbcType=DECIMAL}
</if>
<if test="carDeptName != null">
and cm.car_dept_name = #{carDeptName,jdbcType=VARCHAR}
</if>
<if test="carMakerName != null">
and cm.car_maker_name = #{carMakerName,jdbcType=VARCHAR}
</if>
<if test="hotType != null" >
and cm.hot_type = #{hotType,jdbcType=BIGINT}
</if>
ORDER BY cm.id
</select>3.3 complex types -- class types
<update id="updateByPrimaryKeySelective" parameterType="com.epeit.api.model.CommonCarMake" >
update common_car_make
<set >
<if test="carDeptName != null" >
car_dept_name = #{carDeptName,jdbcType=VARCHAR},
</if>
<if test="carMakerName != null" >
car_maker_name = #{carMakerName,jdbcType=VARCHAR},
</if>
<if test="icon != null" >
icon = #{icon,jdbcType=VARCHAR},
</if>
<if test="carMakerPy != null" >
car_maker_py = #{carMakerPy,jdbcType=VARCHAR},
</if>
<if test="hotType != null" >
hot_type = #{hotType,jdbcType=BIGINT},
</if>
</set>
where id = #{id,jdbcType=BIGINT}
</update>3.4 complex type -- the case where the map contains an array
<select id="selectProOrderByOrderId" resultType="com.epeit.api.model.ProOrder" parameterType="java.util.HashMap" >
select sum(pro_order_num) proOrderNum,product_id productId,promotion_id promotionId
from pro_order
where 1=1
<if test="orderIds != null">
and
<foreach collection="orderIds" item="item" open="order_id IN(" separator="," close=")">
#{item,jdbcType=BIGINT}
</foreach>
</if>
GROUP BY product_id,promotion_id
</select>3, resultType and resultMap
resultType: directly represents the return type, including basic data type and complex data type.
resultMap: externally defined reference, which indicates the mapping result to by the corresponding external id. it is generally used when the field name and attribute name are inconsistent, or complex joint query is required to freely control the mapping result
4, association
Mapping to a complex "data type" attribute of a JavaBean only handles one-to-one association.
<!--user User table mapping--> <resultMap type="SysUser" id="SysUserResult"> <id property="userId" column="user_id" /> <result property="deptId" column="dept_id" /> <result property="userName" column="user_name" /> <result property="roleId" column="role_id" /> <result property="status" column="status" /> <association property="dept" column="dept_id" javaType="SysDept" resultMap="deptResult" /> <collection property="roles" javaType="java.util.List" resultMap="RoleResult" /> </resultMap>
Attribute node of association:
Property: the property name of the entity object that maps the database column
javaType: complete java class name and qualified name; The type of property mapped by property
Child element
id: it is generally a mapped primary key, which can improve performance.
result:
column: the field name of the mapped database.
Property: the entity object property corresponding to the mapped data column.
5, collection
It is mapped to a complex "data type" attribute of JavaBean. This attribute is a collection list that handles one to many association.
The rest are basically consistent with the association.
Both association and collection have the function of delayed loading.
Delayed loading: query from a single table first, and then query the associated table when necessary, which greatly improves the database performance, because relatively speaking, single table query is faster than multi table query