查询
调用接口方法查询
jpql查询
使用@Query注解,结合JPQL的语句方式完成查询
/**
* JpaRepository<实体类类型,主键类型>:用来完成基本CRUD操作
* JpaSpecificationExecutor<实体类类型>:用于复杂查询(分页等查询操作)
*/
public interface CustomerDao extends JpaRepository<Customer, Long>, JpaSpecificationExecutor<Customer> {
//@Query 使用jpql的方式查询。
@Query(value="from Customer")
public List<Customer> findAllCustomer();
//@Query 使用jpql的方式查询。?1代表参数的占位符,其中1对应方法中的参数索引
@Query(value="from Customer where custName = ?1")
public List<Customer> findCustomer(String custName);
// @Query结合@Modifying 来将该操作标识为修改查询
@Query(value="update Customer set custName = ?1 where custId = ?2")
@Modifying
public void updateCustomer(String custName,Long custId);
}
测试方法:
@Test
public void testfindAllCustomer(){
List<Customer> allCustomer = customerDao.findAllCustomer();
for (Customer c:
allCustomer) {
System.out.println(c);
}
}
@Test
public void testfindCustomer(){
List<Customer> allCustomer =customerDao.findCustomer("船只");
for (Customer c:
allCustomer) {
System.out.println(c);
}
}
@Test
@Transactional // 默认会回滚
@Rollback(false) // 不自动回滚
public void testQUpdata(){
customerDao.updateCustomer("aaaa", 8l);
}
sql查询【了解】
/**
* nativeQuery : 使用本地sql的方式查询
*/
@Query(value="select * from cst_customer",nativeQuery=true)
public List<Customer> findSql();
@Query(value="select * from cst_customer where cust_name like ?1",nativeQuery=true)
public List<Customer> findSqlByName(String Nama);
方法命名规则查询
- 是对jpql查询进行的更加深入的封装
- 只需要按照SpringDataJPA提供的方法名称规则定义方法,不需要再去配置jpql语句,完成查询
- Spring Data JPA在程序执行的时候会根据方法名称进行解析,并自动生成查询语句进行查询
/**
* 方法名的约定:
* findBy : 查询
* 对象中的属性名(首字母大写) : 查询的条件
* CustName
* * 默认情况 : 使用 等于的方式查询
* 特殊的查询方式
*
* findByCustName -- 根据客户名称查询
*
* 再springdataJpa的运行阶段
* 会根据方法名称进行解析 findBy from xxx(实体类)
* 属性名称 where custName =
*
* 1.findBy + 属性名称 (根据属性名称进行完成匹配的查询=)
* 2.findBy + 属性名称 + “查询方式(Like | isnull)”
* findByCustNameLike
* 3.多条件查询
* findBy + 属性名 + “查询方式” + “多条件的连接符(and|or)” + 属性名 + “查询方式”
*/
//方法命名方式查询(根据客户名称查询客户)
public List<Customer> findByCustName(String custName);
Keyword | Sample | JPQL |
---|---|---|
And | findByLastnameAndFirstname | … where x.lastname = ?1 and x.firstname = ?2 |
Or | findByLastnameOrFirstname | … where x.lastname = ?1 or x.firstname = ?2 |
Is,Equals | 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) |