Native Queries – Sådan kalder du native SQL-forespørgsler med JPA &Hibernate
Java Persistence Query Language (JPQL) er den mest almindelige måde at forespørge data på fra en database med JPA. Det giver dig mulighed for at genbruge dine kortlægningsdefinitioner og er nemmere at bruge end SQL. Men den understøtter kun en lille delmængde af SQL-standarden, og den understøtter heller ikke databasespecifikke funktioner.
Så hvad skal du gøre, hvis du skal bruge en databasespecifik forespørgselsfunktion, eller din DBA giver dig en meget optimeret forespørgsel, som du ikke kan omdanne til JPQL? Bare ignorere det og gøre alt arbejdet i Java-koden?
Selvfølgelig ikke! JPA har sit eget forespørgselssprog, men det er designet som en utæt abstraktion og understøtter native SQL-forespørgsler. Du kan oprette disse forespørgsler på samme måde som JPQL-forespørgsler, og de kan endda returnere administrerede enheder, hvis du vil.
I denne artikel vil jeg vise dig, hvordan du bruger native SQL-forespørgsler, forskellige muligheder for at kortlægge forespørgselsresultatet til DTO'er og entitetsobjekter og undgå en almindelig præstationsfælde.
Definition og udførelse af en indbygget forespørgsel
Ligesom JPQL-forespørgsler kan du definere din oprindelige SQL-forespørgsel ad hoc eller bruge en annotering til at definere en navngivet indbygget forespørgsel.
Opret ad hoc indbyggede forespørgsler
Det er ret simpelt at oprette en indbygget ad-hoc-forespørgsel. EntityManager grænsefladen giver createNativeQuery metode til det. Det returnerer en implementering af Forespørgslen grænsefladen, som er den samme, som du får, når du kalder createQuery metode til at oprette en JPQL-forespørgsel.
Det følgende kodestykke viser et simpelt eksempel på brug af en indbygget forespørgsel til at vælge for- og efternavne fra forfattertabellen. Jeg ved, at der ikke er behov for at gøre dette med en indbygget SQL-forespørgsel. Jeg kunne bruge en standard JPQL-forespørgsel til dette, men jeg vil fokusere på JPA-delen og ikke genere dig med nogle skøre SQL-ting 😉
Persistensudbyderen analyserer ikke SQL-sætningen, så du kan bruge enhver SQL-sætning, som din database understøtter. For eksempel brugte jeg det i et af mine seneste projekter til at forespørge PostgreSQL-specifikke jsonb kolonner med Hibernate og tilknyttede forespørgselsresultaterne til POJO'er og enheder.
Query q = em.createNativeQuery("SELECT a.firstname, a.lastname FROM Author a");
List<Object[]> authors = q.getResultList();
for (Object[] a : authors) {
System.out.println("Author "
+ a[0]
+ " "
+ a[1]);
}
Som du kan se, kan du bruge den oprettede Forespørgsel på samme måde som enhver JPQL-forespørgsel. Jeg har ikke givet nogen kortoplysninger for resultatet. Derfor er EntityManager returnerer en Liste af Objekt[] som du skal håndtere bagefter. I stedet for selv at kortlægge resultatet kan du også give yderligere kortoplysninger og lade EntityManager lave kortlægningen for dig. Jeg kommer nærmere ind på det i afsnittet om resultathåndtering i slutningen af dette indlæg.
Opret navngivne indbyggede forespørgsler
Du vil ikke blive overrasket, hvis jeg fortæller dig, at definitionen og brugen af en navngivet indbygget forespørgsel igen minder meget om en navngivet JPQL-forespørgsel.
I de tidligere kodestykker oprettede jeg en dynamisk indbygget forespørgsel for at vælge navnene på alle forfattere. Jeg bruger den samme sætning i følgende kodestykke til at definere en @NamedNativeQuery . Siden Hibernate 5 og JPA 2.2 kan denne annotering gentages, og du kan tilføje flere af den til din enhedsklasse. Hvis du bruger en ældre JPA- eller Hibernate-version, skal du pakke den ind i en @NamedNativeQueries annotation.
@NamedNativeQuery(name = "selectAuthorNames",
query = "SELECT a.firstname, a.lastname FROM Author a")
@Entity
public class Author { ... }
Som du kan se, ligner definitionen meget den af en navngivet JPQL-forespørgsel. Som jeg vil vise dig i det følgende afsnit, kan du endda inkludere resultatkortlægningen. Men mere om det senere.
Du kan bruge @NamedNativeQuery på nøjagtig samme måde som en navngivet JPQL-forespørgsel. Du behøver kun at angive navnet på den navngivne indbyggede forespørgsel som en parameter til createNamedQuery metoden for EntityManager .
Query q = em.createNamedQuery("selectAuthorNames");
List<Object[]> authors = q.getResultList();
for (Object[] a : authors) {
System.out.println("Author "
+ a[0]
+ " "
+ a[1]);
}
Parameterbinding
I lighed med JPQL-forespørgsler kan og bør du bruge parameterbindinger til dine forespørgselsparametre i stedet for at sætte værdierne direkte i forespørgselsstrengen. Dette giver flere fordele:
- du behøver ikke bekymre dig om SQL-injektion,
- vedholdenhedsudbyderen kortlægger dine forespørgselsparametre til de korrekte typer og
- vedholdenhedsudbyderen kan foretage interne optimeringer for at forbedre ydeevnen.
JPQL og native SQL-forespørgsler bruger den samme Forespørgsel grænseflade, som giver en setParameter metode til positionelle og navngivne parameterbindinger. Men understøttelsen af navngivne parameterbindinger til indbyggede forespørgsler er en Hibernate-specifik funktion. Positionelle parametre refereres til som "?" i din oprindelige forespørgsel, og deres nummerering starter ved 1.
Følgende kodestykke viser et eksempel på en ad-hoc native SQL-forespørgsel med en positionsbindingsparameter. Du kan bruge bind-parameteren på samme måde i en @NamedNativeQuery .
Query q = em.createNativeQuery("SELECT a.firstname, a.lastname FROM Author a WHERE a.id = ?");
q.setParameter(1, 1);
Object[] author = (Object[]) q.getSingleResult();
System.out.println("Author "
+ author[0]
+ " "
+ author[1]);
Hibernate understøtter også navngivne parameterbindinger til indbyggede forespørgsler, men som jeg allerede har sagt, er dette ikke defineret af specifikationen og kan muligvis ikke overføres til andre JPA-implementeringer.
Ved at bruge navngivne parameterbindinger definerer du et navn for hver parameter og giver det til setParameter metode til at binde en værdi til den. Navnet skelner mellem store og små bogstaver, og du skal tilføje ": ” symbol som et præfiks.
Query q = em.createNativeQuery("SELECT a.firstname, a.lastname FROM Author a WHERE a.id = :id");
q.setParameter("id", 1);
Object[] author = (Object[]) q.getSingleResult();
System.out.println("Author "
+ author[0]
+ " "
+ author[1]);
Resultathåndtering
Som du har set i de foregående kodestykker, returnerer din indbyggede forespørgsel et Objekt[] eller en liste af Objekt[]. Hvis du vil hente dit forespørgselsresultat som en anden datastruktur, skal du give yderligere kortlægningsoplysninger til din persistensudbyder. Der er 3 almindeligt anvendte muligheder:
- Du kan tilknytte hver post af dit forespørgselsresultat til en administreret enhed ved hjælp af enhedens kortlægningsdefinition.
- Du kan bruge JPA's @SqlResultSetMapping annotering til at knytte hver resultatpost til en kombination af DTO'er, administrerede enheder eller skalære værdier.
- Og du kan bruge Hibernates ResultTransformer til at kortlægge hver post eller hele resultatsættet til DTO'er, administrerede enheder eller skalære værdier.
Anvend enhedstilknytningen
Genbrug af tilknytningsdefinitionen af din enhedsklasse er den enkleste måde at tilknytte hver post af forespørgselsresultatet til et administreret enhedsobjekt. Når du gør det, skal du vælge alle kolonner, der er kortlagt af enhedsklassen ved hjælp af det alias, der bruges i din enheds tilknytningsdefinition.
Dernæst skal du fortælle din persistensudbyder, hvilken enhedsklasse den skal kortlægge forespørgselsresultatet til. For en ad-hoc native SQL-forespørgsel gør du det ved at angive en klassereference som en parameter til createNativeQuery metode.
Query q = em.createNativeQuery("SELECT a.id, a.version, a.firstname, a.lastname FROM Author a", Author.class);
List<Author> authors = (List<Author>) q.getResultList();
for (Author a : authors) {
System.out.println("Author "
+ a.getFirstName()
+ " "
+ a.getLastName());
}
Du kan gøre det samme ved at bruge en @NamedNativeQuery ved at henvise til enhedsklassen som @NamedNativeQuery 's resultClass attribut.
@NamedNativeQuery(name = "selectAuthorEntities",
query = "SELECT a.id, a.version, a.firstname, a.lastname FROM Author a",
resultClass = Author.class)
@Entity
public class Author { ... }
Hibernate anvender derefter automatisk denne tilknytning, når du udfører den forespørgsel.
Brug JPA's @SqlResultSetMapping
JPA's @SqlResultSetMapping er meget mere fleksibel end den forrige. Du kan ikke kun bruge det til at kortlægge dit forespørgselsresultat til administrerede enhedsobjekter, men også til DTO'er, skalarværdier og enhver kombination af disse. Den eneste begrænsning er, at Hibernate anvender den definerede tilknytning til hver post i resultatsættet. På grund af det kan du ikke nemt gruppere flere registreringer af dit resultatsæt.
Disse kortlægninger er ret kraftfulde, men deres definition kan blive kompleks. Derfor giver jeg kun en hurtig introduktion i denne artikel. Hvis du vil dykke dybere ned i @SqlResultMapping s, læs venligst følgende artikler:
- Resultatsætkortlægning:Grundlæggende
- Resultatsætkortlægning:komplekse kortlægninger
- Resultatsætkortlægning:Konstruktørresultatkortlægninger
- Mapping af resultatsæt:Dvale specifikke funktioner
Her kan du se et grundlæggende eksempel på en DTO-mapping.
@SqlResultSetMapping(
name = "BookAuthorMapping",
classes = @ConstructorResult(
targetClass = BookAuthor.class,
columns = {
@ColumnResult(name = "id", type = Long.class),
@ColumnResult(name = "firstname"),
@ColumnResult(name = "lastname"),
@ColumnResult(name = "numBooks", type = Long.class)}))
Hver @SqlResultSetMapping skal have et unikt navn i persistensenheden. Du vil bruge det i din kode til at referere til denne kortlægningsdefinition.
@ConstructorResult annotation fortæller Hibernate at kalde konstruktøren af bogforfatteren klasse og angiv resultatsættets id , fornavn , efternavn , og numBooks felter som parametre. Dette giver dig mulighed for at instansiere uadministrerede DTO-objekter, som passer godt til alle skrivebeskyttede operationer.
Efter at have defineret tilknytningen, kan du angive dens navn som den anden parameter til createNativeQuery metode. Hibernate vil derefter slå kortdefinitionen op i den aktuelle persistensenhed og anvende den på hver post i resultatsættet.
Query q = em.createNativeQuery("SELECT a.id, a.firstname, a.lastname, count(b.id) as numBooks FROM Author a JOIN BookAuthor ba on a.id = ba.authorid JOIN Book b ON b.id = ba.bookid GROUP BY a.id",
"BookAuthorMapping");
List<BookAuthor> authors = (List<BookAuthor>) q.getResultList();
for (BookAuthor a : authors) {
System.out.println("Author "
+ a.getFirstName()
+ " "
+ a.getLastName()
+ " wrote "
+ a.getNumBooks()
+ " books.");
}
Og i lighed med de foregående eksempler kan du anvende den samme tilknytning til en @NamedNativeQuery ved at angive navnet på tilknytningen som resultSetMapping attribut.
@NamedNativeQuery(name = "selectAuthorValue",
query = "SELECT a.id, a.firstname, a.lastname, count(b.id) as numBooks FROM Author a JOIN BookAuthor ba on a.id = ba.authorid JOIN Book b ON b.id = ba.bookid GROUP BY a.id",
resultSetMapping = "BookAuthorMapping")
@Entity
public class Author { ... }
Når du har gjort det, kan du udføre din @NamedNativeQuery og Hibernate anvender @SqlResultSetMapping automatisk.
Query q = em.createNamedQuery("selectAuthorValue");
List<BookAuthor> authors = (List<BookAuthor>) q.getResultList();
for (BookAuthor a : authors) {
System.out.println("Author "
+ a.getFirstName()
+ " "
+ a.getLastName()
+ " wrote "
+ a.getNumBooks()
+ " books.");
}
Brug Hibernate-specifik ResultTransformer
ResultTransformer s er en Hibernate-specifik funktion med samme mål som JPA's @SqlResultSetMapping . De giver dig mulighed for at definere en tilpasset kortlægning af resultatsættet af din oprindelige forespørgsel. Men i modsætning til @SqlResultSetMapping , implementerer du denne kortlægning som Java-kode, og du kan kortlægge hver post eller hele resultatsættet.
Følg mig på YouTube for ikke at gå glip af nye videoer.
Hibernate leverer et sæt standardtransformere, og implementeringen af den brugerdefinerede transformer blev meget nemmere i Hibernate 6. Jeg forklarede alt dette meget detaljeret og forskellen mellem Hibernate-versionerne i min guide til ResultTransformer.
Følgende kodestykke viser implementeringen af en TupleTransformer til Hibernate 6. Den anvender samme kortlægning som den tidligere brugte @SqlResultSetMapping .
List<BookAuthor> authors = (List<BookAuthor>) session
.createQuery("SELECT a.id, a.firstname, a.lastname, count(b.id) as numBooks FROM Author a JOIN BookAuthor ba on a.id = ba.authorid JOIN Book b ON b.id = ba.bookid GROUP BY a.id")
.setTupleTransformer((tuple, aliases) -> {
log.info("Transform tuple");
BookAuthor a = new BookAuthor();
a.setId((Long) tuple[0]);
a.setFirstName((String) tuple[1]);
a.setLastName((String) tuple[2]);
a.setNumBooks((Integer) tuple[3]);
return a;
}).getResultList();
for (BookAuthor a : authors) {
System.out.println("Author "
+ a.getFirstName()
+ " "
+ a.getLastName()
+ " wrote "
+ a.getNumBooks()
+ " books.");
}
Som du kan se i kodestykket, kaldte jeg setTupleTransformer metode til at tilføje transformeren til forespørgslen. Det gør transformeren uafhængig af forespørgslen, og du kan anvende den på en @NamedNativeQuery på samme måde.
Definer forespørgselsrummet for at undgå ydeevneproblemer
I begyndelsen af artiklen nævnte jeg, at Hibernate ikke analyserer din oprindelige SQL-sætning. Det giver den fordel, at du ikke er begrænset til de funktioner, som Hibernate understøtter, men at du kan bruge alle funktioner, der understøttes af din database.
Følg mig på YouTube for ikke at gå glip af nye videoer.
Men det gør det også umuligt at bestemme forespørgselsrummet. Forespørgselsrummet beskriver, hvilke entitetsklasser dine forespørgselsreferencer. Hibernate bruger det til at optimere den beskidte kontrol og skylning, som den skal udføre, før forespørgslen udføres. Jeg forklarer dette mere detaljeret i Hibernate Query Spaces – Optimizing Flush and Cache Operations.
Det vigtige, du skal vide, når du bruger native SQL-forespørgsler, er at angive forespørgselsrummet. Du kan gøre det ved at udpakke Hibernates SynchronizeableQuery fra JPA's Forespørgsel grænsefladen og kalder addSynchronizedEntityClass metode med en reference til din enhedsklasse.
Query q = em.createNamedQuery("selectAuthorEntities");
SynchronizeableQuery hq = q.unwrap(SynchronizeableQuery.class);
hq.addSynchronizedEntityClass(Author.class);
List<Author> authors = (List<Author>) q.getResultList();
for (Author a : authors) {
System.out.println("Author "
+ a.getFirstName()
+ " "
+ a.getLastName());
}
Dette fortæller Hibernate, hvilken enhed der klassificerer dine forespørgselsreferencer. Det kan derefter begrænse den beskidte check til objekter af disse entitetsklasser og tømme dem til databasen. Mens du gør det, ignorerer Hibernate alle ændringer på entitetsobjekter i andre enhedsklasser. Dette undgår unødvendige databaseoperationer og giver Hibernate mulighed for at anvende yderligere ydeevneoptimeringer.
Konklusion
JPQL er det mest brugte forespørgselssprog med JPA og Hibernate. Det giver en nem måde at forespørge data fra databasen på. Men den understøtter kun en lille delmængde af SQL-standarden, og den understøtter heller ikke databasespecifikke funktioner. Hvis du vil bruge nogen af disse funktioner, skal du bruge en indbygget SQL-forespørgsel.
Du kan definere en indbygget ad-hoc-forespørgsel ved at ringe til EntityManager 's createNativeQuery metode og angive SQL-sætningen som en parameter. Eller du kan bruge @NamedNativeQuery annotation for at definere en navngivet forespørgsel, som du kan udføre på samme måde som JPQL's @NamedQuery .
Indbyggede forespørgsler returnerer deres resultat som et Objekt[] eller en Liste
Og du bør altid definere forespørgselsrummet for dine indbyggede forespørgsler. Det gør det muligt for Hibernate at optimere den beskidte kontrol og skylning, den skal udføre, før forespørgslen udføres.
