将SQL联接转换为JPQL

Question

当前，JPQL查询的联接中的所有项目都将在getRangeOfArticles方法中返回，而只应返回范围的项目。

我想要的MySQL控制台输出，但希望在JPQL:中实现

mysql> 
mysql> SELECT * FROM articles LEFT OUTER JOIN newsgroups ON articles.NEWSGROUP_ID=newsgroups.ID  WHERE newsgroups.NEWSGROUP = "gwene.com.economist" AND articles.ID BETWEEN 450 AND 453;
+-----+---------------+--------------+------+---------------------+
| ID  | MESSAGENUMBER | NEWSGROUP_ID | ID   | NEWSGROUP           |
+-----+---------------+--------------+------+---------------------+
| 450 |           554 |            5 |    5 | gwene.com.economist |
| 451 |           555 |            5 |    5 | gwene.com.economist |
| 452 |           556 |            5 |    5 | gwene.com.economist |
| 453 |           557 |            5 |    5 | gwene.com.economist |
+-----+---------------+--------------+------+---------------------+
4 rows in set (0.02 sec)
mysql> 
mysql> show tables;
+------------------+
| Tables_in_usenet |
+------------------+
| articles         |
| newsgroups       |
+------------------+
2 rows in set (0.01 sec)
mysql> describe articles;
+---------------+------------+------+-----+---------+----------------+
| Field         | Type       | Null | Key | Default | Extra          |
+---------------+------------+------+-----+---------+----------------+
| ID            | bigint(20) | NO   | PRI | NULL    | auto_increment |
| MESSAGENUMBER | int(11)    | YES  |     | NULL    |                |
| NEWSGROUP_ID  | bigint(20) | YES  | MUL | NULL    |                |
+---------------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> describe newsgroups;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| ID        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| NEWSGROUP | varchar(255) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> 

我正在尝试用getRangeOfArticles方法在JPQL中复制SQL查询：

package net.bounceme.dur.usenet.driver;
import java.util.List;
import java.util.logging.Logger;
import javax.mail.Folder;
import javax.mail.Message;
import javax.persistence.*;
import net.bounceme.dur.usenet.model.Article;
import net.bounceme.dur.usenet.model.Newsgroup;
class DatabaseUtils {
    private static final Logger LOG = Logger.getLogger(DatabaseUtils.class.getName());
    private EntityManagerFactory emf = Persistence.createEntityManagerFactory("USENETPU");
    private EntityManager em = emf.createEntityManager();
    //SELECT MAX(MESSAGENUMBER) FROM articles LEFT OUTER JOIN newsgroups ON articles.NEWSGROUP_ID=newsgroups.ID  WHERE newsgroups.NEWSGROUP = "gwene.com.economist";
    public int getMaxMessageNumber(Folder folder) {
        int maxMessageNumber = 0;
        String newsgroup = folder.getFullName();
        String queryString = "select max(article.messageNumber) from Article article left join article.newsgroup newsgroup where newsgroup.newsgroup = '" + newsgroup + "'";
        try {
            maxMessageNumber = (Integer) em.createQuery(queryString).getSingleResult();
        } catch (Exception e) {
            LOG.severe("setting max to zero for " + newsgroup);
        }
        LOG.fine(folder.getFullName() + "t" + maxMessageNumber);
        return maxMessageNumber;
    }
    //SELECT * FROM articles LEFT OUTER JOIN newsgroups ON articles.NEWSGROUP_ID=newsgroups.ID  WHERE newsgroups.NEWSGROUP = "gwene.com.economist" AND articles.ID BETWEEN 450 AND 500;   
    public List<Article> getRangeOfArticles(Page page) {
        String fullNewsgroupName = page.getFolder().getFullName();
        int minRange = page.getMin();
        int maxRange = page.getMax();
        String queryString = "select article from Article article left join article.newsgroup newsgroup where newsgroup.newsgroup = :newsGroupParam";
        TypedQuery<Article> query = em.createQuery(queryString, Article.class);
        query.setParameter("newsGroupParam", fullNewsgroupName);
        List<Article> results = query.getResultList();
        LOG.info(results.toString());
        return results;
    }
    public void persistArticle(Message message, Folder folder) {
        em.getTransaction().begin();
        String fullNewsgroupName = folder.getFullName();
        Newsgroup newsgroup = null;
        TypedQuery<Newsgroup> query = em.createQuery("SELECT n FROM Newsgroup n WHERE n.newsgroup = :newsGroupParam", Newsgroup.class);
        query.setParameter("newsGroupParam", fullNewsgroupName);
        try {
            newsgroup = query.getSingleResult();
            LOG.fine("found " + query.getSingleResult());
        } catch (javax.persistence.NoResultException e) {
            LOG.fine(e + "ncould not find " + fullNewsgroupName);
            newsgroup = new Newsgroup(folder);
            em.persist(newsgroup);
        } catch (NonUniqueResultException e) {
            LOG.warning("nshould never happent" + fullNewsgroupName);
        } /*
         * finally { if (em.getTransaction().isActive()) {
         * em.getTransaction().rollback(); }
         */
        Article article = new Article(message, newsgroup);
        em.persist(article);
        em.getTransaction().commit();
    }
    public void close() {
        em.close();
        emf.close();//necessary?
    }
}

Answer 1

如果您想通过Newsgroup的名称查询Article的集合，可以执行以下操作：

String queryString = "select a from Article a where a.newsgroup.newsgroup = :newsGroupParam";
TypedQuery<Article> query = em.createQuery(queryString, Article.class);
query.setParameter("newsGroupParam", fullNewsgroupName);
List<Article> results = query.getResultList();

不要在JPQL中编写联接，让JPA为您做这项工作。

作为一个额外的提示，您应该始终这样编写查询，以防止SQL注入。

public int getMaxMessageNumber(Folder folder) {
    int maxMessageNumber = 0;
    String queryString = "select max(a.messageNumber) from Article a where a.newsgroup.newsgroup = :newsgroup";
    Query q = em.createQuery(queryString);
    q.setParameter("newsgroup", folder.getFullName());
    try {
        maxMessageNumber = (Integer) q.getSingleResult();
    } catch (Exception e) {
        LOG.severe("setting max to zero for " + newsgroup);
    }
    LOG.fine(folder.getFullName() + "t" + maxMessageNumber);
    return maxMessageNumber;
}

Answer 2

就查询而言，我相信它是String queryString = "select article from Article article left join article.newsgroup newsgroup where newsgroup.newsgroup = :newsGroupParam and article.messageNumber between 100 and 500";，尽管我需要在其中添加一些范围参数。见下文getRangeOfArticles：

package net.bounceme.dur.usenet.driver;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.mail.Folder;
import javax.mail.Message;
import javax.mail.MessagingException;
import javax.persistence.*;
import net.bounceme.dur.usenet.model.Article;
import net.bounceme.dur.usenet.model.Newsgroup;
import net.bounceme.dur.usenet.model.Usenet;
class DatabaseUtils {
    private static final Logger LOG = Logger.getLogger(DatabaseUtils.class.getName());
    private EntityManagerFactory emf = Persistence.createEntityManagerFactory("USENETPU");
    private EntityManager em = emf.createEntityManager();
    //SELECT MAX(MESSAGENUMBER) FROM articles LEFT OUTER JOIN newsgroups ON articles.NEWSGROUP_ID=newsgroups.ID  WHERE newsgroups.NEWSGROUP = "gwene.com.economist";
    public int getMaxMessageNumber(Folder folder) {
        int maxMessageNumber = 0;
        String newsgroup = folder.getFullName();
        String queryString = "select max(article.messageNumber) from Article article left join article.newsgroup newsgroup where newsgroup.newsgroup = '" + newsgroup + "'";
        try {
            maxMessageNumber = (Integer) em.createQuery(queryString).getSingleResult();
        } catch (Exception e) {
            LOG.severe("setting max to zero for " + newsgroup);
        }
        LOG.fine(folder.getFullName() + "t" + maxMessageNumber);
        return maxMessageNumber;
    }
    //SELECT * FROM articles LEFT OUTER JOIN newsgroups ON articles.NEWSGROUP_ID=newsgroups.ID  WHERE newsgroups.NEWSGROUP = "gwene.com.economist" AND articles.ID BETWEEN 450 AND 500;   
    public List<Article> getRangeOfArticles(Page page) {
        String fullNewsgroupName = page.getFolder().getFullName();
        int minRange = page.getMin();
        int maxRange = page.getMax();
        String queryString = "select article from Article article left join article.newsgroup newsgroup where newsgroup.newsgroup = :newsGroupParam and article.messageNumber between 100 and 500";
        TypedQuery<Article> query = em.createQuery(queryString, Article.class);
        query.setParameter("newsGroupParam", fullNewsgroupName);
        List<Article> articles = query.getResultList();
        Usenet usenet = Usenet.INSTANCE;
        for (Article article : articles) {
            List<String> string = new ArrayList<>();
            string.add(article.getId().toString());
            string.add(Long.toString(article.getMessageNumber()));
            Message message = usenet.getMessage(page.getFolder(), article.getMessageNumber());
            try {
                string.add(message.getSubject());
            } catch (MessagingException ex) {
                Logger.getLogger(DatabaseUtils.class.getName()).log(Level.SEVERE, null, ex);
            }
            Newsgroup n = article.getNewsgroup();
            string.add(n.getNewsgroup());
            LOG.info(string.toString());
        }
        return articles;
    }
    public void persistArticle(Message message, Folder folder) {
        em.getTransaction().begin();
        String fullNewsgroupName = folder.getFullName();
        Newsgroup newsgroup = null;
        TypedQuery<Newsgroup> query = em.createQuery("SELECT n FROM Newsgroup n WHERE n.newsgroup = :newsGroupParam", Newsgroup.class);
        query.setParameter("newsGroupParam", fullNewsgroupName);
        try {
            newsgroup = query.getSingleResult();
            LOG.fine("found " + query.getSingleResult());
        } catch (javax.persistence.NoResultException e) {
            LOG.fine(e + "ncould not find " + fullNewsgroupName);
            newsgroup = new Newsgroup(folder);
            em.persist(newsgroup);
        } catch (NonUniqueResultException e) {
            LOG.warning("nshould never happent" + fullNewsgroupName);
        } /*
         * finally { if (em.getTransaction().isActive()) {
         * em.getTransaction().rollback(); }
         */
        Article article = new Article(message, newsgroup);
        em.persist(article);
        em.getTransaction().commit();
    }
    public void close() {
        em.close();
        emf.close();//necessary?
    }
}

然而，我似乎遇到了一些错误：

Aug 06, 2012 12:00:02 AM net.bounceme.dur.usenet.model.Usenet getMessage
SEVERE: null
javax.mail.MessageRemovedException: No such article: 273

所以，也许查询语法或含义有错误，或者我有逻辑错误。