Spring Boot学习日志（三）再谈Jpa

• Spring Boot学习日志（一）Hello World
• Spring Boot学习日志（二）JPA基本操作
• Spring Boot学习日志（三）再谈Jpa
• Spring Boot学习日志（四）使用Mybatis
• Spring Boot学习日志（五）结合Swagger管理接口

背景

在上一篇JPA基本操作中讲述了SpringBoot Jpa基本的操作，但实际场景中会有很多复杂的场景。下面讲述条件查询，自定义查询以及多表查询。

导入依赖库和数据库配置

与上一篇JPA基本操作设置一致，可直接复制过来。

创建测试用到的表

CREATE TABLE `tb_group` (
  `groupid` int(11) NOT NULL DEFAULT '0',
  `groupname` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (`groupid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL,
  `username` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `group` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

创建实体类

@Entity
@Table(name = "tb_user")
@SuppressWarnings("serial")
public class UserEntity implements Serializable {
    @Id
    @Column(name = "id")
    private Integer id;

    @Column(name = "username")
    private String username;

    //映射多对一的关联关系
    @JoinColumn(name="group")//关联user表的字段
    @ManyToOne
    private Group group;


    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Group getGroup() {
        return group;
    }

    public void setGroup(Group group) {
        this.group = group;
    }
}
@Entity
@Table(name = "tb_group")
public class Group implements Serializable {
    @Id
    @Column(name = "id")
    private String id;

    @Column(name = "groupname")
    private String groupname;

    @Column(name = "group")
    private String group;

    public String getGroup() {
        return group;
    }

    public void setGroup(String group) {
        this.group = group;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getGroupname() {
        return groupname;
    }

    public void setGroupname(String groupname) {
        this.groupname = groupname;
    }

}

UserEntity 为用户实体
Group为用户组所属的分组


此上可以看出用户和用户组是多对一的关系，比如一个用户属于一个用户组，一个用户组有多个用户。

Jpa自定义简单查询

除了Jpa自动帮我们生成的sql之外，jpa还可以根据方法名自动生成sql,比如findXXX,readXXX,queryXXX,countXXX, getXXX，后面加上属性名称。
以查询为例：

   //根据用户名查找用户
    List<UserEntity> findUserEntityByUsername(String username);

    //根据用户id查找用户
    List<UserEntity> findUserEntityById(Integer id);

这些都是单一条件的查询，也可以复杂条件的查询，比如And、Or，如果希望使用And或者Or关键字，只需要在后面加上Or加属性名
比如：

    //根据用户名和用户id查找用户
    List<UserEntity> findUserEntityByIdAndUsername(Integer id,String username);

    //根据用户名或用户id查找用户
    List<UserEntity> findUserEntityByIdOrUsername(Integer id,String username);

常用的Like、OrderBy都可以使用：

    //排序查询
    List<UserEntity> findUserEntityByUsernameIsNotNullOrderById();

自定义sql查询:

   @Query(value = "select * from tb_user u left join tb_group g on g.group = u.group where u.username=?1", nativeQuery = true)
    @Modifying
    List<UserEntity> findUser(@Param("name")String name);

对应规则

关键字	例子	对应sql片段
find,query	findByName	select … where x.name = ?1
delete	deleteByName	delete … where x.name = ?1
count	countByName	select count(x.id) … where x.name=?1
And	findByLastnameAndFirstname	… where x.lastname = ?1 and x.firstname = ?2
Or	findByLastnameOrFirstname	… where x.lastname = ?1 or x.firstname = ?2
Is,Equals	findByFirstname,findByFirstnameIs,findByFirstnameEquals	… where x.firstname = ?1
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	findByAgeIsNull	… where x.age is null
IsNotNull,NotNull	findByAge(Is)NotNull	… where x.age not null
Like	findByFirstnameLike	… where x.firstname like ?1
NotLike	findByFirstnameNotLike	… where x.firstname not like ?1
StartingWith	findByFirstnameStartingWith	… where x.firstname like ?1 (parameter bound with appended %)
EndingWith	findByFirstnameEndingWith	… where x.firstname like ?1 (parameter bound with prepended %)
Containing	findByFirstnameContaining	… where x.firstname like ?1 (parameter bound wrapped in %)
OrderBy	findByAgeOrderByLastnameDesc	… where x.age = ?1 order by x.lastname desc
Not	findByLastnameNot	… where x.lastname <> ?1
In	findByAgeIn(Collection ages)	… where x.age in ?1
NotIn	findByAgeNotIn(Collection age)	… where x.age not in ?1
True	findByActiveTrue()	… where x.active = true
False	findByActiveFalse()	… where x.active = false
IgnoreCase	findByFirstnameIgnoreCase	… where UPPER(x.firstame) = UPPER(?1)

Controller示例

@RequestMapping(value = "/user")
@RestController
public class UserController {
    @Autowired
    private UserJpa jpa;
    @RequestMapping(value = "/findUser")
    public List<UserEntity> findUser(String username){
        return jpa.findUser(username);
    }

    @RequestMapping(value = "/order")
    public List<UserEntity> findUserEntityByUsernameIsNotNullOrderById(){
        return jpa.findUserEntityByUsernameIsNotNullOrderById();
    }

    @RequestMapping(value = "/name")
    public List<UserEntity> findUserEntityByUsername(String username){
        return jpa.findUserEntityByUsername(username);
    }

    @RequestMapping(value = "/id")
    public List<UserEntity> findUserEntityById(Integer id){
        return jpa.findUserEntityById(id);
    }

    @RequestMapping(value = "/and")
    public List<UserEntity> findUserEntityByIdAndUsername(UserEntity userEntity){
        return jpa.findUserEntityByIdAndUsername(userEntity.getId(),userEntity.getUsername());
    }

    @RequestMapping(value = "/or")
    public List<UserEntity> findUserEntityByIdOrUsername(UserEntity userEntity){
        return jpa.findUserEntityByIdOrUsername(userEntity.getId(),userEntity.getUsername());
    }
}

执行结果举例

总结

虽然可以通过spring boot jpa自动生成很多sql复杂语句，但是在使用中感觉还是很不好用，在很多时候需要夺表查询，写起来还是没有Mybatis那么“舒服”。下面一节就开始记录Mybatis相关内容。