@Override public boolean borrarPedido(TransferPedido pedido) throws DAOException { boolean error = false; Statement stmt = null; // Get the connection from the transaction Connection connection = null; try { connection = (Connection) TransactionManager.getInstancia().getTransaction().getResource(); } catch (ClassCastException ex) { } ; if (connection == null) { // if we dont find the transaction, make the sentence with no transaction try { connection = BBDDConnection.getConnection(); stmt = connection.createStatement(); int editadas = stmt.executeUpdate( "UPDATE pedidos SET " + "activo = 0 " + "WHERE id = " + pedido.getId() + " AND activo = 1"); if (editadas != 1) { error = true; } } catch (SQLException ex) { throw new DAOException(ex); } } else { try { stmt = connection.createStatement(); int editadas = stmt.executeUpdate( "UPDATE pedidos SET " + "activo = 0 " + "WHERE id = " + pedido.getId() + " AND activo = 1"); if (editadas != 1) { error = true; } } catch (SQLException ex) { throw new DAOException(ex); } } return !error; }
@Override public TransferListaPedidos principalPedido(int lockMode) throws DAOException { TransferListaPedidos out = new TransferListaPedidos(); out.setLista(new ArrayList<TransferPedido>()); Statement stmt = null; ResultSet rs = null; // Get the connection from the transaction Connection connection = null; try { connection = (Connection) TransactionManager.getInstancia().getTransaction().getResource(); } catch (ClassCastException ex) { } ; if (connection == null || lockMode == 0) { // if we dont find the transaction, make the sentence with no transaction try { if (connection == null) connection = BBDDConnection.getConnection(); stmt = connection.createStatement(); stmt.execute("SELECT id, id_proveedor, fecha, estado " + "FROM pedidos WHERE activo = 1"); rs = stmt.getResultSet(); TransferPedido aux; while (rs.next()) { aux = new TransferPedido(); aux.setId(rs.getInt(1)); aux.setIdProveedor(rs.getInt(2)); aux.setFecha(rs.getString(3)); aux.setEstado(rs.getString(4).toCharArray()[0]); out.getLista().add(aux); } } catch (SQLException sqlex) { throw new DAOException(sqlex); } } else { try { stmt = connection.createStatement(); stmt.execute( "SELECT id, id_proveedor, fecha, estado " + "FROM pedidos WHERE activo = 1 FOR UPDATE"); rs = stmt.getResultSet(); TransferPedido aux; while (rs.next()) { aux = new TransferPedido(); aux.setId(rs.getInt(1)); aux.setIdProveedor(rs.getInt(2)); aux.setFecha(rs.getString(3)); aux.setEstado(rs.getString(4).toCharArray()[0]); out.getLista().add(aux); } } catch (SQLException sqlex) { throw new DAOException(sqlex); } } return out; }
@Override public TransferPedido consultarPedido(TransferPedido pedido, int lockMode) throws DAOException { TransferPedido out = new TransferPedido(); Statement stmt = null; ResultSet rs = null; // Get the connection from the transaction Connection connection = null; try { connection = (Connection) TransactionManager.getInstancia().getTransaction().getResource(); } catch (ClassCastException ex) { } ; if (connection == null || lockMode == 0) { // if we dont find the transaction, make the sentence with no transaction try { if (connection == null) connection = BBDDConnection.getConnection(); stmt = connection.createStatement(); stmt.execute( "SELECT id, fecha, id_proveedor, estado FROM pedidos " + "WHERE id = '" + pedido.getId() + "' AND activo = '1'"); rs = stmt.getResultSet(); if (rs.next()) { out.setId(rs.getInt(1)); out.setFecha(rs.getString(2)); out.setIdProveedor(rs.getInt(3)); out.setEstado(rs.getString(4).toCharArray()[0]); stmt.execute( "SELECT id_prod, cantidad, precio_uni, id_pedido FROM linea_pedido " + "WHERE id_pedido = '" + pedido.getId() + "'"); rs = stmt.getResultSet(); while (rs.next()) { out.addLineaPedido(rs.getInt(1), rs.getInt(2), rs.getDouble(3), rs.getInt(4)); } } else { out.setId(-1); } } catch (SQLException sqlex) { throw new DAOException(sqlex); } } else { try { stmt = connection.createStatement(); stmt.execute( "SELECT id, fecha, id_proveedor, estado FROM pedidos " + "WHERE id = '" + pedido.getId() + "' AND activo = '1' FOR UPDATE"); rs = stmt.getResultSet(); if (rs.next()) { out.setId(rs.getInt(1)); out.setFecha(rs.getString(2)); out.setIdProveedor(rs.getInt(3)); out.setEstado(rs.getString(4).toCharArray()[0]); stmt.execute( "SELECT id_prod, cantidad, precio_uni, id_pedido FROM linea_pedido " + "WHERE id_pedido = '" + pedido.getId() + "' FOR UPDATE"); rs = stmt.getResultSet(); while (rs.next()) { out.addLineaPedido(rs.getInt(1), rs.getInt(2), rs.getDouble(3), rs.getInt(4)); } } else { out.setId(-1); } } catch (SQLException sqlex) { throw new DAOException(sqlex); } } return out; }
@Override public boolean crearPedido(TransferPedido pedido) throws DAOException { boolean error = false; Statement stmt = null; // Get the connection from the transaction Connection connection = null; try { connection = (Connection) TransactionManager.getInstancia().getTransaction().getResource(); } catch (ClassCastException ex) { } ; if (connection == null) { // if we dont find the transaction, make the sentence with no transaction try { connection = BBDDConnection.getConnection(); stmt = connection.createStatement(); stmt.addBatch( "INSERT INTO pedidos (id_proveedor,fecha, estado) " + "VALUES(" + pedido.getIdProveedor() + ", STR_TO_DATE('" + pedido.getFecha() + "','%d-%m-%Y'), 'P')"); for (int i = 0; i < pedido.getNumLineasPedido(); i++) stmt.addBatch( "INSERT INTO linea_pedido (id_pedido, id_prod, precio_uni, cantidad) " + "VALUES(" + "(SELECT coalesce(MAX(id),'1') FROM pedidos), " + pedido.getLineaPedido(i).getIdProducto() + ", " + pedido.getLineaPedido(i).getPrecio() + ", " + pedido.getLineaPedido(i).getCantidad() + ")"); for (int current : stmt.executeBatch()) if (current != 1) error = true; } catch (SQLException ex) { throw new DAOException(ex); } } else { try { stmt = connection.createStatement(); stmt.addBatch( "INSERT INTO pedidos (id_proveedor,fecha, estado) " + "VALUES(" + pedido.getIdProveedor() + ", STR_TO_DATE('" + pedido.getFecha() + "','%d-%m-%Y'), 'P')"); for (int i = 0; i < pedido.getNumLineasPedido(); i++) stmt.addBatch( "INSERT INTO linea_pedido (id_pedido, id_prod, precio_uni, cantidad) " + "VALUES(" + "(SELECT coalesce(MAX(id),'1') FROM pedidos), " + pedido.getLineaPedido(i).getIdProducto() + ", " + pedido.getLineaPedido(i).getPrecio() + ", " + pedido.getLineaPedido(i).getCantidad() + ")"); for (int current : stmt.executeBatch()) if (current != 1) error = true; } catch (SQLException ex) { throw new DAOException(ex); } } return !error; }