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

极简Querydsl入门

1 数据库准备

1.1 创建名为learnsql的数据库

1.2 建表

1.2.1 创建部门表

1.2.2 创建职员表

1.2.3 创建薪水等级表

2 编写Java代码

2.1 配置文件

2.2 创建实体

2.2.1 部门表实体

2.2.2 职员表实体

2.2.3 薪水等级表实体

2.3 创建repository

2.3.1 部门表对应的repository

2.3.2 职员表对应的repository

2.3.3 薪水等级表对应的repository

2.4 创建service

2.5 创建controller

2.6 在浏览器调用接口

推荐阅读更多精彩内容