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----
}
  1. 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方法名查询

支持的关键字,如下表:

KeywordSampleJPQL snippet
AndfindByLastnameAndFirstname… where x.lastname = ?1 and x.firstname = ?2
OrfindByLastnameOrFirstname… where x.lastname = ?1 or x.firstname = ?2
Is,EqualsfindByFirstname,findByFirstnameIs… where x.firstname = ?1
BetweenfindByStartDateBetween… where x.startDate between ?1 and ?2
LessThanfindByAgeLessThan… where x.age < ?1
LessThanEqualfindByAgeLessThanEqual… where x.age <= ?1
GreaterThanfindByAgeGreaterThan… where x.age > ?1
GreaterThanEqualfindByAgeGreaterThanEqual… where x.age >= ?1
AfterfindByStartDateAfter… where x.startDate > ?1
BeforefindByStartDateBefore… where x.startDate < ?1
IsNullfindByAgeIsNull… where x.age is null
IsNotNull,NotNull findByAge(Is)NotNull… where x.age not null
LikefindByFirstnameLike… where x.firstname like ?1
NotLikefindByFirstnameNotLike… where x.firstname not like ?1
StartingWithfindByFirstnameStartingWith… where x.firstname like ?1(parameter bound with appended %)
EndingWithfindByFirstnameEndingWith… where x.firstname like ?1(parameter bound with prepended %)
ContainingfindByFirstnameContaining… where x.firstname like ?1(parameter bound wrapped in %)
OrderByfindByAgeOrderByLastnameDesc… where x.age = ?1 order by x.lastname desc
NotfindByLastnameNot… where x.lastname <> ?1
InfindByAgeIn(Collection ages)… where x.age in ?1
NotInfindByAgeNotIn(Collection ages)… where x.age not in ?1
TruefindByActiveTrue()… where x.active = true
FalsefindByActiveFalse()… where x.active = false
IgnoreCasefindByFirstnameIgnoreCase… where UPPER(x.firstame) = UPPER(?1)

// spring data jpa中使用count计数方法很简单
//直接在dao层写方法即可
int countByUidAndTenementId(String parentUid, String tenementId);
  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值