动态MySQL及连表查询

目录

1.动态MySQL

1.1set标签

1.2foreach标签

2.mybatis映射文件处理特殊字符

3.mybatis模糊查询

3.1使用字符串函数拼接

4.连表查询

---

1.动态MySQL

1.1set标签

set标签一般配合if标签使用于修改语句，如果传递的参数为null，那么不会修改该列的值

<update id="update">
          update t_student
          <set>
             <if test="name!=null and name!=''">
                name=#{name},
             </if>
             <if test="age!=null">
                age=#{age},
             </if>
             <if test="sex!=null and sex!=''">
                 sex=#{sex},
             </if>
             <if test="address!=address">
                  address=#{address},
             </if>
          </set>
          where id=#{id}
    </update>

1.2foreach标签

  如果你使用的为数组array  如果你使用的为集合 那么就用list
        collection:类型
        item:数组中每个元素赋值的变量名
        open: 以谁开始
        close:以谁结束
        separator:分割符

 根据id查询

<select id="findById" resultType="com.lpt.entity.student">
        select * from t_student where id in
        <foreach collection="array" item="id" open="(" close=")" separator=",">
             #{id}
        </foreach>
    </select>

删除

<delete id="batchDelete">
        <foreach collection="array" item="id" open="delete from student where  id in(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>

添加

<!--insert into student(name,age) values('lpt',0),('lpt01',0),('lpt02',0)-->


<insert id="saveBatch">
        insert into t_student(name,age) values
        <foreach collection="list" item="stu" separator=",">
            (#{stu.name},#{stu.isdeleted})
        </foreach>
    </insert>

2.mybatis映射文件处理特殊字符

<!--<![CDATA[sql]]>-->

<select id="findByMaxMin" resultType="com.lpt.entity.student">
           <![CDATA[select * from t_student where id >#{min} and id <#{max}]]>
    </select>

3.mybatis模糊查询

select * from 表明 where 列明 like '%%'

3.1使用字符串函数拼接

<select id="findByLike" resultType="com.lpt.entity.student">
        select * from t_student where name like concat('%',#{name},'%')
    </select>

3.2使用${}

<select id="findByLike" resultType="com.lpt.entity.student">
        select * from t_student where name like '%${name}%'
    </select>

通过使用发现${}实际上是字符串拼接，它不能防止sql注入， 而#{}它是预编译，它可以防止sql注入问题，#{}实际使用的PreparedStatement.

4.连表查询

1.多对一：从数据多的地方来查询一的一方

班级表：

 学生表:

 学生实体类

package com.lpt.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private int id;
    private String name;
    private int age;
    private Integer classId;

    private Clazz clazz;//学生所属的班级
}

 把链表查询体现到实体类上

package com.lpt.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Clazz {
    private Integer cid;
    private String cname;
}

 <resultMap id="StuMapper" type="com.lpt.entity.Student">
        <id column="stu_id" property="id"/>
        <result column="stu_name" property="name"/>
        <result column="stu_age" property="age"/>
        <result column="class_id" property="classId"/>
        <association property="clazz" javaType="com.lpt.entity.Clazz">
            <id column="cid" property="cid"/>
            <result column="cname" property="cname"/>
        </association>
    </resultMap>
    <select id="select" resultMap="StuMapper">
        select * from t_stu s join clazz c on s.class_id = c.cid where stu_id=#{id}
    </select>

查询结果如下