java - サブクエリ - spring data jpa




単一のテーブルで最新の半固有の行を見つけるためのHibernateエンティティクエリ (6)

@を使用して一時変数とテーブルを作成できます。 配列の作り方がよくわかりません。

declare @product1 = 'Pencil'
declare @product2 = 'Notebook'
declare @purchaser_name1 = 'Bob'
declare @purchaser_name2= 'Steve'

これにより、各顧客/商品の組み合わせの最新の購入日が取得されます。

select 
product_name, purchaser_name, max(purchase_date) as max_purchase_date
into @temp
from purchases with(nolock) where 
product_name in (@product1,@product2) and
purchaser_name in (@purchaser_name1,@purchaser_name2)
group by product_name, purchaser_name

サークルを戻してIDを取得する必要がある場合は、購入に戻ってそれらを取得することができます。

select p.* from purchases p with(nolock) 
inner join @temp t 
on p.product_name = t.product_name
and p.purchaser_name = t.purchaser_name
and p.purchase_date = t.max_purchase_date

テーブル名の後の "with(nolock)"に注意してください。 それはパフォーマンスに役立つかもしれません。

私はHibernateデータベースに単一のテーブルがあるようにしています:

PURCHASE_ID | PRODUCT_NAME | PURCHASE_DATE | PURCHASER_NAME | PRODUCT_CATEGORY
------------------------------------------------------------------------------
     1          Notebook      09-07-2018          Bob            Supplies
     2          Notebook      09-06-2018          Bob            Supplies
     3           Pencil       09-06-2018          Bob            Supplies
     4            Tape        09-10-2018          Bob            Supplies
     5           Pencil       09-09-2018         Steve           Supplies
     6           Pencil       09-06-2018         Steve           Supplies
     7           Pencil       09-08-2018         Allen           Supplies

そして、私は他のいくつかの制限に基づいて、最新の購入品だけを返したいです。 例えば:

List<Purchase> getNewestPurchasesFor(Array<String> productNames, Array<String> purchaserNames) { ... }

を使用して呼び出すことができます:

List<Purchase> purchases = getNewestPurchasesFor(["Notebook", "Pencil"], ["Bob", "Steve"]);

英語では、「BobまたはSteveのどちらかによって、NotebookまたはPencilのいずれかで最新の購入をお願いします。」

そして提供します:

PURCHASE_ID | PRODUCT_NAME | PURCHASE_DATE | PURCHASER_NAME
-----------------------------------------------------------
     1          Notebook      09-07-2018          Bob            
     3           Pencil       09-06-2018          Bob            
     5           Pencil       09-09-2018         Steve           

つまり、複数の列に対する "明確な"検索、または並べ替え後の結合れた列の一意のキーに基づく "制限"のようなものですが、これまでに示した例ではSELECT DISTINCT(PRODUCT_NAME, PURCHASER_NAME)使用して説明しました。列のみですが、フォーマットを使用する必要があります。

from Purchases as entity where ...

モデルタイプが関係をそのまま残して返されるように。

現在、私のクエリは私にすべての古い購入品も返します。

PURCHASE_ID | PRODUCT_NAME | PURCHASE_DATE | PURCHASER_NAME | PRODUCT_CATEGORY
------------------------------------------------------------------------------
     1          Notebook      09-07-2018          Bob            Supplies
     2          Notebook      09-06-2018          Bob            Supplies
     3           Pencil       09-06-2018          Bob            Supplies
     5           Pencil       09-09-2018         Steve           Supplies
     6           Pencil       09-06-2018         Steve           Supplies

繰り返し購入すると、パフォーマンスが大幅に低下します。

これを達成するために使用すべき特別なキーワードはありますか? クエリ言語とSQL-fuは私の強みではありません。

編集する

現在Criteria APIを使用しています。今後も引き続き使用してください。

Criteria criteria = session.createCriteria(Purchase.class);
criteria.addOrder(Order.desc("purchaseDate"));
// Product names
Criterion purchaseNameCriterion = Restrictions.or(productNames.stream().map(name -> Restrictions.eq("productName", name)).toArray(Criterion[]::new));
// Purchaser
Criterion purchaserCriterion = Restrictions.or(purchaserNames.stream().map(name -> Restrictions.eq("purchaser", name)).toArray(Criterion[]::new));
// Bundle the two together
criteria.add(Restrictions.and(purchaseNameCriterion, purchaserCriterion));

criteria.list(); // Gives the above results

異なる投影法を使用しようとすると、エラーが発生します。

ProjectionList projections = Projections.projectionList();
projections.add(Projections.property("productName"));
projections.add(Projections.property("purchaser"));
criteria.setProjection(Projections.distinct(projections));

の結果:

17:08:39 ERROR Order by expression "THIS_.PURCHASE_DATE" must be in the result list in this case; SQL statement:

上記のように、射影/特殊列セットを追加すると、 それらの列を結果/戻り値として使用したいことがHibernateに示されるように思われるため、返されるモデルオブジェクトを一意の列値に基づいて単純に制限します。


更新

休止条件を使用するには、サブクエリアプローチを試すことができます。

DetachedCriteria subQuery = DetachedCriteria.forClass(Purchase.class, "p2");

ProjectionList groupBy = Projections.projectionList();
groupBy.add(Projections.max("purchaseDate"));
groupBy.add(Projections.groupProperty("productName"));
groupBy.add(Projections.groupProperty("purchaserName"));
subQuery.setProjection(groupBy);

subQuery.add(Restrictions.in("productName", productNames));
subQuery.add(Restrictions.in("purchaserName", purchaserName));

Criteria purchase = session.createCriteria(Purchase.class, "p1");
purchase.add(Subqueries.propertiesIn(new String[] {"purchaseDate", "productName", "purchaserName"}, subQuery));
purchase.addOrder(Order.desc("purchaseDate"));

List<Purchase> p1 = purchase.list();

もう1つの方法は、ネイティブSQLを使用することです。

SELECT p1.*
FROM purchase p1 LEFT JOIN purchase p2
  ON (p1.purchaser_name = p2.purchaser_name 
      AND p1.product_name = p2.product_name 
      AND p1.purchase_date < p2.purchase_date)
WHERE p2.id IS NULL 
      AND p1.product_name IN ("Notebook", "Pencil") 
      AND p1.purchaser_name IN ("Bob", "Steve")
ORDER BY p1.product_name DESC

このSQLを使用すると、副問合せアプローチと比較してパフォーマンスが大幅に向上します。

ただし、Hibernate Criteriaに変換できないようです(Criteriaにはエンティティ間のパス/マッピングが必要です)。


こんにちは私はあなたの周りにあまりにも多くの魔法なしであなたに1つの非常に単純なHQLベースの解決策を提供するでしょう。 解決策は次のHQLクエリです。

select p.id, max(p.date) from Purchase p where p.productName in('notebook','pencil') and p.purchaseName in ('ob', 'Steve') group by p.productName ,p.purchaseName

レコードのIDを取得したら、IDで実際の商品を選択できます。

これで、このクエリはテーブル全体を返すと思います。 そうではないでしょう。 サーバーサイドカーソルをサポートする最新のデータベースのほとんどは、指定したレコード数だけを返します。

Queryを初期化したら、次のステップはそれにいくつの結果を返させたいかを伝えることです。

    Query query = query.setMaxResults(1)
    query.setFetchSize();
    query.scroll(ScrollMode.FORWARD_ONLY);
    // here is a hint for MySQL
    query.setMaxResults(100)

これを正しく使用すると、このクエリは完全なテーブルを返しません。 それは言われるのと同じくらい戻ってくるでしょう。


さて、最初に私は要求されたレコードだけを抽出するクエリを構築しました:

select p1.* from Purchase p1

  join (
    select 
        max(PURCHASE_DATE) as maxdate, 
        purchaser_name, 
        PRODUCT_NAME from Purchase 
    where 
        (product_name ='Notebook' or product_name = 'Pencil') 
        and purchaser_name in ('Bob','Steve')
    group by 
        purchaser_name, 
        PRODUCT_NAME) p2

  on p1.PURCHASE_DATE = p2.maxDate
  and p1.PRODUCT_NAME = p2.PRODUCT_NAME
  and p1.PURCHASER_NAME = p2.PURCHASER_NAME;

出力として与えた

PURCHASE_ID PRODUCT_NAME    PURCHASE_DATE             PURCHASER_NAME    PRODUCT_CATEGORY
1           Notebook        2018-07-09 00:00:00.000   Bob               Supplies
3           Pencil          2018-06-09 00:00:00.000   Bob               Supplies
5           Pencil          2018-09-09 00:00:00.000   Steve             Supplies

これで、そのクエリをSQLQueryで変換し、それを.setResultTransformer(Transformers.aliasToBean(Purchase.class))を使用してBeanに変換できます。 私はyourSessionセッションと名付けyourSessionた、それに応じてそれを変更してください:

List<Purchase> list = yourSession.createSQLQuery(
          "select p1.* from Purchase p1 "
        + " join ( "
        + "     select "
        + "         max(PURCHASE_DATE) as maxdate, "
        + "         purchaser_name, "
        + "         PRODUCT_NAME from Purchase "
        + "     where "
        + "         (product_name ='Notebook' or product_name = 'Pencil') " //this must be created dinamically based on your parameters
        + "         and purchaser_name in ('Bob','Steve') " //and this too
        + "     group by "
        + "         purchaser_name, "
        + "         PRODUCT_NAME) p2 "

        + " on p1.PURCHASE_DATE = p2.maxDate "
        + " and p1.PRODUCT_NAME = p2.PRODUCT_NAME "
        + " and p1.PURCHASER_NAME = p2.PURCHASER_NAME ")
        .setResultTransformer(Transformers.aliasToBean(Purchase.class))
        .list();

現在欠けているのは、 NotebookBobなどのパラメータをこのコードをラップするメソッドに渡すことです。 パラメータリストの大きさに基づいて条件を記述するヘルパーメソッドを作成します。

私はデータベースに接続した休止状態ではないので、コードを自由に手に入れたので、多少の修正が必要になるかもしれませんが、一般的な考え方でうまくいくはずです。

SQLQueryなしでそれを行うのは、いや、はるかに難しく、読むのが難しくなります。必要なのは、結果をBeanにそのまま残すことです。これが、これによって達成されることです。


自動インクリメントIDフィールドがあると仮定して、次のHQLを試してください。

FROM Purchase p WHERE p.id IN(SELECT MAX(p1.id) FROM Purchase p1 WHERE p1.productName IN('Notebook','Pencil') AND p1.purchaseName IN('Bob', 'Steve') GROUP BY p1.productName, p1.purchaseName)

解決策は、最初に、独立した基準を使用してproductName、PurchaserName、およびmax(purchaseDate)グループをproductName、PurchaserNameで取得することです。 これら3つの属性を使用して一意の行を識別することになります。 しかし、同じ購入者が同じ日に同じ商品を複数回購入した場合、ここで1つ注意が必要です。その場合、上記の条件を使用して一意の行を特定できず、複数のレコードがDBから取得されます。 これを解決するには、DBのpurchaseDateフィールドにdatetime型またはtimestamp型を使用する必要があります。 さて、必要な結果を得るためにCriteriaクエリでデタッチされた基準からこれらの属性を使用してください。

DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Purchase.class, "inner");
    detachedCriteria.add(Restrictions.in("inner.productName", new String[] { "Notebook", "Pencil" }));
    detachedCriteria.add(Restrictions.in("inner.purchaserName", new String[] { "Bob", "Steve" }));
    detachedCriteria.setProjection(Projections.projectionList().add(Projections.max("inner.purchaseDate"))
            .add(Projections.groupProperty("inner.productName"))
            .add(Projections.groupProperty("inner.purchaserName")));
    Session session = this.getEntityManager().unwrap(Session.class);
    Criteria criteria = session.createCriteria(Purchase.class, "b");
    ProjectionList projectionList = Projections.projectionList();
    projectionList.add(Projections.property("b.purchaseId"));
    projectionList.add(Projections.property("b.productName"));
    projectionList.add(Projections.property("b.purchaseDate"));
    projectionList.add(Projections.property("b.purchaserName"));
    criteria.setProjection(projectionList);
    criteria.add(Subqueries.propertiesIn(new String[] { "b.purchaseDate", "b.productName", "b.purchaserName" },
            detachedCriteria));
    criteria.list();

この基準クエリはmysqlのクエリの下で起動します。

select this_.purchase_id as y0_, this_.product_name as y1_, this_.purchase_date as y2_, this_.purchaser_name as y3_ from purchase this_ where (this_.purchase_date, this_.product_name, this_.purchaser_name) in (select max(inner_.purchase_date) as y0_, inner_.product_name as y1_, inner_.purchaser_name as y2_ from purchase inner_ where inner_.product_name in (?, ?) and inner_.purchaser_name in (?, ?) group by inner_.product_name, inner_.purchaser_name)




hibernate