Transforme automáticamente las uniones implícitas al estilo de Oracle en ANSI JOIN utilizando jOOQ: Java, SQL y jOOQ.

Mientras que jOOQ se usa principalmente como un SQL DSL interno para SQL embebido y dinámico en Java, donde ofrece la mejor solución del mercado, jOOQ se usa cada vez más para una de sus funcionalidades secundarias: su analizador. Habiendo sido introducido en jOOQ 3.9 principalmente con el propósito de poder analizar declaraciones DDL, por ejemplo, para aplicar ingeniería inversa a su esquema con fines de generación de código, hemos agregado un número cada vez mayor de funciones y capacidades de transformación de SQL que permiten que el analizador se use como un producto independiente, a través de la CLI, nuestro sitio web o a través de la API jOOQ regular. Una característica que se agregó a jOOQ 3.14, que es especialmente útil para aquellos que usan jOOQ como analizador, es la capacidad de convertir las uniones implícitas de estilo antiguo de Oracle en ANSI JOIN.

¿Por qué evitar las "uniones implícitas"?

La sintaxis de combinación implícita al estilo antiguo de Oracle es compatible y está bien optimizada por la mayoría de los proveedores de RDBMS. En el pasado, antes de SQL-92, así es como usábamos las tablas de unión interna, por ejemplo, al consultar la base de datos de Sakila:



SELECT *
FROM actor a, film_actor fa, film f
WHERE a.actor_id = fa.actor_id
AND fa.film_id = f.film_id


Es cierto que la sintaxis es bastante intuitiva. Simplemente declare todas las tablas de las que desea extraer datos, luego asegúrese de que los datos apropiados se retengan solo filtrando los valores de clave principal/clave externa correspondientes. Por supuesto, esto puede salir muy mal. Por muchas razones obvias, como cuando olvida un predicado de combinación después de agregar una tabla. Si la consulta es compleja, puede ser difícil de depurar. La solución es ANSI JOIN. Comenzando con SQL-92 (¡casi 30 años ahora!), así es como nos asociamos con la mayoría de los RDBMS:



SELECT *
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
JOIN film f ON fa.film_id = f.film_id


Si todavía es posible definir predicados de combinación incorrectos, al menos ya no es posible olvidar un predicado, porque es sintácticamente incorrecto (excepto para MySQL, donde, desafortunadamente, la cláusula ON es opcional):



SELECT *
FROM actor a
JOIN film_actor fa -- Syntax error
JOIN film f -- Syntax error


JOIN implícito de jOOQ

Tenga en cuenta que es común referirse a la sintaxis anterior como "unión implícita", mientras que JPQL y jOOQ han reciclado el término para otro tipo de "unión implícita", que se basa en una ruta de clave externa e incluso menos propenso a errores que ANSI sintaxis SQL. . Con jOOQ, la consulta anterior se puede escribir de la siguiente manera:



ctx.select(
FILM_ACTOR.actor().asterisk(),
FILM_ACTOR.asterisk(),
FILM_ACTOR.film().asterisk())
.from(FILM_ACTOR)
.fetch();


La mera presencia de estas rutas de relación uno a uno en la consulta agregará implícitamente el LEFT JOIN Donde INNER JOIN a la FROM cláusula. Es simplemente una conveniencia además de JOIN ANSI ordinario, no un reemplazo.

Transformar uniones implícitas de Oracle

Cuando tenga una base de código antigua que quiera actualizar y transformar todas sus consultas usando ANSI JOIN, use jOOQ para eso. Puede usar las capacidades de programación de jOOQ (como se mencionó anteriormente) o el sitio web gratuito https://www.jooq.org/translate. En el sitio web, simplemente elija la opción "Oracle style to ANSI JOIN", coloque el siguiente SQL a la izquierda:

Contribución



SELECT
a.first_name,
a.last_name,
count(c.category_id)
FROM
actor a,
film_actor fa,
film f,
film_category fc,
category c
WHERE a.actor_id = fa.actor_id
AND fa.film_id = f.film_id
AND fc.category_id = c.category_id
GROUP BY
a.actor_id,
a.first_name,
a.last_name


Salir



SELECT
a.first_name,
a.last_name,
count(c.category_id)
FROM actor a
JOIN film_actor fa
ON a.actor_id = fa.actor_id
JOIN film f
ON fa.film_id = f.film_id
CROSS JOIN (
film_category fc
JOIN category c
ON fc.category_id = c.category_id
)
GROUP BY
a.actor_id,
a.first_name,
a.last_name


Y... ups. El resultado muestra correctamente el CROSS JOIN no deseado resultante porque faltaba uno de los predicados de unión: ¡sí, la herramienta ya ha ayudado! Arreglemos la consulta de entrada:

Entrada fija



SELECT
a.first_name,
a.last_name,
count(c.category_id)
FROM
actor a,
film_actor fa,
film f,
film_category fc,
category c
WHERE a.actor_id = fa.actor_id
AND fa.film_id = f.film_id
AND f.film_id = fc.film_id -- This was missing
AND fc.category_id = c.category_id
GROUP BY
a.actor_id,
a.first_name,
a.last_name


salida fija



SELECT
a.first_name,
a.last_name,
count(c.category_id)
FROM actor a
JOIN film_actor fa
ON a.actor_id = fa.actor_id
JOIN film f
ON fa.film_id = f.film_id
JOIN film_category fc
ON f.film_id = fc.film_id
JOIN category c
ON fc.category_id = c.category_id
GROUP BY
a.actor_id,
a.first_name,
a.last_name


Esto también funciona si usó la sintaxis de unión externa arcana de Oracle usando (+) (o servidor SQL *=, que no ha sido compatible desde hace algún tiempo). Es posible que tenga esta entrada:

Contribución



SELECT
a.first_name,
a.last_name,
count(c.category_id)
FROM
actor a,
film_actor fa,
film f,
film_category fc,
category c
WHERE a.actor_id = fa.actor_id(+)
AND fa.film_id = f.film_id(+)
AND f.film_id = fc.film_id(+)
AND fc.category_id(+) = c.category_id
GROUP BY
a.actor_id,
a.first_name,
a.last_name


Producir esta salida



SELECT
a.first_name,
a.last_name,
count(c.category_id)
FROM actor a
LEFT OUTER JOIN film_actor fa
ON a.actor_id = fa.actor_id
LEFT OUTER JOIN film f
ON fa.film_id = f.film_id
LEFT OUTER JOIN (
film_category fc
RIGHT OUTER JOIN category c
ON fc.category_id = c.category_id
)
ON f.film_id = fc.film_id
GROUP BY
a.actor_id,
a.first_name,
a.last_name


Cómo. ¡Uy de nuevo! Un morir (+) los símbolos estaban en el lado equivocado, por eso tenemos esto RIGHT OUTER JOIN. Nuevamente, la herramienta mostró que la sintaxis anterior era bastante propensa a errores. Arreglemoslo.

Entrada fija



SELECT
a.first_name,
a.last_name,
count(c.category_id)
FROM
actor a,
film_actor fa,
film f,
film_category fc,
category c
WHERE a.actor_id = fa.actor_id(+)
AND fa.film_id = f.film_id(+)
AND f.film_id = fc.film_id(+)
AND fc.category_id = c.category_id(+)
GROUP BY
a.actor_id,
a.first_name,
a.last_name


salida fija



SELECT
a.first_name,
a.last_name,
count(c.category_id)
FROM actor a
LEFT OUTER JOIN film_actor fa
ON a.actor_id = fa.actor_id
LEFT OUTER JOIN film f
ON fa.film_id = f.film_id
LEFT OUTER JOIN film_category fc
ON f.film_id = fc.film_id
LEFT OUTER JOIN category c
ON fc.category_id = c.category_id
GROUP BY
a.actor_id,
a.first_name,
a.last_name


Conclusión

¡Juega y cuéntanos lo que piensas! https://www.jooq.org/translate

Si quieres conocer otros artículos parecidos a Transforme automáticamente las uniones implícitas al estilo de Oracle en ANSI JOIN utilizando jOOQ: Java, SQL y jOOQ. puedes visitar la categoría Código.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Subir

Esta página web utiliza cookies para analizar de forma anónima y estadística el uso que haces de la web, mejorar los contenidos y tu experiencia de navegación. Para más información accede a la Política de Cookies . Ver mas