Example usage for java.sql ResultSet getShort

List of usage examples for java.sql ResultSet getShort

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSet
  6. getShort

Introduction

In this page you can find the example usage for java.sql ResultSet getShort.

Prototype

short getShort(String columnLabel) throws SQLException;

Source Link

Document

Retrieves the value of the designated column in the current row of this ResultSet object as a short in the Java programming language.

Usage

From source file:com.nextep.designer.sqlgen.generic.impl.JDBCCapturer.java

/**
 * Returns a <code>Collection</code> of the indexes for the specified table
 * present in the data source pointed to by the connection object provided
 * by the specified <code>context</code> and notifies the specified
 * <code>monitor</code> while capturing.
 * //w  ww.ja v a2s  .  co  m
 * @param context
 *            a {@link ICaptureContext} to store the captured objects
 * @param monitor
 *            the {@link IProgressMonitor} to notify while capturing objects
 * @param table
 *            the {@link IBasicTable} for which foreign keys must be
 *            captured
 * @return a {@link Collection} of {@link IIndex} objects if the specified
 *         table has indexes, an empty <code>Collection</code> otherwise
 */
private Collection<IIndex> getTableIndexes(ICaptureContext context, IProgressMonitor monitor,
        IBasicTable table) {
    Collection<IIndex> indexes = new ArrayList<IIndex>();
    IFormatter formatter = getConnectionVendor(context).getNameFormatter();

    final String tableName = table.getName();
    try {
        final DatabaseMetaData md = ((Connection) context.getConnectionObject()).getMetaData();

        ResultSet rset = null;
        if (md != null) {
            rset = md.getIndexInfo(getObjectOrContextCatalog(context, table),
                    getObjectOrContextSchema(context, table), tableName, false, false);
            CaptureHelper.updateMonitor(monitor, getCounter(), 1, 1);
        }

        if (rset != null) {
            IIndex currIndex = null;
            String currIndexName = null;
            boolean indexIsValid = false;

            try {
                while (rset.next()) {
                    final String indexName = rset.getString(COLUMN_NAME_INDEX_NAME);
                    final boolean nonUnique = rset.getBoolean(COLUMN_NAME_NON_UNIQUE);
                    final String indexColumnName = rset.getString(COLUMN_NAME_COLUMN_NAME);
                    final String ascOrDesc = rset.getString(COLUMN_NAME_ASC_OR_DESC);
                    final short indexType = rset.getShort(COLUMN_NAME_TYPE);

                    if (indexName != null && !"".equals(indexName.trim())) { //$NON-NLS-1$
                        if (LOGGER.isDebugEnabled()) {
                            String logPrefix = "[" + indexName + "]"; //$NON-NLS-1$ //$NON-NLS-2$
                            LOGGER.debug("= " + logPrefix + " Index Metadata ="); //$NON-NLS-1$ //$NON-NLS-2$
                            LOGGER.debug(logPrefix + "[" + COLUMN_NAME_INDEX_NAME + "] " //$NON-NLS-1$ //$NON-NLS-2$
                                    + indexName);
                            LOGGER.debug(logPrefix + "[" + COLUMN_NAME_NON_UNIQUE + "] " //$NON-NLS-1$ //$NON-NLS-2$
                                    + nonUnique);
                            LOGGER.debug(logPrefix + "[" + COLUMN_NAME_COLUMN_NAME + "] " //$NON-NLS-1$ //$NON-NLS-2$
                                    + indexColumnName);
                            LOGGER.debug(logPrefix + "[" + COLUMN_NAME_ASC_OR_DESC + "] " //$NON-NLS-1$ //$NON-NLS-2$
                                    + ascOrDesc);
                            LOGGER.debug(logPrefix + "[" + COLUMN_NAME_TYPE + "] " + indexType); //$NON-NLS-1$ //$NON-NLS-2$
                        }

                        if (null == currIndexName || !currIndexName.equals(indexName) || indexIsValid) {
                            currIndexName = indexName;
                            final String formatIndexName = formatter.format(indexName);
                            final String formatIndexColumnName = formatter.format(indexColumnName);

                            if (null == currIndex || !formatIndexName.equals(currIndex.getIndexName())) {
                                IVersionable<IIndex> v = VersionableFactory.createVersionable(IIndex.class,
                                        context.getConnection().getDBVendor());
                                currIndex = v.getVersionnedObject().getModel();
                                currIndex.setName(formatIndexName);
                                currIndex.setIndexType(
                                        nonUnique ? CaptureHelper.getIndexType(indexType) : IndexType.UNIQUE);
                                indexes.add(currIndex);
                                indexIsValid = true;
                            }

                            final IBasicColumn column = (IBasicColumn) context.getCapturedObject(
                                    IElementType.getInstance(IBasicColumn.TYPE_ID),
                                    CaptureHelper.getUniqueObjectName(tableName, formatIndexColumnName));
                            if (column != null) {
                                /*
                                 * Columns are ordered by INDEX_NAME,
                                 * ORDINAL_POSITION in the returned
                                 * ResultSet, so we don't have to specify
                                 * the position of the index column when
                                 * adding it to the index.
                                 */
                                currIndex.addColumnRef(column.getReference());
                            } else {
                                LOGGER.warn("Index [" + formatIndexName
                                        + "] has been partially captured during import because the referencing column ["
                                        + tableName + "[" + formatIndexColumnName //$NON-NLS-1$
                                        + "]] could not be found in the current workspace");
                                indexIsValid = false;

                                /*
                                 * Now the index is invalid, we remove it
                                 * from the indexes list that will be
                                 * returned to the caller of this method.
                                 */
                                indexes.remove(currIndex);
                            }
                        }
                    }
                }
            } finally {
                CaptureHelper.safeClose(rset, null);
            }
        }
    } catch (SQLException sqle) {
        LOGGER.error("Unable to fetch indexes for table [" + tableName + "] from "
                + getConnectionVendorName(context) + " server: " + sqle.getMessage(), sqle);
    }

    return indexes;
}

From source file:com.nextep.designer.sqlgen.generic.impl.JDBCCapturer.java

/**
 * Returns a <code>Collection</code> of the foreign keys of the specified
 * table present in the data source pointed to by the connection object
 * provided by the specified <code>context</code> and notifies the specified
 * <code>monitor</code> while capturing.
 * /*from w w  w  .j a va2 s.c  o  m*/
 * @param context
 *            a {@link ICaptureContext} to store the captured objects
 * @param monitor
 *            the {@link IProgressMonitor} to notify while capturing objects
 * @param allTables
 *            a <code>Map</code> of all tables previously captured
 * @param allTablesColumns
 *            a <code>Map</code> of all columns previously captured
 * @param table
 *            the {@link IBasicTable} for which foreign keys must be
 *            captured
 * @return a {@link Collection} of {@link ForeignKeyConstraint} objects if
 *         the specified table has foreign keys, an empty
 *         <code>Collection</code> otherwise
 */
private Collection<ForeignKeyConstraint> getTableForeignKeys(ICaptureContext context, IProgressMonitor monitor,
        Map<String, IBasicTable> allTables, Map<String, IBasicColumn> allTablesColumns, IBasicTable table) {
    Collection<ForeignKeyConstraint> foreignKeys = new ArrayList<ForeignKeyConstraint>();
    IFormatter formatter = getConnectionVendor(context).getNameFormatter();

    final String tableName = table.getName();
    try {
        final DatabaseMetaData md = ((Connection) context.getConnectionObject()).getMetaData();

        ResultSet rset = null;
        if (md != null) {
            rset = md.getImportedKeys(getObjectOrContextCatalog(context, table),
                    getObjectOrContextSchema(context, table), tableName);
            CaptureHelper.updateMonitor(monitor, getCounter(), 1, 1);
        }

        if (rset != null) {
            ForeignKeyConstraint currFk = null;
            String currFkName = null;
            boolean keyIsValid = false;

            try {
                while (rset.next()) {
                    final String fkName = rset.getString(COLUMN_NAME_FK_NAME);
                    final String fkColumnName = rset.getString(COLUMN_NAME_FKCOLUMN_NAME);
                    final String pkTableName = rset.getString(COLUMN_NAME_PKTABLE_NAME);
                    final String pkName = rset.getString(COLUMN_NAME_PK_NAME);
                    final short onUpdateRule = rset.getShort(COLUMN_NAME_UPDATE_RULE);
                    final short onDeleteRule = rset.getShort(COLUMN_NAME_DELETE_RULE);
                    final short deferrability = rset.getShort(COLUMN_NAME_DEFERRABILITY);

                    if (fkName != null && !"".equals(fkName.trim())) { //$NON-NLS-1$
                        if (LOGGER.isDebugEnabled()) {
                            String logPrefix = "[" + tableName + "][" + fkName + "]"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
                            LOGGER.debug("= " + logPrefix + " Foreign Key Metadata ="); //$NON-NLS-1$ //$NON-NLS-2$
                            LOGGER.debug(logPrefix + "[" + COLUMN_NAME_FKCOLUMN_NAME + "] " //$NON-NLS-1$ //$NON-NLS-2$
                                    + fkColumnName);
                            LOGGER.debug(logPrefix + "[" + COLUMN_NAME_PKTABLE_NAME + "] " //$NON-NLS-1$ //$NON-NLS-2$
                                    + pkTableName);
                            LOGGER.debug(logPrefix + "[" + COLUMN_NAME_PK_NAME + "] " + pkName); //$NON-NLS-1$ //$NON-NLS-2$
                            LOGGER.debug(logPrefix + "[" + COLUMN_NAME_UPDATE_RULE + "] " //$NON-NLS-1$ //$NON-NLS-2$
                                    + onUpdateRule);
                            LOGGER.debug(logPrefix + "[" + COLUMN_NAME_DELETE_RULE + "] " //$NON-NLS-1$ //$NON-NLS-2$
                                    + onDeleteRule);
                            LOGGER.debug(logPrefix + "[" + COLUMN_NAME_DEFERRABILITY + "] " //$NON-NLS-1$ //$NON-NLS-2$
                                    + deferrability);
                        }

                        if (null == currFkName || !currFkName.equals(fkName) || keyIsValid) {
                            currFkName = fkName;
                            final String formatFkName = formatter.format(fkName);
                            final String formatFkColumnName = formatter.format(fkColumnName);

                            /*
                             * We need to check for each foreign key's
                             * column that the referenced table exists in
                             * the current context because some columns
                             * might be pointing to a synonym.
                             */
                            final String formatPkTableName = formatter.format(pkTableName);
                            IBasicTable pkTable = allTables.get(formatPkTableName);

                            if (pkTable != null) {

                                if (null == currFk || !formatFkName.equals(currFk.getName())) {
                                    final IKeyConstraint refPk = DBGMHelper.getPrimaryKey(pkTable);

                                    if (refPk != null) {
                                        /*
                                         * FIXME [BGA]: The
                                         * TypedObjectFactory does not work
                                         * as UniqueKeyConstraint and
                                         * ForeignKeyConstraint classes have
                                         * the same super interface
                                         * IKeyConstraint. We use an
                                         * explicit constructor instead.
                                         */
                                        // currFk = typedObjFactory
                                        // .create(ForeignKeyConstraint.class);
                                        // currFk.setName(formatFkName);
                                        // currFk.setConstrainedTable(pkTable);
                                        currFk = new ForeignKeyConstraint(formatFkName, "", //$NON-NLS-1$
                                                pkTable);

                                        currFk.setRemoteConstraint(refPk);
                                        currFk.setOnUpdateAction(
                                                CaptureHelper.getForeignKeyAction(onUpdateRule));
                                        currFk.setOnDeleteAction(
                                                CaptureHelper.getForeignKeyAction(onDeleteRule));
                                        foreignKeys.add(currFk);
                                        keyIsValid = true;
                                    } else {
                                        LOGGER.warn("Foreign key [" + formatFkName
                                                + "] has been ignored during import because the referenced primary key ["
                                                + formatPkTableName + "[" //$NON-NLS-1$
                                                + formatter.format(pkName)
                                                + "]] could not be found in the current workspace");
                                        keyIsValid = false;
                                        continue;
                                    }
                                }

                                final IBasicColumn column = allTablesColumns
                                        .get(CaptureHelper.getUniqueObjectName(tableName, formatFkColumnName));
                                if (column != null) {
                                    /*
                                     * Columns are ordered by PKTABLE_NAME,
                                     * KEY_SEQ in the returned ResultSet, so
                                     * we don't have to specify the position
                                     * of the constrained column when adding
                                     * it to the foreign key constraint.
                                     */
                                    currFk.addColumn(column);
                                } else {
                                    LOGGER.warn("Foreign key [" + formatFkName
                                            + "] has been ignored during import because the referencing column ["
                                            + tableName + "[" + formatFkColumnName //$NON-NLS-1$
                                            + "]] could not be found in the current workspace");
                                    keyIsValid = false;

                                    /*
                                     * Now the foreign key is invalid, we
                                     * remove it from the foreign keys list
                                     * that will be returned to the caller
                                     * of this method.
                                     */
                                    foreignKeys.remove(currFk);
                                }
                            } else {
                                if (LOGGER.isDebugEnabled()) {
                                    LOGGER.debug("Foreign key column [" + formatFkName + "[" //$NON-NLS-2$
                                            + formatFkColumnName
                                            + "]] has been ignored during import because the referenced table ["
                                            + formatPkTableName
                                            + "] could not be found in the current workspace");
                                }
                            }
                        }
                    }
                }
            } finally {
                CaptureHelper.safeClose(rset, null);
            }
        }
    } catch (SQLException sqle) {
        LOGGER.error("Unable to fetch foreign keys for table [" + tableName + "] from "
                + getConnectionVendorName(context) + " server: " + sqle.getMessage(), sqle);
    }

    return foreignKeys;
}

From source file:gsn.wrappers.TetraedreFluoWrapper.java

public void run() {
    DataEnumerator data;//from w  w w  . j a v a2 s .  c  om

    try {
        Thread.sleep(2000);
    } catch (InterruptedException e) {
        logger.error(e.getMessage(), e);
    }

    Connection conn = null;
    ResultSet resultSet = null;

    while (isActive()) {
        try {
            conn = sm.getConnection();
            StringBuilder query = new StringBuilder("select * from ").append(table_name)
                    .append(" where physical_input=").append(physical_input).append(" AND timestamp*1000 > "
                            + latest_timed + "  order by timestamp limit 0," + buffer_size);

            resultSet = sm.executeQueryWithResultSet(query, conn);

            //logger.debug(query);

            while (resultSet.next()) {
                Serializable[] output = new Serializable[this.getOutputFormat().length];

                //long pk = resultSet.getLong(1);
                long timed = resultSet.getLong(3) * 1000;

                //logger.warn("pk => "+ pk);
                //logger.warn("timed => "+ timed);

                for (int i = 0; i < dataFieldsLength; i++) {

                    switch (dataFieldTypes[i]) {
                    case DataTypes.VARCHAR:
                    case DataTypes.CHAR:
                        output[i] = resultSet.getString(i + 1);
                        break;
                    case DataTypes.INTEGER:
                        output[i] = resultSet.getInt(i + 1);
                        break;
                    case DataTypes.TINYINT:
                        output[i] = resultSet.getByte(i + 1);
                        break;
                    case DataTypes.SMALLINT:
                        output[i] = resultSet.getShort(i + 1);
                        break;
                    case DataTypes.DOUBLE:
                        output[i] = resultSet.getDouble(i + 1);
                        break;
                    case DataTypes.FLOAT:
                        output[i] = resultSet.getFloat(i + 1);
                        break;
                    case DataTypes.BIGINT:
                        output[i] = resultSet.getLong(i + 1);
                        break;
                    case DataTypes.BINARY:
                        output[i] = resultSet.getBytes(i + 1);
                        break;
                    }
                    //logger.warn(i+" (type: "+dataFieldTypes[i]+" ) => "+output[i]);
                }

                StreamElement se = new StreamElement(dataFieldNames, dataFieldTypes, output, timed);
                latest_timed = se.getTimeStamp();

                //logger.warn(" Latest => " + latest_timed);

                this.postStreamElement(se);

                updateCheckPointFile(latest_timed);

                //logger.warn(se);
            }

        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            sm.close(resultSet);
            sm.close(conn);
        }

        try {
            Thread.sleep(rate);
        } catch (InterruptedException e) {
            logger.error(e.getMessage(), e);
        }
    }
}

From source file:gov.ca.cwds.es.live.RawClient.java

@Override
public RawClient read(final ResultSet rs) throws SQLException {
    super.read(rs);

    this.cltSensitivityIndicator = trimToNull(rs.getString(ColPos.CLT_SENSTV_IND.ordinal()));
    this.cltSoc158SealedClientIndicator = trimToNull(rs.getString(ColPos.CLT_SOC158_IND.ordinal()));
    this.cltBirthDate = rs.getDate(ColPos.CLT_BIRTH_DT.ordinal());
    this.cltClientIndexNumber = trimToNull(rs.getString(ColPos.CLT_CL_INDX_NO.ordinal()));
    this.cltCommonFirstName = (rs.getString(ColPos.CLT_COM_FST_NM.ordinal()));
    this.cltCommonLastName = trimToNull(rs.getString(ColPos.CLT_COM_LST_NM.ordinal()));
    this.cltCommonMiddleName = trimToNull(rs.getString(ColPos.CLT_COM_MID_NM.ordinal()));
    this.cltCreationDate = rs.getDate(ColPos.CLT_CREATN_DT.ordinal());
    this.cltDeathDate = rs.getDate(ColPos.CLT_DEATH_DT.ordinal());
    this.cltDeathDateVerifiedIndicator = trimToNull(rs.getString(ColPos.CLT_DTH_DT_IND.ordinal()));
    this.cltDriverLicenseNumber = trimToNull(rs.getString(ColPos.CLT_DRV_LIC_NO.ordinal()));
    this.cltDriverLicenseStateCodeType = rs.getShort(ColPos.CLT_D_STATE_C.ordinal());
    this.cltEmailAddress = trimToNull(rs.getString(ColPos.CLT_EMAIL_ADDR.ordinal()));
    this.cltEthUnableToDetReasonCode = trimToNull(rs.getString(ColPos.CLT_ETH_UD_CD.ordinal()));
    this.cltGenderCode = trimToNull(rs.getString(ColPos.CLT_GENDER_CD.ordinal()));
    this.cltHispUnableToDetReasonCode = trimToNull(rs.getString(ColPos.CLT_HISP_UD_CD.ordinal()));
    this.cltHispanicOriginCode = trimToNull(rs.getString(ColPos.CLT_HISP_CD.ordinal()));
    this.cltImmigrationStatusType = rs.getShort(ColPos.CLT_IMGT_STC.ordinal());
    this.cltLiterateCode = trimToNull(rs.getString(ColPos.CLT_LITRATE_CD.ordinal()));
    this.cltMaritalStatusType = rs.getShort(ColPos.CLT_MRTL_STC.ordinal());
    this.cltMilitaryStatusCode = trimToNull(rs.getString(ColPos.CLT_MILT_STACD.ordinal()));
    this.cltNamePrefixDescription = trimToNull(rs.getString(ColPos.CLT_NMPRFX_DSC.ordinal()));
    this.cltNameType = rs.getShort(ColPos.CLT_NAME_TPC.ordinal());

    this.cltPrimaryEthnicityType = rs.getShort(ColPos.CLT_P_ETHNCTYC.ordinal());
    this.cltPrimaryLanguageType = rs.getShort(ColPos.CLT_P_LANG_TPC.ordinal());
    this.cltSecondaryLanguageType = rs.getShort(ColPos.CLT_S_LANG_TC.ordinal());
    this.cltReligionType = rs.getShort(ColPos.CLT_RLGN_TPC.ordinal());

    this.cltSensitiveHlthInfoOnFileIndicator = trimToNull(rs.getString(ColPos.CLT_SNTV_HLIND.ordinal()));
    this.cltSoc158PlacementCode = trimToNull(rs.getString(ColPos.CLT_SOCPLC_CD.ordinal()));
    this.cltSocialSecurityNumChangedCode = trimToNull(rs.getString(ColPos.CLT_SSN_CHG_CD.ordinal()));
    this.cltSocialSecurityNumber = trimToNull(rs.getString(ColPos.CLT_SS_NO.ordinal()));
    this.cltSuffixTitleDescription = trimToNull(rs.getString(ColPos.CLT_SUFX_TLDSC.ordinal()));
    this.cltTribalAncestryClientIndicatorVar = trimToNull(rs.getString(ColPos.CLT_TRBA_CLT_B.ordinal()));
    this.cltZippyCreatedIndicator = trimToNull(rs.getString(ColPos.CLT_ZIPPY_IND.ordinal()));

    this.cltLastUpdatedId = trimToNull(rs.getString(ColPos.CLT_LST_UPD_ID.ordinal()));
    this.cltLastUpdatedTime = rs.getTimestamp(ColPos.CLT_LST_UPD_TS.ordinal());

    return this;
}

From source file:eionet.meta.dao.mysql.VocabularyFolderDAOImpl.java

/**
 * {@inheritDoc}//  w  ww . j a v a  2 s  . c  o m
 */
@Override
public VocabularyFolder getVocabularyFolder(int vocabularyFolderId) {
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("id", vocabularyFolderId);

    StringBuilder sql = new StringBuilder();
    sql.append(
            "select v.VOCABULARY_ID, v.IDENTIFIER, v.LABEL, v.REG_STATUS, v.WORKING_COPY, v.BASE_URI, v.VOCABULARY_TYPE, ");
    sql.append("v.WORKING_USER, v.DATE_MODIFIED, v.USER_MODIFIED, v.CHECKEDOUT_COPY_ID, v.CONTINUITY_ID, ");
    sql.append("v.CONCEPT_IDENTIFIER_NUMERIC, ");
    sql.append("f.ID, f.IDENTIFIER, f.LABEL, v.NOTATIONS_EQUAL_IDENTIFIERS ");
    sql.append("from VOCABULARY v ");
    sql.append("left join VOCABULARY_SET f on f.ID=v.FOLDER_ID ");
    sql.append("where VOCABULARY_ID=:id");

    VocabularyFolder result = getNamedParameterJdbcTemplate().queryForObject(sql.toString(), params,
            new RowMapper<VocabularyFolder>() {
                @Override
                public VocabularyFolder mapRow(ResultSet rs, int rowNum) throws SQLException {
                    VocabularyFolder vf = new VocabularyFolder();
                    vf.setId(rs.getInt("v.VOCABULARY_ID"));
                    vf.setIdentifier(rs.getString("v.IDENTIFIER"));
                    vf.setLabel(rs.getString("v.LABEL"));
                    vf.setRegStatus(RegStatus.fromString(rs.getString("v.REG_STATUS")));
                    vf.setType(VocabularyType.valueOf(rs.getString("v.VOCABULARY_TYPE")));
                    vf.setWorkingCopy(rs.getBoolean("v.WORKING_COPY"));
                    vf.setWorkingUser(rs.getString("v.WORKING_USER"));
                    vf.setDateModified(rs.getTimestamp("v.DATE_MODIFIED"));
                    vf.setUserModified(rs.getString("v.USER_MODIFIED"));
                    vf.setCheckedOutCopyId(rs.getInt("v.CHECKEDOUT_COPY_ID"));
                    vf.setContinuityId(rs.getString("v.CONTINUITY_ID"));
                    vf.setNumericConceptIdentifiers(rs.getBoolean("v.CONCEPT_IDENTIFIER_NUMERIC"));
                    vf.setNotationsEqualIdentifiers(rs.getBoolean("NOTATIONS_EQUAL_IDENTIFIERS"));
                    vf.setBaseUri(rs.getString("v.BASE_URI"));
                    vf.setFolderId(rs.getShort("f.ID"));
                    vf.setFolderName(rs.getString("f.IDENTIFIER"));
                    vf.setFolderLabel(rs.getString("f.LABEL"));
                    return vf;
                }
            });

    return result;
}

From source file:eionet.meta.dao.mysql.VocabularyFolderDAOImpl.java

/**
 * {@inheritDoc}/*from   ww  w.j av  a 2  s.c  om*/
 */
@Override
public VocabularyFolder getVocabularyWorkingCopy(int checkedOutCopyId) {
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("checkedOutCopyId", checkedOutCopyId);

    StringBuilder sql = new StringBuilder();
    sql.append(
            "select v.VOCABULARY_ID, v.IDENTIFIER, v.LABEL, v.REG_STATUS, v.WORKING_COPY, v.BASE_URI, v.VOCABULARY_TYPE, ");
    sql.append("v.WORKING_USER, v.DATE_MODIFIED, v.USER_MODIFIED, v.CHECKEDOUT_COPY_ID, v.CONTINUITY_ID, ");
    sql.append("v.CONCEPT_IDENTIFIER_NUMERIC, ");
    sql.append("f.ID, f.IDENTIFIER, f.LABEL, v.NOTATIONS_EQUAL_IDENTIFIERS ");
    sql.append("from VOCABULARY v ");
    sql.append("left join VOCABULARY_SET f on f.ID=v.FOLDER_ID ");
    sql.append("where v.CHECKEDOUT_COPY_ID=:checkedOutCopyId");

    VocabularyFolder result = getNamedParameterJdbcTemplate().queryForObject(sql.toString(), params,
            new RowMapper<VocabularyFolder>() {
                @Override
                public VocabularyFolder mapRow(ResultSet rs, int rowNum) throws SQLException {
                    VocabularyFolder vf = new VocabularyFolder();
                    vf.setId(rs.getInt("v.VOCABULARY_ID"));
                    vf.setIdentifier(rs.getString("v.IDENTIFIER"));
                    vf.setLabel(rs.getString("v.LABEL"));
                    vf.setRegStatus(RegStatus.fromString(rs.getString("v.REG_STATUS")));
                    vf.setType(VocabularyType.valueOf(rs.getString("v.VOCABULARY_TYPE")));
                    vf.setWorkingCopy(rs.getBoolean("v.WORKING_COPY"));
                    vf.setWorkingUser(rs.getString("v.WORKING_USER"));
                    vf.setDateModified(rs.getTimestamp("v.DATE_MODIFIED"));
                    vf.setUserModified(rs.getString("v.USER_MODIFIED"));
                    vf.setCheckedOutCopyId(rs.getInt("v.CHECKEDOUT_COPY_ID"));
                    vf.setContinuityId(rs.getString("v.CONTINUITY_ID"));
                    vf.setNumericConceptIdentifiers(rs.getBoolean("v.CONCEPT_IDENTIFIER_NUMERIC"));
                    vf.setNotationsEqualIdentifiers(rs.getBoolean("NOTATIONS_EQUAL_IDENTIFIERS"));
                    vf.setBaseUri(rs.getString("v.BASE_URI"));
                    vf.setFolderId(rs.getShort("f.ID"));
                    vf.setFolderName(rs.getString("f.IDENTIFIER"));
                    vf.setFolderLabel(rs.getString("f.LABEL"));
                    return vf;
                }
            });

    return result;
}

From source file:eionet.meta.dao.mysql.VocabularyFolderDAOImpl.java

@Override
public VocabularyFolder getVocabularyFolderOfConcept(int conceptId) {
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("conceptId", conceptId);

    StringBuilder sql = new StringBuilder();
    sql.append(/*from   w w  w . j  a va2s.  com*/
            "select v.VOCABULARY_ID, v.IDENTIFIER, v.LABEL, v.REG_STATUS, v.WORKING_COPY, v.BASE_URI, v.VOCABULARY_TYPE, ");
    sql.append("v.WORKING_USER, v.DATE_MODIFIED, v.USER_MODIFIED, v.CHECKEDOUT_COPY_ID, v.CONTINUITY_ID, ");
    sql.append("v.CONCEPT_IDENTIFIER_NUMERIC, ");
    sql.append("f.ID, f.IDENTIFIER, f.LABEL, v.NOTATIONS_EQUAL_IDENTIFIERS ");
    sql.append("from VOCABULARY v ");
    sql.append("left join VOCABULARY_SET f on f.ID=v.FOLDER_ID ");
    sql.append("where VOCABULARY_ID=");
    sql.append("(select VOCABULARY_ID from VOCABULARY_CONCEPT where VOCABULARY_CONCEPT_ID=:conceptId)");

    VocabularyFolder result = getNamedParameterJdbcTemplate().queryForObject(sql.toString(), params,
            new RowMapper<VocabularyFolder>() {
                @Override
                public VocabularyFolder mapRow(ResultSet rs, int rowNum) throws SQLException {
                    VocabularyFolder vf = new VocabularyFolder();
                    vf.setId(rs.getInt("v.VOCABULARY_ID"));
                    vf.setIdentifier(rs.getString("v.IDENTIFIER"));
                    vf.setLabel(rs.getString("v.LABEL"));
                    vf.setRegStatus(RegStatus.fromString(rs.getString("v.REG_STATUS")));
                    vf.setType(VocabularyType.valueOf(rs.getString("v.VOCABULARY_TYPE")));
                    vf.setWorkingCopy(rs.getBoolean("v.WORKING_COPY"));
                    vf.setWorkingUser(rs.getString("v.WORKING_USER"));
                    vf.setDateModified(rs.getTimestamp("v.DATE_MODIFIED"));
                    vf.setUserModified(rs.getString("v.USER_MODIFIED"));
                    vf.setCheckedOutCopyId(rs.getInt("v.CHECKEDOUT_COPY_ID"));
                    vf.setContinuityId(rs.getString("v.CONTINUITY_ID"));
                    vf.setNumericConceptIdentifiers(rs.getBoolean("v.CONCEPT_IDENTIFIER_NUMERIC"));
                    vf.setNotationsEqualIdentifiers(rs.getBoolean("NOTATIONS_EQUAL_IDENTIFIERS"));
                    vf.setBaseUri(rs.getString("v.BASE_URI"));
                    vf.setFolderId(rs.getShort("f.ID"));
                    vf.setFolderName(rs.getString("f.IDENTIFIER"));
                    vf.setFolderLabel(rs.getString("f.LABEL"));
                    return vf;
                }
            });

    return result;
}

From source file:eionet.meta.dao.mysql.VocabularyFolderDAOImpl.java

/**
 * {@inheritDoc}/*from   ww w  . j  ava  2 s.c om*/
 */
@Override
public VocabularyFolder getVocabularyFolder(String folderName, String identifier, boolean workingCopy) {
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("identifier", identifier);
    params.put("workingCopy", workingCopy);
    params.put("folderIdentifier", folderName);

    StringBuilder sql = new StringBuilder();
    sql.append(
            "select v.VOCABULARY_ID, v.IDENTIFIER, v.LABEL, v.REG_STATUS, v.WORKING_COPY, v.BASE_URI, v.VOCABULARY_TYPE, ");
    sql.append("v.WORKING_USER, v.DATE_MODIFIED, v.USER_MODIFIED, v.CHECKEDOUT_COPY_ID, v.CONTINUITY_ID, ");
    sql.append("v.CONCEPT_IDENTIFIER_NUMERIC, ");
    sql.append("f.ID, f.IDENTIFIER, f.LABEL, v.NOTATIONS_EQUAL_IDENTIFIERS ");
    sql.append("from VOCABULARY v ");
    sql.append("left join VOCABULARY_SET f on f.ID=v.FOLDER_ID ");
    sql.append(
            "where v.IDENTIFIER=:identifier and v.WORKING_COPY=:workingCopy and f.IDENTIFIER=:folderIdentifier");

    VocabularyFolder result = getNamedParameterJdbcTemplate().queryForObject(sql.toString(), params,
            new RowMapper<VocabularyFolder>() {
                @Override
                public VocabularyFolder mapRow(ResultSet rs, int rowNum) throws SQLException {
                    VocabularyFolder vf = new VocabularyFolder();
                    vf.setId(rs.getInt("v.VOCABULARY_ID"));
                    vf.setIdentifier(rs.getString("v.IDENTIFIER"));
                    vf.setLabel(rs.getString("v.LABEL"));
                    vf.setRegStatus(RegStatus.fromString(rs.getString("v.REG_STATUS")));
                    vf.setType(VocabularyType.valueOf(rs.getString("v.VOCABULARY_TYPE")));
                    vf.setWorkingCopy(rs.getBoolean("v.WORKING_COPY"));
                    vf.setWorkingUser(rs.getString("v.WORKING_USER"));
                    vf.setDateModified(rs.getTimestamp("v.DATE_MODIFIED"));
                    vf.setUserModified(rs.getString("v.USER_MODIFIED"));
                    vf.setCheckedOutCopyId(rs.getInt("v.CHECKEDOUT_COPY_ID"));
                    vf.setContinuityId(rs.getString("v.CONTINUITY_ID"));
                    vf.setNumericConceptIdentifiers(rs.getBoolean("v.CONCEPT_IDENTIFIER_NUMERIC"));
                    vf.setNotationsEqualIdentifiers(rs.getBoolean("NOTATIONS_EQUAL_IDENTIFIERS"));
                    vf.setBaseUri(rs.getString("v.BASE_URI"));
                    vf.setFolderId(rs.getShort("f.ID"));
                    vf.setFolderName(rs.getString("f.IDENTIFIER"));
                    vf.setFolderLabel(rs.getString("f.LABEL"));
                    return vf;
                }
            });

    return result;
}

From source file:eionet.meta.dao.mysql.VocabularyFolderDAOImpl.java

@Override
public List<VocabularyFolder> getRecentlyReleasedVocabularyFolders(int limit) {

    StringBuilder sql = new StringBuilder();
    sql.append(/* w w w . j av  a2 s  .c om*/
            "select v.VOCABULARY_ID, v.IDENTIFIER, v.LABEL, v.REG_STATUS, v.WORKING_COPY, v.BASE_URI, v.VOCABULARY_TYPE, ");
    sql.append("v.WORKING_USER, v.DATE_MODIFIED, v.USER_MODIFIED, v.CHECKEDOUT_COPY_ID, v.CONTINUITY_ID, ");
    sql.append("v.CONCEPT_IDENTIFIER_NUMERIC, v.NOTATIONS_EQUAL_IDENTIFIERS, f.ID, f.IDENTIFIER, f.LABEL ");
    sql.append("from VOCABULARY v ");
    sql.append("left join VOCABULARY_SET f on f.ID=v.FOLDER_ID ");
    sql.append("where v.WORKING_COPY=FALSE and v.REG_STATUS like :releasedStatus ");
    sql.append("order by v.DATE_MODIFIED desc, v.IDENTIFIER asc ");
    sql.append("limit :limit");

    Map<String, Object> params = new HashMap<String, Object>();
    params.put("releasedStatus", RegStatus.RELEASED.toString());
    params.put("limit", limit);

    List<VocabularyFolder> items = getNamedParameterJdbcTemplate().query(sql.toString(), params,
            new RowMapper<VocabularyFolder>() {
                @Override
                public VocabularyFolder mapRow(ResultSet rs, int rowNum) throws SQLException {
                    VocabularyFolder vf = new VocabularyFolder();
                    vf.setId(rs.getInt("v.VOCABULARY_ID"));
                    vf.setIdentifier(rs.getString("v.IDENTIFIER"));
                    vf.setLabel(rs.getString("v.LABEL"));
                    vf.setRegStatus(RegStatus.fromString(rs.getString("v.REG_STATUS")));
                    vf.setType(VocabularyType.valueOf(rs.getString("v.VOCABULARY_TYPE")));
                    vf.setWorkingCopy(rs.getBoolean("v.WORKING_COPY"));
                    vf.setWorkingUser(rs.getString("v.WORKING_USER"));
                    vf.setDateModified(rs.getTimestamp("v.DATE_MODIFIED"));
                    vf.setUserModified(rs.getString("v.USER_MODIFIED"));
                    vf.setCheckedOutCopyId(rs.getInt("v.CHECKEDOUT_COPY_ID"));
                    vf.setContinuityId(rs.getString("v.CONTINUITY_ID"));
                    vf.setNumericConceptIdentifiers(rs.getBoolean("v.CONCEPT_IDENTIFIER_NUMERIC"));
                    vf.setNotationsEqualIdentifiers(rs.getBoolean("NOTATIONS_EQUAL_IDENTIFIERS"));
                    vf.setBaseUri(rs.getString("v.BASE_URI"));
                    vf.setFolderId(rs.getShort("f.ID"));
                    vf.setFolderName(rs.getString("f.IDENTIFIER"));
                    vf.setFolderLabel(rs.getString("f.LABEL"));
                    return vf;
                }
            });

    return items;

}

From source file:eionet.meta.dao.mysql.VocabularyFolderDAOImpl.java

@Override
public List<VocabularyFolder> getReleasedVocabularyFolders(int folderId) {
    List<String> statuses = new ArrayList<String>();
    statuses.add(RegStatus.RELEASED.toString());
    statuses.add(RegStatus.PUBLIC_DRAFT.toString());

    Map<String, Object> params = new HashMap<String, Object>();
    params.put("folderId", folderId);
    params.put("statuses", statuses);

    StringBuilder sql = new StringBuilder();
    sql.append(/*w  ww.j  a  va  2s  .  co  m*/
            "select v.VOCABULARY_ID, v.IDENTIFIER, v.LABEL, v.REG_STATUS, v.WORKING_COPY, v.BASE_URI, v.VOCABULARY_TYPE, ");
    sql.append("v.WORKING_USER, v.DATE_MODIFIED, v.USER_MODIFIED, v.CHECKEDOUT_COPY_ID, v.CONTINUITY_ID, ");
    sql.append("v.CONCEPT_IDENTIFIER_NUMERIC, ");
    sql.append("f.ID, f.IDENTIFIER, f.LABEL, v.NOTATIONS_EQUAL_IDENTIFIERS ");
    sql.append("from VOCABULARY v ");
    sql.append("left join VOCABULARY_SET f on f.ID=v.FOLDER_ID ");
    sql.append("where v.WORKING_COPY=FALSE and v.FOLDER_ID=:folderId and v.REG_STATUS in (:statuses) ");
    sql.append("order by f.IDENTIFIER, v.IDENTIFIER ");

    List<VocabularyFolder> items = getNamedParameterJdbcTemplate().query(sql.toString(), params,
            new RowMapper<VocabularyFolder>() {
                @Override
                public VocabularyFolder mapRow(ResultSet rs, int rowNum) throws SQLException {
                    VocabularyFolder vf = new VocabularyFolder();
                    vf.setId(rs.getInt("v.VOCABULARY_ID"));
                    vf.setIdentifier(rs.getString("v.IDENTIFIER"));
                    vf.setLabel(rs.getString("v.LABEL"));
                    vf.setRegStatus(RegStatus.fromString(rs.getString("v.REG_STATUS")));
                    vf.setType(VocabularyType.valueOf(rs.getString("v.VOCABULARY_TYPE")));
                    vf.setWorkingCopy(rs.getBoolean("v.WORKING_COPY"));
                    vf.setWorkingUser(rs.getString("v.WORKING_USER"));
                    vf.setDateModified(rs.getTimestamp("v.DATE_MODIFIED"));
                    vf.setUserModified(rs.getString("v.USER_MODIFIED"));
                    vf.setCheckedOutCopyId(rs.getInt("v.CHECKEDOUT_COPY_ID"));
                    vf.setContinuityId(rs.getString("v.CONTINUITY_ID"));
                    vf.setNumericConceptIdentifiers(rs.getBoolean("v.CONCEPT_IDENTIFIER_NUMERIC"));
                    vf.setNotationsEqualIdentifiers(rs.getBoolean("NOTATIONS_EQUAL_IDENTIFIERS"));
                    vf.setBaseUri(rs.getString("v.BASE_URI"));
                    vf.setFolderId(rs.getShort("f.ID"));
                    vf.setFolderName(rs.getString("f.IDENTIFIER"));
                    vf.setFolderLabel(rs.getString("f.LABEL"));
                    return vf;
                }
            });

    return items;
}