spring-data-jpa常用代码片段示例

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来将该操作标识为修改查询，这样框架最终会生成一个更新的操作，而非查询操作。

1. 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----
}

2. 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);