mysql collection使用_mybatis中association和collection的使用

在写xml配置文件中的sql语句时，需要先去数据库工具进行查询，将查询之后的结果来写代码，比较不容易混淆。而且，查询之后的结果的字段名对应resultMap中或标签中的column属性

一、两边查询：association一对一

association标签用于一对一关系

association的作用是：两张表的关联查询，将另一张表的字段关联过来，然后一起映射到实体类中

例子：作者和博客的关系：一对一(假设每个作者只能有一个博客)

author表和blog表.png

两表一对一查询结果.png

进行两表查询时，我们先在数据库管理工具上进行查询，然后将查询后的表与java代码进行映射

1. 实体类

Author.java

public class Author {

private Integer id;

private String authorName;

public Integer getId() {

return id;

}

public void setId(Integer id) {

this.id = id;

}

public String getAuthorName() {

return authorName;

}

public void setAuthorName(String authorName) {

this.authorName = authorName;

}

@Override

public String toString() {

return "Author{" +

"id=" + id +

", authorName='" + authorName + '\'' +

'}';

}

}

Blog.java

public class Blog {

private Integer id;

private String blogName;

private Author author;

public Integer getId() {

return id;

}

public void setId(Integer id) {

this.id = id;

}

public String getBlogName() {

return blogName;

}

public void setBlogName(String blogName) {

this.blogName = blogName;

}

public Author getAuthor() {

return author;

}

public void setAuthor(Author author) {

this.author = author;

}

@Override

public String toString() {

return "Blog{" +

"id=" + id +

", blogName='" + blogName + '\'' +

", author=" + author +

'}';

}

}

2. 配置文件

jdbc.properties

jdbc.driver = com.mysql.jdbc.Driver

jdbc.url = jdbc:mysql://localhost:3306/blog?serverTimezone=UTC

jdbc.username = root

jdbc.password = msj

mybatis配置文件mybatis01.xml

/p>

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

mybatis映射文件mapper01.xml

注意：和标签中的column属性指的是查询之后的表字段，所以进行两边查询时，先把sql语句查询出来，再把字段名和属性名进行一一映射。

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

select b.id,b.blog_name,a.id as a_id, a.author_name as a_name from blog b right join author a on b.a_id = a.id;

3.mapper接口和测试类

BlogMapper.java

public interface BlogMapper {

List findAll();

}

TestDemo.java

public class TestDemo {

@Test

public void test() throws IOException {

InputStream is = Resources.getResourceAsStream("mybatis01.xml");

SqlSessionFactory build = new SqlSessionFactoryBuilder().build(is);

SqlSession session = build.openSession();

BlogMapper mapper = session.getMapper(BlogMapper.class);

List list = mapper.findAll();

for(Blog b:list){

System.out.println(b);

}

}

}

运行结果

Blog{id=1, blogName='aablog', author=Author{id=4, authorName='aa'}}

Blog{id=2, blogName='bbblog', author=Author{id=5, authorName='bb'}}

Blog{id=3, blogName='aablog', author=Author{id=6, authorName='cc'}}

二、两边查询：association多对一

association也可以用在多对一的关系上

例子：文章和博客的关系：多对一

文章和博客表.png

1.实体类

Article.java

//这里省略set,get，toString方法，自己添加

private Integer id;

private String articleName;

private Blog blog;

Blog.java

private Integer id;

private String blogName;

2.配置类(同上)

这里要在mybatis01.xml进行添加配置

Mapper02.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

select a.id,a.article_name,b.id as b_id,b.blog_name from article a join blog b

on a.b_id = b.id

3. 测试

ArticleMapper.java

public interface ArticleMapper {

List findAll();

}

TestDemo.java

public class TestDemo {

@Test

public void test() throws IOException {

InputStream is = Resources.getResourceAsStream("mybatis01.xml");

SqlSessionFactory build = new SqlSessionFactoryBuilder().build(is);

SqlSession session = build.openSession();

ArticleMapper mapper = session.getMapper(ArticleMapper.class);

List all = mapper.findAll();

for(Article a:all){

System.out.println(a);

}

}

}

结果查询：

Article{id=1, articleName='111', blog=Blog{id=1, blogName='aablog'}}

Article{id=2, articleName='222', blog=Blog{id=1, blogName='aablog'}}

Article{id=3, articleName='333', blog=Blog{id=1, blogName='aablog'}}

Article{id=4, articleName='444', blog=Blog{id=2, blogName='bbblog'}}

Article{id=5, articleName='555', blog=Blog{id=2, blogName='bbblog'}}

Article{id=6, articleName='666', blog=Blog{id=3, blogName='ccblog'}}

三、两边查询：collection一对多

collection用在一对多的关系上

例子：博客和文章的关系：一对多

1.实体类

Blog.java

注意：这里博客与文章是一对多的关系，所以文章需要用List

private Integer id;

private String blogName;

private List article;

Article.java

private Integer id;

private String articleName;

2.配置类(同上)

这里要在mybatis01.xml进行添加配置

Mapper03.xml

注意：中的属性是ofType

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

select a.id as a_id,a.article_name,b.id as b_id,b.blog_name from article a join blog b

on a.b_id = b.id

3. 测试

TestDemo.java

public class TestDemo {

@Test

public void test() throws IOException {

InputStream is = Resources.getResourceAsStream("mybatis01.xml");

SqlSessionFactory build = new SqlSessionFactoryBuilder().build(is);

SqlSession session = build.openSession();

BlogMapper mapper = session.getMapper(BlogMapper.class);

List blogList = mapper.findAll();

for(Blog b:blogList){

System.out.println(b);

}

}

}

测试结果：

Blog{id=1, blogName='aablog', article=[Article{id=1, articleName='111'}, Article{id=2, articleName='222'}, Article{id=3, articleName='333'}]}

Blog{id=2, blogName='bbblog', article=[Article{id=4, articleName='444'}, Article{id=5, articleName='555'}]}

Blog{id=3, blogName='ccblog', article=[Article{id=6, articleName='666'}]}