Example usage for java.sql ResultSet getTimestamp

List of usage examples for java.sql ResultSet getTimestamp

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

Introduction

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

Prototype

java.sql.Timestamp getTimestamp(String columnLabel) throws SQLException;

Source Link

Document

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

Usage

From source file:com.krminc.phr.security.PHRRealm.java

private void InvalidPasswordAttempt(String username) throws NoSuchUserException {
    String query = "SELECT failed_password_attempts, failed_password_window_start FROM user_users WHERE username = ?";
    ResultSet rs = null;
    PreparedStatement st = null;//from  w  ww  .j  a v  a 2 s .c  om
    Timestamp windowStart = null;
    int failedAttemptsVal = 0;
    try {
        createDS();
        conn = ds.getNonTxConnection();
        st = conn.prepareStatement(query);
        st.setString(1, username);
        rs = st.executeQuery();
    } catch (Exception e) {
        log("Error getting roles from database");
        log(e.getMessage());
        rs = null;
    } finally {
        try {
            conn.close();
        } catch (Exception e) {
            log(e.getMessage());
        }
        conn = null;
    }
    if (rs != null) {
        try {
            rs.beforeFirst();
            while (rs.next()) {
                failedAttemptsVal = rs.getInt(1);
                windowStart = rs.getTimestamp(2);
            }
        } catch (Exception e) {
            log("Error getting invalid attempt values from resultset");
            log(e.getMessage());
        } finally {
            try {
                st.close();
                rs.close();
            } catch (Exception e) {
                log(e.getMessage());
            }
        }
    } else {
        throw new NoSuchUserException("User not available.");
    }

    //take values and decide whether to lock account, increment failed attempts, or start new failure window
    if (windowStart != null) {
        //check if user has more than X previously existing failed logins
        if (Integer.valueOf(failedAttemptsVal).compareTo(failedAttempts) >= 0) {
            //lock out
            doFailedUpdate(username, null, failedAttemptsVal + 1, true);
        } else {
            //dont lock account, just increment failed attempts
            doFailedUpdate(username, windowStart, failedAttemptsVal + 1, false);
        }
    } else {
        //windowStart is null, set to now and set failed attempts to 1
        GregorianCalendar tempCal = new GregorianCalendar();
        windowStart = new java.sql.Timestamp(tempCal.getTimeInMillis());
        failedAttemptsVal = 1;
        doFailedUpdate(username, windowStart, failedAttemptsVal, false);
    }

}

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

/**
 * {@inheritDoc}/*from  w  w w . j  ava2s . 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 w ww .j a  va2s . c o  m*/
 */
@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(/*  w  w w . j  av a  2  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, ");
    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:com.sf.ddao.orm.RSMapperFactoryRegistry.java

public static RowMapperFactory getScalarMapper(final Type itemType, final int idx, boolean req) {
    if (itemType == String.class) {
        return new ScalarRMF() {
            public Object map(ResultSet rs) throws SQLException {
                return rs.getString(idx);
            }//from   w  ww  . j a v a  2  s .co  m
        };
    }
    if (itemType == Integer.class || itemType == Integer.TYPE) {
        return new ScalarRMF() {
            public Object map(ResultSet rs) throws SQLException {
                return rs.getInt(idx);
            }
        };
    }
    if (itemType == URL.class) {
        return new ScalarRMF() {
            public Object map(ResultSet rs) throws SQLException {
                return rs.getURL(idx);
            }
        };
    }
    if (itemType == BigInteger.class) {
        return new ScalarRMF() {
            public Object map(ResultSet rs) throws SQLException {
                final BigDecimal res = rs.getBigDecimal(idx);
                return res == null ? null : res.toBigInteger();
            }
        };
    }
    if (itemType == BigDecimal.class) {
        return new ScalarRMF() {
            public Object map(ResultSet rs) throws SQLException {
                return rs.getBigDecimal(idx);
            }
        };
    }
    if (itemType == InputStream.class) {
        return new ScalarRMF() {
            public Object map(ResultSet rs) throws SQLException {
                return rs.getBinaryStream(idx);
            }
        };
    }
    if (itemType == Boolean.class || itemType == Boolean.TYPE) {
        return new ScalarRMF() {
            public Object map(ResultSet rs) throws SQLException {
                return rs.getBoolean(idx);
            }
        };
    }
    if (itemType == Blob.class) {
        return new ScalarRMF() {
            public Object map(ResultSet rs) throws SQLException {
                return rs.getBlob(idx);
            }
        };
    }
    if (itemType == java.sql.Date.class || itemType == java.util.Date.class) {
        return new ScalarRMF() {
            public Object map(ResultSet rs) throws SQLException {
                return rs.getTimestamp(idx);
            }
        };
    }
    if (itemType instanceof Class) {
        final Class itemClass = (Class) itemType;
        final ColumnMapper columnMapper = ColumnMapperRegistry.lookup(itemClass);
        if (columnMapper != null) {
            return new ScalarRMF() {
                public Object map(ResultSet rs) throws SQLException {
                    return columnMapper.map(rs, idx);
                }
            };
        }
        final Converter converter = ConvertUtils.lookup(itemClass);
        if (converter != null) {
            return new ScalarRMF() {
                public Object map(ResultSet rs) throws SQLException {
                    String s = rs.getString(idx);
                    if (s == null) {
                        return null;
                    }
                    return converter.convert(itemClass, s);
                }
            };
        }
        if (Enum.class.isAssignableFrom((Class<?>) itemType)) {
            return new ScalarRMF() {
                public Object map(ResultSet rs) throws SQLException {
                    String s = rs.getString(idx);
                    if (s == null) {
                        return null;
                    }
                    //noinspection unchecked
                    return Enum.valueOf((Class<Enum>) itemType, s);
                }
            };
        }
    }
    if (req) {
        throw new IllegalArgumentException("no mapping defined for " + itemType);
    }
    return null;
}

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

/**
 * {@inheritDoc}//from w w w  .j  a  va 2s. com
 */
@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(// ww  w .  jav  a  2s  .  c  o  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, 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(//from ww  w  . j  ava  2  s.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 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;
}

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

/**
 * {@inheritDoc}/*from   w  w  w  .  j  a v  a 2s  . c o m*/
 */
@Override
public List<VocabularyFolder> getVocabularyFolders(String userName) {
    Map<String, Object> params = new HashMap<String, Object>();

    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 ");
    if (StringUtils.isBlank(userName)) {
        sql.append("v.WORKING_COPY=FALSE ");
    } else {
        sql.append("(v.WORKING_COPY=FALSE or v.WORKING_USER=:workingUser) ");
        params.put("workingUser", userName);
    }
    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;
}

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

/**
 * @see eionet.meta.dao.ISchemaDAO#searchSchemas(eionet.meta.service.data.SchemaFilter)
 *//*  w ww .j  av  a2s. com*/
@Override
public SchemasResult searchSchemas(SchemaFilter searchFilter) {

    int nameAttrId = getNameAttributeId();

    StringBuilder sql = new StringBuilder().append("select ")
            .append("S.*, SS.*, ATR1.VALUE as NAME_ATTR, atr2.VALUE as SS_NAME_ATTR, ")
            .append("if(SS.SCHEMA_SET_ID is null, S.WORKING_COPY, SS.WORKING_COPY) as WCOPY, ")
            .append("if(SS.SCHEMA_SET_ID is null, S.WORKING_USER, SS.WORKING_USER) as WUSER, ")
            .append("if(SS.SCHEMA_SET_ID is null, S.REG_STATUS, SS.REG_STATUS) as REGSTAT ").append("from ")
            .append("T_SCHEMA as S left outer join T_SCHEMA_SET as SS on (S.SCHEMA_SET_ID=SS.SCHEMA_SET_ID) ")
            .append("left outer join ATTRIBUTE as ATR1 on ")
            .append("(S.SCHEMA_ID=ATR1.DATAELEM_ID and ATR1.PARENT_TYPE=:attrParentType1 ")
            .append("and ATR1.M_ATTRIBUTE_ID=:nameAttrId) ").append("left outer join ATTRIBUTE as atr2 on ")
            .append("(S.SCHEMA_SET_ID=atr2.DATAELEM_ID and atr2.PARENT_TYPE=:attrParentType2 ")
            .append("and atr2.M_ATTRIBUTE_ID=:nameAttrId) ").append("where 1=1 ");

    String searchingUser = searchFilter.getSearchingUser();
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("attrParentType1", DElemAttribute.ParentType.SCHEMA.toString());
    params.put("attrParentType2", DElemAttribute.ParentType.SCHEMA_SET.toString());
    params.put("nameAttrId", nameAttrId);

    // Where clause
    if (searchFilter.isValued()) {
        if (StringUtils.isNotEmpty(searchFilter.getFileName())) {
            sql.append("and S.FILENAME like :fileName ");
            params.put("fileName", "%" + searchFilter.getFileName() + "%");
        }
        if (StringUtils.isNotEmpty(searchFilter.getSchemaSetIdentifier())) {
            sql.append("and SS.IDENTIFIER like :schemaSetIdentifier ");
            params.put("schemaSetIdentifier", "%" + searchFilter.getSchemaSetIdentifier() + "%");
        }
        if (searchFilter.isAttributesValued()) {
            for (int i = 0; i < searchFilter.getAttributes().size(); i++) {
                Attribute a = searchFilter.getAttributes().get(i);
                String idKey = "attrId" + i;
                String valueKey = "attrValue" + i;
                if (StringUtils.isNotEmpty(a.getValue())) {
                    sql.append("and ");
                    sql.append("S.SCHEMA_ID IN ( ");
                    sql.append("select A.DATAELEM_ID from ATTRIBUTE A where ");
                    sql.append("A.M_ATTRIBUTE_ID = :" + idKey + " and A.VALUE like :" + valueKey
                            + " and A.PARENT_TYPE = :parentType ");
                    sql.append(") ");
                }
                params.put(idKey, a.getId());
                String value = "%" + a.getValue() + "%";
                params.put(valueKey, value);
                params.put("parentType", DElemAttribute.ParentType.SCHEMA.toString());
            }
        }
    }

    // Having.
    if (StringUtils.isBlank(searchingUser)) {
        sql.append("having (WCOPY=false ");
        if (StringUtils.isEmpty(searchFilter.getRegStatus())) {
            sql.append("AND REGSTAT IN ( :regStatusPublic ) ");
            params.put("regStatusPublic", RegStatus.getPublicStatuses());
        }
        sql.append(") ");
    } else {
        sql.append("having ((WCOPY=false or WUSER=:workingUser)");
        params.put("workingUser", searchingUser);
        if (StringUtils.isNotEmpty(searchFilter.getRegStatus())) {
            sql.append(" and REGSTAT=:regStatus");
            params.put("regStatus", searchFilter.getRegStatus().toString());
        }
        sql.append(") ");
    }

    // Sorting
    if (StringUtils.isNotEmpty(searchFilter.getSortProperty())) {
        sql.append("order by ").append(searchFilter.getSortProperty());
        if (SortOrderEnum.ASCENDING.equals(searchFilter.getSortOrder())) {
            sql.append(" asc ");
        } else {
            sql.append(" desc ");
        }
    }

    if (searchFilter.isUsePaging()) {
        sql.append(" LIMIT ").append(searchFilter.getOffset()).append(",").append(searchFilter.getPageSize());
    }

    // LOGGER.debug("SQL: " + sql.toString());

    List<Schema> resultList = getNamedParameterJdbcTemplate().query(sql.toString(), params,
            new RowMapper<Schema>() {
                @Override
                public Schema mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Schema schema = new Schema();
                    schema.setId(rs.getInt("S.SCHEMA_ID"));
                    schema.setSchemaSetId(rs.getInt("SS.SCHEMA_SET_ID"));
                    schema.setFileName(rs.getString("S.FILENAME"));
                    schema.setContinuityId(rs.getString("S.CONTINUITY_ID"));
                    schema.setRegStatus(RegStatus.fromString(rs.getString("S.REG_STATUS")));
                    schema.setWorkingCopy(rs.getBoolean("S.WORKING_COPY"));
                    schema.setWorkingUser(rs.getString("S.WORKING_USER"));
                    schema.setDateModified(rs.getTimestamp("S.DATE_MODIFIED"));
                    schema.setUserModified(rs.getString("S.USER_MODIFIED"));
                    schema.setComment(rs.getString("S.COMMENT"));
                    schema.setCheckedOutCopyId(rs.getInt("S.CHECKEDOUT_COPY_ID"));
                    schema.setSchemaSetIdentifier(rs.getString("SS.IDENTIFIER"));
                    schema.setSchemaSetWorkingCopy(rs.getBoolean("SS.WORKING_COPY"));
                    schema.setSchemaSetWorkingUser(rs.getString("SS.WORKING_USER"));
                    schema.setNameAttribute(rs.getString("NAME_ATTR"));
                    schema.setSchemaSetNameAttribute(rs.getString("SS_NAME_ATTR"));
                    schema.setOtherDocument(rs.getBoolean("S.OTHER_DOCUMENT"));
                    return schema;
                }
            });

    String totalSql = "SELECT FOUND_ROWS()";
    int totalItems = getJdbcTemplate().queryForInt(totalSql);

    SchemasResult result = new SchemasResult(resultList, totalItems, searchFilter);
    return result;
}