SQL：mapper中常用方法

MyBatis Mapper配置详解

最新推荐文章于 2024-08-20 09:37:33 发布

原创最新推荐文章于 2024-08-20 09:37:33 发布 · 1.6k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mapper

JAVA 同时被 2 个专栏收录

27 篇文章

订阅专栏

数据库

14 篇文章

订阅专栏

本文详细解析了MyBatis框架中Mapper配置文件的使用方法，包括实体类映射、SQL语句配置、参数传递及结果映射等关键内容。通过具体示例，介绍了分页查询、单个对象查询、全表查询、数据插入、删除、更新操作的实现方式，以及统计查询的配置。适合初学者和开发者深入理解MyBatis的数据持久层技术。

resultMap 中定义实体类的名称

方法名	方法	参数	返回值
分页查询	findByPage	实体类对象	List
根据ID查询	findById	实体类对象	实体类对象
查询全部	find	实体类对象	List
插入	insert	Cart	-
删除	delete	实体类对象	-
更新	update	实体类对象	-
更新全部	updateAll	-	-
统计	count	实体类对象	int

<?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.jy.repository.order.CartDao">

    <resultMap id="base" type="Cart">
        <id column="id" jdbcType="INTEGER" property="id"/>
    </resultMap>

    <!-- 分页查询List -->
    <select id="findByPage" resultMap="base" parameterType="Cart">
        SELECT s.*  FROM
        sc_cart s
        where 1=1
        and s.userId = #{param.userId}
        and s.rentType = #{param.rentType}
        <if test="param.selected == 1">
            and s.selected = #{param.selected}
        </if>
        ORDER BY id desc
    </select>

    <!-- 查询单个对象 -->
    <select id="findById" resultMap="base" parameterType="Cart">
        SELECT s.* FROM
        sc_cart s
        where  s.id = #{id}
    </select>

    <select id="find" resultMap="base"  parameterType="Cart">
        SELECT s.* FROM
        sc_cart s
        WHERE  1 = 1
        and s.goodsId = #{goodsId}
        and s.userId = #{userId}
    </select>
    <select id="count" resultType="int" parameterType="Cart">
        SELECT count(*) FROM sc_cart s where s.userId = #{userId}
    </select>
    <insert id="insert"  parameterType="Cart">
        INSERT INTO sc_cart(
        goodsId,
        num,
        money,
        userId,
        goodsName,
        goodsImg,
        state,
        createTime,
        firstWeekPrice,
        subsequentPrice,
        rentType
        ) VALUES (
        #{goodsId},
        #{num},
        #{money},
        #{userId},
        #{goodsName},
        #{goodsImg},
        #{state},
        now(),
        #{firstWeekPrice},
        #{subsequentPrice},
         #{rentType}
        )
    </insert>
    <delete id="delete" parameterType="Cart">
        delete from sc_cart where 1=1
        <if test='id!="" and id != null'>
        and id = #{id} and userId=#{userId}
         </if>
        <if test='goodsId !="" and goodsId != null'>
            and goodsId = #{goodsId}  AND userId=#{userId}
        </if>

    </delete>
    <update id="update">
        UPDATE sc_cart s set s.selected =  #{selected} where s.id = #{id}
    </update>
    <update id="updateAll">
         UPDATE sc_cart s set s.selected =  #{selected}   WHERE s.userId = #{userId}
    </update>
    <select flushCache="true" id="select_ids" parameterType="Map" resultType="Cart">
        SELECT s.*, c.attchentUrl img,a.goodsType goodsType,a.goodsSubType goodsSubType, b.userId agencyUserId,b.account agencyAccount,b.agencyName  FROM
        sc_cart s
        LEFT JOIN cxy_attachment c on s.goodsId = c.goodsId  and  c.goodType=1 and attchentName='small1'
        LEFT JOIN sc_goods a on s.goodsId=a.id
        LEFT JOIN sc_service_info b on a.userId=b.userId
        where s.id in
        <foreach item="item" index="index" collection="ids" open="(" separator="," close=")">
            #{item}
        </foreach>
        and s.userId = #{userId}  order by s.id desc
    </select>
</mapper>