SpringDataJPA（二）SpringDataJPA自定义关键字查询

我会带着你远行 2021-12-23 04:11 459阅读 0赞

## 源码 ##

**GitHub：** [https://github.com/291685399/springboot-learning/tree/master/springboot-springdatajpa02][https_github.com_291685399_springboot-learning_tree_master_springboot-springdatajpa02]

## 什么是自定义关键字查询 ##

SpringDataJPA支持自定义关键字查询，在方法中含有关键字，SpringDataJPA会为你自动生成SQL

## SpringDataJPA提供的关键字 ##

<table> 
 <thead> 
  <tr> 
   <th>关键字</th> 
   <th>方法名举例</th> 
   <th>对应的SQL</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>And</td> 
   <td>findByNameAndAge</td> 
   <td>where name = ? and age = ?</td> 
  </tr> 
  <tr> 
   <td>Or</td> 
   <td>findByNameOrAge</td> 
   <td>where name = ? or age = ?</td> 
  </tr> 
  <tr> 
   <td>Is</td> 
   <td>findByNameIs</td> 
   <td>where name = ?</td> 
  </tr> 
  <tr> 
   <td>Equals</td> 
   <td>findByNameEquals</td> 
   <td>where name = ?</td> 
  </tr> 
  <tr> 
   <td>Between</td> 
   <td>findByAgeBetween</td> 
   <td>where age between ? and ?</td> 
  </tr> 
  <tr> 
   <td>LessThan</td> 
   <td>findByAgeLessThan</td> 
   <td>where age &lt; ?</td> 
  </tr> 
  <tr> 
   <td>LessThanEquals</td> 
   <td>findByAgeLessThanEqual</td> 
   <td>where age &lt;= ?</td> 
  </tr> 
  <tr> 
   <td>GreatorThan</td> 
   <td>findByAgeGreaterThan</td> 
   <td>where age &gt; ?</td> 
  </tr> 
  <tr> 
   <td>GreatorThanEquals</td> 
   <td>findByAgeGreaterThanEqual</td> 
   <td>where age &gt;= ?</td> 
  </tr> 
  <tr> 
   <td>After</td> 
   <td>findByAgeAfter</td> 
   <td>where age &gt; ?</td> 
  </tr> 
  <tr> 
   <td>Before</td> 
   <td>findByAgeBefore</td> 
   <td>where age &lt; ?</td> 
  </tr> 
  <tr> 
   <td>IsNull</td> 
   <td>findByNameIsNull</td> 
   <td>where name is null</td> 
  </tr> 
  <tr> 
   <td>IsNotNull，NotNull</td> 
   <td>findByNameIsNotNull，findByNameNotNull</td> 
   <td>where name is not null</td> 
  </tr> 
  <tr> 
   <td>Not</td> 
   <td>findByNameNot</td> 
   <td>where name &lt;&gt;?</td> 
  </tr> 
  <tr> 
   <td>In</td> 
   <td>findByAgeIn</td> 
   <td>where age in (?)</td> 
  </tr> 
  <tr> 
   <td>NotIn</td> 
   <td>findByAgeNotIn</td> 
   <td>where age not in (?)</td> 
  </tr> 
  <tr> 
   <td>NotLike</td> 
   <td>findByNameNotLike</td> 
   <td>where name not like ?</td> 
  </tr> 
  <tr> 
   <td>Like</td> 
   <td>findByNameLike</td> 
   <td>where name like ?</td> 
  </tr> 
  <tr> 
   <td>StartingWith</td> 
   <td>findByNameStartingWith</td> 
   <td>where name like ‘?%’</td> 
  </tr> 
  <tr> 
   <td>EndingWith</td> 
   <td>findByNameEndingWith</td> 
   <td>where name like ‘%?’</td> 
  </tr> 
  <tr> 
   <td>Containing，Contains</td> 
   <td>findByNameContaining，findByNameContains</td> 
   <td>where name like ‘%?%’</td> 
  </tr> 
  <tr> 
   <td>OrderBy</td> 
   <td>findByOrderByAgeDesc</td> 
   <td>order by age desc</td> 
  </tr> 
  <tr> 
   <td>True</td> 
   <td>findByBossTrue</td> 
   <td>where boss = true</td> 
  </tr> 
  <tr> 
   <td>False</td> 
   <td>findByBossFalse</td> 
   <td>where boss = false</td> 
  </tr> 
  <tr> 
   <td>IgnoreCase</td> 
   <td>findByNameIgnoreCase</td> 
   <td>where UPPER(name) = UPPER(?)</td> 
  </tr> 
 </tbody> 
</table>

## 自定义关键字查询 ##

**pom.xml：**

<dependencies>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
    
        
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
    
        
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>

**application.properties：**

# datasource
    spring.datasource.url=jdbc:mysql://127.0.0.1:3306/springboot-springdatajpa02?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
    spring.datasource.driver-class-name=com.mysql.jdbc.Driver
    spring.datasource.username=root
    spring.datasource.password=root
    # springdatajpa
    #打印出自动生产的SQL，方便调试的时候查看
    spring.jpa.show-sql=true
    #更新数据库表结构
    spring.jpa.hibernate.ddl-auto=update
    #对打印的sql进行格式化，方便查看
    spring.jpa.properties.hibernate.format_sql=true
    spring.jpa.properties.hibernate.use_sql_comments=true
    #指定生成表名的存储引擎为InneoDB
    spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

**User：**

@Entity
    @Data
    public class User { 
        @Id
        @GeneratedValue
        private Long id;
        private String name;
        private Integer age;
        private String sex;
        private String address;
    }

*  继承Repository或者Repository子接口

**UserRepository：**

@RunWith(SpringRunner.class)
    @SpringBootTest
    public class UserRepositoryTests { 
    
        @Autowired
        private UserRepository userRepository;
    
        @Test
        public void findByNameAndAge() { 
            List<User> userList = userRepository.findByNameAndAge("ljk", 18);
            System.out.println(userList);
        }
    
        @Test
        public void findByNameOrAge() { 
            List<User> userList = userRepository.findByNameOrAge("ljk", 18);
            System.out.println(userList);
        }
    
        @Test
        public void findByNameIs() { 
            String str = "wyj";
            List<User> userList = userRepository.findByNameIs(str);
            System.out.println(userList);
        }
    
        @Test
        public void findByNameEquals() { 
            String str = "wyj";
            List<User> userList = userRepository.findByNameEquals(str);
            System.out.println(userList);
        }
    
        @Test
        public void findByAgeBetween() { 
            int littleAge = 17;
            int bigAge = 18;
            List<User> userList = userRepository.findByAgeBetween(littleAge, bigAge);
            System.out.println(userList);
        }
    
        @Test
        public void findByAgeLessThan() { 
            int age = 19;
            List<User> userList = userRepository.findByAgeLessThan(age);
            System.out.println(userList);
        }
    
        @Test
        public void findByAgeLessThanEqual() { 
            int age = 18;
            List<User> userList = userRepository.findByAgeLessThanEqual(age);
            System.out.println(userList);
        }
    
        @Test
        public void findByAgeGreaterThan() { 
            int age = 17;
            List<User> userList = userRepository.findByAgeGreaterThan(age);
            System.out.println(userList);
        }
    
        @Test
        public void findByAgeGreaterThanEqual() { 
            int age = 18;
            List<User> userList = userRepository.findByAgeGreaterThanEqual(age);
            System.out.println(userList);
        }
    
        @Test
        public void findByOrderByAgeDesc() { 
            List<User> userList = userRepository.findByOrderByAgeDesc();
            System.out.println(userList);
        }
    
        @Test
        public void findByNameIgnoreCase() { 
            String name = "Wyj";
            List<User> userList = userRepository.findByNameIgnoreCase(name);
            System.out.println(userList);
        }
    }

[https_github.com_291685399_springboot-learning_tree_master_springboot-springdatajpa02]: https://github.com/291685399/springboot-learning/tree/master/springboot-springdatajpa02