Java >> Java tutorial >  >> Tag >> SQL

Kom godt i gang med jOOQ – Opbygning af SQL-forespørgsler i Java

SQL

JPA og Hibernate passer godt til at implementere vedvarende og opdatere use cases og simple forespørgsler. Men de fleste applikationer har brug for meget mere end det. Du skal kunne bruge det fulde funktionssæt af SQL til at implementere dine forespørgsler. Det er derfor, JPA understøtter indfødte forespørgsler. Men det er ikke behageligt at bruge. Du ønsker sandsynligvis ikke at angive din forespørgsel som en simpel streng eller selv at håndtere forskellene mellem de forskellige databasedialekter.

Andre biblioteker er meget bedre egnede til at implementere komplekse SQL-forespørgsler. En af dem er jOOQ. Det giver dig en Java DSL, der giver dig mulighed for at bygge SQL-forespørgsler på en behagelig og typesikker måde. Det abstraherer de tekniske vanskeligheder ved almindelig JDBC og håndterer de subtile forskelle mellem de forskellige SQL-dialekter.

I dette indlæg vil jeg give dig en grundlæggende introduktion til jOOQ, før jeg viser dig, hvordan du integrerer det med Hibernate i mit næste indlæg.

Så lad os komme i gang.

Afhængigheder

Før du kan bruge jOOQ i dit projekt, skal du tilføje et par afhængigheder til det. Følgende kodestykke viser maven-afhængighederne af jOOQ-fællesskabsudgaven, som du kan bruge med open source-databaser. Hvis du bruger andre databaser, såsom Oracle eller SQL Server, skal du have en af ​​jOOQs kommercielle licenser.

<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq</artifactId>
  <version>${version.jooq}</version>
</dependency>
<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-meta</artifactId>
  <version>${version.jooq}</version>
</dependency>
<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen</artifactId>
  <version>${version.jooq}</version>
</dependency>

Kodegenerering

Kodegenereringstrinnet er valgfrit, men jeg anbefaler det stærkt. Hvis du vil forbedre udviklerproduktiviteten og skrive dine forespørgsler på en typesikker måde, bør du bruge jOOQs kodegenerator. Det opretter Java-klasser, der kortlægger dine tabeller, sekvenser, lagrede procedurer og mere. Du kan bruge disse klasser til at definere dine forespørgsler og til at behandle de valgte resultater. Men mere om det senere.

jOOQ giver dig et sæt kodegeneratorer, som du kan bruge på kommandolinjen, i Eclipse og som et Maven-plugin. De kan generere jOOQs metamodelklasser baseret på en eksisterende database, et SQL-script eller dine entitetstilknytninger.

Generering af jOOQ-klasser baseret på en database

Lad os tage et kig på et eksempel. Jeg bruger en PostgreSQL med en simpel testdatabase, der indeholder tabellerne author , bog , bogforfatter og udgiver .

Her er et eksempel på en Maven build-konfiguration, der kalder kodegeneratoren i Mavens generate mål. Generatoren opretter forbindelse til offentligheden skema af jooq database på en PostgreSQL-server på localhost. Den skriver de genererede klasser til pakken org.thoughts.on.java.db i mappen target/generated-sources/jooq .

<plugin>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen-maven</artifactId>
  <version>${version.jooq}</version>
 
  <executions>
    <execution>
      <goals>
        <goal>generate</goal>
      </goals>
    </execution>
  </executions>
 
  <dependencies>
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>9.4.1208</version>
    </dependency>
  </dependencies>
 
  <configuration>
    <jdbc>
      <driver>org.postgresql.Driver</driver>
      <url>jdbc:postgresql:jOOQ</url>
      <user>postgres</user>
      <password>postgres</password>
    </jdbc>

    <generator>
      <database>
        <name>org.jooq.util.postgres.PostgresDatabase</name>
        <includes>.*</includes>
        <excludes></excludes>
        <inputSchema>public</inputSchema>
      </database>
      <target>
        <packageName>org.thoughts.on.java.db</packageName>
        <directory>target/generated-sources/jooq</directory>
      </target>
    </generator>
  </configuration>
</plugin>

Når du har kørt Maven build, kan du finde et sæt klasser i pakkerne org.thoughts.on.java.db , org.thoughts.on.java.db.tables og org.thoughts.on.java.db.tables.records .

Klasserne i org.thoughts.on.java.db pakke giver nem adgang til skemaet og alle tabeller, sekvenser, nøgler og indekser. Vi har ikke brug for disse klasser i dette eksempel.

Vi vil kun bruge klasserne i org.thoughts.on.java.db.tables pakke til at henvise til tabeller og deres kolonner i SQL-forespørgsler og klasser i org.thoughts.on.java.db.tables.records pakke til at håndtere resultaterne af disse forespørgsler.

Implementering af forespørgsler med jOOQ

Det fantastiske ved jOOQ er, at DSL er meget lig SQLs syntaks. Så hvis du er fortrolig med SQL, har du ingen problemer med at skrive dine forespørgsler med jOOQ.

Det hele starter med oprettelsen af ​​en DSLContext som du skal initialisere med en JDBC forbindelse og SQLD-dialekten du vil bruge. I dette eksempel bruger jeg en PostgreSQL 9.4-database på localhost.

String user = "postgres";
String pass = "postgres";
String url = "jdbc:postgresql:jOOQ";

// Create a JDBC Connection
try (Connection conn = DriverManager.getConnection(url, user, pass)) {
	// Create a context for your database
	DSLContext ctx = DSL.using(conn, SQLDialect.POSTGRES_9_4);
	
	// Do something useful ...

} catch (Exception e) {
	e.printStackTrace();
}

Du kan derefter bruge DSLContext for at oprette dine forespørgsler.

En simpel forespørgsel

Lad os starte med en simpel forespørgsel, der henter alle poster fra bogen tabel.

Result<Record> result = ctx.select().from(BOOK).fetch();
for (Record r : result) {
	Long id = r.get(BOOK.ID);
	String title = r.get(BOOK.TITLE);
	Date publishingDate = r.get(BOOK.PUBLISHINGDATE);
	log.info("Book: id="+id+" title="+title+ " publishingDate="+publishingDate);
}

Som du kan se, er definitionen af ​​en sådan forespørgsel let, og koden ligner næsten SQL. vælg uden parameter metode på DSLContext definerer en projektion, der inkluderer alle kolonner og fra metoden definerer fra hvilken databasetabel disse poster skal vælges.

Her kan du se en fordel ved kodegenereringen. I stedet for at angive navnet på bogen tabel som en streng , kan du bruge en statisk attribut for den genererede bog klasse. Den klasse repræsenterer bogen tabel og giver dig en stærkt indtastet, statisk attribut for hver kolonne i tabellen. Det muliggør færdiggørelse af kode i din IDE. Og hvis du integrerer kodegenereringstrinnet i din byggeproces, kan du også være sikker på, at din kode altid matcher din tabelmodel. Hvis tabelmodellen ændres, vil kodegeneratoren opdatere Java-klasserne, og din IDE vil vise dig kompileringstidsfejl ved alle kodestykker, du skal opdatere.

Når du har defineret din forespørgsel, skal du udføre den og hente resultatet. I dette eksempel gør jeg det ved at kalde fetch metode.

I næste trin kan du behandle resultatet. Forespørgslen returnerede en samling af Record grænseflader. Hver af dem repræsenterer en registrering af forespørgselsresultatet. Som du kan se i kodestykket, kan du hente værdien af ​​hvert felt i resultatposten ved at kalde get metode med en attribut for den genererede bog klasse, der refererer til en af ​​de valgte databasekolonner. Denne attribut giver også de nødvendige typeoplysninger for at undgå typeafstøbninger. Hvis du ikke bruger kodegeneratoren eller bruger et alias i din projektion, kan du også angive en streng med navnet på resultatsætkolonnen.

En lidt mindre enkel forespørgsel

Denne gang vil jeg vælge fornavn, efternavn og antallet af bøger skrevet af alle forfattere, hvis efternavn starter med "Jan" og slutter med "en".

Result<Record3<String, String, Integer>> result = 
		ctx.select(
				AUTHOR.FIRSTNAME, 
				AUTHOR.LASTNAME, 
				DSL.count(BOOK_AUTHOR.BOOKID).as("bookCount"))
			.from(AUTHOR)
				.leftJoin(BOOK_AUTHOR).on(AUTHOR.ID.eq(BOOK_AUTHOR.AUTHORID))
			.where(AUTHOR.LASTNAME.like("Jan%en"))
			.groupBy(AUTHOR.FIRSTNAME, AUTHOR.LASTNAME)
			.fetch();
for (Record r : result) {
	String firstName = r.get(AUTHOR.FIRSTNAME);
	String lastName = r.get(AUTHOR.LASTNAME);
	Integer bookCount = r.get("bookCount", int.class);
	System.out.println(firstName + " " + lastName + " wrote " + bookCount + " book(s).");
}

Du kan igen se, at Java-koden ligner meget den SQL-sætning, du vil oprette.

vælg metode definerer fremskrivningen. Jeg bruger den genererede Forfatter klasse for at referere til fornavnet og efternavn kolonner af forfatteren bord. jOOQs DSL klasse giver masser af metoder, der gør det muligt at kalde SQL-funktioner. Jeg bruger det her til at kalde SQLs count funktion og definere aliaset bookCount for det felt.

Så definerer jeg FROM-klausulen i forespørgslen. fra metoden returnerer et SelectJoinStep grænseflade, som gør det muligt for dig at definere forskellige typer join-klausuler eller at kombinere resultaterne af flere forespørgsler med sæt-operatorer, såsom UNION eller SKRYDNING . Dette er en kæmpe fordel sammenlignet med JPA's JPQL-forespørgsler, som jeg normalt viser på denne blog. jOOQ giver dig mulighed for at bruge alle SQL-funktioner, så du kan drage fordel af de kraftfulde forespørgselsfunktioner, som din database tilbyder.

Lad os fortsætte med at specificere WHERE-sætningen. Du kan gøre det ved at ringe til hvor metode med en streng , en SQL forespørgselsdel eller en eller flere Betingelse s. Jeg foretrækker at definere min forespørgsel på en typesikker måde, så jeg bruger den genererede Author klasse for at referere til efternavnet kolonnen og for at definere synes godt om udtryk. Som du kan se i kodestykket, definerer jeg ikke en bindingsparameter og indstiller bare strengen "Jan%en" som dens værdi. jOOQ tilføjer automatisk en bindingsparameter til forespørgslen og indstiller den angivne værdi som bindingsparameterværdien.

OK, næsten færdig. Vi skal blot tilføje en GROUP BY-sætning til kolonnerne fornavn og efternavn . I lighed med definitionen af ​​de pervious klausuler, kan du gøre det ved at kalde groupBy metode med referencer til de 2 databasekolonner.

Det er alt, du skal gøre for at definere forespørgslen. Kaldet af hentet metoden udfører forespørgslen og returnerer et Resultat grænseflade, som indeholder en samling af stærkt indtastede Record grænseflader. Som i det foregående eksempel kan du derefter bruge denne grænseflade og jOOQs genererede klasser til at behandle forespørgselsresultatet.

Oversigt

Som du har set, giver jOOQ dig mulighed for at implementere dine forespørgsler med en typesikker DSL, der ligner SQL ret meget. En af dens vigtigste fordele er, at den abstraherer vanskelighederne ved JDBC og forskellene i SQL-dialekterne uden at begrænse dig til en lille delmængde af SQL.

I dette indlæg viste jeg dig kun et par grundlæggende spørgsmål. Men du kan også bygge meget komplekse SQL-forespørgsler ved hjælp af rekursion, vinduesfunktioner og andre avancerede SQL-funktioner.

Og jeg har gode nyheder til dig, hvis du bruger Hibernate og vil begynde at skrive dine mere komplekse forespørgsler med jOOQ. Hibernate og jOOQ fungerer rigtig fint sammen. Jeg viser dig, hvordan du integrerer dem i Hibernate &jOOQ – A Match Made in Heaven.


Java tag