java jpa框架多表联查处理

分页和多表关联查询有多种方式

@Query使用

• 单表查询所有：

public interface SubjectDao extends JpaRepository<Subject,Integer>, JpaSpecificationExecutor<User>, Serializable {
   @Query(value = "select * from subject",nativeQuery = true)
   public List<Subject> ul();
}

• 单表分页查询

@Test
    public void hi1(){
        Pageable page=  PageRequest.of(1,2);
        Page<Subject>kl=subjectDao.ul(page);
        System.out.println(kl.getContent().size());
        System.out.println(kl.getTotalElements());
    }

public interface SubjectDao extends JpaRepository<Subject,Integer>, JpaSpecificationExecutor<User>, Serializable {
    @Query(value = "select * from subject",nativeQuery = true)
    public Page<Subject> ul(Pageable pageable);
}

• 多表分页查询

public interface SubjectDao extends JpaRepository<Subject,Integer>, JpaSpecificationExecutor<User>, Serializable {
    @Query(value = "select * from subject s inner join subject_auths sa on s.id=sa.subject_id",nativeQuery = true)
    Page<Subject> ul(Pageable pageable);
}

调试结果如下：

• 此时只能看到subject中的信息，如果我想看到与之关连的subject_auth中的信息。可以做一对一的配置

- subject中的配置，其中subject_id是subject_auth表中的字段，它是subject的外键

其结果如下：

• 查询优化
  上述都是select *查询所有的操作。如果我只想查询部分字段。。
  注意：想当然的如果这样写

public interface SubjectDao extends JpaRepository<Subject,Integer>, JpaSpecificationExecutor<User>, Serializable {
    @Query(value = "select s.id,sa.id from subject s inner join subject_auths sa on s.id=sa.subject_id",nativeQuery = true)
    Page<Subject> ul(Pageable pageable);
}

1. 则会报

Caused by: java.sql.SQLException: Column ‘business_scope’ not found.

spring data jpa执行完查询后，都会将结果一个一个的注入到对应的pojo的每个属性中.但由于我只查询sublect的id和subject_auth的id

select
s.id,
sa.id from
subject s
inner join
subject_auths sa
on s.id=sa.subject_id limit ?,?

所以spring data jpa就会认为你的数据库里subject表中只有id这一个字段，subject_auth里只有id这一个字段。但你的Subject类有businessScope属性，并且做了映射@Column(name=“business_scope”),所以它从结果集中找不到这个字段名，注入到suoject对象的businessScope属性中，就抛出上述错误。

2. 如果查出来的字段有同名的，记得取别名，否则会报错如下：

Caused by: org.hibernate.loader.custom.NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [id] during auto-discovery of a native-sql query

3. 并且还会 报==Unknown column 's' in 'field list'==

运行的sql如下（你只分页查询了部分字段，还想做统计。此时它不知道该按照那个字段做统计 ）
select count(s) from subject s inner join subject_auths sa on s.id=sa.subject_id

针对以上几点问题。我做了进一步的测试

将上述语句改写成jpql语句

public interface SubjectDao extends JpaRepository<Subject,Integer>, JpaSpecificationExecutor<User>, Serializable {
    @Query(value = "From Subject s inner join SubjectAuths sa on s.id=sa.subjectId")
    Page<Object> ul(Pageable pageable);
}

其相应的sql：

Hibernate: 
    select
        subject0_.id as id1_13_0_,
        subjectaut1_.id as id1_14_1_,
        subject0_.business_scope as business2_13_0_,
        subject0_.enterprise_nature as enterpri3_13_0_,
        subject0_.estiblish_time as estiblis4_13_0_,
        subject0_.first_holder as first_ho5_13_0_,
        subject0_.name as name6_13_0_,
        subject0_.reg_capital as reg_capi7_13_0_,
        subject0_.reg_location as reg_loca8_13_0_,
        subject0_.tags as tags9_13_0_,
        subjectaut1_.auth_time as auth_tim2_14_1_,
        subjectaut1_.expiring_time as expiring3_14_1_,
        subjectaut1_.subject_id as subject_4_14_1_,
        subjectaut1_.user_id as user_id5_14_1_ 
    from
        subject subject0_ 
    inner join
        subject_auths subjectaut1_ 
            on (
                subject0_.id=subjectaut1_.subject_id
            ) limit ?,
        ?
Hibernate: 
    select
        count(subject0_.id) as col_0_0_ 
    from
        subject subject0_ 
    inner join
        subject_auths subjectaut1_ 
            on (
                subject0_.id=subjectaut1_.subject_id
            )

---

spring data jpa在使用@Query时，如果nativeQuery=true，并且只是查询部分字段，那么进行多表联查，结果集是List<Object[]>,它不能完成对结果集自动封装，只能我们自己来处理结果集。我们可以写jpql语句，再构造一个vo类型的pojo，通过select new Test(a,b,c)…让它帮我们完成封装。

spring的动态代理我觉得最明显的效果在spring data jpa中发挥的淋漓尽致。通过它完全解放了dao层的操作，可以真正让程序员有更多时间在service层操作上。-----实现了面向接口编程。实现类有spring的代理机制给你完成。

public interface SubjectDao extends JpaRepository<Subject,Integer>, JpaSpecificationExecutor<User>, Serializable {
    @Query(value = "select new org.mj.pojo.Test(s.id,sa.subjectId) from Subject s inner join SubjectAuths sa on s.id=sa.subjectId")
    Page<Test> ul(Pageable pageable);
}




@Getter
@Setter
public class Test implements Serializable {
    Integer id;
    Integer subjectId;

    public Test(Integer id,Integer subjectId){
        this.subjectId=subjectId;
        this.id=id;
    }
}

用jpql不能写 join on应该用 join where

antlr.SemanticException: Path expected for join!

测试说明：

public interface SubjectDao extends JpaRepository<Subject,Integer>, JpaSpecificationExecutor<User>, Serializable {
    @Query(value = "select s.id as sid,sa.id as said from Subject s inner join SubjectAuths sa on s.id=sa.subjectId")
    Page<Object> ul(Pageable pageable);
}

jpql语句的关联查询

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Unable to locate appropriate constructor on class [org.mj.pojo.Test]. Expected arguments are: long, int [select new org.mj.pojo.Test(s.id,sa.subjectId) from org.mj.pojo.Subject s inner join org.mj.pojo.SubjectAuths sa on s.id=sa.subjectId]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74)
at org.hibernate.hql.internal.ast.ErrorTracker.throwQueryException(ErrorTracker.java:93)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:277)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:191)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:143)
at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:119)
at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:80)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153)
at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:611)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:720)
… 132 more

• 解决方案是：