Spring Data JPA 动态sql查询 普通查询

前言：

      依赖jar包源码地址：https://github.com/slyak/spring-data-jpa-extrahttps://github.com/slyak/spring-data-jpa-extra

      spring-data-jpa-extra 来解决三个问题：

• 动态原生查询支持，如 mybatis
• 返回类型可以是任何东西
• 没有代码，只有 sql

1、版本依赖（ 注意依赖版本！）

<dependency>
   <groupId>com.slyak</groupId>
   <artifactId>spring-boot-starter-jpa-extra</artifactId>
   <version>2.1.1.RELEASE</version>
</dependency>

2、配置config

/**
 * spring-data-jpa-extra配置类
 */
@Configuration
@EnableJpaRepositories(basePackages = "com.aug.jpa.orm", repositoryBaseClass = GenericJpaRepositoryImpl.class, repositoryFactoryBeanClass = GenericJpaRepositoryFactoryBean.class)
public class JpaExtraConfiguration {

}

3、Dao层继承及使用说明

1.   动态查询在dao方法上加注解

   @TemplateQuery  

   /**
    * @Description:
    * @Auther: tsy
    * @Date: 2022/08/09/3:29 下午
    */
   @Repository
   //extends JpaRepository 默认继承
   //public interface UserRepository extends JpaRepository<User, Integer>, JpaSpecificationExecutor<User> {
   //extends GenericJpaRepository 支持模版自定义动态sql查询
   public interface UserRepository extends GenericJpaRepository<User, Long>, JpaSpecificationExecutor<User> {
   
       User findByName(String userName);
   
       @Query("select n from User n where n.name = ?1")
       User find(String userName);
   
       @Query(value = "select n.id,n.name from orm_user n where n.name = ?1", nativeQuery = true)
       User findByUserName(String userName);
   
       @Query(value = "select n.id,n.name from orm_user n where n.name = :#{#user.name} ", nativeQuery = true)
       User findByUser(@Param("user") User user);
   
       @TemplateQuery
       List<UserBo> findUserBos();
   
       @TemplateQuery
       UserBo findUserBoById();
   
       @TemplateQuery
       Page<User> queryUserList(@Param("userParam") UserParam userParam, Pageable pageable);
   }
   

2. 在resources下创建明名为sql的文件夹，创建User.sftl文件 （路径可配置，约定优于配置）

-- findUserBos
select id, name as userName, phone_number as phoneNumber from orm_user

-- queryUserList
select *
from orm_user
where id = :id
order by
id desc

-- findUserBoById
select id, name as userName, phone_number as phoneNumber
from orm_user
where id = 1

         if 条件查询

    <#if processNoList??>
        AND note.process_no in (
        <#list processNoList as no>
            '${no}'<#if no_has_next>,</#if>
        </#list>
      )
    </#if>
    <#if processStatus??>
        AND note.process_status = :processStatus
    </#if>

4、结果演示

 

5、项目结构