JPA实用手册，即看即用

修改查询

@Modifying

@Query("update User u set u.firstname = ?1 where u.lastname = ?2")

int setFixedFirstnameFor(String firstname, String lastname);

使用 Sort 和 JpaSort

public interface UserRepository extends JpaRepository {

  @Query("select u from User u where u.lastname like ?1%")

  List findByAndSort(String lastname, Sort sort);

  @Query("select u.id, LENGTH(u.firstname) as fn_len from User u where u.lastname like ?1%")

  List findByAsArrayAndSort(String lastname, Sort sort);

}

repo.findByAndSort("lannister", new Sort("firstname"));           

repo.findByAndSort("stark", new Sort("LENGTH(firstname)"));       

repo.findByAndSort("targaryen", JpaSort.unsafe("LENGTH(firstname)"));

repo.findByAsArrayAndSort("bolton", new Sort("fn_len"));   

使用已命名参数

public interface UserRepository extends JpaRepository {

  @Query("select u from User u where u.firstname = :firstname or u.lastname = :lastname")

  User findByLastnameOrFirstname(@Param("lastname") String lastname,

                                 @Param("firstname") String firstname);

}   

原生SQL分页

public interface UserRepository extends JpaRepository {

  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",

    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",

    nativeQuery = true)

  Page findByLastname(String lastname, Pageable pageable);

}

Sort sort =newSort(Sort.Direction.DESC,"createTime");//创建时间降序排序Pageable pageable =newPageRequest(pageNumber,pageSize,sort);

使用原生SQL

public interface UserRepository extends JpaRepository {

  @Query(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?1", nativeQuery = true)

  User findByEmailAddress(String emailAddress);

}

为了消除不确定性，可以在方法名内使用下划线“_”手动定义隔断点。

List findByAddress_ZipCode(ZipCode zipCode);

查询方法建立

distinct flag

ignoring case

order by

public interface PersonRepository extends Repository {

  List findByEmailAddressAndLastname(EmailAddress emailAddress, String lastname);

  // Enables the distinct flag for the query  List findDistinctPeopleByLastnameOrFirstname(String lastname, String firstname);

  List findPeopleDistinctByLastnameOrFirstname(String lastname, String firstname);

  // Enabling ignoring case for an individual property  List findByLastnameIgnoreCase(String lastname);

  // Enabling ignoring case for all suitable properties  List findByLastnameAndFirstnameAllIgnoreCase(String lastname, String firstname);

  // Enabling static ORDER BY for a query  List findByLastnameOrderByFirstnameAsc(String lastname);

  List findByLastnameOrderByFirstnameDesc(String lastname);

}

异步查询结果

@Async

Future findByFirstname(String firstname);           

@Async

CompletableFuture findOneByFirstname(String firstname);

@Async

ListenableFuture findOneByLastname(String lastname);

Like模糊查询

@Query(value = "select name,author,price from Book b where b.name like %:name%")

List findByNameMatch(@Param("name") String name);

In 查询

@Query(value = "select * from trade$seek_purchase_offer where sp_id in (:spIds) and of_enuu = :enUu", nativeQuery = true)

    List getSeekPurchaseOfferList(@Param("spIds") List spIds, @Param("enUu") Long enUu);

MappedSuperClass:

映射为非实体父类，该实体父类不会生成对应的数据表

@OneToOne

@Entity

@Table(name = "costume_all_id")

public class AllId extends AbstractEntity {

    private static final long serialVersionUID = 1L;

    @OneToOne(cascade = CascadeType.ALL)

    @JoinColumn(name = "costume_member_fk")

    private Member member;// 用户表外键

}

@OneToMany和@ManyToOne

@Entity

@Table(name = "costume_organization")

public class Organization extends AbstractEntity {

    private static final long serialVersionUID = 1L;

    @Column(nullable = false, length = 50)

    private String name; // 组织名称

    @OneToMany(mappedBy = "organization")

    private Set departmentSet; // 部门集合

}

@Entity

@Table(name = "costume_department")

public class Department extends AbstractEntity {

    private static final long serialVersionUID = 1L;

    @Column(nullable = false, length = 50)

    private String name; // 部门名称

    @ManyToOne(optional = false)

    private Organization organization; // 组织外键

    @ManyToMany

    private Set memberSet; // 用户表外键

    public Organization getOrganization() {

        return organization;

    }

    @JsonBackReference

    public void setOrganization(Organization organization) {

        this.organization = organization;

    }

}

作者：一位不著名的普通群众
链接：https://www.jianshu.com/p/f548362acd6d