Spring boot学习之spring-data-jpa的使用（二）
上篇我们简单介绍了一些简单的基本查询，虽然hibernate机制中有@ManyToOne,@ManyToMany...等关联性的配置，而在实际的开发中这些简单的基本查询是远远不够的，而此篇我们便来介绍一下其相关的一些复杂查询。
1.简单的自定义查询
所谓简单的自定义查询就是根据方法名自动生成SQL，当然方法名是有命令规则的，主要语法是findXXBy,readAXXBy,queryXXBy,countXXBy, getXXBy后面跟属性名称，以及可以使用一些And、Or等关键字，还可以加上OrderBy进行排序，详见如下表格：
| 关键字 | 方法命名 | sql where字句 |
| And | findByNameAndPwd | where name= ? and pwd =? |
| Or | findByNameOrSex | where name= ? or sex=? |
| Is,Equals | findById,findByIdEquals | where id= ? |
| Between | findByIdBetween | where id between ? and ? |
| LessThan | findByIdLessThan | where id < ? |
| LessThanEqual | findByIdLessThanEquals | where id <= ? |
| GreaterThan | findByIdGreaterThan | where id > ? |
| GreaterThanEqual | findByIdGreaterThanEquals | where id > = ? |
| After | findByIdAfter | where id > ? |
| Before | findByIdBefore | where id < ? |
| IsNull | findByNameIsNull | where name is null |
| isNotNull,NotNull | findByNameNotNull | where name is not null |
| Like | findByNameLike | where name like ? |
| NotLike | findByNameNotLike | where name not like ? |
| StartingWith | findByNameStartingWith | where name like '?%' |
| EndingWith | findByNameEndingWith | where name like '%?' |
| Containing | findByNameContaining | where name like '%?%' |
| OrderBy | findByIdOrderByXDesc | where id=? order by x desc |
| Not | findByNameNot | where name <> ? |
| In | findByIdIn(Collection<?> c) | where id in (?) |
| NotIn | findByIdNotIn(Collection<?> c) | where id not in (?) |
| True | findByAaaTue | where aaa = true |
| False | findByAaaFalse | where aaa = false |
| IgnoreCase | findByNameIgnoreCase | where UPPER(name)=UPPER(?) |
使用情况的代码如下：
package com.zxl.examples.service;
import com.zxl.examples.entity.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;
/**
* Created by Administrator on 2017/7/21.
*/
public interface UserRepository extends JpaRepository
{
//方法名要规则，sql会根据方法名自动生成sql进行操作
public List
findByUsername(String username);
List
findByNameAndPassword(String name,String password);
List
findByNameOrPassword(String name,String password);
List
findByPasswordLessThan(String password); List
findByNameAndPasswordLessThan(String name,String password); List
findByIdLessThanAndPasswordLessThan(Long id,String password); List
findByIdLessThanAndPasswordLessThanEqual(Long id,String password); List
findByIdLessThanEqual(Long id); List
findByPasswordBetween(String password1,String password2); List
findByUsernameIn(List
usernames); List
findByNameLike(String name); List
findByNameContaining(String name); }
2. 带有分页的自定义查询
在实际的开发中是免不了遇到有分页的情况的，而Spring data jpa已经帮我们实现了分页的功能，我们只需要在方法中传入Pageable参数即可，如要实现自定排序在方法中还可传入Sort参数，具体使用情况见如下代码，UserRepository类中的代码如下：
package com.zxl.examples.service;
import com.zxl.examples.entity.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;
/**
* Created by Administrator on 2017/7/21.
*/
public interface UserRepository extends JpaRepository
{
//方法名要规则，sql会根据方法名自动生成sql进行操作
public Slice
findByNameOrderByIdDesc(String name, Pageable pageable);
public Page
findByNameOrderByPasswordAscIdDesc(String name, Pageable pageable);
List
findByNameOrderByIdDesc(String name,Sort sort);
List
findByNameOrPassword(String name,String password, Pageable pageable); }
package com.zxl.examples.controller;
import com.zxl.examples.controller.common.ResultBean;
import com.zxl.examples.controller.common.SuccessBean;
import com.zxl.examples.entity.User;
import com.zxl.examples.service.UserRepository;
import com.zxl.examples.service.UserSerivceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Slice;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.*;
import java.util.ArrayList;
import java.util.List;
/**
* Created by Administrator on 2017/7/24.
*/
@RestController
public class UserController {
@Autowired
UserRepository userRepository;
@Autowired
UserSerivceImpl userSerivce;
@GetMapping("/users")
public List
findUserList(){
return userRepository.findAll();
}
@PostMapping("/users/add")
public User addUser(@RequestParam("username") String username,
@RequestParam("name") String name,
@RequestParam("password") String password){
User user = new User();
user.setUsername(username);
user.setName(name);
user.setPassword(password);
return userRepository.save(user);
}
@GetMapping("/users/{id}")
public User getUserById(@PathVariable Long id){
return userRepository.findOne(id);
}
@PutMapping("/users/{id}")
public User updUserById(@PathVariable Long id,@RequestParam("name") String name){
User user = userRepository.findOne(id);//先查出来，否则修改的时候会将其他request中没有的参数也给覆盖掉
user.setName(name);
return userRepository.save(user);//与保存是同一个方法
}
@DeleteMapping("/users/{id}")
public ResultBean delUserById(@PathVariable Long id){
userRepository.delete(id);
return new SuccessBean();
}
@GetMapping("/users/username/{username}")
public List
findByUsername(@PathVariable ("username") String username){
return userRepository.findByUsername(username);
}
@PostMapping("/users/addMore")
public void addMore(){
userSerivce.addMoreUsers();
}
@PostMapping("/users/addList")
public void addMoreList(){
userSerivce.addMoreList();
}
@GetMapping("/users/page/{page}")
public Page
findAllLimit(@PathVariable Integer page){
//页数从0开始算，比如第一页应传page=0
return userRepository.findAll(new PageRequest(page,6));
}
@GetMapping("/users/pagesort/{page}")
public Page
findAllLimitSort(@PathVariable Integer page){
//页数从0开始算，比如第一页应传page=0
return userRepository.findAll(new PageRequest(page,6,new Sort(Sort.Direction.DESC,"id")));
}
@GetMapping("/users/pagesortmore/{page}")
public Page
findAllLimitSortMore(@PathVariable Integer page){ //页数从0开始算，比如第一页应传page=0 List
orders = new ArrayList
(); Sort.Order nameAscOrder = new Sort.Order(Sort.Direction.ASC,"name"); Sort.Order idDescOrder = new Sort.Order(Sort.Direction.DESC,"id"); orders.add(nameAscOrder); orders.add(idDescOrder); return userRepository.findAll(new PageRequest(page,6,new Sort(orders))); } @GetMapping("/users/page/param/{page}") public Slice
findByNameOrderByIdDesc(@PathVariable Integer page){ //页数从0开始算，比如第一页应传page=0 return userRepository.findByNameOrderByIdDesc("123",new PageRequest(page,6,new Sort(Sort.Direction.DESC,"id"))); } @GetMapping("/users/page/parampw/{page}") public Page
findByNameOrderBypasswordAscOrderByIdDesc(@PathVariable Integer page){ //页数从0开始算，比如第一页应传page=0 List
orders = new ArrayList
(); Sort.Order nameAscOrder = new Sort.Order(Sort.Direction.ASC,"password"); Sort.Order idDescOrder = new Sort.Order(Sort.Direction.DESC,"id"); orders.add(nameAscOrder); orders.add(idDescOrder); return userRepository.findByNameOrderByPasswordAscIdDesc("123",new PageRequest(page,6,new Sort(orders))); } @GetMapping("/users/order") public List
findByNameOrderByIdDesc(){ //页数从0开始算，比如第一页应传page=0 return userRepository.findByNameOrderByIdDesc("123",new Sort(Sort.Direction.DESC,"id")); } @GetMapping("/users/moreparam") public List
findByNameAndPassword(){ //页数从0开始算，比如第一页应传page=0 return userRepository.findByNameAndPassword("123","123"); } @GetMapping("/users/moreparam2") public List
findByNameOrPassword(){ //页数从0开始算，比如第一页应传page=0 return userRepository.findByNameOrPassword("123","1"); } @GetMapping("/users/moreparam3") public List
findByNameAndPasswordLimit(){ //页数从0开始算，比如第一页应传page=0 return userRepository.findByNameOrPassword("123","123",new PageRequest(0,3,new Sort(Sort.Direction.DESC,"id"))); } @GetMapping("/users/moreparam4") public List
findByPasswordLessThan(){ //页数从0开始算，比如第一页应传page=0 return userRepository.findByPasswordLessThan("1"); } @GetMapping("/users/moreparam5") public List
findByNameAndPasswordLessThan(){ //页数从0开始算，比如第一页应传page=0 return userRepository.findByNameAndPasswordLessThan("123","1"); } @GetMapping("/users/moreparam6") public List
findByIdLessThanAndPasswordLessThan(){ //页数从0开始算，比如第一页应传page=0 return userRepository.findByIdLessThanAndPasswordLessThan(10L,"2"); } @GetMapping("/users/moreparam7") public List
findByIdLessThanAndPasswordLessThanEquals(){ //页数从0开始算，比如第一页应传page=0 return userRepository.findByIdLessThanAndPasswordLessThanEqual(10L,"1"); } @GetMapping("/users/moreparam8") public List
findByIdLessThanEquals(){ //页数从0开始算，比如第一页应传page=0 return userRepository.findByIdLessThanEqual(10L); } @GetMapping("/users/moreparam9") public List
findByPasswordBetween(){ //页数从0开始算，比如第一页应传page=0 return userRepository.findByPasswordBetween("1","2"); } @GetMapping("/users/moreparam10") public List
findByUsernameIn(){ //页数从0开始算，比如第一页应传page=0 List
usernames = new ArrayList
(); usernames.add("1"); usernames.add("2"); usernames.add("3"); return userRepository.findByUsernameIn(usernames); } @GetMapping("/users/moreparam11") public List
findByNameLike(){ //页数从0开始算，比如第一页应传page=0 return userRepository.findByNameLike("1"); } @GetMapping("/users/moreparam12") public List
findByNameContaining(){ //页数从0开始算，比如第一页应传page=0 return userRepository.findByNameContaining("1"); } @GetMapping("/users/moreparam13") public List
findByUsername(){ //页数从0开始算，比如第一页应传page=0 User user = new User(); user.setUsername("1"); return userRepository.findByUsername(user); } @GetMapping("/users/moreparam14") public List
findByUsername14(){ //页数从0开始算，比如第一页应传page=0 return userRepository.findByUsername("1"); } @GetMapping("/users/moreparam15") public List
findByIdAndUsername15(){ //页数从0开始算，比如第一页应传page=0 return userRepository.findByIdAndUsername(12L,"345"); } @GetMapping("/users/moreparam16") public List
findAllLimit16(){ //页数从0开始算，比如第一页应传page=0 return userRepository.findAll2(new PageRequest(0,4)); } @GetMapping("/users/moreparam17") public List
findAllLimit17(){ //页数从0开始算，比如第一页应传page=0 return userRepository.findAll3(); } @GetMapping("/users/moreparam18/{username}") public User getUserByUsername(@PathVariable String username){ //页数从0开始算，比如第一页应传page=0 return userSerivce.getUserByUsername(username); } @DeleteMapping("/users/del/{id}") public void delUserById12(@PathVariable Long id){ User user = userRepository.findOne(id); if(user !=null){ userSerivce.delUserById(user); } } @GetMapping("/users/moreparam19") public String setUserInRedis(){ //页数从0开始算，比如第一页应传page=0 return userSerivce.setUserInRedis(); } @GetMapping("/users/moreparam20") public void delUserInRedis(){ //页数从0开始算，比如第一页应传page=0 userSerivce.delUserInRedis(); } @PostMapping("/users/moreparam21/{username}") public User save(@PathVariable String username){ //页数从0开始算，比如第一页应传page=0 User user = new User(); user.setUsername(username); user.setPassword("123456"); user.setName(username); userSerivce.save(user); return user; } @PostMapping("/users/moreparam22/{username}") public User addUser(@PathVariable String username){ //页数从0开始算，比如第一页应传page=0 User user = new User(); user.setUsername(username); user.setPassword("123456"); user.setName(username); userSerivce.addUser(user); return user; } @PostMapping("/users/moreparam23/{username}") public User addUser2(@PathVariable String username){ //页数从0开始算，比如第一页应传page=0 User user = new User(); user.setUsername(username); user.setPassword("123456"); user.setName(username); userSerivce.addUser2(user); return user; } @GetMapping("/users/moreparam24/{username}") public User getUserByUsername2(@PathVariable String username){ //页数从0开始算，比如第一页应传page=0 return userSerivce.getUserByUsername2(username); } @GetMapping("/users/moreparam25/{username}") public User getUserByUsername3(@PathVariable String username){ //页数从0开始算，比如第一页应传page=0 return userSerivce.getUserByUsername3(username); } }
3. 自定义sql查询
其实Spring data 觉大部分的SQL都可以根据方法名定义的方式来实现，但是由于某些原因我们想使用自定义的SQL来查询，spring data也是完美支持的；在SQL的
查询方法上面使用@Query注解，如涉及到删除和修改在需要加上@Modifying.也可以根据需要添加 @Transactional 对事物的支持，查询超时的设置等
代码详见如下：
package com.zxl.examples.service;
import com.zxl.examples.entity.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;
/**
* Created by Administrator on 2017/7/21.
*/
public interface UserRepository extends JpaRepository
{
//SpEL (Spring Expression Language)
@Query("select u from User u where u.username = :#{#user.username}")
List
findByUsername(@Param("user") User user);
@Query("select u from User u where u.username = :username")
List
findByUsername2(@Param("username") String username);
@Query("select u from User u where u.username=?2 AND u.id=?1")
List
findByIdAndUsername(Long id,String username);
@Query("select u from User u")
List
findAll2(Pageable pageable); @Query(value="select * from user limit 0,6",nativeQuery = true)//使用原生的sql，此处连的是mysql List
findAll3(); }
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
