List of usage examples for java.sql ResultSet getDate
java.sql.Date getDate(String columnLabel) throws SQLException;
ResultSet object as a java.sql.Date object in the Java programming language. From source file:com.mimp.hibernate.HiberEtapa.java
public ArrayList<ExpedienteFamilia> listaInfoFamilias(String exp) { Session session = sessionFactory.getCurrentSession(); session.beginTransaction();/*from w ww . j a v a 2 s .co m*/ final String BuscarExp = exp; final ArrayList<ExpedienteFamilia> allInfoFam = new ArrayList(); Work work = new Work() { @Override public void execute(Connection connection) throws SQLException { ExpedienteNna expnna; String hql = "{call HE_BUSCAR_EXP(?,?)}"; CallableStatement statement = connection.prepareCall(hql); statement.setString(1, BuscarExp); statement.registerOutParameter(2, OracleTypes.CURSOR); statement.execute(); ResultSet rs = (ResultSet) statement.getObject(2); while (rs.next()) { ExpedienteFamilia tempEF = new ExpedienteFamilia(); Familia tempFam = new Familia(); Unidad TempUn = new Unidad(); Set<Evaluacion> listaEv = new HashSet<Evaluacion>(); Set<InfoFamilia> listaInf = new HashSet<InfoFamilia>(); tempEF.setIdexpedienteFamilia(rs.getLong("IDEXPEDIENTE_FAMILIA")); tempFam.setIdfamilia(rs.getLong("IDFAMILIA")); TempUn.setIdunidad(rs.getLong("IDUNIDAD")); tempEF.setNumero(rs.getLong("NUMERO")); tempEF.setExpediente(rs.getString("EXPEDIENTE")); tempEF.setHt(rs.getString("HT")); tempEF.setNumeroExpediente(rs.getString("NUMERO_EXPEDIENTE")); tempEF.setFechaIngresoDga(rs.getDate("FECHA_INGRESO_DGA")); tempEF.setEstado(rs.getString("ESTADO")); tempEF.setTupa(rs.getDate("TUPA")); tempEF.setNacionalidad(rs.getString("NACIONALIDAD")); tempEF.setRnsa(rs.getShort("RNSA")); tempEF.setRnaa(rs.getShort("RNAA")); tempEF.setTipoFamilia(rs.getString("TIPO_FAMILIA")); tempEF.setTipoListaEspera(rs.getString("TIPO_LISTA_ESPERA")); tempEF.setHtFicha(rs.getString("HTFICHA")); tempEF.setnFicha(rs.getString("NFICHA")); tempEF.setFechaIngresoFicha(rs.getDate("FECHA_INGRESO_FICHA")); String hql2 = "{call HE_LISTAEVAL_BY_IDEXPFAM(?,?)}"; CallableStatement statement2 = connection.prepareCall(hql2); statement2.setLong(1, tempEF.getIdexpedienteFamilia()); statement2.registerOutParameter(2, OracleTypes.CURSOR); statement2.execute(); ResultSet rs2 = (ResultSet) statement2.getObject(2); while (rs2.next()) { Evaluacion tempEval = new Evaluacion(); tempEval.setIdevaluacion(rs2.getLong("IDEVALUACION")); tempEval.setExpedienteFamilia(tempEF); tempEval.setTipo(rs2.getString("TIPO")); tempEval.setFechaAsignacion(rs2.getDate("FECHA_ASIGNACION")); tempEval.setResultado(rs2.getString("RESULTADO")); tempEval.setFechaResultado(rs2.getDate("FECHA_RESULTADO")); tempEval.setObservacion(rs2.getString("OBSERVACION")); tempEval.setSustento(rs2.getString("SUSTENTO")); tempEval.setNDesignacion(rs2.getString("N_DESIGNACION")); tempEval.setNumEval(rs2.getString("NUM_EVAL")); Set<Resolucion> listaRe = new HashSet<Resolucion>(); if (tempEval.getTipo().equals("legal")) { String hql3 = "{call HE_ULTRESOL_LEGAL(?,?)}"; CallableStatement statement3 = connection.prepareCall(hql3); statement3.setLong(1, tempEval.getIdevaluacion()); statement3.registerOutParameter(2, OracleTypes.CURSOR); statement3.execute(); ResultSet rs3 = (ResultSet) statement3.getObject(2); if (rs3.next()) { Resolucion tempResol = new Resolucion(); tempResol.setIdresolucion(rs3.getLong("IDRESOLUCION")); tempResol.setTipo(rs3.getString("TIPO")); tempResol.setNumero(rs3.getString("NUMERO")); tempResol.setFechaResol(rs3.getDate("FECHA_RESOL")); tempResol.setFechaNotificacion(rs3.getDate("FECHA_NOTIFICACION")); tempResol.setEvaluacion(tempEval); listaRe.add(tempResol); } rs3.close(); statement3.close(); } tempEval.setResolucions(listaRe); listaEv.add(tempEval); } rs2.close(); statement2.close(); String hql4 = "{call HE_GETINFOFAM_POR_IDFAM(?,?)}"; CallableStatement statement4 = connection.prepareCall(hql4); statement4.setLong(1, tempFam.getIdfamilia()); statement4.registerOutParameter(2, OracleTypes.CURSOR); statement4.execute(); ResultSet rs4 = (ResultSet) statement4.getObject(2); if (rs4.next()) { InfoFamilia tempInfo = new InfoFamilia(); tempInfo.setIdinfoFamilia(rs4.getLong("IDINFO_FAMILIA")); tempInfo.setFamilia(tempFam); tempInfo.setDepRes(rs4.getString("DEP_RES")); tempInfo.setPaisRes(rs4.getString("PAIS_RES")); tempInfo.setDomicilio(rs4.getString("DOMICILIO")); tempInfo.setPropiedadVivienda(rs4.getString("PROPIEDAD_VIVIENDA")); tempInfo.setTipoVivienda(rs4.getString("TIPO_VIVIENDA")); tempInfo.setAreaVivTotal(rs4.getLong("AREA_VIV_TOTAL")); tempInfo.setAreaVivConst(rs4.getLong("AREA_VIV_CONST")); tempInfo.setDistVivienda(rs4.getString("DIST_VIVIENDA")); tempInfo.setLuz(rs4.getShort("LUZ")); tempInfo.setAgua(rs4.getShort("AGUA")); tempInfo.setDesague(rs4.getShort("DESAGUE")); tempInfo.setOtrosServ(rs4.getString("OTROS_SERV")); tempInfo.setMaterConst(rs4.getString("MATER_CONST")); tempInfo.setPared(rs4.getString("PARED")); tempInfo.setTecho(rs4.getString("TECHO")); tempInfo.setPiso(rs4.getString("PISO")); String charValueStr = ""; if (rs4.getString("NIVEL_SOCIOECONOMICO") != null) { charValueStr = rs4.getString("NIVEL_SOCIOECONOMICO"); } if (!charValueStr.equals("") && charValueStr != null) { tempInfo.setNivelSocioeconomico(charValueStr.charAt(0)); } tempInfo.setExpectativaEdadMin(rs4.getShort("EXPECTATIVA_EDAD_MIN")); tempInfo.setExpectativaGenero(rs4.getString("EXPECTATIVA_GENERO")); tempInfo.setOrigenHijos(rs4.getString("ORIGEN_HIJOS")); tempInfo.setPuedeViajar(rs4.getShort("PUEDE_VIAJAR")); tempInfo.setPredisposicionAp(rs4.getString("PREDISPOSICION_AP")); tempInfo.setCondicion(rs4.getString("CONDICION")); tempInfo.setAntecedenteFamilia(rs4.getString("ANTECEDENTE_FAMILIA")); tempInfo.setFechaAntecedenteFamilia(rs4.getDate("FECHA_ANTECEDENTE_FAMILIA")); tempInfo.setObservaciones(rs4.getString("OBSERVACIONES")); tempInfo.setNnaIncesto(rs4.getShort("NNA_INCESTO")); tempInfo.setNnaMental(rs4.getShort("NNA_MENTAL")); tempInfo.setNnaEpilepsia(rs4.getShort("NNA_EPILEPSIA")); tempInfo.setNnaAbuso(rs4.getShort("NNA_ABUSO")); tempInfo.setNnaSifilis(rs4.getShort("NNA_SIFILIS")); tempInfo.setNnaSeguiMedico(rs4.getShort("NNA_SEGUI_MEDICO")); tempInfo.setNnaOperacion(rs4.getShort("NNA_OPERACION")); tempInfo.setNnaHiperactivo(rs4.getShort("NNA_HIPERACTIVO")); tempInfo.setNnaEspecial(rs4.getShort("NNA_ESPECIAL")); tempInfo.setNnaEnfermo(rs4.getShort("NNA_ENFERMO")); tempInfo.setNnaMayor(rs4.getShort("NNA_MAYOR")); tempInfo.setNnaAdolescente(rs4.getShort("NNA_ADOLESCENTE")); tempInfo.setNnaHermano(rs4.getShort("NNA_HERMANO")); tempInfo.setEstadoCivil(rs4.getString("ESTADO_CIVIL")); tempInfo.setFechaMatrimonio(rs4.getDate("FECHA_MATRIMONIO")); tempInfo.setTelefono(rs4.getString("TELEFONO")); tempInfo.setExpectativaEdadMax(rs4.getShort("EXPECTATIVA_EDAD_MAX")); tempInfo.setnHijos(rs4.getShort("NHIJOS")); listaInf.add(tempInfo); } rs4.close(); statement4.close(); Entidad tempEnt = new Entidad(); String hql5 = "{call HE_GETENTIDAD_FAMILIA(?,?)}"; CallableStatement statement5 = connection.prepareCall(hql5); statement5.setLong(1, tempFam.getIdfamilia()); statement5.registerOutParameter(2, OracleTypes.CURSOR); statement5.execute(); ResultSet rs5 = (ResultSet) statement5.getObject(2); if (rs5.next()) { tempEnt.setIdentidad(rs5.getLong("IDENTIDAD")); tempEnt.setNombre(rs5.getString("NOMBRE")); } rs5.close(); statement5.close(); Set<Designacion> listaDesig = new HashSet<Designacion>(); String hql6 = "{call REPORTE_ULTDESIG(?,?)}"; CallableStatement statement6 = connection.prepareCall(hql6); statement6.setLong(1, tempEF.getIdexpedienteFamilia()); statement6.registerOutParameter(2, OracleTypes.CURSOR); statement6.execute(); ResultSet rs6 = (ResultSet) statement6.getObject(2); if (rs6.next()) { Designacion tempDesig = new Designacion(); tempDesig.setIddesignacion(rs6.getLong("IDDESIGNACION")); tempDesig.setNDesignacion(rs6.getString("N_DESIGNACION")); tempDesig.setPrioridad(rs6.getLong("PRIORIDAD")); tempDesig.setFechaPropuesta(rs6.getDate("FECHA_PROPUESTA")); tempDesig.setFechaConsejo(rs6.getDate("FECHA_CONSEJO")); tempDesig.setAceptacionConsejo(rs6.getShort("ACEPTACION_CONSEJO")); tempDesig.setTipoPropuesta(rs6.getString("TIPO_PROPUESTA")); listaDesig.add(tempDesig); } rs6.close(); statement6.close(); tempFam.setEntidad(tempEnt); tempFam.setInfoFamilias(listaInf); tempEF.setDesignacions(listaDesig); tempEF.setFamilia(tempFam); tempEF.setUnidad(TempUn); tempEF.setEvaluacions(listaEv); allInfoFam.add(tempEF); } rs.close(); statement.close(); } }; session.doWork(work); return allInfoFam; }
From source file:com.mimp.hibernate.HiberEtapa.java
public ExpedienteFamilia getInfoFamilia(long id) { Session session = sessionFactory.getCurrentSession(); session.beginTransaction();/*w w w.ja v a2 s . c o m*/ final Long idExp = id; final ExpedienteFamilia expFamilia = new ExpedienteFamilia(); Work work = new Work() { @Override public void execute(Connection connection) throws SQLException { ExpedienteNna expnna; String hql = "{call HE_GET_EXPEDIENTE_FAMILIA(?,?)}"; CallableStatement statement = connection.prepareCall(hql); statement.setLong(1, idExp); statement.registerOutParameter(2, OracleTypes.CURSOR); statement.execute(); ResultSet rs = (ResultSet) statement.getObject(2); while (rs.next()) { //ExpedienteFamilia tempEF = new ExpedienteFamilia(); Familia tempFam = new Familia(); Unidad TempUn = new Unidad(); Set<Evaluacion> listaEv = new HashSet<Evaluacion>(); Set<InfoFamilia> listaInf = new HashSet<InfoFamilia>(); expFamilia.setIdexpedienteFamilia(rs.getLong("IDEXPEDIENTE_FAMILIA")); tempFam.setIdfamilia(rs.getLong("IDFAMILIA")); TempUn.setIdunidad(rs.getLong("IDUNIDAD")); expFamilia.setNumero(rs.getLong("NUMERO")); expFamilia.setExpediente(rs.getString("EXPEDIENTE")); expFamilia.setHt(rs.getString("HT")); expFamilia.setNumeroExpediente(rs.getString("NUMERO_EXPEDIENTE")); expFamilia.setFechaIngresoDga(rs.getDate("FECHA_INGRESO_DGA")); expFamilia.setEstado(rs.getString("ESTADO")); expFamilia.setTupa(rs.getDate("TUPA")); expFamilia.setNacionalidad(rs.getString("NACIONALIDAD")); expFamilia.setRnsa(rs.getShort("RNSA")); expFamilia.setRnaa(rs.getShort("RNAA")); expFamilia.setTipoFamilia(rs.getString("TIPO_FAMILIA")); expFamilia.setTipoListaEspera(rs.getString("TIPO_LISTA_ESPERA")); expFamilia.setHtFicha(rs.getString("HTFICHA")); expFamilia.setnFicha(rs.getString("NFICHA")); expFamilia.setFechaIngresoFicha(rs.getDate("FECHA_INGRESO_FICHA")); String hql2 = "{call HE_LISTAEVAL_BY_IDEXPFAM(?,?)}"; CallableStatement statement2 = connection.prepareCall(hql2); statement2.setLong(1, expFamilia.getIdexpedienteFamilia()); statement2.registerOutParameter(2, OracleTypes.CURSOR); statement2.execute(); ResultSet rs2 = (ResultSet) statement2.getObject(2); while (rs2.next()) { Evaluacion tempEval = new Evaluacion(); tempEval.setIdevaluacion(rs2.getLong("IDEVALUACION")); tempEval.setExpedienteFamilia(expFamilia); tempEval.setTipo(rs2.getString("TIPO")); tempEval.setFechaAsignacion(rs2.getDate("FECHA_ASIGNACION")); tempEval.setResultado(rs2.getString("RESULTADO")); tempEval.setFechaResultado(rs2.getDate("FECHA_RESULTADO")); tempEval.setObservacion(rs2.getString("OBSERVACION")); tempEval.setSustento(rs2.getString("SUSTENTO")); tempEval.setNDesignacion(rs2.getString("N_DESIGNACION")); tempEval.setNumEval(rs2.getString("NUM_EVAL")); Set<Resolucion> listaRe = new HashSet<Resolucion>(); if (tempEval.getTipo().equals("legal")) { String hql3 = "{call HE_ULTRESOL_LEGAL(?,?)}"; CallableStatement statement3 = connection.prepareCall(hql3); statement3.setLong(1, tempEval.getIdevaluacion()); statement3.registerOutParameter(2, OracleTypes.CURSOR); statement3.execute(); ResultSet rs3 = (ResultSet) statement3.getObject(2); if (rs3.next()) { Resolucion tempResol = new Resolucion(); tempResol.setIdresolucion(rs3.getLong("IDRESOLUCION")); tempResol.setTipo(rs3.getString("TIPO")); tempResol.setNumero(rs3.getString("NUMERO")); tempResol.setFechaResol(rs3.getDate("FECHA_RESOL")); tempResol.setFechaNotificacion(rs3.getDate("FECHA_NOTIFICACION")); tempResol.setEvaluacion(tempEval); listaRe.add(tempResol); } rs3.close(); statement3.close(); } tempEval.setResolucions(listaRe); listaEv.add(tempEval); } rs2.close(); statement2.close(); String hql4 = "{call HE_GETINFOFAM_POR_IDFAM(?,?)}"; CallableStatement statement4 = connection.prepareCall(hql4); statement4.setLong(1, tempFam.getIdfamilia()); statement4.registerOutParameter(2, OracleTypes.CURSOR); statement4.execute(); ResultSet rs4 = (ResultSet) statement4.getObject(2); if (rs4.next()) { InfoFamilia tempInfo = new InfoFamilia(); tempInfo.setIdinfoFamilia(rs4.getLong("IDINFO_FAMILIA")); tempInfo.setFamilia(tempFam); tempInfo.setDepRes(rs4.getString("DEP_RES")); tempInfo.setPaisRes(rs4.getString("PAIS_RES")); tempInfo.setDomicilio(rs4.getString("DOMICILIO")); tempInfo.setPropiedadVivienda(rs4.getString("PROPIEDAD_VIVIENDA")); tempInfo.setTipoVivienda(rs4.getString("TIPO_VIVIENDA")); tempInfo.setAreaVivTotal(rs4.getLong("AREA_VIV_TOTAL")); tempInfo.setAreaVivConst(rs4.getLong("AREA_VIV_CONST")); tempInfo.setDistVivienda(rs4.getString("DIST_VIVIENDA")); tempInfo.setLuz(rs4.getShort("LUZ")); tempInfo.setAgua(rs4.getShort("AGUA")); tempInfo.setDesague(rs4.getShort("DESAGUE")); tempInfo.setOtrosServ(rs4.getString("OTROS_SERV")); tempInfo.setMaterConst(rs4.getString("MATER_CONST")); tempInfo.setPared(rs4.getString("PARED")); tempInfo.setTecho(rs4.getString("TECHO")); tempInfo.setPiso(rs4.getString("PISO")); String charValueStr = ""; if (rs4.getString("NIVEL_SOCIOECONOMICO") != null) { charValueStr = rs4.getString("NIVEL_SOCIOECONOMICO"); } if (!charValueStr.equals("") && charValueStr != null) { tempInfo.setNivelSocioeconomico(charValueStr.charAt(0)); } tempInfo.setExpectativaEdadMin(rs4.getShort("EXPECTATIVA_EDAD_MIN")); tempInfo.setExpectativaGenero(rs4.getString("EXPECTATIVA_GENERO")); tempInfo.setOrigenHijos(rs4.getString("ORIGEN_HIJOS")); tempInfo.setPuedeViajar(rs4.getShort("PUEDE_VIAJAR")); tempInfo.setPredisposicionAp(rs4.getString("PREDISPOSICION_AP")); tempInfo.setCondicion(rs4.getString("CONDICION")); tempInfo.setAntecedenteFamilia(rs4.getString("ANTECEDENTE_FAMILIA")); tempInfo.setFechaAntecedenteFamilia(rs4.getDate("FECHA_ANTECEDENTE_FAMILIA")); tempInfo.setObservaciones(rs4.getString("OBSERVACIONES")); tempInfo.setNnaIncesto(rs4.getShort("NNA_INCESTO")); tempInfo.setNnaMental(rs4.getShort("NNA_MENTAL")); tempInfo.setNnaEpilepsia(rs4.getShort("NNA_EPILEPSIA")); tempInfo.setNnaAbuso(rs4.getShort("NNA_ABUSO")); tempInfo.setNnaSifilis(rs4.getShort("NNA_SIFILIS")); tempInfo.setNnaSeguiMedico(rs4.getShort("NNA_SEGUI_MEDICO")); tempInfo.setNnaOperacion(rs4.getShort("NNA_OPERACION")); tempInfo.setNnaHiperactivo(rs4.getShort("NNA_HIPERACTIVO")); tempInfo.setNnaEspecial(rs4.getShort("NNA_ESPECIAL")); tempInfo.setNnaEnfermo(rs4.getShort("NNA_ENFERMO")); tempInfo.setNnaMayor(rs4.getShort("NNA_MAYOR")); tempInfo.setNnaAdolescente(rs4.getShort("NNA_ADOLESCENTE")); tempInfo.setNnaHermano(rs4.getShort("NNA_HERMANO")); tempInfo.setEstadoCivil(rs4.getString("ESTADO_CIVIL")); tempInfo.setFechaMatrimonio(rs4.getDate("FECHA_MATRIMONIO")); tempInfo.setTelefono(rs4.getString("TELEFONO")); tempInfo.setExpectativaEdadMax(rs4.getShort("EXPECTATIVA_EDAD_MAX")); tempInfo.setnHijos(rs4.getShort("NHIJOS")); listaInf.add(tempInfo); } rs4.close(); statement4.close(); Entidad tempEnt = new Entidad(); String hql5 = "{call HE_GETENTIDAD_FAMILIA(?,?)}"; CallableStatement statement5 = connection.prepareCall(hql5); statement5.setLong(1, tempFam.getIdfamilia()); statement5.registerOutParameter(2, OracleTypes.CURSOR); statement5.execute(); ResultSet rs5 = (ResultSet) statement5.getObject(2); if (rs5.next()) { tempEnt.setIdentidad(rs5.getLong("IDENTIDAD")); tempEnt.setNombre(rs5.getString("NOMBRE")); } rs5.close(); statement5.close(); Set<Designacion> listaDesig = new HashSet<Designacion>(); String hql6 = "{call REPORTE_ULTDESIG(?,?)}"; CallableStatement statement6 = connection.prepareCall(hql6); statement6.setLong(1, expFamilia.getIdexpedienteFamilia()); statement6.registerOutParameter(2, OracleTypes.CURSOR); statement6.execute(); ResultSet rs6 = (ResultSet) statement6.getObject(2); if (rs6.next()) { Designacion tempDesig = new Designacion(); tempDesig.setIddesignacion(rs6.getLong("IDDESIGNACION")); tempDesig.setNDesignacion(rs6.getString("N_DESIGNACION")); tempDesig.setPrioridad(rs6.getLong("PRIORIDAD")); tempDesig.setFechaPropuesta(rs6.getDate("FECHA_PROPUESTA")); tempDesig.setFechaConsejo(rs6.getDate("FECHA_CONSEJO")); tempDesig.setAceptacionConsejo(rs6.getShort("ACEPTACION_CONSEJO")); tempDesig.setTipoPropuesta(rs6.getString("TIPO_PROPUESTA")); listaDesig.add(tempDesig); } rs6.close(); statement6.close(); tempFam.setEntidad(tempEnt); tempFam.setInfoFamilias(listaInf); expFamilia.setDesignacions(listaDesig); expFamilia.setFamilia(tempFam); expFamilia.setUnidad(TempUn); expFamilia.setEvaluacions(listaEv); } rs.close(); statement.close(); } }; session.doWork(work); return expFamilia; }
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 www. j a v a 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; }
From source file:com.mimp.hibernate.HiberEtapa.java
public ArrayList<PostAdopcion> getListaPostAdopcion() { Session session = sessionFactory.getCurrentSession(); session.beginTransaction();// w ww . ja v a 2s .com final ArrayList<PostAdopcion> allPostAdopcion = new ArrayList(); Work work = new Work() { @Override public void execute(Connection connection) throws SQLException { String hql = "{call REPORTE_POST(?)}"; CallableStatement statement = connection.prepareCall(hql); statement.registerOutParameter(1, OracleTypes.CURSOR); statement.execute(); ResultSet rs = (ResultSet) statement.getObject(1); while (rs.next()) { Set<ExpedienteFamilia> listExp = new HashSet<ExpedienteFamilia>(); Set<InfoFamilia> listInfo = new HashSet<InfoFamilia>(); Set<Evaluacion> listEval = new HashSet<Evaluacion>(); Set<Resolucion> listResol = new HashSet<Resolucion>(); Set<InformePostAdoptivo> listInformes = new HashSet<InformePostAdoptivo>(); Resolucion tempResol = new Resolucion(); Evaluacion tempEval = new Evaluacion(); ExpedienteFamilia tempExpFam = new ExpedienteFamilia(); Familia tempFam = new Familia(); PostAdopcion tempPost = new PostAdopcion(); Unidad tempUa = new Unidad(); InfoFamilia tempInfo = new InfoFamilia(); tempEval.setIdevaluacion(rs.getLong("IDEVALUACION")); tempResol.setIdresolucion(rs.getLong("IDRESOLUCION")); tempResol.setTipo(rs.getString("TIPO")); tempResol.setNumero(rs.getString("NUMERO")); tempResol.setFechaResol(rs.getDate("FECHA_RESOL")); tempResol.setFechaNotificacion(rs.getDate("FECHA_NOTIFICACION")); tempPost.setIdpostAdopcion(rs.getLong("IDPOST_ADOPCION")); tempPost.setNumeroInformes(rs.getLong("NUMERO_INFORMES")); tempPost.setidNna(rs.getLong("IDNNA")); tempPost.setFechaResolucion(rs.getDate("FECHA_RESOLUCION")); String hql4 = "{call REPORTE_POST_INFORME(?,?)}"; CallableStatement statement4 = connection.prepareCall(hql4); statement4.setLong(1, tempPost.getIdpostAdopcion()); statement4.registerOutParameter(2, OracleTypes.CURSOR); statement4.execute(); ResultSet rs4 = (ResultSet) statement4.getObject(2); while (rs4.next()) { InformePostAdoptivo tempInforme = new InformePostAdoptivo(); Personal tempPersonal = new Personal(); tempInforme.setFechaRecepcionProyectado(rs4.getDate("FECHA_RECEPCION_PROYECTADO")); tempInforme.setFechaRecepcion(rs4.getDate("FECHA_RECEPCION")); tempPersonal.setNombre(rs4.getString("NOMBRE")); tempPersonal.setApellidoP(rs4.getString("APELLIDO_P")); tempInforme.setFechaInforme(rs4.getDate("FECHA_INFORME")); tempInforme.setEstado(rs4.getString("ESTADO")); tempInforme.setFechaActa(rs4.getDate("FECHA_ACTA")); tempInforme.setPersonal(tempPersonal); listInformes.add(tempInforme); } rs4.close(); statement4.close(); tempPost.setInformePostAdoptivos(listInformes); Long idEntidad = rs.getLong("IDENTIDAD"); if (!rs.wasNull()) { Entidad tempEnt = new Entidad(); String query2 = "{call RENAD_ENTIDAD(?,?)}"; CallableStatement statement2 = connection.prepareCall(query2); statement2.setLong(1, idEntidad); statement2.registerOutParameter(2, OracleTypes.CURSOR); statement2.execute(); ResultSet rs2 = (ResultSet) statement2.getObject(2); while (rs2.next()) { tempEnt.setIdentidad(rs2.getLong("IDENTIDAD")); tempEnt.setNombre(rs2.getString("NOMBRE")); tempEnt.setUser(rs2.getString("USER_")); tempEnt.setPass(rs2.getString("PASS")); tempEnt.setDireccion(rs2.getString("DIRECCION")); tempEnt.setTelefono(rs2.getString("TELEFONO")); tempEnt.setPais(rs2.getString("PAIS")); tempEnt.setResolAuto(rs2.getString("RESOL_AUTO")); tempEnt.setFechaResol(rs2.getDate("FECHA_RESOL")); tempEnt.setResolRenov(rs2.getString("RESOL_RENOV")); tempEnt.setFechaRenov(rs2.getDate("FECHA_RENOV")); tempEnt.setFechaVenc(rs2.getDate("FECHA_VENC")); tempEnt.setObs(rs2.getString("OBS")); } rs2.close(); statement2.close(); tempFam.setEntidad(tempEnt); } tempExpFam.setExpediente(rs.getString("EXPEDIENTE")); tempExpFam.setIdexpedienteFamilia(rs.getLong("IDEXPEDIENTE_FAMILIA")); tempUa.setNombre(rs.getString("NOMBRE")); tempUa.setIdunidad(rs.getLong("IDUNIDAD")); tempUa.setDepartamento(rs.getString("DEPARTAMENTO")); tempInfo.setIdinfoFamilia(rs.getLong("IDINFO_FAMILIA")); tempInfo.setPaisRes(rs.getString("PAIS_RES")); tempInfo.setDepRes(rs.getString("DEP_RES")); Set<Adoptante> listadop = new HashSet<Adoptante>(); String query3 = "{call RENAD_ADOPTANTE(?,?)}"; CallableStatement statement3 = connection.prepareCall(query3); statement3.setLong(1, tempInfo.getIdinfoFamilia()); statement3.registerOutParameter(2, OracleTypes.CURSOR); statement3.execute(); ResultSet rs3 = (ResultSet) statement3.getObject(2); while (rs3.next()) { Adoptante tempAdoptante = new Adoptante(); tempAdoptante.setIdadoptante(rs3.getLong("IDADOPTANTE")); tempAdoptante.setInfoFamilia(tempInfo); tempAdoptante.setNombre(rs3.getString("NOMBRE")); tempAdoptante.setApellidoP(rs3.getString("APELLIDO_P")); tempAdoptante.setApellidoM(rs3.getString("APELLIDO_M")); String tempsexo = ""; tempsexo = rs3.getString("SEXO"); if (!rs3.wasNull()) { tempAdoptante.setSexo(tempsexo.charAt(0)); } tempAdoptante.setFechaNac(rs3.getDate("FECHA_NAC")); tempAdoptante.setLugarNac(rs3.getString("LUGAR_NAC")); tempAdoptante.setDepaNac(rs3.getString("DEPA_NAC")); tempAdoptante.setPaisNac(rs3.getString("PAIS_NAC")); String tempTipoDoc = ""; tempTipoDoc = rs3.getString("TIPO_DOC"); if (!rs3.wasNull()) { tempAdoptante.setTipoDoc(tempTipoDoc.charAt(0)); } tempAdoptante.setNDoc(rs3.getString("N_DOC")); tempAdoptante.setCelular(rs3.getString("CELULAR")); tempAdoptante.setCorreo(rs3.getString("CORREO")); tempAdoptante.setNivelInstruccion(rs3.getString("NIVEL_INSTRUCCION")); tempAdoptante.setCulminoNivel(rs3.getShort("CULMINO_NIVEL")); tempAdoptante.setProfesion(rs3.getString("PROFESION")); tempAdoptante.setTrabajadorDepend(rs3.getShort("TRABAJADOR_DEPEND")); tempAdoptante.setOcupActualDep(rs3.getString("OCUP_ACTUAL_DEP")); tempAdoptante.setCentroTrabajo(rs3.getString("CENTRO_TRABAJO")); tempAdoptante.setDireccionCentro(rs3.getString("DIRECCION_CENTRO")); tempAdoptante.setTelefonoCentro(rs3.getString("TELEFONO_CENTRO")); tempAdoptante.setIngresoDep(rs3.getLong("INGRESO_DEP")); tempAdoptante.setTrabajadorIndepend(rs3.getShort("TRABAJADOR_INDEPEND")); tempAdoptante.setOcupActualInd(rs3.getString("OCUP_ACTUAL_IND")); tempAdoptante.setIngresoIndep(rs3.getLong("INGRESO_INDEP")); tempAdoptante.setSeguroSalud(rs3.getShort("SEGURO_SALUD")); tempAdoptante.setTipoSeguro(rs3.getString("TIPO_SEGURO")); tempAdoptante.setSeguroVida(rs3.getShort("SEGURO_VIDA")); tempAdoptante.setSistPensiones(rs3.getShort("SIST_PENSIONES")); tempAdoptante.setSaludActual(rs3.getString("SALUD_ACTUAL")); listadop.add(tempAdoptante); } rs3.close(); statement3.close(); tempInfo.setAdoptantes(listadop); tempExpFam.setUnidad(tempUa); tempExpFam.setFamilia(tempFam); tempEval.setExpedienteFamilia(tempExpFam); tempResol.setEvaluacion(tempEval); listResol.add(tempResol); tempEval.setResolucions(listResol); listEval.add(tempEval); tempExpFam.setEvaluacions(listEval); listExp.add(tempExpFam); tempFam.setExpedienteFamilias(listExp); tempInfo.setFamilia(tempFam); listInfo.add(tempInfo); tempFam.setInfoFamilias(listInfo); tempPost.setFamilia(tempFam); allPostAdopcion.add(tempPost); } rs.close(); statement.close(); } }; session.doWork(work); return allPostAdopcion; }
From source file:edu.ku.brc.specify.toycode.mexconabio.BuildTags.java
/** * /*from w w w. j a v a 2 s . co m*/ */ public void process() throws SQLException { int dupAgents = 0; int dupLocality = 0; int unknown = 0; boolean doAll = false; BasicSQLUtils.setDBConnection(dbConn); boolean doTrim = false; if (doTrim || doAll) { String trimNamesSQL = "UPDATE tagger SET first=TRIM(first),last=TRIM(last),company=TRIM(company),address1=TRIM(address1),address2=TRIM(address2),city=TRIM(city),state=TRIM(state)"; BasicSQLUtils.update(srcDBConn2, trimNamesSQL); String removeQuote = "UPDATE tagger SET first=SUBSTRING_INDEX(first, '\"', -1),last=SUBSTRING_INDEX(last, '\"', -1),company=SUBSTRING_INDEX(company, '\"', -1),address1=SUBSTRING_INDEX(address1, '\"', -1)," + "address2=SUBSTRING_INDEX(address2, '\"', -1),city=SUBSTRING_INDEX(city, '\"', -1), state=SUBSTRING_INDEX(state, '\"', -1)"; BasicSQLUtils.update(srcDBConn2, removeQuote); String trimNamesSQL2 = "UPDATE tag SET city=TRIM(city),county=TRIM(county),state=TRIM(state)"; BasicSQLUtils.update(srcDBConn2, trimNamesSQL2); String removeQuote2 = "UPDATE tag SET city=SUBSTRING_INDEX(city, '\"', -1), county=SUBSTRING_INDEX(county, '\"', -1), state=SUBSTRING_INDEX(state, '\"', -1)"; BasicSQLUtils.update(srcDBConn2, removeQuote2); } IdMapperMgr idMapperMgr = IdMapperMgr.getInstance(); idMapperMgr.setDBs(srcDBConn2, dbConn); IdHashMapper agentMapper; Division division = (Division) session.get(Division.class, 2); initialPrepareStatements(); BasicSQLUtils.update(srcDBConn, "UPDATE tag SET `Date` = null WHERE Date = '0000-00-00'"); //IdMapperMgr.setSkippingOldTableCheck(true); boolean doAgents = false; if (doAgents || doAll) { agentMapper = new IdTableMapper("agent", "AgentID", false, false); String sql = "SELECT first, last, company, address1, address2, city, state, country, zip, phone, fax, enail, tnum FROM tagger ORDER BY tnum"; Statement stmt = srcDBConn.createStatement(); stmt.setFetchSize(Integer.MIN_VALUE); log.debug("Querying for Agents..."); ResultSet rs = stmt.executeQuery(sql); int cnt = 0; while (rs.next()) { String first = rs.getString(1); String last = rs.getString(2); String company = rs.getString(3); String addr1 = rs.getString(4); String addr2 = rs.getString(5); String city = rs.getString(6); String state = rs.getString(7); String country = rs.getString(8); String zip = rs.getString(9); String phone = rs.getString(10); String fax = rs.getString(11); String email = rs.getString(12); Integer oldId = rs.getInt(13); if (oldId == null) { log.error("Null primary Id: " + last + " " + first); continue; } Agent agent = getAgent(first, last, city, state); Integer agentId = null; if (agent == null) { agent = new Agent(); agent.initialize(); agent.setFirstName(first); agent.setLastName(last); agent.setEmail(email); agent.setRemarks(company); agent.setDivision(division); Address addr = new Address(); addr.initialize(); addr.setAddress(addr1); addr.setAddress2(addr2); addr.setCity(city); addr.setState(state); addr.setCountry(country); addr.setPostalCode(zip); addr.setPhone1(phone); addr.setFax(fax); agent.getAddresses().add(addr); addr.setAgent(agent); Transaction trans = null; try { trans = session.beginTransaction(); session.saveOrUpdate(agent); session.saveOrUpdate(addr); trans.commit(); agentId = agent.getId(); } catch (Exception ex) { ex.printStackTrace(); try { if (trans != null) trans.rollback(); } catch (Exception ex2) { ex2.printStackTrace(); } } } else { agentId = agent.getId(); dupAgents++; //System.out.println("Found Agent: "+first+", "+last); } agentMapper.put(oldId, agentId); cnt++; if (cnt % 500 == 0) { System.out.println("Agents: " + cnt); } if (cnt % 400 == 0) { HibernateUtil.closeSession(); session = HibernateUtil.getCurrentSession(); hibSession = new HibernateDataProviderSession(session); } } rs.close(); stmt.close(); division = (Division) session.get(Division.class, 2); } else { //agentMapper = idMapperMgr.addTableMapper("agent", "AgentID", false); IdHashMapper.setEnableDelete(false); agentMapper = new IdTableMapper("agent", "AgentID", null, false, false); } System.out.println("Duplicated Agent: " + dupAgents); boolean doTags = true; if (doTags || doAll) { HashMap<String, Integer> localityHash = new HashMap<String, Integer>(); HashMap<Integer, String> geoFullNameHash = new HashMap<Integer, String>(); int divId = 2; int dspId = 3; int colId = 4; String sql = "SELECT t.tagid, t.`date`, t.wild, t.gender, t.city, t.county, t.state, t.country, t.zip, t.observations, t.lat, t.lon, t.sunangle, p.tnum " + "FROM tag AS t Inner Join page AS p ON t.page = p.page "; Statement stmt = srcDBConn.createStatement(); stmt.setFetchSize(Integer.MIN_VALUE); log.debug("Querying for Tags..."); ResultSet rs = stmt.executeQuery(sql); int cnt = 0; log.debug("Done querying for Tags..."); Calendar cal = Calendar.getInstance(); Timestamp ts = new Timestamp(cal.getTime().getTime()); String common = "TimestampCreated, Version, CreatedByAgentID"; String coStr = String.format( "INSERT INTO collectionobject (CatalogNumber, FieldNumber, Text1, Remarks, CollectionID, CollectionMemberId, CollectingEventID, %s) VALUES(?,?,?,?,?,?,?,?,?,?)", common); String ceStr = String.format( "INSERT INTO collectingevent (StartDate, Method, DisciplineID, LocalityID, %s) VALUES(?,?,?,?,?,?,?)", common); String lcStr = String.format( "INSERT INTO locality (Latitude1, Longitude1, SrcLatLongUnit, Lat1text, Long1text, LatLongType, DisciplineID, MaxElevation, LocalityName, GeographyID, %s) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)", common); String clStr = String.format( "INSERT INTO collector (OrderNumber, IsPrimary, CollectingEventID, DivisionID, AgentID, %s) VALUES(?,?,?,?,?,?,?,?)", common); PreparedStatement coStmt = dbConn.prepareStatement(coStr); PreparedStatement ceStmt = dbConn.prepareStatement(ceStr); PreparedStatement lcStmt = dbConn.prepareStatement(lcStr); PreparedStatement clStmt = dbConn.prepareStatement(clStr); int recNum = 1; while (rs.next()) { String tag = rs.getString(1); if (tag != null && tag.startsWith("ERR")) continue; Date date = rs.getDate(2); String wild = rs.getString(3); String gender = rs.getString(4); String city = rs.getString(5); String county = rs.getString(6); String state = rs.getString(7); String country = rs.getString(8); //String zip = rs.getString(9); String obs = rs.getString(10); double lat = rs.getDouble(11); double lon = rs.getDouble(12); double angle = rs.getDouble(13); Integer taggerId = rs.getInt(14); String locName = null; String fullName = null; Integer locId = null; Integer geoId = getGeography(country, state, county); if (geoId != null) { //locName = localityHash.get(geoId); fullName = geoFullNameHash.get(geoId); if (fullName == null) { fullName = BasicSQLUtils .querySingleObj("SELECT FullName FROM geography WHERE GeographyID = " + geoId); geoFullNameHash.put(geoId, fullName); } if (StringUtils.isNotEmpty(city)) { locName = city + ", " + fullName; } else { locName = fullName; } locId = localityHash.get(locName); } else { unknown++; fullName = "Unknown"; locName = buildLocalityName(city, fullName); geoId = 27507; // Unknown locId = localityHash.get(locName); //log.error("Couldn't find matching geography["+country+", "+state+", "+county+"]"); } if (locId == null) { lcStmt.setDouble(1, lat); lcStmt.setDouble(2, lon); lcStmt.setByte(3, (byte) 0); lcStmt.setString(4, Double.toString(lat)); lcStmt.setString(5, Double.toString(lon)); lcStmt.setString(6, "Point"); lcStmt.setInt(7, dspId); lcStmt.setDouble(8, angle); lcStmt.setString(9, locName); lcStmt.setObject(10, geoId); lcStmt.setTimestamp(11, ts); lcStmt.setInt(12, 1); lcStmt.setInt(13, 1); lcStmt.executeUpdate(); locId = BasicSQLUtils.getInsertedId(lcStmt); localityHash.put(locName, locId); } else { dupLocality++; } // (StartDate, Method, DisciplineID, LocalityID ceStmt.setDate(1, date); ceStmt.setString(2, wild); ceStmt.setInt(3, dspId); ceStmt.setInt(4, locId); ceStmt.setTimestamp(5, ts); ceStmt.setInt(6, 1); ceStmt.setInt(7, 1); ceStmt.executeUpdate(); Integer ceId = BasicSQLUtils.getInsertedId(ceStmt); //(CatalogNumber, FieldNumber, Text1, Remarks, CollectionID, CollectionMemberId coStmt.setString(1, String.format("%09d", recNum++)); coStmt.setString(2, tag); coStmt.setString(3, gender); coStmt.setString(4, obs); coStmt.setInt(5, colId); coStmt.setInt(6, colId); coStmt.setInt(7, ceId); coStmt.setTimestamp(8, ts); coStmt.setInt(9, 1); coStmt.setInt(10, 1); coStmt.executeUpdate(); //Integer coId = BasicSQLUtils.getInsertedId(coStmt); //Integer coltrId = null; if (taggerId != null) { Integer agentId = agentMapper.get(taggerId); //System.out.println(agentId); if (agentId != null) { // OrderIndex, IsPrimary, CollectingEventID, DivisionID, AgentID clStmt.setInt(1, 0); clStmt.setBoolean(2, true); clStmt.setInt(3, ceId); clStmt.setInt(4, divId); clStmt.setInt(5, agentId); clStmt.setTimestamp(6, ts); clStmt.setInt(7, 1); clStmt.setInt(8, 1); clStmt.executeUpdate(); //coltrId = BasicSQLUtils.getInsertedId(clStmt); //BasicSQLUtils.getInsertedId(clStmt); } else { log.debug("Couldn't find Agent in DB for tagger id (tnum): " + taggerId + " AgentID:: " + agentId); } } else { log.debug("Couldn't find Mapped Id for tagger id (tnum): " + taggerId); } cnt++; if (cnt % 1000 == 0) { System.out.println("Col Obj: " + cnt); } } coStmt.close(); ceStmt.close(); lcStmt.close(); clStmt.close(); System.out.println("Duplicated Agent: " + dupAgents); System.out.println("Duplicated Localities: " + dupLocality); System.out.println("Unknown Localities: " + unknown); System.out.println("Localities: " + BasicSQLUtils.getCountAsInt(dbConn, "SELECT COUNT(*) FROM locality")); } }
From source file:edu.ku.brc.specify.web.SpecifyExplorer.java
/** * @param clazz//from www . j ava2 s.c o m * @param fieldName */ public void doAlphaIndexPageSQL(final PrintWriter out, final String className, final String letter, final int numLetters, final UIFieldFormatterIFace fmt, final String sql) { boolean isNumeric = fmt != null && fmt.isNumeric(); int inx = template.indexOf(contentTag); String subContent = template.substring(0, inx); out.println(StringUtils.replace(subContent, "<!-- Title -->", className)); ClassDisplayInfo cdi = classHash.get(className); SimpleDateFormat sdf = new SimpleDateFormat("yyyy"); boolean useLetter = true; Connection connection = null; Statement stmt = null; ResultSet rs = null; try { connection = DBConnection.getInstance().createConnection(); stmt = connection.createStatement(); rs = stmt.executeQuery(sql); Vector<NameId> alphaList = new Vector<NameId>(); Hashtable<String, NameId> alphaHash = new Hashtable<String, NameId>(); boolean doingIndex = StringUtils.isEmpty(letter); System.out.println("\n\ndoingIndex " + doingIndex + " letter[" + letter + "]"); int numMin = Integer.MAX_VALUE; int numMax = Integer.MIN_VALUE; int cnt = 0; while (rs.next()) { String name; int id = rs.getInt(1); if (isNumeric) { name = rs.getString(2); name = (String) fmt.formatToUI(name); Integer numAsInt = null; Integer floor = null; try { numAsInt = Integer.parseInt(name); floor = Integer.parseInt(letter); } catch (Exception ex) { } numMin = Math.min(numMin, numAsInt); numMax = Math.min(numMax, numAsInt); if (doingIndex) { int numSegment = numAsInt / 1000; String c = Integer.toString(numSegment); NameId nis = alphaHash.get(c); if (nis == null) { nis = new NameId(c, 0, numSegment); alphaHash.put(c, nis); } nis.add(); } else { if (numAsInt >= floor && numAsInt < (floor + 1000)) { alphaList.add(new NameId(name, id, numAsInt)); } } } else { name = rs.getString(2); if (StringUtils.isEmpty(name)) { name = rs.getString(1); } if (cdi.isUseIdentityTitle()) { DataProviderSessionIFace session = null; try { session = DataProviderFactory.getInstance().createSession(); FormDataObjIFace fdi = (FormDataObjIFace) session .createQuery("from " + className + " where id = " + id, false).list().get(0); if (fdi != null) { String title = fdi.getIdentityTitle(); if (StringUtils.isNotEmpty(title)) { name = title; } } } catch (Exception ex) { ex.printStackTrace(); //log.error(ex); } finally { session.close(); } } else if (cdi.getIndexClass() != null) { if (cdi.getIndexClass() == Calendar.class) { Date date = rs.getDate(2); if (date != null) { name = sdf.format(date); } else { name = "0000"; } useLetter = false; } } int len = Math.min(numLetters, name.length()); if (doingIndex) { String c = useLetter ? name.substring(0, len).toLowerCase() : name; NameId nis = alphaHash.get(c); if (nis == null) { nis = new NameId(c, 0); alphaHash.put(c, nis); } nis.add(); } else { if ((useLetter && name.substring(0, len).toUpperCase().equals(letter)) || (!useLetter && name.equals(letter))) { alphaList.add(new NameId(name, id)); } } } cnt++; } System.out.println("alphaHash.size: " + alphaHash.size()); if (doingIndex) { alphaList = new Vector<NameId>(alphaHash.values()); } Collections.sort(alphaList); System.out.println("alphaList.size: " + alphaList.size()); if (doingIndex) { DBTableInfo ti = DBTableIdMgr.getInstance().getByShortClassName(className); out.println("<center><br/><span style=\"font-size: 14pt;\">Index For " + ti.getTitle() + "</span><br/>"); out.println("<table class=\"brdr\" border=\"0\" cellpadding=\"4\" cellspacing=\"0\">\n"); out.println("<tr><th class=\"brdr\" align=\"center\" nowrap=\"nowrap\">Index</th>"); out.println("<th class=\"brdr\" align=\"center\" nowrap=\"nowrap\">Count</th></tr>\n"); int i = 0; for (NameId nis : alphaList) { String ltrStr = nis.getNum() != null ? Integer.toString(nis.getNum() * 1000) : nis.getName().toUpperCase(); out.println("<tr>"); out.println("<td nowrap=\"nowrap\" class=\"brdr" + (((i + 1) % 2 == 0) ? "even" : "odd") + "\" align=\"center\"> <a href=\"" + servletURL + "?cls=" + className + "<r=" + ltrStr + "\">" + ltrStr + "</a> </td>\n"); out.println("<td nowrap=\"nowrap\" class=\"brdr" + (((i + 1) % 2 == 0) ? "even" : "odd") + "\" align=\"center\"><a href=\"" + servletURL + "?cls=" + className + "<r=" + ltrStr + "\">" + nis.getId() + "</a></td>\n"); out.println("</tr>"); i++; } out.println("</table></center>\n"); } else { if (alphaList.size() > 0) { if (useLetter) { out.println("<br/>" + alphaList.get(0).getName().charAt(0) + "<br/>\n"); } out.println("<table class=\"brdr\" border=\"0\" cellpadding=\4\" cellspacing=\"0\">\n"); int i = 1; for (NameId nis : alphaList) { out.println("<tr>"); out.println("<td nowrap=\"nowrap\" class=\"brdr" + (((i + 1) % 2 == 0) ? "even" : "odd") + "\" >"); out.println("<a href=\"" + servletURL + "?cls=" + className + "&id=" + nis.getId() + "\">" + nis.getName() + "</a>"); out.println("</td></tr>\n"); i++; } out.println("</table>\n"); } } } catch (Exception ex) { ex.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } if (stmt != null) { stmt.close(); } if (connection != null) { connection.close(); } } catch (Exception e) { e.printStackTrace(); } } out.println(template.substring(inx + contentTag.length() + 1, template.length())); log.info("Done"); }
From source file:edu.harvard.i2b2.ontology.dao.ConceptDao.java
private ParameterizedRowMapper<ModifierType> getModMapper(final NodeType node, final boolean ofuscatedUserFlag, final String dbType) { ParameterizedRowMapper<ModifierType> mapper = new ParameterizedRowMapper<ModifierType>() { public ModifierType mapRow(ResultSet rs, int rowNum) throws SQLException { ModifierType child = new ModifierType(); if (node.getType().equals("limited")) { child.setName(rs.getString("c_name")); child.setAppliedPath(rs.getString("m_applied_path")); child.setBasecode(rs.getString("c_basecode")); child.setKey("\\\\" + node.getNode() + rs.getString("c_fullname")); child.setLevel(rs.getInt("c_hlevel")); child.setFullname(rs.getString("c_fullname")); child.setVisualattributes(rs.getString("c_visualattributes")); child.setSynonymCd(rs.getString("c_synonym_cd")); child.setTooltip(rs.getString("c_tooltip")); } else { child.setName(rs.getString("c_name")); child.setAppliedPath(rs.getString("m_applied_path")); child.setBasecode(rs.getString("c_basecode")); child.setKey("\\\\" + node.getNode() + rs.getString("c_fullname")); child.setLevel(rs.getInt("c_hlevel")); child.setFullname(rs.getString("c_fullname")); child.setVisualattributes(rs.getString("c_visualattributes")); child.setSynonymCd(rs.getString("c_synonym_cd")); child.setFacttablecolumn(rs.getString("c_facttablecolumn")); child.setTooltip(rs.getString("c_tooltip")); 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")); }//from w w w .ja v a2 s.c o m if (node.isBlob() == true) { try { if (dbType.equals("POSTGRESQL")) { if (rs.getString("c_comment") == null) child.setComment(null); else child.setComment(rs.getString("c_comment")); } else { 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); } String c_xml = null; try { if (dbType.equals("POSTGRESQL")) c_xml = rs.getString("c_metadataxml"); else if (rs.getClob("c_metadataxml") != null) c_xml = JDBCUtil.getClobString(rs.getClob("c_metadataxml")); } catch (IOException e) { log.error(e.getMessage()); child.setMetadataxml(null); } if (c_xml == null) { child.setMetadataxml(null); } else { 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 ((node.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")); } return child; } }; return mapper; }
From source file:org.openmrs.module.sync.api.db.hibernate.HibernateSyncDAO.java
public void exportChildDB(String uuidForChild, OutputStream os) throws DAOException { PrintStream out = new PrintStream(os); Set<String> tablesToSkip = new HashSet<String>(); {/*www.j av a 2 s. co m*/ tablesToSkip.add("hl7_in_archive"); tablesToSkip.add("hl7_in_queue"); tablesToSkip.add("hl7_in_error"); tablesToSkip.add("formentry_archive"); tablesToSkip.add("formentry_queue"); tablesToSkip.add("formentry_error"); tablesToSkip.add("sync_class"); tablesToSkip.add("sync_import"); tablesToSkip.add("sync_record"); tablesToSkip.add("sync_server"); tablesToSkip.add("sync_server_class"); tablesToSkip.add("sync_server_record"); // TODO: figure out which other tables to skip // tablesToSkip.add("obs"); // tablesToSkip.add("concept"); // tablesToSkip.add("patient"); } List<String> tablesToDump = new ArrayList<String>(); Session session = sessionFactory.getCurrentSession(); String schema = (String) session.createSQLQuery("SELECT schema()").uniqueResult(); log.warn("schema: " + schema); // Get all tables that we'll need to dump { Query query = session.createSQLQuery( "SELECT tabs.table_name FROM INFORMATION_SCHEMA.TABLES tabs WHERE tabs.table_schema = '" + schema + "'"); for (Object tn : query.list()) { String tableName = (String) tn; if (!tablesToSkip.contains(tableName.toLowerCase())) tablesToDump.add(tableName); } } log.warn("tables to dump: " + tablesToDump); String thisServerGuid = getGlobalProperty(SyncConstants.PROPERTY_SERVER_UUID); // Write the DDL Header as mysqldump does { out.println("-- ------------------------------------------------------"); out.println("-- Database dump to create an openmrs child server"); out.println("-- Schema: " + schema); out.println("-- Parent GUID: " + thisServerGuid); out.println("-- Parent version: " + OpenmrsConstants.OPENMRS_VERSION); out.println("-- ------------------------------------------------------"); out.println(""); out.println("/*!40101 SET CHARACTER_SET_CLIENT=utf8 */;"); out.println("/*!40101 SET NAMES utf8 */;"); out.println("/*!40103 SET TIME_ZONE='+00:00' */;"); out.println("/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;"); out.println("/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;"); out.println("/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;"); out.println("/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;"); out.println("/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;"); out.println("/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;"); out.println("/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;"); out.println("/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;"); out.println(""); } try { // JDBC way of doing this // Connection conn = // DriverManager.getConnection("jdbc:mysql://localhost/" + schema, // "test", "test"); Connection conn = sessionFactory.getCurrentSession().connection(); try { Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); // Get the create database statement ResultSet rs = st.executeQuery("SHOW CREATE DATABASE " + schema); for (String tableName : tablesToDump) { out.println(); out.println("--"); out.println("-- Table structure for table `" + tableName + "`"); out.println("--"); out.println("DROP TABLE IF EXISTS `" + tableName + "`;"); out.println("SET @saved_cs_client = @@character_set_client;"); out.println("SET character_set_client = utf8;"); rs = st.executeQuery("SHOW CREATE TABLE " + tableName); while (rs.next()) { out.println(rs.getString("Create Table") + ";"); } out.println("SET character_set_client = @saved_cs_client;"); out.println(); { out.println("-- Dumping data for table `" + tableName + "`"); out.println("LOCK TABLES `" + tableName + "` WRITE;"); out.println("/*!40000 ALTER TABLE `" + tableName + "` DISABLE KEYS */;"); boolean first = true; rs = st.executeQuery("select * from " + tableName); ResultSetMetaData md = rs.getMetaData(); int numColumns = md.getColumnCount(); int rowNum = 0; boolean insert = false; while (rs.next()) { if (rowNum == 0) { insert = true; out.print("INSERT INTO `" + tableName + "` VALUES "); } ++rowNum; if (first) { first = false; } else { out.print(", "); } if (rowNum % 20 == 0) { out.println(); } out.print("("); for (int i = 1; i <= numColumns; ++i) { if (i != 1) { out.print(","); } if (rs.getObject(i) == null) { out.print("NULL"); } else { switch (md.getColumnType(i)) { case Types.VARCHAR: case Types.CHAR: case Types.LONGVARCHAR: out.print("'"); out.print( rs.getString(i).replaceAll("\n", "\\\\n").replaceAll("'", "\\\\'")); out.print("'"); break; case Types.BIGINT: case Types.DECIMAL: case Types.NUMERIC: out.print(rs.getBigDecimal(i)); break; case Types.BIT: out.print(rs.getBoolean(i)); break; case Types.INTEGER: case Types.SMALLINT: case Types.TINYINT: out.print(rs.getInt(i)); break; case Types.REAL: case Types.FLOAT: case Types.DOUBLE: out.print(rs.getDouble(i)); break; case Types.BLOB: case Types.VARBINARY: case Types.LONGVARBINARY: Blob blob = rs.getBlob(i); out.print("'"); InputStream in = blob.getBinaryStream(); while (true) { int b = in.read(); if (b < 0) { break; } char c = (char) b; if (c == '\'') { out.print("\'"); } else { out.print(c); } } out.print("'"); break; case Types.CLOB: out.print("'"); out.print( rs.getString(i).replaceAll("\n", "\\\\n").replaceAll("'", "\\\\'")); out.print("'"); break; case Types.DATE: out.print("'" + rs.getDate(i) + "'"); break; case Types.TIMESTAMP: out.print("'" + rs.getTimestamp(i) + "'"); break; default: throw new RuntimeException("TODO: handle type code " + md.getColumnType(i) + " (name " + md.getColumnTypeName(i) + ")"); } } } out.print(")"); } if (insert) { out.println(";"); insert = false; } out.println("/*!40000 ALTER TABLE `" + tableName + "` ENABLE KEYS */;"); out.println("UNLOCK TABLES;"); out.println(); } } } finally { conn.close(); } // Now we mark this as a child out.println("-- Now mark this as a child database"); if (uuidForChild == null) uuidForChild = SyncUtil.generateUuid(); out.println("update global_property set property_value = '" + uuidForChild + "' where property = '" + SyncConstants.PROPERTY_SERVER_UUID + "';"); // Write the footer of the DDL script { out.println("/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;"); out.println("/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;"); out.println("/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;"); out.println("/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;"); out.println("/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;"); out.println("/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;"); out.println("/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;"); out.println("/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;"); } out.flush(); out.close(); } catch (IOException ex) { log.error("IOException", ex); } catch (SQLException ex) { log.error("SQLException", ex); } }
From source file:edu.harvard.i2b2.ontology.dao.ConceptDao.java
private ParameterizedRowMapper<ConceptType> getMapper(final NodeType node, final boolean ofuscatedUserFlag, final String dbType) { 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")); if (!(node.getType().equals("default"))) { child.setBasecode(rs.getString("c_basecode")); child.setLevel(rs.getInt("c_hlevel")); // cover get Code Info case where we dont know the vocabType.category apriori if (node.getNode() != null) child.setKey("\\\\" + node.getNode() + rs.getString("c_fullname")); else child.setKey("\\\\" + rs.getString("tableCd") + rs.getString("c_fullname")); child.setSynonymCd(rs.getString("c_synonym_cd")); child.setVisualattributes(rs.getString("c_visualattributes")); Integer totalNumValue = rs.getInt("c_totalnum"); boolean nullFlag = rs.wasNull(); /*/*from www . j a v a 2 s . c o m*/ if (nullFlag) { ("null in totalnum flag "); } else { ("not null in totalnum flag "); } if (rs.getString("c_totalnum") == null) { ("null in totalnum flag using getString method"); } else { ("not null in totalnum flag using getString method [" + rs.getString("c_totalnum") + "]"); } */ if (ofuscatedUserFlag == false && nullFlag == false) { child.setTotalnum(totalNumValue); } child.setTooltip(rs.getString("c_tooltip")); child.setValuetypeCd(rs.getString("valuetype_cd")); if (!(node.getType().equals("limited"))) { 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")); } } if (node.isBlob() == true) { try { if (dbType.equals("POSTGRESQL")) { if (rs.getString("c_comment") == null) child.setComment(null); else child.setComment(rs.getString("c_comment")); } else { 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); } String c_xml = null; try { if (dbType.equals("POSTGRESQL")) c_xml = rs.getString("c_metadataxml"); else if (rs.getClob("c_metadataxml") != null) c_xml = JDBCUtil.getClobString(rs.getClob("c_metadataxml")); } catch (IOException e) { log.error(e.getMessage()); child.setMetadataxml(null); } if (c_xml == null) { child.setMetadataxml(null); } else { 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 ((node.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")); } return child; } }; return mapper; }
From source file:com.mimp.hibernate.HiberEtapa.java
public ArrayList<Revision> getListaRevisionNumero(final String numero) { Session session = sessionFactory.getCurrentSession(); final ArrayList<Revision> allRevision = new ArrayList(); Work work = new Work() { @Override/* w w w .j a va2 s.c om*/ public void execute(Connection connection) throws SQLException { String hql = "{call HE_GET_LISTA_REVISION_NUM(?,?)}"; CallableStatement statement = connection.prepareCall(hql); statement.setString(1, numero); statement.registerOutParameter(2, OracleTypes.CURSOR); statement.execute(); ResultSet rs = (ResultSet) statement.getObject(2); while (rs.next()) { Revision tempRev = new Revision(); Nna nna = new Nna(); ExpedienteFamilia expFamilia = new ExpedienteFamilia(); Familia fam = new Familia(); Unidad unidad = new Unidad(); tempRev.setIdrevision(rs.getLong("IDREVISION")); if (rs.getLong("IDNNA") != 0) { String hql2 = "{call HE_GET_NNA(?, ?)}"; CallableStatement statement2 = connection.prepareCall(hql2); statement2.setLong(1, rs.getLong("IDNNA")); statement2.registerOutParameter(2, OracleTypes.CURSOR); statement2.execute(); ResultSet rs2 = (ResultSet) statement2.getObject(2); if (rs2.next()) { nna.setIdnna(rs2.getLong(1)); tempRev.setNna(nna); } statement2.close(); } if (rs.getLong("IDEXPEDIENTE_FAMILIA") != 0) { String hql3 = "{call HE_GET_EXPEDIENTE_FAMILIA(?, ?)}"; CallableStatement statement3 = connection.prepareCall(hql3); statement3.setLong(1, rs.getLong("IDEXPEDIENTE_FAMILIA")); statement3.registerOutParameter(2, OracleTypes.CURSOR); statement3.execute(); ResultSet rs3 = (ResultSet) statement3.getObject(2); if (rs3.next()) { expFamilia.setIdexpedienteFamilia(rs3.getLong(1)); if (rs3.getLong(2) != 0) { String hql4 = "{call HE_GETFAMILIA(?, ?)}"; CallableStatement statement4 = connection.prepareCall(hql4); statement4.setLong(1, rs3.getLong(2)); statement4.registerOutParameter(2, OracleTypes.CURSOR); statement4.execute(); ResultSet rs4 = (ResultSet) statement4.getObject(2); if (rs4.next()) { fam.setIdfamilia(rs4.getLong(1)); expFamilia.setFamilia(fam); } rs4.close(); statement4.close(); } if (rs3.getLong(3) != 0) { String hql5 = "{call HE_GET_UNIDAD(?, ?)}"; CallableStatement statement5 = connection.prepareCall(hql5); statement5.setLong(1, rs3.getLong(3)); statement5.registerOutParameter(2, OracleTypes.CURSOR); statement5.execute(); ResultSet rs5 = (ResultSet) statement5.getObject(2); if (rs5.next()) { unidad.setIdunidad(rs5.getLong(1)); expFamilia.setUnidad(unidad); } rs5.close(); statement5.close(); } expFamilia.setNumero(rs3.getLong(4)); expFamilia.setExpediente(rs3.getString(5)); expFamilia.setHt(rs3.getString(6)); expFamilia.setNumeroExpediente(rs3.getString(7)); expFamilia.setFechaIngresoDga(rs3.getDate(8)); expFamilia.setEstado(rs3.getString(9)); expFamilia.setTupa(rs3.getDate(10)); expFamilia.setNacionalidad(rs3.getString(11)); expFamilia.setRnsa(rs3.getShort(12)); expFamilia.setRnaa(rs3.getShort(13)); expFamilia.setTipoFamilia(rs3.getString(14)); expFamilia.setTipoListaEspera(rs3.getString(15)); expFamilia.setHtFicha(rs3.getString(16)); expFamilia.setnFicha(rs3.getString(17)); expFamilia.setFechaIngresoFicha(rs3.getDate(18)); tempRev.setExpedienteFamilia(expFamilia); } rs3.close(); statement3.close(); } tempRev.setNumero(rs.getString("NUMERO")); tempRev.setFechaRevision(rs.getDate("FECHA_REVISION")); tempRev.setComentarios(rs.getString("COMENTARIOS")); allRevision.add(tempRev); } rs.close(); statement.close(); } }; session.doWork(work); return allRevision; }