SpringBoot mybatis 的 Mapper.xml - sql语句

SpringBoot Mybatis 表结构如下： 

有两张表 Item.java    User.java 

Item：

@Data
public class Item implements Serializable{
	
	private int id;
	private String name;
	private int userid;

User.java 

public class User implements Serializable{

	private int id;
	private String name;
	private String sex;
	

---

1. 获得插入数据的ID：    

关键的两个属性   useGeneratedKeys="true"  keyProperty="id"

<insert id="insert" parameterType="com.xw.bootStudy.entity.Item" useGeneratedKeys="true" keyProperty="id">
    	insert into item (name,user_id) values (#{name},#{userid})
    </insert>

mapper.insert(item);
item.getId();

 在mapper方法之后，就可以getID()。

获得查询总数：      resultType="int"  就可以了。返回的是int类型

  <select id="findcount" resultType="int">
        select count(*) from item
    </select>

---

2. mapper中 单个参数传入：

controller:

    @GetMapping("/finduid/{uid}")
	public List<Item> finduid(@PathVariable("uid") String uid) {
		return service.finduid(uid);
	}
	
	@GetMapping("/findname/{name}")
	public List<Item> findname(@PathVariable("name") String name) {
		return service.findname(name);
	}

Mapper.java

@Mapper
@Repository
public interface ItemMapper {

	public List<Item> findname(String name);

	public List<Item> finduid(String uid);

    // ⭐⭐⭐单个参数可以省略 @param 参数。完整写法如下：
    public List<Item> findname(@Param("name")  String name);

xml：

   <!-- 单参数案例 -->
   <!-- 简单类型参数， #{}中可以随意写。传入的是uid,这里写成了value 。-->
   <!-- 数据库中user_id是int类型，这里传入的是string类型，也可以查出来 -->
    <select id="finduid" parameterType="String"  resultType="com.xw.bootStudy.entity.Item">
        select * from item where user_id = #{value}
    </select>
    
   <!-- ⭐⭐⭐  建议这样，和外部传入参数名称一致 -->
    <select id="findname" parameterType="String"  resultType="com.xw.bootStudy.entity.Item">
        select * from item where name = #{name}
    </select>

---

3. 模糊查询：

    <!-- 模糊查询 -->
    <!-- 因为#{...}解析成sql语句时候，会在变量外侧自动加单引号'  ' ,所以这里 % 需要使用双引号"  "，不能使用单引号 '  '，不然会查不到任何结果。-->
    <select id="findname2" parameterType="String"  resultMap="BaseResultMap">
        select * from item where name like "%"#{name}"%"
    </select>

    <!-- ⭐⭐⭐ 建议这种写法 -->
    select * from item where name like CONCAT('%','#{name}','%')

==>  Preparing: select * from item where name like "%"?"%" 

---

4.1  多条件 where if 查询：

 <!-- 传入的参数 Item 对象 -->
    <!-- 
    	<where>：声明where标签开始
        </where>：表示where标签结束
	where标签会自动判断前面是否有字段，如果有字段会使用and 
    -->
    <select id="findnameuid" parameterType="com.xw.bootStudy.entity.Item"  resultMap="BaseResultMap">
        select  
        <include refid="Base_Column_List"></include>
         from item 
         <where>
	        <if test=" name != null">
	         name like "%"#{name}"%"  
	        </if>
	         <if test=" userid != null">
	         and user_id = #{userid} 
	        </if>
        </where>
    </select>

==>  Preparing: select id, name, user_id from item WHERE user_id = ? 

4.2  <if> 标签 test 判断 数字大小，并比较大小

在 Mapper.xml 中把 status 来比较大小，status字段需要 使用 query.status 方式写。同时比较大小 需要使用    lt 、gt 等，不能直接使用 >, <

-------------------【Mapper.Java】 
 List<MerchantContractVO> selectMiniAppContractPage(@Param("partnerId")String partnerId,
                                                       @Param("query") MerchantContractMiniAppQuery query);

------------------- 【MerchantContractMiniAppQuery】  对象中status属性为Integer类型
public class MerchantContractPageQuery {
    private Integer status;
    private String merchantName;


------------------- 【Mapper.xml】 把status来比较大小，需要使用 lt gt 等，不能直接使用 >, <
<select id="merchantOrderPage"
            resultType="com.xxx.MerchantContractVO">
        SELECT *
        FROM
            tk_merchant_contract
        where partner_id = #{partnerID} and  contract_type = #{query.contractType} 
        <if test="query.merchantName != null and query.merchantName != '' ">
            AND merchant_name like concat('%',#{query.merchantName},'%')
        </if>
        <if test="query.status != null and query.status lt 4 ">
            and  inner_status = #{query.status}
        </if>
        <if test="query.status != null and query.status gte 4 ">
            and status = #{query.status}
        </if>
    </select>

---

5. sql片段：

<!-- sql 片段   -->
	<!-- 
		id:唯一标识
		基于单表来定义sql片段，可重用性才高
		不要带 where 标签，因为可能要导入多个sql标签
	 -->
	<sql id="userquery">
		 <if test=" name != null">
	         name like "%"#{name}"%"  
	        </if>
	         <if test=" userid != null">
	         and user_id = #{userid} 
	        </if>
	</sql>

使用：
 <include refid="userquery"></include>

例子：
<select id="findnameuid" parameterType="com.xw.bootStudy.entity.Item"  resultMap="BaseResultMap">
        select  
        <include refid="Base_Column_List"></include>
         from item 
</select>

---

6. <foreach> 标签使用

   
        <!-- 
        collection:指定输入对象中集合属性          
            java 中申明的 变量名为 ids   private List<Integer> ids;  collection="ids"
        item:没个遍历生成对象
        open:开始遍历拼接的字符串
        close:结束时遍历拼接的串
        separator：遍历的两个对象中需要拼接的串
	    private List<Integer> ids;
         -->
        <foreach collection="ids" item="id" open="(" close=")" separator=",">
        #{id}
        </foreach>

  collection  中的值 是 变量名称   ids  。 java 中申明的 变量名为 ids private List<Integer>  ids。

6.1 <foreach> 遍历 list 样例： 先判断List 不能为null 且 List size大于 0

 <if test="accountIds != null and  accountIds.size > 0 ">

<foreach> 标签中 collection 与 @param 的值保持一致

-----------------【Mapper.Java如下】 accountIds 是一个 String类型的 List
  
List<MerchantDateRangeDetailVO> selectPartnerTodayVerifyData(@Param("accountIds") List<String> accountIds,
                                                                 @Param("start") String start,
                                                                 @Param("end") String end);


----------------------【Mapper.xml  sql 如下】  foreach  accountIds 
 <select id="selectPartnerTodayVerifyData"
            resultType="com.xx.MerchantDateRangeDetailVO">

        select
            concat(
                    (floor
                        (FROM_UNIXTIME(ledger_time , '%H') / 2 )
                    ) * 2 + 2, ':00'
                ) AS segment,
            sum(amount_ledger_total) as value
        from
            tk_ledger_detail
        where
        verify_time &lt;=  unix_timestamp(#{end} )  and
        verify_time >=  unix_timestamp(#{start} )
        <if test="accountIds != null and  accountIds.size > 0 ">
            and account_id in
            <foreach collection="accountIds" open="(" close=")" separator="," item="id">
                #{id}
            </foreach>
        </if>

        GROUP BY segment;
    </select>

 6.2   <foreach> 遍历 。当List 在对象内部作为一个属性传入时

【如下所示，当List 是对象的一个属性时，collection="query.minStatus" 应该这样写】

---------------------------【Mapper.Java】---------------------------------------
List<MerchantContractVO> selectMiniAppContractPage(@Param("partnerId")String partnerId,
                                                    @Param("query") MerchantContractMiniAppQuery query);

---------------------------在 MerchantContractMiniAppQuery 对象中包含List ------------
public class MerchantContractMiniAppQuery extends BaseQuery {

    private String merchantName;
    private List<Integer> operationStatus;
    private List<Integer> cashStatus;
    private List<Integer> minStatus;
    private List<Integer> maxStatus;

---------------------------【Mapper/xml】---------------------------------------⭐⭐⭐⭐⭐
【如下所示，当list是对象的属性是，collection="query.minStatus" 应该这样写】
select id="selectMiniAppContractPage"
            resultType="xxx.xxx.MerchantContractVO">

        select id, xxx
        from tk_merchant_contract
        where  partner_id = #{partnerId} and delete_flag = 0
        <if test="query.merchantName != null and query.merchantName != ''">
            and merchant_name like concat('%',#{query.merchantName},'%')
        </if>
        <if test="query.minStatus != null or query.maxStatus != null or query.cashStatus != null">
            and ( 1 = 0
            <trim suffixOverrides="and|or">
                <if test="query.minStatus != null and query.minStatus.size > 0">
                    or	 (  contract_type = 1 AND inner_status IN
                                    <foreach collection="query.minStatus" open="(" close=")" separator="," item="id">
                                        #{id}
                                    </foreach>
                          )
                </if>
         
            </trim>
                )
        </if>

 7. <foreach> 批量插入 insertBatch 

List<AppLoginLog> list = appLoginLogMapper.selectListByUid(loginReq.getUid());

// 批量插入 
appLoginLogHistoryMapper.insertBatch(list);

 Mapper：

void insertBatch(List<AppLoginLog> list);

 Xml：

  <insert id="insertBatch">
    INSERT INTO t_app_login_log_history(mobile,uid,token,app_type,platform,os_version,app_version_no,
    phone_model,longitude,latitude,create_time)
    VALUES
    <foreach collection="list" item="loginLog" separator=",">
      (#{loginLog.mobile},#{loginLog.uid},#{loginLog.token},#{loginLog.appType},#{loginLog.platform},
      #{loginLog.osVersion},#{loginLog.appVersionNo},#{loginLog.phoneModel},
      #{loginLog.longitude},#{loginLog.latitude},#{loginLog.createTime})
    </foreach>
  </insert>

8. <foreach> 批量删除 

                    List<Long> ids = new ArrayList<>() ;
					for (int i = 0; i < list.size(); i++) {
						if (i == list.size() -1) {
							loginLog = list.get(i);
							break;
						}
						ids.add(list.get(i).getId());
					}

					if(!ids.isEmpty()){
                               // 批量删除
				appLoginLogMapper.deleteByIds(ids);
                      }

 Mapper：

void deleteByIds(List<Long> list);

Xml ：

 <delete id="deleteByIds" >
    delete from t_app_login_log where id in
    <foreach collection="list" item="id" index="index" open="(" close=")" separator=",">
      #{id}
    </foreach>

  </delete>

9. 使用pojo包装类型 作为复杂 查询参数 ：

 多条件复杂查询往往需要多表查询。可能一个查询条件来自User ，另一个来自 Item 。一个 实体类中的字段不满足要求，需要包装类。

1.  ItemCunstom  继承 Item。作为一个Item扩展类（这个 ItemCustom 类中可以根据业务添加 User 类属性）

public class ItemCustom extends Item{
	private static final long serialVersionUID = -4152878785466622052L;

   2. ItemCunstom  作为 ItemVO 的属性（这个 ItemVO 类中可以根据业务添加 User 类属性）

@Data
public class ItemVO {

	private ItemCustom custom;
	
	// 传入多个id
	private List<Integer> ids;

	public List<Integer> getIds() {
		return ids;
	}

Controller ：   把 ITemVO new 出来并 把 ItemCustom set进去。把 ItemVO对象传递到xml 

@GetMapping("/find3/{name}")
	public List<Item> find3( @PathVariable("name") String name) {
		ItemCustom item = new ItemCustom();
		item.setName(name);
		ItemVO vo = new ItemVO();
		vo.setCustom(item);
		return service.find3(vo);
	};

 Mapper.java ：

@Mapper
@Repository
public interface ItemMapper {
	public List<Item> find3(ItemVO item);

Mapper.xml ： 

	<!-- 使用 pojo 包装类型 -->
 	<select id="find3" parameterType="com.xw.bootStudy.custom.ItemVO"  resultMap="BaseResultMap">
        select  
            <include refid="Base_Column_List"></include>
         from item 
         where 
	          name like "%"#{custom.name}"%"  
               // custom 是 ItemVo 的属性名称
    </select>

---

10. 高级映射一对一查询：  resultType

查询 Item信息及关联的用户信息。

主查询表：Item                 关联表：User

Item 表中有个 外键 user_id 。通过外键关联查询用户表只能查询出一条记录，可以使用内连接。即 直接在后面加表名 user

select i.id,i.name,i.user_id,u.name username,u.sex 
			from item i,user u 
		where i.user_id = u.id

因为查询的数据涉及到两张表   user、item 所以我们的返回类也需要包装。查询字段，发现有3个字段是item的，两个字段（username、sex）是user的。 注意 表中字段要和对象字段对应不能错。比如 sql中 u.name username 的username要在类中找到

这里使用 item 的包装类型， ItemCustom extends Item  . 把 user 的两个属性加进来。

public class ItemCustom extends Item{
	private static final long serialVersionUID = -4152878785466622052L;
	
	private String username;
	private String sex;
	
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}

Mapper.xml ： 返回的 resultType 填写  ItemCustom 这个包装类

    <select id="find4"  resultType="com.xw.bootStudy.custom.ItemCustom">
      select i.id,i.name,i.user_id,u.name username,u.sex 
			from item i,user u 
		where i.user_id = u.id
       
    </select>
    

---

11. 一对一查询：  返回  resultMap

Items.java  这个Items 有点类似 上面的ItemCustom 。 这里 Items 把 Item中的字段加进来， 然后关联的用户信息 User 对象加进来

@Data
public class Items implements Serializable{

	private static final long serialVersionUID = 7744908086528208930L;
	private int id;
	private String name;
	private int userid;
	// 这里使用  resultMap  返回对象，查询订单关联的 用户信息，所以把user 对象放进来
	private User user;

Mapper.java

public List<Items> findresultMap();

Mapper.xml

定义 resultMap ：itemResultMap   ；type 类型就是上面的  Items

<resultMap type="com.xw.bootStudy.custom.Items" id="itemResultMap">
		<!-- 指定查询中的惟一标识，如果有多个列组成唯一标识，配置多个id -->
		<id column="id" property="id"/>
		<result column="name" property="name"/>
		<result column="user_id" property="userid"/>
		<!-- 配置映射的关联的用户信息 -->
		<!-- 用于映射关联查询的  单个对象  的信息。           
                一张订单 是不是只能查询关联出一个用户，所以就用 association -->
		<!-- property : 要将关联查询到的用户信息映射到 Items 的那个属性中 ,自然是那个 private User user 的 user了-->
		<association property="user" javaType="com.xw.bootStudy.entity.User">
			<!-- 关联查询用户的惟一标识 -->
			<!-- 
				column : 指定唯一标识 用户信息的列
				javaType : 映射到user的那个属性
			 -->
			<id column="user_id" property="id"/>
			<result column="username" property="name"/>
			<result column="sex" property="sex"/>
		</association>
	</resultMap>

    <!--  一对一的 resultMap -->
    <!-- 返回的 resultMap 就是上面定义的 -->
    <select id="findresultMap"  resultMap="itemResultMap">
      select i.id,i.name,i.user_id,u.name username,u.sex 
			from item i,user u 
		where i.user_id = u.id
    </select>

---

12. 传入 HashMap 作为查询参数 ：

controller ：

/**
	 * 传递 map 到 xml中作为查询参数
	 * @return
	 */
	@GetMapping("/findresultMap2")
	public List<Items> findresultMap2(){
		HashMap<String,Object> map = new HashMap<>();
		map.put("userid", 5);
		return service.findresultMap2(map);
	}

Mapper.java

public List<Items> findresultMap2(HashMap<String ,Object> map);

XML ： 直接把 key 作为参数写入就可以。

 <!--  一对一的 返回 resultMap。 传入 hashmap 参数查询-->
    <select id="findresultMap2"  resultMap="itemResultMap" >
      select i.id,i.name,i.user_id,u.name username,u.sex 
			from item i,user u 
		where i.user_id = u.id and u.id = #{userid}
    </select>

---

13. 返回一个以实际业务表 为原型的 java 实体类 DTO

根据业务查询返回如下字段：

然后我们 新建一个DTO，字段与之一一对应。

public class ItemDetailedDTO implements Serializable{
	
	private int id;
	private String itemname;
	private int uid;
	private String username;
	private int sex;
	private String goods_name;
	private int item_id;
	

XML ：   返回类型  resultType  直接返回这个  com.xw.bootStudy.custom.ItemDetailedDTO    

 <select id="finddto" resultType="com.xw.bootStudy.custom.ItemDetailedDTO">
    	select item.id,item.name itemname ,u.id uid ,u.`name` username,u.sex, d.goods_name ,d.item_id
			from 
			item , 
			user u , 
			item_detailed d 
			where item.user_id = u.id and item.id = d.item_id;
    </select>

---

14. 查询时间 ，存的时间是 年月日 时分秒，要求按照天来查。  CURDATE() 函数结果 : 2023-09-05

 select * from t_order where create_time BETWEEN CONCAT(CURDATE(),' 00:00:00') AND CONCAT(CURDATE(),' 23:59:59') 

15. Mysql 中常用的时间函数.

获取当前时间 NOW()、 CURRENT_DATE

-- 【当前时间】
SELECT NOW();             --  结果： 2023-09-05 06:48:47
SELECT CURRENT_DATE;      --  结果： 2023-09-05
SELECT CURRENT_TIME;      --  结果： 06:48:47
SELECT CURRENT_TIMESTAMP; --  结果： 2023-09-05 06:48:47

SELECT NOW();           -- 2023-09-05 07:14:04
SELECT DATE(NOW());     -- 2023-09-05

select YEAR(NOW());     -- 2023
select MONTH(NOW());    -- 9
select DAY(NOW());      -- 5
SELECT HOUR(NOW());     -- 7

 15.1  DATE_FORMAT  时间格式转换

-- 【DATE_FORMAT】格式转换
select  DATE_FORMAT(NOW(),'%Y-%m-%d');                    -- 2023-09-05
select  DATE_FORMAT('2024-09-25 07:14:04','%Y-%m-%d');    -- 2024-09-25
select  DATE_FORMAT(NOW(),'%Y%m%d');                      -- 20230905
select  DATE_FORMAT(NOW(),'%Y%m');                        -- 202309 可以统计月份

例：根据年月 统计数据，并按年月分组
SELECT DATE_FORMAT(statistics_date, '%Y%m') as segment,
       sum(total_verify_fee)                as value
from   month_verify_statistics
        GROUP BY segment

 15.2  DATE_ADD  时间加减

-- 【DATE_ADD】
select DATE_ADD(DATE(NOW()),INTERVAL 1 day);     -- DATE(NOW()是2023-09-05  加一天后是 2023-09-06
select DATE_ADD('2025-02-28',INTERVAL 1 day);    -- 加一天 2025-03-01
select DATE_ADD('2025-02-28',INTERVAL -10 day);  -- 往前10天  2025-02-18
select DATE_ADD('2025-02-28',INTERVAL 3 MONTh);  -- 加3个月  2025-05-28
select DATE_ADD('2025-02-28',INTERVAL 3 YEAR);   -- 加3年   2028-05-28
SELECT '2020-02-01' + INTERVAL 5 DAY AS DATE;    -- 2020-02-06

    DATE_ADD 函数时间单位：
（1）MICROSECOND：微秒
（2）SECOND：秒
（3）MINUTE：分钟
（4）HOUR：小时
（5）DAY：天
（6）WEEK：周
（7）MONTH：月
（8）QUARTER：季度
（9）YEAR：年

15.3   【FROM_UNIXTIME】 

 把时间戳转换为 ymd等 格式的时间       例： 1689668704 -->  2023-07-18 08:25:04

【FROM_UNIXTIME】  FROM_UNIXTIME(unix_timestamp,format)  把时间戳转换为 ymd等 格式的时间   

-- 数据库中  verify_time 字段是存储的 时间戳格式，秒级
SELECT 
verify_time, 
FROM_UNIXTIME(verify_time) , 
FROM_UNIXTIME(verify_time,'%Y%m%d'),
FROM_UNIXTIME(verify_time,'%Y-%m-%d'),
FROM_UNIXTIME(verify_time,'%H'),
FROM_UNIXTIME(verify_time,'%i')
from tk_ledger_detail

查询结果如下：

15.4  UNIX_TIMESTAMP

 把时间转换为 时间戳

-- UNIX_TIMESTAMP
SELECT UNIX_TIMESTAMP('2023-07-18 08:25:04');  -- 1689668704
SELECT UNIX_TIMESTAMP('2023-07-18');           -- 1689638400
SELECT UNIX_TIMESTAMP('2023-07-18 00:00:00');  -- 1689638400

16. ArrayList 传递到Mapper.xml  中判断有没有值

   判断List 是否时null ， 且size 大于 0  .   <if test="accountIds != null and  accountIds.size > 0 ">

【Mapper.java 】:
    List<MerchantDateRangeDetailVO> selectPartnerTodayVerifyData(@Param("accountIds") List<String> accountIds,
                                                                 @Param("start") String start,
                                                                 @Param("end") String end);


【Mapper.xml】 :  当这个 List 不为空且长度大于0时才会做
where 1 = 1 
 <if test="accountIds != null and  accountIds.size > 0 ">
            and account_id in
            <foreach collection="accountIds" open="(" close=")" separator="," item="id">
                #{id}
            </foreach>
        </if>

17. 多参数、数组传递到 mapper.xml 中。对数组判断有没有值。是否为null，且数组长度 等于 2

【Mapper.java】 数组 dateRange  是 CooperativeOrderQuery对象中一个属性
List<CooperativeOrderInfoVO> selectOrderList(@Param("partnerID")String partnerID,
                                                 @Param("query") CooperativeOrderQuery query);


【Mapper.xml】 
select * from tk_merchant_contract
 where partner_id = #{partnerID}
<if test="query.merchantName != null and query.merchantName != '' ">
     AND merchant_name like concat('%',#{query.merchantName},'%')
</if>
<if test="query.status != null ">
    and status = #{query.status}
</if>
<if test="query.dateRange != null and query.dateRange.length == 2 ">
    and create_date &lt;=  #{query.dateRange[1]}
    and create_date >=  #{query.dateRange[0]}
</if>
    

CooperativeOrderQuery  对象 中数组属性

public class CooperativeOrderQuery {
  private String[] dateRange;
}