mybatis笔记-2-xml（一对一，一对多，多对多）

一对一：
实体

package com.entity.oneToOne;import java.io.Serializable;public class Person implements Serializable{    private Integer id;    private String name;    private String sex;    private Integer age;    private Card card;    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 String getSex() {        return sex;    }    public void setSex(String sex) {        this.sex = sex;    }    public Integer getAge() {        return age;    }    public void setAge(Integer age) {        this.age = age;    }    public Card getCard() {        return card;    }    public void setCard(Card card) {        this.card = card;    }}

package com.entity.oneToOne;import java.io.Serializable;public class Card implements Serializable{    private Integer id;    private String code;    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getCode() {        return code;    }    public void setCode(String code) {        this.code = code;    }}

对应的表

CREATE TABLE `tb_card` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `code` varchar(100) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `tb_person` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(100) DEFAULT NULL,  `sex` varchar(100) DEFAULT NULL,  `age` int(11) DEFAULT NULL,  `card_id` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `card_id` (`card_id`),  CONSTRAINT `tb_person_ibfk_1` FOREIGN KEY (`card_id`) REFERENCES `tb_card` (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

对应的xml

    <!-- oneToOne -->    <select id="selectCardById" parameterType="int"    resultType="com.entity.oneToOne.Card">        select * from tb_card where id = #{id}    </select>    <select id="selectPersonById" parameterType="int"        resultMap="personMapper">        select * from tb_person where id = #{id}    </select>    <!-- 映射Person对象的resultMap -->    <resultMap type="com.entity.oneToOne.Person" id="personMapper">        <id property="id" column="id"/>        <result property="name" column="name"/>        <result property="sex" column="sex"/>        <result property="age" column="age"/>        <!-- 一对一映射 -->        <association property="card" column="card_id"        select="com.entity.oneToOne.relationMapping.selectCardById" javaType="com.entity.oneToOne.Card"        ></association>    </resultMap>

总的xml，下面都会由这个总的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>    <settings>        <setting name="logImpl" value="LOG4J"/>    </settings>    <!--  懒加载     <settings>        <setting name="lazyLoadingEnabled" value="true"/>        <setting name="aggressiveLazyLoading" value="false"/>    </settings> -->    <environments default="development">        <environment id="development">            <transactionManager type="JDBC" />            <!-- 配置数据库连接信息 -->            <dataSource type="POOLED">                <property name="driver" value="com.mysql.jdbc.Driver" />                <property name="url" value="jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=utf-8" />                <property name="username" value="root" />                <property name="password" value="caoxuekun" />            </dataSource>        </environment>    </environments>    <mappers>         <!-- 注册userMapper.xml文件，          userMapper.xml位于me.gacl.mapping这个包下，所以resource写成me/gacl/mapping/userMapper.xml-->         <mapper resource="usersMapper.xml"/>         <mapper resource="relationMapping.xml"/>     </mappers></configuration>

测试操作代码

        // mybatis的配置文件                String resource = "conf.xml";                // 使用类加载器加载mybatis的配置文件（它也加载关联的映射文件）                InputStream is = Test1.class.getClassLoader().getResourceAsStream(resource);                // 构建sqlSession的工厂                SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);                // 使用MyBatis提供的Resources类加载mybatis的配置文件（它也加载关联的映射文件）                // Reader reader = Resources.getResourceAsReader(resource);                // 构建sqlSession的工厂                // SqlSessionFactory sessionFactory = new                // SqlSessionFactoryBuilder().build(reader);                // 创建能执行映射文件中sql的sqlSession                SqlSession session = sessionFactory.openSession();                return session;    }    //oneToOne    public static void oneToOne(){        SqlSession session = getSqlSession();        Person person = session.selectOne("com.entity.oneToOne.relationMapping.selectPersonById",1);        session.commit();        session.close();        System.out.println(person.getId()+"=="+person.getName()+"==="+person.getSex()+"==="+person.getAge()+"===="+person.getCard().getCode());    }

---

一对多
实体：

package com.entity.oneToMany;import java.io.Serializable;import java.util.List;public class Clazz implements Serializable{    private Integer id;//班级id    private String code;//班级编号    private String name;//班级名称    private List<Student> students;    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getCode() {        return code;    }    public void setCode(String code) {        this.code = code;    }    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;    }}

package com.entity.oneToMany;public class Student {    private Integer id;    private String name;    private String sex;    private Integer age;    private Clazz clazz;    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 String getSex() {        return sex;    }    public void setSex(String sex) {        this.sex = sex;    }    public Integer getAge() {        return age;    }    public void setAge(Integer age) {        this.age = age;    }    public Clazz getClazz() {        return clazz;    }    public void setClazz(Clazz clazz) {        this.clazz = clazz;    }}

数据库：

//一对多create table tb_clazz(id int PRIMARY key auto_increment,code VARCHAR(100),name VARCHAR(100));create table tb_student(id int PRIMARY key auto_increment,name VARCHAR(100),sex VARCHAR(18),age int,clazz_id int,FOREIGN key(clazz_id) REFERENCES tb_clazz(id));

xml配置：

<!-- oneToMany -->    <select id="selectClazzById" parameterType="int"         resultMap="clazzResultMap">        select * from tb_clazz where id = #{id}    </select>    <!-- 映射Clazz 对象的resultMap -->    <resultMap type="com.entity.oneToMany.Clazz" id="clazzResultMap">        <id property="id" column="id"/>        <result property="code" column="code"/>        <result property="name" column="name"/>        <!--             一对多关联            会使用column的参数执行           -->        <collection property="students" javaType="ArrayList"            column="id" ofType="com.entity.oneToMany.Student"            select="com.entity.oneToOne.relationMapping.selectStudentByClazzId" >            <id property="id" column="id"/>            <result property="name" column="name"/>            <result property="sex" column="sex"/>            <result property="age" column="age"/>        </collection>    </resultMap>        <select id="selectStudentById"         parameterType="com.entity.oneToMany.Student"        resultMap="studentResultMap">        select * from tb_clazz c,tb_student s        where c.id = s.clazz_id        and s.id = #{id}    </select>    <select id="selectStudentByClazzId" parameterType="int"        resultMap="studentResultMap">        select * from tb_student where clazz_id = #{id}    </select>    <resultMap type="com.entity.oneToMany.Student" id="studentResultMap">        <id property="id" column="id"/>        <result property="name" column="name"/>        <result property="sex" column="sex"/>        <result property="age" column="age"/>        <!-- 多对一关联 -->        <association property="clazz" javaType="com.entity.oneToMany.Clazz">            <id property="id" column="id"/>             <result property="code" column="code"/>            <result property="name" column="name"/>        </association>    </resultMap>

测试操作代码

    public static SqlSession getSqlSession(){        // mybatis的配置文件                String resource = "conf.xml";                // 使用类加载器加载mybatis的配置文件（它也加载关联的映射文件）                InputStream is = Test1.class.getClassLoader().getResourceAsStream(resource);                // 构建sqlSession的工厂                SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);                // 使用MyBatis提供的Resources类加载mybatis的配置文件（它也加载关联的映射文件）                // Reader reader = Resources.getResourceAsReader(resource);                // 构建sqlSession的工厂                // SqlSessionFactory sessionFactory = new                // SqlSessionFactoryBuilder().build(reader);                // 创建能执行映射文件中sql的sqlSession                SqlSession session = sessionFactory.openSession();                return session;    }    //oneToMany    public static void oneToMany(){        SqlSession session = getSqlSession();        Clazz clazz = session.selectOne("com.entity.oneToOne.relationMapping.selectClazzById",1);        session.close();        session.commit();        System.out.println("班级："+clazz.getId()+"==="+clazz.getCode()+"==="+clazz.getName());        for(Student s : clazz.getStudents()){            System.out.println("学生："+s.getId()+"==="+s.getName()+"==="+s.getSex());        }    }

---

多对多
实体

package com.entity.manyToMany;import java.io.Serializable;import java.util.List;public class Article implements Serializable{    private Integer id;//商品id    private String name;//商品名称    private Double price;//商品价格    private String remark;//商品描述    private List<Order> orders;    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 Double getPrice() {        return price;    }    public void setPrice(Double price) {        this.price = price;    }    public String getRemark() {        return remark;    }    public void setRemark(String remark) {        this.remark = remark;    }    public List<Order> getOrders() {        return orders;    }    public void setOrders(List<Order> orders) {        this.orders = orders;    }}

package com.entity.manyToMany;import java.io.Serializable;import java.util.List;public class Order implements Serializable {    private Integer id;//订单id    private String code;//订单编号    private Double total;//订单总金额    private User user;    private List<Article> articles;//一个订单可包含多种商品    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getCode() {        return code;    }    public void setCode(String code) {        this.code = code;    }    public Double getTotal() {        return total;    }    public void setTotal(Double total) {        this.total = total;    }    public User getUser() {        return user;    }    public void setUser(User user) {        this.user = user;    }    public List<Article> getArticles() {        return articles;    }    public void setArticles(List<Article> articles) {        this.articles = articles;    }}

package com.entity.manyToMany;import java.io.Serializable;import java.util.List;public class User implements Serializable{    private Integer id;    private String username;    private String loginname;    private String password;    private String phone;    private String address;//收获地址    private List<Order> orders;//订单    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getUsername() {        return username;    }    public void setUsername(String username) {        this.username = username;    }    public String getLoginname() {        return loginname;    }    public void setLoginname(String loginname) {        this.loginname = loginname;    }    public String getPassword() {        return password;    }    public void setPassword(String password) {        this.password = password;    }    public String getPhone() {        return phone;    }    public void setPhone(String phone) {        this.phone = phone;    }    public String getAddress() {        return address;    }    public void setAddress(String address) {        this.address = address;    }    public List<Order> getOrders() {        return orders;    }    public void setOrders(List<Order> orders) {        this.orders = orders;    }}

数据库

//多对多create table tb_user(id int PRIMARY key auto_increment,username VARCHAR(100),loginname varchar(100),password VARCHAR(100),phone varchar(18));create table tb_article(id int primary key auto_increment,name VARCHAR(100),price double,remark VARCHAR(18));create table tb_order(id int primary key auto_increment,code VARCHAR(180),total double,user_id int,FOREIGN key(user_id) REFERENCES tb_user(id));//tb_order和tb_article的中间表create table tb_item(order_id int,article_id int,amout int,PRIMARY key(order_id,article_id),FOREIGN key(order_id) REFERENCES tb_order(id),FOREIGN key(article_id) REFERENCES tb_article(id));

xml配置

<!--     com.entity.oneToOne.relationMapping     -->    <!-- manyToMany -->    <select id="selectUserById" parameterType="int" resultMap="userResultMap">        select * from tb_user where id = #{id}    </select>    <resultMap type="com.entity.manyToMany.User" id="userResultMap">        <id property="id" column="id"/>        <result property="username" column="username"/>        <result property="loginname" column="loginname"/>        <result property="password" column="password"/>        <result property="phone" column="phone"/>        <result property="address" column="address"/>        <!-- 一对多关联映射：collection -->        <collection property="orders" javaType="ArrayList"            column="id" ofType="com.entity.manyToMany.User"            select="com.entity.oneToOne.relationMapping.selectOrderById">            <id property="id" column="id"/>            <result property="code" column="code"/>            <result property="total" column="total"/>        </collection>    </resultMap>    <resultMap type="com.entity.manyToMany.Order" id="orderResultMap">        <id property="id" column="oid"/>        <result property="code" column="code"/>        <result property="total" column="total"/>        <!-- 多对一关联映射：association -->        <association property="user" javaType="com.entity.manyToMany.User">            <id property="id" column="id"/>            <result property="username" column="username"/>            <result property="loginname" column="loginname"/>            <result property="password" column="password"/>            <result property="phone" column="phone"/>            <result property="address" column="address"/>        </association>        <!-- 多对多映射的关键：collection -->        <collection property="articles" javaType="ArrayList"            column="oid" ofType="com.entity.manyToMany.Article"            select="com.entity.oneToOne.relationMapping.selectArticleByOrderId">            <id property="id" column="id"/>            <result property="name" column="name"/>            <result property="price" column="price"/>            <result property="remark" column="remark"/>        </collection>    </resultMap>    <!-- 注意，如果查询出来的列同名，例如tb_user表的id和tb_order表的id都是id，同名，则需要使用别名区分 -->    <select id="selectOrderById" parameterType="int" resultMap="orderResultMap">        select u.*,o.id as oid,code,total,user_id from        tb_user u,tb_order o where u.id = o.user_id        and o.id = #{id}    </select>    <!-- 根据user id查询订单 -->    <select id="selectOrderByUserId" parameterType="int" resultType="com.entity.manyToMany.Order">        select * from tb_order where user_id = #{id}    </select>    <select id="selectArticleByOrderId" parameterType="int"        resultType="com.entity.manyToMany.Article">        select * from tb_article where id IN(            select article_id from tb_item where order_id = #{id}        )       </select>

测试代码：

    public static SqlSession getSqlSession(){        // mybatis的配置文件                String resource = "conf.xml";                // 使用类加载器加载mybatis的配置文件（它也加载关联的映射文件）                InputStream is = Test1.class.getClassLoader().getResourceAsStream(resource);                // 构建sqlSession的工厂                SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);                // 使用MyBatis提供的Resources类加载mybatis的配置文件（它也加载关联的映射文件）                // Reader reader = Resources.getResourceAsReader(resource);                // 构建sqlSession的工厂                // SqlSessionFactory sessionFactory = new                // SqlSessionFactoryBuilder().build(reader);                // 创建能执行映射文件中sql的sqlSession                SqlSession session = sessionFactory.openSession();                return session;    }//manyToMany    public static void manyToMany(){        SqlSession session = getSqlSession();        System.out.println("******一对多*******");        //一对多        User user = session.selectOne("com.entity.oneToOne.relationMapping.selectUserById",2);        System.out.println("用户："+user.getId()+"===="+user.getLoginname()+"==="+user.getUsername()+"==="+user.getPassword()+"=="+user.getPassword());        for(Order o:user.getOrders()){            System.out.println("订单："+o.getId()+"==="+o.getCode()+"==="+o.getTotal());        }        System.out.println("\r\n******多对多********\r\n");        //多对多        Order order = session.selectOne("com.entity.oneToOne.relationMapping.selectOrderById",1);        for(Article a : order.getArticles()){            System.out.println("订单id为1时的商品："+a.getId()+"==="+a.getName()+"==="+a.getRemark());        }        session.commit();        session.close();    }