SpringBoot使用SpringDataJPA通过方法名查询

JPA依赖及配置在这篇文章: SpringBoot框架使用SpringDataJPA

JPA方法名查询

约定方法名一定要根据命名规范来书写，Spring Data 会根据前缀、中间连接词（Or、And、Like、NotNull 等类似 SQL 中的关键字）、内部拼接 SQL 代理生成方法的实现,约定方法名的方法见表:

1. 方法名表图

关键词	SQL符号	举例	最终执行的JPQL 片段
And	and	findByAddressLikeAndState	… where x.lastname = ?1 and x.firstname = ?2
Or	or	findByLastnameOrFirstname	… where x.lastname = ?1 or x.firstname = ?2
top	limit	findTop4	… where x.lastname limit (4)
Is,Equals	=	findByFirstname/ findByFirstnameIs / findByFirstnameEquals	… where x.firstname = ?1
Between	between	findByStartDateBetween	… where x.startDate between ?1 and ?2
LessThan	<	findByAgeLessThan	… where x.age < ?1
LessThanEqual	<=	findByAgeLessThanEqual	… where x.age <= ?1
GreaterThan	>	findByAgeGreaterThan	… where x.age > ?1
GreaterThanEqual	>=	findByAgeGreaterThanEqual	… where x.age >= ?1
After	>	findByStartDateAfter	… where x.startDate > ?1
Before	<	findByStartDateBefore	… where x.startDate < ?1
IsNull	is null	findByAgeIsNull	… where x.age is null
NotNull	is not null	findByAge(Is)NotNull	… where x.age not null
Like	like	findByFirstnameLike	… where x.firstname like ?1
NotLike	not like	findByFirstnameNotLike	… where x.firstname not like ?1
StartingWith	like ‘xxx%’	findByFirstnameStartingWith	… where x.firstname like ?1(parameter bound with appended %)
EndingWith	like ‘xxx%’	findByFirstnameEndingWith	… where x.firstname like ?1(parameter bound with prepended %)
OrderBy	order by	OrderByCreatetimeDesc	… where x.state= ?1 order by x.createtime desc
Not	<>	findByLastnameNot	… where x.lastname <> ?1
In	in()	findByAgeIn(Collection ages)	… where x.age in ?1
NotIn	not in()	findByAgeNotIn(Collection ages)	… where x.age not in ?1
TRUE	=true	findByActiveTrue()	… where x.active = true
FALSE	=false	findByActiveFalse()	… where x.active = false

2. 举例说明

RecruitDao 代码

package com.tensquare.recruit.dao;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

import com.tensquare.recruit.pojo.Recruit;

import java.util.List;

/**
 * recruit数据访问接口
 *
 * @author Administrator
 */
public interface RecruitDao extends JpaRepository<Recruit, String>, JpaSpecificationExecutor<Recruit> {
    // 查询状态为2并以创建日期降序排序，查询前4条记录 推荐职位查询
    public List<Recruit> findTop4ByStateOrderByCreatetimeDesc(String state);

    // 查询状态不为0 并以创建日期降序排序，查询前12条记录 最新职位查询
    public List<Recruit> findTop12ByStateNotOrderByCreatetimeDesc(String state);

    // 查询招聘信息地址在大望路的并且状态为2  And,Like应用查询
    public List<Recruit> findByAddressLikeAndState(String address,String state);

}

RecruitService 代码

/**
 * recruit服务层
 *
 * @author Administrator
 */
@Service
@Transactional
public class RecruitService {

    @Autowired
    private RecruitDao recruitDao;

    @Autowired
    private IdWorker idWorker;

    /*
     * 查询招聘信息地址在大望路的并且状态为2
     * */
    public List<Recruit> findAdressAndState() {
        List<Recruit> latestJobList = recruitDao.findByAddressLikeAndState("%大望路%", "2");
        return latestJobList;
    }

    /*
     * 最新职位查询
     * */

    public List<Recruit> theLatestJob() {
        List<Recruit> latestJobList = recruitDao.findTop12ByStateNotOrderByCreatetimeDesc("0");
        return latestJobList;
    }

    /*
     * 查询前四条推荐职位
     * */
    public List<Recruit> recommendPosition() {
        List<Recruit> tuijianList = recruitDao.findTop4ByStateOrderByCreatetimeDesc("2");
        return tuijianList;
    }
  
}

RecruitController 代码

/**
 * recruit控制器层
 *
 * @author Administrator
 */
@RestController
@CrossOrigin
@RequestMapping("/recruit")
public class RecruitController {

    @Autowired
    private RecruitService recruitService;

    /*
     * 查询招聘信息地址在大望路的并且状态为2
     * */
    @RequestMapping(value = "/search/filterList", method = RequestMethod.GET)
    public Result findByAddressLikeAndState() {
        List<Recruit> recruits = recruitService.findAdressAndState();
        return new Result(true, StatusCode.OK, "查询成功", recruits);
    }

    /*
     * 最新职位查询
     * */
    @RequestMapping(value = "/search/newlist", method = RequestMethod.GET)
    public Result theLatestJob() {
        List<Recruit> recruits = recruitService.theLatestJob();
        return new Result(true, StatusCode.OK, "查询成功", recruits);
    }

    /*
     * 推荐职位查询
     * */
    @RequestMapping(value = "/search/recommend", method = RequestMethod.GET)
    public Result recommendPosition() {
        List<Recruit> recruits = recruitService.recommendPosition();
        return new Result(true, StatusCode.OK, "查询成功", recruits);
    }
}

3. 测试

按地址和状态查询:

最新职位查询

推荐职位查询

引用资料: https://blog.csdn.net/weixin_43094965/article/details/117203091