Spring Data JPA方法名命名规则

@Repository
public interface VmhostDao extends JpaRepository {

  // 方法一：使用@Query注解，可以自定义接收对象
  // 问题：查询条件要嵌入SQL语句内，一些复杂的情形不好处理，例如某个字段模糊检索，字段是动态的；分页查询countQuery把查询语句重复了一遍

  @Query("select new com.example.demo.entity.VmhostDTO(v, u, t) from VmhostPO v left join AuthUserPO u on v.userid = u.id left join AuthTenantPO t on v.tenantid = t.id where v.name like %?1%")
  List findVmhost(String name);

  @Query("select new com.example.demo.entity.VmhostInfoDTO(v.id, v.name, u.username, t.name) from VmhostPO v left join AuthUserPO u on v.userid = u.id left join AuthTenantPO t on v.tenantid = t.id where v.name like %:name%")
  List findVmhostInfo(String name);

  @Query("select v.id as id, v.name as name, u.username as userName, t.name as tname from VmhostPO v left join AuthUserPO u on v.userid = u.id left join AuthTenantPO t on v.tenantid = t.id")
  List findVmhostInfoByProjection();

  @Query(value = "select new com.example.demo.entity.VmhostInfoDTO(v.id, v.name, u.username, t.name) from VmhostPO v left join AuthUserPO u on v.userid = u.id left join AuthTenantPO t on v.tenantid = t.id where v.name like %:name%", 
  countQuery = "select count(*) from VmhostPO v left join AuthUserPO u on v.userid = u.id left join AuthTenantPO t on v.tenantid = t.id where v.name like %:name%")
  Page findVmhostInfoByPage(String name, Pageable pageable);

}

public interface VmhostSpecWithRelationDao
    extends JpaRepository, JpaSpecificationExecutor {

  // 方案二：使用Specification查询
  // 问题：实体必须配置关系，否则无法左连接；每个关联对象是单独数据库查询
}



package com.example.demo.service;

@Service
public class VmhostService {

  public List listVmhostSpecWithRelation(String name) {
    Specification spec = (root, cq, cb) -> {
      root.join("user", JoinType.LEFT);
      root.join("tenant", JoinType.LEFT);
      return cb.like(root.get("name"), "%" + name + "%");
    };
    List list = vmhostSpecWithRelationDao.findAll(spec);
    return list;
  }

}

// JpaSpecificationExecutor的参数和JpaRepository不一样，没啥用，SimpleJpaRepository总是用的JpaRepository的参数
public interface VmhostSpecDao extends JpaRepository, JpaSpecificationExecutor {

  // 方案二：@Query和Specification是不能混用的，也无法改变接收结果集对象

  // 无法混用，总是query有效，spec参数压根就不会理会
  @Query("from VmhostPO")
  List findVmhost(Specification spec);

  // 覆盖JpaSpecificationExecutor的方法可以吗？一样的，根本不会走到findAll的默认实现 
  @Override
  @Query("select new com.example.demo.entity.VmhostInfoDTO(v.id, v.name, u.username, t.name) from VmhostPO v left join AuthUserPO u on v.userid = u.id left join AuthTenantPO t on v.tenantid = t.id")
  List findAll(Specification spec);
}

  // 这样写没有用，生成如下sql
  // select vmhostpo0_.id as id1_2_, vmhostpo0_.addresses as addresse2_2_, vmhostpo0_.availablezone as availabl3_2_, vmhostpo0_.baremetal as baremeta4_2_, vmhostpo0_.cpucore as cpucore5_2_, vmhostpo0_.createtime as createti6_2_, vmhostpo0_.disksize as disksize7_2_, vmhostpo0_.floatip as floatip8_2_, vmhostpo0_.hostname as hostname9_2_, vmhostpo0_.locked as locked10_2_, vmhostpo0_.metadata as metadat11_2_, vmhostpo0_.name as name12_2_, vmhostpo0_.privatenetworkid as private13_2_, vmhostpo0_.ramsize as ramsize14_2_, vmhostpo0_.tenantid as tenanti15_2_, vmhostpo0_.tenantname as tenantn16_2_, vmhostpo0_.type as type17_2_, vmhostpo0_.userid as userid18_2_, vmhostpo0_.username as usernam19_2_, vmhostpo0_.vmstatus as vmstatu20_2_ from vmhost vmhostpo0_ cross join auth_user authuserpo1_ cross join auth_tenant authtenant2_ where vmhostpo0_.userid=authuserpo1_.id and vmhostpo0_.tenantid=authtenant2_.id and (vmhostpo0_.name like ?)
public Optional listVmhostSpec(String name) {
  Specification spec = (root, cq, cb) -> {
    // 只能cross join，要left join需要在实体上建立关系
    Root user = cq.from(AuthUserPO.class);
    Root tenant = cq.from(AuthTenantPO.class);
    // 这里执行select没有用，这个函数只能返回查询条件，外层会覆盖select
    cq.multiselect(root.get("id"), root.get("name"), user.get("username"), tenant.get("name"));
    return cb.and(cb.equal(root.get("userid"), user.get("id")), cb.equal(root.get("tenantid"), tenant.get("id")),
        cb.like(root.get("name"), "%" + name + "%"));

  };
  return vmhostSpecDao.findOne(spec);
}

protected  TypedQuery getQuery(@Nullable Specification spec, Class domainClass, Sort sort) {

    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery query = builder.createQuery(domainClass);

    Root root = applySpecificationToCriteria(spec, domainClass, query);
    query.select(root);

    if (sort.isSorted()) {
        query.orderBy(toOrders(sort, root, builder));
    }

    return applyRepositoryMethodMetadata(em.createQuery(query));
}

@Repository
public class VmhostEMDao {

  @Autowired
  @PersistenceContext
  private EntityManager entityManager;

  // 方案三：使用原生的entityManager，解决@Query的SQL无法动态拼接问题
  // 此时分页就需要自己封装了，也没有了JPA自动实现的接口
  // 注意这里like后面要引号

  @SuppressWarnings("unchecked")
  public List findVmhost(String name) {
    List list = entityManager.createQuery(
        "select new com.example.demo.entity.VmhostDTO(v, u, t) from VmhostPO v left join AuthUserPO u on v.userid = u.id left join AuthTenantPO t on v.tenantid = t.id where v.name like '%"
            + name + "%'")
        .getResultList();
    return list;
  }

  @SuppressWarnings("unchecked")
  public List findVmhostInfoByProjection() {
    // 此时总是Object[]，不支持投影
    List list = entityManager.createQuery(
        "select v.id as id, v.name as name, u.username as userName, t.name as tname from VmhostPO v left join AuthUserPO u on v.userid = u.id left join AuthTenantPO t on v.tenantid = t.id")
        .getResultList();
    return list;
  }

}

@Repository
public class VmhostCQDao {

  @Autowired
  @PersistenceContext
  private EntityManager entityManager;

  // 方案四：相对于方案三，使用了类型安全的CriteriaQuery，其实Specification也是用的CriteriaQuery，所以存在和Specification一样的限制，但是可以控制select了，比Specification灵活一点

  public List findVmhost(String name) {
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery query = builder.createQuery(VmhostDTO.class);
    // 实体上没有配置关系，无法使用left join，只能联合查询(inner join)
    Root root = query.from(VmhostPO.class);
    Root rootUser = query.from(AuthUserPO.class);
    Root rootTenant = query.from(AuthTenantPO.class);
    query.multiselect(root, rootUser, rootTenant).where(builder.equal(root.get("userid"), rootUser.get("id")),
        builder.equal(root.get("tenantid"), rootTenant.get("id")), builder.like(root.get("name"), "%" + name + "%"));

    List list = entityManager.createQuery(query).getResultList();
    return list;
  }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14

package com.example.demo.dao;

import com.example.demo.entity.VmhostPO;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;

public interface VmhostQDSLDao
    extends JpaRepository, QuerydslPredicateExecutor, VmhostRepository {

  // 方案五：VmhostRepository使用原生的entityManager配合QueryDSL，完美解决所有问题
  // 对于单表也可以使用QuerydslPredicateExecutor，自动拥有默认实现

}

1
2
3
4
5
6
7

@NoRepositoryBean
public class BaseRepository {

  @PersistenceContext
  protected EntityManager em;

}

1
2
3
4
5
6
7

public interface VmhostRepository {

  public List findVmhost(Predicate predicate);

  public QueryResults findVmhostByPage(Predicate predicate, Pageable pageable);

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

@Repository
public class VmhostRepositoryImpl extends BaseRepository implements VmhostRepository {

  // 多表左连接

  @Override
  public List findVmhost(Predicate predicate) {
    JPAQueryFactory queryFactory = new JPAQueryFactory(em);
    JPAQuery jpaQuery = queryFactory
        .select(Projections.constructor(VmhostDTO.class, QVmhostPO.vmhostPO, QAuthUserPO.authUserPO,
            QAuthTenantPO.authTenantPO))
        .from(QVmhostPO.vmhostPO).leftJoin(QAuthUserPO.authUserPO)
        .on(QVmhostPO.vmhostPO.userid.stringValue().eq(QAuthUserPO.authUserPO.id.stringValue()))
        .leftJoin(QAuthTenantPO.authTenantPO)
        .on(QVmhostPO.vmhostPO.tenantid.stringValue().eq(QAuthTenantPO.authTenantPO.id.stringValue()));
    jpaQuery.where(predicate);
    return jpaQuery.fetch();
  }

  @Override
  public QueryResults findVmhostByPage(Predicate predicate, Pageable pageable) {
    JPAQueryFactory queryFactory = new JPAQueryFactory(em);
    JPAQuery jpaQuery = queryFactory
        .select(Projections.constructor(VmhostDTO.class, QVmhostPO.vmhostPO, QAuthUserPO.authUserPO,
            QAuthTenantPO.authTenantPO))
        .from(QVmhostPO.vmhostPO).leftJoin(QAuthUserPO.authUserPO)
        .on(QVmhostPO.vmhostPO.userid.stringValue().eq(QAuthUserPO.authUserPO.id.stringValue()))
        .leftJoin(QAuthTenantPO.authTenantPO)
        .on(QVmhostPO.vmhostPO.tenantid.stringValue().eq(QAuthTenantPO.authTenantPO.id.stringValue()))
        .offset(pageable.getOffset()).limit(pageable.getPageSize());
    jpaQuery.where(predicate);
    return jpaQuery.fetchResults();
  }

}

1

@EnableJpaRepositories(repositoryBaseClass = XXXXXX.class)