构建项目
- 访问地址：http://start.spring.io
- 添加Web、MySQL、JPA依赖
使用Druid数据源
- pom.xml添加Druid依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.9</version>
</dependency>
- 添加application.yml配置文件
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8
username: root
password: 123456
#配置监控统计拦截的filters
filters: stat,wall,log4j
#最大活跃数
maxActive: 20
#初始化数量
initialSize: 1
#最大连接等待超时时间
maxWait: 60000
#打开PSCache，并指定每个连接PSCache的大小
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
#通过connectionProperties属性打开mergeSql功能
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
minldle: 1
timeBetweenEvictionRunsMillis: 60000
minEvictableldleTimeMillis: 300000
validationQuery: select 1 from dual
testWhiledle: true
testOnBorrow: false
testOnReturn: false
jpa:
properties:
hibernate:
show_sql: true
format_sql: true
配置QueryDSL
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
</dependency>
<plugin>
<groupId>com.mysema.maven</groupId>
<artifactId>apt-maven-plugin</artifactId>
<version>1.1.3</version>
<executions>
<execution>
<goals>
<goal>process</goal>
</goals>
<configuration>
<outputDirectory>target/generated-sources/java</outputDirectory>
<processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
</configuration>
</execution>
</executions>
<dependencies>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<version>4.1.4</version>
</dependency>
</dependencies>
</plugin>
建表
DROP TABLE IF EXISTS `t_goods_info`;
CREATE TABLE `t_goods_info` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`type_id` varchar(50) DEFAULT NULL COMMENT '分类ID',
`name` varchar(50) DEFAULT NULL COMMENT '名称',
`price` decimal COMMENT '价格',
`unit` varchar(50) DEFAULT NULL COMMENT '单位',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `t_goods_type`;
CREATE TABLE `t_goods_type` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(50) DEFAULT NULL COMMENT '名称',
`is_show` char(2) DEFAULT NULL COMMENT '是否显示',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
建实体类
GoodsInfo.java
package com.gala.springdataJPA.entity;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;
import javax.persistence.Table;
@Entity
@Table(name = "t_goods_info")
public class GoodsInfo {
private Long id;
private GoodsType goodType;
private String name;
private Double price;
private String unit;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
@OneToOne
@JoinColumn(name = "type_id")
public GoodsType getGoodType() {
return goodType;
}
public void setGoodType(GoodsType goodType) {
this.goodType = goodType;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
public String getUnit() {
return unit;
}
public void setUnit(String unit) {
this.unit = unit;
}
}
GoodsType.java
package com.gala.springdataJPA.entity;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name = "t_goods_type")
public class GoodsType {
private Long id;
private String name;
private String isShow;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getIsShow() {
return isShow;
}
public void setIsShow(String isShow) {
this.isShow = isShow;
}
}
生成Query查询对象
执行maven compile命令的时候，QueryDSL这个插件会自动在我们配置的目录下生成Qxxx结构化查询实体对象，QueryDSL完全采用面向对象的方式来创建查询。
使用QueryDSL
- 创建GoodInfoDao接口，让其继承QueryDslPredicateExecutor接口，该接口是SpringDataJPA提供的querydsl查询接口。
package com.gala.springdataJPA.jpa;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import com.gala.springdataJPA.entity.GoodsInfo;
public interface GoodInfoDao extends JpaRepository<GoodsInfo, Long>, QuerydslPredicateExecutor<GoodsInfo> {
}
- 创建QueryController
package com.gala.springdataJPA.controller;
import java.util.Iterator;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.gala.springdataJPA.entity.GoodsInfo;
import com.gala.springdataJPA.entity.QGoodsInfo;
import com.gala.springdataJPA.jpa.GoodInfoDao;
import com.google.common.collect.Lists;
import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.jpa.impl.JPAQuery;
@RestController
public class QueryController {
@Autowired
private GoodInfoDao goodInfoDao;
@PersistenceContext
private EntityManager entityManager;
@RequestMapping(value = "/query")
public List<GoodsInfo> list() {
QGoodsInfo _goods = QGoodsInfo.goodsInfo;
// 构建JPA查询对象
JPAQuery<GoodsInfo> jpaQuery = new JPAQuery<>(entityManager);
// 返回查询接口
return jpaQuery.select(_goods).from(_goods).where(_goods.goodType.id.eq(Long.valueOf("1"))).fetch();
}
@RequestMapping(value = "/join")
public List<GoodsInfo> join() {
QGoodsInfo _goods = QGoodsInfo.goodsInfo;
// 查询条件
BooleanExpression expression = _goods.goodType.id.eq(Long.valueOf("1"));
// 执行查询
Iterator<GoodsInfo> iterator = goodInfoDao.findAll(expression).iterator();
List<GoodsInfo> goodInfos = Lists.newArrayList();
while (iterator.hasNext()) {
goodInfos.add(iterator.next());
}
return goodInfos;
}
}
