package com.itheima;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;
import javax.persistence.criteria.*;
import java.util.List;
import java.util.Optional;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:beans.xml")
public class AppTest {
@Autowired
private UserDao userDao;
@Test
@Transactional
@Rollback(false)
public void testFindOne1() {
//查询用户 条件 select * from user where username='xiaoming';
Specification<User> spe=new Specification<User>() {
@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
//构建 Predicate 查询条件 包含三部分 列名 方式 参数值
//root 代表的该实体类属性根对象 找root 获取该实体类某个属性
//CriteriaQuery 不解释 用不着
//criteriaBuilder 标准查询条件构建器
//套路:
//1.找root 拿属性
//2. 找criteriaBuilder 调对应方法 构建查询条件
Path<Object> username = root.get("username");
Predicate predicate = criteriaBuilder.equal(username, "xiaoming");
return predicate;
}
};
Optional<User> optional = userDao.findOne(spe);
System.out.println(optional.get());
}
@Test
@Transactional
@Rollback(false)
public void testFindOne2() {
//查询用户 条件 select * from user where age>10;
Specification<User> spe=new Specification<User>() {
@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
//获取root age属性
Path<Integer> age = root.get("age");
//criteriaBuilder 构建
Predicate predicate = criteriaBuilder.gt(age, 10);
return predicate;
}
};
Optional<User> optional = userDao.findOne(spe);
System.out.println(optional.get());
}
@Test
@Transactional
@Rollback(false)
public void testFindOne3() {
//查询用户 条件 select * from user where username='小明' and password='123';
Specification<User> spe=new Specification<User>() {
@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
Path<Object> username = root.get("username");
Predicate predicate1 = criteriaBuilder.equal(username, "xiaoming");
Path<Object> password = root.get("password");
Predicate predicate2 = criteriaBuilder.equal(password, "123");
Predicate and = criteriaBuilder.and(predicate1, predicate2);
Predicate or = criteriaBuilder.or(predicate1, predicate2);
return or;
}
};
Optional<User> optional = userDao.findOne(spe);
System.out.println(optional.get());
}
@Test
@Transactional
@Rollback(false)
public void testFindAll1() {
//查询用户 条件 select * from user where username like '%xiao%';
Specification<User> spe=new Specification<User>() {
@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
Path<String> username = root.get("username");
Predicate like = criteriaBuilder.like(username, "%xiao%");
return like;
}
};
List<User> users = userDao.findAll(spe);
users.forEach(u-> System.out.println(u));
}
@Test
@Transactional
@Rollback(false)
public void testFindSort1() {
//查询用户 条件 select * from user where username like '%xiao%' order by age desc;
Specification<User> spe=new Specification<User>() {
@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
Path<String> username = root.get("username");
Predicate like = criteriaBuilder.like(username, "%xiao%");
return like;
}
};
Sort sort=new Sort(Sort.Direction.DESC,"age");
List<User> users = userDao.findAll(spe, sort);
users.forEach(u-> System.out.println(u));
}
@Test
@Transactional
@Rollback(false)
public void testFindSort2() {
//查询用户 条件 select * from user where username like '%xiao%' order by age desc;
Specification<User> spe=new Specification<User>() {
@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
Path<String> username = root.get("username");
Predicate like = criteriaBuilder.like(username, "%xiao%");
return like;
}
};
Sort sort=new Sort(Sort.Direction.DESC,"age","id");
List<User> users = userDao.findAll(spe, sort);
users.forEach(u-> System.out.println(u));
}
@Test
@Transactional
@Rollback(false)
public void testFindSort3() {
//查询用户 条件 select * from user where username like '%xiao%' order by age desc;
Specification<User> spe=new Specification<User>() {
@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
Path<String> username = root.get("username");
Predicate like = criteriaBuilder.like(username, "%xiao%");
return like;
}
};
Sort sort1=new Sort(Sort.Direction.ASC,"age");
Sort sort2=new Sort(Sort.Direction.DESC,"id");
//合并排序
Sort sort = sort1.and(sort2);
List<User> users = userDao.findAll(spe, sort);
users.forEach(u-> System.out.println(u));
}
@Test
@Transactional
@Rollback(false)
public void testFindPage() {
//查询用户 条件 select * from user where username like '%xiao%' limit 0,2;
Specification<User> spe=new Specification<User>() {
@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
Path<String> username = root.get("username");
Predicate like = criteriaBuilder.like(username, "%xiao%");
return like;
}
};
//参数 第一个参数 代表的页码 第二参数每页的个数
Pageable pageable=PageRequest.of(1,2);
Page<User> page = userDao.findAll(spe, pageable);
List<User> users = page.getContent();
System.out.println("当前页的数据:");
users.forEach(u-> System.out.println(u));
long totalElements = page.getTotalElements();
System.out.println("总个数:"+totalElements);
int totalPages = page.getTotalPages();
System.out.println("总页数:"+totalPages);
}
@Test
@Transactional
@Rollback(false)
public void testFindPageAnd() {
//查询用户 条件 select * from user where username like '%xiao%' order by age asc,id desc limit 0,2;
Specification<User> spe=new Specification<User>() {
@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
Path<String> username = root.get("username");
Predicate like = criteriaBuilder.like(username, "%xiao%");
return like;
}
};
Sort sort1=new Sort(Sort.Direction.ASC,"age");
Sort sort2=new Sort(Sort.Direction.DESC,"id");
//合并排序
Sort sort = sort1.and(sort2);
//参数 第一个参数 代表的页码 第二参数每页的个数
Pageable pageable=PageRequest.of(1,2,sort);
Page<User> page = userDao.findAll(spe, pageable);
List<User> users = page.getContent();
System.out.println("当前页的数据:");
users.forEach(u-> System.out.println(u));
long totalElements = page.getTotalElements();
System.out.println("总个数:"+totalElements);
int totalPages = page.getTotalPages();
System.out.println("总页数:"+totalPages);
}
@Test
@Transactional
@Rollback(false)
public void testFindCount() {
//查询用户 条件 select count(id) from user where username like '%xiao%' ;
Specification<User> spe=new Specification<User>() {
@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
Path<String> username = root.get("username");
Predicate like = criteriaBuilder.like(username, "%xiao%");
return like;
}
};
long count = userDao.count(spe);
System.out.println(count);
}
}