【Mybatis】多对一 一对多的处理

多对一 一对多的处理

万能Map

==================================================================================
万能Map使用方法
1.接口方法
User selectUserByNP(Map<String,Object> map);
2.编写sql语句
<select id="selectUserByNP2" parameterType="map" resultType="com.kuang.pojo.User">
  select * from user where name = #{username} and pwd = #{pwd}
</select>
3.测试类中
Map<String, Object> map = new HashMap<String, Object>();
map.put("username","小明");
map.put("pwd","123456");
User user = mapper.selectUserByNP2(map);
==================================================================================
注意点
1.当有多个参数的时候建议使用万能Map，或者注解@Param!
2.万能Map可以使用在增删改查任一过程中
3.#{}与${}的区别
#{}的作用主要是替换预编译语句(PrepareStatement)中的占位符?
${}的作用是直接进行字符串替换(vue中一直这样用)
==================================================================================

模糊查询

1.在java代码执行的时候 传递通配符
①接口方法
List<User> getUserLike(String name);
②编写sql语句
    <!--模糊查询-->
<select id="getUserLike" resultType="com.kuang.pojo.User">
    select * from mybatis.user where name like #{value}
</select>
③测试类
@Test
public void testGetUserLike(){
    //1.获取sqlSession对象
    SqlSession sqlSession= mybatis_utils.getSqlSession();
    //2.执行sql语句
    Mapper mapper=sqlSession.getMapper(Mapper.class);
    List<User> userList=mapper.getUserLike("%李%");
    for (User user : userList) {
        System.out.println(user);
    }
    //3.关闭sqlSession
    sqlSession.close();
}
2.在sql语句中拼接通配符 会引起sql注入
①接口方法
List<User> getUserLike(String name);
②编写sql语句
    <!--模糊查询-->
<select id="getUserLike" resultType="com.kuang.pojo.User">
    select * from mybatis.user where name like "%"#{value}"%"
</select>
③测试类
@Test
public void testGetUserLike(){
    //1.获取sqlSession对象
    SqlSession sqlSession= mybatis_utils.getSqlSession();
    //2.执行sql语句
    Mapper mapper=sqlSession.getMapper(Mapper.class);
    List<User> userList=mapper.getUserLike("李");
    for (User user : userList) {
        System.out.println(user);
    }
    //3.关闭sqlSession
    sqlSession.close();
}

分页的实现

1.使用limit实现分页(在SQL层面实现) 推荐使用
①接口方法
List<User> getUserByLimit(Map<String,Integer> map);
②编写sql语句
    <!--分页-->
<select id="getUserByLimit" parameterType="map" resultType="User">
    select * from mybatis.user limit #{startIndex},#{pageSize}
</select>
③测试类
@Test
public void testGetUserByLimit(){
    //1.获取sqlSession对象
    SqlSession sqlSession= mybatis_utils.getSqlSession();
    //2.执行sql语句
    UserMapper mapper=sqlSession.getMapper(UserMapper.class);
    HashMap<String,Integer> map=new HashMap<String,Integer>();
    map.put("startIndex",0);
    map.put("pageSize",2);
    List<User> userList=mapper.getUserByLimit(map);
    for (User user : userList) {
        System.out.println(user);
    }
    //3.关闭sqlSession
    sqlSession.close();
}
2.使用RowBounds实现分页(在Java代码层面实现) 了解

使用注解开发

注意啦注意啦！！！使用注解的话 不再需要UserMapper.xml配置文件
1.编写接口类
package com.kuang.dao;

import com.kuang.pojo.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface UserMapper {
    //查询所有数据
    @Select("select * from user")
    List<User> getUserList();
    //通过ID查询 sql语句中引用的就是@Param中设定的属性名
    @Select("select * from user where id=#{id} and name=#{name}")
    User getUserById(@Param("id") int id,@Param("name") String name);
    //添加(删除，更新)用户
    @Insert("insert into user(id,name,pwd) values(#{id},#{name},#{pwd})")
    int addUser(User user);
}
2.在mybatis-config.xml文件中绑定接口 不再需要注册UserMapper.xml
<!--绑定接口-->
<mappers>
    <mapper class="com.kuang.dao.UserMapper"></mapper>
    <!--如果有很多接口需要注册 可以写下面的-->
    <!--<mapper resource="com/kuang/dao/*Mapper.xml"></mapper>-->
</mappers>
3.编写测试类 测试类与之前没有区别
package com.kuang.dao;


import com.kuang.pojo.User;
import com.kuang.utils.mybatis_utils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class MapperTest {
    @Test
    public void testGetUserList(){
        //1.获取sqlSession对象
        SqlSession sqlSession= mybatis_utils.getSqlSession();
        //2.执行sql语句
        UserMapper mapper=sqlSession.getMapper(UserMapper.class);
        List<User> userList=mapper.getUserList();
        for (User user : userList) {
            System.out.println(user);
        }
        //3.关闭sqlSession
        sqlSession.close();
    }
    @Test
    public void testGetUserById(){
        //1.获取sqlSession对象
        SqlSession sqlSession= mybatis_utils.getSqlSession();
        //2.执行sql语句
        UserMapper mapper=sqlSession.getMapper(UserMapper.class);
        User user=mapper.getUserById(5,"李五");
        System.out.println(user);
        //3.关闭sqlSession
        sqlSession.close();
    }
    @Test
    public void testAddUser(){
        //1.获取sqlSession对象
        SqlSession sqlSession= mybatis_utils.getSqlSession();
        //2.执行sql语句
        UserMapper mapper=sqlSession.getMapper(UserMapper.class);//底层主要应用反射
        int res=mapper.addUser(new User(7,"墨染","element"));
        if(res>0){
            System.out.println("添加成功啦");
        }
        //3.关闭sqlSession
        sqlSession.close();
    }
}
4.工具类修改为如下 可以实现自动提交事务 增删改的时候无需手动提交
//获取SqlSession连接
public static SqlSession getSqlSession(){
    return sqlSessionFactory.openSession(true);
}
5.其他配置 实体类 工具类 核心配置文件与之前一样

多对一处理

• 搭建数据库表

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, '秦老师');

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;

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');

• 核心配置文件和工具类与之前相同 实体类如下

注意:实体类使用了Lombok插件 方便构造 但是具体项目中不建议使用
=============Student.java================
package com.kuang.pojo;

import lombok.Data;

@Data
public class Student {
    private int id;
    private String name;
    //学生需要关联一个老师
    private Teacher teacher;
}
=============Teacher.java================
package com.kuang.pojo;

import lombok.Data;

@Data
public class Teacher {
    private int id;
    private String name;
}

• DAO接口

=====================StudentMapper.java======================
package com.kuang.dao;

import com.kuang.pojo.Student;

import java.util.List;

public interface StudentMapper {
    //查询所有的学生信息 以及 对应的老师信息
    List<Student> getStudent();
    List<Student> getStudent2();
}
=====================TeacherMapper.java======================
package com.kuang.dao;

public interface TeacherMapper {

}

• 接口实现类

===================================================文件名: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.kuang.dao.StudentMapper">
    <!--需求:查询所有的学生信息 以及 对应的老师信息-->
    <!--===============================================按照查询嵌套处理(子查询)==========================================-->
    <!--
    思路:
        1.查询所有的学生信息
        2.根据查询出来的学生的tid,寻找对应的老师
        实际上还是在解决属性名和字段名不一致问题  所以使用resultMap
    -->
    <select id="getStudent" resultMap="StudentToTeacher">
        select * from mybatis.student;
    </select>
    <resultMap id="StudentToTeacher" type="Student">
        <!--简单属性-->
        <result property="id" column="id"></result>
        <result property="name" column="name"></result>
        <!--复杂属性 单独处理
        对象:association(多对一)
        集合:collection(一对多)
        -->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"></association>
    </resultMap>
    <select id="getTeacher" resultType="Teacher">
        select * from mybatis.teacher where id=#{tid};
    </select>
    <!--===============================================按照结果嵌套处理(联表查询)==========================================-->
    <select id="getStudent2" resultMap="StudentToTeacher2">
        select s.id sid,s.name sname,t.name tname,t.id tid
        from student s,teacher t
        where s.tid=t.id;
    </select>
    <resultMap id="StudentToTeacher2" type="Student">
        <result property="id" column="sid"></result>
        <result property="name" column="sname"></result>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"></result>
            <result property="id" column="tid"></result>
        </association>
    </resultMap>
</mapper>
===================================================文件名:TeacherMapper.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.kuang.dao.TeacherMapper">
</mapper>

• 测试

import com.kuang.dao.StudentMapper;
import com.kuang.pojo.Student;
import com.kuang.utils.mybatis_utils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class Testyk {
    @Test
    public void testGetStudent(){
        SqlSession sqlSession= mybatis_utils.getSqlSession();
        StudentMapper mapper=sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList=mapper.getStudent();
        for (Student student1 : studentList) {
            System.out.println(student1);
        }
        sqlSession.close();
    }
    @Test
    public void testGetStudent2(){
        SqlSession sqlSession= mybatis_utils.getSqlSession();
        StudentMapper mapper=sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList=mapper.getStudent2();
        for (Student student1 : studentList) {
            System.out.println(student1);
        }
        sqlSession.close();
    }
}

一对多处理

• 搭建数据库表 数据库使用多对一搭建的即可
• 核心配置文件和工具类与之前相同 实体类如下

注意:实体类使用了Lombok插件 方便构造 但是具体项目中不建议使用
=============Student.java================
package com.kuang.pojo;

import lombok.Data;

@Data
public class Student {
    private int id;
    private String name;
    private int tid;
}

=============Teacher.java================
package com.kuang.pojo;

import lombok.Data;

import java.util.List;

@Data
public class Teacher {
    private int id;
    private String name;
    //一个老师拥有多个学生
    private List<Student> studentList;
}

• DAO接口

=====================StudentMapper.java======================
package com.kuang.dao;

public interface StudentMapper {

}
=====================TeacherMapper.java======================
package com.kuang.dao;

import com.kuang.pojo.Teacher;
import org.apache.ibatis.annotations.Param;


public interface TeacherMapper {
    //获取老师
    //List<Teacher> getTeacher();
    //获取指定老师下所有学生的信息以及老师的信息
    Teacher getTeacher(@Param("tid") int id);
    Teacher getTeacher2(@Param("tid") int id);
}

• 接口实现类

===================================================文件名: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.kuang.dao.StudentMapper">

</mapper>
===================================================文件名:TeacherMapper.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.kuang.dao.TeacherMapper">
    <!--========================================按结果嵌套处理=========================================-->
    <select id="getTeacher" resultMap="TeacherToStudent">
        select s.id sid,s.name sname,t.name tname,t.id tid
        from student s,teacher t
        where s.tid=t.id and t.id=#{tid};
    </select>
    <resultMap id="TeacherToStudent" type="Teacher">
        <result property="id" column="tid"></result>
        <result property="name" column="tname"></result>
        <collection property="studentList" ofType="Student">
            <result property="id" column="sid"></result>
            <result property="name" column="sname"></result>
            <result property="tid" column="tid"></result>
        </collection>
    </resultMap>
    <!--========================================按查询嵌套处理=========================================-->
    <select id="getTeacher2" resultMap="TeacherToStudent2">
        select * from mybatis.teacher where id=#{tid};
    </select>
    <resultMap id="TeacherToStudent2" type="Teacher">
        <result property="id" column="id"></result>
        <result property="name" column="name"></result>
        <collection property="studentList" column="id" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId"></collection>
    </resultMap>
    <select id="getStudentByTeacherId" resultType="Student">
        select * from mybatis.student where tid=#{id};
    </select>
    <!--
    javaType 指定实体类中属性的类型 ArrayList->List
    ofType 指定映射到集合中的类型 也就是泛型的约束类型
    -->
</mapper>

• 测试

import com.kuang.dao.TeacherMapper;
import com.kuang.pojo.Teacher;
import com.kuang.utils.mybatis_utils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;


public class Testyk {
    @Test
    public void testGetTeacher(){
        SqlSession sqlSession= mybatis_utils.getSqlSession();
        TeacherMapper mapper=sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher=mapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }
    @Test
    public void testGetTeacher2(){
        SqlSession sqlSession= mybatis_utils.getSqlSession();
        TeacherMapper mapper=sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher=mapper.getTeacher2(1);
        System.out.println(teacher);
        sqlSession.close();
    }
}