Ejemplo n.º 1
1
  @Test
  public void testNewResultSet_GetMergeCost() throws Exception {

    // Mocks
    ConnectionManager connectionManager = mock(ConnectionManager.class);
    Connection connection = mock(Connection.class);
    PreparedStatement preparedStatement = mock(PreparedStatement.class);
    java.sql.ResultSet resultSet = mock(java.sql.ResultSet.class);

    // Behaviour
    when(connectionManager.getConnection(any(SQLiteIndex.class))).thenReturn(connection);
    when(connection.prepareStatement(
            "SELECT COUNT(objectKey) FROM " + TABLE_NAME + " WHERE value = ?;"))
        .thenReturn(preparedStatement);
    when(preparedStatement.executeQuery()).thenReturn(resultSet);
    when(resultSet.getStatement()).thenReturn(preparedStatement);
    when(resultSet.next()).thenReturn(true);
    when(resultSet.getInt(1)).thenReturn(3);

    // Iterator
    ResultSet<Car> carsWithAbs =
        new SQLiteIndex<String, Car, Integer>(
                Car.FEATURES, OBJECT_TO_ID, ID_TO_OBJECT, connectionManager)
            .retrieve(equal(Car.FEATURES, "abs"), new QueryOptions());

    Assert.assertNotNull(carsWithAbs);
    int size = carsWithAbs.getMergeCost();

    assertEquals(3, size);
    verify(connection, times(1)).close();
  }
Ejemplo n.º 2
0
    /**
     * Returns the reults as an ArrayList of Record objects. The Result set is closed within this
     * call
     *
     * @param maxRows if the result set contains more than this param then an exception is thrown
     */
    public ArrayList<DataPoint> getArrayList(int maxRows) {
      ArrayList<DataPoint> results = new ArrayList<DataPoint>();
      int count = 0;

      try {
        if (m_onFirstResult) {
          count++;
          results.add(factory.newDataPoint(m_resultSet));
        }

        while (m_resultSet.next() && (count < maxRows)) {
          count++;
          results.add(factory.newDataPoint(m_resultSet));
        }

        if (m_resultSet.next())
          throw new GenOrmException(
              "Bound of " + maxRows + " is too small for query [" + m_query + "]");
      } catch (java.sql.SQLException sqle) {
        sqle.printStackTrace();
        throw new GenOrmException(sqle);
      }

      close();
      return (results);
    }
Ejemplo n.º 3
0
  public Vector buscar(String pidConsulta) throws java.sql.SQLException, Exception {
    Medicina medicina = null;
    Vector medicinas;
    java.sql.ResultSet rs;
    String sql;
    sql =
        "SELECT id, nombre,dosis, numDias, FK_idConsulta, fechaInicio, fechaFinal, precauciones "
            + "FROM TMedicina "
            + "WHERE FK_idConsulta='"
            + pidConsulta
            + "';";
    rs = Conector.getConector().ejecutarSQL(sql, true);
    medicinas = new Vector<Medicina>();
    if (rs.next()) {
      do {
        medicina =
            new Medicina(
                rs.getString("nombre"),
                rs.getString("dosis"),
                rs.getString("numDias"),
                rs.getString("FK_idConsulta"),
                rs.getString("fechaInicio"),
                rs.getString("fechaFinal"),
                rs.getString("precauciones"));
        medicinas.add(medicina);
      } while (rs.next());
    } else {
      throw new Exception("No hay examenes de ese paciente.");
    }
    rs.close();

    return medicinas;
  }
Ejemplo n.º 4
0
    /**
     * This call expects only one record in the result set. If multiple records are found an
     * excpetion is thrown. The ResultSet object is automatically closed by this call.
     */
    public DataPoint getOnlyRecord() {
      DataPoint ret = null;

      try {
        if (m_resultSet.next()) ret = factory.newDataPoint(m_resultSet);

        if (m_resultSet.next())
          throw new GenOrmException("Multiple rows returned in call from DataPoint.getOnlyRecord");
      } catch (java.sql.SQLException sqle) {
        throw new GenOrmException(sqle);
      }

      close();
      return (ret);
    }
Ejemplo n.º 5
0
    /**
     * Find the record with the specified primary keys
     *
     * @return DataPoint or null if no record is found
     */
    public DataPoint find(int id) {
      DataPoint rec = new DataPoint();

      // Create temp object and look in cache for it
      ((DataPoint_base) rec).initialize(id);
      rec = (DataPoint) GenOrmDataSource.getGenOrmConnection().getCachedRecord(rec.getRecordKey());

      java.sql.PreparedStatement genorm_statement = null;
      java.sql.ResultSet genorm_rs = null;

      if (rec == null) {
        try {
          // No cached object so look in db
          genorm_statement = GenOrmDataSource.prepareStatement(SELECT + FROM + KEY_WHERE);
          genorm_statement.setInt(1, id);

          s_logger.debug(genorm_statement.toString());

          genorm_rs = genorm_statement.executeQuery();
          if (genorm_rs.next()) rec = newDataPoint(genorm_rs);
        } catch (java.sql.SQLException sqle) {
          throw new GenOrmException(sqle);
        } finally {
          try {
            if (genorm_rs != null) genorm_rs.close();

            if (genorm_statement != null) genorm_statement.close();
          } catch (java.sql.SQLException sqle2) {
            throw new GenOrmException(sqle2);
          }
        }
      }

      return (rec);
    }
Ejemplo n.º 6
0
  @Test
  public void testNewResultSet_Contains() throws Exception {

    // Mocks
    ConnectionManager connectionManager = mock(ConnectionManager.class);
    Connection connectionContains = mock(Connection.class);
    Connection connectionDoNotContain = mock(Connection.class);
    PreparedStatement preparedStatementContains = mock(PreparedStatement.class);
    PreparedStatement preparedStatementDoNotContains = mock(PreparedStatement.class);
    java.sql.ResultSet resultSetContains = mock(java.sql.ResultSet.class);
    java.sql.ResultSet resultSetDoNotContain = mock(java.sql.ResultSet.class);

    // Behaviour
    when(connectionManager.getConnection(any(SQLiteIndex.class)))
        .thenReturn(connectionContains)
        .thenReturn(connectionDoNotContain);
    when(connectionContains.prepareStatement(
            "SELECT COUNT(objectKey) FROM " + TABLE_NAME + " WHERE value = ? AND objectKey = ?;"))
        .thenReturn(preparedStatementContains);
    when(connectionDoNotContain.prepareStatement(
            "SELECT COUNT(objectKey) FROM " + TABLE_NAME + " WHERE value = ? AND objectKey = ?;"))
        .thenReturn(preparedStatementDoNotContains);
    when(preparedStatementContains.executeQuery()).thenReturn(resultSetContains);
    when(preparedStatementDoNotContains.executeQuery()).thenReturn(resultSetDoNotContain);
    when(resultSetContains.next()).thenReturn(true).thenReturn(false);
    when(resultSetContains.getInt(1)).thenReturn(1);
    when(resultSetDoNotContain.next()).thenReturn(true).thenReturn(false);
    when(resultSetDoNotContain.getInt(1)).thenReturn(0);

    // Iterator
    ResultSet<Car> carsWithAbs =
        new SQLiteIndex<String, Car, Integer>(
                Car.FEATURES, OBJECT_TO_ID, ID_TO_OBJECT, connectionManager)
            .retrieve(equal(Car.FEATURES, "abs"), new QueryOptions());

    Assert.assertNotNull(carsWithAbs);
    boolean resultContains = carsWithAbs.contains(data.get(0));
    assertTrue(resultContains);
    verify(connectionContains, times(1)).close();

    boolean resultDoNotContain = carsWithAbs.contains(data.get(1));
    assertFalse(resultDoNotContain);
    verify(connectionDoNotContain, times(1)).close();
  }
Ejemplo n.º 7
0
    /** Returns true if there is another record in the result set. */
    public boolean next() {
      boolean ret = false;
      m_onFirstResult = true;
      try {
        ret = m_resultSet.next();
      } catch (java.sql.SQLException sqle) {
        throw new GenOrmException(sqle);
      }

      return (ret);
    }
Ejemplo n.º 8
0
  public void assertObjectExistenceInSQLIteMasterTable(
      final String name,
      final String type,
      boolean exists,
      final ConnectionManager connectionManager) {
    Connection connection = null;
    PreparedStatement statement = null;
    try {
      connection = connectionManager.getConnection(null);
      statement = connection.prepareStatement("SELECT name FROM sqlite_master WHERE type=?");
      statement.setString(1, type);
      java.sql.ResultSet indices = statement.executeQuery();

      boolean found = false;
      StringBuilder objectsFound = new StringBuilder();
      String next;
      while (indices.next()) {
        next = indices.getString(1);
        objectsFound.append("'").append(next).append("' ");
        if (name.equals(next)) {
          found = true;
        }
      }

      if (exists)
        Assert.assertTrue(
            "Object '"
                + name
                + "' must exists in 'sqlite_master' but it doesn't. found: "
                + found
                + ". Objects found: "
                + objectsFound,
            found);
      else
        Assert.assertFalse(
            "Object '"
                + name
                + "' must NOT exists in 'sqlite_master' but it does. found: "
                + found
                + " Objects found: "
                + objectsFound,
            found);

    } catch (Exception e) {
      throw new IllegalStateException(
          "Unable to verify existence of the object '" + name + "' in the 'sqlite_master' table",
          e);
    } finally {
      DBUtils.closeQuietly(connection);
      DBUtils.closeQuietly(statement);
    }
  }
Ejemplo n.º 9
0
  protected String getSampleName(String strDir, String strTray) {
    String strSampleName = "";
    String strSample;
    int nTray = 0;
    try {
      nTray = Integer.parseInt(strTray);
    } catch (Exception e) {
    }

    if (nTray <= 0) return strSampleName;

    String cmd =
        "SELECT loc_,studystatus from study WHERE (autodir=\'"
            + strDir
            + "\') AND (hostname=\'"
            + getHost()
            + "\')";
    // NB: bArrSample[0] is not used; array implicitly initialized false
    boolean[] bArrSample = new boolean[nTray + 1];
    m_dbResult = null;

    try {
      m_dbResult = getdbmanager().executeQuery(cmd);
    } catch (Exception e) {
      return strSampleName;
    }
    if (m_dbResult == null) return strSampleName;

    try {
      while (m_dbResult.next()) {
        strSample = m_dbResult.getString(1);
        int nSample = 0;
        nSample = Integer.parseInt(strSample);
        try {
          bArrSample[nSample] = true;
        } catch (IndexOutOfBoundsException ioobe) {
          Messages.postDebug("getSampleName: index out of bounds: " + nSample);
        }
      }
      for (int i = 1; i < bArrSample.length; i++) {
        if (!bArrSample[i]) {
          strSampleName = String.valueOf(i);
          break;
        }
      }
    } catch (Exception e) {
      // e.printStackTrace();
      Messages.writeStackTrace(e);
    }
    return strSampleName;
  }
Ejemplo n.º 10
0
    /**
     * Returns the reults as an ArrayList of Record objects. The Result set is closed within this
     * call
     */
    public ArrayList<DataPoint> getArrayList() {
      ArrayList<DataPoint> results = new ArrayList<DataPoint>();

      try {
        if (m_onFirstResult) results.add(factory.newDataPoint(m_resultSet));

        while (m_resultSet.next()) results.add(factory.newDataPoint(m_resultSet));
      } catch (java.sql.SQLException sqle) {
        sqle.printStackTrace();
        throw new GenOrmException(sqle);
      }

      close();
      return (results);
    }
Ejemplo n.º 11
0
  @Test
  public void testNewResultSet_Iterator_Close() throws Exception {

    QueryOptions queryOptions = new QueryOptions();

    // Mocks
    ConnectionManager connectionManager = mock(ConnectionManager.class);
    Connection connection = mock(Connection.class);
    PreparedStatement preparedStatement = mock(PreparedStatement.class);
    java.sql.ResultSet resultSet = mock(java.sql.ResultSet.class);
    @SuppressWarnings("unchecked")
    SimpleAttribute<Integer, Car> idToObject =
        (SimpleAttribute<Integer, Car>) mock(SimpleAttribute.class);

    // Behaviour
    when(connectionManager.getConnection(any(SQLiteIndex.class))).thenReturn(connection);
    when(connection.prepareStatement(
            "SELECT DISTINCT objectKey, value FROM " + TABLE_NAME + " WHERE value = ?;"))
        .thenReturn(preparedStatement);
    when(preparedStatement.executeQuery()).thenReturn(resultSet);
    when(resultSet.getStatement()).thenReturn(preparedStatement);
    when(resultSet.next()).thenReturn(true).thenReturn(true).thenReturn(false);
    when(resultSet.getInt(1)).thenReturn(1).thenReturn(3);
    when(idToObject.getValue(1, queryOptions)).thenReturn(data.get(0));
    when(idToObject.getValue(3, queryOptions)).thenReturn(data.get(2));

    // Iterator
    ResultSet<Car> carsWithAbs =
        new SQLiteIndex<String, Car, Integer>(
                Car.FEATURES, OBJECT_TO_ID, idToObject, connectionManager)
            .retrieve(equal(Car.FEATURES, "abs"), queryOptions);

    Assert.assertNotNull(carsWithAbs);
    Iterator carsWithAbsIterator = carsWithAbs.iterator();

    assertTrue(carsWithAbsIterator.hasNext());
    Assert.assertNotNull(carsWithAbsIterator.next());
    assertTrue(carsWithAbsIterator.hasNext());
    Assert.assertNotNull(carsWithAbsIterator.next());
    assertFalse(carsWithAbsIterator.hasNext());

    // The end of the iteration should close the resources
    verify(connection, times(1)).close();
    verify(preparedStatement, times(1)).close();
    verify(resultSet, times(1)).close();
  }
Ejemplo n.º 12
0
  protected void setTrayActive(String strDir) {
    String strTray;
    String strStudy;
    String strActive = "Active";
    String cmd =
        "SELECT vrack_,studystatus from study WHERE (autodir=\'"
            + strDir
            + "\') AND (hostname=\'"
            + getHost()
            + "\')";
    m_dbResult = null;

    try {
      m_dbResult = getdbmanager().executeQuery(cmd);
    } catch (Exception e) {
      return;
    }
    if (m_dbResult == null) return;

    int nTray = 0;
    try {
      while (m_dbResult.next()) {
        strTray = m_dbResult.getString(1);
        strStudy = m_dbResult.getString(2);
        if (strStudy != null && strStudy.equalsIgnoreCase(strActive)) {
          try {
            nTray = Integer.parseInt(strTray);
          } catch (Exception e) {
          }

          if (nTray > 0) {
            m_pnlVast[nTray - 1].setbackground(Color.blue);
            m_pnlVast[nTray - 1].setToolTipText(Util.getTooltipString(TRAYACTIVE));
            break;
          }
        }
      }
    } catch (Exception e) {
      // e.printStackTrace();
      Messages.writeStackTrace(e);
    }
  }
Ejemplo n.º 13
0
    /**
     * This resets the key generator from the values in the database Usefull if the generated key
     * has been modified via some other means Connection must be open before calling this
     */
    public synchronized void reset() {
      m_nextKey = 0;
      java.sql.Statement stmnt = null;
      java.sql.ResultSet rs = null;
      try {
        stmnt = GenOrmDataSource.createStatement();
        rs = stmnt.executeQuery(MAX_QUERY);

        if (rs.next()) m_nextKey = rs.getInt(1);
      } catch (java.sql.SQLException sqle) {
        // The exception may occur if the table does not yet exist
        if (WARNINGS) System.out.println(sqle);
      } finally {
        try {
          if (rs != null) rs.close();

          if (stmnt != null) stmnt.close();
        } catch (java.sql.SQLException sqle2) {
          throw new GenOrmException(sqle2);
        }
      }
    }
Ejemplo n.º 14
0
    public DataPointKeyGenerator(javax.sql.DataSource ds) {
      m_nextKey = 0;
      java.sql.Connection con = null;
      java.sql.Statement stmnt = null;
      try {
        con = ds.getConnection();
        con.setAutoCommit(true);
        stmnt = con.createStatement();
        java.sql.ResultSet rs = stmnt.executeQuery(MAX_QUERY);
        if (rs.next()) m_nextKey = rs.getInt(1);

        rs.close();
      } catch (java.sql.SQLException sqle) {
        // The exception may occur if the table does not yet exist
        if (WARNINGS) System.out.println(sqle);
      } finally {
        try {
          if (stmnt != null) stmnt.close();

          if (con != null) con.close();
        } catch (java.sql.SQLException sqle) {
        }
      }
    }
  /**
   * A method to build a collection of collaborator objects representing a network based on an
   * organisation
   *
   * @param id the id of the central organisation
   * @param radius the number of edges required from the central contributor
   * @return the collection of collaborator objects
   */
  @SuppressWarnings("rawtypes")
  public TreeMap<Integer, Collaborator> getRawCollaboratorData_org(String id, int radius) {

    // check the parameters
    if (InputUtils.isValidInt(id) == false) {
      throw new IllegalArgumentException("Error: the id parameter is required");
    }

    if (InputUtils.isValidInt(radius, ExportServlet.MIN_DEGREES, ExportServlet.MAX_DEGREES)
        == false) {
      throw new IllegalArgumentException(
          "Error: the radius parameter must be between "
              + ExportServlet.MIN_DEGREES
              + " and "
              + ExportServlet.MAX_DEGREES);
    }

    // define helper variables
    // collection of collaborators
    java.util.TreeMap<Integer, Collaborator> cId_cObj_map =
        new java.util.TreeMap<Integer, Collaborator>();

    // set of collaborators that we've already processed
    java.util.TreeSet<Integer> foundCollaboratorsSet = new java.util.TreeSet<Integer>();

    // define other helper variables
    String contributor_id = null;
    QuerySolution row = null;
    Collaborator collaborator = null;

    String sql =
        "SELECT DISTINCT b.eventid   "
            + "FROM events a, orgevlink b "
            + "WHERE b.organisationid = ? " // +id
            + "AND a.eventid = b.eventid";

    int[] param = {Integer.parseInt(id)};
    java.sql.ResultSet resultSet = db.exePreparedStatement(sql, param);

    ArrayList<String> eventResults = new ArrayList<String>();

    try {
      //		 	check to see that data was returned
      if (!resultSet.last()) {
        db.tidyup();
        return null;
      } else resultSet.beforeFirst();

      // loop through the resultset
      while (resultSet.next() == true) {
        eventResults.add(resultSet.getString(1));
      }

    } catch (java.sql.SQLException ex) {
      System.out.println("Exception: " + ex.getMessage());
      resultSet = null;
    }

    db.tidyup();

    // helper
    int first = 0;

    // define the query
    String sparqlQuery1 =
        "PREFIX foaf:       <"
            + FOAF.NS
            + "> "
            + "PREFIX ausestage:  <"
            + AuseStage.NS
            + "> "
            + "PREFIX event:      <http://purl.org/NET/c4dm/event.owl#> "
            + "PREFIX dcterms:    <http://purl.org/dc/terms/> "
            + "SELECT DISTINCT ?agent ?givenName ?familyName "
            + "WHERE { ";
    for (String event : eventResults) {
      if (first > 0) {
        sparqlQuery1 += "UNION ";
      }
      first++;

      sparqlQuery1 +=
          "{<ausstage:e:"
              + event
              + "> a                event:Event; "
              + "   event:agent      ?agent. "
              + "	?agent             a                foaf:Person; "
              + "   foaf:givenName   ?givenName; "
              + "   foaf:familyName  ?familyName. "
              + " } ";
    }
    sparqlQuery1 += " } ";

    // execute query
    ResultSet results = rdf.executeSparqlQuery(sparqlQuery1);

    // now we transfer the results to a TreeMap <Integer,
    while (results.hasNext()) {

      row = results.nextSolution();

      contributor_id = AusStageURI.getId(row.get("agent").toString());

      collaborator = new Collaborator(contributor_id);

      cId_cObj_map.put(Integer.parseInt(contributor_id), collaborator);
      foundCollaboratorsSet.add(Integer.parseInt(contributor_id));
    }

    rdf.tidyUp();

    return cId_cObj_map;
  } // end getRawCollaboratorData_org method
Ejemplo n.º 16
0
  private List<Object[]> getCustomStgUsers(boolean usedb2) {
    List<Object[]> listData = new ArrayList<Object[]>();
    String ServerName = "";
    int PortNumber;
    String DatabaseName = "";
    java.util.Properties properties;
    String url = "";
    java.sql.Connection con = null;

    if (usedb2) {
      ServerName = synDB.getConfigProperties("SERVER_NAME");
      PortNumber = Integer.parseInt(synDB.getConfigProperties("PORT_NUMBER"));
      DatabaseName = synDB.getConfigProperties("DATABASE");
      properties = new java.util.Properties();
      properties.put("user", synDB.getConfigProperties("USER"));
      properties.put("password", synDB.getConfigProperties("PASSWORD"));
      properties.put("sslConnection", "true");
      System.setProperty("javax.net.ssl.trustStore", synDB.getConfigProperties("CACERTS_PATH"));
      System.setProperty("javax.net.ssl.trustStorePassword", "changeit");
      System.setProperty("db2.jcc.charsetDecoderEncoder", "3");
      url = "jdbc:db2://" + ServerName + ":" + PortNumber + "/" + DatabaseName;

      try {
        new DB2Driver();
      } catch (Exception e) {
        System.out.println("Error: failed to load Db2 jcc driver.");
      }
    } else {
      ServerName = synDB.getConfigProperties("SERVER_NAME");
      PortNumber = Integer.parseInt(synDB.getConfigProperties("PORT_NUMBER"));
      DatabaseName = synDB.getConfigProperties("DATABASE");

      properties = new java.util.Properties();
      properties.put("user", synDB.getConfigProperties("USER"));
      properties.put("password", synDB.getConfigProperties("PASSWORD"));
      url = "jdbc:postgresql://" + ServerName + ":" + PortNumber + "/" + DatabaseName;
    }

    try {
      System.out.println("url: " + url);
      con = java.sql.DriverManager.getConnection(url, properties);
      try {
        String sql =
            "SELECT a.ID_NRIC, FIRST_NAME, LAST_NAME, MOBILE, EMAIL, ACCOUNT_STATUS, AGENT_CODE, AGENCY, NEED2FA, NEEDTNC, USER_TYPE, USER__SUB_TYPE, a.CREATED_DATE, b.ROLE_NAME "
                + "FROM CUSTOM_STG_USER a INNER JOIN CUSTOM_STG_USER_ROLE b ON (b.ID_NRIC=a.ID_NRIC) ";
        StringBuilder sbSQLSTGUser = new StringBuilder();
        sbSQLSTGUser.append(sql);

        System.out.println("Select from PSE.CUSTOM_STG_USER DB2: " + sbSQLSTGUser.toString());
        java.sql.Statement ps = con.createStatement();
        java.sql.ResultSet rs = ps.executeQuery(sbSQLSTGUser.toString());
        System.out.println("get data....");

        Object[] objArrays = new Object[14];
        while (rs.next()) {
          objArrays = new Object[14];
          objArrays[0] = rs.getString(1);
          objArrays[1] = rs.getString(2);
          objArrays[2] = rs.getString(3);
          objArrays[3] = rs.getString(4);
          objArrays[4] = rs.getString(5);
          objArrays[5] = rs.getString(6);
          objArrays[6] = rs.getString(7);
          objArrays[7] = rs.getString(8);
          objArrays[8] = rs.getString(9);
          objArrays[9] = rs.getString(10);
          objArrays[10] = rs.getString(11);
          objArrays[11] = rs.getString(12);
          objArrays[12] = rs.getString(13);
          objArrays[13] = rs.getString(14);
          listData.add(objArrays);
          System.out.println(
              "GET FROM DB2 | ID_NRIC:"
                  + objArrays[0]
                  + ", FIRST_NAME:"
                  + objArrays[1]
                  + ", LAST_NAME:"
                  + objArrays[2]
                  + ", MOBILE:"
                  + objArrays[3]
                  + ", EMAIL:"
                  + objArrays[4]
                  + ", ACCOUNT_STATUS:"
                  + objArrays[5]
                  + ", AGENT_CODE:"
                  + objArrays[6]
                  + ", AGENCY:"
                  + objArrays[7]
                  + ", NEED2FA:"
                  + objArrays[8]
                  + ", NEEDTNC:"
                  + objArrays[9]
                  + ", USER_TYPE:"
                  + objArrays[10]
                  + ", USER_SUB_TYPE:"
                  + objArrays[11]
                  + ", CREATED_DATE :"
                  + objArrays[12]
                  + ", ROLE_NAME :"
                  + objArrays[13]);
        }
      } catch (Exception e) {
        e.printStackTrace();
        System.out.println("select is failing1");
      }

      if (con != null) {
        try {
          con.close();
          con = null;
        } catch (Exception e) {
        }
      }
    } catch (Exception e) {
      e.printStackTrace();
    }

    return listData;
  }
  /**
   * A method to build a list of Collaboration objects representing a list of collaborations
   * associated with a collaborator
   *
   * @param id the unique identifier of a collaborator
   * @return a CollaborationList object containing a list of Collaboration objects
   */
  public CollaborationList getCollaborationList_org(String org_id, String id) {

    // check on the input parameters
    if (InputUtils.isValidInt(id) == false) {
      throw new IllegalArgumentException("The id parameter cannot be null");
    }

    // declare helper variables
    CollaborationList list = new CollaborationList(id);

    // define other helper variables
    Collaboration collaboration = null;

    // define other helper variables
    String partner = null;
    Integer count = null;
    String firstDate = null;
    String lastDate = null;

    // define the base sql query
    String sqlQuery =
        "SELECT distinct count(*), con.contributorid, min(e.first_date), max(e.first_date) "
            + "FROM contributor con, conevlink c , conevlink c2, orgevlink o, events e "
            + "WHERE o.organisationid = ? " // + org_id + " "
            + "AND c.eventid = O.EVENTID "
            + "AND e.eventid = O.EVENTID "
            + "AND e.eventid = c.EVENTID "
            + "AND c.contributorid != ?" // + id + " "
            + "AND con.contributorid = c.contributorid "
            + "AND c2.contributorid = ? " // + id + " "
            + "AND c2.eventid = c.eventid "
            + "GROUP BY con.contributorid, con.first_name ";

    int[] param = {Integer.parseInt(org_id), Integer.parseInt(id), Integer.parseInt(id)};
    // execute the query
    java.sql.ResultSet resultSet = db.exePreparedStatement(sqlQuery, param);

    try {
      //		 	check to see that data was returned
      if (!resultSet.last()) {
        db.tidyup();
        return null;
      } else resultSet.beforeFirst();

      // loop though the resulset
      while (resultSet.next()) {
        // get the data
        partner = resultSet.getString(2);
        count = resultSet.getInt(1);
        firstDate = resultSet.getDate(3).toString();
        lastDate = resultSet.getDate(4).toString();

        // create the collaboration object
        collaboration = new Collaboration(id, partner, count, firstDate, lastDate);

        // add the collaboration to the list
        list.addCollaboration(collaboration);
      }

    } catch (java.sql.SQLException ex) {
      System.out.println("Exception: " + ex.getMessage());
      resultSet = null;
    }

    db.tidyup();
    // return the list of collaborations
    return list;
  } // end the CollaborationList method