JPA扩展（自定义sql）

pom.xml

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

    <modelVersion>4.0.0</modelVersion>

    <parent>

        <groupId>org.springframework.boot</groupId>

        <artifactId>spring-boot-starter-parent</artifactId>

        <version>1.5.9.RELEASE</version>

        <relativePath/> <!-- lookup parent from repository -->

    </parent>

    <groupId>per.qiao</groupId>

    <artifactId>springbootdemo</artifactId>

    <version>0.0.1-SNAPSHOT</version>

    <name>springbootdemo</name>

    <description>sprnigboot学习</description>

    <properties>

        <java.version>1.8</java.version>

    </properties>

    <dependencies>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter</artifactId>

        </dependency>

        <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>

		<!-- 扩展JPA包 -->

        <dependency>

            <groupId>com.slyak</groupId>

            <artifactId>spring-data-jpa-extra</artifactId>

            <version>2.1.2.RELEASE</version>

        </dependency>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-jdbc</artifactId>

        </dependency>

        <dependency>

            <groupId>mysql</groupId>

            <artifactId>mysql-connector-java</artifactId>

        </dependency>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-test</artifactId>

        </dependency>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-configuration-processor</artifactId>

        </dependency>

        <dependency>

            <groupId>org.projectlombok</groupId>

            <artifactId>lombok</artifactId>

        </dependency>

    </dependencies>

    <build>

        <plugins>

            <plugin>

                <groupId>org.springframework.boot</groupId>

                <artifactId>spring-boot-maven-plugin</artifactId>

            </plugin>

        </plugins>

    </build>

</project>

得益于spring-data-jpa-extra 包

先编写两个文件

@Configuration

@EnableConfigurationProperties(SpringJpaExtraProperties.class)

@AutoConfigureAfter({ DataSourceAutoConfiguration.class })

public class JpaExtraAutoConfiguration {

    @Autowired

    private SpringJpaExtraProperties springJpaProperties;

    @Bean

    protected FreemarkerSqlTemplates freemarkerSqlTemplates() {

        FreemarkerSqlTemplates sqlTemplates = new FreemarkerSqlTemplates();

        String templateBasePackage = springJpaProperties.getTemplateBasePackage();

        if (templateBasePackage != null) {

            sqlTemplates.setTemplateBasePackage(templateBasePackage);

        }

        String templateLocation = springJpaProperties.getTemplateLocation();

        if (templateLocation != null) {

            sqlTemplates.setTemplateLocation(templateLocation);

        }

        // 默认是xml

        sqlTemplates.setSuffix(".sftl");

        return sqlTemplates;

    }

}

@ConfigurationProperties(prefix = "spring.jpa.extra")

public class SpringJpaExtraProperties {

    /**

     * 源码看 FreemarkerSqlTemplates.resolveSqlResource

     * 例如 templateLocation：classpath:/sqltemplates 那么 扫描路径为 classpath:/sqltemplates/** /*.sftl

     *  templateLocation：classpath:/sqltemplates/Test.sftl 那么将只扫描这个一个文件

     *  例如  templateBasePackage：sqltemplates.mysql 那么扫描路径为 classpath*: sqltemplates/sql/** /*.sftl

     *

     *  两个属性可以共存

     */

    private String templateLocation;

    private String templateBasePackage;

    public String getTemplateLocation() {

        return templateLocation;

    }

    public void setTemplateLocation(String templateLocation) {

        this.templateLocation = templateLocation;

    }

    public String getTemplateBasePackage() {

        return templateBasePackage;

    }

    public void setTemplateBasePackage(String templateBasePackage) {

        this.templateBasePackage = templateBasePackage;

    }

}

再写一个facoties文件

META-INF/spring.facotries

org.springframework.boot.autoconfigure.EnableAutoConfiguration=\

per.qiao.config.JpaExtraAutoConfiguration

yml文件

debug: false

spring:

  main:

    banner-mode: "off"

  jpa:

    database: mysql

    show-sql: true

    hibernate:

        ddl-auto: update

        naming:

            #命名策略

          strategy: org.hibernate.cfg.ImprovedNamingStrategy

    properties:

          hibernate:

            dialect: org.hibernate.dialect.MySQL5Dialect

    extra:

        #源码看 FreemarkerSqlTemplates.resolveSqlResource

        #templateLocation: classpath: sqltemplates

        templateBasePackage: sqltemplates

  datasource:

    driver-class-name: com.mysql.jdbc.Driver

    url: jdbc:mysql://localhost:3306/test?characterEncoding=utf8

    username: root

    password: 123456

Dao文件

public interface TestRepository extends GenericJpaRepository<Test, Long>, JpaSpecificationExecutor {

    @TemplateQuery

    List<Test> getData();

    @TemplateQuery

    List<Test> getList(@Param("id") Integer id);

    @TemplateQuery

    List<Test> getListByPage(Test test, Pageable pageable);

    @TemplateQuery

    List<Map<String, Object>> getListMap(@Param("id") Integer id);

    @TemplateQuery

    Page<Test> findByName(String name, Pageable pageable);

    // 这个是JPA默认的支持@Query使用原生sql

    @Query(nativeQuery = true, value = "select * from test where name like ?1")

    List<Test> findTest(String name);

}

sql的文件 Test.sftl

该文件使用freemarker的语法 FreeMarker基础语法

--getData

select id, name, subject from test where 1 = 1

<#--<#if content??>-->

    <#--AND id = ${id}-->

<#--</#if>-->

--getList

select * from test where 1 = 1

<#if id??>

    AND id = ${id}

</#if>

--getListByPage

select * from test where 1 = 1

<#if id??>

    AND id = ${id}

</#if>

--getListMap

select name, birthday from test t left join test2 t2 on t.id = t2.id where 1 = 1

<#if id??>

    And t.id = ${id}

</#if>

--findByName

select * from test

<#if name??>

    And name = ${name}

</#if>

entity

@Entity

@ToString

@Builder

@NoArgsConstructor

@AllArgsConstructor

@Data

public class Test implements Serializable {

    @Id

    private Long id;

    private String name;

    private String subject;

    private Integer score;

}

spirngBoot启动类上要加上@EnableJpaRepositories

@SpringBootApplication

@RestController

@ComponentScan({"per.qiao.entity"})

@EnableJpaRepositories(

        basePackages = {"per.qiao.**.dao"},

        repositoryFactoryBeanClass = GenericJpaRepositoryFactoryBean.class,

        repositoryBaseClass = GenericJpaRepositoryImpl.class)

public class App {

调用



public void say() {

    // getData

    List<Test> data = testRepository.getData();

    //

    List<Test> tests = testRepository.getList(1);

    //

    Test test = Test.builder().id(1L).build();

    PageRequest pageRequest = new PageRequest(1, 2, new Sort(Sort.Direction.ASC, "id"));

    List<Test> pageDatas = testRepository.getListByPage(test, pageRequest);

    System.out.println(pageDatas);

    //

    List<Map<String, Object>> listMap = testRepository.getListMap(1);

    //

    Page<Test> page = testRepository.findByName("张三", pageRequest);

    List<Test> content = page.getContent();

    System.out.println(content);

    // 使用jpa的默认@Query注解

    List<Test> tests = testRepository.findTest("张%");

    System.out.println(tests);

}

注意sftl配置文件的名字要与实体类的名字一样

可以自定义修改一些东西，比如文件名与类名一样这点，关键类在FreemarkerTemplateQuery

源码地址: spring-data-jpa-extra

巴特西

JPA扩展（自定义sql）

最新文章

热门文章