Home | 简体中文 | 繁体中文 | 杂文 | 打赏(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎专栏 | Search | Email

10.3. Repository

Spring Data JPA 为此提供了一些表达条件查询的关键字:

		
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	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)		

常用如下:
And --- 等价于 SQL 中的 and 关键字,比如 findByUsernameAndPassword(String user, Striang pwd)
Or --- 等价于 SQL 中的 or 关键字,比如 findByUsernameOrAddress(String user, String addr)
Between --- 等价于 SQL 中的 between 关键字,比如 findBySalaryBetween(int max, int min)
LessThan --- 等价于 SQL 中的 "<",比如 findBySalaryLessThan(int max)
GreaterThan --- 等价于 SQL 中的">",比如 findBySalaryGreaterThan(int min)
IsNull --- 等价于 SQL 中的 "is null",比如 findByUsernameIsNull()
IsNotNull --- 等价于 SQL 中的 "is not null",比如 findByUsernameIsNotNull()
NotNull --- 与 IsNotNull 等价
Like --- 等价于 SQL 中的 "like",比如 findByUsernameLike(String user)
NotLike --- 等价于 SQL 中的 "not like",比如 findByUsernameNotLike(String user)
OrderBy ---等价于 SQL 中的 "order by",比如 findByUsernameOrderBySalaryAsc(String user)
Not --- 等价于 SQL 中的 "! =",比如 findByUsernameNot(String user)
In --- 等价于 SQL 中的 "in",比如 findByUsernameIn(Collection<String> userList) ,方法的参数可以是 Collection 类型,也可以是数组或者不定长参数
NotIn --- 等价于 SQL 中的 "not in",比如 findByUsernameNotIn(Collection<String> userList) ,方法的参数可以是 Collection 类型,也可以是数组或者不定长
		
		
		
	@Autowired
	private ArticleRepository articleRepository;

	@RequestMapping("/mysql")
	@ResponseBody
	public String mysql() {
		articleRepository.save(new Article("Neo", "Chen"));
		for (Article article : articleRepository.findAll()) {
			System.out.println(article);
		}
		Article tmp = articleRepository.findByTitle("Neo");
		return tmp.getTitle();
	}

	@RequestMapping("/search")
	@ResponseBody
	public String search() {

		
		for (Article article : articleRepository.findBySearch(1)) { System.out.println(article); }
		 
		List<Article> tmp = articleRepository.findBySearch(1L);

		tmp.forEach((temp) -> {
			System.out.println(temp.toString());
		});

		return tmp.get(0).getTitle();
	}
		
		

10.3.1. 传递枚举参数

			
package cn.netkiller.api.repository;

import org.springframework.data.repository.CrudRepository;

import cn.netkiller.api.domain.StatisticsHistory;

public interface StatisticsHistoryRepostitory extends CrudRepository<StatisticsHistory, Long> {

	public StatisticsHistory findByMemberIdAndStatisticsIdAndType(long member_id, long statistics_id,
			StatisticsHistory.StatisticsType type);

}
			
			

			
	@Autowired
	private StatisticsHistoryRepostitory statisticsHistoryRepostitory;			
			
	statisticsHistoryRepostitory.findByMemberIdAndStatisticsIdAndType(uid, id, type);
			
			

10.3.2. 翻页操作

			
package cn.netkiller.api.repository;

import java.util.List;

import org.springframework.data.domain.Pageable;
import org.springframework.data.repository.CrudRepository;

import cn.netkiller.api.domain.RecentRead;

public interface RecentReadRepostitory extends CrudRepository<RecentRead, Long> {

	List<RecentRead> findByMemberId(long id, Pageable pageable);

}
			
			

Top 10 实例

			
	@RequestMapping("/recent/read/list/{id}")
	public List<RecentRead> recentList(@PathVariable long id) {
		int page = 0;
		int limit = 10;
		List<RecentRead> recentRead = recentReadRepostitory.findByMemberId(id, new PageRequest(page, limit));
		return recentRead;
	}
			
			

10.3.3. 排序操作操作

			
List<UserModel> findByName(String name, Sort sort);
			
			

			
Sort sort = new Sort(Direction.DESC, "id"); 
repostitory.findByName("Neo", sort);
			
			

10.3.4. OrderBy

			
public List<StudentEntity> findAllByOrderByIdAsc();
public List<StudentEntity> findAllByOrderByIdDesc();
List<RecentRead> findByMemberIdOrderByIdDesc(int memberId, Pageable pageable);
			
			

10.3.5. Query

10.3.5.1. 参数传递

				
package api.repository.oracle;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import api.domain.oracle.Member;

@Repository
public interface MemberRepository extends CrudRepository<Member, Long> {
	public Page<Member> findAll(Pageable pageable);

	// public Member findByBillno(String billno);

	public Member findById(String id);

	@Query("SELECT m FROM Member m WHERE m.status = 'Y' AND m.id = :id")
	public Member findFinishById(@Param("id") String id);

}
				
				
				
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface PersonRepository extends JpaRepository<Person, Long> {
    @Query("SELECT p FROM Person p WHERE LOWER(p.lastName) = LOWER(:lastName)")
    public List<Person> find(@Param("lastName") String lastName);
}
				
				

10.3.6. @Transactional

10.3.6.1. 删除更新需要 @Transactional 注解

				
package cn.netkiller.api.repository;

import javax.transaction.Transactional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import cn.netkiller.api.domain.RecentRead;

@Repository
public interface RecentReadRepostitory extends CrudRepository<RecentRead, Integer> {

	Page<RecentRead> findByMemberIdOrderByIdDesc(int memberId, Pageable pageable);

	int countByMemberId(int memberId);
	
	@Transactional
	@Modifying
	@Query("DELETE FROM RecentRead r WHERE r.memberId = ?1 AND r.articleId = ?2")
	void deleteByMemberIdAndArticleId(int memberId, int articleId);
	
	@Transactional
	@Modifying
	@Query("delete from RecentRead where member_id = :member_id")
	public void deleteByMemberId(@Param("member_id") int memberId);

	int countByMemberIdAndArticleId(int memberId, int articleId);

}				
				
				

10.3.6.2. 回滚操作

				
	// 指定Exception回滚
	@Transactional(rollbackFor=Exception.class)
    public void methodName() {
       // 不会回滚
       throw new Exception("...");
    }

	//指定Exception回滚,但其他异常不回滚
	@Transactional(noRollbackFor=Exception.class)
    public ItimDaoImpl getItemDaoImpl() {
        // 会回滚
        throw new RuntimeException("注释");
    }