private DataSource getDataSource(String dataSourceName) {
   DataSource ds = null;
   try {
     ds = OntologyUtil.getInstance().getDataSource(dataSourceName);
   } catch (I2B2Exception e2) {
     log.error(e2.getMessage());
     ;
   }
   return ds;
 }
  public List findCodeInfo(
      final VocabRequestType vocabType, final List categories, ProjectType projectInfo)
      throws DataAccessException, I2B2Exception {
    DataSource ds = null;
    try {
      ds = OntologyUtil.getInstance().getDataSource("java:OntologyLocalDS");
    } catch (I2B2Exception e2) {
      log.error(e2.getMessage());
      throw e2;
    }

    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();
    //		log.info(tableCd);

    // 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());
      throw e1;
    }

    List tableNames = null;
    if (tableCd != null) {
      // 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;
            }
          };
      tableNames = jt.query(tableSql, map, tableCd);
    } else { // tableCd is null, so query all tables user has access to
      String whereClause = "where ";
      Iterator it = categories.iterator();
      while (it.hasNext()) {
        ConceptType entry = null;
        if (whereClause.equals("where ")) {
          entry = (ConceptType) it.next();
          whereClause =
              whereClause + " c_table_cd = '" + StringUtil.getTableCd(entry.getKey()) + "' ";
        } else {
          entry = (ConceptType) it.next();
          whereClause =
              whereClause
                  + " or "
                  + " c_table_cd = '"
                  + StringUtil.getTableCd(entry.getKey())
                  + "' ";
        }
      }
      String tableSql =
          "select distinct(c_table_name) from " + metadataSchema + "table_access " + whereClause;

      ParameterizedRowMapper<String> map =
          new ParameterizedRowMapper<String>() {
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
              String name = (rs.getString("c_table_name"));
              return name;
            }
          };

      try {
        tableNames = jt.query(tableSql, map);
      } catch (DataAccessException e) {
        log.error(e.getMessage());
        throw e;
      }
    }

    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'";

    String codeInfoSql = null;
    if (tableNames != null) {
      Iterator it = tableNames.iterator();
      String table = (String) it.next();
      String tableCdSql =
          ", (select distinct(c_table_cd) from "
              + metadataSchema
              + "TABLE_ACCESS where c_table_name = '"
              + table
              + "') as tableCd";
      String basecode = " '" + vocabType.getMatchStr().getValue() + "' ";
      codeInfoSql =
          "select "
              + parameters
              + tableCdSql
              + " from "
              + metadataSchema
              + table
              + " where upper(c_basecode) =  "
              + basecode.toUpperCase()
              + hidden
              + synonym;
      ;
      while (it.hasNext()) {
        table = (String) it.next();
        tableCdSql =
            ", (select distinct(c_table_cd) from "
                + metadataSchema
                + "TABLE_ACCESS where c_table_name = '"
                + table
                + "') as tableCd";
        codeInfoSql =
            codeInfoSql
                + " union select "
                + parameters
                + tableCdSql
                + " from "
                + metadataSchema
                + table
                + " where upper(c_basecode) =  "
                + basecode.toUpperCase()
                + hidden
                + synonym;
      }
      codeInfoSql = codeInfoSql + " order by c_name ";
    } else return null;

    log.debug(codeInfoSql);
    final boolean obfuscatedUserFlag = Roles.getInstance().isRoleOfuscated(projectInfo);

    ParameterizedRowMapper<ExpandedConceptType> mapper =
        new ParameterizedRowMapper<ExpandedConceptType>() {
          public ExpandedConceptType mapRow(ResultSet rs, int rowNum) throws SQLException {
            ExpandedConceptType entry = new ExpandedConceptType();
            // TODO fix this for all/+blob

            entry.setName(rs.getString("c_name"));
            entry.setTableCd(rs.getString("tablecd"));
            if (vocabType.getType().equals("core")) {
              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.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 {
      if (tableNames != null) queryResult = jt.query(codeInfoSql, mapper);
    } catch (DataAccessException e) {
      log.error(e.getMessage());
      throw e;
    }
    log.debug("result size = " + queryResult.size());

    if (queryResult != null) {
      if (parameters != DEFAULT) {
        //				 fix the key so it equals "\\tableCd\fullname"
        //  in all cases but default
        Iterator it = queryResult.iterator();
        while (it.hasNext()) {
          ExpandedConceptType entry = (ExpandedConceptType) it.next();
          entry.setKey("\\\\" + entry.getTableCd() + entry.getKey());
        }
      }

      // Cant gather clobs when you perform unions....
      //  So you have to loop through all the results and gather clobs
      if (vocabType.isBlob() == true) {
        Iterator itr = queryResult.iterator();
        while (itr.hasNext()) {
          ConceptType child = (ConceptType) itr.next();
          Iterator it = tableNames.iterator();
          while (it.hasNext()) {
            String clobSql =
                "select c_metadataxml, c_comment from "
                    + metadataSchema
                    + (String) it.next()
                    + " where c_name = ? and "
                    + synonym;
            ParameterizedRowMapper<ConceptType> map =
                new ParameterizedRowMapper<ConceptType>() {
                  public ConceptType mapRow(ResultSet rs, int rowNum) throws SQLException {
                    ConceptType concept = new ConceptType();
                    try {
                      if (rs.getClob("c_metadataxml") == null) {
                        concept.setMetadataxml(null);
                      } else {
                        String c_xml = JDBCUtil.getClobString(rs.getClob("c_metadataxml"));
                        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());
                            concept.setMetadataxml(null);
                          }
                          if (rootElement != null) {
                            XmlValueType xml = new XmlValueType();
                            xml.getAny().add(rootElement);
                            concept.setMetadataxml(xml);
                          }
                        } else {
                          concept.setMetadataxml(null);
                        }
                      }
                    } catch (IOException e) {
                      log.error(e.getMessage());
                      concept.setMetadataxml(null);
                    }
                    try {
                      if (rs.getClob("c_comment") == null) {
                        concept.setComment(null);
                      } else {
                        concept.setComment(JDBCUtil.getClobString(rs.getClob("c_comment")));
                      }

                    } catch (IOException e) {
                      log.error(e.getMessage());
                      concept.setComment(null);
                    }
                    return concept;
                  }
                };
            List clobResult = null;
            try {
              clobResult = jt.query(clobSql, map, child.getName(), child.getTooltip());
            } catch (DataAccessException e) {
              log.error(e.getMessage());
              throw e;
            }
            if (clobResult != null) {
              if ((((ConceptType) (clobResult.get(0))).getMetadataxml() != null)
                  || (((ConceptType) (clobResult.get(0))).getComment() != null)) {

                child.setMetadataxml(((ConceptType) (clobResult.get(0))).getMetadataxml());
                child.setComment(((ConceptType) (clobResult.get(0))).getComment());
                break;
              }
            } else {
              child.setMetadataxml(null);
              child.setComment(null);
            }
          }
        }
      }
    }
    return queryResult;
  }