11.20工作总结（mybatis中SQL语法汇总）

1. SQL case when then用法

https://blog.csdn.net/hyfstyle/article/details/88687876

1.case有两种格式，简单case函数和case搜索函数

case sex
     when '1' then '男'
     when '2' then '女'
     else '未知' end  --简单case函数

case when sex = '1' then '男'
     when sex = '2' then '女'
     else '未知' end  --case搜索函数，比较常用

2. 创建一张info表，其中包含id,name,tag三个字段

Sql> create table info(id int,name varchar(20),tag character(1));
Sql> insert into info(1,'Alice','2');
Sql> insert into info(2,'Jack','1');
Sql> insert into info(3,'Mary','0');

select id,name,(case tag  when '1' then '男' when '2' then '女' else '未知' end ) sex from info;

                              id   name   sex
                               1   Alice   女
                               2   Jack    男
                               3   Mary   未知

3. 例如SQL中常用到的case when 场景

case when value_tag='y' then '是' else '否' end value_tag --如果tag是'y'则返回'是'，否则返回'否' 

case when id between '0' and '99' then id else '-9' end --如果ID在0到99之间则直接返回ID值否则返回代码'-9'

case when value_amt  < 10000 then '101'
     when value_amt between 10000 and 30000 then '102' 
	 when value_amt > 30000 then '103'  else '-1' end val_type --如果value_amt在10000以下返回val_type'101',10000与300

2. Mybatis中foreach标签的使用

Mybatis中foreach标签的作用：
一般使用foreach遍历传入的列表，如list。在Mybatis中的xml文件中，和普通的sql语句组合成一条完整的语句

foreach代码示例

 <select id="findFileStatusByUrlList" parameterType="java.util.List" resultMap="BaseResultMap">
    SELECT * FROM file_approve WHERE file IN
    <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
      #{item}
    </foreach>
  </select>
  <!--最终的效果等同于：
   SELECT * FROM file_approve WHERE file IN ("idCard","bankCard","securityCard",...)
-->

foreach标签参数解释

其中collection和item参数为必填！

collection:必须指定，
当传入的参数为list类型，为list
当传入的参数为array类型，为array
当传入的参数为map类型，为map的键key（较少使用）
item：迭代集合时，元素的别名
index：在list和数组中,index是元素的序号，在map中，index是元素的key
open：包裹被迭代集合元素的开始符号，一般为"("
close：包裹被迭代集合元素的结束符号，一般为")"
separator：被迭代元素之间的分隔符，一般为","

使用foreach注意事项
1.在sql标签需内要指定parameterType，即传入参数类型
2.同时也需要指定resultMap（查询的时候需要），返回值类型。用户也可自定义自己的resultMap。一般返回BaseResultMap，即对应的实体类。

1. 传入的参数为list的时候

对应的Dao中的Mapper文件是：

public List<User> selectByIds(List<Integer> ids);

xml文件代码片段：

<select id="selectByIds" resultType="com.txw.pojo.User">
        select * from user where id in
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
</select>

dao

int getTotalByOrggrade(@Param("list")List<Integer> orggrade);

xml

<select id="getTotalByOrggrade" resultType="java.lang.Integer">
			SELECT COUNT(*)
			FROM FX_ORG
			WHERE orggrade in
				<foreach collection="list" open="(" item="orggrade" separator="," close=")">
					#{orggrade}
				</foreach>
	</select>

2. 传入的参数为Array的时候
对应的Dao中的Mapper文件是：

public List<User> selectByIds(int[] ids);

xml文件代码片段：

<select id="selectByIds" resultType="com.txw.pojo.User">
        select * from user where id in
        <foreach collection="array" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

3. 传入的参数为Map的时候

对应的Dao中的Mapper文件是：

public List<User> selectByIds(Map<String, Object> params);

xml文件代码片段：

<select id="selectByIds" resultType="com.txw.pojo.User">
        select * from user where  id in
        <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

map的时候需要注意的是：collection的值“ids”是存储在map中的key（比如：map.put(“ids”,ids)）;尤其需要注意；

3. mybatis中使用

当写更新语句时<set>可以消除后置

<update id="updateOrgAuthPage">
    	update fx_org_auth
    		<set>
     			 enable='0'
   			 </set>
   		 where  sysid=#{sysid} and enable='1' and orgid in
     		<foreach collection="orgdel" item="item" open="(" close=")"  separator="," >
    		  #{item} 
  			</foreach>
  	</update>

4. dao传入的是map或者list时，mapping.xml如何操作？

dao

 List<Org> getOrgTreeByOrggrade(@Param("map")HashMap<String,Object> map,@Param("list")List<Integer> orggrade);

mapping.xml

<select id="getOrgTreeByOrggrade" resultMap="BaseResultMap">
		SELECT *
		FROM FX_ORG
		WHERE 1=1
		<if test="map.orgid!='' and map.orgid!=null and map.orgid!='null'">
			<if test="map.userrole!='15,16' and map.userrole!='15,17'">--非畜牧局,非卫计局
				and (orgpid=#{map.orgid} or orgid=#{map.orgid})
			</if>
		</if>
		<if test="map.orgtype!='' and map.orgtype!=null and map.orgtype!='null'">
			and orgtype=#{map.orgtype}
		</if>
		and orggrade in
		<foreach collection="list" open="(" item="orggrade" separator="," close=")">
			#{orggrade}
		</foreach>

	</select>

1. 使用到了<where>条件中双<if>判断
2. 传入参数是map集合如何取值：map.orgtype
3. <foreach>标签遍历List集合

5.mybatis的selectKey作用

SelectKey在Mybatis中是为了解决Insert数据时不支持主键自动生成的问题，他可以很随意的设置生成主键的方式。
并且将生成的主键放入parameterType="map"中
使用mybatis的selectKey就可以得到sequence的值，同时也会将值返回。不过对于不同的数据库有不同的操作方式。

SelectKey需要注意order属性，像MySQL一类支持自动增长类型的数据库中，order需要设置为after才会取到正确的值。

像Oracle这样取序列的情况，需要设置为before，否则会报错。

下面是一个xml和注解的例子，SelectKey很简单，两个例子就够了：

<insert id="insert" parameterType="map">  
    insert into table1 (name) values (#{name})  
    <selectKey resultType="java.lang.Integer" keyProperty="id">  
      CALL IDENTITY()  
    </selectKey>  
  </insert>

上面xml的传入参数是map，selectKey会将结果放到入参数map中。用POJO的情况一样，但是有一点需要注意的是，keyProperty对应的字段在POJO中必须有相应的setter方法，setter的参数类型还要一致，否则会报错。

<insert id="insertUser" parameterClass="XXX.User"> 
          <selectKey resultClass="long" keyProperty="id"order="BEFORE"> 
              select SEQ_USER_ID.nextval as id from dual 
          </selectKey> 
           insert into user  (id,name,password) 
          values (#{id},#{name},#{password}) 
      </insert>

这句话会在插入user之前执行（order=“BEFORE”），该句话执行完之后，会生成一个ID，传进来的参数User对象里的id字段就会被赋值成sequence的值。

对于mysql

<insert id="insertUser" parameterClass="XXX.User"> 
          insert into user (name,password) 
         values (#{id},#{name},#{password}) 
          <selectKey resultClass="long" keyProperty="id" order="after">  
             SELECT LAST_INSERT_ID() AS ID  
        </selectKey>  
      </insert>

将selectKey放在insert之后，通过LAST_INSERT_ID() 获得刚插入的自动增长的id的值。插入之后获得ID赋值到传进来的对象中（对象中必须有相应的属性）。

6. SQL中关于EXISTS谓词的理解

在SQL语言中，EXISTS（存在）谓词是一个非常重要的查询关键词。

让我们先看看EXISTS的用法：EXISTS代表存在量词。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。

例如，以下的SQL语句：

SELECT sname
FROM student
WHERE exists
(
SELECT *
  FROM sc
  WHERE sc.sno=student.sno AND cno='1;
)