学MyBatis看这四篇就够了！（3）之聊聊模糊查询以及复杂查询案例

关于模糊查询

模糊查询一般使用${},而取出基本数据类型或者String或者对象的属性用#{},这样可以放置SQL注入：

SELECT * FROM student WHERE name like '%${name}%';

当在处理这样的场景时，只是进行了简单的字符串拼接，此时如果传入的时对象的属性，即User中的name属性，拼接后就是：

SELECT * FROM student WHERE name like '%李%';

而当使用#{}时，

SELECT * FROM student WHERE name like '%#{name}%';

但是如果传参进来就是：

SELECT * FROM student WHERE name like '%'李'%';

因为#{}之前会判断基本数据类型，如果时String就自动加引号，如果时int就直接做拼接等，所以，这样的模糊查询不能用#{}

虽然普通的查询也能使用${}，但是因为只是做简单的sql字符串拼接，所以存在SQL注入的风险例如：

SELECT * FROM user WHERE name = '${name}' AND password = '${password}'

我们可以使用User对象的password属性为’OR ‘1’ = '1

那么，最终的SQL就会变回：

SELECT * FROM user WHERE name = 'yogurt' AND password = ''OR '1' = '1'

所以，1=1恒成立，即可在不知道用户名和密码的情况下完成登录验证，不安全，所以这样不对

创建复杂查询案例

创建表以及外键约束

创建两个表，student表和teacher表：

student：

CREATE TABLE `student` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  `tid` INT(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fktid` (`tid`),
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

teacher：

CREATE TABLE `teacher` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

插入教室表数据：

INSERT INTO teacher(`id`, `name`) VALUES (1, '秦老师')

插入学生表数据：

INSERT INTO `student` (`id`, `name`, `tid`) VALUES (1, '小明', 1); INSERT INTO `student` (`id`, `name`, `tid`) VALUES (2, '小红', 1); INSERT INTO `student` (`id`, `name`, `tid`) VALUES (3, '小张', 1); INSERT INTO `student` (`id`, `name`, `tid`) VALUES (4, '小李', 1); INSERT INTO `student` (`id`, `name`, `tid`) VALUES (5, '小王', 1);

ER图表示：

编写Mapper以及实体类

代码目录：

为了达到一目了然的效果，把xml文件和java文件分开，xml文件都放在resources中，并且代码目录要和原来一样，放在“dao”层，一个实体类对应一个Mapper.xml一个Mapper.java。

Student实体类：

因为每个学生对应一个老师，学生对老师是一个一对多的关系，那么创建一个student对象时需要考虑到这个问题，所以讲学生表中的tid转化为teacher对象

package com.lwh.pojo;

public class Student { 
    private int id;
    private String name;
    private Teacher teacher;

    （为了展示明了，简洁，展示时省略tostring和get，set方法，请读者误偷懒，否则报错）
}

Teacher实体类：

package com.lwh.pojo;

public class Teacher { 
    private int id;
    private String name;

    （为了展示明了，简洁，展示时省略tostring和get，set方法，请读者误偷懒，否则报错）
}

总共完成两个任务，一个是检索出老师，一个是检错出正确的学生信息，即学生id，学生姓名，学生对应的老师，先看最简单的检索出老师

有两个xml文件，故config中需要绑定两个xml

注意，我使用了typeAliases标签，这是一个简写的标签，使用package可以简写类的路径全名，再接下来学生检索会说明

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--导入properties文件-->
    <properties resource="db.properties"/>

    <!--开启或者关闭一些特性 <settings> <setting name="" value=""/> </settings> -->

    <typeAliases>
<!-- <typeAlias type="com.lwh.pojo" alias="user"/>-->
<!-- 使用了这个标签，那么包下的所有类都会以简单类名的小写形式作为他的别名 <package name="com.com.lwh.pojo"/> -->
        <package name="com.lwh.pojo"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
        <environment id="the_two">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--每一个xml都要注册-->
    <mappers>
        <mapper resource="com/lwh/dao/StudentMapper.xml"/>
        <mapper resource="com/lwh/dao/TeacherMapper.xml"/>
    </mappers>
</configuration>

工具类Utils：

package com.lwh.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class MybatisUtils { 
    private static SqlSessionFactory sqlSessionFactory;
    static { 
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        try { 
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) { 
            e.printStackTrace();
        }
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }

    public static SqlSession getSqlSession(){ 
        return sqlSessionFactory.openSession();
    }
}

编写TeacherMapper：

这里采用偷懒的写法，通过注解来写入sql，@Param中是要传入的字段，即id，，然后方法体的上面写出sql语句，加上传入的参数

package com.lwh.dao;

import com.lwh.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

public interface TeacherMapper { 
    @Select("select * from teacher where id=#{id}")
    Teacher selectTeacher(@Param("id") int id);
}

编写TeacherMapper.xml：

这里即便在接口中写出了sql语句但是还是要绑定Mapper.xml文件

namespace的内容就是dao层接口的目录

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lwh.dao.TeacherMapper">

</mapper>

在测试方法中测试

@Test
public void testTeacher(){ 
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    TeacherMapper teacherMapper=sqlSession.getMapper(TeacherMapper.class);
    Teacher teacher = teacherMapper.selectTeacher(1);

    System.out.println(teacher);
    sqlSession.close();
}

结果正确

再看最难的检索所有学生信息

分析如下：

1. 我们需要的是学生的id和name和对应的授课老师
2. 数据库中是用tid来表示老师的唯一id，故可以用连接查询来检索出所有学生的信息
3. 实体类中也是将tid改成了teacher对象
4. 可以先查学生的id和name再到教室表中查教室的基本信息

先编写StudentMapper接口：

package com.lwh.dao;

import com.lwh.pojo.Student;
import java.util.List;

public interface StudentMapper { 
    List<Student> selectAll();
}

编写StudentMapper,xml文件（重！！！）：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lwh.dao.StudentMapper">
    <select id="selectAll" resultMap="StudentTeacher">
        select * from student
    </select>
    
    <resultMap id="StudentTeacher" type="student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>

    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id=#{id}
    </select>
</mapper>

先分析第一个select标签，它对应的方法是dao层中的selectAll，但是返回的类型如果使用student的话，那么直接去单表查询，必定是查不到teacher信息的，即可以查到id和name，但是teacher会为null，所以需要使用到resultMap

resultMap的值就必须在之后的标签中写到，如之后的resulMap的id值是上一个select标签的resultMap的值，而真正的类型（type）是student，这里用到了简写，在上面的总配置文件mybatis-config.xml文件中说了，package标签之后的对象可以直接对象名，不用写完整路径，在resultMap的子标签中需要把所有字段都写出来：

1. 成员变量（property）id就是数据库中的id字段
2. 成员变量（property）name就是数据库中的name字段
3. 成员变量（property）teacher是一个对象，故需要用association子标签包围，对应数据库的字段是tid，对应的java文件，也就是java对象是Teacher，其中需要再创建一个子查询getTeacher
4. 子查询直接写出select标签，id为创建的子查询名字，getTeacher，返回结果类型是Teacher
5. 直接学出查询的语句：select * from teacher where id=#{id}，根据id查询，其实传入的参数可以“乱写”，mybatis可以只能推断你的传参，即便你写一个#{12313}仍能得到正确的结果

编写测试类

@Test
    public void testStudent(){ 
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

        List<Student> list = mapper.selectAll();
        for (Student student:list) { 
            System.out.println(student);
        }
        sqlSession.close();
    }

得到结果：