Mybatis使用（2）----查询

实现增删改查功能

查询

1.StudentMapper接口

public interface StudentMapper {
    //查询所有学生信息
    List<Student> getAll();
    //根据学生id查询
    Student getById(Integer id);
    }

2.StudentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dpc.mapper.StudentMapper">
	<-- 查询所有学生信息
	    没有输入参数
	    输出类型为Student
	!-->
    <select id="getAll" resultType="com.dpc.pojo.Student">
        select id,name,sex,birthday,grade,tid from student
    </select>
    <-- 根据指定id查询学生信息
	   输入参数：paramenterType 类型为Integet（int）
	   基本数据类型和String可以省略不写。
	    输出类型为 Student（在核心配置类中指定包，不需要写全限定名）
	!-->
    <select id="getById" parameterType="Integer" resultType="student">
        select id,name,sex,birthday,grade,tid from student where id=#{id}
    </select>
</mapper>

3.mybatisConfig.xml

<?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>
    <!--读取jdbc.properties属性-->
    <properties resource="db.properties"></properties>
    <!--设置日志输出-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <!--注册实体类的别名-->
    <typeAliases>
        <package name="com.dpc.pojo"></package>
    </typeAliases>
    <!--配置环境变量-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <!--
                    private String driver;
                    private String url;
                    private String username;
                    private String password;
                -->
                <property name="driver" value="${db.driverClassName}"></property>
                <property name="url" value="${db.url}"></property>
                <property name="username" value="${db.username}"></property>
                <property name="password" value="${db.password}"></property>
            </dataSource>
        </environment>
    </environments>
    <!--注册mapper.xml文件-->
    <mappers>
        <mapper class="com.dpc.mapper.StudentMapper"/>
        <!--批量注册-->
<!--        <package name="com.dpc.mapper"></package>-->
    </mappers>
</configuration>

4.测试

public class test1 {
    SqlSession sqlSession;
    //动态代理对象
    StudentMapper studentMapper;
    @Before
    public void openSqlSession() throws IOException {
        //1.读取核心配置文件
        InputStream in = Resources.getResourceAsStream("myBatisConfig.xml");
        //2.创建工厂对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //3.取出sqlSession
        sqlSession = factory.openSession(true);//自动提交事务
        //4.取出动态代理的对象,完成接口中方法的调用,实则是调用xml文件中相的标签的功能
        studentMapper = sqlSession.getMapper(StudentMapper.class);
    }
    @After
    public void closeSqlSession(){
        sqlSession.close();
    }
    @Test
    public void testGetAll(){
        List<Student> list = studentMapper.getAll();
        list.forEach(student -> System.out.println(student));
    }
    @Test
    public void testGetById(){
        Student student = studentMapper.getById(3301);
        System.out.println(student);
    }
}

模糊查询

1.’ ${} ’

<!--    根据学生姓名模糊查询-->
 <select id="getByName" parameterType="String" resultType="student">
   select id,name,sex,birthday,grade,tid from student where Name like "%${name}"
 </select>

存在问题：sql注入

1.2测试

 @Test
    public void testGetByName(){
        String s="\"or \"1==1 ";
        List<Student> list = studentMapper.getByName(s);
        list.forEach(student -> {
            System.out.println(student);
        });
    }

1.3结果

sql语句直接拼接输入字段，将所有结果查询出来

==>  Preparing: select id,name,sex,birthday,grade,tid from student where Name like "%"or "1==1 "
==> Parameters: 
<==    Columns: id, name, sex, birthday, grade, tid
<==        Row: 3300, 张三, 0, 1999-01-01, 一年级, 1
<==        Row: 3301, 李四, 1, 1998-01-01, 二年级, 2
<==        Row: 3302, 王五, 1, 1997-01-01, 三年级, 1
<==        Row: 3303, 赵六, 0, 1998-08-01, 二年级, 2
<==        Row: 3304, 钱七, 1, 1996-01-01, 四年级, 3
<==        Row: 3305, 周八, 0, 1995-01-01, 五年级, 1
<==      Total: 6
Student{id=3300, name='张三', sex=0, birthday=Fri Jan 01 00:00:00 GMT+08:00 1999, grade='一年级', tid=1}
Student{id=3301, name='李四', sex=1, birthday=Thu Jan 01 00:00:00 GMT+08:00 1998, grade='二年级', tid=2}
Student{id=3302, name='王五', sex=1, birthday=Wed Jan 01 00:00:00 GMT+08:00 1997, grade='三年级', tid=1}
Student{id=3303, name='赵六', sex=0, birthday=Sat Aug 01 00:00:00 GMT+08:00 1998, grade='二年级', tid=2}
Student{id=3304, name='钱七', sex=1, birthday=Mon Jan 01 00:00:00 GMT+08:00 1996, grade='四年级', tid=3}
Student{id=3305, name='周八', sex=0, birthday=Sun Jan 01 00:00:00 GMT+08:00 1995, grade='五年级', tid=1}

2. ‘ #{} ’

<!--    优化后的模糊查询-->
    <select id="getByNameGood" parameterType="String" resultType="student">
        select <include refid="allColumns"></include>
        from student
        where name like concat("%",#{name},"%")
    </select>

2.1测试

@Test
    public void testGetByNameGood(){
        String s="\"or \"1==1 ";
        String ss="%五";
        List<Student> list = studentMapper.getByNameGood(s);
        List<Student> list1 = studentMapper.getByNameGood(ss);
        list.forEach(student -> {
            System.out.println(student);
        });
        list1.forEach(student -> {
            System.out.println(student);
        });
    }

2.2结果

==>  Preparing: select id,name,sex,birthday,grade,tid from student where name like concat("%",?,"%")
==> Parameters: "or "1==1 (String)
<==      Total: 0
==>  Preparing: select id,name,sex,birthday,grade,tid from student where name like concat("%",?,"%")
==> Parameters: %五(String)
<==    Columns: id, name, sex, birthday, grade, tid
<==        Row: 3302, 王五, 1, 1997-01-01, 三年级, 1
<==      Total: 1