如何快速一键搞定JPA中所有findBy语法规则

逃离我推掉我的手 2022-06-01 10:35 251阅读 0赞

**JPA中findBy基本语法规则**

**1.首先先新建一个数据库，名字叫做jpatest**

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1][]

**2.新建一个SpringBoot项目（如果新手还不会，请先阅读idea中如何快速创建SpringBoot项目）**

**这边需要引入jpa+mysql+web的相关依赖，如果创建的时候没有引入就需要在pom.xml进行配置**

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 1][]

**新建一个controller包（控制类所在地），entity包（实体类所在地），repository包（dao类所在地）**

**3.application.properties配置文件中添加mysql数据库和jpa的相关配置**

\#数据库驱动

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

\#数据库url地址，其中jpatest是数据库名称

spring.datasource.url=jdbc:mysql://localhost:3308/jpatest?useUnicode=true&characterEncoding=utf-8

\#用户名称

spring.datasource.username=root

\#用户密码

spring.datasource.password=220316

\#创建数据库表结构（更新的状态下创建）

spring.jpa.hibernate.ddl-auto=update

\#显示执行的sql语句

spring.jpa.show-sql=true

**4.新建表映射实体类**

package com.lzq.jpa.entity;

import org.hibernate.annotations.GenericGenerator;

import javax.persistence.\*;

/\*\*

\* Created by qiang on 2018/1/22.

\*/

@Entity

@Table(name="user")

public class User \{

@Id

@GenericGenerator(name = "PKUUID", strategy = "uuid2")

@GeneratedValue(generator = "PKUUID")

@Column(length = 36)

protected String id;

/\*\*

\* 名字

\*/

@Column(name = "name", nullable = true, length = 30)

private String name;

/\*\*

\* 身高

\*/

@Column(name = "height", nullable = true, length = 10)

private Integer height;

public User() \{

public String getId() \{

return id;

public void setId(String id) \{

this.id = id;

public String getName() \{

return name;

public void setName(String name) \{

this.name = name;

public Integer getHeight() \{

return height;

public void setHeight(Integer height) \{

this.height = height;

关于实体类的注释我会在接下来的章节中具体的介绍给大家，这篇文章先不做介绍。

**5.jpa的数据操作接口（也是本章节中的重点）**

package com.lzq.jpa.entity.repository;

import com.lzq.jpa.entity.User;

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

import java.util.List;

/\*\*

\* Created by qiang on 2018/1/22.

\*/

public interface UserRepository extends JpaRepository<User,String>\{

/\*\*

\* 相当于 select \*from user where name=?

\* @param name

\* @return

\*/

public List<User> findByName(String name);

/\*\*

\* 相当于select \*from user where name like ?

\* 但是有一点需要注意的是，%需要我们自己来写

\* @param name

\* @return

\*/

public List<User> findByNameLike(String name);

/\*\*

\* 相当于select \*from user where name not like ?

\* 但是有一点需要注意的是，%需要我们自己来写

\* @param name

\* @return

\*/

public List<User> findByNameNotLike(String name);

/\*\*

\* 相当于 select \*from user where name <> ?

\* @param name

\* @return

\*/

public List<User> findByNameNot(String name);

/\*\*

\* 相当于 select \*from user where id in (?)

\* @param ids

\* @return

\*/

public List<User> findByIdIn(List<String> ids);

/\*\*

\* 相当于 select \*from user where id not in ()

\* @param ids

\* @return

\*/

public List<User> findByIdNotIn(List<String> ids);

/\*\*

\* 相当于 select \*from user where name=? order by height desc

\* @param name

\* @return

\*/

public List<User> findByNameOrderByHeightDesc(String name);

/\*\*

\* 相当于 select \*from user where name=? order by height asc

\* @param name

\* @return

\*/

public List<User> findByNameOrderByHeightAsc(String name);

/\*\*

\* 相当于 select \*from user where name is null

\* @return

\*/

public List<User> findByNameIsNull();

/\*\*

\* 相当于 select \*from user where name is not null

\* @return

\*/

public List<User> findByNameIsNotNull();

/\*\*

\* 相当于 select \*from user where name =? and height=?

\* @param name

\* @param height

\* @return

\*/

public List<User> findByNameAndHeight(String name,int height);

/\*\*

\* 相当于 select \*from user where name =? or height=?

\* @param name

\* @param height

\* @return

\*/

public List<User> findByNameOrHeight(String name,int height);

/\*\*

\* 相当于 select \*from user where height between ? and ?

\* 需要注意的是mysql是有包含两个端点值的

\* @param start

\* @param end

\* @return

\*/

public List<User> findByHeightBetween(int start,int end);

/\*\*

\* 相当于 select \*from user where height < ?

\* 需要注意的是mysql是没有包含端点值的

\* @param less

\* @return

\*/

public List<User> findByHeightLessThan(int less);

/\*\*

\* 相当于 select \*from user where height > ?

\* 需要注意的是mysql是没有包含端点值的

\* @param greater

\* @return

\*/

public List<User> findByHeightGreaterThan(int greater);

具体语法规则和对应的sql都在代码中给出来了，这边需要和大家说的是UserRepository接口的特点。我们通过继承JpaRepository《对应的实体类，主键属性值》来编写findBy等相关的函数来查询数据库。继承JpaRepository的接口在使用的时候，通过@Autowired会自动创建接口的实现类，不需要怎么去实现这个接口，这也是jpa最方便的地方。

**5.通过控制类调用dao接口**

package com.lzq.jpa.controller;

import com.lzq.jpa.entity.User;

import com.lzq.jpa.entity.repository.UserRepository;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.ResponseBody;

import java.util.ArrayList;

import java.util.List;

/\*\*

\* Created by qiang on 2018/1/22.

\*/

@Controller

@RequestMapping(value = "jpa")

public class UserController \{

@Autowired

private UserRepository userRepository;

@RequestMapping(value = "findByName",method = RequestMethod.GET)

@ResponseBody

public List<User> findByName(String name)\{

return userRepository.findByName(name);

@RequestMapping(value = "findByHeightBetween",method = RequestMethod.GET)

@ResponseBody

public List<User> findByHeightBetween(int start,int end)\{

return userRepository.findByHeightBetween(start,end);

@RequestMapping(value = "findByHeightLessThan",method = RequestMethod.GET)

@ResponseBody

public List<User> findByHeightLessThan(int less)\{

return userRepository.findByHeightLessThan(less);

@RequestMapping(value = "findByHeightGreaterThan",method = RequestMethod.GET)

@ResponseBody

public List<User> findByHeightGreaterThan(int greater)\{

return userRepository.findByHeightGreaterThan(greater);

@RequestMapping(value = "findByNameAndHeight",method = RequestMethod.GET)

@ResponseBody

public List<User> findByNameAndHeight(String name,int height)\{

return userRepository.findByNameAndHeight(name,height);

@RequestMapping(value = "findByNameOrHeight",method = RequestMethod.GET)

@ResponseBody

public List<User> findByNameOrHeight(String name,int height)\{

return userRepository.findByNameOrHeight(name,height);

@RequestMapping(value = "findByNameIsNull",method = RequestMethod.GET)

@ResponseBody

public List<User> findByNameIsNull()\{

return userRepository.findByNameIsNull();

@RequestMapping(value = "findByNameIsNotNull",method = RequestMethod.GET)

@ResponseBody

public List<User> findByNameIsNotNull()\{

return userRepository.findByNameIsNotNull();

@RequestMapping(value = "findByNameOrderByHeightDesc",method = RequestMethod.GET)

@ResponseBody

public List<User> findByNameOrderByHeightDesc(String name)\{

return userRepository.findByNameOrderByHeightDesc(name);

@RequestMapping(value = "findByNameOrderByHeightAsc",method = RequestMethod.GET)

@ResponseBody

public List<User> findByNameOrderByHeightAsc(String name)\{

return userRepository.findByNameOrderByHeightAsc(name);

@RequestMapping(value = "findByNameLike",method = RequestMethod.GET)

@ResponseBody

public List<User> findByNameLike(String name)\{

return userRepository.findByNameLike("%"+name+"%");

@RequestMapping(value = "findByNameNotLike",method = RequestMethod.GET)

@ResponseBody

public List<User> findByNameNotLike(String name)\{

return userRepository.findByNameNotLike("%"+name+"%");

@RequestMapping(value = "findByNameNot",method = RequestMethod.GET)

@ResponseBody

public List<User> findByNameNot(String name)\{

return userRepository.findByNameNot(name);

@RequestMapping(value = "findByIdIn",method = RequestMethod.GET)

@ResponseBody

public List<User> findByIdIn(String ids)\{

List<String>list=new ArrayList<>();

String\[\] idsStr=ids.split(",");

for(String id:idsStr)\{

list.add(id);

return userRepository.findByIdIn(list);

@RequestMapping(value = "findByIdNotIn",method = RequestMethod.GET)

@ResponseBody

public List<User> findByIdNotIn(String ids)\{

List<String>list=new ArrayList<>();

String\[\] idsStr=ids.split(",");

for(String id:idsStr)\{

list.add(id);

return userRepository.findByIdNotIn(list);

**到这里关于jpa中findBy基本的语法就介绍完毕了，下一节将介绍jpa中关联查询的用法。**

**项目GitHub地址：https://github.com/1913045515/JPA**

[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1]: /images/20220601/91329b8b182a45fd8ec085e83e6c925d.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 1]: /images/20220601/456959b856f0481aba12742cb1c9e116.png