搭建开发环境:
ParameterType(输入类型)
传递简单类型
使用#{}占位符,或者${}进行sql拼接。
传递pojo对象
Mybatis使用ognl表达式解析对象字段的值,#{}或者${}括号中的值为pojo属性名称。
传递pojo包装对象
新建包装的pojo.
package com.st.mybatis.pojo;
/**
*
* 包装的pojo
*
*/
public class QuervVo {
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
接口类:
//包装方式
List<User> getUserByQueryVo(QueryVo vo);
编写映射文件语句:
<select id="getUserByQueryVo" parameterType="queryvo" resultType="com.st.mybatis.pojo.User">
SELECT
`id`,
`username`,
`birthday`,
`sex`,
`address`
FROM `user`
<!--WHERE username LIKE #{name} -->
where username like '%${user.username}%'
</select>
提供测试类:
@Test
public void testGetUserByQuervVo() {
SqlSessionFactory sesessionfactory=SqlSessionFactoryUtils.getSqlSessionFactory();
SqlSession sqlsession=sesessionfactory.openSession();
UserMapper userMapper=sqlsession.getMapper(UserMapper.class);
QueryVo vo=new QueryVo();
User user =new User();
user.setUsername("张");
vo.setUser(user);
List<User> list=userMapper.getUserByQueryVo(vo);
for (User user1 : list) {
System.out.println(user1);
}
sqlsession.close();
}
运行结果:
ParameterType(输出类型)
输出简单类型
需求:查询用户表数据条数
sql:select count(*) from ‘user’
Mapper.xml文件
编写测试:
@Test
public void testGetUserCount() {
SqlSessionFactory sesessionfactory=SqlSessionFactoryUtils.getSqlSessionFactory();
SqlSession sqlsession=sesessionfactory.openSession();
UserMapper userMapper=sqlsession.getMapper(UserMapper.class);
Integer userCount=userMapper.getUserCount();
System.out.println("用户总记录数为:"+userCount);
sqlsession.close();
}
如果数据库名字和执行的语句不一致可能会导致以下错误,在正确的开发下是不能修改数据库字段的,所以引入以下几个方法:
编写Order:
package com.st.mybatis.pojo;
import java.util.Date;
public class Order {
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number == null ? null : number.trim();
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note == null ? null : note.trim();
}
@Override
public String toString() {
return "Order [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
+ ", note=" + note + "]";
}
}
编写Order接口:
package com.st.mybatis.mapper;
import java.util.List;
import com.st.mybatis.pojo.Order;
public interface OrderMapper {
/**
* 获取订单列表
* @return
*/
List<Order> getOrderList();
}
编写OrderMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"D://mybatis-dtd/dtd/mybatis-3-mapper.dtd">
<!--
动态代理开发规则:
1.namespace必需是接口的全路径名
2.接口的方法名必须与sql id一致
3.接口的入参与parameterType类型一致
4.接口的返回值必须与resultType类型一致
-->
<!--命名空间,用于隔离sql-->
<mapper namespace="com.st.mybatis.mapper.OrderMapper">
<!--
id: sql语句执行的唯一标识
parameterType: 入参的类型
resultType: 返回结果的数据类型
#{}:是一个占位符 相当于jdbc的?
'%${value}%'字符串拼接指令
-->
<select id="getOrderList" resultType="order">
SELECT
`id`,
`user_id`,
`number`,
`createtime`,
`note`
FROM
`order`
LIMIT 0, 1000;
</select>
</mapper>
编写测试类:
@Test
public void testGetOrderList() {
SqlSessionFactory sqlSessionFactory=SqlSessionFactoryUtils.getSqlSessionFactory();
SqlSession sqlSession=sqlSessionFactory.openSession();
OrderMapper orderMapper=sqlSession.getMapper(OrderMapper.class);
List<Order> list= orderMapper.getOrderList();
for (Order order : list) {
System.out.println(order);
}
sqlSession.close();
}
结果:
因为数据库里面是这个字段:
第一种解决方法:取别名。
执行测试方法:结果正确
第二种解决方案:引入ResultMap。
配置映射:
<!--resultMap入门-->
<resultMap type="order" id="order_list_map">
<!--<id>用于映射主键-->
<id property="id" column="id" />
<!--普通字段用<result>映射-->
<result property="userId" column="user_id"/>
<result property="number" column="number"/>
<result property="createtime" column="createtime"/>
<result property="note" column="note"/>
</resultMap>
<select id="getOrderListMap" resultMap="order_list_map">
SELECT
`id`,
`user_id`,
`number`,
`createtime`,
`note`
FROM
`order`
LIMIT 0, 1000;
</select>
id: 映射主键。
普通字段映射.
和hibernate的映射差不多.
编写测试类:
@Test
public void testGetOrderListMap() {
SqlSessionFactory sqlSessionFactory=SqlSessionFactoryUtils.getSqlSessionFactory();
SqlSession sqlSession=sqlSessionFactory.openSession();
OrderMapper orderMapper=sqlSession.getMapper(OrderMapper.class);
List<Order> list= orderMapper.getOrderListMap();
for (Order order : list) {
System.out.println(order);
}
sqlSession.close();
}
结果和数据库数据一致:
动态sql标签:
动态sql -if标签的使用:
编写映射文件:
<select id="getUserByPojo" parameterType="user" resultType="com.st.mybatis.pojo.User">
SELECT
`id`,
`username`,
`birthday`,
`sex`,
`address`
FROM `user`
<!--WHERE username LIKE #{name} -->
where 1=1
<if test="username!=null and username!=''">username like '%${username}%'</if>
<if test="sex!=null and sex!=''">and sex=#{sex}</if>
</select>
1=1:执行条件永远成立
and sex=? 是实际应用场景中出现的
编写接口:
//演示if标签根据用户名和性别查询用户列表
List<User> getUserByPojo();
编写测试类:
@Test
public void testGetUserByPojo() {
SqlSessionFactory sesessionfactory=SqlSessionFactoryUtils.getSqlSessionFactory();
SqlSession sqlsession=sesessionfactory.openSession();
UserMapper userMapper=sqlsession.getMapper(UserMapper.class);
User user =new User();
user.setUsername("张");
user.setSex("1");
List<User> list=userMapper.getUserByPojo();
for (User user1 : list) {
System.out.println(user1);
}
sqlsession.close();
}
运行结果:
动态sql -where标签的使用:
where标签:会自动补上where的条件语句 不需要程序猿自己写上.尽管写上也是1=1.
运行结果正确:
动态sql -sql片段的使用:
sql片段:即抽取公共sql,用法:公共sql的地方引入sql
使用:
结果同样效果:
动态sql -foreach的使用:
foreach:循环标签
collection:要遍历的集合
open:循环开始之前输出的内容
item:设置循环变量
separator:分隔符
close:循环结束之后输出的内容
编写映射:
<select id="getUserByForeachId" parameterType="queryvo" resultType="user">
SELECT
<include refid="user_id"></include>
FROM
`user`
<where>
<!--
foreach:循环标签
collection:要遍历的集合
open:循环开始之前输出的内容
item:设置循环变量
separator:分隔符
close:循环结束之后输出的内容
-->
<foreach collection="ids" open="id IN(" item="uId" separator="," close=")">
#{uId}
</foreach>
</where>
</select>
编写接口:
//演示foreach标签
List<User> getUserByForeachId(QueryVo vo);
编写测试类:
@Test
public void testGetUserByForeachId() {
SqlSessionFactory sesessionfactory=SqlSessionFactoryUtils.getSqlSessionFactory();
SqlSession sqlsession=sesessionfactory.openSession();
UserMapper userMapper=sqlsession.getMapper(UserMapper.class);
QueryVo vo=new QueryVo();
vo.setIds(Arrays.asList(10,25,36,27,29,26,31,34));
List<User> list=userMapper.getUserByForeachId(vo);
for (User user1 : list) {
System.out.println(user1);
}
sqlsession.close();
}
运行结果: