PublisherAssertionTable xref

View Javadoc

1   /*
2    * Copyright 2001-2004 The Apache Software Foundation.
3    * 
4    * Licensed under the Apache License, Version 2.0 (the "License");
5    * you may not use this file except in compliance with the License.
6    * You may obtain a copy of the License at
7    * 
8    *      http://www.apache.org/licenses/LICENSE-2.0
9    * 
10   * Unless required by applicable law or agreed to in writing, software
11   * distributed under the License is distributed on an "AS IS" BASIS,
12   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13   * See the License for the specific language governing permissions and
14   * limitations under the License.
15   */
16  package org.apache.juddi.datastore.jdbc;
17  
18  import java.sql.Connection;
19  import java.sql.PreparedStatement;
20  import java.sql.ResultSet;
21  import java.util.Vector;
22  
23  import org.apache.commons.logging.Log;
24  import org.apache.commons.logging.LogFactory;
25  import org.apache.juddi.datatype.KeyedReference;
26  import org.apache.juddi.datatype.assertion.PublisherAssertion;
27  import org.apache.juddi.datatype.response.AssertionStatusItem;
28  import org.apache.juddi.datatype.response.CompletionStatus;
29  import org.apache.juddi.datatype.response.KeysOwned;
30  import org.apache.juddi.registry.RegistryEngine;
31  import org.apache.juddi.util.Config;
32  
33  /***
34   * @author Steve Viens (steve@users.sourceforge.net)
35   */
36  class PublisherAssertionTable
37  {
38    // private reference to the jUDDI logger
39    private static Log log = LogFactory.getLog(PublisherAssertionTable.class);
40  
41    static String insertSQL = null;
42    static String selectSQL = null;
43    static String deleteDeadAssertionsSQL = null;
44    static String updateFromCheckSQL = null;
45    static String updateToCheckSQL = null;
46    static String updateFromCheckByFromKeySQL = null;
47    static String updateToCheckByToKeySQL = null;
48    static String selectAssertionsSQL = null;
49    static String selectRelationships = null;
50    static String tablePrefix;
51    
52    static
53    {
54     tablePrefix = Config.getStringProperty(
55         RegistryEngine.PROPNAME_TABLE_PREFIX,RegistryEngine.DEFAULT_TABLE_PREFIX);
56      // buffer used to build SQL statements
57      StringBuffer sql = null;
58  
59      // build insertSQL
60      sql = new StringBuffer(150);
61      sql.append("INSERT INTO ").append(tablePrefix).append("PUBLISHER_ASSERTION (");
62      sql.append("FROM_KEY,");
63      sql.append("TO_KEY,");
64      sql.append("TMODEL_KEY,");
65      sql.append("KEY_NAME,");
66      sql.append("KEY_VALUE,");
67      sql.append("FROM_CHECK,");
68      sql.append("TO_CHECK) ");
69      sql.append("VALUES (?,?,?,?,?,?,?)");
70      insertSQL = sql.toString();
71  
72      // build selectSQL
73      sql = new StringBuffer(200);
74      sql.append("SELECT ");
75      sql.append("FROM_KEY,");
76      sql.append("TO_KEY,");
77      sql.append("TMODEL_KEY,");
78      sql.append("KEY_NAME,");
79      sql.append("KEY_VALUE ");
80      sql.append("FROM ").append(tablePrefix).append("PUBLISHER_ASSERTION ");
81      sql.append("WHERE FROM_KEY=? ");
82      sql.append("AND TO_KEY=? ");
83      sql.append("AND TMODEL_KEY=? ");
84      sql.append("AND KEY_NAME=? ");
85      sql.append("AND KEY_VALUE=?");
86      selectSQL = sql.toString();
87  
88      // build deleteDeadAssertionsSQL
89      sql = new StringBuffer(200);
90      sql.append("DELETE FROM ").append(tablePrefix).append("PUBLISHER_ASSERTION ");
91      sql.append("WHERE FROM_CHECK='false' ");
92      sql.append("AND TO_CHECK='false'");
93      deleteDeadAssertionsSQL = sql.toString();
94  
95      // build updateFromCheckSQL
96      sql = new StringBuffer(200);
97      sql.append("UPDATE ").append(tablePrefix).append("PUBLISHER_ASSERTION ");
98      sql.append("SET FROM_CHECK=? ");
99      sql.append("WHERE FROM_KEY=? ");
100     sql.append("AND TO_KEY=? ");
101     sql.append("AND TMODEL_KEY=? ");
102     sql.append("AND KEY_NAME=? ");
103     sql.append("AND KEY_VALUE=?");
104     updateFromCheckSQL = sql.toString();
105 
106     // build updateToCheckSQL
107     sql = new StringBuffer(200);
108     sql.append("UPDATE ").append(tablePrefix).append("PUBLISHER_ASSERTION ");
109     sql.append("SET TO_CHECK=? ");
110     sql.append("WHERE FROM_KEY=? ");
111     sql.append("AND TO_KEY=? ");
112     sql.append("AND TMODEL_KEY=? ");
113     sql.append("AND KEY_NAME=? ");
114     sql.append("AND KEY_VALUE=?");
115     updateToCheckSQL = sql.toString();
116 
117     // build updateFromCheckByFromKeySQL
118     sql = new StringBuffer(200);
119     sql.append("UPDATE ").append(tablePrefix).append("PUBLISHER_ASSERTION ");
120     sql.append("SET FROM_CHECK=? ");
121     sql.append("WHERE FROM_KEY IN ");
122     updateFromCheckByFromKeySQL = sql.toString();
123 
124     // build updateFromCheckByFromKeySQL
125     sql = new StringBuffer(200);
126     sql.append("UPDATE ").append(tablePrefix).append("PUBLISHER_ASSERTION ");
127     sql.append("SET TO_CHECK=? ");
128     sql.append("WHERE TO_KEY IN ");
129     updateFromCheckByFromKeySQL = sql.toString();
130 
131     // build selectAssertionsSQL
132     sql = new StringBuffer(200);
133     sql.append("SELECT ");
134     sql.append("FROM_KEY,");
135     sql.append("TO_KEY,");
136     sql.append("TMODEL_KEY,");
137     sql.append("KEY_NAME,");
138     sql.append("KEY_VALUE,");
139     sql.append("FROM_CHECK,");
140     sql.append("TO_CHECK ");
141     sql.append("FROM ").append(tablePrefix).append("PUBLISHER_ASSERTION ");
142     selectAssertionsSQL = sql.toString();
143 
144     // build selectRelationships
145     sql = new StringBuffer(200);
146     sql.append("SELECT TMODEL_KEY,KEY_NAME,KEY_VALUE ");
147     sql.append("FROM ").append(tablePrefix).append("PUBLISHER_ASSERTION ");
148     sql.append(
149       "WHERE ((FROM_KEY = ? AND TO_KEY = ?) OR (FROM_KEY = ? AND TO_KEY = ?)) ");
150     sql.append("AND FROM_CHECK = 'true' ");
151     sql.append("AND TO_CHECK = 'true' ");
152     selectRelationships = sql.toString();
153   }
154 
155   /***
156    * Insert new row into the PUBLISHER_ASSERTION table.
157    *
158    * @param assertion Publisher Assertion object holding values to be inserted
159    * @param fromCheck boolean true if the FROM_KEY is owned by the individual 'adding' this assertion (otherwise false).
160    * @param toCheck boolean true if the TO_KEY is owned by the individual 'adding' this assertion (otherwise false).
161    * @param connection JDBC connection
162    * @throws java.sql.SQLException
163    */
164   public static void insert(
165     PublisherAssertion assertion,
166     boolean fromCheck,
167     boolean toCheck,
168     Connection connection)
169     throws java.sql.SQLException
170   {
171     PreparedStatement statement = null;
172 
173     try
174     {
175       // prep insert values
176       String tModelKey = null;
177       String keyedRefName = null;
178       String keyedRefValue = null;
179 
180       if (assertion.getKeyedReference() != null)
181       {
182         tModelKey = assertion.getKeyedReference().getTModelKey();
183         keyedRefName = assertion.getKeyedReference().getKeyName();
184         keyedRefValue = assertion.getKeyedReference().getKeyValue();
185       }
186 
187       statement = connection.prepareStatement(insertSQL);
188       statement.setString(1, assertion.getFromKey());
189       statement.setString(2, assertion.getToKey());
190       statement.setString(3, tModelKey);
191       statement.setString(4, keyedRefName);
192       statement.setString(5, keyedRefValue);
193       statement.setString(6, String.valueOf(fromCheck));
194       statement.setString(7, String.valueOf(toCheck));
195 
196       if (log.isDebugEnabled()) {
197           log.debug(
198             "insert into " + tablePrefix + "PUBLISHER_ASSERTION table:\n\n\t"
199               + insertSQL
200               + "\n\t FROM_KEY="
201               + assertion.getFromKey()
202               + "\n\t TO_KEY="
203               + assertion.getToKey()
204               + "\n\t TMODEL_KEY="
205               + tModelKey
206               + "\n\t KEY_NAME="
207               + keyedRefName
208               + "\n\t KEY_VALUE="
209               + keyedRefValue
210               + "\n\t FROM_CHECK="
211               + fromCheck
212               + "\n\t TO_CHECK="
213               + toCheck
214               + "\n");
215       }
216 
217       // insert
218       statement.executeUpdate();
219     }
220     catch (java.sql.SQLException sqlex)
221     {
222       log.error(sqlex.getMessage());
223       throw sqlex;
224     }
225     finally
226     {
227       try
228       {
229         statement.close();
230       }
231       catch (Exception e)
232       { /* ignored */
233       }
234     }
235   }
236 
237   /***
238    * Select one row from the PUBLISHER_ASSERTION table.
239    *
240    * @param assertionIn
241    * @param connection JDBC connection
242    * @throws java.sql.SQLException
243    */
244   public static PublisherAssertion select(
245     PublisherAssertion assertionIn,
246     Connection connection)
247     throws java.sql.SQLException
248   {
249     PublisherAssertion assertionOut = null;
250     PreparedStatement statement = null;
251     ResultSet resultSet = null;
252 
253     try
254     {
255       KeyedReference keyedRefIn = assertionIn.getKeyedReference();
256 
257       statement = connection.prepareStatement(selectSQL);
258       statement.setString(1, assertionIn.getFromKey());
259       statement.setString(2, assertionIn.getToKey());
260       statement.setString(3, keyedRefIn.getTModelKey());
261       statement.setString(4, keyedRefIn.getKeyName());
262       statement.setString(5, keyedRefIn.getKeyValue());
263 
264       if (log.isDebugEnabled()) {
265           log.debug(
266             "select from " + tablePrefix + "PUBLISHER_ASSERTION table:\n\n\t"
267               + selectSQL
268               + "\n\t FROM_KEY="
269               + assertionIn.getFromKey()
270               + "\n\t TO_KEY="
271               + assertionIn.getToKey()
272               + "\n\t TMODEL_KEY="
273               + keyedRefIn.getTModelKey()
274               + "\n\t KEY_NAME="
275               + keyedRefIn.getKeyName()
276               + "\n\t KEY_VALUE="
277               + keyedRefIn.getKeyValue()
278               + "\n");
279       }
280 
281       resultSet = statement.executeQuery();
282       if (resultSet.next())
283       {
284         KeyedReference keyedRefOut = new KeyedReference();
285         keyedRefOut.setKeyName(resultSet.getString(4)); //("KEY_NAME"));
286         keyedRefOut.setKeyValue(resultSet.getString(5)); //("KEY_VALUE"));
287         keyedRefOut.setTModelKey(resultSet.getString(3)); //("TMODEL_KEY"));
288 
289         assertionOut = new PublisherAssertion();
290         assertionOut.setFromKey(resultSet.getString(1)); //("FROM_KEY"));
291         assertionOut.setToKey(resultSet.getString(2)); //("TO_KEY"));
292         assertionOut.setKeyedReference(keyedRefOut);
293       }
294 
295       return assertionOut;
296     }
297     catch (java.sql.SQLException sqlex)
298     {
299       log.error(sqlex.getMessage());
300       throw sqlex;
301     }
302     finally
303     {
304       try
305       {
306         resultSet.close();
307       }
308       catch (Exception e)
309       { /* ignored */
310       }
311       try
312       {
313         statement.close();
314       }
315       catch (Exception e)
316       { /* ignored */
317       }
318     }
319   }
320 
321   /***
322    * Delete row from the PUBLISHER_ASSERTION table.
323    *
324    * @throws java.sql.SQLException
325    */
326   public static void deleteDeadAssertions(Connection connection)
327     throws java.sql.SQLException
328   {
329     PreparedStatement statement = null;
330 
331     try
332     {
333       // prepare the delete
334       statement = connection.prepareStatement(deleteDeadAssertionsSQL);
335 
336       if (log.isDebugEnabled()) {
337           log.debug(
338             "delete from " + tablePrefix + "PUBLISHER_ASSERTION table:\n\n\t"
339               + deleteDeadAssertionsSQL
340               + "\n");
341       }
342 
343       // execute
344       statement.executeUpdate();
345     }
346     catch (java.sql.SQLException sqlex)
347     {
348       log.error(sqlex.getMessage());
349       throw sqlex;
350     }
351     finally
352     {
353       try
354       {
355         statement.close();
356       }
357       catch (Exception e)
358       { /* ignored */
359       }
360     }
361   }
362 
363   /***
364    * Update the FROM_CHECK column in the PUBLISHER_ASSERTION table for a
365    * particular PublisherAssertion.
366    *
367    * @param  assertion The PublisherAssertion to update BusinessKey
368    * @param  fromCheck The value to set the FROM_CHECK column to.
369    * @param  connection JDBC connection
370    * @throws java.sql.SQLException
371    */
372   public static void updateFromCheck(
373     PublisherAssertion assertion,
374     boolean fromCheck,
375     Connection connection)
376     throws java.sql.SQLException
377   {
378     KeyedReference keyedRef = assertion.getKeyedReference();
379     PreparedStatement statement = null;
380     ResultSet resultSet = null;
381 
382     try
383     {
384       if (log.isDebugEnabled()) {
385           log.debug(
386             "update " + tablePrefix + "PUBLISHER_ASSERTION table:\n\n\t"
387               + updateFromCheckSQL
388               + "\n\t FROM_CHECK="
389               + String.valueOf(fromCheck)
390               + "\n\t FROM_KEY="
391               + assertion.getFromKey()
392               + "\n\t TO_KEY="
393               + assertion.getToKey()
394               + "\n\t TMODEL_KEY="
395               + keyedRef.getTModelKey()
396               + "\n\t KEY_NAME="
397               + keyedRef.getKeyName()
398               + "\n\t KEY_VALUE="
399               + keyedRef.getKeyValue()
400               + "\n");
401       }
402 
403       // create a statement to query with
404       statement = connection.prepareStatement(updateFromCheckSQL);
405       statement.setString(1, String.valueOf(fromCheck));
406       statement.setString(2, assertion.getFromKey());
407       statement.setString(3, assertion.getToKey());
408       statement.setString(4, keyedRef.getTModelKey());
409       statement.setString(5, keyedRef.getKeyName());
410       statement.setString(6, keyedRef.getKeyValue());
411 
412       // execute
413       statement.executeUpdate();
414     }
415     catch (java.sql.SQLException sqlex)
416     {
417       log.error(sqlex.getMessage());
418       throw sqlex;
419     }
420     finally
421     {
422       try
423       {
424         resultSet.close();
425       }
426       catch (Exception e)
427       { /* ignored */
428       }
429       try
430       {
431         statement.close();
432       }
433       catch (Exception e)
434       { /* ignored */
435       }
436     }
437   }
438 
439   /***
440    * Update the TO_CHECK column in the PUBLISHER_ASSERTION table
441    * for a particular PublisherAssertion.
442    *
443    * @param assertion The PublisherAssertion to update BusinessKey
444    * @param toCheck The value to set the TO_CHECK column to.
445    * @param connection JDBC connection
446    * @throws java.sql.SQLException
447    */
448   public static void updateToCheck(
449     PublisherAssertion assertion,
450     boolean toCheck,
451     Connection connection)
452     throws java.sql.SQLException
453   {
454     KeyedReference keyedRef = assertion.getKeyedReference();
455     PreparedStatement statement = null;
456     ResultSet resultSet = null;
457 
458     try
459     {
460       if (log.isDebugEnabled()) {
461           log.debug(
462             "update " + tablePrefix + "PUBLISHER_ASSERTION table:\n\n\t"
463               + updateToCheckSQL
464               + "\n\t TO_CHECK="
465               + String.valueOf(toCheck)
466               + "\n\t FROM_KEY="
467               + assertion.getFromKey()
468               + "\n\t TO_KEY="
469               + assertion.getToKey()
470               + "\n\t TMODEL_KEY="
471               + keyedRef.getTModelKey()
472               + "\n\t KEY_NAME="
473               + keyedRef.getKeyName()
474               + "\n\t KEY_VALUE="
475               + keyedRef.getKeyValue()
476               + "\n");
477       }
478 
479       // create a statement to query with
480       statement = connection.prepareStatement(updateToCheckSQL);
481       statement.setString(1, String.valueOf(toCheck));
482       statement.setString(2, assertion.getFromKey());
483       statement.setString(3, assertion.getToKey());
484       statement.setString(4, keyedRef.getTModelKey());
485       statement.setString(5, keyedRef.getKeyName());
486       statement.setString(6, keyedRef.getKeyValue());
487 
488       // execute
489       statement.executeUpdate();
490     }
491     catch (java.sql.SQLException sqlex)
492     {
493       log.error(sqlex.getMessage());
494       throw sqlex;
495     }
496     finally
497     {
498       try
499       {
500         resultSet.close();
501       }
502       catch (Exception e)
503       { /* ignored */
504       }
505       try
506       {
507         statement.close();
508       }
509       catch (Exception e)
510       { /* ignored */
511       }
512     }
513   }
514 
515   /***
516    * Update the FROM_CHECK column for all rows from in the PUBLISHER_ASSERTION
517    * table whose FROM_KEY is in the Vector of BusinessKeys passed in.
518    *
519    * @param  fromKeysIn A Vector of BusinessKeys to update
520    * @param  fromCheck The value to set the FROM_CHECK column to
521    * @param  connection JDBC connection
522    * @throws java.sql.SQLException
523    */
524   public static void updateFromCheckByFromKey(
525     Vector fromKeysIn,
526     boolean fromCheck,
527     Connection connection)
528     throws java.sql.SQLException
529   {
530     StringBuffer sql = new StringBuffer();
531     sql.append(updateFromCheckByFromKeySQL);
532     sql.append("WHERE FROM_KEY IN ");
533     appendIn(sql, fromKeysIn);
534 
535     PreparedStatement statement = null;
536     ResultSet resultSet = null;
537 
538     try
539     {
540       // prepare
541       statement = connection.prepareStatement(sql.toString());
542       statement.setString(1, String.valueOf(fromCheck));
543 
544       if (log.isDebugEnabled()) {
545           log.debug(
546             "update " + tablePrefix + "PUBLISHER_ASSERTION table:\n\n\t" + sql.toString() + "\n");
547       }
548 
549       // execute
550       statement.executeUpdate();
551     }
552     catch (java.sql.SQLException sqlex)
553     {
554       log.error(sqlex.getMessage());
555       throw sqlex;
556     }
557     finally
558     {
559       try
560       {
561         resultSet.close();
562       }
563       catch (Exception e)
564       { /* ignored */
565       }
566       try
567       {
568         statement.close();
569       }
570       catch (Exception e)
571       { /* ignored */
572       }
573     }
574   }
575 
576   /***
577    * Update the TO_CHECK column for all rows from in the PUBLISHER_ASSERTION
578    * table whose TO_KEY is in the Vector of BusinessKeys passed in.
579    *
580    * @param toKeysIn A Vector of BusinessKeys to update
581    * @param toCheck The value to set the TO_KEY column to
582    * @param connection JDBC connection
583    * @throws java.sql.SQLException
584    */
585   public static void updateToCheckByToKey(
586     Vector toKeysIn,
587     boolean toCheck,
588     Connection connection)
589     throws java.sql.SQLException
590   {
591     StringBuffer sql = new StringBuffer();
592     sql.append(updateFromCheckByFromKeySQL);
593     sql.append("WHERE TO_KEY IN ");
594     appendIn(sql, toKeysIn);
595 
596     PreparedStatement statement = null;
597     ResultSet resultSet = null;
598 
599     try
600     {
601       // create a statement to query with
602       statement = connection.prepareStatement(sql.toString());
603       statement.setString(1, String.valueOf(toCheck));
604 
605       if (log.isDebugEnabled()) {
606           log.debug(
607             "update " + tablePrefix + "PUBLISHER_ASSERTION table:\n\n\t" + sql.toString() + "\n");
608       }
609 
610       // execute
611       statement.executeUpdate();
612     }
613     catch (java.sql.SQLException sqlex)
614     {
615       log.error(sqlex.getMessage());
616       throw sqlex;
617     }
618     finally
619     {
620       try
621       {
622         resultSet.close();
623       }
624       catch (Exception e)
625       { /* ignored */
626       }
627       try
628       {
629         statement.close();
630       }
631       catch (Exception e)
632       { /* ignored */
633       }
634     }
635   }
636 
637   /***
638    * Select any rows from the PUBLISHER_ASSERTION table where the FROM_KEY
639    * or TO_KEY column value is found in the Vector of BusinessKeys passed in
640    * and return the results as a Vector of assertionStatusItem instances.
641    *
642    * The assertionStatusItems returned represent PublisherAssertions in
643    * which the fromKey and toKey are both are under the control of a
644    * particular Publisher.
645    *
646    * NOTE: Each AssertionStatusItem returned from this method will have a
647    *       completion stauts of 'status:complete' because only assertions
648    *       in which both business entities are managed (was published) by
649    *       same publisher.
650    *
651    * @param keysIn Vector business keys to look for in the FROM_KEY and TO_KEY column.
652    * @param connection JDBC connection
653    * @throws java.sql.SQLException
654    */
655   public static Vector selectBothKeysOwnedAssertion(
656     Vector keysIn,
657     Connection connection)
658     throws java.sql.SQLException
659   {
660     if ((keysIn == null) || (keysIn.size() == 0))
661       return null;
662 
663     StringBuffer sql = new StringBuffer();
664     sql.append(selectAssertionsSQL);
665     sql.append("WHERE FROM_KEY IN ");
666     appendIn(sql, keysIn);
667     sql.append("AND TO_KEY IN ");
668     appendIn(sql, keysIn);
669 
670     Vector itemList = new Vector();
671     PreparedStatement statement = null;
672     ResultSet resultSet = null;
673 
674     try
675     {
676       statement = connection.prepareStatement(sql.toString());
677 
678       if (log.isDebugEnabled()) {
679           log.debug(
680             "select from " + tablePrefix + "PUBLISHER_ASSERTION table:\n\n\t" + selectSQL + "\n");
681       }
682 
683       resultSet = statement.executeQuery();
684       while (resultSet.next())
685       {
686         AssertionStatusItem item = new AssertionStatusItem();
687         item.setFromKey(resultSet.getString(1)); //("FROM_KEY"));
688         item.setToKey(resultSet.getString(2)); //("TO_KEY"));
689 
690         // construct and set the KeyedReference instance
691         KeyedReference keyedRef = new KeyedReference();
692         keyedRef.setTModelKey(resultSet.getString(3)); //("TMODEL_KEY"));
693         keyedRef.setKeyName(resultSet.getString(4)); //("KEY_NAME"));
694         keyedRef.setKeyValue(resultSet.getString(5)); //("KEY_VALUE"));
695         item.setKeyedReference(keyedRef);
696 
697         // construct and set the KeysOwned instance
698         KeysOwned keysOwned = new KeysOwned();
699         keysOwned.setFromKey(item.getFromKey());
700         keysOwned.setToKey(item.getToKey());
701         item.setKeysOwned(keysOwned);
702 
703         // determine & set the 'completionStatus' (always 'status:complete' here)
704         item.setCompletionStatus(
705           new CompletionStatus(CompletionStatus.COMPLETE));
706 
707         // add the assertionStatusItem
708         itemList.addElement(item);
709       }
710 
711       return itemList;
712     }
713     catch (java.sql.SQLException sqlex)
714     {
715       log.error(sqlex.getMessage());
716       throw sqlex;
717     }
718     finally
719     {
720       try
721       {
722         resultSet.close();
723       }
724       catch (Exception e)
725       { /* ignored */
726       }
727       try
728       {
729         statement.close();
730       }
731       catch (Exception e)
732       { /* ignored */
733       }
734     }
735   }
736 
737   /***
738    * Select any rows from the PUBLISHER_ASSERTION table where the FROM_KEY column
739    * DOES CONTAIN one of the business keys found in the Vector of keys passed in
740    * and the TO_KEY column DOES NOT CONTAIN one of the business keys from the same
741    * Vector of keys. Return the results as a Vector of assertionStatusItem instances.
742    *
743    * The assertionStatusItems returned represent PublisherAssertions in
744    * which ONLY the "fromKey" is under the control of a particular Publisher.
745 
746    * @param keysIn Vector business keys to look for in the FROM_KEY and TO_KEY column.
747    * @param connection JDBC connection
748    * @throws java.sql.SQLException
749    */
750   public static Vector selectFromKeyOwnedAssertion(
751     Vector keysIn,
752     Connection connection)
753     throws java.sql.SQLException
754   {
755     if ((keysIn == null) || (keysIn.size() == 0))
756       return null;
757 
758     StringBuffer sql = new StringBuffer();
759     sql.append(selectAssertionsSQL);
760     sql.append("WHERE FROM_KEY IN ");
761     appendIn(sql, keysIn);
762     sql.append("AND TO_KEY NOT IN ");
763     appendIn(sql, keysIn);
764 
765     Vector itemList = new Vector();
766     PreparedStatement statement = null;
767     ResultSet resultSet = null;
768 
769     try
770     {
771       statement = connection.prepareStatement(sql.toString());
772 
773       if (log.isDebugEnabled()) {
774           log.debug(
775             "select from " + tablePrefix + "PUBLISHER_ASSERTION table:\n\n\t" + selectSQL + "\n");
776       }
777 
778       resultSet = statement.executeQuery();
779       while (resultSet.next())
780       {
781         AssertionStatusItem item = new AssertionStatusItem();
782         item.setFromKey(resultSet.getString(1)); //("FROM_KEY"));
783         item.setToKey(resultSet.getString(2)); //("TO_KEY"));
784 
785         // construct and set the KeyedReference instance
786         KeyedReference keyedRef = new KeyedReference();
787         keyedRef.setTModelKey(resultSet.getString(3)); //("TMODEL_KEY"));
788         keyedRef.setKeyName(resultSet.getString(4)); //("KEY_NAME"));
789         keyedRef.setKeyValue(resultSet.getString(5)); //("KEY_VALUE"));
790         item.setKeyedReference(keyedRef);
791 
792         // construct and set the KeysOwned instance
793         KeysOwned keysOwned = new KeysOwned();
794         keysOwned.setFromKey(item.getFromKey());
795         keysOwned.setToKey(null);
796         item.setKeysOwned(keysOwned);
797 
798         // determine and set the assertions 'completionStatus'
799         CompletionStatus status = null;
800         boolean fromCheck =
801           new Boolean(resultSet.getString(6)).booleanValue();//("FROM_CHECK")
802         boolean toCheck =
803           new Boolean(resultSet.getString(7)).booleanValue();//("TO_CHECK")
804         if ((fromCheck) && (toCheck))
805           status = new CompletionStatus(CompletionStatus.COMPLETE);
806         else if ((fromCheck) && (!toCheck))
807           status = new CompletionStatus(CompletionStatus.TOKEY_INCOMPLETE);
808         else if ((!fromCheck) && (toCheck))
809           status = new CompletionStatus(CompletionStatus.FROMKEY_INCOMPLETE);
810         item.setCompletionStatus(status);
811 
812         // add the assertionStatusItem
813         itemList.addElement(item);
814       }
815 
816       return itemList;
817     }
818     catch (java.sql.SQLException sqlex)
819     {
820       log.error(sqlex.getMessage());
821       throw sqlex;
822     }
823     finally
824     {
825       try
826       {
827         resultSet.close();
828       }
829       catch (Exception e)
830       { /* ignored */
831       }
832       try
833       {
834         statement.close();
835       }
836       catch (Exception e)
837       { /* ignored */
838       }
839     }
840   }
841 
842   /***
843    * Select any rows from the PUBLISHER_ASSERTION table where the FROM_KEY column
844    * DOES NOT CONTAIN one of the business keys found in the Vector of keys passed
845    * in and the TO_KEY column DOES CONTAIN one of the business keys from the same
846    * Vector of keys. Return the results as a Vector of assertionStatusItem instances.
847    *
848    * The assertionStatusItems returned represent PublisherAssertions in
849    * which ONLY the "toKey" is under the control of a particular Publisher.
850    *
851    * @param keysIn Vector business keys to look for in the FROM_KEY and TO_KEY column.
852    * @param connection JDBC connection
853    * @throws java.sql.SQLException
854    */
855   public static Vector selectToKeyOwnedAssertion(
856     Vector keysIn,
857     Connection connection)
858     throws java.sql.SQLException
859   {
860     if ((keysIn == null) || (keysIn.size() == 0))
861       return null;
862 
863     StringBuffer sql = new StringBuffer();
864     sql.append(selectAssertionsSQL);
865     sql.append("WHERE FROM_KEY NOT IN ");
866     appendIn(sql, keysIn);
867     sql.append("AND TO_KEY IN ");
868     appendIn(sql, keysIn);
869 
870     Vector itemList = new Vector();
871     PreparedStatement statement = null;
872     ResultSet resultSet = null;
873 
874     try
875     {
876       statement = connection.prepareStatement(sql.toString());
877 
878       if (log.isDebugEnabled()) {
879           log.debug(
880             "select from " + tablePrefix + "PUBLISHER_ASSERTION table:\n\n\t" + selectSQL + "\n");
881       }
882 
883       resultSet = statement.executeQuery();
884       while (resultSet.next())
885       {
886         AssertionStatusItem item = new AssertionStatusItem();
887         item.setFromKey(resultSet.getString(1));//("FROM_KEY"));
888         item.setToKey(resultSet.getString(2));//("TO_KEY"));
889 
890         // construct and set the KeyedReference instance
891         KeyedReference keyedRef = new KeyedReference();
892         keyedRef.setKeyName(resultSet.getString(4));//("KEY_NAME"));
893         keyedRef.setKeyValue(resultSet.getString(5));//("KEY_VALUE"));
894         keyedRef.setTModelKey(resultSet.getString(3));//("TMODEL_KEY"));
895         item.setKeyedReference(keyedRef);
896 
897         // construct and set the KeysOwned instance
898         KeysOwned keysOwned = new KeysOwned();
899         keysOwned.setFromKey(null);
900         keysOwned.setToKey(item.getToKey());
901         item.setKeysOwned(keysOwned);
902 
903         // determine and set the assertions 'completionStatus'
904         CompletionStatus status = null;
905         boolean fromCheck =
906           new Boolean(resultSet.getString(6)).booleanValue();//("FROM_CHECK"));
907         boolean toCheck =
908           new Boolean(resultSet.getString(7)).booleanValue();//("TO_CHECK"));
909         if ((fromCheck) && (toCheck))
910           status = new CompletionStatus(CompletionStatus.COMPLETE);
911         else if ((fromCheck) && (!toCheck))
912           status = new CompletionStatus(CompletionStatus.TOKEY_INCOMPLETE);
913         else if ((!fromCheck) && (toCheck))
914           status = new CompletionStatus(CompletionStatus.FROMKEY_INCOMPLETE);
915         item.setCompletionStatus(status);
916 
917         // add the assertionStatusItem
918         itemList.addElement(item);
919       }
920 
921       return itemList;
922     }
923     catch (java.sql.SQLException sqlex)
924     {
925       log.error(sqlex.getMessage());
926       throw sqlex;
927     }
928     finally
929     {
930       try
931       {
932         resultSet.close();
933       }
934       catch (Exception e)
935       { /* ignored */
936       }
937       try
938       {
939         statement.close();
940       }
941       catch (Exception e)
942       { /* ignored */
943       }
944     }
945   }
946 
947   /***
948    * Select any rows from the PUBLISHER_ASSERTION table where the FROM_KEY column
949    * CONTAINS one of the business keys found in the Vector of keys passed in OR
950    * the TO_KEY column CONTAINS one of the business keys from the same Vector
951    * f keys. Return the results as a Vector of PublisherAssertion instances.
952    *
953    * @param keysIn Vector business keys to look for in the FROM_KEY and TO_KEY column.
954    * @param connection JDBC connection
955    * @throws java.sql.SQLException
956    */
957   public static Vector selectAssertions(Vector keysIn, Connection connection)
958     throws java.sql.SQLException
959   {
960     if ((keysIn == null) || (keysIn.size() == 0))
961       return null;
962 
963     StringBuffer sql = new StringBuffer();
964     sql.append(selectAssertionsSQL);
965     sql.append("WHERE (FROM_KEY IN ");
966     appendIn(sql, keysIn);
967     sql.append("AND FROM_CHECK = 'true') ");
968     sql.append("OR (TO_KEY IN ");
969     appendIn(sql, keysIn);
970     sql.append("AND TO_CHECK = 'true')");
971 
972     Vector assertionList = new Vector();
973     PreparedStatement statement = null;
974     ResultSet resultSet = null;
975 
976     try
977     {
978       statement = connection.prepareStatement(sql.toString());
979 
980       if (log.isDebugEnabled()) {
981           log.debug(
982             "select from " + tablePrefix + "PUBLISHER_ASSERTION table:\n\n\t" + selectSQL + "\n");
983       }
984 
985       resultSet = statement.executeQuery();
986       while (resultSet.next())
987       {
988         PublisherAssertion assertion = new PublisherAssertion();
989         assertion.setFromKey(resultSet.getString(1));//("FROM_KEY"));
990         assertion.setToKey(resultSet.getString(2));//("TO_KEY"));
991 
992         // construct and set the KeyedReference instance
993         KeyedReference keyedRef = new KeyedReference();
994         keyedRef.setKeyName(resultSet.getString(4));//("KEY_NAME"));
995         keyedRef.setKeyValue(resultSet.getString(5));//("KEY_VALUE"));
996         keyedRef.setTModelKey(resultSet.getString(3));//("TMODEL_KEY"));
997         assertion.setKeyedReference(keyedRef);
998 
999         // add the assertionStatusItem
1000         assertionList.addElement(assertion);
1001       }
1002 
1003       return assertionList;
1004     }
1005     catch (java.sql.SQLException sqlex)
1006     {
1007       log.error(sqlex.getMessage());
1008       throw sqlex;
1009     }
1010     finally
1011     {
1012       try
1013       {
1014         resultSet.close();
1015       }
1016       catch (Exception e)
1017       { /* ignored */
1018       }
1019       try
1020       {
1021         statement.close();
1022       }
1023       catch (Exception e)
1024       { /* ignored */
1025       }
1026     }
1027   }
1028 
1029   /***
1030    * Retrieve the TMODEL_KEY, KEY_NAME and KEY_VALUE from all assertions
1031    * where the FROM_KEY = businessKey and the TO_KEY = relatedBusinessKey
1032    * parameters or the FROM_KEY = relatedBusinessKey and the TO_KEY =
1033    * businessKey.
1034    *
1035    * @param businessKey The BusinessKey we're searching for relationships to.
1036    * @param relatedKey The BusinessKey of the related BusinessEntity.
1037    * @param connection JDBC connection
1038    * @throws java.sql.SQLException
1039    */
1040   public static Vector selectRelatedBusinesses(
1041     String businessKey,
1042     String relatedKey,
1043     Connection connection)
1044     throws java.sql.SQLException
1045   {
1046     Vector refList = new Vector();
1047     PreparedStatement statement = null;
1048     ResultSet resultSet = null;
1049 
1050     try
1051     {
1052       statement = connection.prepareStatement(selectRelationships);
1053       statement.setString(1, businessKey);
1054       statement.setString(2, relatedKey);
1055       statement.setString(3, relatedKey);
1056       statement.setString(4, businessKey);
1057 
1058       if (log.isDebugEnabled()) {
1059           log.debug(
1060             "select from " + tablePrefix + "PUBLISHER_ASSERTION table:\n\n\t"
1061               + selectRelationships
1062               + "\n\t BUSINESS_KEY="
1063               + businessKey.toString()
1064               + "\n\t RELATED_BUSINESS_KEY="
1065               + relatedKey.toString()
1066               + "\n");
1067       }
1068 
1069       resultSet = statement.executeQuery();
1070       if (resultSet.next())
1071       {
1072         KeyedReference keyedRef = new KeyedReference();
1073         keyedRef.setKeyName(resultSet.getString(2));//("KEY_NAME"));
1074         keyedRef.setKeyValue(resultSet.getString(3));//("KEY_VALUE"));
1075         keyedRef.setTModelKey(resultSet.getString(1));//("TMODEL_KEY"));
1076 
1077         // add the KeyedRef to the Vector
1078         refList.addElement(keyedRef);
1079       }
1080 
1081       return refList;
1082     }
1083     catch (java.sql.SQLException sqlex)
1084     {
1085       log.error(sqlex.getMessage());
1086       throw sqlex;
1087     }
1088     finally
1089     {
1090       try
1091       {
1092         resultSet.close();
1093       }
1094       catch (Exception e)
1095       { /* ignored */
1096       }
1097       try
1098       {
1099         statement.close();
1100       }
1101       catch (Exception e)
1102       { /* ignored */
1103       }
1104     }
1105   }
1106 
1107   /***
1108    * Utility method used to construct SQL "IN" statements such as
1109    * the following SQL example:
1110    *
1111    *   SELECT * FROM TABLE WHERE MONTH IN ('jan','feb','mar')
1112    *
1113    * @param sql StringBuffer to append the final results to
1114    * @param keysIn Vector of Strings used to construct the "IN" clause
1115    */
1116   private static void appendIn(StringBuffer sql, Vector keysIn)
1117   {
1118     if (keysIn == null)
1119       return;
1120 
1121     sql.append("(");
1122 
1123     int keyCount = keysIn.size();
1124     for (int i = 0; i < keyCount; i++)
1125     {
1126       String key = (String) keysIn.elementAt(i);
1127       sql.append("'").append(key).append("'");
1128 
1129       if ((i + 1) < keyCount)
1130         sql.append(",");
1131     }
1132 
1133     sql.append(") ");
1134   }
1135 }