引入依赖
这里使用mysql数据库,所以要引用mysql数据库连接包
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
添加配置
在application.yml文件添加数据库连接配置及jpa配置
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/springboot_1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
username: root
password: 123456
jpa:
properties:
hibernate:
hbm2ddl:
auto: update
dialect: org.hibernate.dialect.MySQL5InnoDBDialect
format_sql: true
show-sql: true
实体类
a)组织
@Entity
@Table(name="t_organization")
public class Organization {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable=false)
private String name;
@Column(nullable=false)
private String code;
@OneToMany(mappedBy="organization",cascade=CascadeType.REMOVE,fetch=FetchType.EAGER)
private List<User> userList;
public Organization(){};
public Organization(String name,String code,List<User> userList){
this.name = name;
this.code = code;
this.userList = userList;
}
b)用户
@Entity
@Table(name="t_user")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false)
private String name;
@Column(nullable = false)
private String sex;
@ManyToOne(cascade={CascadeType.MERGE,CascadeType.PERSIST})
@JoinColumn(name="orgId")
@JsonIgnore
private Organization organization;
@ManyToMany(cascade={CascadeType.MERGE,CascadeType.PERSIST})
@JoinTable(name="t_user_role",joinColumns={@JoinColumn(name="userId")},
inverseJoinColumns={@JoinColumn(name="roleId")} )
private List<Role> roleList;
public User(){};
public User(String name,String sex,Organization organization){
this.name = name;
this.sex = sex;
this.organization = organization;
}
c)角色
@Entity
public class Role {
@Id
@GeneratedValue
private Long id;
@Column
private String name;
@ManyToMany(cascade={CascadeType.MERGE,CascadeType.PERSIST})
@JoinTable(name="t_user_role",joinColumns={@JoinColumn(name="roleId")},
inverseJoinColumns={@JoinColumn(name="userId")} )
@JsonIgnore
private List<User> userList;
@ManyToMany(cascade={CascadeType.MERGE,CascadeType.PERSIST})
@JoinTable(name="t_role_privilege",joinColumns={@JoinColumn(name="roleId")},
inverseJoinColumns={@JoinColumn(name="privilegeId")})
private List<Privilege> privilegeList;
public Role(){};
public Role(String name,List<User> userList, List<Privilege> privilegeList){
this.name = name;
this.userList = userList;
this.privilegeList = privilegeList;
}
d)权限
@Entity
public class Privilege {
@Id
@GeneratedValue
private Long id;
@Column
private String name;
@ManyToMany(cascade={CascadeType.MERGE,CascadeType.PERSIST})
@JoinTable(name="t_role_privilege",joinColumns={@JoinColumn(name="privilegeId")},
inverseJoinColumns={@JoinColumn(name="roleId")})
@JsonIgnore
private List<Role> roleList;
public Privilege(){};
public Privilege(String name,List<Role> roleList){
this.name = name;
this.roleList = roleList;
}
级联
a)cascade:级联操作权限
PERSIST 级联保存
REMOVE 级联删除
MERGE 级联更新
DETACH 级联游离,要想删除某一条学生信息,但学生的id在成绩表中作为外键,无法直接删除
REFRESH 级联刷新 与MERGE 好像没什么区别
ALL 上面所有的结合,慎用
b)fetch:设置关联对象的加载方式
EAGER 立即加载
LAZY 延迟加载,需要用到的时候再加载
关联对象
a)OneToMany
一对多,一对多一般不维护关系表字段,所以在这端添加 mappedBy=“mappedBy值” , mappedBy值是多端对象指向一端的属性值,mappedBy与JoinColumn、JoinTable互斥。因为JoinColumn、JoinTable表示这一端维护这外键或中间表
b)ManyToOne
多对一,一般在这端添加外键,外键对应的字段 JoinColumn(name="外键") ,外键由这端进行维护
c)ManyToMany
多对多,一般添加一个中间表, @JoinTable(name="t_user_role",joinColumns={@JoinColumn(name="自己的ID字段")},inverseJoinColumns={@JoinColumn(name="另一端的ID字段")} )
接口编写
编写一个用户接口
public interface UserRepository extends JpaRepository<User, Long>{
List<User> findByName(String name);
List<User> findByNameLike(String name);
在要使用用户接口的地主直接引用即可
@Resource
private UserRepository userRepository;
命名查询
下表描述了JPA支持的关键字以及包含该关键字的JPA命名查询方法:
关键字 | 示例 | SQL |
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<Age> ages) | … where x.age in ?1 |
NotIn | findByAgeNotIn(Collection<Age> ages) | … 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(? |
Query查询
a)原生sql查询,nativeQuery = true
@Query(value="select * from t_user u where u.name =?1",nativeQuery = true)
List<User> queryBySql(String name);
b)hql查询
@Query(value="select u from User u where u.name =?1",nativeQuery = false)
List<User> queryByHql(String name);
c)使用命名参数传参
@Query("select u from User u where u.firstname = :firstname or u.lastname = :lastname")
User findByLastnameOrFirstname(@Param("lastname")String lastname,@Param("firstname")String firstname);
d)返回的不是实体对象
如果返回的不是实体对象,则可以先建一个dto返回类对象UserVo,把查询的结果映射到实体类上
@Query(value="select new com.ouou.vo.UserVo(u.id,u.name,u.sex) from User u where u.name =?1",nativeQuery = false)
List<UserVo> queryByHqlUserVo(String name);
或返回一个数组对象,然后在代码里进行组装提取
@Query(value="select u.id,u.name,u.sex from User u where u.name =?1",nativeQuery = false)
List<Object[]> queryByHqlObject(String name);
分页查询
编写分页接口
Page<User> findByNameLike(String name,Pageable pageable);
@Query(value="select new com.ouou.vo.UserVo(u.id,u.name,u.sex) from User u where u.name like ?1",nativeQuery = false)
Page<UserVo> queryByHqlUserVoPage(String name,Pageable pageable);
a)通过参数生成Pageable对象
Pageable
定义了很多方法,但其核心的信息只有两个:一是分页的信息(page、size),二是排序的信息。Spring Data Jpa提供了PageRequest
的具体实现,Spring MVC提供了对Spring Data JPA非常好的支持,我们只提供分页以及排序信息即可:
@RequestMapping(value = "/params", method=RequestMethod.GET)
public Page<User> getEntryByParams(Integer page,Integer size) {
Sort sort = new Sort(Direction.DESC, "id");
Pageable pageable = new PageRequest(page, size, sort);
return userRepository.findByNameLike("姓名",pageable);
}
在这里,我们通过参数获得分页的信息,并通过Sort
以及Direction
告诉pageable需要通过id逆序排列
b)直接获取Pageable对象
@RequestMapping(value = "/params", method=RequestMethod.GET)
public Page<User> getEntryByPageable(@PageableDefault(value = 15, sort = { "id" }, direction = Sort.Direction.DESC)
Pageable pageable) {
returnuserRepository.findByNameLike("姓名",pageable);
}
我们可以看到,我们只需要在方法的参数中直接定义一个pageable类型的参数,当Spring发现这个参数时,Spring会自动的根据request的参数来组装该pageable对象,Spring支持的request参数如下:
- page,第几页,从0开始,默认为第0页
- size,每一页的大小,默认为20
- sort,排序相关的信息,以
property,property(,ASC|DESC)
- 的方式组织,例如
sort=firstname&sort=lastname,desc
- 表示在按firstname正序排列基础上按lastname倒序排列
这样,我们就可以通过url的参数来进行多样化、个性化的查询,而不需要为每一种情况来写不同的方法了。
通过url来定制pageable很方便,但唯一的缺点是不太美观,因此我们需要为pageable设置一个默认配置,这样很多情况下我们都能够通过一个简洁的url来获取信息了。
Spring提供了@PageableDefault
帮助我们个性化的设置pageable的默认配置。例如@PageableDefault(value = 15, sort = { "id" }, direction = Sort.Direction.DESC)
表示默认情况下我们按照id倒序排列,每一页的大小为15
事务
在service 或 controller类上或方法上添加注解 @Transactional即可。注意:如果service类和controller类上都有@Transactional,则controller类上的事务注解生效,service上的事务注解不生效