首页 > 代码库 > 怎么编写mybatis.xml文件,实现sql增删改查
怎么编写mybatis.xml文件,实现sql增删改查
<?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.shangyu.mapper.dsz.CarInfoMapper">
<!-- 查询字段 -->
<sql id="Base_Column_List">
uid, supplier_id, license, driver_id, organization_id, car_model, car_type, frame_no, engine_no, car_price,
oil_card, first_regist_date, insurance_date, insurance_amount, check_date, remark, create_user, create_time, update_time, update_user,carowner,
device_no, attr1, attr2, attr3, attr4, attr5, delete_flag
</sql>
<!-- 查询条件 -->
<sql id="selectWhereClause">
<where>
<trim prefixOverrides="and">
<if test="keyword != null">
and (license like CONCAT(‘%‘,#{keyword},‘%‘)
OR car_model like CONCAT(‘%‘,#{keyword},‘%‘)
OR carowner like CONCAT(‘%‘,#{keyword},‘%‘)
OR frame_no like CONCAT(‘%‘,#{keyword},‘%‘)
OR engine_no like CONCAT(‘%‘,#{keyword},‘%‘)
OR oil_card like CONCAT(‘%‘,#{keyword},‘%‘)
OR device_no like CONCAT(‘%‘,#{keyword},‘%‘)
)
</if>
<if test="license != null">
and license like CONCAT(‘%‘,#{license},‘%‘)
</if>
<if test="organizationId != null">
and organization_id = #{organizationId}
</if>
<if test="carModel != null">
and car_model like CONCAT(‘%‘,#{carModel},‘%‘)
</if>
<if test="carowner != null">
and carowner like CONCAT(‘%‘,#{carowner},‘%‘)
</if>
<if test="carType != null">
and car_type = #{carType}
</if>
<if test="frameNo != null">
and frame_no like CONCAT(‘%‘,#{frameNo},‘%‘)
</if>
<if test="engineNo != null">
and engine_no like CONCAT(‘%‘,#{engineNo},‘%‘)
</if>
<if test="carPrice != null">
and car_price = #{carPrice}
</if>
<if test="oilCard != null">
and oil_card like CONCAT(‘%‘,#{oilCard},‘%‘)
</if>
<if test="firstRegistDate != null">
and first_regist_date = #{firstRegistDate}
</if>
<if test="insuranceDate != null">
and insurance_date = #{insuranceDate}
</if>
<if test="insuranceAmount != null">
and insurance_amount = #{insuranceAmount}
</if>
<if test="checkDate != null">
and check_date = #{checkDate}
</if>
<if test="remark != null">
and remark = #{remark}
</if>
<if test="createUser != null">
and create_user = #{createUser}
</if>
<if test="updateTime !=null">
and update_time = #{updateTime}
</if>
<if test="updateUser !=null">
and update_user = #{updateUser}
</if>
<if test="deviceNo !=null">
and device_no like CONCAT(‘%‘,#{deviceNo},‘%‘)
</if>
<if test="attr1 !=null">
and attr1 = #{attr1}
</if>
<if test="attr2 !=null">
and attr2 = #{attr2}
</if>
<if test="attr3 !=null">
and attr3 = #{attr3}
</if>
<if test="attr4 !=null">
and attr4 = #{attr4}
</if>
<if test="attr5 !=null">
and attr5 = #{attr5}
</if>
<if test="beginTime != null">
<![CDATA[ and check_date>=#{beginTime}]]>
</if>
<if test="endTime != null">
<![CDATA[ and check_date<=#{endTime}]]>
</if>
<if test="deleteFlag != null">
and delete_flag = #{deleteFlag}
</if>
<if test="driverUsers != null">
and uid in(select a.carinfo_id from tb_car_info_driver a where a.delete_flag=‘0‘ and a.driver_user=#{driverUsers})
</if>
</trim>
</where>
</sql>
<!-- 插入车辆数据 -->
<insert id="insert" parameterType="CarInfo">
insert into tb_car_info(
uid, supplier_id, license, driver_id, organization_id, car_model, car_type, frame_no, engine_no, car_price,oil_card, first_regist_date,
insurance_date, insurance_amount, check_date, remark, create_user, create_time,update_time, update_user,carowner,device_no,attr1,attr2,attr3,attr4,attr5,delete_flag
) values (
#{uid}, #{supplierId}, #{license}, #{driverId}, #{organizationId}, #{carModel}, #{carType}, #{frameNo}, #{engineNo}, #{carPrice},#{oilCard}, #{firstRegistDate},
#{insuranceDate}, #{insuranceAmount}, #{checkDate}, #{remark}, #{createUser}, #{createTime}, #{updateTime}, #{updateUser},#{carowner},#{deviceNo},#{attr1},#{attr2},
#{attr3},#{attr4},#{attr5},#{deleteFlag}
)
</insert>
<!-- 批量插入司机数据 -->
<insert id="insertUser">
insert into tb_car_info_driver (carinfo_id, driver_user, delete_flag) values
<foreach collection="driverUsers" item="item" index="index" separator="," >
(#{uid}, #{item}, #{deleteFlag})
</foreach>
</insert>
<!-- 单个删除车辆 -->
<delete id="deleteById" parameterType="CarInfo">
delete from tb_car_info where uid = #{uid}
</delete>
<!-- 单个删除司机 -->
<delete id="deleteDriverById" parameterType="java.lang.String" >
delete from tb_car_info_driver where delete_flag = ‘0‘ and carinfo_id = #{uid}
</delete>
<!-- 批量删除 -->
<delete id="deleteByIds" >
delete from tb_car_info where uid in
<foreach collection="array" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</delete>
<!-- 更新数据 -->
<update id="updateById" parameterType="CarInfo">
update tb_car_info
<set>
<if test="license != null">
license = #{license},
</if>
<if test="driverId != null">
driver_id = #{driverId},
</if>
<if test="organizationId != null">
organization_id =#{organizationId},
</if>
<if test="carModel != null">
car_model = #{carModel},
</if>
<if test="carType != null">
car_type = #{carType},
</if>
<if test="frameNo != null">
frame_no = #{frameNo},
</if>
<if test="engineNo != null">
engine_no = #{engineNo},
</if>
<if test="carPrice != null">
car_price = #{carPrice},
</if>
<if test="oilCard != null">
oil_card = #{oilCard},
</if>
<if test="firstRegistDate != null">
first_regist_date = #{firstRegistDate},
</if>
<if test="insuranceDate != null">
insurance_date = #{insuranceDate},
</if>
<if test="insuranceAmount != null">
insurance_amount = #{insuranceAmount},
</if>
<if test="checkDate != null">
check_date = #{checkDate},
</if>
<if test="remark != null">
remark = #{remark},
</if>
<if test="createUser != null">
create_user = #{createUser},
</if>
<if test="updateTime !=null">
update_time = #{updateTime},
</if>
<if test="updateUser !=null">
update_user = #{updateUser},
</if>
<if test="carowner !=null">
carowner = #{carowner},
</if>
<if test="deviceNo !=null">
device_no = #{deviceNo},
</if>
<if test="deleteFlag != null">
delete_flag = #{deleteFlag}
</if>
<if test="attr1 !=null">
attr1 = #{attr1},
</if>
<if test="attr2 !=null">
attr2 = #{attr2},
</if>
<if test="attr3 !=null">
attr3 = #{attr3},
</if>
<if test="attr4 !=null">
attr4 = #{attr4},
</if>
<if test="attr5 !=null">
attr5 = #{attr5}
</if>
</set>
where uid = #{uid}
</update>
<!-- 更新司机 -->
<update id="updateDriverById" parameterType="java.lang.String" >
update tb_car_info_driver set delete_flag = ‘1‘ where delete_flag = ‘0‘ and carinfo_id = #{uid}
</update>
<!-- 查询 -->
<select id="getById" resultType="CarInfo" parameterType="java.lang.String">
select
<include refid="Base_Column_List"/>
from tb_car_info
where uid=#{uid}
</select>
<!-- 查询司机 -->
<select id="getByDriverId" resultType="java.lang.String" parameterType="java.lang.String">
select driver_user from tb_car_info_driver where delete_flag=‘0‘ and carinfo_id=#{uid}
</select>
<!-- 按Map查询 -->
<select id="findByMap" resultType="CarInfo" parameterType="java.util.Map">
select
<include refid="Base_Column_List" />,
(SELECT CASE WHEN TIMESTAMPDIFF(MINUTE,create_time,NOW())>5 THEN 3 ELSE STATUS END FROM tb_gps_record WHERE device_no=ci.device_no ORDER BY create_time DESC LIMIT 1) AS STATUS
from tb_car_info ci
<include refid="selectWhereClause" />
<if test="orderByClause != null">
order by ${orderByClause}
</if>
<if test="offset != null and pageSize != null">
limit #{offset} , #{pageSize}
</if>
</select>
<select id="findByDriverNull" resultType="CarInfo" parameterType="java.util.Map">
SELECT <include refid="Base_Column_List" />
FROM tb_car_info b WHERE
b.uid NOT IN(
SELECT a.`carinfo_id` FROM tb_car_info_driver a where a.delete_flag=‘0‘)
</select>
<!-- 统计数量 -->
<select id="countByMap" resultType="java.lang.Integer"
parameterType="java.util.Map">
select count(*) from tb_car_info
<include refid="selectWhereClause" />
</select>
</mapper>
案例：mybatis.xml中sql编写规范
本文出自 “不凡人生——求知者” 博客，谢绝转载！
怎么编写mybatis.xml文件,实现sql增删改查