【Mybatis学习】Mybatis级联之一对多

最新推荐文章于 2023-05-10 16:52:47 发布

炒熟的番茄

最新推荐文章于 2023-05-10 16:52:47 发布

阅读量333

点赞数

分类专栏： mybatis 文章标签： mybatis

本文链接：https://blog.csdn.net/qq_33560169/article/details/78494278

版权

mybatis 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

Mybatis级联之一对多

1.实体与表结构User/t_user、Book/t_book

package mybatis.domain;

import lombok.Getter;
import lombok.Setter;

import java.io.Serializable;
import java.util.List;

/**
 * @author wsz
 * @date 2017年11月8日20:04:14
 */
public class User implements Serializable{

    @Setter
    @Getter
    private int id;

    @Setter
    @Getter
    private String username;

    @Setter
    @Getter
    private String realName;

    @Setter
    @Getter
    private String password;

    @Setter
    @Getter
    private List<Book> books;

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", realName='" + realName + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `real_name` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;

package mybatis.domain;

import com.sun.org.glassfish.gmbal.Description;
import lombok.Getter;
import lombok.Setter;

import java.io.Serializable;
/**
 * @author wsz
 * @date 2017年11月8日20:04:54
 */
public class Book implements Serializable {

    @Description("关联外键")
    @Setter
    @Getter
    private String userId;

    @Description("主键")
    @Setter
    @Getter
    private int id;

    @Setter
    @Getter
    private String author;

    @Setter
    @Getter
    private String name;

    @Setter
    @Getter
    private String title;

    @Setter
    @Getter
    private double price;

    @Setter
    @Getter
    private String des;

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", author='" + author + '\'' +
                ", name='" + name + '\'' +
                ", title='" + title + '\'' +
                ", price=" + price +
                ", des='" + des + '\'' +
                '}';
    }
}

DROP TABLE IF EXISTS `t_book`;
CREATE TABLE `t_book` (
  `bid` int(11) NOT NULL AUTO_INCREMENT,
  `author` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `des` varchar(255) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`bid`),
  KEY `uid` (`user_id`),
  CONSTRAINT `uid` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;

2.mapp配置文件与接口方法

package mybatis.dao;

import mybatis.domain.User;

public interface UserMapper {

    User findById(int id);

    int insertUser(User user);

    int updateUser(User user);
}

<?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">
<mapper namespace="mybatis.dao.UserMapper">
    <resultMap id="userMap" type="mybatis.domain.User">
        <id property="id"           column="id"/>
        <result property="username" column="username"/>
        <result property="realName" column="real_name"/>
        <result property="password" column="password"/>
        <!--一对多级联-->
        <collection property="books" ofType="mybatis.domain.Book" column="user_id">
            <id property="id"            column="bid"/> <!--更改book表id为bid否则只能关联查询出一条信息 -->
            <result property="author"    column="author"/>
            <result property="name"      column="name"/>
            <result property="title"     column="title"/>
            <result property="price"     column="price"/>
            <result property="des"       column="des"/>
        </collection>
    </resultMap>

    <sql id="userSql" >
        id, username, real_name,password
    </sql>

    <select id="findById" parameterType="int" resultMap="userMap">
        select u.*,b.*
        from t_user u , t_book b
        where u.id = b.user_id and u.id = #{id}
    </select>

    <insert id="insertUser" parameterType="mybatis.domain.User" useGeneratedKeys="true" keyProperty="id">
        insert into t_user(id, username, real_name, password)
        values (#{id}, #{username}, #{realName},#{password});
    </insert>

    <!--利用trim也可用去掉逗号 -->
    <update id="updateUser" parameterType="mybatis.domain.User" >
        update t_user
        <trim prefix="set" suffixOverrides=",">
            <if test="username != null and username != ''">
                username = #{username},
            </if>
            <if test="realName != null and realName != ''">
                real_name = #{realName},
            </if>
            <if test="password != null and password != ''">
                password = #{password}
            </if>
        </trim>
        where id = #{id}
    </update>
</mapper>

package mybatis.dao;

import mybatis.domain.Book;

import java.util.List;

public interface BookMapper {

    Book findById(int id);

    int insertBook(Book book);

    int batchInsertBook(List<Book> books);

    int updateBook(Book book);
}

<?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">
<mapper namespace="mybatis.dao.BookMapper">
    <resultMap id="bookMap" type="mybatis.domain.Book">
        <id property="id"            column="bid"/> <!--更改book表id->bid否则只能关联查询出一条信息 -->
        <result property="author"    column="author"/>
        <result property="name"      column="name"/>
        <result property="title"     column="title"/>
        <result property="price"     column="price"/>
        <result property="des"       column="des"/>
        <association property="userId" resultMap="mybatis.dao.UserMapper.userMap"/>
    </resultMap>
    <sql id="bookSql">
        id, author, name, title, price, des
    </sql>

    <select id="findById" resultMap="bookMap" parameterType="int">
        select <include refid="bookSql"/>
        from t_book
        where id = #{id}
    </select>

    <insert id="insertBook" parameterType="mybatis.domain.Book" keyProperty="id" useGeneratedKeys="true">
        insert into t_book(id, author, name, title, price, des)
        values (#{id},#{author},#{name},#{title},#{price},#{des});
    </insert>

    <insert id="batchInsertBook">
        insert into t_book(bid, author, name, title, price, des,user_id)
        values
          <foreach collection="list" item="item" open="" separator="," close="" index="key">
              (#{item.id},#{item.author},#{item.name},#{item.title},#{item.price},#{item.des},#{item.userId})
          </foreach>
    </insert>

    <!--set末尾遇到逗号自动去掉 -->
    <update id="updateBook" parameterType="mybatis.domain.Book">
        update t_book
        set
        <if test="author != null and author !=''">
           author = #{author},
        </if>
        <if test="name != null and name !=''">
            name = #{name},
        </if>
        <if test="title != null and title !=''">
           title = #{title},
        </if>
        <if test="price != null and price !=''">
           price = #{price},
        </if>
        <if test="des != null and des !=''">
           des = #{des},
        </if>
        <if test="userId != null and userId !=''">
           user_id = #{userId}
        </if>
        where bid = #{id}
    </update>
</mapper>

3. 测试

package mybatis.controller;

import mybatis.dao.BookMapper;
import mybatis.dao.UserMapper;
import mybatis.domain.Book;
import mybatis.domain.User;
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 java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

public class Test {
    private static SqlSession session = null;
    public static void main(String[] args) throws IOException {
        String resource = "mybatis.xml";
        InputStream is = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        session = sqlSessionFactory.openSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        BookMapper bookMapper = session.getMapper(BookMapper.class);
        try{
//            insertUser(mapper);
//            findUser(mapper);
            update(mapper,bookMapper);
        }finally {
            if(session != null){
                session.close();
            }
        }
    }

    public  static void update(UserMapper mapper,BookMapper bookMapper){
        User user = mapper.findById(28);
        user.setUsername("28");
        int i = mapper.updateUser(user);

        List<Book> books = user.getBooks();
        if(!books.isEmpty()){//测试更新一条数据
            Book book = books.get(0);
            book.setName("翻车鱼");
            book.setUserId("29");
            bookMapper.updateBook(book);
        }
        session.commit();
        System.out.println(i);
    }

    public static  void findUser(UserMapper mapper){
        User user = mapper.findById(28);
        System.out.println(user.toString());
        List<Book> books = user.getBooks();
        for (Book book : books) {
            System.out.println(book.toString());
        }
    }
    public static  void insertUser(UserMapper mapper){
        Random random = new Random();
        User user = new User();
        user.setUsername(String.valueOf(random.nextInt()));
        user.setRealName(String.valueOf(random.nextInt()));
        user.setPassword(String.valueOf(random.nextInt()));
        int id = mapper.insertUser(user);

        List<Book> books = new ArrayList<Book>();
        for(int i = 0;i < 3;i++){
            Book b = new Book();
            b.setName("大白菜"+i);
            b.setTitle("系统"+i);
            b.setAuthor("ww"+i);
            b.setPrice(2.5D);
            b.setDes("东风谷");
            b.setUserId(String.valueOf(user.getId()));
            books.add(b);
        }
        BookMapper bookMapper = session.getMapper(BookMapper.class);
        int ids =  bookMapper.batchInsertBook(books);
        session.commit();
        System.out.println(id + "_"+ ids);
    }
}