发布于 2年前
使用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>说明:
- 我这里的jpa的版本没有配置版本号，是因为我的工程继承了一个parent的父工程，父工程已经配置了版本号，所以子工程会继承父工程的版本号，顺便说一下，这里我使用的版本号是2.5.5，因为我使用的spingboot的版本号是springboot2.5.5
- 同样的道理，我的父工程了配置了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是做啥的呢？
且听我慢慢说来
- 这个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的部分属性应该怎么写呢？
