public int insertNewNetwrok(Network n) throws DataAccessException { PreparedStatement ps = null; Connection connInventory = null; int risultato = 0; if (!exsistNetwork(n)) { try { connInventory = DBConnectionManager.inventoryConnectionFactory(); connInventory.setAutoCommit(false); String sql = "insert into network(description,mac_address,ip_address,id_item_pc) values(?,?,?,?)"; ps = connInventory.prepareStatement(sql); ps.setString(1, n.getDescription()); ps.setString(2, n.getMac_address()); ps.setString(3, n.getIp_address()); ps.setInt(4, n.getId_pc()); risultato = ps.executeUpdate(); if (risultato == 1) connInventory.commit(); else connInventory.rollback(); DBConnectionManager.CloseConnection(connInventory); } catch (SQLException e) { log.error("insertNewNetwrok: SQL failed", e); e.printStackTrace(); throw new DataAccessException(e.toString(), e); } catch (RuntimeException e) { throw new DataAccessException(e.toString()); } catch (DataLayerException e) { e.printStackTrace(); } finally { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } } else { risultato = -1; } return risultato; }
public List<Network> getNetworks(int id_item_pc) throws DataAccessException { PreparedStatement ps = null; ResultSet rs = null; Connection connInventory = null; Network net = null; List<Network> allnetwork = new ArrayList<Network>(); try { connInventory = DBConnectionManager.inventoryConnectionFactory(); String sql = "select n.* from network n where id_item_pc"; ps = connInventory.prepareStatement(sql); rs = ps.executeQuery(); while (rs.next()) { net = new Network(); net.setId(rs.getInt("id")); net.setDescription(rs.getString("description")); net.setMac_address(rs.getString("mac_address")); net.setIp_address(rs.getString("ip_address")); net.setId_pc(rs.getInt("id_item_pc")); allnetwork.add(net); } ps.close(); rs.close(); DBConnectionManager.CloseConnection(connInventory); } catch (SQLException e) { log.error("getNetworks: SQL failed", e); e.printStackTrace(); throw new DataAccessException(e.toString(), e); } catch (RuntimeException e) { throw new DataAccessException(e.toString()); } catch (DataLayerException e) { e.printStackTrace(); } finally { try { ps.close(); rs.close(); } catch (SQLException e) { e.printStackTrace(); } } return allnetwork; }
public boolean exsistNetwork(Network net) throws DataAccessException { PreparedStatement ps = null; Connection connInventory = null; ResultSet rs = null; boolean esiste = false; try { connInventory = DBConnectionManager.inventoryConnectionFactory(); if (net.getId() > 0) { String sql = "select id from network where (mac_address = ? or ip_address = ?) and id<>?"; ps = connInventory.prepareStatement(sql); ps.setString(1, net.getMac_address()); ps.setString(2, net.getIp_address()); ps.setInt(3, net.getId()); } else { String sql = "select id from network where (mac_address = ? or ip_address = ?)"; ps = connInventory.prepareStatement(sql); ps.setString(1, net.getMac_address()); ps.setString(2, net.getIp_address()); } rs = ps.executeQuery(); if (rs.next()) esiste = true; DBConnectionManager.CloseConnection(connInventory); } catch (SQLException e) { log.error("exsistNetwork: SQL failed", e); e.printStackTrace(); throw new DataAccessException(e.toString(), e); } catch (RuntimeException e) { throw new DataAccessException(e.toString()); } catch (DataLayerException e) { e.printStackTrace(); } finally { try { rs.close(); ps.close(); } catch (SQLException e) { e.printStackTrace(); } } return esiste; }
public int deleteNetwork(Network n) throws DataAccessException { PreparedStatement ps = null; Connection connInventory = null; int risultato = 0; try { connInventory = DBConnectionManager.inventoryConnectionFactory(); connInventory.setAutoCommit(false); String sql = "delete from network where id=?"; ps = connInventory.prepareStatement(sql); ps.setInt(1, n.getId()); risultato = ps.executeUpdate(); if (risultato > 0) connInventory.commit(); else connInventory.rollback(); DBConnectionManager.CloseConnection(connInventory); } catch (SQLException e) { log.error("deleteNetwork: SQL failed", e); e.printStackTrace(); throw new DataAccessException(e.toString(), e); } catch (RuntimeException e) { throw new DataAccessException(e.toString()); } catch (DataLayerException e) { e.printStackTrace(); } finally { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } return risultato; }