MyBatis高级 注解实现多表操作

一对一

环境准备

@Results：封装映射关系的父注解

Result[] value():定义了Result数组

 

@Result：封装映射关系的子注解。

column属性：查询出的表中字段名称

property属性：实体对象中的属性名称

javaType属性：被包含对象的数据类型

one属性：一对一查询固定属性

@one：一对一查询的注解

　select属性：指定调用某个接口中的方法

 

 

 card

package itheima.bean;

public class Card {
    private Integer id;     //主键id
    private String number;  //身份证号

    private Person p;       //所属人的对象

    public Card() {
    }

    public Card(Integer id, String number, Person p) {
        this.id = id;
        this.number = number;
        this.p = p;

    }

    public Integer getId() {
        return id;
    }

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

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public Person getP() {
        return p;
    }

    public void setP(Person p) {
        this.p = p;
    }

    @Override
    public String toString() {
        return "Card{" +
                "id=" + id +
                ", number='" + number + '\'' +
                ", p=" + p +
                '}';
    }
}

　　Person

package itheima.bean;

public class Person {
    private Integer id;     //主键id
    private String name;    //人的姓名
    private Integer age;    //人的年龄

    public Person() {
    }

    public Person(Integer id, String name, Integer age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Integer getAge() {
        return age;
    }

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

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

　　CardMapper

package itheima.one_to_one;

import itheima.bean.Card;
import itheima.bean.Person;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface CardMapper {
    //查询全部
    @Select("select * from card")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "number",property = "number"),
            @Result(
                    property = "p",         //被包含对象的变量名
                    javaType = Person.class,//被包含对象实际的数据类型
                    column = "pid",         //根据查询出的card表中的pid字段查询person表
                    /*
                    *   one,@one 一对一固定写法
                    *   select属性：指定调用哪个接口中的哪个方法
                    * */
                    one=@One(select = "itheima.one_to_one.PersonMapper.selectById")
            )
    })
    public abstract List<Card> selectAll();
}

　　PersonMapper

package itheima.one_to_one;

import itheima.bean.Person;
import org.apache.ibatis.annotations.Select;

public interface PersonMapper {
    //根绝id查询
    @Select("select * from student where id=#{id}")
    public abstract Person selectById(Integer id);
}

　　Test01

package itheima.one_to_one;

import itheima.bean.Card;
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.List;

public class Test01 {
    @Test
    public void selectAll() throws Exception {
        //1.加在核心配置文件
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");

        //2.获取SqlSession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //3.通过工厂对象获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        //4.获取StudentMapper接口的实现类对象
        CardMapper mapper = sqlSession.getMapper(CardMapper.class);
        //5.调用实现类对象中的方法，接受结果
        List<Card> list = mapper.selectAll();

        //6.处理结果
        for (Card card : list) {
            System.out.println(card);
        }

        //7.释放资源
        sqlSession.close();
        is.close();
    }
}

　　jdbc.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.23.129:3306/db2
username=root
password=root

　　log4j.properties

# Global logging configuration
# ERROR WARN INFO DEBUG
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

　　MyBatisConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!--MyBatis的DTD约束-->
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">

<!--configuration 核心根标签-->
<configuration>

    <!--引入数据库连接的配置文件-->
    <properties resource="jdbc.properties"/>

    <!--配置LOG4J-->
    <settings>
        <setting name="logImpl" value="log4j"/>
    </settings>

    <!--起别名-->
    <typeAliases>
        <package name="itheima.bean"/>
    </typeAliases>

    <!--environments配置数据库环境，环境可以有多个。default属性指定使用的是哪个-->
    <environments default="mysql">
        <!--environment配置数据库环境  id属性唯一标识-->
        <environment id="mysql">
            <!-- transactionManager事务管理。  type属性，采用JDBC默认的事务-->
            <transactionManager type="JDBC"></transactionManager>
            <!-- dataSource数据源信息   type属性 连接池-->
            <dataSource type="POOLED">
                <!-- property获取数据库连接的配置信息 -->
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>
    <!--配置映射关系-->
    <mappers>
        <package name="itheima"/>
    </mappers>

</configuration>

　　一对多

环境准备

@Results：封装映射关系的父注解

　Result[]value():定义了Result数组

@Result：封装映射关系的子注解。

  column属性：查询出的表中字段名称

  property属性：实体对象中的属性名称

  javaType属性：被包含对象的数据类型

  many属性：一对多查询固定属性

@Many：一对多查询的注解

  select属性：指定调用某个接口中的方法

bean目录下

Student

package itheima.bean;

import java.util.List;

public class Student {
    private Integer id; //主键id
    private String name; //学生姓名
    private Integer age; //学生年龄

    public Student() {
    }

    public Student(Integer id, String name, Integer age) {
        this.id = id;
        this.name = name;
        this.age = age;

    }


    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Integer getAge() {
        return age;
    }

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

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

　　Classes

package itheima.bean;

import java.util.List;

public class Classes {
    private Integer id;     //主键id
    private String name;    //班级名称

    private List<Student> students; //班级中所有学生对象

    public Classes() {
    }

    public Classes(Integer id, String name, List<Student> students) {
        this.id = id;
        this.name = name;
        this.students = students;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }

    @Override
    public String toString() {
        return "Classes{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", students=" + students +
                '}';
    }
}

　　one_to_many包下

ClassesMapper

package itheima.one_to_many;

import itheima.bean.Classes;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface ClassesMapper {
    //查询全部
    @Select("select * from classes")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "name",property = "name"),
            @Result(
                    property = "students",//被包含对象的变量名
                    javaType = List.class,//被包含对象的实际数据类型
                    column = "id",         //根据查询出的classes表中的id字段来查询student表
                    /*
                    *   many,@many 一对多查询的固定写法
                    * select属性：指定调用哪个接口中的哪个查询方法
                    * */
                    many = @Many(select ="itheima.one_to_many.StudentMapper.selectByCid")

            )
    })
    public abstract List<Classes> selecrAll();
}

　　StudentMapper

package itheima.one_to_many;

import itheima.bean.Student;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface StudentMapper {
    //根据cid查询student表
    @Select("select * from student where cid=#{cid}")
    public abstract List<Student> selectByCid(Integer cid);
}

　　Test01

package itheima.one_to_many;

import itheima.bean.Card;
import itheima.bean.Classes;
import itheima.bean.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 org.w3c.dom.ls.LSOutput;

import java.io.InputStream;
import java.util.List;
import java.util.SortedMap;

public class Test01 {
    @Test
    public void selectAll() throws Exception {
        //1.加在核心配置文件
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");

        //2.获取SqlSession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //3.通过工厂对象获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        //4.获取StudentMapper接口的实现类对象
        ClassesMapper mapper= sqlSession.getMapper(ClassesMapper.class);
        //5.调用实现类对象中的方法，接受结果
        List<Classes> list = mapper.selecrAll();

        //6.处理结果
        for (Classes cls : list) {
            System.out.println(cls.getId()+","+cls.getName());
            List<Student> students = cls.getStudents();
            for (Student student : students) {
                System.out.println("\t"+student);
            }
        }

        //7.释放资源
        sqlSession.close();
        is.close();
    }
}

　　

 

 多对多

环境准备

@result：封装映射关系的父注解

　　Result[] value():定义了Result数组

@Result：封装映射关系的子注解

　　column属性：查询出的表中字段名称

　　property属性：实体对象中的属性名称

　　javaType属性：被包含对象的数据类型

　　many属性：一对多查询固定属性

@Many：一对多查询的注解

　　select属性：指定调用某个接口中的方法

bean目录下 Course

package itheima.bean;

public class Course {
    private Integer id; //主键id
    private String name; //课程名称

    public Course() {
    }

    public Course(Integer id, String name) {
        this.id = id;
        this.name = name;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    @Override
    public String toString() {
        return "Course{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

　　Student

package itheima.bean;

import java.util.List;

public class Student {
    private Integer id; //主键id
    private String name; //学生姓名
    private Integer age; //学生年龄

    private List<Course> courses; //学生选择的课程集合
    public Student() {
    }

    public Student(Integer id, String name, Integer age, List<Course> courses) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.courses = courses;
    }

    public List<Course> getCourses() {
        return courses;
    }

    public void setCourses(List<Course> courses) {
        this.courses = courses;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Integer getAge() {
        return age;
    }

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

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

　　many_to_many包下CourseMapper

package itheima.many_to_many;

import itheima.bean.Course;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface CourseMapper {
    @Select("select c.id,c.name from stu_cr sc,course c where sc.cid=c.id and sc.sid=#{id}")
    public abstract List<Course> selectBySid(Integer id);
}

　　StudentMapper

package itheima.many_to_many;

import itheima.bean.Student;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface StudentMapper {
    //查询全部
    @Select("select distinct s.id,s.name,s.age from student s,stu_cr sc where sc.sid=s.id")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "name",property = "name"),
            @Result(column = "age",property = "age"),
            @Result(
                    property = "courses",//被包含对象的变量名
                    javaType = List.class,//被包含对象的实际数据类型
                    column = "id",          //根据查询出student表的id作为关联条件去查询中间表和课程表
                    /*
                    * many，@Many 一对多查询的固定写法
                    * select属性：指定调用哪个接口中的哪个查询方法
                    *
                    * */
                    many = @Many(select = "itheima.many_to_many.CourseMapper.selectBySid")
            )
    })
    public abstract List<Student> selectAll();
}

　　Test01

package itheima.many_to_many;

import itheima.bean.Classes;
import itheima.bean.Course;
import itheima.bean.Student;
import itheima.one_to_many.ClassesMapper;
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.List;

public class Test01 {
    @Test
    public void selectAll() throws Exception {
        //1.加在核心配置文件
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");

        //2.获取SqlSession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //3.通过工厂对象获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        //4.获取StudentMapper接口的实现类对象
        StudentMapper mapper= sqlSession.getMapper(StudentMapper.class);
        //5.调用实现类对象中的方法，接受结果
        List<Student> list = mapper.selectAll();

        //6.处理结果
        for (Student student : list) {
            System.out.println(student.getId()+","+student.getName()+","+student.getAge());
            List<Course> courses = student.getCourses();
            for (Course crs : courses) {
                System.out.println("\t"+crs);
            }
        }

        //7.释放资源
        sqlSession.close();
        is.close();
    }
}

　　数据库表

一对一

CREATE DATABASE db2;

USE db2;

CREATE TABLE person(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT
);
INSERT INTO person VALUES (NULL,'张三',23);
INSERT INTO person VALUES (NULL,'李四',24);
INSERT INTO person VALUES (NULL,'王五',25);

CREATE TABLE card(
	id INT PRIMARY KEY AUTO_INCREMENT,
	number VARCHAR(30),
	pid INT,
	CONSTRAINT cp_fk FOREIGN KEY (pid) REFERENCES person(id)
);
INSERT INTO card VALUES (NULL,'12345',1);
INSERT INTO card VALUES (NULL,'23456',2);
INSERT INTO card VALUES (NULL,'34567',3);

select c.id cid,number,pid NAME age from card c,person p where c.pid=p.id

　　一对多

CREATE TABLE classes(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
INSERT INTO classes VALUES (NULL,'黑马一班');
INSERT INTO classes VALUES (NULL,'黑马二班');


CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT,
	cid INT,
	CONSTRAINT cs_fk FOREIGN KEY (cid) REFERENCES classes(id)
);
INSERT INTO student VALUES (NULL,'张三',23,1);
INSERT INTO student VALUES (NULL,'李四',24,1);
INSERT INTO student VALUES (NULL,'王五',25,2);
INSERT INTO student VALUES (NULL,'赵六',26,2);


select c.id cid,c.name cname,s.id sid,s.name sname,s.age sage from classes c,student s where c.id=s.cid

　　多对多

CREATE TABLE course(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
INSERT INTO course VALUES (NULL,'语文');
INSERT INTO course VALUES (NULL,'数学');


CREATE TABLE stu_cr(
	id INT PRIMARY KEY AUTO_INCREMENT,
	sid INT,
	cid INT,
	CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id),
	CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)
);
INSERT INTO stu_cr VALUES (NULL,1,1);
INSERT INTO stu_cr VALUES (NULL,1,2);
INSERT INTO stu_cr VALUES (NULL,2,1);
INSERT INTO stu_cr VALUES (NULL,2,2);

select sc.sid,s.name sname,s.age sage,sc.cid,c.name cname from student s,course c,stu_cr sc where sc.sid=s.id and sc.cid=c.id

　　注解多表操作小结

@Result：封装映射关系的父注解

　　Result[]value():定义了Result数组

@Result：封装映射关系的子注解

　　column属性：查询出的表中字段名称

　　property属性：实体对象中的属性名称

　　javaType属性：被包含对象的数据属性

　　one属性：一对一查询固定属性

　　many属性：一对多查询固定属性，多对多

@One：一对一查询的注解

select属性：指定调用某个接口中的方法

@Many：一对多，多对多的注解

select属性：指定调用某个接口中的方法