mapper练习

package items.user.test;

import com.github.pagehelper.PageHelper; import com.github.pagehelper.PageInfo; import items.user.dao.UserDao; import items.user.domain.User; import org.junit.Test; import tk.mybatis.mapper.entity.Example; import tlits.MyBatisUtils; import java.util.ArrayList; import java.util.Collections; import java.util.Date; import java.util.List;

public class test {

/** * 需求：对用户信息进行查询， * 要求查询：用户名包含“小”，并且日期在”2018-01-01”~”2020-12-01”的用户信息 * 这些信息需要根据日期进行倒序排序 * 展示结果时进行分页，一次展示2条数据，展示第一页数据 */ @Test public void run14(){ UserDao dao = MyBatisUtils.getMapper(UserDao.class); PageHelper.startPage(1,2); Example e = new Example(User.class); Example.Criteria c = e.createCriteria(); c.andLike("username","%小%"); c.andBetween("birthday","2018-01-01","2020-12-01"); // 第一个是空格 e.setOrderByClause(" birthday desc"); List<User> users = dao.selectByExample(e); PageInfo<User> pi = new PageInfo<>(users); for (User user : pi.getList()) { System.out.println(user); } MyBatisUtils.close(); } /** * 排序 * */ @Test public void run13(){ UserDao dao = MyBatisUtils.getMapper(UserDao.class); Example e = new Example(User.class); e.setOrderByClause("uid asc"); List<User> users = dao.selectByExample(e); for (User user : users) { System.out.println(user); } MyBatisUtils.close(); } /** * 根据 条件进行数据修改 * */ @Test public void run12(){ UserDao dao = MyBatisUtils.getMapper(UserDao.class); // 进行条件拼接前步骤 Example e = new Example(User.class); Example.Criteria c = e.createCriteria(); c.andLike("username", "%小%"); User user = new User(null,null,null,"女",null); int i = dao.updateByExampleSelective(user, e); System.out.println("操作修改了"+i+"条数据"); MyBatisUtils.commitAndClose(); } /** * 根据 ID 批量删除 * */ @Test public void run11(){ UserDao dao = MyBatisUtils.getMapper(UserDao.class); // 进行条件拼接前步骤 Example e = new Example(User.class); Example.Criteria c = e.createCriteria(); ArrayList<Integer> integers = new ArrayList<>(); Collections.addAll(integers,1,3,6); c.andIn("uid",integers); int i = dao.deleteByExample(e); System.out.println("操作成功了"+i+"条数据"); MyBatisUtils.commitAndClose(); } /** * 条件查询 * select * from user where username = '管理员' or sex = '女' * */ @Test public void run10(){ UserDao dao = MyBatisUtils.getMapper(UserDao.class); // 进行条件拼接前步骤 Example e = new Example(User.class); Example.Criteria c = e.createCriteria(); // 第一个条件用 and ArrayList<Integer> integers = new ArrayList<>(); Collections.addAll(integers,1,3,5); c.andEqualTo("username","管理员"); c.orEqualTo("sex","女"); List<User> users = dao.selectByExample(e); for (User user : users) { System.out.println(user); } MyBatisUtils.close(); } /** * 条件查询 * ID : 1,3,5 * */ @Test public void run9(){ UserDao dao = MyBatisUtils.getMapper(UserDao.class); // 进行条件拼接前步骤 Example e = new Example(User.class); Example.Criteria c = e.createCriteria(); // 第一个条件用 and ArrayList<Integer> integers = new ArrayList<>(); Collections.addAll(integers,1,3,2); c.andIn("uid",integers); c.andIsNotNull("address"); List<User> users = dao.selectByExample(e); for (User user : users) { System.out.println(user); } MyBatisUtils.close(); } /** * 条件查询 * SELECT * FROM USER WHERE username LIKE '%小%' AND sex = '男' AND * birthday BETWEEN '2018-01-01' AND '2018-04-01'; */ @Test public void run8(){ UserDao mapper = MyBatisUtils.getMapper(UserDao.class); // 进行条件拼接前步骤 Example e = new Example(User.class); Example.Criteria c = e.createCriteria(); // 拼接条件 c.andLike("username", "%小%"); c.andEqualTo("sex","男"); c.andBetween("birthday","2018-01-01","2018-04-01"); List<User> users = mapper.selectByExample(e); for (User user : users) { System.out.println(user); } MyBatisUtils.close(); } /** *删除数据 ：ID : 11 */ @Test public void run7(){ //获取UserDao UserDao dao = MyBatisUtils.getMapper(UserDao.class); int i = dao.deleteByPrimaryKey(11); System.out.println("操作成功了"+i+"条数据 "); //提交并关闭会话 MyBatisUtils.commitAndClose(); } /** *修改数据 ： 测试小红，生日不变 ，女 ，沭阳 */ @Test public void run6(){ //获取UserDao UserDao dao = MyBatisUtils.getMapper(UserDao.class); //封装数据 User user = new User(6,"金刚",null,"女","沭阳"); //调用UserDao保存 //有 null主动赋值null //int i = dao.updateByPrimaryKey : 遇到null 则 把指定指端赋值为null int i = dao.updateByPrimaryKeySelective(user); System.out.println("操作成功了"+i+"条数据 "); //提交并关闭会话 MyBatisUtils.commitAndClose(); } /** *添加数据 ： 测试小蓝，生日为当天，男 ，商丘 */ @Test public void run5(){ //获取UserDao UserDao dao = MyBatisUtils.getMapper(UserDao.class); //封装数据 User user = new User(null,"小蓝",new Date(),"男","商丘"); //调用UserDao保存 //有 null主动赋值null int insert = dao.insert(user); // 另一种方法 ： int insert = dao.insertSelective(user); （//有 null 忽略字段不填） System.out.println("操作成功了"+insert+"条数据"); //提交并关闭会话 MyBatisUtils.commitAndClose(); } /** * 分页查询，查询第一页，一页显示4条记录 */ @Test public void run4(){ //获取UserDao UserDao dao = MyBatisUtils.getMapper(UserDao.class); //开启分页助手 PageHelper.startPage(1,4); //调用Dao用mapper方法 List<User> users = dao.selectAll(); //将查询结果进行封装 PageInfo<User> pi = new PageInfo<>(users); for (User user : pi.getList()) { System.out.println(user); } MyBatisUtils.close(); } /** * 查询所有 */ @Test public void run3(){ UserDao dao = MyBatisUtils.getMapper(UserDao.class); List<User> users = dao.selectAll(); System.out.println(users); MyBatisUtils.close(); } /** * 主键查询 */ @Test public void run2(){ UserDao dao = MyBatisUtils.getMapper(UserDao.class); User user = dao.selectByPrimaryKey(8); System.out.println(user); MyBatisUtils.close(); } /** * 查询第一页，一页显示3条 */ @Test public void run1(){ //1.获取Dao对象 UserDao mapper = MyBatisUtils.getMapper(UserDao.class); //2.开启分页助手 PageHelper.startPage(1,3); //3.正常调用UserDao List<User> ulist = mapper.search(); //4.将查询结果进行封装 PageInfo<User> pi = new PageInfo<>(ulist); for (User user : pi.getList()) { System.out.println(user); } System.out.println("当前第几："+pi.getPageNum()+"页"); System.out.println("总记录数："+pi.getTotal()); System.out.println("总页数："+pi.getPages()); System.out.println("一页显示 "+pi.getPageSize()+" 条数据"); System.out.println("当前页的所有数据："+pi.getList()); MyBatisUtils.close(); }

}