开发总结-Dao层（Mapper层）

Mybatis-plus新用法

VehicleBO one = vehicleService.getOne(Wrappers.<VehicleBO>lambdaQuery().eq(VehicleBO::getVin, reqVo.getVin()));

boolean b = bizAccountApplyService.remove(Wrappers.<BizAccountApplyBO>lambdaQuery().eq(BizAccountApplyBO::getId, 1457753126076448l));

iBizAccountService.update(Wrappers.<BizAccountBO>lambdaUpdate().setSql("follow_count=follow_count+1").eq(BizAccountBO::getId, accountId));

mybatisPlus坑

字段更新为null 默认不生效，除非加上updateStrategy = FieldStrategy.IGNORED

@ApiModelProperty(value = "贷款类型")
@TableField(value = "LOAN_TYPE", updateStrategy = FieldStrategy.IGNORED)
private String loanType;

Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-01400: 无法将 NULL 插入 (“ZHXY_RSW_LOCAL”.“T_EXAM_SUBJECT”.“ID”)
使用mybatis-plus插入时，明明设置了id的值，还报这个错
解决办法：在id字段上，添加 @TableId(type = IdType.INPUT)
在这里插入图片描述

条件是表中的某个字段不为null时，添加过滤

在这里插入图片描述
上面这样写会报错，下面这样写才对

left join时左表过滤用where，右表过滤用and

说有下面两张表
在这里插入图片描述

– 找出每个班级的名称及其对应的女同学数量

SELECT c.name, count(s.name) as num 
FROM classes c left join students s 
on s.class_id = c.id 
and s.gender = 'f'
group by c.name

– 找出一班的同学总数

SELECT c.name, count(s.name) as num 
FROM classes c left join students s 
on s.class_id = c.id 
where c.name = '一班' 
group by c.name

mysql分组后组内排序然后取每组第一条结果

需求：按room_id分组后，组内按data_time排序，然后取每组的第一条数据
可能看不懂，但就这么写没错，亲测正确（必须加limit）

		select t.* from (
            select t1.room_id,t2.room_name,t1.queuing_time,t1.in_num
            from t_meal_room t2
            left join t_meal_data t1 on t1.room_id=t2.id
            order by t1.data_time desc 
limit 999999999
        )  t  group by room_id

resultMap

适用场景：类似一个项目多个合同
在这里插入图片描述

<resultMap id="projectManegeMap" type="com.iflytek.project.base.vo.resp.ProjMoneyManageVo">
    <result column="PROJECT_ID" property="projectId"/>
    <result column="PROJECT_NAME" property="projectName"/>
    <result column="PROJECT_CODE" property="projectCode"/>
    <result column="PROJECT_STAGE" property="projectStage"/>
    <result column="ESTIMATED_INVESTMENT_AMOUNT" property="estimatedInvestmentAmount"/>
    <!--ProjMoneyManageVo有属性 List<ContractInfo> contractList-->
    <collection property="contractList" javaType="list" ofType="com.iflytek.project.base.vo.resp.ContractInfo">
        <result column="CONTRACT_ID" property="contractId"/>
        <result column="CONTRACT_MONEY" property="contractMoney"/>
        <result column="SIGN_DATE" property="signDate"/>
        <result column="PAYMENTS_NUM" property="paymentsNum"/>
        <result column="CURRENT_NUM" property="currentNum"/>
        <result column="PAYED_MONEY" property="payedMoney"/>
        <result column="TOPAY_MONEY" property="topayMoney"/>
        <result column="PAYMENTS_PER" property="paymentsPer"/>
        <result column="PAYED_TOPAY" property="payedTopay"/>
        <result column="PAYED_PER" property="payedPer"/>
    </collection>
</resultMap>

in 占位符

mapper接口ids传数组String[]

List<TTopicFileStorage> selectFileList(@Param("ids") String[] ids, @Param("stage") String stage);

mapper.xml

and d.id IN
<foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
    #{item}
</foreach>

或者
mapper接口idList传列表List

List<ProjectFileResp> getProjectFileByIds(@Param("idList") List<String> idList);

mapper.xml

<if test='idList != null and idList.size() >0'>
    and t.id in
    <foreach collection="idList" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</if>

根据某个字段正序和倒叙排序

前端传过来排序的字段和如何排
sortField: rk_avg_score
sortOrder: asc
后端排序的时候使用 $ 代替#，这里只能用$, 用#不行报错：ORA-01745: 无效的主机/绑定变量名

order by ${reqVo.sortField} ${reqVo.sortOrder}

函数使用

日期转为指定格式的字符串
在这里插入图片描述
字符串转时间

AND h.HJSJ >= to_date('2021/01/01 00:00:00', 'YYYY-MM-DD hh24:mi:ss')

如果为null则显示0（oracle）

NVL(expr, 0)

MySQL中是

IFNULL(t4.CURRENT_NUM,0) CURRENT_NUM

保留两位小数

ROUND(expr, 2)

oracle批量插入

比循环一个一个插入快
mapper接口：

Integer insertCommonSelectUser(List<CommonSelectUser> list);

xml文件：

<insert id="insertCommonSelectUser" parameterType="java.util.List">
       insert all 
       <foreach collection="list" item="item" index="index">
           into COMMON_SELECT_USER(ID,REF_ID,REF_SRC,USER_CODE,USER_NAME)
           values (#{item.id},#{item.refId},#{item.refSrc},#{item.userCode},#{item.userName})
       </foreach>
       SELECT 1 FROM DUAL
</insert>

插入系统当前时间

oracle：sysdate
mysql：now()

将这种查询结果封装为list套list结构

想要的效果：公共的流程类别信息提出来，里面包含所有该类别的流程信息列表
在这里插入图片描述

返回结果结构：

[
        {
            "id":"624f25edffb74976a1d3bbe5d0e2d34e",  //流程类别id
            "name":"行政办公",  //流程类别名称
            "flowList":[  //该流程类别下的所有流程信息列表
                {
                    "id":"2",
                    "createPermission":"全员可见",
                    "flowName":"公务用车",
                    "lcbm":null,
                    "remark":"公务用车"
                }
            ]
        },
        {
            "id":"7677ee5644254a74a7656eaeee186202",
            "name":"学生工作",
            "flowList":[
                {
                    "id":"4",
                    "createPermission":"全员可见",
                    "flowName":"流程4",
                    "lcbm":null,
                    "remark":null
                },
                {
                    "createUser":null,
                    "createDate":null,
                    "opUser":null,
                    "opDate":null,
                    "activeFlag":null,
                    "id":"c5b946f6dca5439c9defd1cc8b700dae",
                    "createPermission":"全员可见",
                    "flowName":"接待申请",
                    "lcbm":null,
                    "remark":"复杂表单xxxx"
                }
            ]
        },
        {
            "id":"a61689b5eee44155ab22ace3a5a0d5e0",
            "name":"教学教学",
            "flowList":[
                {
                    "id":"1",
                    "createPermission":"指定人员可见",
                    "flowName":"保修申请",
                    "lcbm":null,
                    "remark":"这个是很短的一个说明"
                }
            ]
        }
]

怎么实现? 返回结果使用resultMap
在这里插入图片描述

GROUP BY

test原始表
在这里插入图片描述
SELECT NAME FROM test GROUP BY NAME 的结果：

理解分析：GROUP BY NAME
该句执行后，我们想象生成了虚拟表3，如下所图所示，生成过程是这样的：group by name，那么找name那一列，具有相同name值的行，合并成一行，接下来对这个虚拟表执行select操作，如果执行select * 因为SCORE有两个数据，所以会报错，对于SCORE这样多个值得该怎么办呢？答案就是聚合函数
在这里插入图片描述
GROUP BY多个字段
例如: GROUP BY NAME,NUMBER我们可以把name和number 看成一个整体字段，以他们整体来进行分组的。如下图

SELECT NAME,AVG(SCORE) AVG FROM test GROUP BY NAME,NUMBER 结果为:

GROUP BY统计
有这样一张表：

统计：按专业为维度，统计每个专业老师平均指导学生数量
思路：用专业分组，统计所有的学生数/所有的老师数

	SELECT x.XNZYMC, ROUND(COUNT(x.XH)/COUNT(DISTINCT x.GH), 0) ZDXSS
	From x 
	GROUP BY x.XNZYMC

在这里插入图片描述
HAVING COUNT(1) > 1

SELECT COMMENT_ID
FROM COMMENT_COMMENTTAG
WHERE POST_ID = 1
GROUP BY COMMENT_ID, COMMENT_TAG
HAVING COUNT(1) > 1

根据COMMENT_ID 和 COMMENT_TAG分组后取数量大于1的COMMENT_ID

深入理解JOIN

select * from student

在这里插入图片描述

select * from score

在这里插入图片描述

select a.* ,b.* from student a join score b on a.s_id=b.s_id

在这里插入图片描述

select a.* ,b.* from student a left join score b on a.s_id=b.s_id

在这里插入图片描述
由此可见join或者left join都是以数据多的一张表为基表，数据少表的根据关联字段匹配到数据多的表。

LEFT JOIN ON 和 JOIN ON

在这里插入图片描述

SELECT * FROM `product` t1 LEFT JOIN `product_details` t2 ON t1.ID=t2.ID

在这里插入图片描述
分析理解：把两张表水平拼接在一起，左表全部查出来，根据条件来匹配出右表，不匹配的右表用null代替。

JOIN ON相当于在LEFT JOIN ON的基础上，将右表不匹配的记录null删除

SELECT * FROM `product` t1 JOIN `product_details` t2 ON t1.ID=t2.ID

在这里插入图片描述

MySQL中的concat

concat(str1,str2,…)
在这里插入图片描述

Oracle中的||（类似MySQL中的concat）拼接字段

Oracle中的concat() 与 ‘||’ 不同在于，concat()只能对两个字符串进行拼接(字符串多的话只能嵌套使用)，而||可以对字符串无限拼接。跟MySQL中的concat一样。
在这里插入图片描述

Mysql 中group_concat

使用情况：
在这里插入图片描述

select t3.*,group_concat(t4.GROUP_NAME separator',') groupName
 from t3
 left join t4 on t4.ID=t3.AREA_ID

Oracle中WM_CONCAT

功能类似MySQL的group_concat
原表：
在这里插入图片描述
结果：

做法：
第一步：先将原表两个字段CAPACITY, SCORE合并为一个字段CAPACITY_SCORE

SELECT PROJECT_ID,PROJECT_NAME,AWARD_LEVEL_NAME,
	   CONCAT(CONCAT(t4.CAPACITY_NAME, ':'), t3.SCORE) CAPACITY_SCORE
	FROM XXX ...

第二步：再将合并后的结果列转行
在这里插入图片描述

SELECT PROJECT_ID,PROJECT_NAME,AWARD_LEVEL_NAME,
TO_CHAR(WM_CONCAT(CAPACITY_SCORE)) GROUP_CAPACITY_SCORE 
FROM (
	SELECT PROJECT_ID,PROJECT_NAME,AWARD_LEVEL_NAME,
	   CONCAT(CONCAT(t4.CAPACITY_NAME, ':'), t3.SCORE) CAPACITY_SCORE
	FROM XXX ...
) tt GROUP BY PROJECT_ID,PROJECT_NAME,AWARD_LEVEL_NAME

注意1：SELECT 的字段都要再GROUP BY中分组，否则出现 > ORA-00979: not a GROUP BY expression

注意2：TO_CHAR(WM_CONCAT())配套使用，单独使用WM_CONCAT()查询很慢

Oracle中listagg列转行

有这样一个字典表
在这里插入图片描述
需求结果

如果T_BOOKS_TEACHER表中is_Lately_Three字段存了上面字典表中的TYPE_NO为SFJSNCBJC的多个INFO_CODE，查询的时候要求翻译过来

		SELECT
		b.IS_LATELY_THREE,
			(
				SELECT listagg(info_name, ',') WITHIN GROUP(ORDER BY info_code)
				FROM t_dict_info
				WHERE type_no = 'SFJSNCBJC'
				AND info_code IN
				((SELECT REGEXP_SUBSTR(b.is_Lately_Three, '[^,]+', 1, LEVEL, 'i') AS STR
				FROM DUAL
				CONNECT BY LEVEL <= LENGTH(b.is_Lately_Three) -
				LENGTH(REGEXP_REPLACE(b.is_Lately_Three, ',', '')) + 1))
			) isLatelyThreeName
		FROM
		 T_BOOKS_TEACHER b 
		where
			b.PLAN_ID='3031e8270f7241bb9d93b527e1dbcb77'

在这里插入图片描述

DECODE 函数（好用，有点像case when）

decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
SELECT DECODE(t.SSEX, null,‘未知’,‘1’,‘男’,‘0’,‘女’, ‘男’) SEX
FROM STUDENTS t
在这里插入图片描述

CASE WHEN（MySQL和Oracle）

在这里插入图片描述

CASE WHEN实现行转列

现有表
在这里插入图片描述
需求是

方法1：临时表

方法2：静态sql

统计（MySQL 字段符合条件+1）

在这里插入图片描述

两张表table1和table2根据一个字段id相同，将table2的name字段值设置为table1对应id的name值

在这里插入图片描述

结果table2：

合并两个查询的结果集

在这里插入图片描述

union all

把两个SQL的结果叠加在一起，常用来代替or

(
    select max(t.WHICH_ROUND) WHICH_ROUND,
           t.PROJECT_ID       id
    from t_biz_file t
             join t_biz_project t1
                  on t1.ID = t.BELONG_ID and t.BELONG_TYPE = 'LX' and t1.STATE != 'ZC'
    group by t.PROJECT_ID
)
union all
(
    select max(t.WHICH_ROUND) WHICH_ROUND,
           tbm.ID
    from t_biz_file t
             join t_biz_project t1 on t1.ID = t.PROJECT_ID and t.BELONG_TYPE = 'SH'
             join t_biz_meeting tbm on t.BELONG_ID = tbm.id and tbm.STATE != 'ZC'
    group by tbm.ID
)

排序的字段有null的

在这里插入图片描述

传多个参数，可以使用map也可以使用@Param注解或者使用reqVo对象

使用了@Param注解，xml中就要使用注解中的字符串.属性

使用map

mapper.xml （如果mapper接口中添加了@Param(“map”)，那xml中使用要加map.password这样，没加@Param注解，就不需要加map. 跟javaBean一致）

<!--根据邮件数字字符串区间查询-->
<select id="findByEmailArea" resultType="cn.tedu.store.entity.User">
   select * from t_user where email between #{startEmail} and #{endEmail} and password=#{password}
</select>

mapper接口

List<User> findByEmailArea(Map<String,String> map);

单元测试

@Test
public void findByEmailArea(){
   HashMap<String, String> map = new HashMap<>();
   map.put("startEmail","12346");
   map.put("endEmail","12349");
   map.put("password","zhu");
   List<User> list = mapper.findByEmailArea(map);
   for (User user:list) {
      System.err.println(user);
   }
}

使用@Param注解

Mapper.xml跟上面一样，将参数用#{}占位替代
Mapper接口

List<User> findByEmailArea(@Param("startEmail") String startEmail,

秒客网

开发总结-Dao层（Mapper层）

Mybatis-plus新用法

mybatisPlus坑

条件是表中的某个字段不为null时，添加过滤

left join时左表过滤用where，右表过滤用and

mysql分组后组内排序然后取每组第一条结果

resultMap

in 占位符

根据某个字段正序和倒叙排序

函数使用

oracle批量插入

插入系统当前时间

将这种查询结果封装为list套list结构

GROUP BY

深入理解JOIN

LEFT JOIN ON 和 JOIN ON

MySQL中的concat

Oracle中的||（类似MySQL中的concat）拼接字段

Mysql 中group_concat

Oracle中WM_CONCAT

Oracle中listagg列转行

DECODE 函数（好用，有点像case when）

CASE WHEN（MySQL和Oracle）

CASE WHEN实现行转列

统计（MySQL 字段符合条件+1）

两张表table1和table2根据一个字段id相同，将table2的name字段值设置为table1对应id的name值

合并两个查询的结果集

union all

排序的字段有null的

传多个参数，可以使用map也可以使用@Param注解或者使用reqVo对象

使用map

使用@Param注解

`相关文章`

开发总结-Dao层（Mapper层）

Mybatis-plus新用法

mybatisPlus坑

条件是表中的某个字段不为null时，添加过滤

left join时左表过滤用where，右表过滤用and

mysql分组后 组内排序 然后取每组第一条结果

resultMap

in 占位符

根据某个字段正序和倒叙排序

函数使用

oracle批量插入

插入系统当前时间

将这种查询结果封装为list套list结构

GROUP BY

深入理解JOIN

LEFT JOIN ON 和 JOIN ON

MySQL中的concat

Oracle中的||（类似MySQL中的concat）拼接字段

Mysql 中group_concat

Oracle中WM_CONCAT

Oracle中listagg列转行

DECODE 函数（好用，有点像case when）

CASE WHEN（MySQL和Oracle）

CASE WHEN实现行转列

统计（MySQL 字段符合条件+1）

两张表table1和table2根据一个字段id相同，将table2的name字段值设置为table1对应id的name值

合并两个查询的结果集

union all

排序的字段有null的

传多个参数，可以使用map也可以使用@Param注解或者使用reqVo对象

使用map

使用@Param注解

相关文章

mysql分组后组内排序然后取每组第一条结果

`相关文章`