java 不同库关联查询_Mybatis关联查询（嵌套查询）

上一篇文章《 Mybatis实现数据的增删改查(CRUD) 》介绍了基于Mybatis对数据库的增、删、改、查。这一篇介绍下关联查询(join query)。

三张表：user article blog

表的存储sql文件：

/*

Navicat MySQL Data Transfer

Source Server : localhost

Source Server Version : 50620

Source Host : localhost:3306

Source Database : mybatis

Target Server Type : MYSQL

Target Server Version : 50620

File Encoding : 65001

Date: 2014-10-19 18:27:31

*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for `user`

-- ----------------------------

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`userName` varchar(50) DEFAULT NULL,

`userAge` int(11) DEFAULT NULL,

`userAddress` varchar(200) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of user

-- ----------------------------

INSERT INTO `user` VALUES ('1', 'summer', '30', 'shanghai');

INSERT INTO `user` VALUES ('2', 'test1', '22', 'suzhou');

INSERT INTO `user` VALUES ('3', 'test1', '29', 'some place');

INSERT INTO `user` VALUES ('4', 'lu', '28', 'some place');

INSERT INTO `user` VALUES ('5', 'xiaoxun', '27', 'nanjing');

-- ----------------------------

-- Table structure for `article`

-- ----------------------------

DROP TABLE IF EXISTS `article`;

CREATE TABLE `article` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`userid` int(11) NOT NULL,

`title` varchar(100) DEFAULT NULL,

`content` text,

`blogid` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of article

-- ----------------------------

INSERT INTO `article` VALUES ('1', '1', 'test_title_1', 'test_content_1', '1');

INSERT INTO `article` VALUES ('2', '1', 'test_title_2', 'test_content_2', '1');

INSERT INTO `article` VALUES ('3', '1', 'test_title_3', 'test_content_3', '2');

INSERT INTO `article` VALUES ('4', '1', 'test_title_4', 'test_content_4', '2');

INSERT INTO `article` VALUES ('5', '2', 'test_title_5', 'test_content_5', '2');

-- ----------------------------

-- Table structure for `blog`

-- ----------------------------

DROP TABLE IF EXISTS `blog`;

CREATE TABLE `blog` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`title` varchar(200) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of blog

-- ----------------------------

INSERT INTO `blog` VALUES ('1', 'xiaoxun_blog');

INSERT INTO `blog` VALUES ('2', 'zhang_blog');

配置文件Configuration.xml

/p>

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

User类的定义和User.xml的配置见上一文章。

Article类定义：

package com.mybatis.test;

public class Article {

private int id;

private User user;

private String title;

private String content;

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public User getUser() {

return user;

}

public void setUser(User user) {

this.user = user;

}

public String getTitle() {

return title;

}

public void setTitle(String title) {

this.title = title;

}

public String getContent() {

return content;

}

public void setContent(String content) {

this.content = content;

}

}

Article类中有一个User类。

Article.xml的配置：

/p>

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

select user.id user_id,user.userName user_userName,user.userAddress user_userAddress,

article.id article_id,article.title article_title,article.content article_content

from user,article

where user.id=article.userid and user.id=#{id}

Blog类定义：

package com.mybatis.test;

import java.util.List;

public class Blog {

private int id;

private String title;

private List articles;

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getTitle() {

return title;

}

public void setTitle(String title) {

this.title = title;

}

public List getArticles() {

return articles;

}

public void setArticles(List articles) {

this.articles = articles;

}

}

Blog类中有一个List。

Blog.xml配置：

/p>

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

select user.id user_id,user.userName user_userName,user.userAddress user_userAddress,

article.id article_id,article.title article_title,article.content article_content,

blog.id blog_id, blog.title blog_title

from user,article,blog

where user.id=article.userid and blog.id=article.blogid and blog.id=#{id}

IArticleOperation定义：

package com.mybatis.test;

import java.util.List;

public interface IArticleOperation {

public List getUserArticles(int userID);

}

IBlogOperation定义：

package com.mybatis.test;

public interface IBlogOperation {

Blog getBlogByID(int id);

}

Test类：

package com.mybatis.test;

import java.io.Reader;

import java.util.List;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class Test {

private static SqlSessionFactory sqlSessionFactory;

private static Reader reader;

static {

try {

//通过配置文件初始化sqlSessionFactory

reader = Resources.getResourceAsReader("Configuration.xml");

sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);

} catch (Exception e) {

e.printStackTrace();

}

}

public static SqlSessionFactory getSession() {

return sqlSessionFactory;

}

public void getUserByID(int userID) {

SqlSession session = sqlSessionFactory.openSession();

try {

IUserOperation userOperation = session

.getMapper(IUserOperation.class);

User user = userOperation.selectUserByID(userID);

if (user != null) {

System.out.println(user.getId() + ":" + user.getUserName()

+ ":" + user.getUserAddress());

}

} finally {

session.close();

}

}

public void getUserList(String userName) {

SqlSession session = sqlSessionFactory.openSession();

try {

IUserOperation userOperation = session

.getMapper(IUserOperation.class);

List users = userOperation.selectUsersByName(userName);

for (User user : users) {

System.out.println(user.getId() + ":" + user.getUserName()

+ ":" + user.getUserAddress());

}

} finally {

session.close();

}

}

/**

* 增加后要commit

*/

public void addUser() {

User user = new User();

user.setUserAddress("place");

user.setUserName("test_add");

user.setUserAge(30);

SqlSession session = sqlSessionFactory.openSession();

try {

IUserOperation userOperation = session

.getMapper(IUserOperation.class);

userOperation.addUser(user);

session.commit();

System.out.println("新增用户ID：" + user.getId());

} finally {

session.close();

}

}

/**

* 修改后要commit

*/

public void updateUser() {

SqlSession session = sqlSessionFactory.openSession();

try {

IUserOperation userOperation = session

.getMapper(IUserOperation.class);

User user = userOperation.selectUserByID(1);

if (user != null) {

user.setUserAddress("A new place");

userOperation.updateUser(user);

session.commit();

}

} finally {

session.close();

}

}

/**

* 删除后要commit.

*

* @param id

*/

public void deleteUser(int id) {

SqlSession session = sqlSessionFactory.openSession();

try {

IUserOperation userOperation = session

.getMapper(IUserOperation.class);

userOperation.deleteUser(id);

session.commit();

} finally {

session.close();

}

}

public void getUserArticles(int userid) {

SqlSession session = sqlSessionFactory.openSession();

try {

IArticleOperation articleOperation = session

.getMapper(IArticleOperation.class);

List articles = articleOperation.getUserArticles(userid);

for (Article article : articles) {

System.out.println(article.getTitle() + ":"

+ article.getContent() + "用户名："

+ article.getUser().getUserName() + "用户地址："

+ article.getUser().getUserAddress());

}

} finally {

session.close();

}

}

public void getBlogArticles(int blogid) {

SqlSession session = sqlSessionFactory.openSession();

try {

IBlogOperation blogOperation = session

.getMapper(IBlogOperation.class);

Blog blog = blogOperation.getBlogByID(blogid);

System.out.println(blog.getTitle() + ":");

List articles = blog.getArticles();

for (Article article : articles) {

System.out.println(article.getTitle() + ":"

+ article.getContent() + "用户名："

+ article.getUser().getUserName() + "用户地址："

+ article.getUser().getUserAddress());

/*System.out.println(article.getTitle() + ":"

+ article.getContent());*/

}

} finally {

session.close();

}

}

public static void main(String[] args) {

try {

Test test = new Test();

// test.getUserByID(1);

// test.getUserList("test1");

// test.addUser();

// test.updateUser();

// test.deleteUser(6);

//test.getUserArticles(1);

test.getBlogArticles(1);

} catch (Exception e) {

System.out.println(e.getMessage());

}

}

}

Mybatis的“N+1查询问题”

示例：

javaType="Author" select=”selectAuthor” />

SELECT * FROM BLOG WHERE ID = #{id}

SELECT * FROM AUTHOR WHERE ID = #{id}

有两个查询语句：一个来加载博客，另外一个来加载作者，而且博客的结果映射描述了“selectAuthor”语句应该被用来加载它的 author 属性。

其他所有的属性将会被自动加载，假设它们的列和属性名相匹配。

这种方式很简单，但是对于大型数据集合和列表将不会表现很好。问题就是我们熟知的“N+1 查询问题”。概括地讲，N+1 查询问题可以是这样引起的：

你执行了一个单独的 SQL 语句来获取结果列表(就是“+1”)。

对返回的每条记录，你执行了一个查询语句来为每个加载细节(就是“N”)。

这个问题会导致成百上千的 SQL 语句被执行。这通常不是期望的。

可以采用关联的嵌套结果来解决这个问题：

javaType="Author" resultMap=”authorResult” />

resultMap 这是结果映射的 ID，可以映射关联的嵌套结果到一个合适的对象图中。这是一种替代方法来调用另外一个查询语句。

参考：

http://www.mybatis.org/mybatis-3/zh/sqlmap-xml.html