edu.harvard.i2b2.ontology.dao.GetNameInfoDao.java Source code

Java tutorial

Introduction

Here is the source code for edu.harvard.i2b2.ontology.dao.GetNameInfoDao.java

Source

/*
 * Copyright (c) 2006-2007 Massachusetts General Hospital 
 * All rights reserved. This program and the accompanying materials 
 * are made available under the terms of the i2b2 Software License v1.0 
 * which accompanies this distribution. 
 * 
 * Contributors:
 *       Lori Phillips
 */
package edu.harvard.i2b2.ontology.dao;

import java.io.IOException;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;
import org.jdom.output.DOMOutputter;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

import edu.harvard.i2b2.common.exception.I2B2Exception;
import edu.harvard.i2b2.common.util.db.JDBCUtil;
import edu.harvard.i2b2.common.util.jaxb.DTOFactory;
import edu.harvard.i2b2.ontology.datavo.pm.ProjectType;
import edu.harvard.i2b2.ontology.datavo.vdo.ConceptType;
import edu.harvard.i2b2.ontology.datavo.vdo.VocabRequestType;
import edu.harvard.i2b2.ontology.datavo.vdo.XmlValueType;
import edu.harvard.i2b2.ontology.util.OntologyUtil;
import edu.harvard.i2b2.ontology.util.Roles;

public class GetNameInfoDao extends JdbcDaoSupport {

    private static Log log = LogFactory.getLog(GetCodeInfoDao.class);
    final static String CORE = "c_hlevel, c_fullname, c_name, c_synonym_cd, c_visualattributes, c_totalnum, c_basecode, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype, c_operator, c_dimcode, c_tooltip";
    final static String ALL = CORE + ", update_date, download_date, import_date, sourcesystem_cd, valuetype_cd";
    final static String DEFAULT = " c_name ";
    final static String BLOB = ", c_metadataxml, c_comment ";

    public List findNameInfo(final VocabRequestType vocabType, List categories, ProjectType projectInfo,
            final String dbType) throws DataAccessException {
        DataSource ds = null;
        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;
    }

}