Spring boot学习之spring-data-jpa的使用（二）

Spring boot学习之spring-data-jpa的使用（二）

上篇我们简单介绍了一些简单的基本查询，虽然hibernate机制中有@ManyToOne,@ManyToMany...等关联性的配置，而在实际的开发中这些简单的基本查询是远远不够的，而此篇我们便来介绍一下其相关的一些复杂查询。

1.简单的自定义查询

所谓简单的自定义查询就是根据方法名自动生成SQL，当然方法名是有命令规则的，主要语法是findXXBy,readAXXBy,queryXXBy,countXXBy, getXXBy后面跟属性名称，以及可以使用一些And、Or等关键字，还可以加上OrderBy进行排序，详见如下表格：

关键字方法命名sql where字句AndfindByNameAndPwdwhere name= ? and pwd =?OrfindByNameOrSexwhere name= ? or sex=?Is,EqualsfindById,findByIdEqualswhere id= ?BetweenfindByIdBetweenwhere id between ? and ?LessThanfindByIdLessThanwhere id < ?LessThanEqualfindByIdLessThanEqualswhere id <= ?GreaterThanfindByIdGreaterThanwhere id > ?GreaterThanEqualfindByIdGreaterThanEqualswhere id > = ?AfterfindByIdAfterwhere id > ?BeforefindByIdBeforewhere id < ?IsNullfindByNameIsNullwhere name is nullisNotNull,NotNullfindByNameNotNullwhere name is not nullLikefindByNameLikewhere name like ?NotLikefindByNameNotLikewhere name not like ?StartingWithfindByNameStartingWithwhere name like '?%'EndingWithfindByNameEndingWithwhere name like '%?'ContainingfindByNameContainingwhere name like '%?%'OrderByfindByIdOrderByXDescwhere id=? order by x descNotfindByNameNotwhere name <> ?InfindByIdIn(Collection<?> c)where id in (?)NotInfindByIdNotIn(Collection<?> c)where id not in (?)TruefindByAaaTuewhere aaa = trueFalsefindByAaaFalsewhere aaa = falseIgnoreCasefindByNameIgnoreCasewhere 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);   }

controller中调用代码如下(多余的代码可不用理会，后续会陆续讲到)：

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. */@RestControllerpublic 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();}

自此spring data jpa的一些常用情况介绍完毕了，其还有一些高级应用，若有兴趣可去官网查看。