在MyBatis 3提供了新的基于注解的配置,下面将实现一个基于MyBatis注解的综合实例。
1、增删改查与结果集映射
【实例】使用MyBatis注解,实现对用户信息的增删改查操作和结果集的映射。
(1)在MySQL数据库中创建用户信息表(tb_user)。
-- 创建“用户信息”数据表
CREATE TABLE IF NOT EXISTS tb_user
(
user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
user_account VARCHAR(50) NOT NULL COMMENT '用户账号',
user_password VARCHAR(50) NOT NULL COMMENT '用户密码',
blog_url VARCHAR(50) NOT NULL COMMENT '博客地址',
remark VARCHAR(50) COMMENT '备注'
) COMMENT = '用户信息表';
-- 添加数据
INSERT INTO tb_user(user_account,user_password,blog_url,remark) VALUES('pan_junbiao的博客','123456','https://blog.csdn.net/pan_junbiao','您好,欢迎访问 pan_junbiao的博客');
(2)创建用户信息持久化类(UserInfo.java)。
package com.pjb.mybatis.po;
import org.apache.ibatis.type.Alias;
/**
* 用户信息的持久化类
* @author pan_junbiao
**/
@Alias("userInfo")
public class UserInfo
{
private int userId; //用户编号
private String userAccount; //用户账号
private String userPassword; //用户密码
private String blogUrl; //博客地址
private String remark; //备注
//省略getter与setter方法...
}
(3)编写用户信息Mapper动态代理接口(UserMapper.java)。
package com.pjb.mybatis.mapper;
import com.pjb.mybatis.po.IdcardInfo;
import com.pjb.mybatis.po.RoleInfo;
import com.pjb.mybatis.po.UserInfo;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;
import java.util.List;
import java.util.Map;
/**
* 用户信息Mapper动态代理接口
* @author pan_junbiao
**/
public interface UserMapper
{
/**
* 新增用户,并获取自增主键
*/
@Insert("INSERT INTO tb_user(user_account,user_password,blog_url,remark) VALUES(#{userAccount},#{userPassword},#{blogUrl},#{remark})")
@Options(useGeneratedKeys = true, keyColumn = "user_id", keyProperty = "userId")
//或者:@SelectKey(statement = "SELECT LAST_INSERT_ID()", keyColumn = "user_id", keyProperty = "userId",before = false, resultType = Integer.class)
public int insertUser(UserInfo userInfo);
/**
* 修改用户
*/
@Update("UPDATE tb_user SET user_account = #{userAccount} ,user_password = #{userPassword} ,blog_url=#{blogUrl} ,remark=#{remark} WHERE user_id = #{userId}")
public int updateUser(UserInfo userInfo);
/**
* 删除用户
*/
@Delete("DELETE FROM tb_user WHERE user_id = #{userId}")
public int deleteUser(int userId);
/**
* 根据用户ID,获取用户信息
* 单个参数的查询
*/
@Select("SELECT * FROM tb_user WHERE user_id = #{userId}")
@Results(id = "userResultMap", value = {
@Result(property = "userId", column = "user_id", javaType = Integer.class, jdbcType = JdbcType.INTEGER, id = true),
@Result(property = "userAccount", column = "user_account",javaType = String.class, jdbcType = JdbcType.VARCHAR),
@Result(property = "userPassword", column = "user_password",javaType = String.class, jdbcType = JdbcType.VARCHAR),
@Result(property = "blogUrl", column = "blog_url",javaType = String.class, jdbcType = JdbcType.VARCHAR),
@Result(property = "remark", column = "remark",javaType = String.class, jdbcType = JdbcType.VARCHAR)
})
public UserInfo getUserById(int userId);
/**
* 根据账号和密码,获取用户信息
* 多个参数的查询
*/
@Select("SELECT * FROM tb_user WHERE user_account = #{account} AND user_password = #{password}")
@ResultMap("userResultMap")
public UserInfo getUserByAccountAndPassword(@Param("account") String userAccount, @Param("password") String userPassword);
/**
* 获取用户信息
* 返回HashMap对象
*/
@Select("SELECT * FROM tb_user")
@MapKey("userId")
public Map<Integer,UserInfo> getUserHashMap();
/**
* 根据多个用户ID,获取用户列表
* 动态SQL语句
*/
@Select({"<script>",
"SELECT * FROM tb_user WHERE user_id IN"
+ "<foreach item='id' index='index' collection='array' open='(' separator=',' close=')'>"
+ "#{id}"
+ "</foreach>",
"</script>"})
@ResultMap("userResultMap")
public List<UserInfo> getUserByIdArray(int... ids);
}
(4)在MyBatis的全局配置文件SqlMapConfig.xml(mybatis-config.xml)中,mappers标签中配置上述的mapper映射文件。
<!-- 加载映射文件 -->
<mappers>
<!-- 其他的Mapper映射文件... -->
<!-- 用户信息Mapper动态代理接口 -->
<!-- 注意:这里配置的是class属性,不是resource属性 -->
<mapper class="com.pjb.mybatis.mapper.UserMapper"/>
</mappers>
或者使用接口所在包进行配置:
<!-- 加载映射文件 -->
<mappers>
<!-- 其他的Mapper映射文件... -->
<!-- 使用接口所在包进行配置 -->
<package name="com.pjb.mybatis.mapper"/>
</mappers>
(5)编写执行方法,新增用户,并获取自增主键。
/**
* 新增用户,并获取自增主键
* @author pan_junbiao
*/
@Test
public void insertUser()
{
DataConnection dataConnection = new DataConnection();
SqlSession sqlSession = dataConnection.getSqlSession();
//创建新用户
UserInfo userInfo = new UserInfo();
userInfo.setUserAccount("pan_junbiao的博客");
userInfo.setUserPassword("123456");
userInfo.setBlogUrl("https://blog.csdn.net/pan_junbiao");
userInfo.setRemark("您好,欢迎访问 pan_junbiao的博客");
//获取Mapper代理
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//执行Mapper代理对象的查询方法
int result = userMapper.insertUser(userInfo);
//提交
sqlSession.commit();
//打印结果
System.out.println("执行结果:"+result);
System.out.println("自增主键:"+userInfo.getUserId());
//关闭Session
sqlSession.close();
}
执行结果:
(6)编写执行方法,根据多个用户ID,获取用户列表。
/**
* 根据多个用户ID,获取用户列表(动态SQL语句)
* @author pan_junbiao
*/
@Test
public void getUserByIdArray()
{
DataConnection dataConnection = new DataConnection();
SqlSession sqlSession = dataConnection.getSqlSession();
//获取Mapper代理
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//执行Mapper代理对象的查询方法
int[] ids = {3,4};
List<UserInfo> userInfoList = userMapper.getUserByIdArray(ids);
//打印结果
if(userInfoList!=null && userInfoList.size()>0)
{
for (UserInfo userInfo : userInfoList)
{
System.out.println("用户编号:" + userInfo.getUserId());
System.out.println("用户账号:" + userInfo.getUserAccount());
System.out.println("用户密码:" + userInfo.getUserPassword());
System.out.println("博客地址:" + userInfo.getBlogUrl());
System.out.println("备注信息:" + userInfo.getRemark());
System.out.println("-----------------------------------------");
}
}
//关闭Session
sqlSession.close();
}
执行结果:
2、使用@One注解实现一对一关联查询
【实例】获取用户信息,同时获取一对一关联的身份证信息。
(1)在MySQL数据库创建身份证信息表(tb_idcard),并添加相关数据。
-- 创建“身份证信息”数据表
CREATE TABLE IF NOT EXISTS tb_idcard
(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '身份证ID',
user_id INT NOT NULL COMMENT '用户编号',
idCard_code VARCHAR(45) COMMENT '身份证号码'
) COMMENT = '身份证信息表';
-- 添加数据
INSERT INTO tb_idcard(user_id,idCard_code) VALUE(1,'123456789');
(2)创建身份证信息持久化类(IdcardInfo.java)。
package com.pjb.mybatis.po;
import org.apache.ibatis.type.Alias;
/**
* 身份证信息的持久化类
* @author pan_junbiao
**/
@Alias("idcardInfo")
public class IdcardInfo
{
public int id; //身份证ID
public int userId; //用户编号
public String idCardCode; //身份证号码
//省略getter与setter方法...
}
(3)修改用户信息持久化类(UserInfo.java),添加身份证信息的属性字段。
package com.pjb.mybatis.po;
import org.apache.ibatis.type.Alias;
/**
* 用户信息的持久化类
* @author pan_junbiao
**/
@Alias("userInfo")
public class UserInfo
{
private int userId; //用户编号
private String userAccount; //用户账号
private String userPassword; //用户密码
private String blogUrl; //博客地址
private String remark; //备注
private IdcardInfo idcardInfo; //身份证信息
//省略getter与setter方法...
}
(4)编写用户信息Mapper动态代理接口(UserMapper.java)。
/**
* 获取用户信息和身份证信息
* 一对一关联查询
*/
@Select("SELECT * FROM tb_user WHERE user_id = #{userId}")
@Results(id = "userAndIdcardResultMap", value = {
@Result(property = "userId", column = "user_id", javaType = Integer.class, jdbcType = JdbcType.INTEGER, id = true),
@Result(property = "userAccount", column = "user_account",javaType = String.class, jdbcType = JdbcType.VARCHAR),
@Result(property = "userPassword", column = "user_password",javaType = String.class, jdbcType = JdbcType.VARCHAR),
@Result(property = "blogUrl", column = "blog_url",javaType = String.class, jdbcType = JdbcType.VARCHAR),
@Result(property = "remark", column = "remark",javaType = String.class, jdbcType = JdbcType.VARCHAR),
@Result(property = "idcardInfo", column = "user_id", one = @One(select = "com.pjb.mybatis.mapper.UserMapper.getIdcardInfo", fetchType = FetchType.LAZY))
})
public UserInfo getUserAndIdcardInfo(int id);
/**
* 根据用户ID,获取身份证信息
*/
@Select("SELECT * FROM tb_idcard WHERE user_id = #{userId}")
@Results(id = "idcardInfoResultMap", value = {
@Result(property = "id", column = "id"),
@Result(property = "userId", column = "user_id"),
@Result(property = "idCardCode", column = "idCard_code")})
public IdcardInfo getIdcardInfo(int userId);
(5)编写执行方法,获取用户信息和身份证信息(一对一关联查询)。
/**
* 获取用户信息和身份证信息
* 一对一关联查询
* @author pan_junbiao
*/
@Test
public void getUserAndIdcardInfo()
{
DataConnection dataConnection = new DataConnection();
SqlSession sqlSession = dataConnection.getSqlSession();
//获取Mapper代理
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//执行Mapper代理对象的查询方法
UserInfo userInfo = userMapper.getUserAndIdcardInfo(1);
//打印结果
if(userInfo!=null)
{
System.out.println("用户编号:" + userInfo.getUserId());
System.out.println("用户账号:" + userInfo.getUserAccount());
System.out.println("用户密码:" + userInfo.getUserPassword());
System.out.println("博客地址:" + userInfo.getBlogUrl());
System.out.println("备注信息:" + userInfo.getRemark());
System.out.println("-----------------------------------------");
//获取身份证信息
IdcardInfo idcardInfo = userInfo.getIdcardInfo();
if(idcardInfo!=null)
{
System.out.println("身份证ID:" + idcardInfo.getId());
System.out.println("用户编号:" + idcardInfo.getUserId());
System.out.println("身份证号码:" + idcardInfo.getIdCardCode());
}
}
//关闭Session
sqlSession.close();
}
执行结果:
3、使用@Many注解实现一对多关联查询
【实例】获取用户信息,同时获取一对多关联的权限列表。
(1)在MySQL数据库创建权限信息表(tb_role),并添加相关数据。
-- 创建“权限信息”数据表
CREATE TABLE IF NOT EXISTS tb_role
(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '权限ID',
user_id INT NOT NULL COMMENT '用户编号',
role_name VARCHAR(50) NOT NULL COMMENT '权限名称'
) COMMENT = '权限信息表';
INSERT INTO tb_role(user_id,role_name) VALUES(1,'系统管理员'),(1,'新闻管理员'),(1,'广告管理员');
(2)创建权限信息持久化类(RoleInfo.java)。
package com.pjb.mybatis.po;
import org.apache.ibatis.type.Alias;
/**
* 权限信息的持久化类
* @author pan_junbiao
**/
@Alias("roleInfo")
public class RoleInfo
{
private int id; //权限ID
private int userId; //用户编号
private String roleName; //权限名称
//省略getter与setter方法...
}
(3)修改用户信息持久化类(UserInfo.java),添加权限列表的属性字段。
package com.pjb.mybatis.po;
import org.apache.ibatis.type.Alias;
import java.util.List;
/**
* 用户信息的持久化类
* @author pan_junbiao
**/
@Alias("userInfo")
public class UserInfo
{
private int userId; //用户编号
private String userAccount; //用户账号
private String userPassword; //用户密码
private String blogUrl; //博客地址
private String remark; //备注
private List<RoleInfo> roleInfoList; //权限列表
//省略getter与setter方法...
}
(4)编写用户信息Mapper动态代理接口(UserMapper.java)。
/**
* 获取用户信息和权限列表
* 一对多关联查询
*/
@Select("SELECT * FROM tb_user WHERE user_id = #{userId}")
@Results(id = "userAndRolesResultMap", value = {
@Result(property = "userId", column = "user_id", javaType = Integer.class, jdbcType = JdbcType.INTEGER, id = true),
@Result(property = "userAccount", column = "user_account",javaType = String.class, jdbcType = JdbcType.VARCHAR),
@Result(property = "userPassword", column = "user_password",javaType = String.class, jdbcType = JdbcType.VARCHAR),
@Result(property = "blogUrl", column = "blog_url",javaType = String.class, jdbcType = JdbcType.VARCHAR),
@Result(property = "remark", column = "remark",javaType = String.class, jdbcType = JdbcType.VARCHAR),
@Result(property = "roleInfoList", column = "user_id", many = @Many(select = "com.pjb.mybatis.mapper.UserMapper.getRoleList", fetchType = FetchType.LAZY))
})
public UserInfo getUserAndRolesInfo(int id);
/**
* 根据用户ID,获取权限列表
*/
@Select("SELECT * FROM tb_role WHERE user_id = #{userId}")
@Results(id = "roleInfoResultMap", value = {
@Result(property = "id", column = "id"),
@Result(property = "userId", column = "user_id"),
@Result(property = "roleName", column = "role_name")})
public List<RoleInfo> getRoleList(int userId);
(5)编写执行方法,获取用户信息和权限列表(一对多关联查询)。
/**
* 获取用户信息和权限列表
* 一对多关联查询
* @author pan_junbiao
*/
@Test
public void getUserAndRolesInfo()
{
DataConnection dataConnection = new DataConnection();
SqlSession sqlSession = dataConnection.getSqlSession();
//获取Mapper代理
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//执行Mapper代理对象的查询方法
UserInfo userInfo = userMapper.getUserAndRolesInfo(1);
//打印结果
if(userInfo!=null)
{
System.out.println("用户编号:" + userInfo.getUserId());
System.out.println("用户账号:" + userInfo.getUserAccount());
System.out.println("用户密码:" + userInfo.getUserPassword());
System.out.println("博客地址:" + userInfo.getBlogUrl());
System.out.println("备注信息:" + userInfo.getRemark());
System.out.println("-----------------------------------------");
//获取权限列表
List<RoleInfo> roleInfoList = userInfo.getRoleInfoList();
if(roleInfoList!=null && roleInfoList.size()>0)
{
System.out.println("用户拥有的权限:");
for (RoleInfo roleInfo : roleInfoList)
{
System.out.println(roleInfo.getRoleName());
}
}
}
//关闭Session
sqlSession.close();
}
执行结果:
FetchType.LAZY 和 FetchType.EAGER 有什么区别:
FetchType.LAZY:懒加载,加载一个实体时,定义懒加载的属性不会马上从数据库中加载。
FetchType.EAGER:急加载,加载一个实体时,定义急加载的属性会立即从数据库中加载。