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 {@Autowiredprivate SpringJpaExtraProperties springJpaProperties;@Beanprotected 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);}// 默認是xmlsqlTemplates.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: mysqlshow-sql: truehibernate:ddl-auto: updatenaming:#命名策略strategy: org.hibernate.cfg.ImprovedNamingStrategyproperties:hibernate:dialect: org.hibernate.dialect.MySQL5Dialectextra:#源碼看 FreemarkerSqlTemplates.resolveSqlResource#templateLocation: classpath: sqltemplatestemplateBasePackage: sqltemplatesdatasource:driver-class-name: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3306/test?characterEncoding=utf8username: rootpassword: 123456

Dao文件

public interface TestRepository extends GenericJpaRepository<Test, Long>, JpaSpecificationExecutor {@TemplateQueryList<Test> getData();@TemplateQueryList<Test> getList(@Param("id") Integer id);@TemplateQueryList<Test> getListByPage(Test test, Pageable pageable);@TemplateQueryList<Map<String, Object>> getListMap(@Param("id") Integer id);@TemplateQueryPage<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 {@Idprivate 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() {// getDataList<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

轉載于:https://www.cnblogs.com/qiaozhuangshi/p/11148885.html

總結

以上是生活随笔為你收集整理的JPA扩展（自定义sql）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。