Spring Data JPA 必须掌握的 20+ 个查询关键字

悠悠 2022-03-16 07:28 159阅读 0赞

> 微信公众号：一个优秀的废人  
> 如有问题或建议，请后台留言，我会尽力解决你的问题。

#### 前言 ####

又是小师弟的投稿，确是一个喜欢技术的朋友。以下为原文：

今天闲的无聊看 Spring Data JPA 官方文档的时候，发现并没有完整的 Jpa 关键字语义翻译。所以今天写了一篇中文文档，如果有错误，望大家轻喷。

以下为官方图片以及示例代码和注释 ：

![Spring Data JPA 关键字][Spring Data JPA]

![Spring Data JPA 关键字][Spring Data JPA 1]

**首先参照官方文档创建指定数据库**

CREATE TABLE `demo_jpa`  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `first_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `last_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      `sex` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      `email` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      `age` int(12) NOT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

**示例代码及注释<参照以上顺序>**

/** * @Author: EvilSay * @Date: 2019/2/25 16:15 */
    public interface DemoJpaRepositories extends JpaRepository<DemoJpa,Integer> { 
    
        //根据firstName与LastName查找(两者必须在数据库有)
        DemoJpa findByFirstNameAndLastName(String firstName, String lastName);
    
        //根据firstName或LastName查找(两者其一有就行)
        DemoJpa findByLastNameOrFirstName(String lastName,String firstName);
    
        //根据firstName查找它是否存在数据库里<类似与以下关键字>
        //DemoJpa findByFirstName(String firstName);
        DemoJpa findByFirstNameIs(String firstName);
    
        //在Age数值age到age2之间的数据
        List<DemoJpa> findByAgeBetween(Integer age, Integer age2);
    
        //小于指定age数值之间的数据
        List<DemoJpa> findByAgeLessThan(Integer age);
    
        //小于等于指定age数值的数据
        List<DemoJpa> findByAgeLessThanEqual(Integer age);
    
        //大于指定age数值之间的数据
        List<DemoJpa> findByAgeGreaterThan(Integer age);
    
        //大于或等于指定age数值之间的数据
        List<DemoJpa> findByAgeGreaterThanEqual(Integer age);
    
        //在指定age数值之前的数据类似关键字<LessThan>
        List<DemoJpa> findByAgeAfter(Integer age);
    
        //在指定age数值之后的数据类似关键字<GreaterThan>
        List<DemoJpa>  findByAgeBefore(Integer age);
    
        //返回age字段为空的数据
        List<DemoJpa> findByAgeIsNull();
    
        //返回age字段不为空的数据
        List<DemoJpa> findByAgeNotNull();
    
        /** * 该关键字我一度以为是类似数据库的模糊查询, * 但是我去官方文档看到它里面并没有通配符。 * 所以我觉得它类似 * DemoJpa findByFirstName(String firstName); * @see https://docs.spring.io/spring-data/jpa/docs/2.1.5.RELEASE/reference/html/#jpa.repositories */
        DemoJpa findByFirstNameLike(String firstName);
    
        //同上
        List<DemoJpa> findByFirstNameNotLike(String firstName);
    
        //查找数据库中指定类似的名字(如：输入一个名字"M" Jpa会返回多个包含M开头的名字的数据源)<类似数据库模糊查询>
        List<DemoJpa> findByFirstNameStartingWith(String firstName);
    
        //查找数据库中指定不类似的名字(同上)
        List<DemoJpa> findByFirstNameEndingWith(String firstName);
    
        //查找包含的指定数据源(这个与以上两个字段不同的地方在与它必须输入完整的数据才可以查询)
        List<DemoJpa> findByFirstNameContaining(String firstName);
    
        //根据age选取所有的数据源并按照LastName进行升序排序
        List<DemoJpa> findByAgeOrderByLastName(Integer age);
    
        //返回不是指定age的所有数据
        List<DemoJpa> findByAgeNot(Integer age);
    
        //查找包含多个指定age返回的数据
        List<DemoJpa> findByAgeIn(List<Integer> age);
    
    }

**单元测试<已经全部通过>**

@SpringBootTest
    @RunWith(SpringRunner.class)
    @Slf4j
    public class DemoJpaRepositoriesTest { 
    
        @Autowired
        private DemoJpaRepositories repositories;
    
        @Test
        public void findByFirstNameAndLastName() { 
            DemoJpa demoJpa = repositories.findByFirstNameAndLastName("May", "Eden");
            Assert.assertEquals(demoJpa.getFirstName(),"May");
        }
    
        @Test
        public void findByLastNameOrFirstName() { 
            DemoJpa demoJpa = repositories.findByLastNameOrFirstName("Geordie", "Eden");
            Assert.assertNotEquals(demoJpa.getLastName(),"Eden");
        }
    
        @Test
        public void findByFirstNameIs() { 
            DemoJpa demoJpa = repositories.findByFirstNameIs("amy");
            Assert.assertNull(demoJpa);
        }
    
        @Test
        public void findByAgeBetween() { 
            List<DemoJpa> demoJpaList = repositories.findByAgeBetween(15, 17);
            Assert.assertEquals(3,demoJpaList.size());
        }
    
        @Test
        public void findByAgeLessThan() { 
            List<DemoJpa> demoJpaList = repositories.findByAgeLessThan(17);
            Assert.assertEquals(2,demoJpaList.size());
        }
    
        @Test
        public void findByAgeLessThanEqual() { 
            List<DemoJpa> demoJpaList = repositories.findByAgeLessThanEqual(17);
            Assert.assertEquals(3,demoJpaList.size());
        }
    
        @Test
        public void findByAgeGreaterThan() { 
            List<DemoJpa> demoJpaList = repositories.findByAgeGreaterThan(17);
            Assert.assertEquals(2,demoJpaList.size());
        }
    
        @Test
        public void findByAgeGreaterThanEqual() { 
            List<DemoJpa> demoJpaList = repositories.findByAgeGreaterThanEqual(17);
            Assert.assertEquals(3,demoJpaList.size());
        }
    
        @Test
        public void findByAgeAfter() { 
            List<DemoJpa> demoJpaList = repositories.findByAgeAfter(17);
            Assert.assertEquals(2,demoJpaList.size());
        }
    
        @Test
        public void findByAgeBefore() { 
            List<DemoJpa> demoJpaList = repositories.findByAgeBefore(17);
            Assert.assertEquals(2,demoJpaList.size());
        }
    
        @Test
        public void findByAgeIsNull() { 
            List<DemoJpa> demoJpaList = repositories.findByAgeIsNull();
            Assert.assertEquals(0,demoJpaList.size());
        }
    
        @Test
        public void findByAgeNotNull() { 
            List<DemoJpa> demoJpaList = repositories.findByAgeNotNull();
            Assert.assertEquals(5,demoJpaList.size());
        }
    
        @Test
        public void findByFirstNameLike() { 
            DemoJpa demoJpa = repositories.findByFirstNameLike("May");
            Assert.assertNotNull(demoJpa);
        }
    
        @Test
        public void findByFirstNameNotLike() { 
    
        }
    
        @Test
        public void findByFirstNameStartingWith() { 
            List<DemoJpa> demoJpaList = repositories.findByFirstNameStartingWith("May");
            Assert.assertEquals(2,demoJpaList.size());
        }
    
        @Test
        public void findByFirstNameEndingWith() { 
            List<DemoJpa> demoJpaList = repositories.findByFirstNameEndingWith("Evil");
            Assert.assertEquals(0,demoJpaList.size());
        }
    
        @Test
        public void findByFirstNameContaining() { 
            List<DemoJpa> demoJpaList = repositories.findByFirstNameContaining("hack");
            Assert.assertEquals(0,demoJpaList.size());
        }
    
        @Test
        public void findByAgeOrderByLastName() { 
            List<DemoJpa> demoJpaList = repositories.findByAgeOrderByLastName(18);
            for (DemoJpa demoJpaL : demoJpaList){ 
                log.info("数据结果"+demoJpaL.toString());
            }
        }
    
        @Test
        public void findByAgeNot() { 
            List<DemoJpa> demoJpaList = repositories.findByAgeNot(20);
            Assert.assertEquals(5,demoJpaList.size());
        }
    
        @Test
        public void findByAgeIn() { 
            List<DemoJpa> demoJpaList = repositories.findByAgeIn(Arrays.asList(15, 16));
            Assert.assertEquals(2,demoJpaList.size());
        }
    
    }

#### 后语 ####

如果本文对你哪怕有一丁点帮助，请帮忙点好看。你的好看是我坚持写作的动力。

另外，关注之后在发送 **1024** 可领取免费学习资料。资料内容详情请看这篇旧文：[Python、C++、Java、Linux、Go、前端、算法资料分享][Python_C_Java_Linux_Go]

![1240][]

[Spring Data JPA]: /images/20220316/f4f48afbaa2c4571852fcde9475aa148.png
[Spring Data JPA 1]: /images/20220316/891bfd8597364a4891005bc7d0d4d72c.png
[Python_C_Java_Linux_Go]: https://www.jianshu.com/p/ed43dc8aa11e
[1240]: /images/20220316/da284d737e0f485d82b911676a4d3fac.png