jpa

@GetMapping("/a")
    public List<Users> barChartOfPrisonArea() {

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        //返回值接收类
        CriteriaQuery<UsersVo> criteriaQuery = criteriaBuilder.createQuery(UsersVo.class);
        Root<Users> root = criteriaQuery.from(Users.class);
        //连表 需映射
        Join<Object, Object> root2 = root.join("users2", JoinType.LEFT);
        List<Predicate> predicateList = new ArrayList<>();
        predicateList.add(criteriaBuilder.between(root.get("age"),3,6));
        criteriaQuery.multiselect(
                                root2.get("name"),
                                root.get("name"),
                                root.get("id"),
                                criteriaBuilder.count(root),
                                criteriaBuilder.sum(root.get("age"))
                                ).where(
                                        predicateList.toArray(new Predicate[predicateList.size()])
                                ).groupBy(
                                        root.get("name"),
                                        root.get("id"),
                                        root2.get("name")
                                ).orderBy(
                                        criteriaBuilder.desc(root.get("id"))
                                );
        List list = entityManager.createQuery(criteriaQuery).getResultList();
        System.out.println(list);
        return list;
    }

 public static Specification<MainCard> getDistinctByMainCardId(List<Long> mainCardIdList) {
        return new Specification<MainCard>() {
            @Override
            public Predicate toPredicate(Root<MainCard> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
               if(mainCardIdList.size() == 0){
                   mainCardIdList.add(-1L);
               }
                return criteriaBuilder.in(root.get("id")).value(mainCardIdList);
            }
        };
    }

    public static Specification<RegistrationOfDeath> getCauseBySortAndCode(String causeSort, String causeCode) {
        return new Specification<RegistrationOfDeath>() {
            @Override
            public Predicate toPredicate(Root<RegistrationOfDeath> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {

                List<Predicate> predicateList = new ArrayList<>();
                predicateList.add(criteriaBuilder.equal(root.get("causeOfDeath").get("sort"), causeSort));
                predicateList.add(criteriaBuilder.equal(root.get("causeOfDeath").get("code"), causeCode));
                return criteriaBuilder.and(predicateList.toArray(new Predicate[predicateList.size()]));
            }
        };
    }

    public static Specification<SpecialPrisoners> getLevel(String levelSort,String levelCodeM,String levelCode) {

        return new Specification<SpecialPrisoners>() {
            @Override
            public Predicate toPredicate(Root<SpecialPrisoners> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {

                 return criteriaBuilder.and(
                         criteriaBuilder.equal(root.get("level").get("sort"), levelSort),
                         criteriaBuilder.or(
                                            criteriaBuilder.equal(root.get("level").get("code"), levelCodeM),
                                            criteriaBuilder.equal(root.get("level").get("code"), levelCode)
                                            )

                 );
            }
        };
    }

    public static Specification<ExecutionOutsidePrison> getProtectionRegistration(ProtectionRegistration protectionRegistration) {

        return new Specification<ExecutionOutsidePrison>() {
            @Override
            public Predicate toPredicate(Root<ExecutionOutsidePrison> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                return criteriaBuilder.isMember(protectionRegistration, root.get("protectionRegistrationSet"));
            }
        };
    }

    public static Specification<MainCard> getMainCardByNotIn(List<Long> mainCardIdList) {
        return new Specification<MainCard>() {
            @Override
            public Predicate toPredicate(Root<MainCard> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                if (mainCardIdList.isEmpty()) {
                    mainCardIdList.add(-1L);
                }
                return criteriaBuilder.in(root.get("id")).value(mainCardIdList).not();
            }
        };
    }

package com.aidy.test;



import com.aidy.SpringbootJpaApplication;
import com.aidy.UsersRepository.*;
import com.aidy.pojo.Roles;
import com.aidy.pojo.Users;
import com.aidy.utils.CheckUtil;
import com.aidy.utils.CollectionUtils;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.*;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.persistence.criteria.*;
import java.util.*;



/**
 * 测试类
 */
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = SpringbootJpaApplication.class)
public class TestJpaOneToMany {
    @Autowired
    private RolesJPARepository rolesJPARepository;

    @Autowired
    private TestSpeciRepository testSpeciRepository;

	 @PersistenceContext
    private EntityManager entitymanager;//注入entitymanager

    //单表多条件查询--方式一
    /**
     * root 是查询结果的一个实体对象，也就是查询结果返回的主要对象
     * criteriaQuery select * from t_name
     * where
     * orderby  groupby
     * criteriaBuilder 条件构造器
     */
    Specification speci = new Specification() {

        @Override
        public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) {
            Predicate pre_name = criteriaBuilder.like(root.get("name"), "张三");
            Predicate pre_age = criteriaBuilder.greaterThanOrEqualTo(root.get("age"), 20);
            Predicate pre = criteriaBuilder.and(pre_name, pre_age);
            return pre;
        }
    };

//单表多条件查询--方式二

    /**
     * root 是查询结果的一个实体对象，也就是查询结果返回的主要对象
     * criteriaQuery
     * criteriaBuilder 条件构造器
     */
    @Test
    public void testSpecificationForone_3() {
        Specification speci = new Specification() {

            @Override
            public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<Predicate>();
                predicates.add(criteriaBuilder.like(root.get("name").as(String.class), "张三"));
                predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("age").as(Integer.class), 20));
                return criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
            }
        };
    }

    /**
     * 单表多条件查询方式三--多条件的话,就将多个条件放到一个集合中
     * 1创建集合存储查询条件
     * 2.将查询条件通过条件构造器的方法添加到集合中
     * 3.通过条件构造器的and 方法将所有条件联合起来
     */
    @Test
    public void testSpecificationForone_2() {
        Specification<Users> speci = new Specification<Users>() {
            @Override
            public Predicate toPredicate(Root<Users> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<Predicate>();
                predicates.add(criteriaBuilder.like(root.get("name").as(String.class), "张三"));
                predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("age").as(Integer.class), 20));
                //可变长度参数可以传递一个数组
                return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
            }
        };
        //返回一个对象
        Optional one = testSpeciRepository.findOne(speci);
        if (one.isPresent()) {
            System.out.println("one" + one.get());
        }

    }


    //排序加分页
    @Test
    public void TestOneToMantPagingAndSorting() {
        //排序等定义
        Sort sort = new Sort(Sort.Direction.DESC, "roleid");
        //分页的定义
        Pageable pageable = PageRequest.of(0, 5, sort);

        Specification<Roles> spec = new Specification<Roles>() {
            @Override
            public Predicate toPredicate(Root<Roles> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                return criteriaBuilder.like(root.get("rolename"), "admin");
            }
        };
        Page<Roles> rolesList = rolesJPARepository.findAll(spec, pageable);
        List<Roles> roles = rolesList.getContent();
        System.out.println(roles.size() + "--" + rolesList.getTotalPages() + "**" + rolesList.getTotalElements());
        for (Roles role : roles) {
            System.out.println(role);
            System.out.println(role.getUsers());
        }
    }


    //分组
    @Test
    public void TestGroupBy() {
        Specification<Roles> spec = new Specification<Roles>() {
            @Override
            public Predicate toPredicate(Root<Roles> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                Predicate pre1 = criteriaBuilder.like(root.get("rolename"), "admin");
                return criteriaQuery.where(pre1).groupBy(root.get("roleid")).getRestriction();
            }
        };
        // long count = rolesJPARepository.count(spec);
        List<Roles> list = rolesJPARepository.findAll(spec);
        for (Roles roles : list) {
            System.out.println(list);
        }
    }

    //总记录数
    @Test
    public void TestCount() {
        Specification<Roles> spec = new Specification<Roles>() {
            @Override
            public Predicate toPredicate(Root<Roles> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                return criteriaBuilder.like(root.get("rolename"), "admin");
            }
        };
        long count = rolesJPARepository.count(spec);
        System.out.println(count);
    }

	//求和
	@Test
    public void testSum() {
        int countAge = 0;
        int countId =0;
      List<Users> list = Lists.newArrayList(testPagingAndSortingRepository.findAll());
        for (Users users : list) {
                countAge+=users.getAge();
                countId+=users.getId();
        }
        System.out.println(countAge+"==="+countId);
    }


	    /**
     * 1注入对象: @PersistenceContext
     *            private EntityManager entitymanager;
     * 2求和字段为 Long 类型
     * 3添加对应构造
     *
     */
    @Test
    public void testSum1() {
        CriteriaBuilder criteriaBuilder = entitymanager.getCriteriaBuilder();
        CriteriaQuery<Users> criteriaQuery = criteriaBuilder.createQuery(Users.class);
        Root<Users> root = criteriaQuery.from(Users.class);
        criteriaQuery.multiselect(criteriaBuilder.sum(root.get("age")),criteriaBuilder.sum(root.get("id")));
        List<Users> list = entitymanager.createQuery(criteriaQuery).getResultList();
        System.out.println(list.get(0).getAge()+"---"+list.get(0).getId());
    }


    //使用in查询,返回对象集合
    @Test
    public void TestIn_1() {
        Specification specification = new Specification() {
            @Override
            public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) {
                ArrayList<Integer> ids = new ArrayList<>();
                ids.add(17);
                ids.add(18);
                return root.in(ids);
            }
        };

        List<Users> list = testSpeciRepository.findAll(specification);
        for (Users user : list) {
            System.out.println("list" + user);
        }

    }

    //使用in查询,返回对象集合
    @Test
    public void TestIn() {
        Integer[] idArr = {1, 2, 3, 4};
        List<Integer> ids = (List<Integer>) CollectionUtils.arrayToList(idArr);
        Specification<Roles> spec = new Specification<Roles>() {
            @Override
            public Predicate toPredicate(Root<Roles> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<>();

                if (CheckUtil.valid(ids)) {
                    CriteriaBuilder.In<Object> in = criteriaBuilder.in(root.get("roleid"));
                    for (Integer id : ids) {
                        in.value(id);
                    }
                    predicates.add(in);
                }
                return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
            }
        };
        List<Roles> list = rolesJPARepository.findAll(spec);
        for (Roles roles : list) {
            System.out.println(roles);
        }
    }

    /**
     * 单表多条件查询--多条件的话,就将多个条件放到一个集合中
     * 添加条件中既有and关系又有or关系
     * <p>
     * <p>
     * 1.通过条件构造器的or方法中再通过条件构造器添加并列条件
     */
    @Test
    public void testSpecificationAndOrOr() {
        Specification<Users> speci = new Specification<Users>() {
            @Override
            public Predicate toPredicate(Root<Users> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                //此处传递了两个predicate对象
                return criteriaBuilder.or(
                        //无论是调用条件构造器的and方法还是or方法,只需给其对应的predicate参数即可
                        //这里and中的条件为并列关系
                        criteriaBuilder.and(criteriaBuilder.like(root.get("name"), "三")/*此处返回一个predicate对象*/,
                                criteriaBuilder.greaterThanOrEqualTo(root.get("age"), 20))/*此处返回一个predicate对象*/,
                        //这里的条件与上边的条件为或者关系
                        criteriaBuilder.equal(root.get("id"), 18)/*此处返回一个predicate对象*/);
            }
        };
        //返回一个对象
        Users users = testSpeciRepository.findOne(speci).get();
        System.out.println(users);
    }


	 /**
     * 多条件排序 + 分页 + 多条件查询
     */
    @Test
    public void testGroupByAndManyOrderByAndPage() {
        Specification<Users> specification = new Specification<Users>() {
            @Override
            public Predicate toPredicate(Root<Users> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<>();
                predicates.add(criteriaBuilder.equal(root.get("roles").get("rolename"), "admin"));
                predicates.add(criteriaBuilder.equal(root.get("roles").get("roleid"), 5));
                criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()]));
                //分组
                criteriaQuery.groupBy(root.get("name"));
                return criteriaQuery.getRestriction();
            }
        };
        //多条件排序
        List<String> sorts = new ArrayList<>();
        sorts.add("id");
        sorts.add("name");
        Sort sort = new Sort(Sort.Direction.DESC, sorts);
        //分页
        Pageable pageable = PageRequest.of(0, 2, sort);
        Page<Users> page = testSpeciRepository.findAll(pageable);
        for (Users users : page.getContent()) {
            System.out.println(users);
        }
    }
	
	 /**
     * 单项多对一
     * 
     * 角色表中
     * @OneToMany
     * 	@LazyCollection(LazyCollectionOption.FALSE)
     * 	private Set<Users1> users = new HashSet<>();
     * 用户表无映射
     * 
     * 需求:通过用户表中的条件查询角色信息
     * <p>
     * 方案:通过角色表中的映射对象字段获取其封装的对应属性
     * SetJoin<Roles1, Users1> users = root.join(root.getModel().getSet("users", Users1.class), JoinType.LEFT);
     */
    @Test
    public void testManyTable() {
        Specification<Roles1> specification = new Specification<Roles1>() {
            @Override
            public Predicate toPredicate(Root<Roles1> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<>();
                SetJoin<Roles1, Users1> users = root.join(root.getModel().getSet("users", Users1.class), JoinType.LEFT);
                List<Integer> ids = new ArrayList<Integer>(){
                    {
                        add(1);
                        add(2);
                    }
                };
                Expression<Integer> exp = users.<Integer>get("id");
                predicates.add(exp.in(ids));
                return criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
            }
        };
        Roles1 roles1 = testSpeciRepository1.findOne(specification).get();
        System.out.println(roles1);
        System.out.println(roles1.getUsers());
    }
}
	 /**
	 *单向一对多
	 *
     * 用户表映射到角色表 用户表中有@ManyToOne(fetch=FetchType.EAGER)
	 *private Roles roles
     * 角色表无映射
     * 需求:通过角色表中的条件查询用户信息
     * <p>
     * 方案:通过用户表中的映射对象字段获取其封装的对应属性
     * criteriaBuilder.equal(users.get("roles").get("rolename"), "admin")
     */
    @Test
    public void testManyTable() {
        Specification<Users> specification = new Specification<Users>() {
            @Override
            public Predicate toPredicate(Root<Users> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<>();
                //Join<Object, Object> roles = root.join("roles", JoinType.LEFT);
                predicates.add(criteriaBuilder.equal(root.get("roles").get("rolename"), "admin"));
                predicates.add(criteriaBuilder.equal(root.get("roles").get("roleid"), 5));

                return criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
            }
        };
        Users users = testSpeciRepository.findOne(specification).get();
        System.out.println(users);
        System.out.println(users.getRoles().getRolename());
    }

	//分组求记录数
	public void contextLoads() {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Object[]> cq = cb.createQuery(Object[].class);
        Root<MainCard> stud = cq.from(MainCard.class);
        cq.multiselect(stud.get("name"), cb.count(stud)).groupBy(stud.get("name"));

        List<Object[]> list = em.createQuery(cq).getResultList();
    }
}

package com.aidy.test;


import com.aidy.SpringbootJpaApplication;
import com.aidy.UsersRepository.*;
import com.aidy.pojo.Roles;
import com.aidy.pojo.Users;
import com.aidy.utils.CheckUtil;
import com.aidy.utils.CollectionUtils;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.*;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Optional;
import java.util.function.Function;


/**
 * 测试类
 */
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = SpringbootJpaApplication.class)
public class TestRepositoryByMMCX {

    @Autowired
    private TestPagingAndSortingRepository pagingAndSortingRepository;

//---------------------------------------------------------------命名查询--------------------------------------------------------------------------

    /**
     * List<Users> findAllBy();
     * <p>
     * 获取所有记录
     */
    @Test
    public void testFindAllByMMCX() {
        List<Users> list = pagingAndSortingRepository.findAllBy();
        for (Users users : list) {
            System.out.println(users);
        }
    }

    /**
     * List<Users> findTop6ByNameLike(String name);
     * <p>
     * 获取前多少条记录
     */
    @Test
    public void testTopByMMCX() {
        List<Users> list = pagingAndSortingRepository.findTop6ByNameLike("%张三%");
        for (Users users : list) {
            System.out.println(users);
        }
    }

    /**
     * List<Users> findFirst6ByNameLike(String name);
     * <p>
     * 获取前多少条记录
     */
    @Test
    public void testFirstByMMCX() {
        List<Users> list = pagingAndSortingRepository.findFirst6ByNameLike("%张三%");
        for (Users users : list) {
            System.out.println(users);
        }
    }

    /**
     * boolean existsById(Integer id);
     * <p>
     * 判断是否存在
     */
    @Test
    public void testExsistByMMCX() {
        System.out.println(pagingAndSortingRepository.existsById(17));
    }

    /**
     * List<Users> findByAgeBetween(Integer age,Integer age1);
     * <p>
     * 区间条件
     */
    @Test
    public void testBetweenByMMCX() {
        List<Users> list = pagingAndSortingRepository.findByAgeBetween(20, 30);
        for (Users users : list) {
            System.out.println(users);
        }
    }

    /**
     * List<Users> findByIdIn(Integer[] ids);
     * <p>
     * 通过in 查询
     */
    @Test
    public void testIn() {
        Integer[] ids = {17, 22, 18};
        List<Users> list = pagingAndSortingRepository.findByIdIn(ids);
        for (Users users : list) {
            System.out.println(users);
        }
    }

    /**
     * List<Users> findByNameLike(String name, Sort sort);
     * <p>
     * 排序
     */
    @Test
    public void testSortByMMCX() {
        Sort sort = new Sort(Sort.Direction.DESC, "id");
        List<Users> list = pagingAndSortingRepository.findByNameLike("%张三%", sort);
        for (Users users : list) {
            System.out.println(list);
        }
    }
	
	  /**
     * List<Users> findByNameLikeOrderByIdDesc(String name);
     * <p>
     * 排序
     */
    @Test
    public void testSort2ByMMCX() {
        List<Users> list = pagingAndSortingRepository.findByNameLikeOrderByIdDesc("%张三%");
        for (Users users : list) {
            System.out.println(users);
        }
    }

    /**
     * Page<Users> findByNameLike(String name, Pageable pageable);
     * <p>
     * 分页
     */
    @Test
    public void testPageByMMCX() {
        Pageable pageable = PageRequest.of(0, 5);

        Page<Users> pageList = pagingAndSortingRepository.findByNameLike("%张三%", pageable);
        List<Users> list = pageList.getContent();
        for (Users users : list) {
            System.out.println(pageList.getTotalPages());
            System.out.println(pageList.getTotalElements());
            System.out.println(users);
        }
    }

    /**
     * Page<Users> findByNameLike(String name, Pageable pageable);
     * <p>
     * 带条件
     * <p>
     * 排序后分页
     */
    @Test
    public void testPageAndSortByMMCX() {
        Sort sort = new Sort(Sort.Direction.DESC, "id");
        Pageable pageable = PageRequest.of(0, 5, sort);

        Page<Users> pageList = pagingAndSortingRepository.findByNameLike("%张三%", pageable);
        List<Users> list = pageList.getContent();
        for (Users users : list) {
            System.out.println(pageList.getTotalPages());
            System.out.println(pageList.getTotalElements());
            System.out.println(users);
        }
    }
//---------------------------------------------------------------命名查询--------------------------------------------------------------------------

    /**
     * 总记录数
     */
    @Test
    public void testCount() {

        pagingAndSortingRepository.count();

    }

}

package com.aidysoft.prisonmanagement.lifeManagement.criminalAccountManagement.test;

public interface UsersProjection {
    String getName();

    String getName2();


}



package com.aidysoft.prisonmanagement.lifeManagement.criminalAccountManagement.test;

import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.rest.core.annotation.RepositoryRestResource;

import java.util.List;

/**
 * 〈〉
 */
@RepositoryRestResource
public interface UserRepository extends JpaSpecificationExecutor<Users>, JpaRepository<Users, Long> {
    List<UsersProjection> findByNameLike(String name);

    @Query(value = "select name from users", nativeQuery = true)
    List<UsersProjection> ddd();

    @Query(value = "select u.name as name,u2.name as name2 from Users u left join u.users2 u2 ")
    List<UsersProjection> eee();

}