极简Querydsl入门
2018-12-06 本文已影响0人
境_5f76
1 数据库准备
使用MySQL数据库。
1.1 创建名为learnsql的数据库
建库语句
create datebase learnsql;
使用刚刚创建的learnsql数据库
use learnsql;
1.2 建表
数据表使用常用的职员信息表，分别为：
- 部门表：dept
- 职员表： emp
- 薪水等级表： salgrade
1.2.1 创建部门表
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`deptno` int(10) NOT NULL,
`dname` varchar(14) DEFAULT NULL,
`loc` varchar(13) DEFAULT NULL,
PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `dept` WRITE;
INSERT INTO `dept` VALUES (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCHING','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');
UNLOCK TABLES;
1.2.2 创建职员表
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`empno` int(4) NOT NULL,
`ename` varchar(10) DEFAULT NULL,
`job` varchar(9) DEFAULT NULL,
`mgr` int(4) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` double(7,2) DEFAULT NULL,
`comm` double(7,2) DEFAULT NULL,
`deptno` int(2) DEFAULT NULL,
PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `emp` WRITE;
INSERT INTO `emp` VALUES (7369,'SIMITH','CLERK',7902,'1980-12-17',800.00,NULL,20),(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600.00,300.00,30),(7521,'WARD','SALESMAN',7698,'1981-02-22',1250.00,500.00,30),(7566,'JONES','MANAGER',7839,'1981-04-02',2975.00,NULL,20),(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250.00,1400.00,30),(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850.00,NULL,30),(7782,'CLARK','MANAGER',7839,'1981-06-09',2450.00,NULL,10),(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000.00,NULL,20),(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000.00,NULL,10),(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500.00,NULL,30),(7876,'ADAMS','CLERK',7788,'1987-05-23',1100.00,NULL,20),(7900,'JAMES','CLERK',7698,'1981-12-03',950.00,NULL,30),(7902,'FORD','ANALYST',7566,'1981-12-03',3000.00,NULL,20),(7934,'MILLER','CLERK',7782,'1982-01-23',1300.00,NULL,10);
UNLOCK TABLES;
1.2.3 创建薪水等级表
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
`grade` int(11) DEFAULT NULL,
`losal` int(11) DEFAULT NULL,
`hisal` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `salgrade` WRITE;
INSERT INTO `salgrade` VALUES (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,5000);
UNLOCK TABLES;
2 编写Java代码
2.1 配置文件
#数据源地址
spring.datasource.url=jdbc:mysql://localhost:3306/learnsql?characterEncoding=utf8
#用户名
spring.datasource.username=root
#密码
spring.datasource.password=123456
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
#端口
server.port=8089
2.2 创建实体
首先创建与数据表对应的实体。
2.2.1 部门表实体
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.Data;
@Entity
@Table(name="dept")
public @Data class Dept {
@Id
@Column(name="deptno")
private Long deptno;
@Column(name="dname")
private String dname;
@Column(name="loc")
private String loc;
}
2.2.2 职员表实体
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.Data;
@Entity
@Table(name="emp")
public @Data class Emp {
@Id
@Column(name="empno")
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long empno;
@Column(name="ename")
private String ename;
@Column(name="job")
private String job;
@Column(name="mgr")
private String mgr;
@Column(name="hiredate")
private Date hireDate;
@Column(name="sal")
private Double sal;
@Column(name="comm")
private Double comm;
@Column(name="deptno")
private Long deptno;
}
2.2.3 薪水等级表实体
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="salgrade")
public class SalGrade {
@Id
@Column(name="grade")
private Long grade;
@Column(name="losal")
private Long losal;
@Column(name="hisal")
private Long hisal;
}
2.3 创建repository
要使用Querydsl在创建repository时，一定要继承QueryDslPredicateExecutor接口。
2.3.1 部门表对应的repository
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.querydsl.QueryDslPredicateExecutor;
import com.cha.querydsl.entity.Dept;
public interface DeptRepository extends JpaRepository<Dept, Long>, QueryDslPredicateExecutor<Dept>{
}
2.3.2 职员表对应的repository
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.querydsl.QueryDslPredicateExecutor;
import com.cha.querydsl.entity.Emp;
public interface EmpRepository extends JpaRepository<Emp, Long>,QueryDslPredicateExecutor<Emp>{
}
2.3.3 薪水等级表对应的repository
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.querydsl.QueryDslPredicateExecutor;
import com.cha.querydsl.entity.SalGrade;
public interface SalGradeRepository extends JpaRepository<SalGrade, Long>, QueryDslPredicateExecutor<SalGrade>{
}
2.4 创建service
import javax.annotation.PostConstruct;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.stereotype.Service;
import com.cha.querydsl.entity.Emp;
import com.cha.querydsl.repository.DeptRepository;
import com.cha.querydsl.repository.EmpRepository;
import com.cha.querydsl.repository.SalGradeRepository;
import com.cha.querydsl.entity.QEmp;
import com.querydsl.core.types.Predicate;
import com.querydsl.jpa.impl.JPAQueryFactory;
@Service
public class DESService {
@Autowired
private DeptRepository deptRepository;
@Autowired
private EmpRepository empRepository;
@Autowired
private SalGradeRepository salGradeRepository;
@PersistenceContext
private EntityManager entityManager;
private JPAQueryFactory queryFactory;
@PostConstruct
public void init() {
queryFactory = new JPAQueryFactory(entityManager);
}
public Emp findEmp(String userName) {
QEmp qEmp = QEmp.emp;
//这一句是核心
return queryFactory.selectFrom(qEmp).where(qEmp.ename.eq(userName)).fetchOne();
}
}
2.5 创建controller
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.cha.querydsl.entity.Emp;
import com.cha.querydsl.service.DESService;
@RestController
@RequestMapping("/index")
public class DESController {
@Autowired
private DESService service;
@RequestMapping("/sal")
public Emp findEmp() {
String userName = "KING";
Emp emp = service.findEmp(userName);
return emp;
}
}
2.6 在浏览器调用接口
在浏览器中输入
http://localhost:8089/index/sal
浏览器显示如下图
querydsl.PNG
