- 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相关内容。