Java中使用Springboot和Mybatis框架中,Mapper.xml对SQL语句的用法

1.查询数据时,将实体类变量与数据库中对应

<sql id="colum">
    USER_NAME as userName,
    USER_AGE as userAge
</sql>
Select <include refid =”colum”/> from userinfo

 2.将插入后的数据id映射到实体类上

<insert id = "insertUser">
    <selectKey keyProperty=”id” resultType = “int”order = “BEFORE”>
        (select nvl(max(ID),0) + 1 as ID from user t)
    </selectKey >
    insert into user(name) values( #{name} )
</insert>

3.for循环添加

Mapper.Java中

    int inserUser( @Param("userList") List<User> userList);

Mapper.XML中

<insert id="insertUser" parameterType="java.util.List">
insert all
    	<foreach collection = "userList" index = "index" item = "item">
        	into user values( #{item.userId} )
    </foreach>
    select * from dual
</insert>

4. 模糊查询

Mapper.Java中

List<User> getUserInfo(@Param("name") String name);

Mapper.XML中

<select id="getUserInfo">
    <bind name="userName" value="'%' + name + '%'"/>
    select * from user where name like #{userName}
</select>

5.多嵌套查询

<resultMap id = “getUser” type = “对应的实体类”>
    <result column = “数据库的名称” property = “实体类的名称”/>
    <collection property="featIds" select="getFeatId" 	column="{type = type, userName = userName}"/>
<resultMap/>
 
<select id = “getUserInfo” resultMap = “getUser”/>
<select id = “getFeatId” resultType = “对应的实体类”/>

6.大于号小于号

大于号 >   小于号  <

7. Oracle对应的数据类型

 jdbcType=NUMERIC   (数字)     

 jdbcType=VARCHAR  (字符串)

 8.时间格式

 to_date(#{date},'yyyy-MM-dd hh24:mi:ss')

  9.Oracle中添加当前时间

Sysdate

可以在对应的字段下直接写

列如:

        INSERT INTO USER(username, age, time) 

        VALUES (

                        "张三",

                        "18",

                        sysdate

        )

 10.Trim标签属性

prefix表示以xxx开头

prefixOverrides表示若标签中间内容以xxx开头，就删除

suffix表示以xxx结尾

suffixOverrides表示若标签中间内容以xxx结尾，就删除

列如:

        UPDATE USERNAME 

        < trime prefix = "set"  sufficOverrdes = ",">

                <if test="state != null">

                        state = #{state} ,

                 </if>

                <if test="age!= null">

                        age= #{age} ,

                 </if>

           where id = #{id}

这句话就是代表这段代码以 "set" 开头 , 如果结尾是"逗号" 就删掉.

 11.If-Else标签

choose (when,otherwise)

例如:

        <choose>
            <when test="params!=null">  //when 代表如果
                right JOIN
            </when>
            <otherwise> // otherwise代表否则
                LEFT JOIN
            </otherwise>
        </choose>

 12.if标签中大于号小于号

大于   gt

大于等于 gte

小于  lt 

小于等于  lte

例如:  

        <if text = "age gt '1'  and age lt  '3'"/>

 13.in多参查询

Mapper.Java中

List<User> getUser(@Parm("userIds") List<Integer> userIds);

Mapper.XML中 

select * from user
where userid in
          <foreach collection="userIds" item="item" index="index" open="(" separator="," close=")">
              #{item}
          </foreach>

 14.查询多个字符串类型

and regexp_like(t.userName, '张三|李四|王五')

15.字符的拼接

<!--两表查询拼接字段,截取“/”第八个之前的数据-->
    <select id="getSys001JiancetjInfo" resultType="com.qc.jy.pojo.Sys001JiancetjInfo">
        SELECT t2.*,
        SUBSTR(CONCAT(CONCAT(CONCAT(CONCAT(t1.PATH,t1.gcbm),'/'),t2.jctp),'/'), 1, INSTR(CONCAT(CONCAT(CONCAT(CONCAT(t1.PATH,t1.gcbm),'/'),t2.jctp),'/'), '/', 1, 8) - 1) AS pathjctb,
        SUBSTR(CONCAT(CONCAT(CONCAT(CONCAT(t1.PATH,t1.gcbm),'/'),t2.JCBG),'/'), 1, INSTR(CONCAT(CONCAT(CONCAT(CONCAT(t1.PATH,t1.gcbm),'/'),t2.JCBG),'/'), '/', 1, 8) - 1) AS pathjcbg,
        SUBSTR(CONCAT(CONCAT(CONCAT(CONCAT(t1.PATH,t1.gcbm),'/'),t2.JCSP),'/'), 1, INSTR(CONCAT(CONCAT(CONCAT(CONCAT(t1.PATH,t1.gcbm),'/'),t2.JCSP),'/'), '/', 1, 8) - 1) AS pathjcsp
        FROM SYS_001_Wenjiantjinfo t1 JOIN SyS_001_JIANCETJINFO t2 ON t1.gcbm = t2.gcbm
        <where>
            <if test="featid !=null and featid!=''">
               FEATID = #{featid}
            </if>
        </where>
    </select>

16.使用insert-select进行添加数据

<insert id="addPipingNetworkSketch">
        insert into SYS_002_GUANWANGCAOTU(FROMID,TOID,GDTYPE,GDCZ,GDKJ,REMARK,FROMX,FROMY,TOX,TOY,UPLOADPERSON,ID,FEATID,STYLE,WIDTH,SSQZ,SFJS,LXFS,DWMC)
        SELECT #{fromId},#{toId},#{gdType},#{gdcz},#{gdkj},#{remark},#{fromX},#{fromY},#{toX},#{toY},#{uploadPerson}, MAX(ID) + 1 , #{featId} , #{style}, #{width}, #{ssqz}, #{sfjs}, #{lxfs}, #{dwmc} FROM SYS_002_GUANWANGCAOTU
    </insert>