Cómo mapear una consulta SQL compleja a un objeto JSON usando JDBC
Cuando desarrollamos una aplicación empresarial, no siempre podemos obtener la información que necesitamos desde una sola tabla. Es habitual que un documento dependa de datos almacenados en distintas entidades relacionadas.
Un ejemplo es la construcción de un documento electrónico. Para generar su representación como JSON necesitamos recuperar información de la empresa, receptor, tipo de documento, forma de pago, totales y detalle de productos.
En este ejemplo veremos cómo hacerlo utilizando Java, JDBC y SQL, sin utilizar un ORM.
El problema
Nuestro objetivo es construir un objeto DTEJson con una estructura similar a:
DTEJson
└── DocumentoJSON
├── EncabezadoJson
│ ├── IdDteJson
│ ├── EmisorJson
│ ├── ReceptorJson
│ └── TotalesJson
└── DetalleDteJson[]
Sin embargo, la información necesaria se encuentra distribuida entre varias tablas:
Movimiento
├── Empresa
├── CliProv
├── TipoDocumentos
├── FPago
├── Despacho
├── Traslado
├── Referencia
└── TpoVenta
DetalleMovimiento
└── Producto
Por lo tanto, primero necesitamos recuperar el encabezado del documento.
Consulta del encabezado
Podemos utilizar un bloque de texto de Java para mantener legible una consulta SQL relativamente extensa:
String sql = """
SELECT Empresa.*,
FPago.FPagoSII,
TpoVenta.CodSii AS IndServicio,
TipoDocumentos.CodigoSii AS CodSiiDoc,
Movimiento.NumDoc,
Movimiento.MovimientoFecha,
CliProv.*,
Movimiento.MontoNeto,
Movimiento.MontoExento,
Movimiento.Iva,
Movimiento.TasaIva,
Movimiento.MontoTotal,
DocumentoReferencia.CodigoSii AS CodigoSiiDocumentoReferencia,
Referencia.ReferenciaCod AS CodRef,
Movimiento.FolioRef,
Traslado.TrasladoCod,
Despacho.DespachoCod
FROM Movimiento
INNER JOIN TipoDocumentos
ON Movimiento.TipoDocumentoId = TipoDocumentos.TipoDocumentoId
INNER JOIN TipoDocumentos AS DocumentoReferencia
ON Movimiento.TipoDocumentoReferenciaId =
DocumentoReferencia.TipoDocumentoId
INNER JOIN CliProv
ON CliProv.CliProvId = Movimiento.CliProvId
INNER JOIN FPago
ON Movimiento.idFPago = FPago.idFPago
INNER JOIN Despacho
ON Movimiento.DespachoId = Despacho.DespachoId
INNER JOIN Traslado
ON Movimiento.TrasladoId = Traslado.TrasladoId
INNER JOIN Empresa
ON Movimiento.EmpresaId = Empresa.EmpresaId
INNER JOIN Referencia
ON Movimiento.ReferenciaId = Referencia.Id
INNER JOIN TpoVenta
ON Movimiento.IdTpoVenta = TpoVenta.IdTpoVenta
WHERE Movimiento.MovimientoId = ?
""";
Un detalle interesante de esta consulta es que TipoDocumentos participa dos veces.
La primera relación identifica el tipo del documento que estamos generando:
INNER JOIN TipoDocumentos
ON Movimiento.TipoDocumentoId = TipoDocumentos.TipoDocumentoId
La segunda identifica el tipo de documento que eventualmente estamos referenciando:
INNER JOIN TipoDocumentos AS DocumentoReferencia
ON Movimiento.TipoDocumentoReferenciaId =
DocumentoReferencia.TipoDocumentoId
El alias DocumentoReferencia permite utilizar la misma tabla dos veces dentro de la consulta sin confundir ambas relaciones.
Ejecutando la consulta con PreparedStatement
El identificador del movimiento no se concatena directamente al SQL. Utilizamos un parámetro:
WHERE Movimiento.MovimientoId = ?
y posteriormente:
stm.setInt(1, idmovimiento);
La conexión, el PreparedStatement y el ResultSet pueden manejarse utilizando try-with-resources:
try (Connection conexion = ConexionBD.getConexion();
PreparedStatement stm = conexion.prepareStatement(sql)) {
stm.setInt(1, idmovimiento);
try (ResultSet rs = stm.executeQuery()) {
if (rs.next()) {
// mapear resultado
}
}
}
De esta manera los recursos JDBC son cerrados automáticamente al abandonar sus respectivos bloques.
Del ResultSet al modelo de objetos
Una vez obtenido el registro podemos comenzar a transformar el resultado relacional en nuestra estructura Java.
Por ejemplo, los datos correspondientes al emisor:
emisor.setActecoemisor(rs.getString("EmpresaActeco"));
emisor.setRutemisor(rs.getString("EmpresaRut"));
emisor.setRsemisor(rs.getString("EmpresaRaz"));
emisor.setGiroemisor(rs.getString("EmpresaGir"));
emisor.setCdgsiisucur(rs.getString("SucursalSiiCod"));
emisor.setCiuemisor(rs.getString("EmpresaCiu"));
emisor.setCmnaemisor(rs.getString("EmpresaCom"));
emisor.setDiremisor(rs.getString("EmpresaDir"));
Los datos del receptor:
receptor.setRutreceptor(rs.getString("CliProvRut"));
receptor.setRsreceptor(rs.getString("CliProvRaz"));
receptor.setGiroreceptor(rs.getString("CliProvGir"));
receptor.setDirreceptor(rs.getString("CliProvDir"));
receptor.setCmnareceptor(rs.getString("CliProvCom"));
receptor.setCiureceptor(rs.getString("CliProvCiu"));
La identificación del documento:
iddoc.setTipodte(rs.getInt("CodSiiDoc"));
iddoc.setNumdte(rs.getInt("NumDoc"));
iddoc.setFechaemision(rs.getString("MovimientoFecha"));
Y sus totales:
totales.setMontoneto(rs.getBigDecimal("MontoNeto"));
totales.setMontoexento(rs.getBigDecimal("MontoExento"));
totales.setMontoiva(rs.getBigDecimal("Iva"));
totales.setTasaiva(
rs.getBigDecimal("TasaIva")
.multiply(BigDecimal.valueOf(100))
);
totales.setMontototal(rs.getBigDecimal("MontoTotal"));
Aquí estamos realizando un mapeo manual: las columnas de un modelo relacional se convierten en propiedades de diferentes objetos Java.
Construyendo el encabezado
Una vez mapeados los diferentes componentes podemos componer el objeto:
encabezado.setIddoc(iddoc);
encabezado.setEmisor(emisor);
encabezado.setReceptor(receptor);
encabezado.setTotales(totales);
documento.setEncabezado(encabezado);
Esto permite que nuestra estructura Java sea independiente de cómo estaban distribuidos originalmente los datos entre las tablas.
Recuperando el detalle
Un documento también contiene una cantidad variable de líneas. Por eso utilizamos una segunda consulta:
String sql = """
SELECT Producto.ProductoId,
Producto.ProductoNom,
DetalleMovimiento.Cantidad,
DetalleMovimiento.precioVentaAplicado,
DetalleMovimiento.PrecioCosto,
DetalleMovimiento.DescuentoPct,
DetalleMovimiento.TotalDetalle
FROM DetalleMovimiento
INNER JOIN Movimiento
ON DetalleMovimiento.MovimientoId = Movimiento.MovimientoId
INNER JOIN Producto
ON DetalleMovimiento.ProductoId = Producto.ProductoId
WHERE DetalleMovimiento.MovimientoId = ?
""";
A diferencia del encabezado, aquí esperamos obtener varias filas, por lo que recorremos el ResultSet con while:
while (rs.next()) {
DetalleDteJson detalle = new DetalleDteJson();
detalle.setNrolinea(linea);
CdgItemJson cdgitem = new CdgItemJson();
cdgitem.setTpocodigo("INT");
cdgitem.setVlrcodigo(
String.valueOf(rs.getInt("ProductoId"))
);
detalle.setNmbitem(rs.getString("ProductoNom"));
detalle.setQtyitem(rs.getBigDecimal("Cantidad"));
detalle.setPrcitem(rs.getBigDecimal("precioVentaAplicado"));
detalle.setMontoitem(rs.getBigDecimal("TotalDetalle"));
detalle.setCdgitem(cdgitem);
detalledte.add(detalle);
linea++;
}
Cada registro SQL se transforma en una instancia de DetalleDteJson que posteriormente agregamos a un ArrayList.
Componiendo el documento completo
Finalmente podemos unir ambas partes:
documento.setEncabezado(encabezado);
documento.setDetalleDteJson(getDetalleDTE(idmovimiento));
DTEJson dte = new DTEJson();
dte.setDocumento(documento);
return dte;
El método JDBC ya no devuelve columnas ni un ResultSet.
Devuelve un objeto del dominio:
DTEJson
Este objeto posteriormente puede ser serializado utilizando una biblioteca JSON, por ejemplo Gson.
¿Por qué hacer el mapeo de esta forma?
Este ejemplo permite ver claramente tres representaciones diferentes de los mismos datos:
BASE DE DATOS
│
│ SQL + JOIN
▼
ResultSet JDBC
│
│ mapeo
▼
OBJETOS JAVA
│
│ serialización
▼
JSON
No es obligatorio utilizar Hibernate, JPA o un framework para realizar este proceso.
JDBC permite controlar directamente la consulta SQL y decidir exactamente cómo transformar los resultados en nuestro modelo de objetos.
En sistemas empresariales, donde una operación puede involucrar numerosas tablas y estructuras de salida específicas, comprender este proceso ayuda además a entender qué hacen internamente muchas herramientas de persistencia y mapeo.
Comentarios
Publicar un comentario