发布于 1年前

使用jpa和querydsl来实现嵌套属性的查询

1、引入相关的jpa和querydsl相关的包

<dependency>
      <groupId>com.querydsl</groupId>
      <artifactId>querydsl-jpa</artifactId>
      <version>${querydsl.version}</version>
</dependency>
<dependency>
      <groupId>com.querydsl</groupId>
      <artifactId>querydsl-apt</artifactId>
      <version>${querydsl.version}</version>
      <scope>provided</scope>
</dependency>
<dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

说明:

  1. 我这里的jpa的版本没有配置版本号,是因为我的工程继承了一个parent的父工程,父工程已经配置了版本号,所以子工程会继承父工程的版本号,顺便说一下,这里我使用的版本号是2.5.5,因为我使用的spingboot的版本号是springboot2.5.5
  2. 同样的道理,我的父工程了配置了querydsl的版本,子工程直接继承,我这里的版本是4.4.0,但是这里有个疑问,我顺着父工程去寻找属性querydsl.version,但是没有找到,如果有小伙伴知道怎么找,希望能给我回复

2、引入实体类Message

@Entity
@Table(name = "msg_message")
public class Message implements Serializable {
    @Id
    @Column(name="id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    @Embedded
    @AttributeOverrides({
            @AttributeOverride( name = "address", column = @Column(name = "from_address")),
            @AttributeOverride( name = "name", column = @Column(name = "from_name"))
    })
    private MessageAddress fromMessageAddress;
    @AttributeOverrides({
            @AttributeOverride( name = "address", column = @Column(name = "to_address")),
            @AttributeOverride( name = "name", column = @Column(name = "to_name"))
    })
    @Embedded
    private MessageAddress toMessageAddress;
    private Long templateId;
    private String sendMsg;
    @Embedded
    private Creator creator;
    /**
     * 消息编码
     */
    private String messageCode;
    public String getMessageCode() {
        return messageCode;
    }
    private String status;
    public Message(){
    }
    private Message(MessageAddress fromMessageAddress, MessageAddress toMessageAddress, Long templateId,
                    String sendMsg, Creator creator, String status) {
        this.fromMessageAddress = fromMessageAddress;
        this.toMessageAddress = toMessageAddress;
        this.templateId = templateId;
        this.sendMsg = sendMsg;
        this.creator = creator;
        this.status = status;
        this.messageCode = generateMessageCode();
    }
    private String generateMessageCode() {
        String msgCode="%s-%s-%s";
        //默认技术类,一般是站内信息
        String type = "1";
        Long templateId = this.templateId;
        Calendar calendar = Calendar.getInstance();
        int year = calendar.get(Calendar.YEAR);
        int month = calendar.get(Calendar.MONTH)+1;
        int date = calendar.get(Calendar.DAY_OF_MONTH);
        String time;
        if (month <10) {
            time = ""+year+"0"+month+date;
        } else {
            time = ""+year+month+date;
        }
        return String.format(msgCode, type,templateId, time);
    }
    public static Message newMessageInfo(MessageAddress fromMessageAddress, MessageAddress toMessageAddress, Long templateId,
                                         String sendMsg, Creator creator, String status) {
        return new Message(fromMessageAddress, toMessageAddress, templateId, sendMsg, creator,status);
    }
    public String getStatus() {
        return status;
    }
    public void setStatus(String status) {
        this.status = status;
    }
    public MessageAddress getFromMessageAddress() {
        return fromMessageAddress;
    }
    public void setFromMessageAddress(MessageAddress fromMessageAddress) {
        this.fromMessageAddress = fromMessageAddress;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public MessageAddress getToMessageAddress() {
        return toMessageAddress;
    }
    public void setToMessageAddress(MessageAddress toMessageAddress) {
        this.toMessageAddress = toMessageAddress;
    }
    public Long getTemplateId() {
        return templateId;
    }
    public void setTemplateId(Long templateId) {
        this.templateId = templateId;
    }
    public String getSendMsg() {
        return sendMsg;
    }
    public void setSendMsg(String sendMsg) {
        this.sendMsg = sendMsg;
    }
    public Creator getCreator() {
        return creator;
    }
    public void setCreator(Creator creator) {
        this.creator = creator;
    }
    public void setMessageCode(String messageCode) {
        this.messageCode = messageCode;
    }
    @Override
    public String toString() {
        return "Message{" +
                "id=" + id +
                ", fromMessageAddress=" + fromMessageAddress +
                ", toMessageAddress=" + toMessageAddress +
                ", templateId=" + templateId +
                ", sendMsg='" + sendMsg + '\'' +
                ", creator=" + creator +
                ", messageCode='" + messageCode + '\'' +
                ", status='" + status + '\'' +
                '}';
    }
}
@Embeddable
public class MessageAddress implements Serializable {
    private String address;
    private String name;
    public MessageAddress(){}
    public MessageAddress(String address, String name) {
        this.address = address;
        this.name = name;
    }
    public String getAddress() {
        return address;
    }
    public String getName() {
        return name;
    }
    @Override
    public String toString() {
        return "Address{" +
                "address='" + address + '\'' +
                ", name='" + name + '\'' +
                '}';
    }
    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        MessageAddress that = (MessageAddress) o;
        return address.equals(that.address);
    }
    @Override
    public int hashCode() {
        return Objects.hash(address);
    }
}
@Embeddable
public class Creator {
    private Long creatorId;
    private String creatorName;
    private Long modifierId;
    private String telephone;
    private Long createTime;
    private Long modifyTime;
    public Creator(){}
    private Creator(Long creatorId, String creatorName, String telephone){
        this.creatorId = creatorId;
        this.creatorName = creatorName;
        this.telephone = telephone;
        this.createTime = System.currentTimeMillis();
        this.modifyTime = System.currentTimeMillis();
        this.modifierId = creatorId;
    }
    /**
     * 创建一个新的创建者信息
     * @param creatorId 创建人id
     * @param creatorName 创建人名称
     * @param telephone 电话号码
     * @return 创建者信息
     */
    public static Creator newCreatorInfo(Long creatorId, String creatorName, String telephone) {
        return new Creator(creatorId,creatorName,telephone);
    }
    public Creator withCreateTime(Long createTime) {
        this.createTime = createTime;
        return this;
    }
    public Creator withModifyTime() {
        this.modifyTime = System.currentTimeMillis();
        return this;
    }
    public Creator withModifierId(Long modifierId) {
        this.modifierId = modifierId;
        return this;
    }
    public Long getCreatorId() {
        return creatorId;
    }
    public String getCreatorName() {
        return creatorName;
    }
    public String getTelephone() {
        return telephone;
    }
    public Long getCreateTime() {
        return createTime;
    }
    public Long getModifyTime() {
        return modifyTime;
    }
    public void setCreatorId(Long creatorId) {
        this.creatorId = creatorId;
    }
    public void setCreatorName(String creatorName) {
        this.creatorName = creatorName;
    }
    public void setTelephone(String telephone) {
        this.telephone = telephone;
    }
    public void setCreateTime(Long createTime) {
        this.createTime = createTime;
    }
    public void setModifyTime(Long modifyTime) {
        this.modifyTime = modifyTime;
    }
    public Long getModifierId() {
        return modifierId;
    }
    @Override
    public String toString() {
        return "CreatorInfo{" +
                "creatorId=" + creatorId +
                ", creatorName='" + creatorName + '\'' +
                ", modifierId=" + modifierId +
                ", telephone='" + telephone + '\'' +
                ", createTime=" + createTime +
                ", modifyTime=" + modifyTime +
                '}';
    }
}

这里我需要对Message的属性toAddrsss里的字段address进行查询以及对sendMsg进行查询,还要按照creator属性里面的createTime进行排序,同时进行分页,

由于我的这个对象具有嵌入的属性,所以在使用单纯的jsa进行分页查询的时候,我尝试了很多方法也没有成功,所以我才想尝试一下querydsl

一下是我的query service方法

/**
     * 能进行模糊匹配
     * @param address 消息接收者地址
     * @param page 页号
     * @param size 每页数据
     * @param sendMsg 消息内容查询参数
     * @return 分页消息信息
     */
    public Page<MessageDto> queryMessageInfo(String address, int page, int size, String sendMsg) {
        Pageable pageable = PageRequest.of(page, size);
        QMessage message = QMessage.message;
        QueryResults<MessageDto> queryResults = this.query.select(Projections.bean(MessageDto.class,
                message.sendMsg.as("msg"),message.id.as("id"),message.creator.createTime.as("createTime"))).from(message)
                .where(message.sendMsg.like(sendMsg).and(message.toMessageAddress.address.eq(address)))
                .orderBy(message.creator.createTime.desc())
                .offset(pageable.getOffset())
                .limit(pageable.getPageSize())
                .fetchResults();
        return new PageImpl<>(queryResults.getResults(),pageable, queryResults.getTotal());
    }

一看到这里,小伙伴跟定会懵了,你的QMessage是哪里来的?你的MessageDto是做啥的呢?

且听我慢慢说来

  1. 这个QMessage是插件生成的,因此需要在工程的pom中配置以下插件
<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</outputDirectory>
              <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
            </configuration>
          </execution>
        </executions>
      </plugin>

2.这个MessageDto就是为了只展示前台需要的数据,如果使用Message的话,会将全部数据返回给前台,这样也可以,但是不是最优化的做法。

3.这里的this.query是啥?其实呢?它就是这个

@Autowired
    private EntityManager entityManager;
    private JPAQuery<Void> query;
    @PostConstruct
    public void init() {
        this.query =  new JPAQuery<>(entityManager);
}
注意:第一点中的代码会在maven build的时候才会生成,而且生成的目录是target/generated-sources
如下图所示

注意:这里还特别用蓝色和黄色给予区别对待。
顺便贴出MessageDto对象
public class MessageDto {
    private Long id;
    private String msg;
    private Long createTime;
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getMsg() {
        return msg;
    }
    public void setMsg(String msg) {
        this.msg = msg;
    }
    public Long getCreateTime() {
        return createTime;
    }
    public void setCreateTime(Long createTime) {
        this.createTime = createTime;
    }
}

最后我们在讨论下

QueryResults<Message> queryResults = this.query.select(message).from(message)
                .where(message.sendMsg.like(sendMsg).and(message.toMessageAddress.address.eq(address)))
                .orderBy(message.creator.createTime.desc())
                .offset(pageable.getOffset())
                .limit(pageable.getPageSize())
                .fetchResults();

如果代码写成这样,会有什么好处和不好的地方?

如果不适用MessageDTo,只查询出Message的部分属性应该怎么写呢?

©2020 edoou.com   京ICP备16001874号-3