上一篇写到Mybatis动态代理实现Dao和一些Mybatis的一些配置,下来我们将接着上一篇讲到的Mybatis的参数传递和文件配置。
包装pojo
为什会需要包装pojo呢,包装pojo主要是用于一些复杂查询,它可以将你需要用到的pojo包装进去,可以在查询时从pojo里调属性。下来就用一个包装pojo的实例分析:
1.配置Mybatis核心配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--<properties resource="jdbc.properties">-->
<!--加载配置文件,${}表达式,先加载在此文件的配置,后加载外部的配置文件-->
<!--<property name="xx" value="xxx"/>-->
<!--</properties>-->
<!--配置别名,别名不区分大小写-->
<typeAliases>
<!--给pojo.user起别名,不区分大小写-->
<!--<typeAlias type="pojo.User" alias="USER"/>-->
<!--别名就是类名,不区分大小写,推荐-->
<package name="pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<!-- 使用jdbc事务管理 -->
<transactionManager type="JDBC"/>
<!-- 数据库连接池 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url"
value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true"/>
<property name="username" value="root"/>
<property name="password" value="wrial.qq.com"/>
</dataSource>
</environment>
</environments>
<!--加载映射文件-->
<mappers>
<mapper resource="user.xml"/>
<!--<mapper resource="UserMapper.xml"/>-->
<!--映射文件类扫描
1.接口文件和映射文件必须在同一目录下
2.接口文件和映射文件的命名必须一致
-->
<!--<mapper class="DaoProxy.UserMapper" />-->
<package name="DaoProxy"/>
</mappers>
</configuration>
2.建立扩展类,扩展实现额外的一些功能(如果没有需求也不用写扩展类)
package pojo;
public class UserAdd extends User {
//可以添加User之外的东西,作为扩展类来使用
private String nearPeopel;
public String getNearPeopel() {
return nearPeopel;
}
public void setNearPeopel(String nearPeopel) {
this.nearPeopel = nearPeopel;
}
}
3.使用包装类,包装pojo
(1)没使用扩展类
package pojo;
public class UserQuaryVo {
private User user;
private Order order;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Order getOrder() {
return order;
}
public void setOrder(Order order) {
this.order = order;
}
}
(2)使用扩展类:可以对pojo做一个整合,可以接受复杂的数据进行查询操作
package pojo;
public class UserQuaryVo {
private UserAdd userAdd;
private Order order;
public Order getOrder() {
return order;
}
public void setOrder(Order order) {
this.order = order;
}
public UserAdd getUserAdd() {
return userAdd;
}
public void setUserAdd(UserAdd userAdd) {
this.userAdd = userAdd;
}
}
4.在Mapper中编写sql
<select id="getUserByQuaryVo" parameterType="userquaryvo" resultType="user">
select *
from user
where id = #{user.id}
</select>
5.编写测试类
@Test
public void testGetUserById() throws IOException {
SqlSessionFactory sqlSessionFactory = SqlSessionFactoryUtils.getSqlSessionFactory("SqlMapCofig.xml");
SqlSession sqlSession = sqlSessionFactory.openSession();
UserQuaryVo quaryVo = new UserQuaryVo();
UserAdd userAdd = new UserAdd();
userAdd.setId(1);
quaryVo.setUserAdd(userAdd);
User user = sqlSession.selectOne("DaoProxy.UserMapper.getUserByQuaryVo", quaryVo.getUserAdd().getId());
System.out.println(user.toString());
sqlSession.close();
}
上边就是包装pojo的全过程,在需求复杂的情况下会遇到。
ResultMap入门之使用ResultMap解决数据库和pojo数据类型不符合问题
下边用实例来处理这一问题,基于在核心文件配置包扫描
先不配置类型一致,会出现如下错误(我设置pojo的userId和数据库中user_id),userId不能匹配,所以无法赋值
下来在xml配置resultMap
OrderMapper.xml
<mapper namespace="DaoProxy.OrderMapper">
<resultMap id="GetOrderList" type="order">
<!--id用于映射主键-->
<!--<id property="id" column="id"/>-->
<!--result 映射普通字段-->
<result property="userId" column="user_id"/>
</resultMap>
<select id="GetOrders" resultMap="GetOrderList">
SELECT *
FROM `order`;
</select>
</mapper>
测试类
@Test
public void testGetOrders() throws IOException {
SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory("SqlMapCofig.xml").openSession();
List<Order> orders = sqlSession.selectList("DaoProxy.OrderMapper.GetOrders");
for (Order order1 : orders
) {
System.out.println(order1.toString());
}
}
解决后结果如下:
注:在单表查询可以只写需要改的属性,因为在<resultMap id="GetOrderList" type="order">定义了类型,多表就不能这样了
动态sql语句
为什么说mybatis用起来好用,其中有很大一部分原因是它可以简单的使用动态sql语句,下来我们就来学习一下动态sql。
if标签(用法和基础里if大同小异)
映射文件(实现多条件查询):注意要在where后加1=1,因为如果没有筛选到就查询全部。
<select id="getUserByIfPojo" parameterType="user" resultType="User">
select *
from user
where 1=1
<if test="username!=null and username!=''">
and username like '%${username}%'
</if>
<if test="sex!=null and sex!=''">
and sex=#{sex}
</if>
</select>
测试类
@Test
public void testGet() throws IOException {
SqlSession sqlSession = SqlSessionUtils.getSqlSession("SqlMapCofig.xml");
User user = new User();
user.setUsername("张");
user.setSex("w");
List<User> users = sqlSession.selectList("DaoProxy.UserMapper.getUserByIfPojo",user);
for (User u :users) {
System.out.println(u.toString());
}
sqlSession.close();
}
如果不设置性别测试如下
sql - where
where标签自动补where关键字(和手动写会起冲突)
映射文件
<select id="getUserByIfPojo" parameterType="user" resultType="User">
select *
from user
<where>
<if test="username!=null and username!=''">
and username like '%${username}%'
</if>
<if test="sex!=null and sex!=''">
and sex=#{sex}
</if>
</where>
</select>
sql - sql
sql片段拼接用include关键字
<sql id="user_sql">
'username','sex'
</sql>
<select id="getUserByIfPojo" parameterType="user" resultType="User">
select
<include refid="user_sql"/>
from user
foreach
循环标签,一般用于集合查询
映射文件
<!--通过包装-->
<select id="getUserFromList" parameterType="UserQuaryVo" resultType="user">
select * from user
<where>
<foreach collection="ids" open="id IN(" item="id" separator="," close=")">
#{id}
</foreach>
</where>
</select>
测试类
@Test
public void testList() throws IOException {
SqlSession sqlSession = SqlSessionUtils.getSqlSession("SqlMapCofig.xml");
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
UserQuaryVo vo = new UserQuaryVo();
vo.setIds(Arrays.asList(1,29,31));
List<User> list = userMapper.getUserFromList(vo);
for (User user:list
) {
System.out.println(user.toString());
}
}
这一次就到这结束了,下一次写关联查询还有和Spring进行整合!