mybatis crud_MyBatis教程– CRUD操作和映射关系–第2部分

mybatis crud

为了说明这一点，我们正在考虑以下示例域模型：

会有用户，每个用户可能都有一个博客，每个博客可以包含零个或多个帖子。

这三个表的数据库结构如下：

CREATE TABLE user (
  user_id int(10) unsigned NOT NULL auto_increment,
  email_id varchar(45) NOT NULL,
  password varchar(45) NOT NULL,
  first_name varchar(45) NOT NULL,
  last_name varchar(45) default NULL,
  blog_id int(10) unsigned default NULL,
  PRIMARY KEY  (user_id),
  UNIQUE KEY Index_2_email_uniq (email_id),
  KEY FK_user_blog (blog_id),
  CONSTRAINT FK_user_blog FOREIGN KEY (blog_id) REFERENCES blog (blog_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE blog (
  blog_id int(10) unsigned NOT NULL auto_increment,
  blog_name varchar(45) NOT NULL,
  created_on datetime NOT NULL,
  PRIMARY KEY  (blog_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE post (
  post_id int(10) unsigned NOT NULL auto_increment,
  title varchar(45) NOT NULL,
  content varchar(1024) NOT NULL,
  created_on varchar(45) NOT NULL,
  blog_id int(10) unsigned NOT NULL,
  PRIMARY KEY  (post_id),
  KEY FK_post_blog (blog_id),
  CONSTRAINT FK_post_blog FOREIGN KEY (blog_id) REFERENCES blog (blog_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

在这里，我将解释如何获取和映射*-一对一和一对多结果映射。

package com.sivalabs.mybatisdemo.domain;

public class User 
{
 private Integer userId;
 private String emailId;
 private String password;
 private String firstName;
 private String lastName;
 private Blog blog;
 //setters and getters
}

package com.sivalabs.mybatisdemo.domain;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Blog {

 private Integer blogId;
 private String blogName;
 private Date createdOn;
 private List<Post> posts = new ArrayList<Post>();
 //setters and getters
}

package com.sivalabs.mybatisdemo.domain;

import java.util.Date;

public class Post 
{
 private Integer postId;
 private String title;
 private String content;
 private Date createdOn;
 //setters and getters
}

在mybatis-config.xml中，为bean配置类型别名。

<typeAliases>
  <typeAlias type='com.sivalabs.mybatisdemo.domain.User' alias='User'/>
  <typeAlias type='com.sivalabs.mybatisdemo.domain.Blog' alias='Blog'/>
  <typeAlias type='com.sivalabs.mybatisdemo.domain.Post' alias='Post'/>  
</typeAliases>

*-具有一个结果映射：

在UserMapper.xml中，如下配置sql查询和结果映射：

<mapper namespace='com.sivalabs.mybatisdemo.mappers.UserMapper'>

 <resultMap type='User' id='UserResult'>
    <id property='userId' column='user_id'/>
    <result property='emailId' column='email_id'/>
    <result property='password' column='password'/>
    <result property='firstName' column='first_name'/>
    <result property='lastName' column='last_name'/>
    <association property='blog' resultMap='BlogResult'/>
   </resultMap>

 <resultMap type='Blog' id='BlogResult'>
    <id property='blogId' column='blog_id'/>
    <result property='blogName' column='BLOG_NAME'/>
    <result property='createdOn' column='CREATED_ON'/>    
   </resultMap>

  <select id='getUserById' parameterType='int' resultMap='UserResult'>

     SELECT 
      U.USER_ID, U.EMAIL_ID, U.PASSWORD, U.FIRST_NAME, U.LAST_NAME, 
      B.BLOG_ID, B.BLOG_NAME, B.CREATED_ON
  FROM USER U LEFT OUTER JOIN BLOG B ON U.BLOG_ID=B.BLOG_ID
  WHERE U.USER_ID = #{userId}
  </select>

  <select id='getAllUsers' resultMap='UserResult'>
   SELECT 
     U.USER_ID, U.EMAIL_ID, U.PASSWORD, U.FIRST_NAME, U.LAST_NAME, 
     B.BLOG_ID, B.BLOG_NAME, B.CREATED_ON
 FROM USER U LEFT OUTER JOIN BLOG B ON U.BLOG_ID=B.BLOG_ID
  </select>

</mapper>

在JUnit Test中，编写一种方法来测试关联加载。

public void getUserById() 
{
 SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
 try{
  UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
  User user = userMapper.getUserById(1);
  System.out.println(user.getBlog());
 }finally{
  sqlSession.close();
 }
}

一对多结果映射：

在BlogMapper.xml中，如下配置Blog to Posts关系：

<mapper namespace='com.sivalabs.mybatisdemo.mappers.BlogMapper'>

 <resultMap type='Blog' id='BlogResult'>
    <id property='blogId' column='blog_id'/>
    <result property='blogName' column='BLOG_NAME'/>
    <result property='createdOn' column='CREATED_ON'/>
    <collection property='posts' ofType='Post' resultMap='PostResult' columnPrefix='post_'></collection>
   </resultMap>

   <resultMap type='Post' id='PostResult'>
    <id property='postId' column='post_id'/>
    <result property='title' column='title'/>
    <result property='content' column='content'/>
    <result property='createdOn' column='created_on'/>
   </resultMap>

  <select id='getBlogById' parameterType='int' resultMap='BlogResult'>

     SELECT 
      b.blog_id, b.blog_name, b.created_on, 
      p.post_id as post_post_id, p.title as post_title, p.content as post_content, p.created_on as post_created_on
  FROM blog b left outer join post p on b.blog_id=p.blog_id
     WHERE b.BLOG_ID=#{blogId}
  </select>

  <select id='getAllBlogs' resultMap='BlogResult'>
   SELECT 
    b.blog_id, b.blog_name, b.created_on as blog_created_on, 
     p.post_id as post_post_id, p.title as post_title, p.content as post_content, p.created_on as post_created_on
 FROM blog b left outer join post p on b.blog_id=p.blog_id
  </select>

</mapper>

在JUnit Test中，编写一种测试方法来测试博客到帖子的关系映射。

public void getBlogById() 
{
 SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
 try{
 BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
 Blog blog = blogMapper.getBlogById(1);
 System.out.println(blog);
 List<Post> posts = blog.getPosts();
 for (Post post : posts) {
  System.out.println(post);
 }
 }finally{
  sqlSession.close();
 }
}

支持 整合

MyBatis-Spring是MyBatis的子项目，并提供Spring集成支持，从而大大简化了MyBatis的用法。 对于那些熟悉Spring的依赖注入方法的人来说，使用MyBatis-Spring非常简单。

首先让我们看看不使用Spring的MyBatis的使用过程。

1.通过传递包含数据源属性，映射器XML列表和TypeAliases等的mybatis-config.xml，使用SqlSessionFactoryBuilder创建SqlSessionFactory。

2.从SqlSessionFactory创建SqlSession对象

3.从SqlSession中获取Mapper实例并执行查询。

4.使用SqlSession对象提交或回滚事务。

使用MyBatis-Spring，可以在Spring ApplicationContext中配置上述大多数步骤，并且可以将SqlSession或Mapper实例注入到Spring Beans中。 然后，我们可以使用Spring的TransactionManagement功能，而无需在整个代码中编写事务提交/回滚代码。

现在让我们看看如何配置MyBatis + Spring集成的东西。

步骤＃1：在pom.xml中配置MyBatis-Spring依赖项

<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.10</version>
			<scope>test</scope>
		</dependency>

		<dependency>
		    <groupId>org.mybatis</groupId>
		    <artifactId>mybatis</artifactId>
		    <version>3.1.1</version>
		</dependency>
		<dependency>
		    <groupId>org.mybatis</groupId>
		    <artifactId>mybatis-spring</artifactId>
		    <version>1.1.1</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context-support</artifactId>
			<version>3.1.1.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>3.1.1.RELEASE</version>
			<scope>test</scope>
		</dependency>
		<dependency>
	            <groupId>mysql</groupId>
	            <artifactId>mysql-connector-java</artifactId>
	            <version>5.1.21</version>
	            <scope>runtime</scope>
	        </dependency>
		<dependency>
			<groupId>cglib</groupId>
			<artifactId>cglib-nodep</artifactId>
			<version>2.2.2</version>
		</dependency>

步骤＃2：您不需要在mybatis-config.xml中配置数据库属性。

我们可以在Spring Container中配置DataSource并使用它来构建MyBatis SqlSessionFactory。

MyBatis-Spring使用org.mybatis.spring.SqlSessionFactoryBean代替SqlSessionFactoryBuilder来构建SqlSessionFactory。

我们可以将dataSource，Mapper XML文件位置，typeAliases等传递给SqlSessionFactoryBean。

<bean id='dataSource' class='org.apache.commons.dbcp.BasicDataSource'>
		<property name='driverClassName' value='${jdbc.driverClassName}'/>
		<property name='url' value='${jdbc.url}'/>
		<property name='username' value='${jdbc.username}'/>
		<property name='password' value='${jdbc.password}'/>
	</bean>

	<bean id='sqlSessionFactory' class='org.mybatis.spring.SqlSessionFactoryBean'>
  		<property name='dataSource' ref='dataSource' />
  		<property name='typeAliasesPackage' value='com.sivalabs.mybatisdemo.domain'/>
  		<property name='mapperLocations' value='classpath*:com/sivalabs/mybatisdemo/mappers/**/*.xml' />
	</bean>

步骤＃3：配置提供ThreadSafe SqlSession对象的SqlSessionTemplate。

<bean id='sqlSession' class='org.mybatis.spring.SqlSessionTemplate'>
	  <constructor-arg index='0' ref='sqlSessionFactory' />
	</bean>

步骤＃4：为了能够直接注入Mapper，我们应该注册org.mybatis.spring.mapper.MapperScannerConfigurer并配置要在其中找到Mapper接口的包名称。

<bean class='org.mybatis.spring.mapper.MapperScannerConfigurer'>
	  <property name='basePackage' value='com.sivalabs.mybatisdemo.mappers' />
	</bean>

步骤5：将 TransactionManager配置为支持基于注释的事务支持。

<tx:annotation-driven transaction-manager='transactionManager'/>

	<bean id='transactionManager' class='org.springframework.jdbc.datasource.DataSourceTransactionManager'>
  		<property name='dataSource' ref='dataSource' />
	</bean>

步骤＃6：更新Service类并在Spring容器中注册它们。

package com.sivalabs.mybatisdemo.service;

import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.sivalabs.mybatisdemo.domain.User;
import com.sivalabs.mybatisdemo.mappers.UserMapper;

@Service
@Transactional
public class UserService
{
	@Autowired
	private SqlSession sqlSession; //This is to demonstrate injecting SqlSession object

	public void insertUser(User user) 
	{
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		userMapper.insertUser(user);
	}

	public User getUserById(Integer userId) 
	{
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		return userMapper.getUserById(userId);
	}

}

package com.sivalabs.mybatisdemo.service;

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.sivalabs.mybatisdemo.domain.Blog;
import com.sivalabs.mybatisdemo.mappers.BlogMapper;

@Service
@Transactional
public class BlogService
{
	@Autowired
	private BlogMapper blogMapper; // This is to demonstratee how to inject Mappers directly

	public void insertBlog(Blog blog) {
		blogMapper.insertBlog(blog);
	}

	public Blog getBlogById(Integer blogId) {
		return blogMapper.getBlogById(blogId);
	}

	public List<Blog> getAllBlogs() {
		return blogMapper.getAllBlogs();
	}
}

注意：当我们可以直接注入Mappers时，为什么还要注入SqlSession对象？ 因为SqlSession对象包含更细粒度的方法，所以有时会派上用场。

例如：如果我们想获取更新查询更新了多少条记录，可以使用SqlSession，如下所示：

int updatedRowCount = sqlSession.update('com.sivalabs.mybatisdemo.mappers.UserMapper.updateUser', user);

到目前为止，我还没有找到一种无需使用SqlSession对象就可以获取行更新计数的方法。

步骤＃7编写JUnit测试以测试UserService和BlogService。

package com.sivalabs.mybatisdemo;

import java.util.List;

import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.sivalabs.mybatisdemo.domain.User;
import com.sivalabs.mybatisdemo.service.UserService;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations='classpath:applicationContext.xml')
public class SpringUserServiceTest 
{
	@Autowired
	private UserService userService;

    @Test
	public void testGetUserById() 
	{
		User user = userService.getUserById(1);
		Assert.assertNotNull(user);
		System.out.println(user);
		System.out.println(user.getBlog());
	}

    @Test
    public void testUpdateUser() 
    {
    	long timestamp = System.currentTimeMillis();
		User user = userService.getUserById(2);
		user.setFirstName('TestFirstName'+timestamp);
    	user.setLastName('TestLastName'+timestamp);
    	userService.updateUser(user);
		User updatedUser = userService.getUserById(2);
		Assert.assertEquals(user.getFirstName(), updatedUser.getFirstName());
		Assert.assertEquals(user.getLastName(), updatedUser.getLastName());
	}

}

package com.sivalabs.mybatisdemo;

import java.util.Date;
import java.util.List;

import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.sivalabs.mybatisdemo.domain.Blog;
import com.sivalabs.mybatisdemo.domain.Post;
import com.sivalabs.mybatisdemo.service.BlogService;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations='classpath:applicationContext.xml')
public class SpringBlogServiceTest 
{
	@Autowired
	private BlogService blogService;

	@Test
	public void testGetBlogById() 
	{
		Blog blog = blogService.getBlogById(1);
		Assert.assertNotNull(blog);
		System.out.println(blog);
		List<Post> posts = blog.getPosts();
		for (Post post : posts) {
			System.out.println(post);
		}
	}

    @Test
    public void testInsertBlog() 
    {
    	Blog blog = new Blog();
    	blog.setBlogName('test_blog_'+System.currentTimeMillis());
    	blog.setCreatedOn(new Date());

    	blogService.insertBlog(blog);
		Assert.assertTrue(blog.getBlogId() != 0);
		Blog createdBlog = blogService.getBlogById(blog.getBlogId());
		Assert.assertNotNull(createdBlog);
		Assert.assertEquals(blog.getBlogName(), createdBlog.getBlogName());

	}

}

参考： MyBatis教程：第3部分-映射关系 ，   MyBatis教程：第4部分– JCG合作伙伴 Siva Reddy的My Integrations on Technology上的Spring Integration 。

翻译自: https://www.javacodegeeks.com/2012/11/mybatis-tutorial-crud-operations-and-mapping-relationships-part-2.html

mybatis crud