07 mybatisのマルチテーブル操作

38368 ワード

mybatisのマルチテーブル操作
  • データ操作フロー
  • 1、データベース作成
  • 2、javaエンティティオブジェクト作成
  • 3、mapperファイル構成
  • 4、テスト:データ取得
  • 一、一対一
  • StudioオブジェクトにAddressオブジェクトを含む
  • studentオブジェクトassociation参照address
  • 1.1、データベーステーブルの作成
    create table mybatis_one2one_addresses(
    one2one_addr_id int(3) auto_increment not null primary key,
    one2one_street varchar(20) not null,
    one2one_city varchar(20) not null,
    one2one_state varchar(20) not null,
    one2one_zip varchar(10),
    one2one_country varchar(20));
    
    create table mybatis_one2one_students(
    one2one_stud_id int(3)auto_increment not null primary key,
    one2one_name varchar(20) not null,
    one2one_email varchar(20),
    one2one_dob date,
    one2one_phone varchar(15),
    one2one_addr_id int references addresses(one2one_addr_id));
    
    insert into mybatis_one2one_addresses(one2one_addr_id,one2one_street,one2one_city,one2one_state,one2one_zip,one2one_country) values(1,'redSt','kunshan','W','12345','china');
    insert into mybatis_one2one_addresses(one2one_addr_id,one2one_street,one2one_city,one2one_state,one2one_zip,one2one_country) values(2,'blueST','kunshan','W','12345','china');
    
    insert into mybatis_one2one_students(one2one_stud_id,one2one_name,one2one_email,one2one_phone,one2one_addr_id) values(1,'John','[email protected]','123-456-7890',1);
    insert into mybatis_one2one_students(one2one_stud_id,one2one_name,one2one_email,one2one_phone,one2one_addr_id) values(2,'Paul','[email protected]','111-222-3333',2);
  • 注:studentオブジェクトにaddressが含まれているため、テーブルが作成され、データ挿入はAddressテーブル
  • を先に処理する必要があります.
    1.2、javaエンティティオブジェクトの作成
  • get,setメソッド省略
  • public class One2OneAddress {
        private int addrId;
        private String addrStreet;
        private String addrCity;
        private String addrState;
        private String addrZip;
        private String addrCountry;
    }
    public class One2OneStudent {
        private int stuId;
        private String stuName;
        private String stuEmail;
        private String stuDob;
        private String stuPhone;
        private One2OneAddress stuAddress = new One2OneAddress();
    }
  • 注意:studentクラスでAddressプロパティが直接newするのは、toStringでのポインタ異常を防止するためです
  • 1.3、mapperマッピングファイルの作成:resource/mapper
    
    
    <mapper namespace="one2oneMapper">
        <resultMap id="one2oneAddress" type="com.demo.mybatis.one2one.One2OneAddress">
            <id property="addrId" column="one2one_addr_id"/>
            <result property="addrStreet" column="one2one_street"/>
            <result property="addrCity" column="one2one_city"/>
            <result property="addrState" column="one2one_state"/>
            <result property="addrZip" column="one2one_zip"/>
            <result property="addrCountry" column="one2one_country"/>
        resultMap>
    
        <resultMap id="one2oneStudent" type="com.demo.mybatis.one2one.One2OneStudent">
            <id property="stuId" column="one2one_stud_id"/>
            <result property="stuName" column="one2one_name"/>
            <result property="stuEmail" column="one2one_email"/>
            <result property="stuDob" column="one2one_dob"/>
            <result property="stuPhone" column="one2one_phone"/>
            <association property="stuAddress" resultMap="one2oneAddress"/>
        resultMap>
    
        <select id="selectStudent" parameterType="int" resultMap="one2oneStudent">
            select * from mybatis_one2one_students left outer join mybatis_one2one_addresses
            on mybatis_one2one_students.one2one_addr_id = mybatis_one2one_addresses.one2one_addr_id
            where one2one_stud_id = #{stuId}
        select>
    
        <select id="selectStudentShot" parameterType="int" resultMap="one2oneStudent">
            select * from mybatis_one2one_students student left outer join mybatis_one2one_addresses address
            on student.one2one_addr_id = address.one2one_addr_id
            where one2one_stud_id = #{stuId}
        select>
    
    mapper>

    1.3.1、構成説明:association property=“stuAddress”resultMap=“resultMap=”one 2 oneAddress”
  • association:has-one
  • を表す
  • property="stuAddress":javaエンティティオブジェクトの属性名
  • resultMap="one 2 oneAddress":参照idがone 2 oneAddressであることを示すマッピング構成
  • 1.3.2、構成id説明:selectStudio
  • mybatis_one2one_students student:mybatis_one2one_studentsデータベーステーブル略称student
  • student.one2one_addr_id = address.one2one_addr_id:studentテーブルのaddrIdからAddressテーブルの対応するid値
  • を取得する
    1.4、テスト:データベースからデータを取得する
        @Test
        public void selectStudent() {
            SqlSession session = SessionUtis.getSession();
            One2OneStudent student = session.selectOne("one2oneMapper.selectStudent", 1);
            System.out.println(student.toString());
        }
    
        @Test
        public void selectStudentShot() {
            SqlSession session = SessionUtis.getSession();
            One2OneStudent student = session.selectOne("one2oneMapper.selectStudentShot", 1);
            System.out.println(student.toString());
        }

    二、一対多
  • 1つのclassesオブジェクトに複数のpersonオブジェクトを含む
  • classesマッピングファイルcollectionでpersonオブジェクトを参照
  • 2.1、データベーステーブルの作成
    create table mybatis_one2many_person(
    one2many_person_id int(3) auto_increment not null primary key,
    one2many_name varchar(100),
    one2many_email varchar(100),
    one2many_phone varchar(15));
    
    create table mybatis_one2many_classes(
    one2many_classes_id int(3) auto_increment not null primary key,
    one2many_name varchar(100) not null,
    one2many_description varchar(512),
    one2many_start_date date ,
    one2many_end_date date ,
    one2many_person_ids int references mybatis_one2many_tutors (one2many_person_id));
    
    
    insert into mybatis_one2many_person
    (one2many_name,one2many_email,one2many_phone)
    values('test name','[email protected]','123-456-7890');
    
    insert into mybatis_one2many_person
    (one2many_name,one2many_email,one2many_phone)
    values('test name','[email protected]','111-222-3333');
    
    insert into mybatis_one2many_classes
    (one2many_name,one2many_description,one2many_person_ids) values
    ('JavaSE','JavaSE',1);
    
    insert into mybatis_one2many_classes
    (one2many_name,one2many_description,one2many_person_ids) values
    ('JavaEE','JavaEE',2);
    
    insert into mybatis_one2many_classes
    (one2many_name,one2many_description,one2many_person_ids) values
    ('MyBatis','MyBatis',1);
  • classesには複数のpersonが含まれており、テーブルが作成され、データ挿入にはpersonテーブルとpersonデータがあることを確認する必要があります
  • 2.2、javaエンティティの作成
  • get,setメソッド省略
  • public class One2manyPerson {
        private int personId;
        private String personName;
        private String personEmail;
        private String personPhone;
    }
    public class One2manyClasses {
        private int classId;
        private String className;
        private String classDescription;
        private Date startDate;
        private Date endDate;
        private List personList = new ArrayList();
    }
  • classesには複数のpersonが含まれており、集合方式で体現されている
  • 2.3、mapperマッピングファイルの作成
    2.3.1、方式一:直接関連オブジェクト
  • classes取得personデータ直接resultMap方式取得
  • "one2manyMapper">
        id="one2manyPerson" type="com.demo.mybatis.one2many.One2manyPerson">
            <id property="personId" column="one2many_person_id"/>
            <result property="personName" column="one2many_name"/>
            <result property="personEmail" column="one2many_email"/>
            <result property="personPhone" column="one2many_phone"/>
        
    
    
        id="one2manyClasses" type="com.demo.mybatis.one2many.One2manyClasses">
            <id property="classId" column="one2many_classes_id"/>
            <result property="className" column="one2many_name"/>
            <result property="classDescription" column="one2many_description"/>
            <result property="startDate" column="one2many_start_date"/>
            <result property="endDate" column="one2many_end_date"/>
            property="personList" resultMap="one2manyPerson"/>
        
    
        
    
    

    2.3.2、方式二:間接関連
  • classes関連personデータはsqlクエリー方式で取得
  • "one2manyMapper2">
        id="one2manyPerson" type="com.demo.mybatis.one2many.One2manyPerson">
            <id property="personId" column="one2many_person_id"/>
            <result property="personName" column="one2many_name"/>
            <result property="personEmail" column="one2many_email"/>
            <result property="personPhone" column="one2many_phone"/>
        
    
        
    
        id="one2manyClasses" type="com.demo.mybatis.one2many.One2manyClasses">
            <id property="classId" column="one2many_classes_id"/>
            <result property="className" column="one2many_name"/>
            <result property="classDescription" column="one2many_description"/>
            <result property="startDate" column="one2many_start_date"/>
            <result property="endDate" column="one2many_end_date"/>
            property="personList" column="one2many_person_ids" select="selectPerson"/>
        
    
        
    
    

    2.4、テスト:データ取得
        @Test
        public void selectClasses(){
            SqlSession session = SessionUtis.getSession();
          One2manyClasses classes =  session.selectOne("one2manyMapper.selectClasses",1);
            System.out.println(classes.toString());
        }
        @Test
        public void selectClasses2(){
            SqlSession session = SessionUtis.getSession();
            One2manyClasses classes = session.selectOne("one2manyMapper2.selectClasses",1);
            System.out.println(classes.toString());
        }
  • 採用方式1と方式2は同じ効果ですが、mapper書き方の違い
  • 三、多対多
  • 1つのcartオブジェクトが複数のstudentオブジェクトを含む
  • 1つのstudentオブジェクトが複数のcartオブジェクトを含む
  • cartにはstudentセットが含まれ、studentにはcartセットが含まれている
  • cartのidに対応するstudentのid
  • を含む中間テーブルを採用
    3.1、データベース表の作成
    create table mybatis_many2many_cart (
       many2many_cart_id int(3) auto_increment not null primary key,
       many2many_cart_code varchar(50) not null,
       many2many_cart_name varchar(50) not null);
    
    create table mybatis_many2many_student (
       many2many_student_id int(3) auto_increment not null primary key,
       many2many_name varchar(50) not null,
       many2many_gender varchar(50) ,
       many2many_major varchar(50) ,
       many2many_grade varchar(50));
    
    create table mybatis_many2many_other (
      many2many_other_id  int(3) auto_increment not null primary key,
      many2many_student_id int references mybatis_many2many_student(many2many_student_id),
      many2many_cart_id int references mybatis_many2many_cart(many2many_cart_id));
    
    
    insert into mybatis_many2many_cart
    (many2many_cart_name,many2many_cart_code) values
    ('JavaSE',1);
    insert into mybatis_many2many_cart
    (many2many_cart_name,many2many_cart_code) values
    ('JavaEE',2);
    insert into mybatis_many2many_cart
    (many2many_cart_name,many2many_cart_code) values
    ('MyBatis',1);
    
    insert into mybatis_many2many_student
    (many2many_name,many2many_gender,many2many_major,many2many_grade)
     values('John','johncom','1890','123-7890');
    insert into mybatis_many2many_student
    (many2many_name,many2many_gender,many2many_major,many2many_grade)
    values('Paul','paulcom','111-333','123-90');
    
    insert into mybatis_many2many_other(many2many_student_id,many2many_cart_id)values(1,1);
    insert into mybatis_many2many_other(many2many_student_id,many2many_cart_id)values(1,2);
    insert into mybatis_many2many_other(many2many_student_id,many2many_cart_id)values(1,3);
    insert into mybatis_many2many_other(many2many_student_id,many2many_cart_id)values(2,1);
    insert into mybatis_many2many_other(many2many_student_id,many2many_cart_id)values(2,2);
    insert into mybatis_many2many_other(many2many_student_id,many2many_cart_id)values(2,3);
  • 中間テーブルother作成およびデータ挿入に必要なcartおよびstudentの作成および挿入
  • 3.2、javaエンティティオブジェクトの作成
  • get,setメソッドを省略
  • public class Many2manyCart {
        private int cartId;
        private String carCode;
        private String cartName;
        private List studentList = new ArrayList();
    }
    public class Many2manyStudent {
        private int stuId;
        private String stuName;
        private String stuGender;
        private String stuMajor;
        private String stuGrade;
        private List cartList = new ArrayList();
    }
  • 複数のオブジェクトを集合で実現
  • 3.3、mapperプロファイルの作成
    "many2manyMapper">
    
        id="many2manyStudent" type="com.demo.mybatis.many2many.Many2manyStudent">
            <id property="stuId" column="many2many_student_id"/>
            <result property="stuName" column="many2many_name"/>
            <result property="stuGender" column="many2many_gender"/>
            <result property="stuMajor" column="many2many_major"/>
            <result property="stuGrade" column="many2many_grade"/>
        
        id="many2manyCart" type="com.demo.mybatis.many2many.Many2manyCart">
            <id property="cartId" column="many2many_cart_id"/>
            <result property="carCode" column="many2many_cart_code"/>
            <result property="cartName" column="many2many_cart_name"/>
        
    
        id="otherStudent" type="com.demo.mybatis.many2many.Many2manyStudent" extends="many2manyStudent">
            property="cartList" resultMap="many2manyCart"/>
        
    
        id="otherCart" type="com.demo.mybatis.many2many.Many2manyCart" extends="many2manyCart">
            property="studentList" resultMap="many2manyStudent"/>
        
    
        
    
    
        
    
    
  • otherCart:cartの中間テーブルを移動し、collectionを介してstudent
  • を導入する
  • selectCart:パラメータotherCartを返します.otherCartを複数のオブジェクトに再アセンブリする必要があります
  • 3.4、テスト:データ使用
        @Test
        public void selectStudent() {
            SqlSession session = SessionUtis.getSession();
            List student = session.selectList("many2manyMapper.selectStudent", 1);
            System.out.println(student.toString());
        }
    
        @Test
        public void selectCart() {
            SqlSession session = SessionUtis.getSession();
            List carts = session.selectList("many2manyMapper.selectCart", 1);
            System.out.println(carts.toString());
        }

    コード:
    https://github.com/brusion/brusion-code/tree/master/demo-java/04%20-%20demo%20-%20mybatis/mybatis-04