/** 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;
}
