ParameterType输入类型
1、传递简单类型
2、传递pojo对象
3、传递pojo包装对象
开发中通过可以使用pojo传递查询条件。
查询条件可能是综合的查询条件,不仅包括用户查询条件还包括其它的查询条件(比如查询用户信息的时候,将用户购买商品信息也作为查询条件),这时可以使用包装对象传递输入参数。
包装对象:Pojo类中的一个属性是另外一个pojo。
需求:根据用户名模糊查询用户信息,查询条件放到QueryVo的user属性中。
编写QueryVo
public class QueryVo implements Serializable{
private static final long serialVersionUID = 1L;
//包含其他的pojo
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
Mapper.xml文件
<!-- 使用包装类模糊查询 -->
<select id="selectByQueryVo" parameterType="com.ithou.mybatis.pojo.QueryVo" resultType="user">
select * from User where username like '%${user.username}%'
</select>
Mapper接口
//根据用户名模糊查询用户信息,查询条件放到QueryVo的user属性中
public List<User> selectByQueryVo(QueryVo queryVo);
测试
// 根据用户名模糊查询用户信息
@Test
public void demo2() throws IOException {
// 加载核心配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
QueryVo queryVo = new QueryVo();
User user = new User();
user.setUsername("o");
queryVo.setUser(user);
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> users = userMapper.selectByQueryVo(queryVo);
for (User user2 : users) {
System.out.println(user2);
}
System.out.println(user);
}
resultType输出类型
1、输出pojo对象
2、输出pojo列表
3、输出简单类型
需求:查询用户表数据条数
Mapper.xml文件
<!-- 查询用户表数据条数 -->
<select id="count" resultType="Integer">
select count(*) from User
</select>
Mapper接口
//查询用户表数据条数
public Integer count();
测试
// 查询用户表数据条数
@Test
public void demo3() throws IOException {
// 加载核心配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Integer count = userMapper.count();
System.out.println(count);
}
resultMap
resultType可以指定将查询结果映射为pojo,但需要pojo的属性名和sql查询的列名一致方可映射成功。
如果sql查询字段名和pojo的属性名不一致,可以通过resultMap将字段名和属性名作一个对应关系 ,resultMap实质上还需要将查询结果映射到pojo对象中。
resultMap可以实现将查询结果映射为复杂类型的pojo,比如在查询结果映射对象中包括pojo和list实现一对一查询和一对多查询。
需求:查询订单表order的所有数据
声明pojo对象
数据库表如下:
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL COMMENT '下单用户id',
`number` varchar(32) NOT NULL COMMENT '订单号',
`createtime` datetime NOT NULL COMMENT '创建订单时间',
`note` varchar(100) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`),
KEY `FK_orders_1` (`user_id`),
CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
Order对象
public class Orders implements Serializable{
private static final long serialVersionUID = 1L;
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
get/set/toString...
}
Mapper.xml文件
<mapper namespace="com.ithou.mybatis.mapper.OrderMapper">
<resultMap type="Orders" id="orders">
<id column="id" property="id"/>
<result column="user_Id" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
</resultMap>
<!-- 查询订单表order的所有数据 -->
<select id="selectOrderAll" resultMap="orders">
select * from orders
</select>
</mapper>
Mapper接口
public interface OrderMapper {
//查询订单表order的所有数据
public List<Orders> selectOrderAll();
}
测试
// 查询订单表order的所有数据
@Test
public void demo4() throws IOException {
// 加载核心配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
List<Orders> list = orderMapper.selectOrderAll();
for(Orders orders : list) {
System.out.println(orders);
}
}
动态sql
通过mybatis提供的各种标签方法实现动态拼接sql。
需求:根据性别和名字查询用户
SELECT id, username, birthday, sex, address FROM `user` WHERE sex = 1 AND username LIKE '%张%'
if 标签
Mapper.xml文件
<!-- if标签:根据性别和名字查询用户 -->
<select id="selectBySexAndName" parameterType="user" resultType="user">
select * from User where 1 = 1
<if test="sex != null and sex != ''">
AND sex = #{sex}
</if>
<if test="username != null and username != ''">
AND username like '%${username}%'
</if>
</select>
Mapper接口
// 根据性别和名字查询用户(if标签)
public List<User> selectBySexAndName(User user);
测试
// 查询订单表order的所有数据
@Test
public void demo4() throws IOException {
// 加载核心配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
List<Orders> list = orderMapper.selectOrderAll();
for (Orders orders : list) {
System.out.println(orders);
}
}
注意:字符串类型的数据需要做不等于空字符串校验
Where标签
上面的sql还有where 1=1 这样的语句,很麻烦
可以使用where标签进行改造
Mapper.xml文件
<!-- where标签:根据性别和名字查询用户 -->
<select id="selectBySexAndName2" parameterType="user" resultType="user">
select * from User
<where>
<if test="sex != null and sex != ''">
AND sex = #{sex}
</if>
<if test="username != null and username != ''">
AND username like '%${username}%'
</if>
</where>
</select>
Mapper接口
// 根据性别和名字查询用户(where标签)
public List<User> selectBySexAndName2(User user);
测试..
where标签可以去掉第一个前and
Sql片段
sql中可将重复的sql提取出来,使用时用include引用即可,最终达到sql重用的目的
foreach标签
向sql传递数组或List,mybatis使用foreach解析
需求:根据多个id查询用户信息
sql:select * from user where id in (1,10,39)
Mappe.xml文件
<!-- foreach标签:根据多个id查询用户信息(list ) -->
<select id="selectByIds" resultType="user">
<include refid="selector" />
where id in
<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
#{item}
</foreach>
</select>
<!-- foreach标签:根据多个id查询用户信息(数组) -->
<select id="selectByIds2" resultType="user">
<include refid="selector" />
where id in
<foreach item="item" index="index" collection="array" open="(" separator="," close=")">
#{item}
</foreach>
</select>
<!-- foreach标签:根据多个id查询用户信息(包装对象) -->
<select id="selectByIds3" parameterType="QueryVo" resultType="user">
<include refid="selector" />
where id in
<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
#{item}
</foreach>
</select>
Mapper接口
//根据多个id查询用户信息(foreach标签)
public List<User> selectByIds(List<Integer> list);
public List<User> selectByIds2(Integer[] array);
public List<User> selectByIds3(QueryVo queryVo);
测试
// 根据多个id查询用户信息
@Test
public void demo6() throws IOException {
// 加载核心配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<Integer> list = new ArrayList<Integer>(Arrays.asList(1, 10, 39));
List<User> list2 = userMapper.selectByIds(list);
for (User user2 : list2) {
System.out.println(user2);
}
}
关联查询
一对一查询
Orders对象
public class Orders implements Serializable{
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
private User user;
get/set...
Mapper接口
//一对一查询
public List<Orders> selectOrders();
Mapper.xml文件
<resultMap type="Orders" id="order">
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<association property="user" javaType="User">
<id column="user_id" property="id"/>
<result column="username" property="username"/>
</association>
</resultMap>
<select id="selectOrders" resultMap="order">
select
o.id,
o.user_id,
o.number,
o.createtime,
u.username
from orders o
left join user u
on o.user_id=u.id;
</select>
测试
一对多查询
User对象
public class User implements Serializable {
private static final long serialVersionUID = 1L;
private Integer id;
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String address;// 地址
List<Orders> orders;
get/set...
Mapper接口
//一对多查询
public List<User> selectUsers();
Mapper.xml
<!-- 一对多映射 -->
<!-- public List<User> selectUser(); -->
<resultMap type="User" id="user">
<id column="user_id" property="id" />
<result column="username" property="username" />
<collection property="orders" ofType="Orders">
<id column="id" property="id" />
<result column="number" property="number" />
<result column="createtime" property="createtime" />
</collection>
</resultMap>
<select id="selectUsers" resultMap="user">
select
o.id,
o.user_id,
o.number,
o.createtime,
u.username
from user u
left join orders o
on o.user_id=u.id;
</select>
测试