/** find a layer seq number for appending at the end (ie, larger than any existing) */ public int newSeqNo(CommanderConnection conn, int serviceId) throws Exception // FIXME: can we be more specific please? { log.info("Table_ServiceLayer::newSeqNo( dbc, serviceId=" + serviceId + " )"); Statement stmt = conn.createStatement(); ResultSet rs; rs = stmt.executeQuery( "SELECT max(layerseq)+1" + " from " + Globals.TABLE_SERVICELAYER + " where " + Globals.TABLE_SERVICELAYER_SERVICEID + "=" + serviceId); stmt.setFetchSize(0); // Turn cursor off int layerseq; if (rs == null) layerseq = 1; else { rs.next(); layerseq = rs.getInt(1); } // if it exceeds the max allowed int value, raise an exception if (layerseq > MAX_LAYERSEQ) { Exception e = new Exception("Error: layer sequencing overflow. Please contact your administrator."); throw e; } log.info("Table_ServiceLayer::newSeqNo() -> " + layerseq); return layerseq; }
/** * fetch one tuple by its (service,layer) id, keep in local result set precondition: JDBC * connection opened * * @param conn JDBC connection * @param id service tuple id * @throws SQLException, InvalidInputException */ public void fetchTupleById(CommanderConnection conn, int serviceId, int layerId) throws SQLException { log.info("Table_ServiceLayer::fetchTupleById()"); // execute query String query = "select " + Globals.TABLE_SERVICELAYER_SERVICEID + ", " + Globals.TABLE_SERVICELAYER_LAYERID + ", " + Globals.TABLE_SERVICELAYER_LAYERSEQ + " from " + Globals.TABLE_SERVICELAYER + " where " + Globals.TABLE_SERVICELAYER_SERVICEID + " = " + serviceId + " and " + Globals.TABLE_SERVICELAYER_LAYERID + " = " + layerId; log.debug("want to execute: " + query); Statement stmt = conn.createStatement(); resultSet = stmt.executeQuery(query); log.info("Table_ServiceLayer::fetchTupleById()"); }
/** * fetch all layers pertaining to a particular service, store result locally as a JDBC ResultList * precondition: JDBC connection opened * * @param conn JDBC connection * @throws SQLException */ public void fetchTuplesByServiceId(CommanderConnection conn, int sId) throws SQLException { log.info("Table_ServiceLayer::fetchTuplesByServiceId()"); // execute query String query = "select " + Globals.TABLE_SERVICELAYER_SERVICEID + ", " + Globals.TABLE_SERVICELAYER_LAYERID + ", " + Globals.TABLE_SERVICELAYER_LAYERSEQ + " from " + Globals.TABLE_SERVICELAYER + " where " + Globals.TABLE_SERVICELAYER + "." + Globals.TABLE_SERVICELAYER_SERVICEID + " = " + sId + " order by " + Globals.TABLE_SERVICELAYER + "." + Globals.TABLE_SERVICELAYER_LAYERSEQ; log.debug("want to execute: " + query); Statement stmt = conn.createStatement(); stmt.setFetchSize(0); // Turn cursor off resultSet = stmt.executeQuery(query); log.info("Table_ServiceLayer::fetchTuplesByServiceId()"); }
/** * delete tuple given by its layer id precondition: JDBC connection opened * * @param conn JDBC connection * @param layerId * @throws SQLException */ public void deleteTuplesByLayerId(CommanderConnection conn, int layerId) throws SQLException, ConnectionFailedException { log.info("Table_ServiceLayer::deleteTuplesByLayerId()"); // --- plausi checks ---------------------------------- // - connection object if (conn == null) { log.info("Table_ServiceLayer::deleteTuplesByLayerId() -- Error: no database connection."); throw new ConnectionFailedException("no database connection."); } // --- action ----------------------------------------- String query = "delete from " + Globals.TABLE_SERVICELAYER + " where " + Globals.TABLE_SERVICELAYER_LAYERID + " = " + layerId; log.debug("want to execute: " + query); Statement stmt = conn.createStatement(); stmt.executeUpdate(query); log.info("Table_ServiceLayer::deleteTuplesByLayerId() -- ok"); }
/** * insert one tuple; instance's local attribute set is not modified. precondition: JDBC connection * opened all strings non-null * * @param conn JDBC connection * @param id service tuple id * @throws SQLException */ public void insertTuple(CommanderConnection conn, int serviceId, int layerId, int layerSeq) throws SQLException, ConnectionFailedException, InvalidInputException { log.info("Table_ServiceLayer::insertTuple()"); // --- plausi checks ---------------------------------- // - connection object if (conn == null) { log.info("Table_ServiceLayer::insertTuple() -- Error: no database connection."); throw new ConnectionFailedException("no database connection."); } // --- action ----------------------------------------- // make VERY sure that attribute names and values are listed in the same sequence! String query = "insert into " + Globals.TABLE_SERVICELAYER + " ( " + Globals.TABLE_SERVICELAYER_SERVICEID + ", " + Globals.TABLE_SERVICELAYER_LAYERID + ", " + Globals.TABLE_SERVICELAYER_LAYERSEQ + ") " + " values( " + serviceId + ", " + layerId + ", " + layerSeq + ")"; log.debug("want to execute: " + query); Statement stmt = conn.createStatement(); stmt.executeUpdate(query); log.info("Table_ServiceLayer::insertTuple()"); return; }