Spring Data JPA查询实例

JPA查询

public interface ConsumerCategoryRepository
		extends JpaRepository<ConsumerCategory, Long>,
		JpaSpecificationExecutor<ConsumerCategory>, Serializable {

	@Query(value = "select * from consumer_category where status = 1", nativeQuery = true)
	public List<ConsumerCategory> getAll();

	@Query(value = "select * from consumer_category where if(?1!=-1,id =?1,1=1) and if(?2!='',name =?2,1=1)", nativeQuery = true)
	public ConsumerCategory getByIdAndName(long id, String name);

        @Query("from StudentEO where :name is null or name=:name")
        public Student getByName(String name);

	@Modifying
	@Query("update consumer_category set status=:status where id=:id")
	public int modifyStatus(@Param("id") long id, @Param("status") int status);
}

JPA分页实例

@RequestMapping("/category/getListPageable")
public Result getListPageable(HttpServletRequest request) {
        String pageNum = request.getParameter("pageNum");
        String pageSize = request.getParameter("pageSize");

        String parent = request.getParameter("parent");
        String statusStr = request.getParameter("status");
        String typeStr = request.getParameter("type");
        int num = 1;
        int size = 10;
        if (pageNum != null) {
            num = Integer.valueOf(pageNum);
        }
        if (pageSize != null) {
            size = Integer.valueOf(pageSize);
        }
        ///Sort.Direction是个枚举有ASC(升序)和DESC(降序)
        Sort sort = new Sort(Sort.Direction.ASC, "id");
        ///PageRequest继承于AbstractPageRequest并且实现了Pageable
        ///获取PageRequest对象 index:页码 从0开始  size每页容量 sort排序方式 "id"->properties 以谁为准排序
        Pageable pageable = new PageRequest(num - 1, size, sort);
        Page<CategoryDO> pages = categoryService.queryPageable(pageable, parent, statusStr, typeStr);
        PageImpl<CategoryDO> impl = new PageImpl<>(pages.getContent(), pageable, pages.getTotalElements());
        return Result.ok().put(impl);
    }

 

@Override
public Page<CategoryDO> queryPageable(Pageable pageable, String parentId, String status, String type) {
        CategoryDO category = new CategoryDO();
        if (parentId != null) {
            category.setParent(Long.valueOf(parentId));
        }
        if (status != null) {
            category.setStatus(Integer.valueOf(status));
        }
        if (type != null) {
            category.setType(Integer.valueOf(type));
        }
        //将匹配对象封装成Example对象
        Example<CategoryDO> example = Example.of(category);
        return consumerCategoryRepository.findAll(example, pageable);
    }

 使用ExampleMatcher自定义模糊查询以及忽略查询条件

@Test
public void contextLoads() {
    User user = new User();
    user.setUsername("y");
    user.setAddress("sh");
    user.setPassword("admin");
    ExampleMatcher matcher = ExampleMatcher.matching()
            .withMatcher("username", ExampleMatcher.GenericPropertyMatchers.startsWith())//模糊查询匹配开头，即{username}%
            .withMatcher("address" ,ExampleMatcher.GenericPropertyMatchers.contains())//全部模糊查询，即%{address}%
            .withIgnorePaths("password");//忽略字段，即不管password是什么值都不加入查询条件
    Example<User> example = Example.of(user ,matcher);
    List<User> list = userRepository.findAll(example);
}

 

SpringbootJPA分页 PageRequest过时替换方法

Pageable pageable = new PageRequest(page - 1,size);
替换成：
Pageable pageable = PageRequest.of(page - 1,size);

对于多条件的查询需要参数可有可无的情况下可以使用where if(?1!=-1,id =?1,1=1) and if(?2!='',name =?2,1=1)不作为查询条件的时候传入sql约定的值就可以了。

nativeQuery = true的作用，就是使用原生的sql语句（根据数据库的不同，在sql的语法或结构方面可能有所区别）进行查询数据库的操作。

JpaRepository.java

@NoRepositoryBean
public interface JpaRepository<T, ID extends Serializable>
		extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {

	/*
	 * (non-Javadoc)
	 * @see org.springframework.data.repository.CrudRepository#findAll()
	 */
	List<T> findAll();

	/*
	 * (non-Javadoc)
	 * @see org.springframework.data.repository.PagingAndSortingRepository#findAll(org.springframework.data.domain.Sort)
	 */
	List<T> findAll(Sort sort);

	/*
	 * (non-Javadoc)
	 * @see org.springframework.data.repository.CrudRepository#findAll(java.lang.Iterable)
	 */
	List<T> findAll(Iterable<ID> ids);

	/*
	 * (non-Javadoc)
	 * @see org.springframework.data.repository.CrudRepository#save(java.lang.Iterable)
	 */
	<S extends T> List<S> save(Iterable<S> entities);

	/**
	 * Flushes all pending changes to the database.
	 */
	void flush();

	/**
	 * Saves an entity and flushes changes instantly.
	 * 
	 * @param entity
	 * @return the saved entity
	 */
	<S extends T> S saveAndFlush(S entity);

	/**
	 * Deletes the given entities in a batch which means it will create a single {@link Query}. Assume that we will clear
	 * the {@link javax.persistence.EntityManager} after the call.
	 * 
	 * @param entities
	 */
	void deleteInBatch(Iterable<T> entities);

	/**
	 * Deletes all entities in a batch call.
	 */
	void deleteAllInBatch();

	/**
	 * Returns a reference to the entity with the given identifier.
	 * 
	 * @param id must not be {@literal null}.
	 * @return a reference to the entity with the given identifier.
	 * @see EntityManager#getReference(Class, Object)
	 */
	T getOne(ID id);

	/* (non-Javadoc)
	 * @see org.springframework.data.repository.query.QueryByExampleExecutor#findAll(org.springframework.data.domain.Example)
	 */
	@Override
	<S extends T> List<S> findAll(Example<S> example);

	/* (non-Javadoc)
	 * @see org.springframework.data.repository.query.QueryByExampleExecutor#findAll(org.springframework.data.domain.Example, org.springframework.data.domain.Sort)
	 */
	@Override
	<S extends T> List<S> findAll(Example<S> example, Sort sort);

}

简单的增删改查可以直接调用：

public class PersonRepositoryImpl implements PersonService {

	@Autowired
	PersonRepository personRepository;

	@Override
	public void save(Person person) {
		this.personRepository.save(person);
	}

	@Override
	public void delete(long id) {
		this.personRepository.delete(id);
	}

	@Override
	public List<Person> find() {
		return (List<Person>) this.personRepository.findAll();
	}
}