怎么编写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编写规范


本文转自 沉淀人生 51CTO博客,原文链接:http://blog.51cto.com/825272560/1882014

相关文章
|
11天前
|
SQL Java 数据库连接
mybatis动态SQL常用语法总结
MyBatis 使用 OGNL 表达式语言处理动态SQL,如 `if` 标签进行条件判断,`choose`、`when`、`otherwise` 实现多条件选择,`where`、`set` 管理SQL关键字,`trim` 提供通用修剪功能,`foreach` 遍历集合数据。`sql` 和 `include` 用于代码重用,`selectKey` 处理插入后的返回值。参数传递支持匿名、具名、列表、Map、Java Bean和JSON方式。注意SQL转义及使用合适的jdbcType映射Java类型。
30 7
|
25天前
|
SQL Java 数据库连接
深入探索MyBatis Dynamic SQL:发展、原理与应用
深入探索MyBatis Dynamic SQL:发展、原理与应用
|
27天前
|
SQL XML 数据库
后端数据库开发高级之通过在xml文件中映射实现动态SQL
后端数据库开发高级之通过在xml文件中映射实现动态SQL
24 3
|
27天前
|
SQL XML Java
后端数据库开发JDBC编程Mybatis之用基于XML文件的方式映射SQL语句实操
后端数据库开发JDBC编程Mybatis之用基于XML文件的方式映射SQL语句实操
33 3
|
27天前
|
SQL Java 数据库连接
2万字实操案例之在Springboot框架下基于注解用Mybatis开发实现基础操作MySQL之预编译SQL主键返回增删改查
2万字实操案例之在Springboot框架下基于注解用Mybatis开发实现基础操作MySQL之预编译SQL主键返回增删改查
28 2
MybatisPlus介绍新增用户,根据id查询,引入MybatisPlus的起步依赖,增删改查最简单的写法
MybatisPlus介绍新增用户,根据id查询,引入MybatisPlus的起步依赖,增删改查最简单的写法
|
23天前
|
SQL 缓存 Java
Java框架之MyBatis 07-动态SQL-缓存机制-逆向工程-分页插件
Java框架之MyBatis 07-动态SQL-缓存机制-逆向工程-分页插件
|
23天前
|
SQL Java 数据库连接
MyBatis动态SQL
MyBatis动态SQL
25 0
|
25天前
|
SQL 关系型数据库 MySQL
PolarDB产品使用问题之如何将指定的备份SQL文件导入到集群中
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
26天前
|
SQL Java 数据库连接
Mybatis日志SQL解析
Mybatis日志SQL解析
18 0