Example usage for java.sql ResultSet getClob

List of usage examples for java.sql ResultSet getClob

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

Introduction

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

Prototype

Clob getClob(String columnLabel) throws SQLException;

Source Link

Document

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

Usage

From source file:org.zaproxy.zap.extension.websocket.db.TableWebSocket.java

/**
 * @param rs/*from  w w w  .j av  a 2 s . c o m*/
 * @param interpretLiteralBytes
 * @param payloadLength
 * @return
 * @throws HttpMalformedHeaderException
 * @throws SQLException
 * @throws DatabaseException
 */
private List<WebSocketMessageDTO> buildMessageDTOs(ResultSet rs, boolean interpretLiteralBytes,
        int payloadLength) throws SQLException, DatabaseException {
    ArrayList<WebSocketMessageDTO> messages = new ArrayList<>();
    try {
        while (rs.next()) {
            WebSocketMessageDTO message;

            int channelId = rs.getInt("channel_id");
            WebSocketChannelDTO channel = getChannel(channelId);

            if (rs.getInt("fuzz_id") != 0) {
                WebSocketFuzzMessageDTO fuzzMessage = new WebSocketFuzzMessageDTO(channel);
                fuzzMessage.fuzzId = rs.getInt("fuzz_id");
                fuzzMessage.state = WebSocketFuzzMessageDTO.State.valueOf(rs.getString("state"));
                fuzzMessage.fuzz = rs.getString("fuzz");

                message = fuzzMessage;
            } else {
                message = new WebSocketMessageDTO(channel);
            }

            message.id = rs.getInt("message_id");
            message.setTime(rs.getTimestamp("timestamp"));
            message.opcode = rs.getInt("opcode");
            message.readableOpcode = WebSocketMessage.opcode2string(message.opcode);

            // read payload
            if (message.opcode == WebSocketMessage.OPCODE_BINARY) {
                if (payloadLength == -1) {
                    // load all bytes
                    message.payload = rs.getBytes("payload_bytes");
                } else {
                    Blob blob = rs.getBlob("payload_bytes");
                    int length = Math.min(payloadLength, (int) blob.length());
                    message.payload = blob.getBytes(1, length);
                    blob.free();
                }

                if (message.payload == null) {
                    message.payload = new byte[0];
                }
            } else {
                if (payloadLength == -1) {
                    // load all characters
                    message.payload = rs.getString("payload_utf8");
                } else {
                    Clob clob = rs.getClob("payload_utf8");
                    int length = Math.min(payloadLength, (int) clob.length());
                    message.payload = clob.getSubString(1, length);
                    clob.free();
                }

                if (message.payload == null) {
                    message.payload = "";
                }
            }

            message.isOutgoing = rs.getBoolean("is_outgoing");
            message.payloadLength = rs.getInt("payload_length");

            messages.add(message);
        }
    } finally {
        rs.close();
    }

    messages.trimToSize();

    return messages;
}

From source file:edu.harvard.i2b2.ontology.dao.GetChildrenDao.java

public List findChildrenByParent(final GetChildrenType childrenType, final List categories,
        final ProjectType projectInfo) throws DataAccessException {

    DataSource ds = null;//from  w  w  w.  j  av a2s  .c  o m
    try {
        ds = OntologyUtil.getInstance().getDataSource("java:OntologyLocalDS");
    } catch (I2B2Exception e2) {
        log.error(e2.getMessage());
    }

    SimpleJdbcTemplate jt = new SimpleJdbcTemplate(ds);

    // find return parameters
    String parameters = DEFAULT;
    if (childrenType.getType().equals("core")) {
        parameters = CORE;
    } else if (childrenType.getType().equals("all")) {
        parameters = ALL;
    }
    if (childrenType.isBlob() == true)
        parameters = parameters + BLOB;

    //extract table code
    String tableCd = StringUtil.getTableCd(childrenType.getParent());

    // table code to table name conversion
    // Get metadata schema name from properties file.
    String metadataSchema = "";
    try {
        metadataSchema = OntologyUtil.getInstance().getMetaDataSchemaName();
    } catch (I2B2Exception e1) {
        log.error(e1.getMessage());
    }

    String tableSql = "select distinct(c_table_name) from " + metadataSchema
            + "table_access where c_table_cd = ? ";
    ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
        public String mapRow(ResultSet rs, int rowNum) throws SQLException {
            String name = (rs.getString("c_table_name"));
            return name;
        }
    };

    String tableName = jt.queryForObject(tableSql, map, tableCd);
    String path = StringUtil.getPath(childrenType.getParent());
    String searchPath = path + "%";

    // Lookup to get chlevel + 1 ---  dont allow synonyms so we only get one result back

    String levelSql = "select c_hlevel from " + metadataSchema + tableName
            + " where c_fullname like ?  and c_synonym_cd = 'N'";

    int level = 0;
    try {
        level = jt.queryForInt(levelSql, path);
    } catch (DataAccessException e1) {
        // should only get 1 result back  (path == c_fullname which should be unique)
        log.error(e1.getMessage());
        throw e1;
    }

    String hidden = "";
    if (childrenType.isHiddens() == false)
        hidden = " and c_visualattributes not like '_H%'";

    String synonym = "";
    if (childrenType.isSynonyms() == false)
        synonym = " and c_synonym_cd = 'N'";

    String sql = "select " + parameters + " from " + metadataSchema + tableName
            + " where c_fullname like ? and c_hlevel = ? ";
    sql = sql + hidden + synonym + " order by c_name ";

    //   log.info(sql + path + level);
    final boolean obfuscatedUserFlag = Roles.getInstance().isRoleOfuscated(projectInfo);

    ParameterizedRowMapper<ConceptType> mapper = new ParameterizedRowMapper<ConceptType>() {
        public ConceptType mapRow(ResultSet rs, int rowNum) throws SQLException {
            ConceptType child = new ConceptType();
            child.setName(rs.getString("c_name"));
            child.setBasecode(rs.getString("c_basecode"));
            child.setLevel(rs.getInt("c_hlevel"));
            child.setKey(rs.getString("c_fullname"));
            child.setSynonymCd(rs.getString("c_synonym_cd"));
            child.setVisualattributes(rs.getString("c_visualattributes"));
            Integer totalNum = rs.getInt("c_totalnum");
            if (obfuscatedUserFlag == false) {
                child.setTotalnum(totalNum);
            }
            child.setFacttablecolumn(rs.getString("c_facttablecolumn"));
            child.setTablename(rs.getString("c_tablename"));
            child.setColumnname(rs.getString("c_columnname"));
            child.setColumndatatype(rs.getString("c_columndatatype"));
            child.setOperator(rs.getString("c_operator"));
            child.setDimcode(rs.getString("c_dimcode"));
            child.setTooltip(rs.getString("c_tooltip"));
            if (childrenType.isBlob() == true) {
                try {
                    if (rs.getClob("c_comment") == null)
                        child.setComment(null);
                    else
                        child.setComment(JDBCUtil.getClobString(rs.getClob("c_comment")));
                } catch (IOException e) {
                    log.error(e.getMessage());
                    child.setComment(null);
                }

                if (rs.getClob("c_metadataxml") == null) {
                    child.setMetadataxml(null);
                } else {
                    String c_xml = null;
                    try {
                        c_xml = JDBCUtil.getClobString(rs.getClob("c_metadataxml"));
                    } catch (IOException e) {
                        log.error(e.getMessage());
                        child.setMetadataxml(null);
                    }
                    if ((c_xml != null) && (c_xml.trim().length() > 0) && (!c_xml.equals("(null)"))) {
                        SAXBuilder parser = new SAXBuilder();
                        java.io.StringReader xmlStringReader = new java.io.StringReader(c_xml);
                        Element rootElement = null;
                        try {
                            org.jdom.Document metadataDoc = parser.build(xmlStringReader);
                            org.jdom.output.DOMOutputter out = new DOMOutputter();
                            Document doc = out.output(metadataDoc);
                            rootElement = doc.getDocumentElement();
                        } catch (JDOMException e) {
                            log.error(e.getMessage());
                            child.setMetadataxml(null);
                        } catch (IOException e1) {
                            log.error(e1.getMessage());
                            child.setMetadataxml(null);
                        }
                        if (rootElement != null) {
                            XmlValueType xml = new XmlValueType();
                            xml.getAny().add(rootElement);
                            child.setMetadataxml(xml);
                        }
                    } else {
                        child.setMetadataxml(null);
                    }
                }

            }
            if ((childrenType.getType().equals("all"))) {
                DTOFactory factory = new DTOFactory();
                // make sure date isnt null before converting to XMLGregorianCalendar
                Date date = rs.getDate("update_date");
                if (date == null)
                    child.setUpdateDate(null);
                else
                    child.setUpdateDate(factory.getXMLGregorianCalendar(date.getTime()));

                date = rs.getDate("download_date");
                if (date == null)
                    child.setDownloadDate(null);
                else
                    child.setDownloadDate(factory.getXMLGregorianCalendar(date.getTime()));

                date = rs.getDate("import_date");
                if (date == null)
                    child.setImportDate(null);
                else
                    child.setImportDate(factory.getXMLGregorianCalendar(date.getTime()));

                child.setSourcesystemCd(rs.getString("sourcesystem_cd"));
                child.setValuetypeCd(rs.getString("valuetype_cd"));
            }
            return child;
        }
    };

    //log.info(sql + " " + path + " " + level);

    List queryResult = null;
    try {
        queryResult = jt.query(sql, mapper, searchPath, (level + 1));
    } catch (DataAccessException e) {
        log.error(e.getMessage());
        throw e;
    }
    log.debug("result size = " + queryResult.size());

    //      Fix the key so it equals "\\tableCd\fullname"
    if (queryResult != null) {
        Iterator it = queryResult.iterator();
        while (it.hasNext()) {
            ConceptType child = (ConceptType) it.next();
            child.setKey("\\\\" + tableCd + child.getKey());
        }
    }

    return queryResult;
    // tested statement with aqua data studio   verified output from above against this. 
    // select  c_fullname, c_name, c_synonym_cd, c_visualattributes  from metadata.testrpdr 
    // where c_fullname like '\RPDR\Diagnoses\Circulatory system (390-459)\Arterial vascular disease (440-447)\(446) Polyarteritis nodosa and al%' 
    // and c_hlevel = 5  and c_visualattributes not like '_H%' and c_synonym_cd = 'N'

    // verified both with and without hiddens and synonyms.

    // clob test   level = 4
    //   <parent>\\testrpdr\RPDR\HealthHistory\PHY\Health Maintenance\Mammogram\Mammogram - Deferred</parent> 
}

From source file:org.wso2.carbon.ml.database.internal.MLDatabaseService.java

/**
 * Retrieve a dataset from ID/* w ww . jav a  2s .co  m*/
 */
@Override
public MLDataset getDataset(int tenantId, String userName, long datasetId) throws DatabaseHandlerException {
    Connection connection = null;
    ResultSet result = null;
    PreparedStatement statement = null;
    try {
        connection = dbh.getDataSource().getConnection();
        statement = connection.prepareStatement(SQLQueries.GET_DATASET_USING_ID);
        statement.setInt(1, tenantId);
        statement.setString(2, userName);
        statement.setLong(3, datasetId);
        result = statement.executeQuery();
        if (result.first()) {
            MLDataset dataset = new MLDataset();
            dataset.setId(result.getLong(1));
            dataset.setName(result.getString(2));
            dataset.setComments(MLDatabaseUtils.toString(result.getClob(3)));
            dataset.setDataSourceType(result.getString(4));
            dataset.setDataTargetType(result.getString(5));
            dataset.setDataType(result.getString(6));
            dataset.setTenantId(tenantId);
            dataset.setUserName(userName);
            if (dataset.getId() != 0) {
                List<MLDatasetVersion> datasetVersions = getAllVersionsetsOfDataset(tenantId, userName,
                        dataset.getId());
                if (datasetVersions.size() > 0) {
                    String datasetStatus = MLDBUtil.getDatasetStatus(datasetVersions);
                    dataset.setStatus(datasetStatus);
                }
            }
            return dataset;
        } else {
            return null;
        }
    } catch (SQLException e) {
        throw new DatabaseHandlerException(
                "An error has occurred while extracting a dataset with [id] " + datasetId, e);
    } finally {
        // Close the database resources.
        MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
    }
}

From source file:org.wso2.carbon.ml.database.internal.MLDatabaseService.java

/**
 * Retrieve all datasets/*from www .  j a v a  2s.co  m*/
 */
@Override
public List<MLDataset> getAllDatasets(int tenantId, String userName) throws DatabaseHandlerException {
    Connection connection = null;
    ResultSet result = null;
    PreparedStatement statement = null;
    List<MLDataset> datasets = new ArrayList<MLDataset>();
    try {
        connection = dbh.getDataSource().getConnection();
        statement = connection.prepareStatement(SQLQueries.GET_ALL_DATASETS);
        statement.setInt(1, tenantId);
        statement.setString(2, userName);
        result = statement.executeQuery();
        while (result.next()) {
            MLDataset dataset = new MLDataset();
            dataset.setId(result.getLong(1));
            dataset.setName(result.getString(2));
            dataset.setComments(MLDatabaseUtils.toString(result.getClob(3)));
            dataset.setDataSourceType(result.getString(4));
            dataset.setDataTargetType(result.getString(5));
            dataset.setDataType(result.getString(6));
            dataset.setTenantId(tenantId);
            dataset.setUserName(userName);
            if (dataset.getId() != 0) {
                List<MLDatasetVersion> datasetVersions = getAllVersionsetsOfDataset(tenantId, userName,
                        dataset.getId());
                if (datasetVersions.size() > 0) {
                    String datasetStatus = MLDBUtil.getDatasetStatus(datasetVersions);
                    dataset.setStatus(datasetStatus);
                }
            }
            datasets.add(dataset);
        }
        return datasets;
    } catch (SQLException e) {
        throw new DatabaseHandlerException(" An error has occurred while extracting datasets.", e);
    } finally {
        // Close the database resources.
        MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
    }
}

From source file:org.wso2.carbon.ml.database.internal.MLDatabaseService.java

@Override
public List<MLAnalysis> getAllAnalyses(int tenantId, String userName) throws DatabaseHandlerException {

    Connection connection = null;
    ResultSet result = null;
    PreparedStatement statement = null;
    List<MLAnalysis> analyses = new ArrayList<MLAnalysis>();
    try {/*from  ww  w  .j  a  va2 s  . c om*/
        connection = dbh.getDataSource().getConnection();
        statement = connection.prepareStatement(SQLQueries.GET_ALL_ANALYSES);
        statement.setInt(1, tenantId);
        statement.setString(2, userName);
        result = statement.executeQuery();
        while (result.next()) {
            MLAnalysis analysis = new MLAnalysis();
            analysis.setId(result.getLong(1));
            analysis.setProjectId(result.getLong(2));
            analysis.setComments(MLDatabaseUtils.toString(result.getClob(3)));
            analysis.setName(result.getString(4));
            analysis.setTenantId(tenantId);
            analysis.setUserName(userName);
            analyses.add(analysis);
        }
        return analyses;
    } catch (SQLException e) {
        throw new DatabaseHandlerException(" An error has occurred while extracting analyses.", e);
    } finally {
        // Close the database resources.
        MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
    }
}

From source file:org.wso2.carbon.ml.database.internal.MLDatabaseService.java

@Override
public MLAnalysis getAnalysis(int tenantId, String userName, long analysisId) throws DatabaseHandlerException {
    Connection connection = null;
    ResultSet result = null;
    PreparedStatement statement = null;
    try {//from  w  w  w.j  a va2 s. c o m
        connection = dbh.getDataSource().getConnection();
        statement = connection.prepareStatement(SQLQueries.GET_ANALYSIS_BY_ID);
        statement.setLong(1, analysisId);
        statement.setInt(2, tenantId);
        statement.setString(3, userName);
        result = statement.executeQuery();
        if (result.first()) {
            MLAnalysis analysis = new MLAnalysis();
            analysis.setId(analysisId);
            analysis.setName(result.getString(1));
            analysis.setProjectId(result.getLong(2));
            analysis.setComments(MLDatabaseUtils.toString(result.getClob(3)));
            analysis.setTenantId(tenantId);
            analysis.setUserName(userName);
            return analysis;
        } else {
            return null;
        }
    } catch (SQLException e) {
        throw new DatabaseHandlerException(
                " An error has occurred while retrieving analysis with Id: " + analysisId, e);
    } finally {
        // Close the database resources.
        MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
    }
}

From source file:org.wso2.carbon.ml.database.internal.MLDatabaseService.java

@Override
public MLAnalysis getAnalysisOfProject(int tenantId, String userName, long projectId, String analysisName)
        throws DatabaseHandlerException {
    Connection connection = null;
    ResultSet result = null;
    PreparedStatement statement = null;
    try {//w  w  w.  ja  va 2  s .  c o m
        connection = dbh.getDataSource().getConnection();
        statement = connection.prepareStatement(SQLQueries.GET_ANALYSIS_OF_PROJECT);
        statement.setInt(1, tenantId);
        statement.setString(2, userName);
        statement.setLong(3, projectId);
        statement.setString(4, analysisName);
        result = statement.executeQuery();
        if (result.first()) {
            MLAnalysis analysis = new MLAnalysis();
            analysis.setId(result.getLong(1));
            analysis.setProjectId(result.getLong(2));
            analysis.setComments(MLDatabaseUtils.toString(result.getClob(3)));
            analysis.setName(result.getString(4));
            analysis.setTenantId(tenantId);
            analysis.setUserName(userName);
            return analysis;
        } else {
            return null;
        }
    } catch (SQLException e) {
        throw new DatabaseHandlerException(" An error has occurred while extracting analysis for project id: "
                + projectId + " and analysis name: " + analysisName, e);
    } finally {
        // Close the database resources.
        MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
    }
}

From source file:org.wso2.carbon.ml.database.internal.MLDatabaseService.java

@Override
public List<MLAnalysis> getAllAnalysesOfProject(int tenantId, String userName, long projectId)
        throws DatabaseHandlerException {

    Connection connection = null;
    ResultSet result = null;
    PreparedStatement statement = null;
    List<MLAnalysis> analyses = new ArrayList<MLAnalysis>();
    try {/* w w  w .  j a  v  a2s .  com*/
        connection = dbh.getDataSource().getConnection();
        statement = connection.prepareStatement(SQLQueries.GET_ALL_ANALYSES_OF_PROJECT);
        statement.setInt(1, tenantId);
        statement.setString(2, userName);
        statement.setLong(3, projectId);
        result = statement.executeQuery();
        while (result.next()) {
            MLAnalysis analysis = new MLAnalysis();
            analysis.setId(result.getLong(1));
            analysis.setProjectId(result.getLong(2));
            analysis.setComments(MLDatabaseUtils.toString(result.getClob(3)));
            analysis.setName(result.getString(4));
            analysis.setTenantId(tenantId);
            analysis.setUserName(userName);
            analyses.add(analysis);
        }
        return analyses;
    } catch (SQLException e) {
        throw new DatabaseHandlerException(
                " An error has occurred while extracting analyses for project id: " + projectId, e);
    } finally {
        // Close the database resources.
        MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
    }
}

From source file:org.executequery.gui.resultset.ResultSetTableModel.java

public void createTable(ResultSet resultSet) {

    if (!isOpenAndValid(resultSet)) {

        clearData();//  w w  w  .j ava 2 s. c  o m
        return;
    }

    try {

        resetMetaData();
        ResultSetMetaData rsmd = resultSet.getMetaData();

        columnHeaders.clear();
        visibleColumnHeaders.clear();
        tableData.clear();

        int zeroBaseIndex = 0;
        int count = rsmd.getColumnCount();
        for (int i = 1; i <= count; i++) {

            zeroBaseIndex = i - 1;

            columnHeaders.add(new ResultSetColumnHeader(zeroBaseIndex, rsmd.getColumnLabel(i),
                    rsmd.getColumnName(i), rsmd.getColumnType(i), rsmd.getColumnTypeName(i)));
        }

        int recordCount = 0;
        interrupted = false;

        if (holdMetaData) {

            setMetaDataVectors(rsmd);
        }

        List<RecordDataItem> rowData;
        long time = System.currentTimeMillis();
        while (resultSet.next()) {

            if (interrupted || Thread.interrupted()) {

                throw new InterruptedException();
            }

            recordCount++;
            rowData = new ArrayList<RecordDataItem>(count);

            for (int i = 1; i <= count; i++) {

                zeroBaseIndex = i - 1;

                ResultSetColumnHeader header = columnHeaders.get(zeroBaseIndex);
                RecordDataItem value = recordDataItemFactory.create(header);

                try {

                    int dataType = header.getDataType();
                    switch (dataType) {

                    // some drivers (informix for example)
                    // was noticed to return the hashcode from
                    // getObject for -1 data types (eg. longvarchar).
                    // force string for these - others stick with
                    // getObject() for default value formatting

                    case Types.CHAR:
                    case Types.VARCHAR:
                        value.setValue(resultSet.getString(i));
                        break;
                    case Types.DATE:
                        value.setValue(resultSet.getDate(i));
                        break;
                    case Types.TIME:
                        value.setValue(resultSet.getTime(i));
                        break;
                    case Types.TIMESTAMP:
                        value.setValue(resultSet.getTimestamp(i));
                        break;
                    case Types.LONGVARCHAR:
                    case Types.CLOB:
                        value.setValue(resultSet.getClob(i));
                        break;
                    case Types.LONGVARBINARY:
                    case Types.VARBINARY:
                    case Types.BINARY:
                        value.setValue(resultSet.getBytes(i));
                        break;
                    case Types.BLOB:
                        value.setValue(resultSet.getBlob(i));
                        break;
                    case Types.BIT:
                    case Types.TINYINT:
                    case Types.SMALLINT:
                    case Types.INTEGER:
                    case Types.BIGINT:
                    case Types.FLOAT:
                    case Types.REAL:
                    case Types.DOUBLE:
                    case Types.NUMERIC:
                    case Types.DECIMAL:
                    case Types.NULL:
                    case Types.OTHER:
                    case Types.JAVA_OBJECT:
                    case Types.DISTINCT:
                    case Types.STRUCT:
                    case Types.ARRAY:
                    case Types.REF:
                    case Types.DATALINK:
                    case Types.BOOLEAN:
                    case Types.ROWID:
                    case Types.NCHAR:
                    case Types.NVARCHAR:
                    case Types.LONGNVARCHAR:
                    case Types.NCLOB:
                    case Types.SQLXML:

                        // use getObject for all other known types

                        value.setValue(resultSet.getObject(i));
                        break;

                    default:

                        // otherwise try as string

                        asStringOrObject(value, resultSet, i);
                        break;
                    }

                } catch (Exception e) {

                    try {

                        // ... and on dump, resort to string
                        value.setValue(resultSet.getString(i));

                    } catch (SQLException sqlException) {

                        // catch-all SQLException - yes, this is hideous

                        // noticed with invalid date formatted values in mysql

                        value.setValue("<Error - " + sqlException.getMessage() + ">");
                    }
                }

                if (resultSet.wasNull()) {

                    value.setNull();
                }

                rowData.add(value);
            }

            tableData.add(rowData);

            if (recordCount == maxRecords) {

                break;
            }

        }

        if (Log.isTraceEnabled()) {

            Log.trace("Finished populating table model - " + recordCount + " rows - [ "
                    + MiscUtils.formatDuration(System.currentTimeMillis() - time) + "]");
        }

        fireTableStructureChanged();

    } catch (SQLException e) {

        System.err.println("SQL error populating table model at: " + e.getMessage());
        Log.debug("Table model error - " + e.getMessage(), e);

    } catch (Exception e) {

        if (e instanceof InterruptedException) {

            Log.debug("ResultSet generation interrupted.", e);

        } else {

            String message = e.getMessage();
            if (StringUtils.isBlank(message)) {

                System.err.println("Exception populating table model.");

            } else {

                System.err.println("Exception populating table model at: " + message);
            }

            Log.debug("Table model error - ", e);
        }

    } finally {

        if (resultSet != null) {

            try {

                resultSet.close();

                Statement statement = resultSet.getStatement();
                if (statement != null) {

                    statement.close();
                }

            } catch (SQLException e) {
            }

        }
    }

}

From source file:edu.harvard.i2b2.ontology.dao.GetNameInfoDao.java

public List findNameInfo(final VocabRequestType vocabType, List categories, ProjectType projectInfo,
        final String dbType) throws DataAccessException {
    DataSource ds = null;/*from  w  ww . j a va  2  s.co m*/
    try {
        ds = OntologyUtil.getInstance().getDataSource("java:OntologyLocalDS");
    } catch (I2B2Exception e2) {
        log.error(e2.getMessage());
    }
    SimpleJdbcTemplate jt = new SimpleJdbcTemplate(ds);

    // find return parameters
    String parameters = DEFAULT;
    if (vocabType.getType().equals("core")) {
        parameters = CORE;
    } else if (vocabType.getType().equals("all")) {
        parameters = ALL;
    }
    if (vocabType.isBlob() == true)
        parameters = parameters + BLOB;

    //extract table code
    String tableCd = vocabType.getCategory();

    // table code to table name conversion
    // Get metadata schema name from properties file.
    String metadataSchema = "";
    try {
        metadataSchema = OntologyUtil.getInstance().getMetaDataSchemaName();
    } catch (I2B2Exception e1) {
        log.error(e1.getMessage());
    }

    // table code to table name conversion
    String tableSql = "select distinct(c_table_name) from " + metadataSchema
            + "table_access where c_table_cd = ? ";
    ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
        public String mapRow(ResultSet rs, int rowNum) throws SQLException {
            String name = (rs.getString("c_table_name"));
            return name;
        }
    };

    String table = jt.queryForObject(tableSql, map, tableCd);

    String nameInfoSql = null;
    String compareName = null;

    if (vocabType.getMatchStr().getStrategy().equals("exact")) {
        nameInfoSql = "select " + parameters + " from " + metadataSchema + table + " where upper(c_name) = ?  ";
        compareName = vocabType.getMatchStr().getValue().toUpperCase();
    }

    else if (vocabType.getMatchStr().getStrategy().equals("left")) {
        nameInfoSql = "select " + parameters + " from " + metadataSchema + table
                + " where upper(c_name) like ?  ";
        compareName = vocabType.getMatchStr().getValue().toUpperCase() + "%";
    }

    else if (vocabType.getMatchStr().getStrategy().equals("right")) {
        nameInfoSql = "select " + parameters + " from " + metadataSchema + table
                + " where upper(c_name) like ?  ";
        compareName = "%" + vocabType.getMatchStr().getValue().toUpperCase();
    }

    else if (vocabType.getMatchStr().getStrategy().equals("contains")) {
        nameInfoSql = "select " + parameters + " from " + metadataSchema + table
                + " where upper(c_name) like ?  ";
        compareName = "%" + vocabType.getMatchStr().getValue().toUpperCase() + "%";
    }

    String hidden = "";
    if (vocabType.isHiddens() == false)
        hidden = " and c_visualattributes not like '_H%'";

    String synonym = "";
    if (vocabType.isSynonyms() == false)
        synonym = " and c_synonym_cd = 'N'";

    nameInfoSql = nameInfoSql + hidden + synonym + " order by c_name ";
    final boolean obfuscatedUserFlag = Roles.getInstance().isRoleOfuscated(projectInfo);

    ParameterizedRowMapper<ConceptType> mapper = new ParameterizedRowMapper<ConceptType>() {
        public ConceptType mapRow(ResultSet rs, int rowNum) throws SQLException {
            ConceptType entry = new ConceptType();
            entry.setName(rs.getString("c_name"));
            if (!(vocabType.getType().equals("default"))) {
                entry.setKey(rs.getString("c_fullname"));
                entry.setBasecode(rs.getString("c_basecode"));
                entry.setLevel(rs.getInt("c_hlevel"));
                entry.setSynonymCd(rs.getString("c_synonym_cd"));
                entry.setVisualattributes(rs.getString("c_visualattributes"));
                Integer totalNum = rs.getInt("c_totalnum");
                if (obfuscatedUserFlag == false) {
                    entry.setTotalnum(totalNum);
                }
                entry.setFacttablecolumn(rs.getString("c_facttablecolumn"));
                entry.setTablename(rs.getString("c_tablename"));
                entry.setColumnname(rs.getString("c_columnname"));
                entry.setColumndatatype(rs.getString("c_columndatatype"));
                entry.setOperator(rs.getString("c_operator"));
                entry.setDimcode(rs.getString("c_dimcode"));
                entry.setTooltip(rs.getString("c_tooltip"));
            }
            if (vocabType.isBlob() == true) {
                if (rs.getClob("c_comment") == null)
                    entry.setComment(null);
                else {
                    try {
                        if (dbType.equals("POSTGRESQL"))
                            entry.setComment(rs.getString("c_comment"));
                        else
                            entry.setComment(JDBCUtil.getClobString(rs.getClob("c_comment")));
                    } catch (IOException e1) {
                        log.error(e1.getMessage());
                        entry.setComment(null);
                    }
                }
                if (rs.getClob("c_metadataxml") == null) {
                    entry.setMetadataxml(null);
                } else {
                    String c_xml = null;
                    try {
                        if (dbType.equals("POSTGRESQL"))
                            c_xml = rs.getString("c_comment");
                        else
                            c_xml = JDBCUtil.getClobString(rs.getClob("c_metadataxml"));
                    } catch (IOException e1) {
                        log.error(e1.getMessage());
                        entry.setMetadataxml(null);
                    }
                    if ((c_xml != null) && (c_xml.trim().length() > 0) && (!c_xml.equals("(null)"))) {
                        SAXBuilder parser = new SAXBuilder();
                        java.io.StringReader xmlStringReader = new java.io.StringReader(c_xml);
                        Element rootElement = null;
                        try {
                            org.jdom.Document metadataDoc = parser.build(xmlStringReader);
                            org.jdom.output.DOMOutputter out = new DOMOutputter();
                            Document doc = out.output(metadataDoc);
                            rootElement = doc.getDocumentElement();
                        } catch (JDOMException e) {
                            log.error(e.getMessage());
                            entry.setMetadataxml(null);
                        } catch (IOException e1) {
                            log.error(e1.getMessage());
                            entry.setMetadataxml(null);
                        }
                        if (rootElement != null) {
                            XmlValueType xml = new XmlValueType();
                            xml.getAny().add(rootElement);
                            entry.setMetadataxml(xml);
                        }
                    } else {
                        entry.setMetadataxml(null);
                    }
                }
            }
            if ((vocabType.getType().equals("all"))) {
                DTOFactory factory = new DTOFactory();
                // make sure date isnt null before converting to XMLGregorianCalendar
                Date date = rs.getDate("update_date");
                if (date == null)
                    entry.setUpdateDate(null);
                else
                    entry.setUpdateDate(factory.getXMLGregorianCalendar(date.getTime()));

                date = rs.getDate("download_date");
                if (date == null)
                    entry.setDownloadDate(null);
                else
                    entry.setDownloadDate(factory.getXMLGregorianCalendar(date.getTime()));

                date = rs.getDate("import_date");
                if (date == null)
                    entry.setImportDate(null);
                else
                    entry.setImportDate(factory.getXMLGregorianCalendar(date.getTime()));

                entry.setSourcesystemCd(rs.getString("sourcesystem_cd"));
                entry.setValuetypeCd(rs.getString("valuetype_cd"));
            }
            return entry;
        }
    };

    List queryResult = null;
    try {
        queryResult = jt.query(nameInfoSql, mapper, compareName);
    } catch (DataAccessException e) {
        log.error(e.getMessage());
        throw e;
    }
    log.debug("result size = " + queryResult.size());

    //      Fix the key so it equals "\\tableCd\fullname"
    if (queryResult != null) {
        Iterator itr = queryResult.iterator();
        while (itr.hasNext()) {
            ConceptType entry = (ConceptType) itr.next();
            entry.setKey("\\\\" + tableCd + entry.getKey());
        }
    }
    return queryResult;
}