SpringDataJPA（三）SpringDataJPA自定义@Query查询方法

左手的ㄟ右手 2021-12-24 10:21 399阅读 0赞

## 源码 ##

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

## 什么是自定义@Query查询方法 ##

如果想编写具有多个条件的非常复杂的查询来过滤数据，那么查询方法名称将变得非常复杂。为了避免这种情况，SpringDataJPA支持在@Query注释中自定义SQL自定查询

## 自定义SQL查询 ##

**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-springdatajpa03?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：**

public interface UserRepository extends Repository<User, Long> { 
 
 /** * 根据id查询用户信息 * * User为ORM映射的类名，即为ORM映射的实体类名 * 根据参数的顺序，参数分别为?1，?2，?3...... * * @param id * @return */
 @Query("select u from User u where u.id=?1")
 public User findUser(Long id);
 
 /** * 根据id查询用户信息 * * User为ORM映射的类名，即为ORM映射的实体类名 * :id为传递的参数 * * @param id * @return */
 @Query("select u from User u where u.id=:id")
 public User findUserByParam(@Param("id") Long id);
 
 /** * 根据id和name查询用户信息 * * User为ORM映射的类名，即为ORM映射的实体类名 * 根据参数的顺序，分别为?1，?2，?3...... * * @param id * @return */
 @Query("select u from User u where u.id=?1 and u.name=?2")
 public User findUserByIdAndName(Long id, String name);
 
 /** * 查询所有用户信息 * * User为ORM映射的类名，即为ORM映射的实体类名 * * @return */
 @Query("select u from User u")
 public List<User> findUserList();
 
 /** * 根据name模糊查询 * * User为ORM映射的类名，即为ORM映射的实体类名 * 根据参数的顺序，分别为?1，?2，?3...... * * @param name * @return */
 @Query("select u from User u where name like %?1%")
 public List<User> findUserListByLikeName(String name);
 
 /** * 根据name模糊查询 * * User为ORM映射的类名，即为ORM映射的实体类名 * 根据参数的顺序，分别为?1，?2，?3...... * * @param name * @return */
 @Query("select u from User u where name like concat('%',?1,'%') ")
 public List<User> findUserListByLikeConcatName(String name);
 
 /** * 根据id查询用户信息 * * nativeQuery = true时，JPA能够识别真正的SQL语句 * * @param id * @return */
 @Query(value = "select * from user where id=?1", nativeQuery = true)
 public User findUserByNativeQuery(int id);
 
 /** * 使用SpEL表达式 * * 从Spring Data JPA 1.4版开始，我们支持在手动定义的查询中使用受限制的SpEL模板表达式@Query。 * 在执行查询时，将根据预定义的变量集评估这些表达式。Spring Data JPA支持一个名为的变量entityName。它的用法是select x from #{#entityName} x。 * 它插入entityName与给定存储库关联的域类型。该entityName解决如下：如果域类型已设置的name属性@Entity的注释，它被使用。否则，使用域类型的简单类名 * * @param name * @return */
 @Query("select u from #{#entityName} u where u.name = ?1")
 public List<User> findBySpEL(String name);
 }

**UserRepositoryTests：**

@RunWith(SpringRunner.class)
 @SpringBootTest
 public class UserRepositoryTests { 
 
 @Autowired
 private UserRepository userRepository;
 
 @Test
 public void findUserTest() { 
 Long id = 1L;
 User user = userRepository.findUser(id);
 System.out.println("user:" + user);
 }
 
 @Test
 public void findUserByParamTest() { 
 Long id = 1L;
 User user = userRepository.findUserByParam(id);
 System.out.println("user:" + user);
 }
 
 @Test
 public void findUserByIdAndNameTest() { 
 Long id = 1L;
 String name = "ljk";
 User user = userRepository.findUserByIdAndName(id, name);
 System.out.println("user:" + user);
 }
 
 @Test
 public void findUserListTest() { 
 List<User> userList = userRepository.findUserList();
 System.out.println("userList:" + userList);
 }
 
 @Test
 public void findUserListByLikeNameTest() { 
 String name = "j";
 List<User> userListByLikeName = userRepository.findUserListByLikeName(name);
 System.out.println("userListByLikeName:" + userListByLikeName);
 }
 
 @Test
 public void findUserListByLikeConcatNameTest() { 
 String name = "j";
 List<User> userListByLikeName = userRepository.findUserListByLikeConcatName(name);
 System.out.println("userListByLikeName:" + userListByLikeName);
 }
 
 @Test
 public void findUserByNativeQueryTest() { 
 int id = 1;
 User user = userRepository.findUserByNativeQuery(id);
 System.out.println("user:" + user);
 }
 
 @Test
 public void findByLastnameTest() { 
 String str = "wyj";
 List<User> userList = userRepository.findBySpEL(str);
 System.out.println("userList:" + userList);
 }
 }

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