JPA 多表动态查询

 示例一：

@PersistenceContext
EntityManager entityManager;    
public List<Map<String, Object>> getStudentInfo(String idOrName,Integer province,Integer city,Integer area) {
        StringBuilder sql = new StringBuilder("select s.id,s.name,s.sex,s.sid,s.health,s.grade,s.birthday,c.cname,sp.name as spname,d.name as dname,q.virtue,q.intelligence,q.social,q.art from student s join classes c on s.bid = c.cid join speciality sp on c.zid = sp.id left join department d on sp.did = d.id left join quality q on s.sid = q.sid where (s.sid=:idOrName or s.name=:idOrName)");
        Map<String,Object> params = new HashMap<>();
        params.put("idOrName",idOrName);
        if (!StringUtils.isEmpty(province)){
            sql.append(" and d.id=:province");
            params.put("province",province);
        }
        if (!StringUtils.isEmpty(city)){
            sql.append(" and sp.id=:city");
            params.put("city",city);
        }
        if (!StringUtils.isEmpty(area)){
            sql.append(" and c.cid=:area");
            params.put("area",area);
        }
//unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
//用于处理返回结果ALIAS_TO_ENTITY_MAP可以将查询用实体类或者map来接收
        Query nativeQuery = entityManager.createNativeQuery(sql.toString()).unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        //查询参数填充
        for (Map.Entry<String,Object> param:params.entrySet()){
            nativeQuery.setParameter(param.getKey(),param.getValue());
        }
        //获取结果集
        List<Map<String, Object>> res  = nativeQuery.getResultList();
        return res;
    }

示例二：

对于in 关键词的使用：jpa可以直接传入集合

 public  List<Student> testEntity(List<Integer> ids){
        StringBuilder sql = new StringBuilder("select * from student where bid in :ids");
        //用实体类接收也可以写成这样简单的方式
        //Query nativeQuery = entityManager.createNativeQuery(sql.toString(),Student.class);
        Query nativeQuery = entityManager.createNativeQuery(sql.toString()).unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.Transformers.ALIAS_TO_ENTITY_MAP);
        nativeQuery.setParameter("ids",ids);
        List<Student> students = nativeQuery.getResultList();
        return students;
    }