jpa常用查询方法使用总结&自定义sql查询

一、JPA自定义查询方法

方法名需要按指定规则来起：
单条数据：find+by+对象属性名
多条数据：findAll+by+对象属性名

实体类

@Data
public class People {

    private String id;
    private String name;
    private String code;
    private Integer age;
}

1.1 单条件查询一条数据

People findByCode(String code);

1.2 单条件查询多条数据

List<People> findAllByCode(String code);

1.3 多条件查询数据

People findByCodeAndAgeAndName(String code,Integer age,String name);

1.4 查询某一个字段

查到字段所在数据，从整条数据中再拿单个字段

People findByName(String name);

1.5 in查询

List<People> findByCodeIn(List<String> codes);

1.6 like查询

List<People> findAllByNameLike(String name);

二、自定义sql查询

2.1 单条件查询

@Query(value = "select * from people where code =?1 ",nativeQuery = true)
People findPeopleCustomizing(String code);

2.2 多条件查询

@Query(value = "select * from people where code =?1 and age = ?2",nativeQuery = true)
People findPeopleCustomizing(String code,Integer age);

2.3 复杂多条件查询

@Query(value = "select * from people where code =?1 and age = ?2 and name in (?3) ",nativeQuery = true)
People findPeopleCustomizing(String code,Integer age,List<String> name);

2.4 根据id修改数据

@Modifying(clearAutomatically = true)
@Query(nativeQuery = true,value = "update people set code = ?1 where id = ?2 ")
int updatePeople(String code, String id);

总览

@Repository
public interface PeopleRepository extends JpaRepository<People,String>, JpaSpecificationExecutor<People> {

    //jpa自带的方法，方法名需要按制定规则来起
    //1.单条件查询一条数据
    People findByCode(String code);

    //2.单条件查询多条数据
    List<People> findAllByCode(String code);

    //3.多条件查询数据
    People findByCodeAndAgeAndName(String code,Integer age,String name);

    //4.查询某一个字段
    People findByName(String name);

    //5.in查询
    List<People> findByCodeIn(List<String> codes);

    //6.like查询
    List<People> findAllByNameLike(String name);

    //自定义sql，方法名随意
    //1.自定义sql：单条件查询
    @Query(value = "select * from people where code =?1 ",nativeQuery = true)
    People findPeopleCustomizing(String code);

    //2.自定义sql：多条件查询
    @Query(value = "select * from people where code =?1 and age = ?2",nativeQuery = true)
    People findPeopleCustomizing(String code,Integer age);

    //3.自定义sql：复杂多条件查询
    @Query(value = "select * from people where code =?1 and age = ?2 and name in (?3) ",nativeQuery = true)
    People findPeopleCustomizing(String code,Integer age,List<String> name);

    //4.自定义sql：根据id修改数据
    @Modifying(clearAutomatically = true)
    @Query(nativeQuery = true,value = "update people set code = ?1 where id = ?2 ")
    int updatePeople(String code, String id);

}