Java >> Java tutorial >  >> Tag >> hibernate

Hibernate 6 og JPQL vinduesfunktioner

Introduktion

I denne artikel vil jeg vise dig, hvordan du kan bruge Hibernate 6 til at skrive JPQL-forespørgsler, der bruger SQL-vinduefunktioner.

Denne funktion er blevet tilføjet i Hibernate 6, som giver en ny forespørgselsparser kaldet SQM (Semantic Query Model), som er mere kraftfuld end de tidligere Hibernate HQL-forespørgselsfunktioner.

SQL-vinduefunktioner

Som jeg forklarede i denne artikel, er vinduesfunktioner ekstremt kraftfulde, da de giver os mulighed for at anvende aggregeringsfunktioner på et givet sæt poster uden at skulle reducere resultatsættet til en enkelt række pr. partition, som det er tilfældet for GROUP BY klausul.

Lad os for eksempel antage, at vi har følgende databasetabeller:

account er den overordnede tabel og account_transaction er den underordnede tabel, da den har en account_id Foreign Key-kolonnen, der refererer til id Primær nøgle for account tabel.

account tabellen har to poster knyttet til Alice og Bob:

| id | iban            | owner       |
|----|-----------------|-------------|
| 1  | 123-456-789-010 | Alice Smith |
| 2  | 123-456-789-101 | Bob Johnson |

Og account_transaction indeholder transaktioner, der tilhører både Alice og Bob:

| id | amount | created_on          | account_id |
|----|--------|---------------------|------------|
| 1  | 2560   | 2019-10-13 12:23:00 | 1          |
| 2  | -200   | 2019-10-14 13:23:00 | 1          |
| 3  | 500    | 2019-10-14 15:45:00 | 1          |
| 4  | -1850  | 2019-10-15 10:15:00 | 1          |
| 5  | 2560   | 2019-10-13 15:23:00 | 2          |
| 6  | 300    | 2019-10-14 11:23:00 | 2          |
| 7  | -500   | 2019-10-14 14:45:00 | 2          |
| 8  | -150   | 2019-10-15 10:15:00 | 2          |

Vi er nu interesseret i en opgørelsesrapport, der indeholder følgende kolonner:

  • indtastningsnummeret for hver kontoudskriftspost i forhold til hver konto
  • transaktions-id'et
  • konto-id'et
  • transaktionens tidsstempel
  • transaktionsbeløbet
  • kontosaldoen på det tidspunkt, hvor transaktionen fandt sted

For at få denne rapport skal vi udføre følgende SQL-forespørgsel:

SELECT
   ROW_NUMBER() OVER(
      PARTITION BY account_id
      ORDER BY created_on, id
   ) AS nr,
   id,
   account_id,
   created_on,
   amount,
   SUM(amount) OVER(
       PARTITION BY account_id
       ORDER BY created_on, id
   ) AS balance
FROM account_transaction
ORDER BY id

Denne SQL-forespørgsel bruger to vinduesfunktioner:

ROW_NUMBER funktion fungerer som følger:

Først vil det partitionere forespørgselsresultatet, der er indstillet af account_id , derfor opdeles resultatsættene i to grupper, da vi har transaktioner, der kun tilhører to individuelle konti:

ROW_NUMBER() OVER(
  PARTITION BY account_id
  ORDER BY created_on, id
) AS nr,

For det andet vil den sortere hver partition kronologisk:

ROW_NUMBER() OVER(
  PARTITION BY account_id
  ORDER BY created_on, id
) AS nr,

Grunden til at vi bruger både created_on og id kolonner ved sortering er for at forhindre tilfældet, når to transaktioner registreres på samme tidspunkt. Ved at bruge id , som også er monotont stigende, sørger vi for, at den aktuelle ramme spænder fra den allerførste partitionspost til den aktuelle behandlingsrække.

Når posterne er opdelt og sorteret, vises ROW_NUMBER tildeler fortløbende numre til hver post. Bemærk, at nummereringen nulstilles, når der skiftes til en ny partition.

SUM funktion bruger den samme partitionerings- og sorteringslogik:

SUM(amount) OVER(
  PARTITION BY account_id
  ORDER BY created_on, id
) AS nr,

Som allerede forklaret spænder standardrammen, hvorpå vinduesfunktionen anvendes, fra den allerførste post i den aktuelle partition op til det aktuelle behandlingselement. Af denne grund er SUM funktion vil producere en løbende total.

Så SQL-forespørgslen, der producerer den rapport, vi er interesseret i, ser således ud:

SELECT
   ROW_NUMBER() OVER(
      PARTITION BY account_id
      ORDER BY created_on, id
   ) AS nr,
   id,
   account_id,
   created_on,
   amount,
   SUM(amount) OVER(       
       PARTITION BY account_id
       ORDER BY created_on, id  
   ) AS balance
FROM account_transaction
ORDER BY id

Og når vi udfører denne forespørgsel, får vi følgende resultat:

| nr | id | account_id | created_on                 | amount | balance |
|----|----|------------|----------------------------|--------|---------|
| 1  | 1  | 1          | 2019-10-13 12:23:00.000000 | 2560   | 2560    |
| 2  | 2  | 1          | 2019-10-14 13:23:00.000000 | -200   | 2360    |
| 3  | 3  | 1          | 2019-10-14 15:45:00.000000 | 500    | 2860    |
| 4  | 4  | 1          | 2019-10-15 10:15:00.000000 | -1850  | 1010    |
| 1  | 5  | 2          | 2019-10-13 15:23:00.000000 | 2560   | 2560    |
| 2  | 6  | 2          | 2019-10-14 11:23:00.000000 | 300    | 2860    |
| 3  | 7  | 2          | 2019-10-14 14:45:00.000000 | -500   | 2360    |
| 4  | 8  | 2          | 2019-10-15 10:15:00.000000 | -150   | 2210    |

Dvale JPQL med vinduesfunktioner

Før Hibernate 6 var den eneste måde at bruge Windows-funktioner på med entitetsforespørgsler via Blaze Persistence. Da Hibernate 6 giver en ny semantisk forespørgselsmodel, er entitetsforespørgselssproget meget mere kraftfuldt, end det plejede at være.

Af denne grund kan du med Hibernate 6 nu udføre følgende JPQL-forespørgsel:

List<StatementRecord> records = entityManager.createQuery("""
    SELECT
       ROW_NUMBER() OVER(       
           PARTITION BY at.account.id
           ORDER BY at.createdOn   
       ) AS nr,
       at,
       SUM(at.amount) OVER(       
           PARTITION BY at.account.id
           ORDER BY at.createdOn   
       ) AS balance
    FROM AccountTransaction at
    ORDER BY at.id
    """, StatementRecord.class)
.unwrap(Query.class)
.setTupleTransformer((Object[] tuple, String[] aliases) -> 
    new StatementRecord(
        longValue(tuple[0]),
        (AccountTransaction) tuple[1],
        longValue(tuple[2])
    )
)
.getResultList();

assertEquals(8, records.size());

StatementRecord record1 = records.get(0);
assertEquals(
    1L, 
    record1.nr().longValue()
);
assertEquals(
    1L, 
    record1.transaction().getId().longValue()
);
assertEquals(
    1L, 
    record1.transaction().getAccount().getId().longValue()
);
assertEquals(
    2560L, record1.balance().longValue()
);

Fordi dette er en projektionsforespørgsel, bruger vi den nye TupleTransformer for at returnere en liste over StatementRecord objekter, der har følgende struktur:

public record StatementRecord(
    Long nr,
    AccountTransaction transaction,
    Long balance
) {}

Når du kører ovenstående JPQL-forespørgsel, udfører Hibernate 6 følgende SQL-forespørgsel:

SELECT 
    ROW_NUMBER() OVER(
        PARTITION BY a1_0.account_id
        ORDER BY a1_0.created_on
    ),
    a1_0.id,
    a1_0.account_id,
    a1_0.amount,
    a1_0.created_on,
    SUM(a1_0.amount) OVER(
        PARTITION BY a1_0.account_id
        ORDER BY a1_0.created_on
    )
FROM 
    account_transaction a1_0
ORDER BY 
    a1_0.id

Bemærk, at i modsætning til i Hibernate 5, 4 eller 3, bruger den genererede SQL-forespørgsel ikke yderligere aliaser til projektionen siden JDBC ResultSet læses af kolonneindeks, ikke af aliasnavne, hvilket også giver bedre ydeevne.

Fantastisk, ikke?

Konklusion

Hibernate 6 giver mange fordele, og at have understøttelse af Window Functions giver os mulighed for at skrive projektioner, der kombinerer både enheder og aggregerede data.

Denne nye Hibernate-version er ret revolutionerende, og der er mange flere forespørgselsfunktioner, som snart vil blive implementeret takket være den nye semantiske forespørgselsmodel, der bruges af alle JPQL- og Criteria-entitetsforespørgsler.


No
Java tag