List of usage examples for org.springframework.dao DataAccessException getMessage
@Override
@Nullable
public String getMessage()
From source file:edu.harvard.i2b2.im.dao.PdoDao.java
public int addAudit(String projectInfo, PidSet pids, String userId, String comments, DBInfoType dbInfo) throws I2B2DAOException, I2B2Exception { String metadataSchema = dbInfo.getDb_fullSchema(); setDataSource(dbInfo.getDb_dataSource()); ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() { public String mapRow(ResultSet rs, int rowNum) throws SQLException { String name = (rs.getString("c_table_name")); return name; }/* w ww . j a v a 2 s . c o m*/ }; int numRowsAdded = -1; try { String xml = null; if (comments != null) { String addSql = "insert into " + metadataSchema + "IM_AUDIT (lcl_site, lcl_id, user_id, project_id, comments) values (?,?,?,?,?)"; for (PidType pidType : pids.getPid()) { numRowsAdded += jt.update(addSql, pidType.getPatientId().getSource(), pidType.getPatientId().getValue(), userId, projectInfo, comments); for (PatientMapId pidPatient : pidType.getPatientMapId()) { numRowsAdded += jt.update(addSql, pidPatient.getSource(), pidPatient.getValue(), userId, projectInfo, comments); } } } else { String addSql = "insert into " + metadataSchema + "IM_AUDIT (lcl_site, lcl_id, user_id, project_id) values (?,?,?,?)"; for (PidType pidType : pids.getPid()) { // numRowsAdded += jt.update(addSql, // pidType.getPatientId().getSource(), pidType.getPatientId().getValue(), // userId, projectInfo); for (PatientMapId pidPatient : pidType.getPatientMapId()) { numRowsAdded += jt.update(addSql, pidPatient.getSource(), pidPatient.getValue(), userId, projectInfo); } } } } catch (DataAccessException e) { log.error("Dao addChild failed"); log.error(e.getMessage()); throw new I2B2DAOException("Data access error ", e); } // log.info(addSql + " " + numRowsAdded); log.debug("Number of rows added: " + numRowsAdded); return numRowsAdded; }
From source file:edu.harvard.i2b2.ontology.dao.ConceptDao.java
public List findChildrenByParent(final GetChildrenDataMessage childrenMsg, ProjectType projectInfo, DBInfoType dbInfo) throws I2B2DAOException, I2B2Exception, JAXBUtilException { final GetChildrenType childrenType = childrenMsg.getChildrenType(); // find return parameters String parameters = DEFAULT;/* w w w . ja v a 2 s . c o m*/ if (childrenType.getType().equals("limited")) { parameters = LIMITED; } else if (childrenType.getType().equals("core")) { parameters = CORE; } else if (childrenType.getType().equals("all")) { parameters = CORE + ALL; } if (childrenType.isBlob() == true) parameters = parameters + BLOB; String metadataSchema = dbInfo.getDb_fullSchema(); setDataSource(dbInfo.getDb_dataSource()); if (projectInfo.getRole().size() == 0) { log.error("no role found for this user in project: " + projectInfo.getName()); I2B2Exception e = new I2B2Exception("No role found for user"); throw e; } Boolean protectedAccess = false; Iterator it = projectInfo.getRole().iterator(); while (it.hasNext()) { String role = (String) it.next(); if (role.toUpperCase().equals("DATA_PROT")) { protectedAccess = true; break; } } ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() { public String mapRow(ResultSet rs, int rowNum) throws SQLException { String name = (rs.getString("c_table_name")); return name; } }; //extract table code String tableCd = StringUtil.getTableCd(childrenType.getParent()); String tableName = null; if (!protectedAccess) { String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access where c_table_cd = ? and c_protected_access = ? "; // log.info("getChildren " + tableSql); try { tableName = jt.queryForObject(tableSql, map, tableCd, "N"); } catch (DataAccessException e) { log.error(e.getMessage()); throw new I2B2DAOException("Database Error"); } } else { String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access where c_table_cd = ?"; try { tableName = jt.queryForObject(tableSql, map, tableCd); } catch (DataAccessException e) { log.error(e.getMessage()); throw new I2B2DAOException("Database Error"); } } 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 = ? 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 new I2B2DAOException("Database Error"); } 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 ? " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "") + " and c_hlevel = ? "; sql = sql + hidden + synonym + " order by upper(c_name) "; //log.info(sql + " " + path + " " + level); boolean obfuscatedUserFlag = Roles.getInstance().isRoleOfuscated(projectInfo); ParameterizedRowMapper<ConceptType> mapper = getMapper(new NodeType(childrenType), obfuscatedUserFlag, dbInfo.getDb_serverType()); if (dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")) { searchPath = StringUtil.escapeSQLSERVER(path); searchPath += "%"; // log.info("escaped searchPath is " + searchPath); } else if (dbInfo.getDb_serverType().toUpperCase().equals("ORACLE")) { searchPath = StringUtil.escapeORACLE(path); searchPath += "%"; } else if (dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL")) { searchPath = StringUtil.escapePOSTGRESQL(path); searchPath += "%"; } List<ConceptType> queryResult = null; try { queryResult = jt.query(sql, mapper, searchPath, (level + 1)); } catch (Exception e) { log.error(e.getMessage()); throw new I2B2DAOException("Database Error"); } if (Float.parseFloat( childrenMsg.getMessageHeaderType().getSendingApplication().getApplicationVersion()) > 1.5) { if (queryResult.size() > 0) { Iterator<ConceptType> it2 = queryResult.iterator(); while (it2.hasNext()) { ConceptType concept = it2.next(); // if a leaf has modifiers report it with visAttrib == F if (concept.getVisualattributes().startsWith("L")) { String modPath = StringUtil.getPath(concept.getKey()); // I have to do this the hard way because there are a dynamic number of applied paths to check // prevent SQL injection if (modPath.contains("'")) { modPath = modPath.replaceAll("'", "''"); } String sqlCount = "select count(*) from " + metadataSchema + tableName + " where m_exclusion_cd is null and c_fullname in"; int queryCount = 0; // build m_applied_path sub-query String m_applied_pathSql = "(m_applied_path = '" + modPath + "'"; while (modPath.length() > 3) { if (modPath.endsWith("%")) { modPath = modPath.substring(0, modPath.length() - 2); modPath = modPath.substring(0, modPath.lastIndexOf("\\") + 1) + "%"; } else modPath = modPath + "%"; m_applied_pathSql = m_applied_pathSql + " or m_applied_path = '" + modPath + "'"; } sqlCount = sqlCount + "(select c_fullname from " + metadataSchema + tableName + " where c_hlevel = 1 and m_exclusion_cd is null and " + m_applied_pathSql + " )"; if (dbInfo.getDb_serverType().toUpperCase().equals("ORACLE")) sqlCount = sqlCount + " MINUS "; else sqlCount = sqlCount + " EXCEPT "; sqlCount = sqlCount + " (select c_fullname from " + metadataSchema + tableName + " where m_exclusion_cd is not null and " + m_applied_pathSql + " )))"; try { queryCount = jt.queryForInt(sqlCount); } catch (DataAccessException e) { // TODO Auto-generated catch block e.printStackTrace(); } // log.debug("COUNT " + queryCount + " for " +sqlCount); if (queryCount > 0) { concept.setVisualattributes(concept.getVisualattributes().replace('L', 'F')); log.debug("changed " + concept.getName() + " from leaf to folder: modCount > 0"); } } } } } // log.debug("Find Children By Parent " + sql); log.debug("get_children result size = " + queryResult.size()); 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:edu.harvard.i2b2.ontology.dao.ConceptDao.java
public List findChildrenByParent(final GetModifierChildrenType modifierChildrenType, ProjectType projectInfo, DBInfoType dbInfo) throws I2B2DAOException, I2B2Exception { // ("MOD: " + modifierChildrenType.getParent()); // log.debug("MOD: " + modifierChildrenType.getAppliedPath()); // find return parameters String parameters = MOD_DEFAULT; if (modifierChildrenType.getType().equals("limited")) { parameters = MOD_LIMITED;/*from ww w . java2 s . c o m*/ } else if (modifierChildrenType.getType().equals("core")) { parameters = MOD_CORE; } else if (modifierChildrenType.getType().equals("all")) { parameters = MOD_CORE + ALL; } if (modifierChildrenType.isBlob() == true) parameters = parameters + BLOB; String metadataSchema = dbInfo.getDb_fullSchema(); setDataSource(dbInfo.getDb_dataSource()); if (projectInfo.getRole().size() == 0) { log.error("no role found for this user in project: " + projectInfo.getName()); I2B2Exception e = new I2B2Exception("No role found for user"); throw e; } Boolean protectedAccess = false; Iterator it = projectInfo.getRole().iterator(); while (it.hasNext()) { String role = (String) it.next(); if (role.toUpperCase().equals("DATA_PROT")) { protectedAccess = true; break; } } ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() { public String mapRow(ResultSet rs, int rowNum) throws SQLException { String name = (rs.getString("c_table_name")); return name; } }; //extract table code String tableCd = StringUtil.getTableCd(modifierChildrenType.getParent()); String tableName = null; if (!protectedAccess) { String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access where c_table_cd = ? and c_protected_access = ? "; // log.info("getChildren " + tableSql); try { tableName = jt.queryForObject(tableSql, map, tableCd, "N"); } catch (DataAccessException e) { log.error(e.getMessage()); throw new I2B2DAOException("Database Error"); } } else { String tableSql = "select distinct(c_table_name) from " + metadataSchema + "table_access where c_table_cd = ?"; try { tableName = jt.queryForObject(tableSql, map, tableCd); } catch (DataAccessException e) { log.error(e.getMessage()); throw new I2B2DAOException("Database Error"); } } String path = StringUtil.getPath(modifierChildrenType.getParent()); String searchPath = path; if (dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")) { searchPath = StringUtil.escapeSQLSERVER(searchPath); } else if (dbInfo.getDb_serverType().toUpperCase().equals("ORACLE")) { searchPath = StringUtil.escapeORACLE(searchPath); } else if (dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL")) { searchPath = StringUtil.escapePOSTGRESQL(searchPath); } searchPath = searchPath + "%"; String levelSql = "select c_hlevel from " + metadataSchema + tableName + " where c_fullname = ? and c_synonym_cd = 'N' and m_applied_path = ? and m_exclusion_cd is null"; int level = 0; try { level = jt.queryForInt(levelSql, path, modifierChildrenType.getAppliedPath()); } catch (DataAccessException e1) { // should only get 1 result back (path == c_fullname which should be unique) log.error(e1.getMessage()); throw new I2B2DAOException("Database Error"); } String hidden = ""; if (modifierChildrenType.isHiddens() == false) hidden = " and c_visualattributes not like '_H%'"; String synonym = ""; if (modifierChildrenType.isSynonyms() == false) synonym = " and c_synonym_cd = 'N'"; String appliedConcept = StringUtil.getLiteralPath(modifierChildrenType.getAppliedConcept()); // I have to do this the hard way because there are a dynamic number of applied paths to check // prevent SQL injection if (appliedConcept.contains("'")) { appliedConcept = appliedConcept.replaceAll("'", "''"); } String inclusionSql = "select c_fullname from " + metadataSchema + tableName + " where m_applied_path = ? and c_hlevel = " + (level + 1) + " and m_exclusion_cd is null"; String modifier_select = " and m_applied_path in ('" + appliedConcept + "'"; while (appliedConcept.length() > 2) { if (appliedConcept.endsWith("%")) { appliedConcept = appliedConcept.substring(0, appliedConcept.length() - 2); appliedConcept = appliedConcept.substring(0, appliedConcept.lastIndexOf("\\") + 1) + "%"; } else appliedConcept = appliedConcept + "%"; inclusionSql = inclusionSql + " union all (select c_fullname from " + metadataSchema + tableName + " where m_applied_path = '" + appliedConcept + "' and c_hlevel = " + (level + 1) + " and m_exclusion_cd is null)"; modifier_select = modifier_select + ", '" + appliedConcept + "'"; } String sql = "select " + parameters + " from " + metadataSchema + tableName + " where m_exclusion_cd is null and c_hlevel = ? and c_fullname like ? " + (!dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL") ? "{ESCAPE '?'}" : "") + hidden + synonym + modifier_select + ") and c_fullname in ("; if (dbInfo.getDb_serverType().toUpperCase().equals("ORACLE")) sql = sql + inclusionSql + " MINUS ("; else sql = sql + inclusionSql + " EXCEPT ("; String exclusionSql = "select c_fullname from " + metadataSchema + tableName + " where m_applied_path = ? and m_exclusion_cd is not null"; String appliedExclConcept = StringUtil.getLiteralPath(modifierChildrenType.getAppliedConcept()); // I have to do this the hard way because there are a dynamic number of applied paths to check // prevent SQL injection if (appliedExclConcept.contains("'")) { appliedExclConcept = appliedExclConcept.replaceAll("'", "''"); } while (appliedExclConcept.length() > 2) { if (appliedExclConcept.endsWith("%")) { appliedExclConcept = appliedExclConcept.substring(0, appliedExclConcept.length() - 2); appliedExclConcept = appliedExclConcept.substring(0, appliedExclConcept.lastIndexOf("\\") + 1) + "%"; } else appliedExclConcept = appliedExclConcept + "%"; exclusionSql = exclusionSql + " union all (select c_fullname from " + metadataSchema + tableName + " where m_applied_path = '" + appliedExclConcept + "' and m_exclusion_cd is not null)"; } sql = sql + exclusionSql + "))"; sql = sql + " order by (c_name) "; // log.debug("findModChildren: " + sql + (level+1) + searchPath + StringUtil.getLiteralPath(modifierChildrenType.getAppliedConcept())); final boolean ofuscatedUserFlag = Roles.getInstance().isRoleOfuscated(projectInfo); ParameterizedRowMapper<ModifierType> modMapper = getModMapper(new NodeType(modifierChildrenType), ofuscatedUserFlag, dbInfo.getDb_serverType()); List queryResult = null; try { queryResult = jt.query(sql, modMapper, (level + 1), searchPath, StringUtil.getLiteralPath(modifierChildrenType.getAppliedConcept()), StringUtil.getLiteralPath(modifierChildrenType.getAppliedConcept())); } catch (DataAccessException e) { log.error(e.getMessage()); throw new I2B2DAOException("Database Error"); } log.debug("Get Mod children result size = " + queryResult.size()); return queryResult; }
From source file:edu.harvard.i2b2.ontology.dao.DblookupDao.java
public List<DblookupType> findDblookups() throws DataAccessException, I2B2DAOException { String sql = "SELECT * FROM " + dbluTable + " WHERE" + keyOrder; List<DblookupType> queryResult = null; try {// www. j a v a 2 s.c o m queryResult = jt.query(sql, getMapper(), domainId, userId); } catch (DataAccessException e) { log.error(e.getMessage()); throw e; } log.debug("result size = " + queryResult.size()); return queryResult; }
From source file:edu.harvard.i2b2.ontology.dao.GetCodeInfoDao.java
public List findCodeInfo(final VocabRequestType vocabType, final List categories, ProjectType projectInfo) throws DataAccessException, I2B2Exception { DataSource ds = null;//from w ww . j a v a2 s.c om 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; }
From source file:edu.harvard.i2b2.ontology.dao.GetSchemesDao.java
public List findSchemes(final GetReturnType returnType) throws DataAccessException { DataSource ds = null;/* w w w. j a v a 2s. co m*/ try { ds = OntologyUtil.getInstance().getDataSource("java:OntologyDemoDS"); } catch (I2B2Exception e2) { // TODO Auto-generated catch block e2.printStackTrace(); } SimpleJdbcTemplate jt = new SimpleJdbcTemplate(ds); // find return parameters String parameters = DEFAULT; // if (returnType.getType().equals("core")){ // parameters = CORE; // } /* else if (childrenType.getType().equals("all")){ parameters = ALL; } if(childrenType.isBlob() == true) parameters = parameters + BLOB;*/ // First step is get metadata schema name from properties file. String metadataSchema = ""; metadataSchema = "i2b2metadata"; // try { //metadataSchema = OntologyUtil.getInstance().getMetaDataSchemaName(); // } catch (I2B2Exception e1) { // log.error(e1.getMessage()); // } String schemesSql = "select distinct " + parameters + " from " + metadataSchema + "schemes "; ParameterizedRowMapper<ConceptType> mapper = new ParameterizedRowMapper<ConceptType>() { public ConceptType mapRow(ResultSet rs, int rowNum) throws SQLException { ConceptType scheme = new ConceptType(); //TODO fix this for all/+blob scheme.setKey(rs.getString("key")); scheme.setName(rs.getString("name")); return scheme; } }; // TODO loop for all projects/roles List queryResult = null; try { queryResult = jt.query(schemesSql, mapper); } catch (DataAccessException e) { log.error(e.getMessage()); throw e; } log.debug("result size = " + queryResult.size()); return queryResult; }
From source file:edu.harvard.i2b2.ontology.dao.MetadataDbDao.java
public List<DBInfoType> getDbLookupByHiveOwner(String domainId, String ownerId) throws I2B2Exception, I2B2DAOException { String metadataSchema = getMetadataSchema(); String sql = "select * from " + metadataSchema + "ont_db_lookup where LOWER(c_domain_id) = ? and c_project_path = ? and (LOWER(c_owner_id) = ? or c_owner_id ='@') order by c_project_path"; String projectId = "@"; // log.info(sql + domainId + projectId + ownerId); List queryResult = null;//ww w .ja v a 2 s .c o m try { queryResult = jt.query(sql, getMapper(), domainId.toLowerCase(), projectId, ownerId.toLowerCase()); } catch (DataAccessException e) { log.error(e.getMessage()); throw new I2B2DAOException("Database error"); } return queryResult; // List<DBInfoType> dataSourceLookupList = // this.query(sql, new Object[]{domainId,projectId,ownerId}, new mapper()); // return dataSourceLookupList; }
From source file:edu.harvard.i2b2.ontology.dao.MetadataDbDao.java
@SuppressWarnings("unchecked") public List<DBInfoType> getDbLookupByHiveProjectOwner(String domainId, String projectId, String ownerId) throws I2B2Exception, I2B2DAOException { String metadataSchema = getMetadataSchema(); String sql = "select * from " + metadataSchema + "ont_db_lookup where LOWER(c_domain_id) = ? and LOWER(c_project_path) like ? and (LOWER(c_owner_id) =? or c_owner_id = '@') order by c_project_path"; // desc c_owner_id desc"; // List<DBInfoType> dataSourceLookupList = this.query(sql, new Object[]{domainId,projectId+"%",ownerId},new int[]{Types.VARCHAR,Types.VARCHAR,Types.VARCHAR},new mapper() ); // return dataSourceLookupList; // log.info(sql + domainId + projectId + ownerId); List queryResult = null;/*w w w.j a v a 2s. c om*/ try { queryResult = jt.query(sql, getMapper(), domainId.toLowerCase(), projectId.toLowerCase(), ownerId.toLowerCase()); } catch (DataAccessException e) { log.error(e.getMessage()); throw new I2B2DAOException("Database error"); } return queryResult; }
From source file:edu.harvard.i2b2.ontology.dao.SchemesDao.java
public List findSchemes(final GetReturnType returnType, final DBInfoType dbInfo) throws DataAccessException { // find return parameters String parameters = DEFAULT;//from ww w . j a v a2 s .c o m String metadataSchema = dbInfo.getDb_fullSchema(); setDataSource(dbInfo.getDb_dataSource()); String schemesSql = "select distinct " + parameters + " from " + metadataSchema + "schemes order by c_name"; ParameterizedRowMapper<ConceptType> mapper = new ParameterizedRowMapper<ConceptType>() { public ConceptType mapRow(ResultSet rs, int rowNum) throws SQLException { ConceptType scheme = new ConceptType(); //assume key is "" unless we explicitly determine otherwise // "" is valid scheme.setKey(""); String c_key = rs.getString("c_key"); if ((c_key != null) && (c_key.trim().length() > 0) && (!c_key.equals("(null)"))) scheme.setKey(c_key); scheme.setName(rs.getString("c_name")); return scheme; } }; List queryResult = null; try { queryResult = jt.query(schemesSql, mapper); } catch (DataAccessException e) { log.error(e.getMessage()); throw e; } log.debug("result size = " + queryResult.size()); return queryResult; }
From source file:edu.harvard.i2b2.pm.dao.PMDbDao.java
@SuppressWarnings("unchecked") public List<DBInfoType> getEnvironment(String domainId) throws I2B2Exception, I2B2DAOException { String sql = "select * from pm_hive_data where active='1' and status_cd <> 'D'"; if (domainId != null) sql += " and domain_id = ?"; // log.info(sql + domainId + projectId + ownerId); List<DBInfoType> queryResult = null; try {/*from w ww . j a va2 s . c o m*/ log.debug("Start query"); if (domainId == null) queryResult = jt.query(sql, getEnvironment()); else queryResult = jt.query(sql, getEnvironment(), domainId); log.debug("Query Size: " + queryResult.size()); log.debug("End query"); } catch (DataAccessException e) { log.error(e.getMessage()); e.printStackTrace(); throw new I2B2DAOException("Database error"); } return queryResult; }