/** 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 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()"); }
/** * 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()"); }
/** * 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; }
public static void main(String[] args) { // --- start action ----------------------------------- System.out.println("START class Table_ServiceLayer component test"); // --- cmd line parameter check ------------------------ if (args.length != 6) { System.err.println("Usage: Table_ServiceLayer msgFile url user password jdbc debugLevel"); return; } String msgFile = args[0]; String url = args[1]; String usr = args[2]; String pwd = args[3]; String jdbc = args[4]; System.out.println( "Parameters: msgFile=" + msgFile + ", url=" + url + ", user="******", password="******", jdbc=" + jdbc); try { // --- test preparation ------------------------------- CommanderConnection c = new CommanderConnection(url, usr, pwd, jdbc); // - set up service tuples Table_Service myService1 = new Table_Service(); int srv1 = myService1.insertTuple( c, UPDATESEQUENCE1, AVAILABILITY1, NAME1, TITLE1, ABSTRACT1, KEYWORDS1, FEES1, ACCESSCONSTRAINTS1, HOSTNAME1, PORT1, PATH1, FORMATS1, BASELAYERNAME1, VENDORCAPABILITIES1, CONTACTPERSON1, CONTACTORGANIZATION1, ADDRESSTYPE1, ADDRESS1, CITY1, STATE1, POSTCODE1, COUNTRY1, TEL1, FAX1, EMAIL1); System.out.println("insert service #1 -> " + srv1); Table_Service myService2 = new Table_Service(); int srv2 = myService2.insertTuple( c, UPDATESEQUENCE2, AVAILABILITY2, NAME2, TITLE2, ABSTRACT2, KEYWORDS2, FEES2, ACCESSCONSTRAINTS2, HOSTNAME2, PORT2, PATH2, FORMATS2, BASELAYERNAME2, VENDORCAPABILITIES2, CONTACTPERSON2, CONTACTORGANIZATION2, ADDRESSTYPE2, ADDRESS2, CITY2, STATE2, POSTCODE2, COUNTRY2, TEL2, FAX2, EMAIL2); System.out.println("insert service #2 -> " + srv2); // - set up layer tuples Table_Layer myLayer1 = new Table_Layer(); int lay1 = myLayer1.insertTuple( c, LNAME1, LTITLE1, SRS1, AUTHORITY1, LATLONXMIN1, LATLONXMAX1, LATLONYMIN1, LATLONYMAX1, BBOXXMIN1, BBOXXMAX1, BBOXYMIN1, BBOXYMAX1, ATTRIBUTIONURL1, ATTRIBUTIONTITLE1, LOGOWIDTH1, LOGOHEIGHT1, LOGOFORMAT1, LOGOURL1, FEATUREURL1, RESOLUTION1, MAPTYPE1); System.out.println("insert layer #1 -> " + lay1); Table_Layer myLayer2 = new Table_Layer(); int lay2 = myLayer2.insertTuple( c, LNAME2, LTITLE2, SRS2, AUTHORITY2, LATLONXMIN2, LATLONXMAX2, LATLONYMIN2, LATLONYMAX2, BBOXXMIN2, BBOXXMAX2, BBOXYMIN2, BBOXYMAX2, ATTRIBUTIONURL2, ATTRIBUTIONTITLE2, LOGOWIDTH2, LOGOHEIGHT2, LOGOFORMAT2, LOGOURL2, FEATUREURL2, RESOLUTION2, MAPTYPE2); System.out.println("insert layer #2 -> " + lay2); // --- test ------------------------------------------- // - create a reference instance Table_ServiceLayer myServiceLayer1 = new Table_ServiceLayer(); myServiceLayer1.insertTuple(c, srv1, lay1, SEQ1); System.out.println("insert Table_ServiceLayer tuple #1 with seq=" + SEQ1); // - update reference instance myServiceLayer1.updateTuple(c, srv1, lay1, SEQ2); // - fetch values via 2nd instance, check all values Table_ServiceLayer myServiceLayer2 = new Table_ServiceLayer(); myServiceLayer2.fetchTupleById(c, srv1, lay1); if (myServiceLayer2.getServiceId() != srv1) System.out.println( "Error: attribute updated != attributed fetched: " + Globals.TABLE_SERVICELAYER_SERVICEID); if (myServiceLayer2.getLayerId() != lay1) System.out.println( "Error: attribute updated != attributed fetched: " + Globals.TABLE_SERVICELAYER_LAYERID); if (myServiceLayer2.getLayerSeq() != SEQ2) System.out.println( "Error: attribute updated != attributed fetched: " + Globals.TABLE_SERVICELAYER_LAYERID); // - delete instance 1 and check it's gone myServiceLayer1.deleteTuple(c, srv1, lay1); try { myServiceLayer2.fetchTupleById(c, srv1, lay1); System.out.println("Error: deleted tuple still seems to exist."); } catch (Exception e) { System.out.println("fetching tuple deleted caused exception, that's ok."); } // - create second instance myServiceLayer2.insertTuple(c, srv1, lay2, SEQ1); System.out.println("insert Table_ServiceLayer tuple #1 with seq=" + SEQ1); // --- test cleanup ----------------------------------- c.abort(); c.close(); } catch (Exception e) { System.out.println("Error: caught exception: " + e.getMessage()); } System.out.println("END class Table_ServiceLayer component test"); }