1. mapper.xml文件中常见标签
除了常见的<select>、<insert>、<update>、<delete>标签以外,还有<resultMap>、<parameterMap>(已被弃用)、<sql>、<include>、<selectKey>,加上动态sql的9个标签,trim、where、set、foreach、if、choose、when、otherwise、bind等,其中<sql>为sql片段标签,通过<include>标签引入sql片段,<selectKey>为不支持自增的主键生成策略标签。
2023/09/22新增:写项目遇到的映射问题
sql标签查出来的字段,例如数据库中的verifyer_id字段,
使用AS设置一个别名project_verifyer_id
resultMap返回结果映射时,column的值就是你刚刚设置的别名project_verifyer_id
数据库查出来的city字段的值就会被property=“verify.verifyerId”
映射给自定义类型Verify 的verifyId属性
附上mapper.xml文件
<?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">
<mapper namespace="com.huashang.mapper.ProjectQryMapper">
<resultMap id="resultMap" type="com.huashang.domain.project.entity.Project">
<id property="id" column="projects_id"/>
<result property="serialNo" column="projects_serial_no"/>
<result property="name" column="projects_name"/>
<result property="phone" column="projects_phone"/>
<result property="province" column="projects_province"/>
<result property="city" column="projects_city"/>
<result property="address" column="projects_address"/>
<result property="idCardNumber" column="projects_id_card_number"/>
<result property="bankNumber" column="projects_bank_number"/>
<result property="bankName" column="projects_bank_name"/>
<result property="houseElectricNumber" column="projects_house_electric_number"/>
<result property="certificate" column="projects_certificate"/>
<result property="overlookImage" column="projects_overlook_image"/>
<result property="panoramicImage" column="projects_panoramic_image"/>
<result property="entranceImage" column="projects_entrance_image"/>
<result property="directionImage" column="projects_direction_image"/>
<result property="roofInfo" column="projects_roof_info"/>
<result property="delegation" column="projects_delegation"/>
<result property="idCardFile" column="projects_id_card_file"/>
<result property="bankCardFile" column="projects_bank_card_file"/>
<result property="verify.verifyerId" column="projects_verifyer_id"/>
<result property="verify.verifyerName" column="projects_verifyer_name"/>
<result property="verify.varifyAt" column="projects_varify_at"/>
<result property="verify.varifyResult" column="projects_varify_result"/>
<result property="verify.reason" column="projects_reason"/>
<result property="sign.signerId" column="projects_signer_id"/>
<result property="sign.signerName" column="projects_signer_name"/>
<result property="sign.signAt" column="projects_sign_at"/>
<result property="sign.signProcess" column="projects_sign_process"/>
<result property="sign.contractFile" column="projects_contract_file"/>
<result property="sign.signComplate" column="projects_sign_complate"/>
<result property="ratify.ratifyerId" column="projects_ratifyer_id"></result>
<result property="ratify.ratifyerName" column="projects_ratifyer_name"/>
<result property="ratify.ratifyAt" column="projects_ratify_at"/>
<result property="ratify.ratifyFile" column="projects_ratify_file"/>
<result property="epc.epcerId" column="projects_epcer_id"></result>
<result property="epc.epcerName" column="projects_epcer_name"/>
<result property="epc.epcAt" column="projects_epc_at"/>
<result property="epc.epcFile" column="projects_epc_file"/>
<result property="build.builderId" column="projects_builder_id"></result>
<result property="build.builderName" column="projects_builder_name"/>
<result property="build.buildAt" column="projects_build_at"/>
<result property="build.buildRemark" column="projects_build_remark"/>
<result property="merge.mergerId" column="projects_merger_id"></result>
<result property="merge.mergerName" column="projects_merger_name"/>
<result property="merge.mergeAt" column="projects_merge_at"/>
<result property="merge.elecMeter" column="projects_elec_meter"/>
<result property="merge.saleContract" column="projects_sale_contract"/>
<result property="confirm.confirmerId" column="projects_confirmer_id"></result>
<result property="confirm.confirmerName" column="projects_confirmer_name"/>
<result property="confirm.confirmAt" column="projects_confirm_at"/>
<result property="confirm.confirmRemark" column="projects_confirm_remark"/>
<result property="createAt" column="projects_create_at"/>
<result property="applyAt" column="projects_apply_at"/>
<result property="userId" column="projects_user_id"/>
<result property="userName" column="projects_user_name"/>
<result property="workflow" column="projects_workflow"/>
<result property="description" column="projects_description"/>
<association property="accessoryRequest" javaType="com.huashang.domain.project.entity.AccessoryRequest"/>
<association property="acceptance" javaType="com.huashang.domain.project.entity.Acceptance"/>
</resultMap>
<select id="find" resultMap="resultMap">
SELECT <include refid="projectsColumn"/>
FROM projects projects
WHERE projects.id = #{id}
</select>
<sql id="projectsColumn">
projects.verifyer_id AS projects_verifyer_id,
projects.id AS projects_id,
projects.serial_no AS projects_serial_no,
projects.name AS projects_name,
projects.phone AS projects_phone,
projects.province AS projects_province,
projects.city AS projects_city,
projects.address AS projects_address,
projects.id_card_number AS projects_id_card_number,
projects.bank_number AS projects_bank_number,
projects.bank_name AS projects_bank_name,
projects.house_electric_number AS projects_house_electric_number,
projects.certificate AS projects_certificate,
projects.overlook_image AS projects_overlook_image,
projects.panoramic_image AS projects_panoramic_image,
projects.entrance_image AS projects_entrance_image,
projects.direction_image AS projects_direction_image,
projects.roof_info AS projects_roof_info,
projects.delegation AS projects_delegation,
projects.id_card_file AS projects_id_card_file,
projects.bank_card_file AS projects_bank_card_file,
projects.verifyer_name AS projects_verifyer_name,
projects.varify_at AS projects_varify_at,
projects.varify_result AS projects_varify_result,
projects.reason AS projects_reason,
projects.signer_id AS projects_signer_id,
projects.signer_name AS projects_signer_name,
projects.sign_at AS projects_sign_at,
projects.sign_process AS projects_sign_process,
projects.contract_file AS projects_contract_file,
projects.sign_complate AS projects_sign_complate,
projects.ratifyer_id AS projects_ratifyer_id,
projects.ratifyer_name AS projects_ratifyer_name,
projects.ratify_at AS projects_ratify_at,
projects.ratify_file AS projects_ratify_file,
projects.epcer_id AS projects_epcer_id,
projects.epcer_name AS projects_epcer_name,
projects.epc_at AS projects_epc_at,
projects.epc_file AS projects_epc_file,
projects.builder_id AS projects_builder_id,
projects.builder_name AS projects_builder_name,
projects.build_at AS projects_build_at,
projects.build_remark AS projects_build_remark,
projects.merger_id AS projects_merger_id,
projects.merger_name AS projects_merger_name,
projects.merge_at AS projects_merge_at,
projects.elec_meter AS projects_elec_meter,
projects.sale_contract AS projects_sale_contract,
projects.confirmer_id AS projects_confirmer_id,
projects.confirmer_name AS projects_confirmer_name,
projects.confirm_at AS projects_confirm_at,
projects.confirm_remark AS projects_confirm_remark,
projects.create_at AS projects_create_at,
projects.apply_at AS projects_apply_at,
projects.user_id AS projects_user_id,
projects.user_name AS projects_user_name,
projects.workflow AS projects_workflow,
projects.description AS projects_description
</sql>
<resultMap id="resultPageMap" type="com.huashang.pageDto.ProjectPageDto">
<id property="id" column="id"/>
<result property="serialNo" column="projects_serial_no" jdbcType="VARCHAR"/>
<result property="name" column="projects_name"/>
<result property="phone" column="projects_phone"/>
<result property="province" column="projects_province"/>
<result property="city" column="projects_city"/>
<result property="address" column="projects_address"/>
<result property="idCardNumber" column="projects_id_card_number"/>
<result property="bankNumber" column="projects_bank_number"/>
<result property="bankName" column="projects_bank_name"/>
<result property="houseElectricNumber" column="projects_house_electric_number"/>
<result property="certificate" column="projects_certificate"/>
<result property="createAt" column="projects_create_at"/>
<result property="applyAt" column="projects_apply_at"/>
<result property="userId" column="projects_user_id"/>
<result property="userName" column="projects_user_name"/>
<result property="workflow" column="projects_workflow"/>
</resultMap>
<sql id="projectsPageColumn">
projects.id AS projects_id,
projects.serial_no AS projects_serial_no,
projects.name AS projects_name,
projects.phone AS projects_phone,
projects.province AS projects_province,
projects.city AS projects_city,
projects.address AS projects_address,
projects.id_card_number AS projects_id_card_number,
projects.bank_number AS projects_bank_number,
projects.bank_name AS projects_bank_name,
projects.house_electric_number AS projects_house_electric_number,
projects.certificate AS projects_certificate,
projects.create_at AS projects_create_at,
projects.apply_at AS projects_apply_at,
projects.user_id AS projects_user_id,
projects.user_name AS projects_user_name,
projects.workflow AS projects_workflow
</sql>
<select id="search" resultMap="resultPageMap">
SELECT <include refid="projectsPageColumn"/>
FROM projects projects
<where>
<if test="search.keyword != null and search.keyword != ''">
and (projects.name like concat('%', #{search.keyword}, '%')
or projects.phone like concat('%', #{search.keyword}, '%')
or projects.province like concat('%', #{search.keyword}, '%')
or projects.city like concat('%', #{search.keyword}, '%'))
</if>
<if test="search.selectedWorkflows != null">
and projects.workflow in
<foreach collection="search.selectedWorkflows" close=")" open="(" item="item" separator=",">
#{item}
</foreach>
</if>
<if test="search.selectedCities != null">
and projects.city in
<foreach collection="search.selectedCities" close=")" open="(" item="item" separator=",">
#{item}
</foreach>
</if>
<if test="search.selectedUsers != null">
and projects.user_id in
<foreach collection="search.selectedUsers" close=")" open="(" item="item" separator=",">
#{item}
</foreach>
</if>
</where>
</select>
<select id="find" resultMap="resultMap">
SELECT <include refid="projectsColumn"/>
FROM projects projects
WHERE projects.id = #{id}
</select>
<select id="fetchCities" resultType="String">
SELECT DISTINCT city FROM projects
</select>
</mapper>
1. 常见标签
- select
<select id="queryUserById" resultType="User">
select id,username,password,gender,regist_time
from t_user
where id = #{arg0}
</select>
<select id="queryUserByIdAndUsername" resultType="User">
select *from t_user where id = #{id} and username = #{username}
</select>
- insert和selectKey
<insert id="insertUser" parameterType="User">
insert into t_user(username,password,gender,birth)
values (username=#{username},password=#{password},gender=#{gender},birth=#{birth})
</insert>
<!-- 调用方法时参数User user不给id,但把自动增长的id返回给user-->
<insert id="insertUser" parameterType="User">
<selectKey order="AFTER" resultType="int" keyProperty="id">
select last_insert_id()
</selectKey>
insert into t_user(username,password,gender,birth)
values (username=#{username},password=#{password},gender=#{gender},birth=#{birth})
</insert>
<!--主键id是字符串类型,字符串就不能自动增长了,那怎么自动填写?
UUID生成字符串,由于UUID有“-”,我们replace替换掉-->
<insert id="insertUser" parameterType="User">
<selectKey order="BEFORE" resultType="String" keyProperty="id">
select replace(UUID(),"_","")
</selectKey>
insert into t_user(username,password,gender,birth)
values (username=#{username},password=#{password},gender=#{gender},birth=#{birth})
</insert>
- update
<update id="updateUser" parameterType="User">
update t_user
set username=#{username},password=#{password},gender=#{gender},birth=#{birth}
where id = #{id}
</update>
- delete
<delete id="deleteById" parameterType="int">
delet from t_user
where id = #{id}
</delete>
- resultMap
<resultMap id="rm" type="User">
<id column="id" property="id"></id>
<result column="password" property="password"></result>
<result column="username" property="username"></result>
<result column="gender" property="gender"></result>
<!--描述自定义属性类passengers:id nationality expire 和passport的映射规则-->
<association property="address" javaType="Address">
<id colum="aid" property="aid"></id>
<result column="address_info" property="address_info"></result>
</association>
<!--描述集合的映射-->
<collection property="order" ofType="Order">
<id colum="oid" property="oid"></id>
<result column="product" property="product"></result>
</collection>
</resultMap>
<select id="queryUser" resultMap="tm">
select u.id,u.username,u.password,u.gender,a.aid,a.address_info,o.oid,o.product
from t_user u join t_address a
on u.id = a.uid
join t_order o
on u.id = o.uid
where id=#{id}
</select>
- sql和include
<sql id="hhh">select *from t_user</sql>
<select id="queryUserByid" resultType="User">
<include refid="hhh"/>
where id=#{id}
</select>
2.动态sql的9个标签
- if
<sql id="hhh">select *from t_user</sql>
<select id="queryUser" resultType="User">
<include refid="hhh"/>
where
<if test="username!=null">
username=#{username}
</if>
<if test="password!=null">
password=#{password}
</if>
</select>
- if和where
<sql id="hhh">select *from t_user</sql>
<select id="queryUserByUnameAndPwd resultType="User">
<include refid="hhh"/>
<where>
<if test="username!=null">
and username = #{username}
</if>
<if test="password!=null">
and password = #{password}
</if>
</where>
</select>
- if和set
<update id="updateUser" parameterType="User">
update t_user
<set>
<if test="password!=null">
password = #{password},
</if>
<if test="username!=null">
username=#{username}
</if>
where id = #{id}
</update>
以上的逗号username因为是最后一个不加逗号
- trim
<select id="queryUserById2" resultType="User">
<include refid="user_fired"/>
<!--prefix=“where" 给拼接的sql语句加前缀,前缀为where,
prefixOverrides="or|and" where子句中如果以or或者and开头,会被覆盖-->
<trim prefix="where" prefixOverrides="or|and">
<if test="username!=null">
username = #{username}
</if>
<if test="gender!=null">
and gender = #{gender}
</if>
</trim>
</select>
<update id="updateUserById" parameterType="User">
update t_user
<!--prefix=“set" 给拼接的sql语句加前缀,前缀为set,
suffixOverrides=","自动将最后一个逗号删除-->
<trim prefix="set" suffixOverrides=",">
<if test="username!=null">
username = #{username},
</if>
<if test="gender!=null">
gender = #{gender},
</if>
</trim>
where id = #{id}
</update>
- foreach
<delete id="deleteUsersById" parameterType="java.util.List">
delete from t_user where id in
<foreach collection="list" open="(" close=")" separator="," item="i">
#{i}
</foreach>
</delete>
<insert id="insertUsers" parameterType="java.util.List">
insert into t_user values
<foreach collection="list" open="" close="" separator="," item="u">
(null,#{u.username},#{u.password},#{u.gender},#{u.registTime})
</foreach>
</insert>
上述collection的值为list,是传入的参数Map的key
如果你传入的参数是有多个类型
如下例题:
例题: 如果传来的参数是以下这种类型:
对应ProjectQryMapper.xml文件中的select语句如下<select id="search" resultMap="resultPageMap"> SELECT * FROM projects projects <where> <if test="search.keyword != null and search.keyword != ''"> and projects.name like concat('%', #{search.keyword}, '%') or projects.phone like concat('%', #{search.keyword}, '%') or projects.province like concat('%', #{search.keyword}, '%') or projects.city like concat('%', #{search.keyword}, '%') </if> <if test="search.selectedWorkflows != null"> and projects.workflow in <foreach collection="search.selectedWorkflows" close=")" open="(" item="item" separator=","> #{item} </foreach> </if> </where> </select>
以上例题要注意判断非空时,List<String>类型的数据不能和字符串比较
不能这么写
- choose和when和otherwise
<select id="findUser" resultType="User">
select *from t_user where id = #{id}
<choose>
<when test="username!=null">
AND username like #{username}
</when>
<when test="password!=null">
AND password = #{password}
</when>
<otherwise>
AND gender = 1
</otherwise>
</choose>
</select>
- bind
<?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">
<mapper namespace="com.how2java.pojo">
<!-- 本来的模糊查询方式 -->
<!-- <select id="listProduct" resultType="Product"> -->
<!-- select * from product_ where name like concat('%',#{0},'%') -->
<!-- </select> -->
<select id="listProduct" resultType="Product">
<bind name="likename" value="'%' + name + '%'" />
select * from product_ where name like #{likename}
</select>
</mapper>