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

源码

GitHub： https://github.com/291685399/springboot-learning/tree/master/springboot-springdatajpa03

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

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

自定义SQL查询

pom.xml：

<dependencies>
    <!-- springboot -->
    <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>

    <!-- SpringDataJPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <!-- mysql -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

    <!-- lombok -->
    <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查询用户信息
     * <p>
     * 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查询用户信息
     * <p>
     * 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查询用户信息
     * <p>
     * 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);

    /**
     * 查询所有用户信息
     * <p>
     * User为ORM映射的类名，即为ORM映射的实体类名
     *
     * @return
     */
    @Query("select u from User u")
    public List<User> findUserList();

    /**
     * 根据name模糊查询
     * <p>
     * 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模糊查询
     * <p>
     * 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查询用户信息
     * <p>
     * nativeQuery = true时，JPA能够识别真正的SQL语句
     *
     * @param id
     * @return
     */
    @Query(value = "select * from user where id=?1", nativeQuery = true)
    public User findUserByNativeQuery(int id);

    /**
     * 使用SpEL表达式
     * <p>
     * 从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);
    }
}