1.使用@Query 注释
public interface BookRepository extends JpaRepository<Book, Long>{
@Query(value = "SELECT * FROM book WHERE name = ?1 AND borrowedAt IS NULL AND createdAt > ?2", nativeQuery = ture)
List<Book> numsRes(String userName, Date rangeDate);
}
2.使用 JPAQuery
import com.mysema.query.types.Predicate
@PersistenceContext(unitName = "book")
private EntityManager entityManager;
QBook BOOK = QBook.book;
private static final Expression<?>[] BOOK_FIELD = {
BOOK.name,
BOOK.id
};
private Predicate makePredicate(String userName, Date createdAt){
BooleanExpression predicate = null;
List<BooleanExpression> ps = new ArrayList<>();
if(userName != null){
ps.add(BOOK.userName).eq(userName);
}
if(createdAt != null){
ps.add(BOOK.creaedAt).goe(createdAt);
}
if(!ps.isEmpty()){
predicate = ps.get(0);
if(ps.size() == 2){
predicate.and(ps.get(1));
}
}
return predicate;
}
private JPAQuery getQueryForBook(Predicate predicate, int startRow, int pageSize){
Epression sortfield = BOOK.createdAt;
OrderSpecifier orderSpecifier = new OrderSpecifier(Order.ASC, sortfield);
JPAQuery jpaQuery = new JPAQUERY(entityManager);
jpaQuery.from(BOOK)
.where(predicate)
.orderBy(sortfield)
.offset(startRow)
.limit(pageSize);
return jpaQuery;
}
private List<BookResult> getBook(Predicate predicate, int startRow, int pageSize){
List<BookResult> list = new ArrayList<>();
JPAQuery jpaQuery = getQueryForBook(predicate, startRow, pageSize);
final List<Tuple> results = jpaQuery.list(BOOK_FIELD);
for(Tuple tuple:results){
list.add(wrapForBook(tuple));
}
return list;
}
private BookResult wrapForBook(Tuple tuple){
BookResult bookResult = new BookResult();
bookResult.setName(tuple.get(BOOK.name));
bookResult.setId(tuple.get(BOOK.id));
return bookResult;
}
3.使用 Jdbctemplate
Jdbctemplate bookTemplate;
private static final String SQL = "SELECT * FROM book WHERE name = :name"
public List<BookResult> findBook(String name){
Map<String, Object> params = new HashMap<>();
params.put("name", name);
return bookTemplate.query(SQL, params, new RowMapper<Book>(){
@Override
public BookResult mapRow(ResultSet rs, int rowNum) throws SQLException{
BookResult bookResult = new BookResult();
bookResult.setid(rs.getLong("id"));
return bookResult;
}
});
}