MyBatis_2

目录

• 使用接口结合xml文件
  • 数据库表与实体类创建
  • Users的操作类
  • Users映射文件
  • 测试
• 解决字段与属性不匹配（以Order表为例）
  • 数据库表与实体类创建
  • 通过给查询字段起别名，要求别名与属性名一致
  • 使用resultMap标签来定义实体类与字段之间的关系
• 实现链表查询【根据班级ID查询班级信息(带有老师信息)】
  • 数据库表与实体类创建
  • 定义 sql 映射文件 ClassMapper.xml
  • 测试
• 实现一对多关联【根据 classId 查询对应的班级信息,包括学生,老师】
  • 数据库表与实体类创建
  • 定义 sql 映射文件 ClassMapper.xml
  • 测试
• $与#的区别

使用接口结合xml文件

数据库表与实体类创建

create database mybatis;
use mybatis;
CREATE TABLE users(id INT PRIMARY KEY AUTO_INCREMENT, NAME
VARCHAR(20), age INT);
INSERT INTO users(NAME, age) VALUES('Tom', 12);
INSERT INTO users(NAME, age) VALUES('Jack', 11);

public class User {
    private int id;
    private String name;
    private int age;
    //get,set 方法 无参与有参的构造方法创建
}

Users的操作类

public interface UsersDao{
    public Users getUser(int id)
}

Users映射文件

新建映射文件UsersMapper

<?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">
//com.huangwei.lb包名
<mapper namespace="com.huangwei.lb.dao.UsersDao">
    <select id="getUser" paramterType="int" resultType="com.huangwei.lb.bean.Users">
        select * from users where id = #{id}
    </select>
</mapper>

测试

新建一个测试类

//com.huangwei.lb包名
class UsersTest{
    static SqlSession session = null;
    final String str = "com.huangwei.lb.mapper.UsersMapper.";
    static UsersDao usesDao = null;
    @BeforeAll
    static void setUpBeforeClass() throws Exception {
          Reader reader = Resources.getResourceAsReader("config.xml");
          SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
          session = sessionFactory.openSession();
          usersDao = session.getMapper(UsersDao.class);
     }
    @Test
    void testFindAll(){
        Users users = usersDao.getUser(1);
    }
}

解决字段与属性不匹配（以Order表为例）

数据库表与实体类创建

CREATE TABLE orders(
order_id INT PRIMARY KEY AUTO_INCREMENT,
order_no VARCHAR(20),
order_price FLOAT
);
INSERT INTO orders(order_no, order_price) VALUES('aaaa', 23);
INSERT INTO orders(order_no, order_price) VALUES('bbbb', 33);
INSERT INTO orders(order_no, order_price) VALUES('cccc', 22);

public class Order {
    private int id;
    private String orderNo;
    private float price;
   //get,set 方法 无参与有参的构造方法创建
}

通过给查询字段起别名，要求别名与属性名一致

<select id="selectOrder" parameterType="int" resultType="_Order">
    select order_id id, order_no orderNo,order_price price from orders where order_id=#{id}
</select>

使用resultMap标签来定义实体类与字段之间的关系

<select id="selectOrderResultMap" parameterType="int" resultMap="orderResultMap">
    select * from orders where order_id=#{id}
</select>
<resultMap type="_Order" id="orderResultMap">
    <id property="id" column="order_id"/>
    <result property="orderNo" column="order_no"/>
    <result property="price" column="order_price"/>
</resultMap>

实现链表查询【根据班级ID查询班级信息(带有老师信息)】

数据库表与实体类创建

CREATE TABLE teacher(
t_id INT PRIMARY KEY AUTO_INCREMENT,
t_name VARCHAR(20)
);
CREATE TABLE class(
c_id INT PRIMARY KEY AUTO_INCREMENT,
c_name VARCHAR(20),
teacher_id INT
);
ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id)
REFERENCES teacher(t_id);
INSERT INTO teacher(t_name) VALUES('LS1');
INSERT INTO teacher(t_name) VALUES('LS2');
INSERT INTO class(c_name, teacher_id) VALUES('bj_a', 1);
INSERT INTO class(c_name, teacher_id) VALUES('bj_b', 2);

public class Teacher {
    private int id;
    private String name;
//get,set 方法 无参与有参的构造方法创建
}
public class Classes {
    private int id;
    private String name;
    private Teacher teacher;
//get,set 方法 无参与有参的构造方法创建
}

定义 sql 映射文件 ClassMapper.xml

方法一：通过联表查询

<select id="getClass" parameterType="int" resultMap="ClassResultMap">
    select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
</select>
<resultMap type="_Classes" id="ClassResultMap">
    <id property="id" column="c_id"/>
    <result property="name" column="c_name"/>
    <association property="teacher" column="teacher_id" javaType="_Teacher">
        <id property="id" column="t_id"/>
        <result property="name" column="t_name"/>
    </association>
</resultMap>

方法二：通过嵌套查询

<select id="getClass2" parameterType="int" resultMap="ClassResultMap2">
    select * from class where c_id=#{id}
</select>
<resultMap type="_Classes" id="ClassResultMap2">
    <id property="id" column="c_id"/>
    <result property="name" column="c_name"/>
    <association property="teacher" column="teacher_id" javaType="_Teacher" select="getTeacher"></association>
</resultMap>
<select id="getTeacher" parameterType="int" resultType="_Teacher">
    SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
</select>

测试

@Test
public void testOO() {
SqlSession sqlSession = factory.openSession();
Classes c = sqlSession.selectOne("com.atguigu.day03_mybatis.test5.OOMapper.getClass",1);
System.out.println(c);
}
@Test
public void testOO2() {
SqlSession sqlSession = factory.openSession();
Classes c =
sqlSession.selectOne("com.atguigu.day03_mybatis.test5.OOMapper.getClass2", 1);
System.out.println(c);
}

实现一对多关联【根据 classId 查询对应的班级信息,包括学生,老师】

数据库表与实体类创建

CREATE TABLE student(
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name VARCHAR(20),
class_id INT
);
INSERT INTO student(s_name, class_id) VALUES('xs_A', 1);
INSERT INTO student(s_name, class_id) VALUES('xs_B', 1);
INSERT INTO student(s_name, class_id) VALUES('xs_C', 1);
INSERT INTO student(s_name, class_id) VALUES('xs_D', 2);
INSERT INTO student(s_name, class_id) VALUES('xs_E', 2);
INSERT INTO student(s_name, class_id) VALUES('xs_F', 2);

public class Student {
    private int id;
    private String name;
    //get,set 方法 无参与有参的构造方法创建
}
public class Classes {
    private int id;
    private String name;
    private Teacher teacher;
    private List<Student> students;
    //get,set 方法 无参与有参的构造方法创建
}

定义 sql 映射文件 ClassMapper.xml

**方法一：联表查询

<select id="getClass3" parameterType="int" resultMap="ClassResultMap3">
    select * from class c, teacher t,student s where c.teacher_id=t.t_id and c.C_id=s.class_id and c.c_id=#{id}
</select>
<resultMap type="_Classes" id="ClassResultMap3">
    <id property="id" column="c_id"/>
    <result property="name" column="c_name"/>
    <association property="teacher" column="teacher_id" javaType="_Teacher">
        <id property="id" column="t_id"/>
        <result property="name" column="t_name"/>
    </association>
<!-- ofType 指定 students 集合中的对象类型 -->
    <collection property="students" ofType="_Student">
        <id property="id" column="s_id"/>
        <result property="name" column="s_name"/>
    </collection>
</resultMap>

**方法二：嵌套查询

<select id="getClass4" parameterType="int" resultMap="ClassResultMap4">
    select * from class where c_id=#{id}
</select>
<resultMap type="_Classes" id="ClassResultMap4">
    <id property="id" column="c_id"/>
    <result property="name" column="c_name"/>
    <association property="teacher" column="teacher_id" javaType="_Teacher" select="getTeacher2"></association>
    <collection property="students" ofType="_Student" column="c_id" select="getStudent"></collection>
</resultMap>
<select id="getTeacher2" parameterType="int" resultType="_Teacher">
    SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
</select>
<select id="getStudent" parameterType="int" resultType="_Student">
    SELECT s_id id, s_name name FROM student WHERE class_id=#{id}
</select>

测试

@Test
public void testOM() {
    SqlSession sqlSession = factory.openSession();
    Classes c = sqlSession.selectOne("com.atguigu.day03_mybatis.test5.OOMapper.getClass3",1);
    System.out.println(c);
}
@Test
public void testOM2() {
    SqlSession sqlSession = factory.openSession();
    Classes c = sqlSession.selectOne("com.atguigu.day03_mybatis.test5.OOMapper.getClass4",1);
    System.out.println(c);
}

$与#的区别

"$"中的变量就是直接替换成值的，实际用于字符串拼接。
一般用于传入数据库对象
"#" 则是根据变量的类型来替换
一般用于传入增删改查的where条件

在同时可用#和$是，建议使用#