现在有一张消息message表,一张用户member表,一张中间表member_message。中间表有一个标识flag是否已读等。
在使用hibernate查询的时候,按照对象是查询不到的。member_message不是配置@ManyToMany生成的,是单独的一个实体!里面3个字段
这样就要用到JPA原生sql了。因为要查询到flag标识是否已读页面需要展示状态
message实体:
package com.allk.entity.message;
import java.util.Date;
import javax.persistence.Basic;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Lob;
import javax.persistence.Transient;
import com.allk.entity.common.IdEntity;
/**
* @Author Darren
* @Time 2017年11月31日 上午11:56:19
* Description:
*/
@Entity
public class Message extends IdEntity {
private static final long serialVersionUID = 8366654814667535609L;
protected String title; // 消息标题
protected String content; // 消息内容
// protected Set<Member> members = new HashSet<Member>(); // 会员
protected Integer shelves; // 消息状态
protected Date createDate;
protected Boolean isAllMembers;
protected Integer type;// 系统消息(2),系统通知(1)
protected int flag;//已读还是未读 未读(2),已读(1)
public Message() {
}
public String getTitle() {
return title;
}
// @ManyToMany(fetch = FetchType.LAZY)
// @JoinTable
// @JsonIgnore
// public Set<Member> getMembers() {
// return members;
// }
@Lob
@Basic(fetch = FetchType.LAZY)
public String getContent() {
return content;
}
public Integer getShelves() {
return shelves;
}
public void setTitle(String title) {
this.title = title;
}
public void setContent(String content) {
this.content = content;
}
public void setShelves(Integer shelves) {
this.shelves = shelves;
}
public Date getCreateDate() {
return createDate;
}
public void setCreateDate(Date createDate) {
this.createDate = createDate;
}
public Integer getType() {
return type;
}
public void setType(Integer type) {
this.type = type;
}
public Boolean getIsAllMembers() {
return isAllMembers;
}
public void setIsAllMembers(Boolean isAllMembers) {
this.isAllMembers = isAllMembers;
}
@Transient
public int getFlag() {
return flag;
}
public void setFlag(int flag) {
this.flag = flag;
}
// public void setMembers(Set<Member> members) {
// this.members = members;
// }
}
package com.allk.entity.message;
import javax.persistence.Entity;
import javax.persistence.Table;
import com.allk.Constant;
import com.allk.entity.common.IdEntity;
/**
* @Author Darren
* @Time 2017年11月13日 下午12:13:05
* @Description:用一句话描述这个类的作用
*/
@Entity
@Table(name = "message_member")
public class MessageMemberMidd extends IdEntity {
private int flag = Constant.MessageByType.UN_READ; // 未读(2),已读(1)
private Long message_id;
private Long member_id;
public int getFlag() {
return flag;
}
public void setFlag(int flag) {
this.flag = flag;
}
public Long getMessage_id() {
return message_id;
}
public void setMessage_id(Long message_id) {
this.message_id = message_id;
}
public Long getMember_id() {
return member_id;
}
public void setMember_id(Long member_id) {
this.member_id = member_id;
}
}
member实体就不贴了,里面内容太多,就是一个实体
查询的代码:
@SuppressWarnings("deprecation")
public SearchResult<Message> findPersonalSysMessage(Integer memeberId, Integer pageNo, Integer pageSize,Integer msgType) {
if(pageNo==null){
pageNo=1;
}
if(pageSize==null){
pageSize=Constant.DEFAULT_PAGE_SIZE;
}
int startIndex = (pageNo-1)*pageSize;
StringBuilder sql=new StringBuilder("SELECT m.*,mm.flag FROM message m LEFT JOIN message_member mm ON mm.message_id=m.id WHERE mm.member_id=? AND m.type=? ORDER BY mm.message_id DESC limit ?,?");
EntityManager entityManager = getSession().getEntityManagerFactory().createEntityManager();
javax.persistence.Query query = entityManager.createNativeQuery(sql.toString());
// @SuppressWarnings("unchecked")
// NativeQuery query = getSession().createNativeQuery(sql.toString(),Message.class);
query.setParameter(1, memeberId);
if(msgType==Constant.MessageByType.SYS_MSG||msgType==null){
query.setParameter(2,Constant.MessageByType.SYS_MSG);
}
if(msgType==Constant.MessageByType.SYS_NOTICE){
query.setParameter(2,Constant.MessageByType.SYS_NOTICE);
}
query.setParameter(3,startIndex);
query.setParameter(4,pageSize);
query.unwrap(NativeQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
List list = query.getResultList();
List<Message> rsList=new ArrayList<Message>();
for (Object object : list) {
Map row=(Map)object;
// String[] row = (String[]) object;
Message message = new Message();
message.setId(Long.valueOf(String.valueOf(row.get("id"))));
message.setTitle((String)row.get("title"));
message.setContent((String)row.get("content"));
message.setShelves((Integer)row.get("shelves"));
message.setCreateDate((Date)row.get("createDate"));
message.setIsAllMembers((Boolean)row.get("isAllMembers"));
message.setType((Integer)row.get("type"));
message.setFlag((Integer)row.get("flag"));
rsList.add(message);
}
query.unwrap(NativeQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
这行代码比较重要,这决定的返回的数据集格式。这样是返回map的数据结构
如果不要setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)也不会出错,只是查询出来的单行数据object是数组,每个元素就是返回值,并没有相对应的列名,这样感觉代码可读性不强,并且也不好区分里面元素到底是哪个字段的值。需要仔细去看,一旦数据库表结构有一点变化,很容易出错
然后挨个挨个放入message对象就行了。message对象是没有在数据库存flag这个标识的(实体有一个flag字段),它只接受查询出来的状态。如果存数据库不合理,所有用户共享此条信息。不能因为一个人就直接改了,所以生成出来了另一张表member_message。一行对应一个用户的一条消息和已读未读的状态