使用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;
    }u
    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的部分属性应该怎么写呢?

版权声明:著作权归作者所有。

相关推荐

TypeScript:接口(interface)属性的定义

  在TypeScript里,接口扮演了一个定义数据结构的角色,它在TypeScript的类型检查中起到很重要的作用。基本用法匿名定义sayHello(person: {name:string}) { console.log(person.name + ", 您好!"); } 这是一个很简单的例子,它使用匿

Sql Server使用查询语句更新数据

Sql Server支持在一个sql语句里使用查询的数据更新表。简单更新简单的使用一个表的数据更新另一个表。UPDATE    table_a  SET    table_a.col1=table_b.col1 FROM    table_b  WHERE table_

Pandas对应SQL的in和not in实现

在Pandas提供了pd.isin(),使用它可以实现SQL的in和not in。not in 对应于:~a.isin(b) 示例:假如有以下dataframe数据,它包含了列data如下:>>> df   data 0   a 1   b 2 &nb

CSS3使用transition属性实现过渡效果

属性详解transition属性目的是让css的一些属性(如background)的以平滑过渡的效果出现。它是一个合并属性,是由以下四个属性组合而成:transition-property:设置应用过渡的CSS属性,如background。transition-duration:设置过渡效果花费的时间。默认是 0。transition-timing-function:设置过渡效果的时间曲线。默认是

IOS实现TouchID和FaceID

IOS TouchID或FaceID核心实现1.#import <LocalAuthentication/LocalAuthentication.h> 2.创建LAContext实例context3.配置context的localizedFallbackTitle,为验证失败后的撤销操作4.主要实现context的两个方法: (1)- (BOOL)canEvaluatePo

禁止input文本框输入实现属性

今天想总结几个很有用的html标签,开发中经常用到,不熟悉的人可能还真不太清楚,分别是: 复制代码代码如下:readonly、disabled、autocomplete readonly表示此域的值不可修改,仅可与 type="text" 配合使用,可复制,可选择,可以接收焦点,后台会接收到传值. 复制代码代码如下:<input type="text" name="www.xxx" read

Python安全地创建多层嵌套目录

Python的pathlib包里的Path类提供了.mkdir()方法,我们使用它就可以安全地创建多层嵌套的目录。1、从pathlib导入Path类from pathlib import Path2、创建一个Path对象,并且以它将要创建的目录作为构造参数p = Path("/nested/directory")3、调用.mkdir()方法创建目录p.mkdir()如果目录是不存在的,这样就可以了