Example #1
0
  private void deleteOldest(JDCConnection oConn, Object oFilterValue) throws SQLException {

    Object oPK = null;

    if (DebugFile.trace) {
      DebugFile.writeln("Begin RecentlyUsed.deleteOldest ([Connection]," + oFilterValue + ")");
      DebugFile.incIdent();
      DebugFile.writeln(
          "Connection.prepareStatement(SELECT "
              + sPK
              + " FROM "
              + sTable
              + " WHERE "
              + sFilter
              + "="
              + oFilterValue
              + " ORDER BY "
              + sDate
              + ")");
    }

    PreparedStatement oStmt =
        oConn.prepareStatement(
            "SELECT " + sPK + " FROM " + sTable + " WHERE " + sFilter + "=? ORDER BY " + sDate,
            ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    oStmt.setObject(1, oFilterValue);
    ResultSet oRSet = oStmt.executeQuery();
    if (oRSet.next()) oPK = oRSet.getObject(1);
    oRSet.close();
    oStmt.close();

    if (null != oPK) {
      if (DebugFile.trace)
        DebugFile.writeln(
            "Connection.prepareStatement(DELETE FROM "
                + sTable
                + " WHERE "
                + sPK
                + "="
                + oPK
                + ")");

      PreparedStatement oDlte =
          oConn.prepareStatement("DELETE FROM " + sTable + " WHERE " + sPK + "=?");
      oDlte.setObject(1, oPK);
      oDlte.executeUpdate();
      oDlte.close();
    } // fi

    if (DebugFile.trace) {
      DebugFile.decIdent();
      DebugFile.writeln("End RecentlyUsed.deleteOldest () : " + oPK);
    }
  } // deleteOldest
Example #2
0
  /**
   * Clear recently used list
   *
   * @param oConn JDBC Database Connection
   * @param oFilterValue Value for field used for filtering items from this list
   * @throws SQLException
   */
  public void clear(JDCConnection oConn, Object oFilterValue) throws SQLException {

    if (DebugFile.trace) {
      DebugFile.writeln("Begin RecentlyUsed.clear ([Connection]," + oFilterValue + ")");
      DebugFile.incIdent();
      DebugFile.writeln(
          "Connection.prepareStatement(DELETE FROM "
              + sTable
              + " WHERE "
              + sFilter
              + "="
              + oFilterValue
              + ")");
    }

    PreparedStatement oDlte =
        oConn.prepareStatement("DELETE FROM " + sTable + " WHERE " + sFilter + "=?");
    oDlte.setObject(1, oFilterValue);
    int iDeleted = oDlte.executeUpdate();
    oDlte.close();

    if (DebugFile.trace) {
      DebugFile.decIdent();
      DebugFile.writeln("End RecentlyUsed.clear () : " + String.valueOf(iDeleted));
    }
  } // clear
Example #3
0
  /**
   * Current list size
   *
   * @param oConn JDBC Database connection
   * @param oFilterValue Value for field used for filtering items from this list
   * @return Number of items in list
   * @throws SQLException
   */
  public int listSize(JDCConnection oConn, Object oFilterValue) throws SQLException {

    if (DebugFile.trace) {
      DebugFile.writeln("Begin RecentlyUsed.listSize ([Connection]," + oFilterValue + ")");
      DebugFile.incIdent();
      DebugFile.writeln(
          "Connection.prepareStatement(SELECT COUNT("
              + sPK
              + ") FROM "
              + sTable
              + " WHERE "
              + sFilter
              + "="
              + oFilterValue
              + ")");
    }

    PreparedStatement oStmt =
        oConn.prepareStatement(
            "SELECT COUNT(" + sPK + ") FROM " + sTable + " WHERE " + sFilter + "=?",
            ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    oStmt.setObject(1, oFilterValue);
    ResultSet oRSet = oStmt.executeQuery();
    oRSet.next();
    Object oCount = oRSet.getObject(1);
    oRSet.close();
    oStmt.close();

    if (DebugFile.trace) {
      DebugFile.decIdent();
      DebugFile.writeln("End RecentlyUsed.listSize () : " + oCount);
    }

    return Integer.parseInt(oCount.toString());
  } // listSize
Example #4
0
  /**
   * Get Term GUID given its numeric identifier and level
   *
   * @param oConn Database Connection
   * @param iDomainId Term Domain identifier
   * @param iTermId Term numeric identifier
   * @param iLevel Term level [0..9]
   * @return Term GUID or <b>null</b> if no term with such numeric identifier and level was found
   * @throws SQLException
   */
  public static String getTerm(JDCConnection oConn, int iDomainId, int iTermId, int iLevel)
      throws SQLException {

    if (DebugFile.trace) {
      DebugFile.writeln(
          "Begin Thesauri.getTerm (Connection], "
              + String.valueOf(iTermId)
              + ","
              + String.valueOf(iLevel));
      DebugFile.incIdent();
    }

    String sTermGUID;
    PreparedStatement oStmt;

    if (iLevel < 9) {
      if (DebugFile.trace)
        DebugFile.writeln(
            "Connection.prepareStatement(SELECT "
                + DB.gu_term
                + " FROM "
                + DB.k_thesauri
                + " WHERE "
                + DB.id_domain
                + "="
                + String.valueOf(iDomainId)
                + " AND "
                + DB.id_term
                + String.valueOf(iLevel)
                + "="
                + String.valueOf(iTermId)
                + " AND "
                + DB.id_term
                + String.valueOf(iLevel + 1)
                + " IS NULL)");

      oStmt =
          oConn.prepareStatement(
              "SELECT "
                  + DB.gu_term
                  + " FROM "
                  + DB.k_thesauri
                  + " WHERE "
                  + DB.id_domain
                  + "=? AND "
                  + DB.id_term
                  + String.valueOf(iLevel)
                  + "=? AND "
                  + DB.id_term
                  + String.valueOf(iLevel + 1)
                  + " IS NULL",
              ResultSet.TYPE_FORWARD_ONLY,
              ResultSet.CONCUR_READ_ONLY);
    } else {
      if (DebugFile.trace)
        DebugFile.writeln(
            "Connection.prepareStatement(SELECT "
                + DB.gu_term
                + " FROM "
                + DB.k_thesauri
                + " WHERE "
                + DB.id_domain
                + "="
                + String.valueOf(iDomainId)
                + " AND "
                + DB.id_term
                + String.valueOf(iLevel)
                + "="
                + String.valueOf(iTermId)
                + ")");

      oStmt =
          oConn.prepareStatement(
              "SELECT "
                  + DB.gu_term
                  + " FROM "
                  + DB.k_thesauri
                  + " WHERE "
                  + DB.id_domain
                  + "=? AND "
                  + DB.id_term
                  + String.valueOf(iLevel)
                  + "=?",
              ResultSet.TYPE_FORWARD_ONLY,
              ResultSet.CONCUR_READ_ONLY);
    }

    oStmt.setInt(1, iDomainId);
    oStmt.setInt(2, iTermId);

    ResultSet oRSet = oStmt.executeQuery();

    if (oRSet.next()) sTermGUID = oRSet.getString(1);
    else sTermGUID = null;

    oRSet.close();
    oStmt.close();

    if (DebugFile.trace) {
      DebugFile.decIdent();
      DebugFile.writeln("End Thesauri.getTerm() : " + sTermGUID);
    }

    return sTermGUID;
  }
Example #5
0
  /**
   * Create Root Term
   *
   * @param oConn Database Connection
   * @param sTxTerm Term Text Singular
   * @param sTxTerm Term Text Plural
   * @param sDeTerm Term Contextual Description
   * @param sIdLanguage Language
   * @param sIdScope Scope
   * @param iIdDomain Domain Numeric Identifier (from k_domains)
   * @param sGuWorkArea WorkArea GUID
   * @return GUID for new term
   * @throws SQLException
   */
  public static String createRootTerm(
      JDCConnection oConn,
      String sTxTerm,
      String sTxTermPlural,
      String sDeTerm,
      String sIdLanguage,
      String sIdScope,
      int iIdDomain,
      String sGuWorkArea)
      throws SQLException {
    PreparedStatement oStmt;
    String sGuRootTerm = Gadgets.generateUUID();
    int iNextVal;

    if (DebugFile.trace) {
      DebugFile.writeln(
          "Begin Thesauri.createRootTerm([Connection],"
              + sTxTerm
              + ","
              + sTxTermPlural
              + ","
              + sDeTerm
              + ","
              + sIdLanguage
              + ","
              + sIdScope
              + String.valueOf(iIdDomain)
              + ","
              + sGuWorkArea
              + ")");
      DebugFile.incIdent();
      DebugFile.writeln(
          "Connection.prepareStatement(INSERT INTO "
              + DB.k_thesauri_root
              + " ("
              + DB.gu_rootterm
              + ","
              + DB.tx_term
              + ","
              + DB.tx_term
              + "2,"
              + DB.id_domain
              + ","
              + DB.gu_workarea
              + ","
              + DB.id_scope
              + ") VALUES ('"
              + sGuRootTerm
              + "','"
              + sTxTerm
              + "',"
              + String.valueOf(iIdDomain)
              + ",'"
              + sGuWorkArea
              + "','"
              + sIdScope
              + "'))");
    }

    oStmt =
        oConn.prepareStatement(
            "INSERT INTO "
                + DB.k_thesauri_root
                + " ("
                + DB.gu_rootterm
                + ","
                + DB.tx_term
                + ","
                + DB.id_domain
                + ","
                + DB.gu_workarea
                + ","
                + DB.id_scope
                + ") VALUES (?,?,?,?,?)");
    oStmt.setString(1, sGuRootTerm);
    oStmt.setString(2, sTxTerm);
    oStmt.setInt(3, iIdDomain);
    oStmt.setString(4, sGuWorkArea);
    oStmt.setString(5, sIdScope);
    oStmt.executeUpdate();
    oStmt.close();

    iNextVal = DBBind.nextVal(oConn, "seq_thesauri");

    if (DebugFile.trace)
      DebugFile.writeln(
          "Connection.prepareStatement(INSERT INTO "
              + DB.k_thesauri
              + " ("
              + DB.gu_rootterm
              + ","
              + DB.gu_term
              + ","
              + DB.tx_term
              + ","
              + DB.tx_term
              + "2,"
              + DB.id_language
              + ","
              + DB.de_term
              + ","
              + DB.id_scope
              + ","
              + DB.id_domain
              + ","
              + DB.id_term
              + "0) VALUES ('"
              + sGuRootTerm
              + "','"
              + sGuRootTerm
              + "','"
              + sTxTerm
              + "','"
              + sIdLanguage
              + "','"
              + sDeTerm
              + "','"
              + sIdScope
              + "',"
              + String.valueOf(iIdDomain)
              + ","
              + String.valueOf(iNextVal)
              + "))");

    oStmt =
        oConn.prepareStatement(
            "INSERT INTO "
                + DB.k_thesauri
                + " ("
                + DB.gu_rootterm
                + ","
                + DB.gu_term
                + ","
                + DB.tx_term
                + ","
                + DB.tx_term
                + "2,"
                + DB.id_language
                + ","
                + DB.de_term
                + ","
                + DB.id_scope
                + ","
                + DB.id_domain
                + ","
                + DB.id_term
                + "0) VALUES (?,?,?,?,?,?,?,?,?)");
    oStmt.setString(1, sGuRootTerm);
    oStmt.setString(2, sGuRootTerm);
    oStmt.setString(3, sTxTerm);
    oStmt.setString(4, sTxTermPlural);
    oStmt.setString(5, sIdLanguage);
    if (sDeTerm == null) oStmt.setNull(6, Types.VARCHAR);
    else oStmt.setString(6, sDeTerm);
    oStmt.setString(7, sIdScope);
    oStmt.setInt(8, iIdDomain);
    oStmt.setInt(9, iNextVal);
    oStmt.executeUpdate();
    oStmt.close();

    if (DebugFile.trace) {
      DebugFile.decIdent();
      DebugFile.writeln("End Thesauri.createRootTerm() : " + sGuRootTerm);
    }

    return sGuRootTerm;
  } // createRootTerm
Example #6
0
  /**
   * Delete a Term and all its childs
   *
   * @param sGuTerm Term GUID
   * @throws SQLException
   */
  public static void delete(JDCConnection oConn, String sGuTerm) throws SQLException {

    PreparedStatement oStmt;

    if (DebugFile.trace) {
      DebugFile.writeln("Begin Thesauri.delete([Connection]," + sGuTerm);
      DebugFile.incIdent();
      DebugFile.writeln(
          "Connection.prepareStatement(DELETE FROM "
              + DB.k_thesauri
              + " WHERE "
              + DB.gu_term
              + "=? OR "
              + DB.id_term
              + "0=? OR "
              + DB.id_term
              + "1=? OR "
              + DB.id_term
              + "2=? OR "
              + DB.id_term
              + "3=? OR "
              + DB.id_term
              + "4=? OR "
              + DB.id_term
              + "5=? OR "
              + DB.id_term
              + "6=? OR "
              + DB.id_term
              + "7=? OR "
              + DB.id_term
              + "8=? OR "
              + DB.id_term
              + "9=?)");
    }

    oStmt =
        oConn.prepareStatement(
            "DELETE FROM "
                + DB.k_thesauri
                + " WHERE "
                + DB.gu_term
                + "=? OR "
                + DB.id_term
                + "0=? OR "
                + DB.id_term
                + "1=? OR "
                + DB.id_term
                + "2=? OR "
                + DB.id_term
                + "3=? OR "
                + DB.id_term
                + "4=? OR "
                + DB.id_term
                + "5=? OR "
                + DB.id_term
                + "6=? OR "
                + DB.id_term
                + "7=? OR "
                + DB.id_term
                + "8=? OR "
                + DB.id_term
                + "9=?");

    oStmt.setString(1, sGuTerm);
    oStmt.setString(2, sGuTerm);
    oStmt.setString(3, sGuTerm);
    oStmt.setString(4, sGuTerm);
    oStmt.setString(5, sGuTerm);
    oStmt.setString(6, sGuTerm);
    oStmt.setString(7, sGuTerm);
    oStmt.setString(8, sGuTerm);
    oStmt.setString(9, sGuTerm);
    oStmt.setString(10, sGuTerm);
    oStmt.setString(11, sGuTerm);

    oStmt.executeUpdate();

    oStmt.close();

    if (DebugFile.trace) {
      DebugFile.decIdent();
      DebugFile.writeln("End Thesauri.delete()");
    }
  } // delete
Example #7
0
  /**
   * Create Term
   *
   * @param oConn Database Connection
   * @param sGuParent Parent Term
   * @param sTxTerm Term Text Singular
   * @param sTxTermPlural Term Text Plural
   * @param sDeTerm Term Contextual Description
   * @param sIdLanguage Language
   * @param sIdScope Scope
   * @param iIdDomain Domain Numeric Identifier (from k_domains)
   * @return GUID for new term
   * @throws SQLException
   */
  public static String createTerm(
      JDCConnection oConn,
      String sGuParent,
      String sTxTerm,
      String sTxTermPlural,
      String sDeTerm,
      String sIdLanguage,
      String sIdScope,
      int iIdDomain)
      throws SQLException {
    ResultSet oRSet;
    PreparedStatement oStmt;
    String sGuTerm = Gadgets.generateUUID();
    String sGuRootTerm;
    Object[] oTerm = new Object[10];
    int iTerm;
    int iNext;

    if (DebugFile.trace) {
      DebugFile.writeln(
          "Begin Thesauri.createTerm([Connection],"
              + sGuParent
              + ","
              + sTxTerm
              + ","
              + sTxTermPlural
              + ","
              + sDeTerm
              + ","
              + sIdLanguage
              + ","
              + sIdScope
              + String.valueOf(iIdDomain)
              + ")");
      DebugFile.incIdent();
      DebugFile.writeln(
          "Connection.prepareStatement(SELECT "
              + DB.gu_rootterm
              + ","
              + DB.id_term
              + "0,"
              + DB.id_term
              + "1,"
              + DB.id_term
              + "2,"
              + DB.id_term
              + "3,"
              + DB.id_term
              + "4,"
              + DB.id_term
              + "5,"
              + DB.id_term
              + "6,"
              + DB.id_term
              + "7,"
              + DB.id_term
              + "8,"
              + DB.id_term
              + "9 FROM "
              + DB.k_thesauri
              + " WHERE "
              + DB.gu_term
              + "='"
              + sGuParent
              + "'");
    }

    oStmt =
        oConn.prepareStatement(
            "SELECT "
                + DB.gu_rootterm
                + ","
                + DB.id_term
                + "0,"
                + DB.id_term
                + "1,"
                + DB.id_term
                + "2,"
                + DB.id_term
                + "3,"
                + DB.id_term
                + "4,"
                + DB.id_term
                + "5,"
                + DB.id_term
                + "6,"
                + DB.id_term
                + "7,"
                + DB.id_term
                + "8,"
                + DB.id_term
                + "9 FROM "
                + DB.k_thesauri
                + " WHERE "
                + DB.gu_term
                + "=?",
            ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    oStmt.setString(1, sGuParent);
    oRSet = oStmt.executeQuery();
    boolean bParentExists = oRSet.next();
    if (bParentExists) {
      sGuRootTerm = oRSet.getString(1);
      oTerm[0] = oRSet.getObject(2);
      oTerm[1] = oRSet.getObject(3);
      oTerm[2] = oRSet.getObject(4);
      oTerm[3] = oRSet.getObject(5);
      oTerm[4] = oRSet.getObject(6);
      oTerm[5] = oRSet.getObject(7);
      oTerm[6] = oRSet.getObject(8);
      oTerm[7] = oRSet.getObject(9);
      oTerm[8] = oRSet.getObject(10);
      oTerm[9] = oRSet.getObject(11);
    } else {
      sGuRootTerm = null;
    }
    oRSet.close();
    oStmt.close();

    if (!bParentExists) {
      if (DebugFile.trace) {
        DebugFile.writeln("Parent term \"" + sGuParent + "\" not found");
        DebugFile.decIdent();
      }
      throw new SQLException("Thesauri.createTerm() Parent term \"" + sGuParent + "\" not found");
    }

    if (DebugFile.trace)
      DebugFile.writeln(
          "Connection.prepareStatement(INSERT INTO "
              + DB.k_thesauri
              + " ("
              + DB.gu_rootterm
              + ","
              + DB.gu_term
              + ","
              + DB.tx_term
              + ","
              + DB.tx_term
              + "2,"
              + DB.id_language
              + ","
              + DB.de_term
              + ","
              + DB.id_scope
              + ","
              + DB.id_domain
              + ","
              + DB.id_term
              + "0,"
              + DB.id_term
              + "1,"
              + DB.id_term
              + "2,"
              + DB.id_term
              + "3,"
              + DB.id_term
              + "4,"
              + DB.id_term
              + "5,"
              + DB.id_term
              + "6,"
              + DB.id_term
              + "7,"
              + DB.id_term
              + "8,"
              + DB.id_term
              + "9) VALUES ('"
              + sGuRootTerm
              + "','"
              + sGuTerm
              + "','"
              + sTxTerm
              + "','"
              + sTxTermPlural
              + "','"
              + sIdLanguage
              + "','"
              + sDeTerm
              + "','"
              + sIdScope
              + "',"
              + String.valueOf(iIdDomain)
              + ",?,?,?,?,?,?,?,?,?,?))");

    oStmt =
        oConn.prepareStatement(
            "INSERT INTO "
                + DB.k_thesauri
                + " ("
                + DB.gu_rootterm
                + ","
                + DB.gu_term
                + ","
                + DB.tx_term
                + ","
                + DB.tx_term
                + "2,"
                + DB.id_language
                + ","
                + DB.de_term
                + ","
                + DB.id_scope
                + ","
                + DB.id_domain
                + ","
                + DB.id_term
                + "0,"
                + DB.id_term
                + "1,"
                + DB.id_term
                + "2,"
                + DB.id_term
                + "3,"
                + DB.id_term
                + "4,"
                + DB.id_term
                + "5,"
                + DB.id_term
                + "6,"
                + DB.id_term
                + "7,"
                + DB.id_term
                + "8,"
                + DB.id_term
                + "9) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
    oStmt.setString(1, sGuRootTerm);
    oStmt.setString(2, sGuTerm);
    oStmt.setString(3, sTxTerm);
    oStmt.setString(4, sTxTermPlural);
    oStmt.setString(5, sIdLanguage);
    if (sDeTerm == null) oStmt.setNull(6, Types.VARCHAR);
    else oStmt.setString(6, sDeTerm);
    oStmt.setString(7, sIdScope);
    oStmt.setInt(8, iIdDomain);

    iTerm = 0;
    do {
      if (null == oTerm[iTerm]) break;

      if (DebugFile.trace)
        DebugFile.writeln(
            "PreparedStatement.setObject("
                + String.valueOf(iTerm + 9)
                + ", "
                + oTerm[iTerm]
                + ", java.sql.Types.INTEGER)");

      oStmt.setObject(iTerm + 9, oTerm[iTerm], java.sql.Types.INTEGER);
    } while (++iTerm <= 9);

    if (10 == iTerm)
      throw new SQLException("Thesauri maximum number of hierarchical levels exceeded");

    iNext = DBBind.nextVal(oConn, "seq_thesauri");

    if (DebugFile.trace)
      DebugFile.writeln(
          "PreparedStatement.setInt ("
              + String.valueOf(iTerm + 9)
              + ","
              + String.valueOf(iNext)
              + ")");

    oStmt.setInt(iTerm + 9, iNext);

    while (++iTerm <= 9) {
      if (DebugFile.trace)
        DebugFile.writeln(
            "PreparedStatement.setObject("
                + String.valueOf(iTerm + 9)
                + ", null, java.sql.Types.INTEGER)");

      oStmt.setObject(iTerm + 9, null, java.sql.Types.INTEGER);
    } // wend

    if (DebugFile.trace) DebugFile.writeln("PreparedStatement.executeUpdate();");

    oStmt.executeUpdate();
    oStmt.close();

    if (DebugFile.trace) {
      DebugFile.decIdent();
      DebugFile.writeln("End Thesauri.createTerm() : " + sGuTerm);
    }

    return sGuTerm;
  } // createTerm
Example #8
0
  /*
   * <p>Get GUID of a degree given its type and name</p>
   * Search is case sensitive
   * @param oConn JDCConnection
   * @param sGuWorkArea String GUID of WorkArea
   * @param sTpDegree Column tp_degree of table k_education_degree (optional, may be <b>null</b>)
   * @param sNmDegree Column nm_degree of table k_education_degree
   * @return GUID of degree or <b>null</b> if no degree with such name was found
   * @throws SQLException
   * @since 6.0
   */
  public static String getIdFromName(
      JDCConnection oConn, String sGuWorkArea, String sTpDegree, String sNmDegree)
      throws SQLException {

    PreparedStatement oStmt;
    ResultSet oRset;
    String sGuDegree;

    if (sTpDegree == null) {
      oStmt =
          oConn.prepareStatement(
              "SELECT "
                  + DB.gu_degree
                  + " FROM "
                  + DB.k_education_degree
                  + " WHERE "
                  + DB.gu_workarea
                  + "=? AND "
                  + DB.nm_degree
                  + "=?");
      oStmt.setString(1, sGuWorkArea);
      oStmt.setString(2, sNmDegree);
    } else if (sTpDegree.length() == 0) {
      oStmt =
          oConn.prepareStatement(
              "SELECT "
                  + DB.gu_degree
                  + " FROM "
                  + DB.k_education_degree
                  + " WHERE "
                  + DB.gu_workarea
                  + "=? AND "
                  + DB.nm_degree
                  + "=?");
      oStmt.setString(1, sGuWorkArea);
      oStmt.setString(2, sNmDegree);
    } else {
      oStmt =
          oConn.prepareStatement(
              "SELECT "
                  + DB.gu_degree
                  + " FROM "
                  + DB.k_education_degree
                  + " WHERE "
                  + DB.gu_workarea
                  + "=? AND "
                  + DB.tp_degree
                  + "=? AND "
                  + DB.nm_degree
                  + "=?");
      oStmt.setString(1, sGuWorkArea);
      oStmt.setString(2, sTpDegree);
      oStmt.setString(3, sNmDegree);
    }
    oRset = oStmt.executeQuery();
    if (oRset.next()) sGuDegree = oRset.getString(1);
    else sGuDegree = null;
    oRset.close();
    oStmt.close();
    return sGuDegree;
  } // getIdFromName