Запрос объекта гибернации для поиска последней, полу-уникальной строки в одной таблице

Ответ 1

Во-первых, используйте запрос агрегации, чтобы получить дату последней покупки для комбинации продукт + покупатель.

Используйте этот запрос как подвыбор, соответствующий кортежам:

from Puchases p 
where (p.PRODUCT_NAME, p1.PURCHASER_NAME, p1.PURCHASE_DATE) in
    (select PRODUCT_NAME, PURCHASER_NAME , max(PURCHASE_DATE) 
     from Purchases 
     where 
        PRODUCT_NAME in :productNames and 
        PURCHASER_NAME in :purchaserNames 
     group by PRODUCT_NAME, PURCHASER_NAME)

Должна быть возможность реализовать то же самое с использованием API критериев, используя Subqueries.propertiesIn.

См. Запрос критериев гибернации для нескольких столбцов с предложением IN и вложенным выбором

.Если ваш PURCHASE_ID гарантированно является "хронологически возрастающим", тогда вы можете просто использовать max (PURCHASE_ID) в подвыборке.

Ответ 2

Вы можете создавать временные переменные и таблицы с помощью @. Я не уверен, как создать массив.

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

Это должно получить самую последнюю дату покупки для каждой комбинации cust/prod

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

Если вам нужен круг назад и получить идентификатор, вы можете присоединиться к покупкам, чтобы получить их

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

Обратите внимание на "с (nolock)" после имен таблиц. что может помочь в производительности.

Ответ 3

Хорошо, поскольку сначала я создал запрос, который извлекает только запрошенные записи:

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 и преобразовать его в bean-компонент с .setResultTransformer(Transformers.aliasToBean(Purchase.class)). Обратите внимание, что я назвал 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();

Ofc, что сейчас отсутствует, это передать ваши параметры, такие как Notebook или Bob в методе, в который вы будете вставлять этот код. Я бы построил вспомогательный метод, который записывает условие на основе того, насколько велик список параметров.

Поскольку у меня нет спящего режима, связанного с базой данных, я освободил код, некоторые исправления могут потребоваться, но общая идея должна сделать трюк.

Выполнение этого без SQLQuery, imho, гораздо труднее и труднее читать: вам нужно сохранить целостность результата в вашем компоненте, и именно этого вы и достигнете.

Ответ 4

Обновить

Чтобы использовать критерии спящего режима, вы можете попробовать подзапрос:

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();

Другой способ - использовать собственный 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 (поскольку для критериев требуется путь/сопоставление между сущностями)

Ответ 5

Привет, я предлагаю вам очень простое решение на основе 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

Когда у вас есть идентификаторы записей, вы можете выбрать Actual products by ID.

Теперь вы подумали бы, что этот запрос вернет вам всю таблицу. Не будет. Самая современная база данных, поддерживающая курсоры на стороне сервера, вернет вам только указанное количество записей, которые вы указали.

После инициализации запроса следующий шаг - это указать, сколько результатов вы хотите вернуть:

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

Это правильно использовало этот запрос, который не вернет вам полную таблицу! Он вернет столько, сколько сказано.

Ответ 6

Попробуйте использовать этот код.

    SessionFactory sessFact = HibernateUtil.getSessionFactory();
    Session session = sessFact.openSession();
    Criteria criteria = session.createCriteria(Purchase.class);
    ProjectionList projList = Projections.projectionList();

    projList.add(Projections.groupProperty("purchaserName"));
    projList.add(Projections.groupProperty("productName"));
    projList.add(Projections.property("purchaseId"));
    projList.add(Projections.property("productName"));
    projList.add(Projections.max("purchaseDate"));
    projList.add(Projections.property("purchaserName"));

    criteria.setProjection(projList);

    List<String> productList = new ArrayList<String>() {
        {
            add("Notebook");
            add("Pencil");
        }
    };
    List<String> purchaserList = new ArrayList<String>() {
        {
            add("Bob");
            add("Steve");
        }
    };

    Disjunction prod = Restrictions.disjunction();
    prod.add(Restrictions.in("productName", productList));

    Disjunction purch = Restrictions.disjunction();
    purch.add(Restrictions.in("purchaserName", purchaserList));

    criteria.add(Restrictions.and(prod, purch));
    List resultList = criteria.list();

SQL в результате (<property name="show_sql">true</property>)

select this_.PURCHASER_NAME as y0_, this_.PRODUCT_NAME as y1_, this_.PURCHASE_ID as y2_, this_.PRODUCT_NAME as y3_, max(this_.PURCHASE_DATE) as y4_, this_.PURCHASER_NAME as y5_ from purchase this_ where ((this_.PRODUCT_NAME in (?, ?)) and (this_.PURCHASER_NAME in (?, ?))) group by this_.PURCHASER_NAME, this_.PRODUCT_NAME

Ответ 7

На мой взгляд, хитрость заключается в том, чтобы увидеть, что "дать мне новейшую" эквивалентно "дать строки без новых покупок". Это переводит на этот вид запроса:

-- This is SQL
-- Note that if two purchases have exactly the same date, this query will
-- return both; you can fine tune the condition inside the exists clause
-- to avoid this
select *
from purchases p1
where
p1.product_name in ('Notebook', 'Pencil') and
p1.purchaser_name in ('Bob', 'Steve') and
not exists (
   select p2.purchase_id
   from purchases p2
   where
   p2.product_name = p1.product_name and
   p2.purchaser_name = p1.purchaser_name and
   p2.purchase_date > p1.purchase_date
)
order by purchase_id;

Хотя это SQL, перевод на HQL должен быть довольно простым, и этого может быть достаточно для вас. Прошло много времени с тех пор, как я использовал критерии Hibernate (в наши дни вы используете API JPA), но это должно быть нечто похожее на это:

DetachedCriteria criteria = DetachedCriteria.forClass(Purchase.class, "p1");
// add here your filters to criteria
// criteria.add(purcharserName in (....));
// criteria.add(productName in (....));
// this appends the not exists clause
DetachedCriteria notExistsCriteria = DetachedCriteria.forClass(Purchase.class, "p2");
notExistsCriteria.add(Restrictions.eqProperty("p2.productName", "p1.productName"));
notExistsCriteria.add(Restrictions.eqProperty("p2.purchaserName", "p1.purchaserName"));
notExistsCriteria.add(Restrictions.gtProperty("p2.purchaseDate", "p1.purchaseDate"));

criteria.add(Subqueries.notExists(notExistsCriteria.setProjection(Projections.property("p1.id"))));

List<Purchase> results = // issue Criteria query

ОБНОВИТЬ:

Я вижу, что Hibernate Criteria поддерживает оператор SQL ALL, поэтому, если ваша база данных поддерживает его, вы также можете написать следующее:

DetachedCriteria criteria = DetachedCriteria.forClass(Purchase.class, "p1");
// add here your filters to criteria
// criteria.add(purcharserName in (....));
// criteria.add(productName in (....));

// this appends the p1.purchaseDate > all (...) filter
DetachedCriteria allCriteria = DetachedCriteria.forClass(Purchase.class, "p2");
allCriteria.add(Restrictions.eqProperty("p2.productName", "p1.productName"));
allCriteria.add(Restrictions.eqProperty("p2.purchaserName", "p1.purchaserName"));

criteria.add(Subqueries.propertyGeAll("p1.purchaseDate", allCriteria.setProjection(Projections.property("p2.purchaseDate"))));

List<Purchase> results = // issue Criteria query

который читается несколько более четко.

Ответ 8

Попробуйте использовать следующий HQL, предполагая, что у вас есть поле auto incrementing id.

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)

Ответ 9

Решение заключается в том, чтобы сначала получить имя productname, buyeraserName и max (buyDate) с помощью productName, имя покупателя, используя отдельные критерии. Это приведет к тому, что мы будем идентифицировать уникальную строку, используя эти три атрибута. Но здесь есть один улов, если один и тот же покупатель купил один и тот же продукт более одного раза в тот же день, тогда мы не сможем идентифицировать уникальную строку, используя вышеуказанное условие, и это приведет к появлению нескольких записей, полученных из БД. Чтобы решить эту проблему, вам необходимо использовать тип даты и времени для поля purchaseDate в БД. Теперь используйте эти атрибуты из отдельных критериев в запросе 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)

Ответ 10

List<Purchase> findByProductNameInAndPurchaserNameInAndPurchaseDateBefore(List<String> productNames, List<String> purchaserNames, Date before);

Я не вижу объект Purchase, но, конечно, если Product сопоставляется как сущность, этот запрос должен быть соответствующим образом обновлен.

ПРИМЕЧАНИЕ. Я бы предложил вам прочитать о полнотекстовом поиске или поиске Hibernate, потому что если у вас будет много запросов, подобных этому, похоже, вам понадобится поддержка полного текстового поиска в вашем проекте.