DEV Community

Edgar Rios Navarro
Edgar Rios Navarro

Posted on

Procesar una transacción completamente con TRANSACT-SQL

Consideremos que, la cadena XML se genera en el servidor de aplicación. Se emplea la configuración de Spring para usar JAXB2.

Debemos asignar una lista de detalle (listCollectionSpendReport) a la entidad que vamos a persistir (collection). Nótese que, previamente se grababa la entidad y luego, uno por uno, los detalles.

//collectionService.insert(collection);
// Guardamos los gastos
if (listCollectionSpendReport != null && listCollectionSpendReport.size() > 0) {
/*CollectionSpend collectionSpend = null;
for (CollectionSpendReport collectionSpendReport : listCollectionSpendReport) {
collectionSpend = new CollectionSpend();
collectionSpend.setCollectionID(collection.getCollectionID());
collectionSpend.setCollectionSpendTypeID(collectionSpendReport.getCollectionSpendTypeID());
collectionSpend.setAmmount(collectionSpendReport.getAmmount());
collectionSpend.setDescription(collectionSpendReport.getDescription());
collectionSpendService.insert(collectionSpend);
}*/
collection.setListCollectionSpendReport(listCollectionSpendReport);
}
//Convierte a xml
StringWriter writer = new StringWriter();
marshaller.marshal(collection, new StreamResult(writer));
log.debug(writer.toString());
collection.setXmlDetail(writer.toString());
collectionService.insertWDetail(collection);

El procedimiento almacenado, recibe los datos de la entidad principal y una cadena de los detalles. Luego, son casteados en formato XML y recorridos en una sentencia SELECT para ser insertados en la tabla correspondiente.

CREATE PROCEDURE [dbo].[MTX_sp_CollectionWDetailNew]
...
@xmlDetail varchar(MAX) = NULL
AS
BEGIN
DECLARE @dispatchStatusID int
SET NOCOUNT ON;
BEGIN TRANSACTION RealizarCambios
BEGIN TRY
-- Inicio del Código
--1. Cabecera
INSERT INTO [dbo].[MTXCollection]
...
SET @collectionID = SCOPE_IDENTITY()
DECLARE @xml XML
SELECT @xml = CAST(
REPLACE(@xmlDetail, 'encoding="UTF-8"', '')
AS XML)
--2. Inserta detalle de gastos
INSERT INTO MTXCollectionSpend ([collectionID],[collectionSpendTypeID],[ammount],[description])
SELECT
@collectionID,
x.Rec.query('./collectionSpendTypeID').value('.', 'int') AS 'collectionSpendTypeID',
x.Rec.query('./ammount').value('.', 'float') AS 'ammount',
x.Rec.query('./description').value('.', 'varchar(255)') AS 'description'
FROM @xml.nodes('/collectionReport/listCollectionSpendReport') as x(Rec)
-- Fin del Código
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
BEGIN
ROLLBACK TRANSACTION RealizarCambios
SELECT 0
END
END CATCH;
IF (@@TRANCOUNT > 0)
BEGIN
COMMIT TRANSACTION RealizarCambios;
SELECT 1
END
END

Documentación

http://docs.spring.io/spring-ws/site/reference/html/oxm.html

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs