文章目录
mybatis 非注解开发,常用配置
实体类字段:userId,userName,userBirthday,userSex,userAddress
表字段:id,username,birthday,sex,address
mybatis实体类的属性名(如在列名前面加上user)和数据库表的列名对应不上
解决方案:
1、SQL语句方案:起别名 执行效率最高(开发效率低)
<!--查询所有用户-->
<select id="findAllUser" resultType="cn.td.domain.User">
select id as userId,username as userName,birthday as userBirthday,sex as userSex,address as userAddress from user;
</select>
2、mybatis提供的解决方案:配置resultMap(查询结果集与实体类的对应关系)
<resultMap id="userMap" type="cn.td.domain.Use">
<!--首先配置主键的对应关系-->
<id property="userId" column="id"></id>
非主键字段的对应关系
<result property="userName" column="username"/>
<result property="userBirthday" column="birthday"/>
<result property="userSex" column="sex"/>
<result property="userAddress" column="address"/>
</resultMap>
<!--使用resultMap来指定对应关系-->
<select id="findAllUser" resultMap="userMap">
select * from user;
</select>
mybatis动态SOL
1 、抽取重复SQL语句
<!--抽取重复SQL语句-->
<sql id="selectDefault">
select id,username,birthday,sex,address from user
</sql>
2、if 标签
<!--根据条件查询-->
<select id="findUserByCondition" parameterType="cn.td.domain.User" resultType="cn.td.domain.User">
<!--引入抽取的重复SQL语句-->
<include refid="selectDefault"/>
<where>
<!--if 标签中test的值是parameterType的属性值-->
<if test="username != null">
and username = #{username}
</if>
</where>
</select>
3、foreach标签
<!--根据queryVo中提供的id集合查询用户信息-->
<select id="findUserInIds" parameterType="cn.td.domain.QueryVo" resultType="cn.td.domain.User">
<!--引入sql语句-->
<include refid="selectDefault"/>
<where>
<if test="ids!=null and ids.size()>0">
<!--
collection:parameterType中提供的集合属性名
open:表示该语句以什么开始 close:表示该语句以什么结束
item:表示本次迭代获取的元素,若collection为List、Set或者数组,则表示其中的元素;若collection为map,则代表key-value的value,该参数为必选
separator:每次迭代后给sql语句加上的分割符
-->
<foreach collection="ids" open="and id in(" close=")" item="uid" separator=",">
#{uid}
</foreach>
</if>
</where>
</select>
mybatis多表操作
user实体类字段:id,username,birthday,sex,address
user表字段:id,username,birthday,sex,address
accout实体类字段:id,uid,money
accout表字段:ID,UID,MONEY
一对一操作
查询所有账户(Account)并包含用户(User)名称和地址信息
1、实现一个Account实体类的子类(不常用)
AccountUser extends Account
mybatis映射文件配置
<!--通过Account子类实现多表查询(不常用) 查询所有账户并包含用户名称和地址信息 -->
<select id="findAllAccountContainsUser" resultType="cn.td.domain.AccountUser">
SELECT account.*,user.`username`,user.`address` FROM account JOIN user ON account.`UID`=user.`id`
</select>
2、从表实体包含主表实体的对象引用实现多表查询
public class Account implements Serializable {
private static final long serialVersionUID = 1362784392528572971L;
private int id;
private int uid;
private double money;
/**
* 一对一关系映射:从表实体包含主表实体的对象引用实现多表查询
*/
private User user;
.......
}
mybatis映射文件配置
<!--定义封装account和user的resultMap-->
<resultMap id="accountUserMap" type="cn.td.domain.Account">
<id property="id" column="ID"/>
<result property="uid" column="UID"/>
<result property="money" column="MONEY"/>
<!--一对一的关系映射:配置封装user的内容-->
<association property="user" column="uid">
<id property="id" column="id"></id>
<!-- 非主键字段的对应关系-->
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
</association>
</resultMap>
<select id="findAllAccount" resultMap="accountUserMap">
<!--引入sql语句-->
SELECT account.*,user.`username`,user.`address` FROM account JOIN user ON account.`UID`=user.`id`
</select>
一对多操作
主表实体包含从表实体的集合引用
public class User implements Serializable {
private static final long serialVersionUID = -1052198782505629130L;
private int id;
private String username;
private Date birthday;
private String sex;
private String address;
/**
* 一对多关系映射:主表实体包含从表实体的集合引用
*/
private List<Account> Accounts;
.......
}
mybatis映射文件配置
<!--定义user的resultMap-->
<resultMap id="userAccountMap" type="cn.td.domain.User">
<id property="id" column="id"></id>
<!-- 非主键字段的对应关系-->
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
<!--配置user对象中accounts集合的映射-->
<!--property:user中集合属性名。ofType:集合中元素的类型-->
<collection property="accounts" ofType="cn.td.domain.Account">
<id property="id" column="ID"/>
<result property="uid" column="UID"/>
<result property="money" column="MONEY"/>
</collection>
</resultMap>
<!--查询所有用户-->
<select id="findAllUser" resultMap="userAccountMap">
SELECT `user`.*,account.`ID`,account.`MONEY` FROM `user` LEFT JOIN account ON user.`id`=account.`UID`
</select>
多对多操作
user实体类字段:id,username,birthday,sex,address
user表字段:id,username,birthday,sex,address
role实体类字段:id,roleName,roleDesc
role表字段:ID,ROLE_NAME,ROLE_DESC
中间表 user_role 字段:UID RID
根据角色查询用户
角色表实体类
public class Role implements Serializable {
private static final long serialVersionUID = -5862141085802279565L;
private Integer id;
private String roleName;
private String roleDesc;
//关系映射:主表实体包含从表实体的集合引用
private List<User> userList;
.......
}
mybatis映射文件配置
<!--定义user的resultMap-->
<resultMap id="roleMap" type="cn.td.domain.Role">
<id property="id" column="ID"></id>
<!-- 非主键字段的对应关系-->
<result property="roleName" column="ROLE_NAME"/>
<result property="roleDesc" column="ROLE_DESC"/>
<collection property="userList" ofType="cn.td.domain.User">
<id property="id" column="id"></id>
<!-- 非主键字段的对应关系-->
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
</collection>
</resultMap>
<!--查询所有角色-->
<select id="findAllRole" resultMap="roleMap">
SELECT
`role`.*,
`user`.*
FROM
`role`
LEFT OUTER JOIN user_role ON `role`.`ID` = user_role.`RID`
LEFT OUTER JOIN `user` ON `user`.`id` = user_role.`UID`
</select>
根据用户查询角色
用户表实体类
public class User implements Serializable {
private static final long serialVersionUID = 1497355656125086674L;
private int id;
private String username;
private Date birthday;
private String sex;
private String address;
//关系映射:主表实体包含从表实体的集合引用
private List<Role> roleList;
......
}
mybatis映射文件配置
<resultMap id="userMap" type="cn.td.domain.User">
<id property="id" column="id"></id>
<!-- 非主键字段的对应关系-->
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
<collection property="roleList" ofType="cn.td.domain.Role">
<id property="id" column="ID"></id>
<!-- 非主键字段的对应关系-->
<result property="roleName" column="ROLE_NAME"/>
<result property="roleDesc" column="ROLE_DESC"/>
</collection>
</resultMap>
<!--查询所有用户-->
<select id="findAllUser" resultMap="userMap">
SELECT
`user`.*,
`role`.*
FROM
`user`
LEFT OUTER JOIN user_role ON `user`.`id` = user_role.`UID`
LEFT OUTER JOIN `role` ON `role`.`ID` = user_role.`RID`
</select>
延迟加载
user实体类字段:id,username,birthday,sex,address
user表字段:id,username,birthday,sex,address
accout实体类字段:id,uid,money
accout表字段:ID,UID,MONEY
通过用户id查找账户信息
userDao.xml配置
<?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="cn.td.dao.UserDao">
<!--定义user的resultMap-->
<resultMap id="userAccountMap" type="cn.td.domain.User">
<id property="id" column="id"></id>
<!-- 非主键字段的对应关系-->
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
<!--一对多配置user对象中accounts集合的映射-->
<!--property:user中集合属性名。ofType:集合中元素的类型-->
<collection property="accounts" column="id" ofType="cn.td.domain.Account" select="cn.td.dao.AccountDao.findAccountById" />
</resultMap>
<!--查询所有用户-->
<select id="findAllUser" resultMap="userAccountMap">
select * from user
</select>
</mapper>
accountDao.xml配置
<?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="cn.td.dao.AccountDao">
<!--根据用户id查询账户信息-->
<select id="findAccountById" parameterType="Integer" resultType="cn.td.domain.Account">
SELECT * FROM account where uid = #{uid}
</select>
</mapper>
通过账户UID查找用户
accountDao.xml配置
<?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="cn.td.dao.AccountDao">
<!--定义封装account和user的resultMap-->
<resultMap id="accountUserMap" type="cn.td.domain.Account">
<id property="id" column="ID"/>
<result property="uid" column="UID"/>
<result property="money" column="MONEY"/>
<!--一对一的关系映射:配置封装user的内容-->
<!--select属性:查询用户的唯一标识-->
<association property="user" column="uid" javaType="cn.td.domain.User" select="cn.td.dao.UserDao.findUserById"/>
</resultMap>
<!--查询所有用户-->
<select id="findAllAccount" resultMap="accountUserMap">
select * from account
</select>
</mapper>
userDao.xml配置
<?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="cn.td.dao.UserDao">
<!--通过用户名查询用户-->
<select id="findUserById" parameterType="Integer" resultType="cn.td.domain.User">
select * from user where id = #{id};
</select>
</mapper>
开启二级缓存
在要开启二级缓存的dao.xml 配置 标签,在要开启的方法上添加useCache="true"属性
<?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="cn.td.dao.UserDao">
<!--开启二级缓存-->
<cache/>
<!--通过用户名查询用户 useCache="true":开启二级缓存-->
<select useCache="true" id="findUserById" parameterType="Integer" resultType="cn.td.domain.User">
SELECT * FROM `user` where id = #{id};
</select>
</mapper>