JavaWeb自我学习——利用MyBatis完成基础查询

今天练习使用MyBatis完成基础查询

1.准备环境

①数据库表student

 ②实体类Student

package test1;

public class Student {
    private String name;
    private Integer id;
    private Integer age;
    private String address;
    private String major;

    public String getName() { return name; }

    public void setName(String name) { this.name = name; }

    public Integer getId() { return id; }

    public void setId(Integer id) { this.id = id; }

    public Integer getAge() { return age; }

    public void setAge(Integer age) { this.age = age; }

    public String getAddress() { return address; }

    public void setAddress(String address) { this.address = address; }

    public String getMajor() { return major; }

    public void setMajor(String major) { this.major = major; }

    @Override
    public String toString() {
        return "Student{" +
                "name='" + name + '\'' +
                ", id=" + id +
                ", age=" + age +
                ", address='" + address + '\'' +
                ", major='" + major + '\'' +
                '}'+'\n';
    }
}

③测试用例

 ④安装MyBatisX插件

    MybatisX是一款基于IDEA的快速开发插件，为效率而生。

    步骤：File —> setting  —> Plugins 搜索MyBatisX安装

安装重启之后你会发现Mapper文件前面的图标变成了两种颜色的鸟，对应不同的文件。（蓝色代表Mapper接口，红色代表Mapper.xml文件）

主要功能:①XML和接口方法相互跳转

** ②根据接口方法生成statement**

点击之后

2.增删改查操作

StudentMapper.java

package Study.Mapper;

import Study.test1.Student;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface StudentMapper {
    //查询所有
    List<Student> selectAll();
    //根据id查询
    Student selectById(int id);
    //多条件查询

    /**
     * 参数接收
     *  1.散装参数：如果方法中有多个参数，需要使用@Param( "SQL参数占位符名称")
     *  2.对象参数:对象的属性名称要和参数占位符名称一致
     *  3-map集合参数:只需要保证SQL中的参数名和map集合的键的名称一致
     *
     * @param age
     * @param major
     * @param address
     * @return
     */
    List<Student> selectByCondition(@Param("age") int age,@Param("major") String major,@Param("address") String address);
    List<Student> selectByCondition(Student student);
    List<Student> selectByCondition(Map map);
}

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">
<!--
    namespace:名称空间
-->
<mapper namespace="Study.Mapper.StudentMapper">
    <!--
    数据库表的字段名称和实体类的属性名称不一样，则不能自动封装数据
        解决方法1. 表字段名称 as 实体类属性名称;
                缺点：每次查询都要as  解决方法：sql片段（但是不灵活）
               2.resultMap:
                   ①定义resultMap标签
                   ②在<select>标签中使用resultMap属性替换resultTyper属性
    -->
<!--    引用sql片段

        <sql id="Student">
        name,id,age,address,major
        </sql>

        引用sql方法：在要插入地方写 <include refid="Student"/>   -->

    <!--    resultMap方法
    id:唯一标识   type:映射类型
    <resultMap id="StudentResultMap" type="Study.test1.Student">
        id完成主键字段映射  result完成一般字段映射
        column值填表中列名，property值填对应的实体属性名称
        <id column="" property=""></id>
        <result column="" property=""></result>
      </resultMap>

    -->
<!--如果用resultMap resultType改成如果用resultMap="对应id"  -->
    <select id="selectAll" resultType="Study.test1.Student">
           select * from student;
    </select>
    <select id="selectById" resultType="Study.test1.Student">
            select * from student where id = #{id};
    </select>
    <select id="selectByCondition" resultType="Study.test1.Student">
            select * from student
                where age = #{age}
                and major = #{major}
                and address = #{address}
    </select>
</mapper>

MyBatisTest.java

package Study.test;

import Study.Mapper.StudentMapper;
import Study.test1.Student;
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 org.junit.Test;

import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MyBatisTest {
    @Test
    public void test() throws Exception{
        //参数①散装
            String major = "RG";
            int age = 20;
            String address = "HeBei";
        //参数②对象
            String major1 = "RG";
            int age1 = 20;
            String address1 = "SiChuan";
            Student stu2 = new Student();
            stu2.setMajor(major1);
            stu2.setAddress(address1);
            stu2.setAge(age1);
        //参数③Map
            String major2 = "JK";
            int age2 = 20;
            String address2 = "SiChuan";
            Map map = new HashMap();
            map.put("major",major2);
            map.put("age",age2);
            map.put("address",address2);
        //参数处理：根据实际情况处理参数，比如加% _等

        //1.获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2.获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //3.获取Mapper接口代理对象
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

        //4.执行方法
        //4.1.查询所有
        List<Student> students1 = studentMapper.selectAll();
        //4.2.根据ID查询
        Student stu1= studentMapper.selectById(2);
        //4.3.多条件查询
            //多参数
            List<Student> students2= studentMapper.selectByCondition(age,major,address);

            List<Student> students3= studentMapper.selectByCondition(stu2);

            List<Student> students4= studentMapper.selectByCondition(map);

        System.out.println("results1:");
        System.out.println(students1);
        System.out.println("results2:");
        System.out.println(stu1);
        System.out.println("results3:");
        System.out.println(students2);
        System.out.println("results4:");
        System.out.println(students3);
        System.out.println("results5:");
        System.out.println(students4);
        //5.释放资源
        sqlSession.close();
    }
}

mybatis-config.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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!--数据库连接信息-->
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/test?useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--加载sql映射文件-->
<!--        <mapper resource="Study/Mapper/StudentMapper.xml"/>-->

        <!--Mapper代理方式-->
        <package name="Study.Mapper"/>

    </mappers>
</configuration>

运行结果：

3.各种细节：（重点）

①数据库表的字段名称和实体类的属性名称不一样，则不能自动封装数据

解决方法：

1. 表字段名称 as 实体类属性名称;
   缺点：每次查询都要as 解决方法：sql片段（但是不灵活）

2.resultMap:
①定义resultMap标签
②在<select>标签中使用resultMap属性替换resultTyper属性

引用sql片段

<sql id="Student">
    name,id,age,address,major
</sql>

引用sql方法：在要插入地方写 <include refid="Student"/>

resultMap方法

属性 id:唯一标识   type:映射类型

<resultMap id="StudentResultMap" type="Study.test1.Student">

    id子标签完成主键字段映射  result子标签完成一般字段映射
    column属性填表中列名，property属性值填对应的实体属性名称

    <id column="" property=""></id>
    <result column="" property=""></result>
</resultMap>

如果用resultMap resultType改成如果用resultMap="对应id"

 <select id="selectAll" resultMap="StudentResultMap">
    select * from student;
 </select>

②在select标签中：

1.参数占位符:

     ①#{}:会将其替换为?，可以防止SQL注入

    ②${}:拼sql。会存在SQL注入问题

    ③使用时机:
             1）参数传递的时候:  #{}
             2）表名或者列名不固定的情况下:  ${}  存在SQL注入问题

2.参数类型:

parameterType: 可以省略

3.特殊字符处理:

例如 <

            ①转义字符:  <

           ②CDATA区:< ![CDATA[     <      ]]>

小结：

本次主要对MyBatis进行查询操作进行了练习，今天到此为止，明天见！