Spring Data JPA must master 20 + query keywords

WeChat public number: an excellent waster
If you have any questions or suggestions, please leave a message in the background. I will try my best to solve your problems.

Preface

It's the contribution of my younger martial brother again. I really like technology. The following is the original:

Today, when I read the official documents of Spring Data JPA, I found that there is no complete semantic translation of Jpa keywords. So I wrote a Chinese document today. If there is any mistake, I hope you can spray it lightly.

Here is the official picture with sample code and comments:

First, create the specified database by referring to the official documents

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;

Example code and notes < refer to the above order >

/**
 * @Author: EvilSay
 * @Date: 2019/2/25 16:15
 */
public interface DemoJpaRepositories extends JpaRepository<DemoJpa,Integer> {

    //Find by firstName and LastName (both must be in the database)
    DemoJpa findByFirstNameAndLastName(String firstName, String lastName);

    //Find by firstName or LastName (one of them is OK)
    DemoJpa findByLastNameOrFirstName(String lastName,String firstName);

    //Find whether it exists in the database according to firstName < similar to the following keywords >
    //DemoJpa findByFirstName(String firstName);
    DemoJpa findByFirstNameIs(String firstName);

    //Data between age and age2
    List<DemoJpa> findByAgeBetween(Integer age, Integer age2);

    //Data less than the specified age value
    List<DemoJpa> findByAgeLessThan(Integer age);

    //Data less than or equal to the specified age value
    List<DemoJpa> findByAgeLessThanEqual(Integer age);

    //Data greater than the specified age value
    List<DemoJpa> findByAgeGreaterThan(Integer age);

    //Data greater than or equal to the specified age value
    List<DemoJpa> findByAgeGreaterThanEqual(Integer age);

    //The data before the age value is specified is similar to the keyword < lessthan >
    List<DemoJpa> findByAgeAfter(Integer age);

    //The data after the specified age value is similar to the keyword < greaterthan >
    List<DemoJpa>  findByAgeBefore(Integer age);

    //Return data with empty age field
    List<DemoJpa> findByAgeIsNull();

    //Return data whose age field is not empty
    List<DemoJpa> findByAgeNotNull();

    /**
     * At one time, I thought it was a fuzzy query similar to database,
     * But I went to the official document and saw that there were no wildcards in it.
     * So I think it's similar
     * 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);

    //Ditto
    List<DemoJpa> findByFirstNameNotLike(String firstName);

    //Find similar names in the database (for example, entering a name "M" Jpa will return mu lt iple data sources with names beginning with M) < similar database fuzzy query >
    List<DemoJpa> findByFirstNameStartingWith(String firstName);

    //Different name specified in lookup database (same as above)
    List<DemoJpa> findByFirstNameEndingWith(String firstName);

    //Find the specified data source contained (this is different from the above two fields in that it must input complete data to query)
    List<DemoJpa> findByFirstNameContaining(String firstName);

    //Select all data sources according to age and sort them in ascending order according to LastName
    List<DemoJpa> findByAgeOrderByLastName(Integer age);

    //Returns all data that is not the specified age
    List<DemoJpa> findByAgeNot(Integer age);

    //Finds data returned by multiple specified age s
    List<DemoJpa> findByAgeIn(List<Integer> age);

}

Unit test < all passed >

@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("Data results"+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());
    }

}

Posterior language

If this article is of any help to you, please help me. Your good looks are my driving force for writing.

In addition, you can receive free learning materials after you send 1024. For details, please refer to this old article: Python, C + +, Java, Linux, Go, front end, algorithm data sharing

Tags: Java Database Spring less Python

Posted on Mon, 02 Dec 2019 09:59:57 -0500 by daijames