根据用户名模糊查询(输入参数为包装类)
pojo类
public class QueryVo implements Serializable{
private User user;
}
UserMapper 接口
public List<User> findUserByQueryVo(QueryVo vo);
UserMapper.xml
<select id="findUserByQueryVo" parameterType="QueryVo" resultType="com.itheima.mybatis.pojo.User">
select * from user where username like '%${user.username}%'
</select>
测试类
public class MybatisMapperTest {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
QueryVo vo = new QueryVo();
User user = new User();
user.setUsername("tom");
vo.setUser(user);
List<User> us = userMapper.findUserByQueryVo(vo);
for (User u : us) {
System.out.println(u);
}
}
查询数据条数
UserMapper接口
public Integer selectNum();
UserMapper.xml
<select id="selectNum" resultType="Integer">
select count(1) from user
</select>
测试类
public class MybatisMapperTest {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Integer selectNum = userMapper.selectNum();
System.out.println(selectNum);
}
ResultMap
查询所有订单
当数据库的字段名和pojo类的属性名不一致时使用
数据库字段
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....
OrderMapper 接口
public List<Orders> selectAllOrders();
OrderMapper.xml
<mapper namespace="com.itheima.mybatis.mapper.OrderMapper">
<resultMap type="Orders" id="aaa">
<id column="id" property="id"/>
<result column="user_id" property="userId" />
</resultMap>
<select id="selectAllOrders" resultMap="aaa">
select * from orders
</select>
</mapper>
测试类
public void testMapper() throws Exception {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
List<Orders> selectAllOrders = orderMapper.selectAllOrders();
for (Orders orders : selectAllOrders) {
System.out.println(orders);
}
}
动态sql
public class QueryVo implements Serializable{
private User user;
private List<Integer> idsList;
private Integer[] ids;
set\get....
根据性别和名字查询用户
UserMapper 接口
public List<User> findUserBySexAndName(User user);
UserMapper.xml
where 1=1 可以用 一对where标签 代替
where 标签可以去掉第一个前and
<select id="findUserBySexAndName" resultType="User" parameterType="User">
select * from user
//where 1=1
<where>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
<if test="username !=null and username !=''">
and username=#{username}
</if>
</where>
</select>
测试类
public void testMapper() throws Exception {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setSex("1");
user.setUsername("陈小明");
List<User> findUserBySexAndName = userMapper.findUserBySexAndName(user);
for (User user2 : findUserBySexAndName) {
System.out.println(user2);
}
}
根据多个id查询用户信息
- 输入参数为包装类
UserMapper 接口
public List<User> findUserByIds(QueryVo vo);
UserMapper.xml
foreach标签,进行遍历
collection:遍历的集合,这里是QueryVo的ids属性
item:遍历的项目,可以随便写,,但是和后面的#{}里面要一致
open:在前面添加的sql片段
close:在结尾处添加的sql片段
separator:指定遍历的元素之间使用的分隔符
<select id="findUserByIds" resultType="User" parameterType="QueryVo">
select * from user
where id in
<foreach collection="idsList" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</select>
测试类
public void testMapper() throws Exception {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
QueryVo vo = new QueryVo();
List<Integer> ids = new ArrayList<Integer>();
ids.add(16);
ids.add(22);
ids.add(24);
vo.setIdsList(ids);
userMapper.findUserByIds(vo);
}
- 输入参数为Integer[] 类
UserMapper 接口
public List<User> findUserByIds(Integer[] ids);
UserMapper.xml
<select id="findUserByIds" parameterType="Integer" resultType="User">
select * from user
where
<foreach collection="array" item="id" separator="," open="id in(" close=")">
#{id}
</foreach>
</select>
测试类
public void testMapper() throws Exception {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Integer[] ids = new Integer[3];
ids[0] = 16;
ids[1] = 22
ids[2] = 24;
List<User> findUserByIds = userMapper.findUserByIds(ids);
for (User user : findUserByIds) {
System.out.println(user);
}
}
- 输入参数为List类型
UserMapper 接口
public List<User> findUserByIds(List<Integer> ids);
UserMapper.xml
<select id="findUserByIds" parameterType="List" resultType="User">
select * from user
<where>
<foreach collection="list" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</where>
</select>
测试类
public void testMapper() throws Exception {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<Integer> list = new ArrayList<>();
list.add(16);
list.add(22);
List<User> findUserByIds = userMapper.findUserByIds(list);
for (User user : findUserByIds) {
System.out.println(user);
}
}
一对一映射
以订单为中心,订单和用户之间的关系是一对一,一个订单对应一个用户
订单表:
Oders类属性值:
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
用户表:
User类属性值:
private Integer id;
private String username;
private String sex;
private Date birthday;
private String address;
步骤:
1.在订单类中添加用户对象,并重写set\get方法
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
2.在OrderMapper 接口中定义一个方法
public List<Orders> selectOrders();
3.在OrderMapper.xml中添加映射并编写sql语句
column:数据库表字段名
property:pojo类属性
<mapper namespace="com.itheima.mybatis.mapper.OrderMapper">
<resultMap type="Orders" id="order">
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<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>
</mapper>
4.测试
public void testOderList() throws Exception {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
List<Orders> selectOrders = orderMapper.selectOrders();
for (Orders orders : selectOrders) {
System.out.println(orders);
}
}
一对多映射
步骤
1.在User类中添加Orders属性,并设置set\get方法
private List<Orders> orders;
public List<Orders> getOrders() {
return orders;
}
public void setOrders(List<Orders> orders) {
this.orders = orders;
}
2.在UserMapper 接口中定义一个方法
public List<User> selectUser();
3.在UserMapper.xml 文件中添加映射并编写sql语句
<resultMap type="User" id="user">
<id column="uid" property="id"/>
<result column="username" property="username"/>
<collection property="orders" ofType="Orders">
<id column="id" property="id"/>
<result column="number" property="number"/>
</collection>
</resultMap>
<select id="selectUser" resultMap="user">
select u.id as uid,u.username,o.id,o.number,o.createtime
from user u
left join orders o
on u.id=o.user_id
</select>
4.测试
public void testOderList() throws Exception {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> selectUser = userMapper.selectUser();
for (User user : selectUser) {
System.out.println(user);
}
}