用JPA和MySQL检查用户是否喜欢该帖子的最佳方法是什么? [英] What would be the best way to implement to check if the Post has been liked by the User using JPA and MySQL?
本文介绍了用JPA和MySQL检查用户是否喜欢该帖子的最佳方法是什么?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
好吧，这听起来很愚蠢，但是我是MySQL的新手，所以我真的不知道是否需要Joins或其他东西.
Ok, it can sound stupid but I'm newbie at MySQL so I really have no idea if it's a case for Joins or something else.
所以我有3个表，POST，USER和LIKES.基本上，他们有:
So I have 3 tables, POST, USER, LIKES. Basically they have:
POST
post_id | caption | media | ...
USER
user_id | name | email | ...
LIKES
like_id | post_id | user_id | ...
Java中的对象是:
And the Objects in Java are:
@Entity
@Table(name="POST")
public class PostMW{
@Id
@Column(name="id", nullable=false)
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;
@OneToOne(cascade = CascadeType.ALL)
@JoinColumn(name="media_id", nullable=false, unique=true)
private MediaMW media;
....
@Transient
private Boolean liked;
}
还有:
@Entity
@Table(name="LIKES")
public class LikeMW {
@Id
@Column(name="id", nullable=false)
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;
@ManyToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "post_id", nullable=false)
@JsonBackReference
private PostMW post;
@ManyToOne(cascade = CascadeType.ALL)
@JoinColumn(name="user_id", nullable=false)
private UserMW user;
}
用户可以请求POST列表，因此如果该用户喜欢，我需要在喜欢"属性中设置每个POST.
An USER can request a list of POST so I need to set in the property "liked" for EACH POST if that USER has liked it.
到目前为止，我做到了:
So far I did:
public class FeedServiceImpl implements FeedService{
private PostDao<PostMW, Long> postDao;
...
@Override
public List<PostMW> getFeed(int page, UserMW user) {
List<PostMW> result;
int offset = page * ConstUtil.FEED_POSTS_PER_PAGE;
postDao.openCurrentSession();
result = postDao.list(offset, ConstUtil.FEED_POSTS_PER_PAGE);
for(PostMW post : result){
//TODO
//post.setLiked(postDao.postLikedByUser(post, user));
//PS
// FOR EVERY POST I'M EXECUTING THE QUERY BELOW
//@NamedQuery(name="isLiked", query="SELECT COUNT(likes.id) FROM
//LikeMW likes WHERE likes.post.id = :postID AND likes.user.id = :userID"),
}
postDao.closeCurrentSession();
return result;
}
...
}
我什至不知道它是否有效，因为我真的不喜欢这种想法，例如检索100条帖子，然后在一个循环内，连续发出100个请求，只是检查用户是否喜欢该帖子.我什至没有尝试.
I don't even know if it works cause I really don't like the idea to retrieve for example 100 posts and inside a loop, make 100 requests in a row just to check if the user liked that post or not so I didn't even try.
那我该怎么办?任何建议将不胜感激！
So, what should I do? Any suggestion would be very appreciate!!
谢谢！
暂时尝试以下查询:
List<Object> result = entityManager
.createQuery("SELECT post FROM PostMW post LEFT OUTER JOIN post.hasLiked liked ON post.id = liked.post_id AND liked.user_id =:userID")
.setParameter("userID", 1)
.setFirstResult(offset).setMaxResults(itemsPerPage)
.getResultList();
更新了我的对象.现在，我的 PostMW 对象具有
Updated my objects. Now my PostMW object has
@Entity
@Table(name="POST")
public class PostMW{
...
@ManyToMany(cascade = CascadeType.ALL)
@JoinTable(name="LIKES", joinColumns=@JoinColumn(name="post_id"), inverseJoinColumns=@JoinColumn(name="user_id"))
private List<UserMW> hasLiked;
...
}
UserMW 有
@Entity
@Table(name="USER")
public class UserMW {
...
@Id
@Column(name="id", nullable=false)
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;
@Column(name="name", nullable=false, length=255)
private String name;
...
}
获取此异常:
java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager:
Exception Description: Internal problem encountered while compiling [SELECT post FROM PostMW post LEFT OUTER JOIN LikeMW likes ON post.id = likes.post_id AND likes.user_id =:userID].
Internal Exception: java.lang.NullPointerException
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1605)
at com.jonathan.myworld.model.dao.impl.PostDaoImpl.list(PostDaoImpl.java:72)
at com.jonathan.myworld.service.impl.FeedServiceImpl.getFeed(FeedServiceImpl.java:45)
at com.jonathan.myworld.UserWS.login(UserWS.java:151)
at com.jonathan.myworld.UserWS.loginWithGoogle(UserWS.java:60)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at com.google.appengine.tools.development.agent.runtime.Runtime.invoke(Runtime.java:130)
at com.google.api.server.spi.SystemService.invokeServiceMethod(SystemService.java:363)
at com.google.api.server.spi.SystemServiceServlet.execute(SystemServiceServlet.java:113)
at com.google.api.server.spi.SystemServiceServlet.doPost(SystemServiceServlet.java:71)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1166)
at com.google.appengine.api.socket.dev.DevSocketFilter.doFilter(DevSocketFilter.java:74)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
at com.google.appengine.tools.development.ResponseRewriterFilter.doFilter(ResponseRewriterFilter.java:128)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
at com.google.appengine.tools.development.HeaderVerificationFilter.doFilter(HeaderVerificationFilter.java:34)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
at com.google.appengine.api.blobstore.dev.ServeBlobFilter.doFilter(ServeBlobFilter.java:63)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
at com.google.apphosting.utils.servlet.TransactionCleanupFilter.doFilter(TransactionCleanupFilter.java:50)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
at com.google.appengine.tools.development.StaticFileFilter.doFilter(StaticFileFilter.java:125)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
at com.google.appengine.tools.development.DevAppServerModulesFilter.doDirectRequest(DevAppServerModulesFilter.java:366)
at com.google.appengine.tools.development.DevAppServerModulesFilter.doDirectModuleRequest(DevAppServerModulesFilter.java:349)
at com.google.appengine.tools.development.DevAppServerModulesFilter.doFilter(DevAppServerModulesFilter.java:116)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:388)
at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418)
at com.google.appengine.tools.development.DevAppEngineWebAppContext.handle(DevAppEngineWebAppContext.java:98)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
at com.google.appengine.tools.development.JettyContainerService$ApiProxyHandler.handle(JettyContainerService.java:511)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
at org.mortbay.jetty.Server.handle(Server.java:326)
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:938)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:755)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)
at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)
Caused by: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Internal problem encountered while compiling [SELECT post FROM PostMW post LEFT OUTER JOIN LikeMW likes ON post.id = likes.post_id AND likes.user_id =:userID].
Internal Exception: java.lang.NullPointerException
at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildUnexpectedException(HermesParser.java:207)
at org.eclipse.persistence.internal.jpa.jpql.HermesParser.populateQueryImp(HermesParser.java:296)
at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildQuery(HermesParser.java:163)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:142)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:116)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:102)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:86)
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1603)
... 49 more
Caused by: java.lang.NullPointerException
at org.eclipse.persistence.queries.DatabaseQuery.addArgument(DatabaseQuery.java:449)
at org.eclipse.persistence.queries.DatabaseQuery.addArgument(DatabaseQuery.java:419)
at org.eclipse.persistence.internal.jpa.jpql.HermesParser.addArguments(HermesParser.java:98)
at org.eclipse.persistence.internal.jpa.jpql.HermesParser.populateQueryImp(HermesParser.java:287)
推荐答案
您可以尝试从刚刚获得的帖子列表中提取喜欢的帖子.并带有"SELECT ... IN"请求)，然后填充布尔值isLiked;像这样的东西:
you could try to extract the liked posts from the list of posts you've just got before. with a "SELECT ... IN" request) and then fill the boolean isLiked; something like :
...
@Override
public List<PostMW> getFeed(int page, UserMW user) {
List<PostMW> result;
int offset = page * ConstUtil.FEED_POSTS_PER_PAGE;
postDao.openCurrentSession();
result = postDao.list(offset, ConstUtil.FEED_POSTS_PER_PAGE);
List<Long> postIds = new ArrayList();
for(PostMW post : result){
postIds.add(post.getid();
}
//then EXtract all liked post from list :
Query queryIn = em.createQuery("SELECT p.post.id FROM LikeMW p WHERE p.user = :user AND p.post IN :values");
queryIn.setParameter("values", postIds );
queryIn.setParameter("user", currentUser);
List<Object[]> resultIn = queryIn.getResultList();
Set<Long> postIdLiked ) new HashSet<>();
for(Object[] row: resultIn) {
postIdLiked.add(row[0]);
}
//then fill the boolean isLiked
for(PostMW post : result){
if (postIdLiked.contains(post.getId())
post.setLiked(true);
}
postDao.closeCurrentSession();
return result;
}
...
这篇关于用JPA和MySQL检查用户是否喜欢该帖子的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
