在Mybatis的介绍与简单使用,已经介绍了Mybatis的基本使用(包括了简单的输入输出类型,pojo输入输出类型,以及一些简单的sql),而这篇博客主要是来讲一些复杂的输入输出类型,动态sql,以及数据库表之间一对一,一对多的关联
pojo包装类输入类型
新建一个pojo包装类QueryVo.java
package pojo;
import java.io.Serializable;
import java.util.List;
/*
*
*/
public class QueryVo implements Serializable{
private user user;
private List<Integer> IdsList;
public List<Integer> getIdsList() {
return IdsList;
}
public void setIdsList(List<Integer> idsList) {
IdsList = idsList;
}
public user getUser() {
return user;
}
public void setUser(user user) {
this.user = user;
}
}
在user.xml书写SQL语句
<?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="map.UserMapper">
<select id="findUserByQueryVo" parameterType="pojo.QueryVo" resultType="pojo.user">
select * from users where Uname like "%"#{user.Uname}"%"
</select>
</mapper>
新建一个测试类MybatisMapperTest.java
public class MybatisMapperTest {
public void testMapperQueryVo() throws Exception{
//加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
//创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//创建Sqlsession
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql
//sqlSession帮我生成一个实现类(给接口)
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
QueryVo vo = new QueryVo();
user user = new user();
user.setUname("z");
vo.setUser(user);
List<pojo.user> findUserByQueryVo = mapper.findUserByQueryVo(vo);
for(user u :findUserByQueryVo){
System.out.println(u);
}
}
}
ResultMap输出类型
在javabean中的属性名跟数据库的列名一样时,Mybatis是可以实现自动映射的,但是但javabean的属性名跟数据库列明不一样时,就需要我们手动映射
新建一张数据库表
再新建一个javabean,不难发现数据库列明Cardid跟javabean属性名Cardid_id不一样
package pojo;
import java.util.List;
public class affair {
private String Lid;
private String Cardid_id;
private String Ltype;
private double Lmoney;
private String Ltid;
private user user;
public user getUser() {
return user;
}
public void setUser(user user) {
this.user = user;
}
public String getLid() {
return Lid;
}
public void setLid(String lid) {
Lid = lid;
}
public String getCardid_id() {
return Cardid_id;
}
public void setCardid_id(String cardid_id) {
Cardid_id = cardid_id;
}
public String getLtype() {
return Ltype;
}
public void setLtype(String ltype) {
Ltype = ltype;
}
public double getLmoney() {
return Lmoney;
}
public void setLmoney(double lmoney) {
Lmoney = lmoney;
}
public String getLtid() {
return Ltid;
}
public void setLtid(String ltid) {
Ltid = ltid;
}
@Override
public String toString() {
return "affair [Lid=" + Lid + ", Cardid_id=" + Cardid_id + ", Ltype=" + Ltype + ", Lmoney=" + Lmoney + ", Ltid="
+ Ltid + ", user=" + user + "]";
}
}
为该javaBean创建一个mapper.xml,命名为affair.xml(注意:手动映射时,select标签的结果映射一定要写resultMap,不能写resultType,否则会报找不到类的错误)
<?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="map.AffairMapper">
<!--
resultMap标签进行手动映射,只需要对javaBean中属性名跟数据库列名进行手动映射即可
其他相同的他会自动映射
-->
<resultMap type="pojo.affair" id="zp">
<result column="Cardid" property="Cardid_id"/>
</resultMap>
<select id="selectAffairList" resultMap="zp" >
select Lid,Cardid,Ltype,Ltid,Lmoney from affairs
</select>
</mapper>
创建一个AffairMapper接口,实现Mapper动态代理
package map;
import java.util.List;
import org.apache.logging.log4j.core.config.Order;
import pojo.affair;
public interface AffairMapper {
//查询所有数据
public List<affair> selectAffairList();
}
最后到测试类中进行测试
public class MybatisMapperTest {
@Test
public void testAffairList() throws Exception{
//加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
//创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//创建Sqlsession
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql
//sqlSession帮我生成一个实现类(给接口)
AffairMapper mapper = sqlSession.getMapper(AffairMapper.class);
List<affair> selectAffairList = mapper.selectAffairList();
for(affair affair : selectAffairList){
System.out.println(affair.toString());
}
}
}
起别名映射:
javabean的属性名跟数据库列明不一样时,除了可以用resultMap进行手动映射外,我们还可以用另外一种更简单的映射方法,那就是在sql语句中起别名,直接在列名后面加上对应的属性名作为别名
<?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="map.AffairMapper">
<select id="selectAffairList" resultMap="zp" >
select Lid,Cardid Cardid_id ,Ltype,Ltid,Lmoney from affairs
</select>
</mapper>
一对一的关联
在上面的affair类中,有一个类型user类型的属性user,这也就意味着一个affair对应一个user,也就是一对一的关联
affair.xml配置如下
使用一对一关联时,不管javaBean属性名跟类名是否相同,都必须手动映射,否则取出来是null
<?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="map.AffairMapper">
<resultMap type="pojo.affair" id="affair">
<result column="Cardid" property="Cardid_id" />
<result column="Lid" property="Lid" />
<result column="Ltype" property="Ltype" />
<result column="Lmoney" property="Lmoney" />
<result column="Ltid" property="Ltid" />
<!-- 一对一 -->
<association property="user" javaType="pojo.user">
<result column="Uname" property="Uname"/>
</association>
</resultMap>
<select id="selectAffairs" resultMap="affair">
select
a.Lid,
a.Cardid,
a.Ltype,
a.Lmoney,
a.Ltid,
u.Uname
from affairs a left join users u on a.Cardid = u.cardid
</select>
</mapper>
在AffairMapper接口中添加方法
package map;
import java.util.List;
import org.apache.logging.log4j.core.config.Order;
import pojo.affair;
public interface AffairMapper {
//一对一关联查询 以日志为中心关联用户
public List<affair> selectAffairs();
}
最后新建一个测试类进行测试
public class MybatisMapperTest2 {
@Test
public void testOrderList() throws Exception{
//加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
//创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//创建Sqlsession
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql
//sqlSession帮我生成一个实现类(给接口)
AffairMapper mapper = sqlSession.getMapper(AffairMapper.class);
List<affair> selectAffairs = mapper.selectAffairs();
for(affair affair :selectAffairs ){
System.out.println(affair.toString());
}
}
}
一对多的关联
有时候一个affair类中可能不是private user user 而是private List<user> user
也就是对应的不是一个user,而是一个user的集合,对应关系也就从一对一变成了一对多
affair.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="map.AffairMapper">
<resultMap type="pojo.affair" id="affair">
<result column="Cardid" property="Cardid_id" />
<result column="Lid" property="Lid" />
<result column="Ltype" property="Ltype" />
<result column="Lmoney" property="Lmoney" />
<result column="Ltid" property="Ltid" />
<!--
ofType是指泛型类型
也就是传入集合(List<user>)里面元素的类型
而非集合本身的类型
-->
<collection property="user" ofType="pojo.user">
<result column="Uname" property="Uname" />
</collection>
</resultMap>
<select id="selectAffairs" resultMap="affair">
select
a.Lid,
a.Cardid,
a.Ltype,
a.Lmoney,
a.Ltid,
u.Uname
from affairs a left join users u on a.Cardid = u.cardid
</select>
</mapper>
接下在AffairMapper添加方法,在测试来进行测试,由于跟上面一对一操作一样就不多说了
动态sql
很多时候普通的增删改查等基本sql语句是不够用的,我们可能需要根据数据的变化动态改变sql
这就需要我们书写动态sql,mybatis给我们提供一些常用的标签
以下的操作均是针对user表
所写的sql均是在user.xml文件中
- sql片段
当我们书写大量sql语句时候,有些sql片段是重复的,为了让重复sql只写一遍,Mybatis就给我们提供sql片段
<!--抽取公共片段-->
<sql id="select">
select * from users
</sql>
<!--引用公共片段-->
<select id="findUsers" resultType="pojo.user">
<include refid="select"></include>
</select>
- ifwhere
if标签可以进行判断有则添加,没有则不添加,而where标签可以去掉第一个条件前面的and
<!-- 根据姓名和名字查询用户 -->
<select id="selectUserBySexAndUsername" parameterType="Integer" resultType="pojo.user">
<include refid="select"></include>
<!-- 根据性别和名字查询用户 where 可以去掉第一个前and -->
<where>
<if test=" Usex != null and Usex != '' ">
Usex = #{Usex}
</if>
<if test="Uname != null and Uname != '' ">
and Uname = #{Uname}
</if>
</where>
</select>
- foreach
foreach变迁主要运用在遍历,经常搭配关键字in使用
in (1,2,3,4) 括号内需要一些数字,此时遍历数组或者集合得到这些数字是最好不过了
遍历数组
<!--
对于数组collection要让它等于"array",否则会报错
-->
<select id="selectUserByIds1" resultType="pojo.user">
<include refid="select"></include>
<where>
<foreach collection="array" item="id" separator="," open="Idcard in(" close=")">
#{id}
</foreach>
</where>
</select>
遍历集合
<!--
遍历集合时,collection要设置为list,否则会报错
-->
<select id="selectUserByIds2" resultType="pojo.user">
<include refid="select"></include>
<where>
<foreach collection="list" item="id" separator="," open="Idcard in(" close=")">
#{id}
</foreach>
</where>
</select>
集合/数据存在pojo包装类中(参看最上面的pojo类)
<!--
遍历pojo包装类时 collection设置该pojo类中数据类型为数组或者集合的属性名
-->
<select id="selectUserByIds" resultType="pojo.user">
<include refid="select"></include>
<where>
<foreach collection="IdsList" item="id" separator="," open="Idcard in(" close=")">
#{id}
</foreach>
</where>
</select>