dsl mysql_极简Querydsl入门

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, QueryDslPredicateExecutor{

}

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,QueryDslPredicateExecutor{

}

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, QueryDslPredicateExecutor{

}

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