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

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