jpa mysql trigger_JPA访问数据库的几种方式

JPA访问数据库的几种方式

本文为原创，转载请注明出处：https://www.cnblogs.com/supiaopiao/p/10901793.html

1. Repository

1.1. 通过方法名称直接生成查询

Keyword

Sample

JPQL snippet

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 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(?1)

In和NotIn可以使用任何Collection的子类作为参数。

案例：

public interfaceUserRepository extendsJpaRepository, JpaSpecificationExecutor {

Page findByName(String name);

List findByNameAndPhone(String name, String phone);

User findUserById(Integer id);

voiddeleteByIdIn(List idList);

}

1.2. @Query注解

可以使用Spring Data JPA 的@Query注解将查询绑定到repository的函数上。

备注：注解到查询方法上的@Query执行顺序优先于下文中的@NamedQuery。

1.2.1. HQL

注意：使用“:属性名”的时候，最好在接口上加上@Param注解，否则会报类似如下异常：

public interfaceUserRepository extendsJpaRepository{

@Query(value = "SELECT u FROM User u WHERE u.namelike %?1%")//不加nativeQuery应使用HQLList findTable2(String name);

@Query(value = "SELECT u FROM User u WHERE u.namelike %:name% and u.sex= :sex")

List findTable3(@Param("name") String name, @Param("sex")String sex);

}

1.2.2. 原生SQL

(1)@Query注解通过设置nativeQuery标识支持执行原生SQL查询语句

public interfaceUserRepository extendsJpaRepository{

@Query(value = "SELECT*FROM user WHERE name like %?1%", nativeQuery = true)

List findTable1(String name);

}

(2)Spring Data JPA通过原生SQL进行查询时，不能满足Page条件进行分页，所以可以通过countQuery标识执行count查询实现Page分页：

public interfaceUserRepository extendsJpaRepository{

@Query(value = "SELECT*FROM user WHERE name like %?1%",

countQuery = "SELECTcount(*) FROM user WHERE name like %?1%",

nativeQuery = true)

Page findTable3(String name, Pageable pageable);

}

1.3. @Modifying注解

在@Query注解中编写JPQL实现DELETE和UPDATE操作的时候必须加上@Modifying注解，以通知Spring Data这是一个DELETE或UPDATE操作。

UPDATE或者DELETE操作需要使用事务，此时需要定义Service层，在Service层的方法上添加事务操作@Transactional。

Modifying查询语句中能用于void/int/Integer 返回类型，不能用于其他类型

注意JPQL不支持INSERT操作。

代码示例：

public interfaceUserRepository extendsJpaRepository{

@Modifying

@Query("UPDATE User u SET u.name= :name WHERE u.id= :id")

voidupdateTable(@Param("name") String name, @Param("id")Integer id);

@Modifying

@Query("delete from User u WHERE u.idin :idList")

voiddeleteByIds(@Param("idList")List idList);

}

2. 实体类中定义，在Repository中使用

2.1. 命名查询@NamedQuery

使用@NamedQuery为实体创建查询适用于定义少量查询。@NamedQuery需要声明在实体类上，@NamedQuery可以实现命名HQL查询(或JPQL)。

备注：注解到查询方法上的@Query(上文中)执行顺序优先于@NamedQuery。

@Entity

@NamedQuery(name = "Two.selectBySex",query = "select t from Two t where t.sex = ?1 group by id")

public classTwo {

@Id

@GeneratedValue(strategy=GenerationType.IDENTITY)

@Column(name="id",nullable=false)

privateInteger id;

@Column(name="name",nullable=false)

privateString name;

@Column(name="sex",nullable=false)

privateString sex;

Set、get方法省略

}

Repository层代码展示：

public interfaceTwoRepository extendsJpaRepository{

//实体类上通过@NamedQuery注解实现查询List selectBySex(String sex);

}

Spring Data处理这些方法对命名查询的调用，以实体类名称开始，后接方法名，以点作连接符。所以NameQuery定义在实体上，而不是定义在方法上。

2.2. 一个实体类中有多个命名查询@NamedQueries

上面我们演示了命名查询@NamedQuery的写法，当然JPA也支持多个@NamedQuery，那就是@NamedQueries

@Entity

@NamedQueries(value = {

@NamedQuery(

name = "Two.selectBySex",

query = "select t from Two t where t.sex = ?1 group by id"),

@NamedQuery(

name = "Two.findUserByPrimaryKey",

query = "select t from Two t where t.id = :id")

})

public classTwo {

@Id

@GeneratedValue(strategy=GenerationType.IDENTITY)

@Column(name="id",nullable=false)

privateInteger id;

@Column(name="name",nullable=false)

privateString name;

@Column(name="sex",nullable=false)

privateString sex;

Set、get方法省略

}

Repository层代码展示：

public interfaceTwoRepository extendsJpaRepository{

//实体类上通过@NamedQuery注解实现查询List selectBySex(String sex);

Two findUserByPrimaryKey(@Param("id") Integer id);

}

Spring Data处理这些方法对命名查询的调用，以实体类名称开始，后接方法名，以点作连接符。所以NameQuery定义在实体上，而不是定义在方法上。

2.3. 命名原生sql查询@NamedNativeQuery

@NamedNativeQuery可以实现命名原生sql查询

@Entity

@NamedNativeQuery(name = "Three.selectBySex", query = "select*from three where sex = ?1 group by id", resultClass = Three.class)

@Table(name="three")

public classThree {

@Id

@GeneratedValue(strategy=GenerationType.IDENTITY)

@Column(name="id",nullable=false)

privateInteger id;

@Column(name="name",nullable=false)

privateString name;

@Column(name="sex",nullable=false)

privateString sex;

Set、get方法省略

}

Repository层代码展示：

public interfaceThreeRepository extendsJpaRepository{

//实体类上通过@NamedNativeQuery注解实现查询List selectBySex(String sex);

}

2.4. 一个实体类中有多个命名查询@NamedNativeQueries

上面我们演示了命名查询@NamedNativeQuery的写法，当然JPA也支持多个@NamedNativeQuery，那就是@NamedNativeQueries

@Entity

@NamedNativeQueries(value = {

@NamedNativeQuery(

name = "Three.selectBySex",

query = "select*from three where sex = ?1 group by id",

resultClass = Three.class),

@NamedNativeQuery(

name = "Three.findUserByPrimaryKey",

query = "select*from three where id = :id",

resultClass = Three.class)//resutlClass用来指定实体类，resutlSetMapping用来指定映射的名称

})

@Table(name="three")

public classThree {

@Id

@GeneratedValue(strategy=GenerationType.IDENTITY)

@Column(name="id",nullable=false)

privateInteger id;

@Column(name="name",nullable=false)

privateString name;

@Column(name="sex",nullable=false)

privateString sex;

Set、get方法省略

}

Repository层代码展示：

public interfaceThreeRepository extendsJpaRepository{

//实体类上通过@NamedNativeQuery注解实现查询List selectBySex(String sex);

Three findUserByPrimaryKey(@Param("id") Integer id);

}

3. 外部ORM文件中定义，在Repository中使用

3.1. 命名查询named-query

使用XML配置，向位于resources/META-INF文件夹下的orm.xml配置文件添加必要的元素。

resources/META-INF下orm.xml配置：

SELECT o FROM One o WHERE o.sex = ?1 group by id

SELECT o FROM One o WHERE o.id = :id

Repository层代码展示：

public interfaceOneRepository extendsJpaRepository{

//1.1XML定义命名查询List selectByName(String name);

One findUserByPrimaryKey(@Param("id") Integer id);

}

3.2. 原生SQL查询named-native-query

使用XML配置，向位于resources/META-INF文件夹下的orm.xml配置文件添加必要的元素。

resources/META-INF下orm.xml配置：

SELECT * FROM one WHERE sex = ?1 group by id

SELECT * FROM one WHERE id = :id

Repository层代码展示：

packagecn.com.bmsoft.stormplan.basic.dao;

importcn.com.bmsoft.stormplan.basic.entity.One;

importorg.springframework.data.jpa.repository.JpaRepository;

importorg.springframework.data.jpa.repository.JpaSpecificationExecutor;

importorg.springframework.data.repository.query.Param;

importjava.util.List;

public interfaceOneRepository extendsJpaRepository{

//1.2XML定义 原生sql查询 named-native-queryList selectByName1(String sex);

One findUserByPrimaryKey1(@Param("id")Integer id);

}

4. SpEL表达式

从Spring Data JPA 1.4版本开始，支持在@Query定义的查询中使用SpEL模板表达式。在执行查询的时候，这些表达式通过一个事先定义好的变量集合求值。Spring Data JPA支持一个名为entityName的变量。它的用法是select x from #{#entityName} x。它会将域类型的entityName与给定repository关联起来。entityName按照如下方式处理：如果域类型在@Entity注解上设置了name属性，则使用该属性。否则直接使用域类型的类名。

下例演示了在定义带有查询方法和手工定义查询的repository接口时使用#{#entityName}表达式。

public interfaceUserRepository extendsJpaRepository{

@Query(value = "SELECT u FROM #{#entityName} u WHERE u.name like %:name% and u.sex = :sex")

List findTable4(@Param("name") String name, @Param("sex")String sex);

}

SpEL表达式的好处：

参考网址：https://www.cnblogs.com/tilv37/p/6944182.html

5. Specifications动态构建查询

5.1. 参数介绍

Predicate:单独每一条查询条件的详细描述

Predicate[]:多个查询条件的详细描述

Root：查询哪个表

CriteriaQuery：查询哪些字段，排序是什么

CriteriaBuilder：字段之间是什么关系，如何生成一个查询条件，每个查询条件都是什么方式

(1)CriteriaQuery:主要是构建查询条件

distinct、select、where、groupby、having、orderby等

(2)CriteriaBuilder:主要是用来进行一些函数操作

① and

② or

③ between

④ lt(小于)、le(小于等于)、gt(大于)、ge(大于等于)

⑤ not(非)等...

5.2. 代码案例

//where empname like ?  group by wages

@Override

publicPage search1(EmployeeVO employeeVO) throwsException {

Pageable pageable = PageRequest.of(employeeVO.getPage() - 1, employeeVO.getSize(), Sort.Direction.ASC,"empid");

Specification specification = newSpecification() {

@Override

publicPredicate toPredicate(Root root, CriteriaQuery> criteriaQuery, CriteriaBuilder cb) {

List list = newArrayList<>(10);

if(StringUtils.isNotBlank(employeeVO.getEmpname())){

list.add(cb.and(

cb.like(root.get("empname").as(String.class),"%"+ employeeVO.getEmpname()+"%")

));

}

Expression wages = root.get("wages").as(BigDecimal.class);

returncriteriaQuery.where(list.toArray(newPredicate[list.size()])).groupBy(wages).getRestriction();

//where empname like ?  group by wages}

};

Page stuPage = employeeRepository.findAll(specification, pageable);

returnstuPage;

}

//where empname like ? or deptid=?

@Override

publicPage search2(EmployeeVO employeeVO) throwsException {

Pageable pageable = PageRequest.of(employeeVO.getPage() - 1, employeeVO.getSize(), Sort.Direction.ASC,"empid");

Specification specification = newSpecification() {

@Override

publicPredicate toPredicate(Root root, CriteriaQuery> criteriaQuery, CriteriaBuilder cb) {

List list = newArrayList<>(10);

if(StringUtils.isNotBlank(employeeVO.getEmpname()) && null!= employeeVO.getDeptid()){

list.add(cb.or(

cb.like(root.get("empname").as(String.class),"%"+ employeeVO.getEmpname()+"%"),

cb.equal(root.get("deptid").as(Integer.class), employeeVO.getDeptid())

));

}//where empname like ? or deptid=?returncriteriaQuery.where(list.toArray(newPredicate[list.size()])).getRestriction();

}

};

Page stuPage = employeeRepository.findAll(specification, pageable);

returnstuPage;

}

//where wages < ?

@Override

publicPage search3(EmployeeVO employeeVO) throwsException {

Pageable pageable = PageRequest.of(employeeVO.getPage() - 1, employeeVO.getSize(), Sort.Direction.ASC,"empid");

Specification specification = newSpecification() {

@Override

publicPredicate toPredicate(Root root, CriteriaQuery> criteriaQuery, CriteriaBuilder cb) {

Predicate wages = cb.lt(root.get("wages").as(BigDecimal.class), employeeVO.getWages());

returncriteriaQuery.where(wages).getRestriction();

}

};//where wages < ?Page stuPage = employeeRepository.findAll(specification, pageable);

returnstuPage;

}

//where wages between ? and ?

@Override

publicPage search4(EmployeeVO employeeVO){

Pageable pageable = PageRequest.of(employeeVO.getPage() - 1, employeeVO.getSize(), Sort.Direction.ASC,"empid");

Specification specification = newSpecification() {

@Override

publicPredicate toPredicate(Root root, CriteriaQuery> criteriaQuery, CriteriaBuilder cb) {

Predicate wages = cb.between(root.get("wages").as(BigDecimal.class), employeeVO.getMinWages(), employeeVO.getMaxWages());

returncriteriaQuery.where(wages).getRestriction();

}

};//where wages between ? and ?Page stuPage = employeeRepository.findAll(specification, pageable);

returnstuPage;

}

6. JPA Join联表查询

构建表关系如下：

实体类代码展示：

@Entity

@Table(name="a")

public classAEntity {

@Id

@GeneratedValue(strategy= GenerationType.IDENTITY)

@Column(name="aid",nullable=false)

privateInteger aid;

@Column(name="aname",nullable=false)

privateString aname;

省略set、get方法

}

@Entity

@Table(name="b")

public classBEntity {

@Id

@GeneratedValue(strategy= GenerationType.IDENTITY)

@Column(name="bid",nullable=false)

privateInteger bid;

@Column(name="bname",nullable=false)

privateString bname;

@OneToOne(cascade=CascadeType.ALL) //B是关系的维护端，当删除 b，会级联删除 a@JoinColumn(name = "aid", referencedColumnName = "aid") //name：关联id，referencedColumnName：A表idprivateAEntity aEntity;//A表

省略set、get方法}

@Entity

@Table(name="c")

public classCEntity {

@Id

@GeneratedValue(strategy= GenerationType.IDENTITY)

@Column(name="cid",nullable=false)

privateInteger cid;

@Column(name="cname",nullable=false)

privateString cname;

省略set、get方法

}

@Entity

@Table(name="d")

public classDEntity {

@Id

@GeneratedValue(strategy= GenerationType.IDENTITY)

@Column(name="did",nullable=false)

privateInteger did;

@Column(name="dname",nullable=false)

privateString dname;

//可选属性optional=false,表示B不能为空。删除d，不影响b@ManyToOne(cascade={CascadeType.MERGE,CascadeType.REFRESH},optional=false)

@JoinColumn(name = "bid", referencedColumnName = "bid")//name：关联id，referencedColumnName：B表idprivateBEntity bEntity;//B表//可选属性optional=false,表示C不能为空。删除d，不影响c@ManyToOne(cascade={CascadeType.MERGE,CascadeType.REFRESH},optional=false)

@JoinColumn(name = "cid", referencedColumnName = "cid")//name：关联id，referencedColumnName：C表id@JsonBackReference

privateCEntity cEntity;//C表省略set、get方法

}

Service层代码展示：

@Override

publicPage search(DVO dVO) {

Pageable pageable = PageRequest.of(dVO.getPage() - 1, dVO.getSize(), Sort.Direction.ASC,"did");

Specification specification = newSpecification() {

@Override

publicPredicate toPredicate(Root root, CriteriaQuery> criteriaQuery, CriteriaBuilder cb) {

List list = newArrayList<>(10);

//A:B:D 1:1:n  根据D的实体类查询anameif(StringUtils.isNotBlank(dVO.getAname())) {

Join ajoin = root.join("bEntity",JoinType.LEFT);

list.add(cb.like(ajoin.get("aEntity").get("aname").as(String.class),"%"+ dVO.getAname()+"%"));

}//select * from d left join b on d.bid=b.bid left join a on b.aid=a.aid where a.aname like "%xxx%"//B:D 1:n  根据D的实体类查询bnameif(StringUtils.isNotBlank(dVO.getBname())) {

Join ajoin = root.join("bEntity",JoinType.LEFT);

list.add(cb.like(ajoin.get("bname").as(String.class),"%"+ dVO.getBname()+"%"));

}//select * from d left join b on d.bid=b.bid where b.bname like "%xxx%"//C:D 1:n  根据D的实体类查询cnameif(StringUtils.isNotBlank(dVO.getCname())) {

Join ajoin = root.join("cEntity",JoinType.LEFT);

list.add(cb.like(ajoin.get("cname").as(String.class),"%"+ dVO.getCname()+"%"));

}//select * from d left join c on d.cid=c.cid where c.cname like "%xxx%"returncriteriaQuery.where(list.toArray(newPredicate[list.size()])).getRestriction();

}

};

Page stuPage = dRepository.findAll(specification, pageable);

returnstuPage;

}