jpa是一套规范,有多种实现,用的比较多的貌似是Hibernate吧。完整的Hibernate很难驾驭,jpa 用起来还是很简单的。
根据Example查询
JPA 的 Example 太鸡肋!! 不推荐使用
例子一
1. 创建实例
User user = new User(); user.setUsername("建");
2. 创建匹配器(字段:username,匹配方式:包含)
ExampleMatcher exampleMatcher.withMatcher("username", match -> match.contains());
3. 组装
//Example<User> example = Example.of(user); //sql: username = ?
Example<User> example = Example.of(user,exampleMatcher);
4. Dao查询
System.out.println(userMapper.findAll(example));
例子二
User user = new User();
user.setUsername("y");
user.setAddress("sh");
user.setPassword("admin");
ExampleMatcher matcher = ExampleMatcher.matching()
.withMatcher("username", match -> match.startsWith())//模糊查询匹配开头,即{username}%
.withMatcher("address" ,match -> match.contains())//全部模糊查询,即%{address}%
.withIgnorePaths("password");//忽略字段,即不管password是什么值都不加入查询条件
Example<User> example = Example.of(user ,matcher);
List<User> list = userRepository.findAll(example);
System.out.println(list);
jpa利用pageable分页排序
@RequestMapping(value = "/testPageable", method = RequestMethod.GET)
public Page<User> testPageable(
@RequestParam("page") Integer page,
@RequestParam("size") Integer size,
@RequestParam("sortType") String sortType,
@RequestParam("sortableFields") String sortableFields
) {
//判断排序类型及排序字段
Sort sort = "ASC".equals(sortType) ? new Sort(Sort.Direction.ASC, sortableFields) : new Sort(Sort.Direction.DESC, sortableFields);
//获取pageable
Pageable pageable = PageRequest.of(page-1,size,sort);
return userRepository.findAll(pageable);
}
jpa Sort排序
/** 单条件排序 **/
public List<User> findListSortSingleCondition(){
//id升序查询
Sort sort = new Sort(Sort.Direction.ASC,"id");
return userDao.findUserLikeBySort(sort);
}
/** 多条件排序 **/
public List<User> findListSortMultiCondition(){
List<Order> orders=new ArrayList<Order>();
Order orderId = new Sort.Order(Sort.Direction.DESC,"id");
Order orderAge = new Sort.Order(Sort.Direction.DESC,"age");
orders.add(orderId);
orders.add(orderAge);
Sort sort = new Sort(orders);
return userDao.findUserLikeBySort(sort);
}
//按线路降序和里程升序排序
Sort sort = new Sort(Sort.Direction.DESC, "section.railway.name").and(new Sort(Sort.Direction.ASC, "rangeMileage"));
//按线路降序和里程升序排序
List<Order> orders = new ArrayList<Order>();
Order nameOrder = new Order(Direction.DESC, "section.railway.name");
Order rangeOrder = new Order(Direction.ASC, "rangeMileage");
orders.add(nameOrder);//先按线路降序
orders.add(rangeOrder);//再按里程升序
Sort sort2 = new Sort(orders);
//按线路升序和里程升序排序
Sort sort = new Sort(Direction.fromString("asc"), "section.railway.name", "rangeMileage");
@Query注解
Dao方法名称查询是最简单的查询方式。但是,很多时候,很难达到预期的查询效果。这时候,可以使用@Query进行查询。
@Query 是在DAO接口的方法上添加自定义查询语句的方式
@Query 注解,标注在方法上,优先于 @NameQuery,也优先于在xml中定义的。
使用@Query注解有两种方式,一种是JPQL的SQL语言方式,一种是原生SQL的语言。
使用@Query查询时,传参也有两种写法:命名参数
(查询语句中:变量名
,同时在方法的参数前面使用@Param("变量名")
) 和 位置编号
(查询语句中?参数顺序数字
,对应方法的参数中的位置,数字从1开始)。
@Query还可以执行一个更新或删除操作,为此,我们需要在使用@Query的同时,用@Modifying来将该操作标识为修改查询,这样框架最终会生成一个更新的操作,而非查询操作。
- User.java 实体类
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
javax.persistence.Entity
@Entity
@Table(name="sys_user")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="id" ,insertable=false,updatable=false)
private Integer id;
/** @Column中的name默认和字段名相同,可以不写注解 **/
@Column(name = "name")
private String name;
private String firstname;
private String lastname;
private Integer age;
private String phone;
private String password;
//---省略getter、setter----
}
- UserDao.java
@Repository
public interface UserDao extends JpaRepository<User, Integer> {
/** ?加数字表示占位符,?1代表在方法参数里的第一个参数,区别于其他的index,这里从1开始 **/
// select * from User where name = ?1 注意不能写成*
@Query(value = "select u from User u where u.name = ?1")
User findUserByName1(String name);
/**
* 使用参数名称
* =:加上变量名,这里是与方法参数中有@Param的值匹配的
*/
@Query(value = "select u from User u where u.name = :name")
User findUserByName2(@Param("name") String username);
@Query("select u from User u where u.firstname = :firstname or u.lastname = :lastname")
User findByLastnameOrFirstname(@Param("lastname") String lastname, @Param("firstname") String firstname);
/** 通过原生sql 进行查询,开启nativeQuery=true,在value里可以用原生SQL语句完成查询 **/
@Query(nativeQuery = true, value = "select * from user u where u.name = ?1")
User findUserByNativeSQL(String username);
@Query(nativeQuery = true, value = "select * from user u where u.name = :name")
User findUserByNativeSQL(@Param("name") String username);
/** 模糊查询 **/
@Query(value = "select u from User u where u.name like %?1% ")
List<User> findUserLike(String name);
/** JPQL分页查询(Pageable:spring-data-jpa自带接口) **/
@Query(value = "select u from User u where u.name like %?1% ")
Page<User> findUserLikeByPage(String name, Pageable page);
/** 原生SQL分页查询 **/
@Query(value = "select * from user where age = :age ORDER BY ?#{#pageable}", nativeQuery = true)
Page<User> search(@Param("age") Integer age, Pageable pageable);
/** 原生SQL分页查询,并统计总记录数 **/
@Query(value = "select * from user where age = :aeg ORDER BY ?#{#pageable}",
countQuery="select count(*) from user where aeg = :age", nativeQuery = true)
Page<User> search1(@Param("age") Integer age, Pageable pageable);
/** @Query查询不支持传入排序对象Sort **/
@Query(value = "select u from User u")
List<User> findUserLikeBySort(Sort sort);
// 使用自定义查询,不能直接转换DTO,JPQL需要new一个对象
@Query("select new com.xxx.domain.UserDTO(u.id, u.name, d.id, d.name) from User u, Department d where u.deptId = d.id and u.id = ?1")
UserDTO findByIdWithDepartment(Integer id);
// 查询结果, 没有对应的pojo, 可以用 List<Object[]> 接收
@Query("select u.id, LENGTH(u.firstname) as fn_len from User u where u.lastname like ?1%")
List<Object[]> findByAsArrayAndSort(String lastname, Sort sort);
// 查询结果, 返回MAP
@Query("select new map (u.name as name,u.email as email) from User")
List<Map<String,Object>> findUser();
// @Query 更新或删除 需要再加上 @Modifying
@Modifying
@Query("update User u set u.firstname = ?1 where u.lastname = ?2")
int setFixedFirstnameFor(String firstname, String lastname);
// 从 Spring Data JPA 1.4 版开始,@Query 还支持 SpEL 模板表达式。
// 它的用法是select x from #{#entityName} x。它插入 entityName 与给定存储库关联的域类型。
// 该entityName:如果域类型已设置的 name 属性 @Entity 的注释,它被使用。否则,使用域类型的简单类名。
@Query("select u from #{#entityName} u where u.lastname = ?1")
List<User> findByLastname(String lastname);
}
Dao方法名查询
支持的关键字,如下表:
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 | … 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) |
// spring data jpa中使用count计数方法很简单
//直接在dao层写方法即可
int countByUidAndTenementId(String parentUid, String tenementId);