【Mybatis学习】Mybatis级联之一对多
来源:互联网 发布:日有所思夜有所梦 知乎 编辑:程序博客网 时间:2024/05/21 09:30
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); }}
4. 总结
一对多级联以使用collection、association关键字实现。
注意:关联表的数据库主键id需要不同,否则只能查询出最多一条的关联信息。表主键需要自增长属性。
本人实现工具为IDEA
GitHub: https://github.com/BeHappyWsz/mybatisOneToMany.git
云盘:http://pan.baidu.com/s/1qYDQlkw 密码:6bc1
阅读全文
0 0
- 【Mybatis学习】Mybatis级联之一对多
- mybatis级联之一对多的关系
- 【MyBatis学习09】高级映射之一对多查询
- 【MyBatis学习09】高级映射之一对多查询
- 【MyBatis学习09】高级映射之一对多查询
- Mybatis学习(08)-高级映射之一对多查询
- 【MyBatis学习09】高级映射之一对多查询
- 【MyBatis学习笔记】系列之一:MyBatis多对多双向关联
- mybatis学习笔记(十一)多对多关联查询/级联操作
- MyBatis的ResultMaps之一对多关系
- 【Mybatis】---高级映射之一对多查询
- MyBatis之一对多关联查询
- (5)MyBatis之一对多关联
- MyBatis 关联映射之一对多
- MyBatis的ResultMaps之一对多关系
- mybatis之一对多查询实例
- mybatis之一对多映射查询(十一)
- MyBatis高级映射之一对多查询
- Luogu 3371(dijkstra堆优化)
- HELLO,21
- 浅谈几种服务器端模型——同步阻塞迭代
- Linux环境下mosquitto的安装及部分问题解决方法
- 【模板】最长公共子序列
- 【Mybatis学习】Mybatis级联之一对多
- 【51nod】1012 最小公倍数LCM
- 每日一练10.31
- Java面向对象(1)
- nodemon文件修改后自动运行node
- [App] rhel7 下 安装 JBoss7
- Android架构图 (5层)
- 8queen(稍后补)
- 使用GO开发桌面GUI程序