List of usage examples for java.sql ResultSet getClob
Clob getClob(String columnLabel) throws SQLException;
ResultSet object as a Clob object in the Java programming language. From source file:cn.clickvalue.cv2.model.rowmapper.BeanPropertyRowMapper.java
/** * Retrieve a JDBC column value from a ResultSet, using the specified value type. * <p>Uses the specifically typed ResultSet accessor methods, falling back to * {@link #getResultSetValue(java.sql.ResultSet, int)} for unknown types. * <p>Note that the returned value may not be assignable to the specified * required type, in case of an unknown type. Calling code needs to deal * with this case appropriately, e.g. throwing a corresponding exception. * @param rs is the ResultSet holding the data * @param index is the column index/*from www . j a v a2s . com*/ * @param requiredType the required value type (may be <code>null</code>) * @return the value object * @throws SQLException if thrown by the JDBC API */ public static Object getResultSetValue(ResultSet rs, int index, Class requiredType) throws SQLException { if (requiredType == null) { return getResultSetValue(rs, index); } Object value = null; boolean wasNullCheck = false; // Explicitly extract typed value, as far as possible. if (String.class.equals(requiredType)) { value = rs.getString(index); } else if (boolean.class.equals(requiredType) || Boolean.class.equals(requiredType)) { value = Boolean.valueOf(rs.getBoolean(index)); wasNullCheck = true; } else if (byte.class.equals(requiredType) || Byte.class.equals(requiredType)) { value = Byte.valueOf(rs.getByte(index)); wasNullCheck = true; } else if (short.class.equals(requiredType) || Short.class.equals(requiredType)) { value = Short.valueOf(rs.getShort(index)); wasNullCheck = true; } else if (int.class.equals(requiredType) || Integer.class.equals(requiredType)) { value = Integer.valueOf(rs.getInt(index)); wasNullCheck = true; } else if (long.class.equals(requiredType) || Long.class.equals(requiredType)) { value = Long.valueOf(rs.getLong(index)); wasNullCheck = true; } else if (float.class.equals(requiredType) || Float.class.equals(requiredType)) { value = Float.valueOf(rs.getFloat(index)); wasNullCheck = true; } else if (double.class.equals(requiredType) || Double.class.equals(requiredType) || Number.class.equals(requiredType)) { value = Double.valueOf(rs.getDouble(index)); wasNullCheck = true; } else if (byte[].class.equals(requiredType)) { value = rs.getBytes(index); } else if (java.sql.Date.class.equals(requiredType)) { value = rs.getDate(index); } else if (java.sql.Time.class.equals(requiredType)) { value = rs.getTime(index); } else if (java.sql.Timestamp.class.equals(requiredType) || java.util.Date.class.equals(requiredType)) { value = rs.getTimestamp(index); } else if (BigDecimal.class.equals(requiredType)) { value = rs.getBigDecimal(index); } else if (Blob.class.equals(requiredType)) { value = rs.getBlob(index); } else if (Clob.class.equals(requiredType)) { value = rs.getClob(index); } else { // Some unknown type desired -> rely on getObject. value = getResultSetValue(rs, index); } // Perform was-null check if demanded (for results that the // JDBC driver returns as primitives). if (wasNullCheck && value != null && rs.wasNull()) { value = null; } return value; }
From source file:it.greenvulcano.gvesb.utils.GVESBPropertyHandler.java
private String expandSQLProperties(String str, Map<String, Object> inProperties, Object object, Object extra) throws PropertiesHandlerException { PreparedStatement ps = null;//w w w. java2s .c o m ResultSet rs = null; String sqlStatement = null; Connection conn = null; String connName = ""; boolean intConn = false; try { if (!PropertiesHandler.isExpanded(str)) { str = PropertiesHandler.expand(str, inProperties, object, extra); } int pIdx = str.indexOf("::"); if (pIdx != -1) { connName = str.substring(0, pIdx); sqlStatement = str.substring(pIdx + 2); intConn = true; } else { sqlStatement = str; } if (intConn) { conn = JDBCConnectionBuilder.getConnection(connName); } else if ((extra != null) && (extra instanceof Connection)) { conn = (Connection) extra; } else { throw new PropertiesHandlerException( "Error handling 'sql' metadata '" + str + "', Connection undefined."); } logger.debug("Executing SQL statement {" + sqlStatement + "} on connection [" + connName + "]"); ps = conn.prepareStatement(sqlStatement); rs = ps.executeQuery(); String paramValue = null; if (rs.next()) { ResultSetMetaData rsmeta = rs.getMetaData(); if (rsmeta.getColumnType(1) == Types.CLOB) { Clob clob = rs.getClob(1); if (clob != null) { Reader is = clob.getCharacterStream(); StringWriter strW = new StringWriter(); IOUtils.copy(is, strW); is.close(); paramValue = strW.toString(); } } else { paramValue = rs.getString(1); } } return (paramValue != null) ? paramValue.trim() : paramValue; } catch (Exception exc) { logger.warn("Error handling 'sql' metadata '" + sqlStatement + "'", exc); if (PropertiesHandler.isExceptionOnErrors()) { if (exc instanceof PropertiesHandlerException) { throw (PropertiesHandlerException) exc; } throw new PropertiesHandlerException("Error handling 'sql' metadata '" + str + "'", exc); } return "sql" + PROP_START + str + PROP_END; } finally { if (rs != null) { try { rs.close(); } catch (Exception exc) { // do nothing } } if (ps != null) { try { ps.close(); } catch (Exception exc) { // do nothing } } if (intConn && (conn != null)) { try { JDBCConnectionBuilder.releaseConnection(connName, conn); } catch (Exception exc) { // do nothing } } } }
From source file:dk.netarkivet.harvester.datamodel.JobDBDAO.java
/** Read a single job from the job database. * * @param jobID ID of the job.//from w w w . ja va2 s.c o m * @param connection an open connection to the harvestDatabase * @return A Job object * @throws UnknownID if the job id does not exist. * @throws IOFailure if there was some problem talking to the database. */ private synchronized Job read(Connection connection, Long jobID) { if (!exists(connection, jobID)) { throw new UnknownID("Job id " + jobID + " is not known in persistent storage"); } PreparedStatement statement = null; try { statement = connection.prepareStatement("SELECT " + "harvest_id, status, channel, " + "forcemaxcount, forcemaxbytes, " + "forcemaxrunningtime, orderxml, " + "orderxmldoc, seedlist, harvest_num," + "harvest_errors, harvest_error_details, " + "upload_errors, upload_error_details, " + "startdate, enddate, submitteddate, creationdate, " + "edition, resubmitted_as_job, continuationof, harvestname_prefix, snapshot " + "FROM jobs WHERE job_id = ?"); statement.setLong(1, jobID); ResultSet result = statement.executeQuery(); result.next(); long harvestID = result.getLong(1); JobStatus status = JobStatus.fromOrdinal(result.getInt(2)); String channel = result.getString(3); long forceMaxCount = result.getLong(4); long forceMaxBytes = result.getLong(5); long forceMaxRunningTime = result.getLong(6); String orderxml = result.getString(7); Document orderXMLdoc = null; boolean useClobs = DBSpecifics.getInstance().supportsClob(); if (useClobs) { Clob clob = result.getClob(8); orderXMLdoc = getOrderXMLdocFromClob(clob); } else { orderXMLdoc = XmlUtils.documentFromString(result.getString(8)); } String seedlist = ""; if (useClobs) { Clob clob = result.getClob(9); seedlist = clob.getSubString(1, (int) clob.length()); } else { seedlist = result.getString(9); } int harvestNum = result.getInt(10); String harvestErrors = result.getString(11); String harvestErrorDetails = result.getString(12); String uploadErrors = result.getString(13); String uploadErrorDetails = result.getString(14); Date startdate = DBUtils.getDateMaybeNull(result, 15); Date stopdate = DBUtils.getDateMaybeNull(result, 16); Date submittedDate = DBUtils.getDateMaybeNull(result, 17); Date creationDate = DBUtils.getDateMaybeNull(result, 18); Long edition = result.getLong(19); Long resubmittedAsJob = DBUtils.getLongMaybeNull(result, 20); Long continuationOfJob = DBUtils.getLongMaybeNull(result, 21); String harvestnamePrefix = result.getString(22); boolean snapshot = result.getBoolean(23); statement.close(); // IDs should match up in a natural join // The following if-block is an attempt to fix Bug 1856, an // unexplained derby deadlock, by making this statement a dirty // read. String domainStatement = "SELECT domains.name, configurations.name " + "FROM domains, configurations, job_configs " + "WHERE job_configs.job_id = ?" + " AND job_configs.config_id = configurations.config_id" + " AND domains.domain_id = configurations.domain_id"; if (Settings.get(CommonSettings.DB_SPECIFICS_CLASS).contains(CommonSettings.DB_IS_DERBY_IF_CONTAINS)) { statement = connection.prepareStatement(domainStatement + " WITH UR"); } else { statement = connection.prepareStatement(domainStatement); } statement.setLong(1, jobID); result = statement.executeQuery(); Map<String, String> configurationMap = new HashMap<String, String>(); while (result.next()) { String domainName = result.getString(1); String configName = result.getString(2); configurationMap.put(domainName, configName); } final Job job = new Job(harvestID, configurationMap, channel, snapshot, forceMaxCount, forceMaxBytes, forceMaxRunningTime, status, orderxml, orderXMLdoc, seedlist, harvestNum, continuationOfJob); job.appendHarvestErrors(harvestErrors); job.appendHarvestErrorDetails(harvestErrorDetails); job.appendUploadErrors(uploadErrors); job.appendUploadErrorDetails(uploadErrorDetails); if (startdate != null) { job.setActualStart(startdate); } if (stopdate != null) { job.setActualStop(stopdate); } if (submittedDate != null) { job.setSubmittedDate(submittedDate); } if (creationDate != null) { job.setCreationDate(creationDate); } job.configsChanged = false; job.setJobID(jobID); job.setEdition(edition); if (resubmittedAsJob != null) { job.setResubmittedAsJob(resubmittedAsJob); } if (harvestnamePrefix == null) { job.setDefaultHarvestNamePrefix(); } else { job.setHarvestFilenamePrefix(harvestnamePrefix); } return job; } catch (SQLException e) { String message = "SQL error reading job " + jobID + " in database" + "\n" + ExceptionUtils.getSQLExceptionCause(e); log.warn(message, e); throw new IOFailure(message, e); } catch (DocumentException e) { String message = "XML error reading job " + jobID + " in database"; log.warn(message, e); throw new IOFailure(message, e); } }
From source file:org.apache.oozie.tools.OozieDBCLI.java
private void convertClobToBlobinDerby(Connection conn, String startingVersion) throws Exception { if (conn == null) { return;//from w w w. j a va 2 s. c o m } System.out.println("Converting clob columns to blob for all tables"); Statement statement = conn.createStatement(); for (Map.Entry<String, List<String>> tableClobColumnMap : getTableClobColumnMap().entrySet()) { String tableName = tableClobColumnMap.getKey(); List<String> columnNames = tableClobColumnMap.getValue(); for (String column : columnNames) { statement.executeUpdate(getAddColumnQuery(tableName, TEMP_COLUMN_PREFIX + column, "blob")); } ResultSet rs = statement.executeQuery(getSelectQuery(tableName, columnNames)); while (rs.next()) { for (String column : columnNames) { if (startingVersion.equals(DB_VERSION_PRE_4_0) && tableName.equals("COORD_ACTIONS") && column.equals("push_missing_dependencies")) { // The push_missing_depdencies column was added in DB_VERSION_FOR_4_0 as a CLOB and we're going to convert // it to BLOB in DB_VERSION_FOR_5_0. However, if Oozie 5 did the upgrade from DB_VERSION_PRE_4_0 to // DB_VERSION_FOR_4_0 (and is now doing it for DB_VERSION_FOR_5_0) push_missing_depdencies will already be a // BLOB because Oozie 5 created the column instead of Oozie 4; and the update query below will fail. continue; } Clob confClob = rs.getClob(column); if (confClob == null) { continue; } PreparedStatement ps = conn.prepareStatement( "update " + tableName + " set " + TEMP_COLUMN_PREFIX + column + "=? where id = ?"); byte[] data = IOUtils.toByteArray(confClob.getCharacterStream(), "UTF-8"); ps.setBinaryStream(1, new ByteArrayInputStream(data), data.length); ps.setString(2, rs.getString(1)); ps.executeUpdate(); ps.close(); } } rs.close(); for (String column : columnNames) { statement.executeUpdate(getDropColumnQuery(tableName, column)); statement.executeUpdate( "RENAME COLUMN " + tableName + "." + TEMP_COLUMN_PREFIX + column + " TO " + column); } } statement.close(); System.out.println("DONE"); }
From source file:it.greenvulcano.gvesb.utils.GVESBPropertyHandler.java
private String expandSQLListProperties(String str, Map<String, Object> inProperties, Object object, Object extra) throws PropertiesHandlerException { PreparedStatement ps = null;/*from w w w.j a v a 2s . co m*/ ResultSet rs = null; String sqlStatement = null; Connection conn = null; String connName = ""; String separator = ","; boolean intConn = false; try { if (!PropertiesHandler.isExpanded(str)) { str = PropertiesHandler.expand(str, inProperties, object, extra); } int pIdx = str.indexOf("::"); if (pIdx != -1) { connName = str.substring(0, pIdx); sqlStatement = str.substring(pIdx + 2); int pIdx2 = str.indexOf("::", pIdx + 2); if (pIdx2 != -1) { separator = str.substring(pIdx + 2, pIdx2); sqlStatement = str.substring(pIdx2 + 2); } intConn = true; } else { sqlStatement = str; } if (intConn) { conn = JDBCConnectionBuilder.getConnection(connName); } else if ((extra != null) && (extra instanceof Connection)) { conn = (Connection) extra; } else { throw new PropertiesHandlerException( "Error handling 'sqllist' metadata '" + str + "', Connection undefined."); } ps = conn.prepareStatement(sqlStatement); rs = ps.executeQuery(); String paramValue = ""; int type = rs.getMetaData().getColumnType(1); while (rs.next()) { if (type == Types.CLOB) { Clob clob = rs.getClob(1); if (clob != null) { Reader is = clob.getCharacterStream(); StringWriter strW = new StringWriter(); IOUtils.copy(is, strW); is.close(); paramValue += separator + strW.toString(); } else { paramValue += separator + "null"; } } else { paramValue += separator + rs.getString(1); } } if (!paramValue.equals("")) { paramValue = paramValue.substring(separator.length()); } return (paramValue != null) ? paramValue.trim() : paramValue; } catch (Exception exc) { logger.warn("Error handling 'sqllist' metadata '" + sqlStatement + "'", exc); if (PropertiesHandler.isExceptionOnErrors()) { if (exc instanceof PropertiesHandlerException) { throw (PropertiesHandlerException) exc; } throw new PropertiesHandlerException("Error handling 'sqllist' metadata '" + str + "'", exc); } return "sqllist" + PROP_START + str + PROP_END; } finally { if (rs != null) { try { rs.close(); } catch (Exception exc) { // do nothing } } if (ps != null) { try { ps.close(); } catch (Exception exc) { // do nothing } } if (intConn && (conn != null)) { try { JDBCConnectionBuilder.releaseConnection(connName, conn); } catch (Exception exc) { // do nothing } } } }
From source file:dk.netarkivet.harvester.datamodel.DomainDBDAO.java
/** * Make SeedList based on entry from seedlists * (id, name, comments, seeds)./*from w ww . j ava2 s . c o m*/ * @param res a Resultset * @return a SeedList based on ResultSet entry. * @throws SQLException if unable to get data from database */ private SeedList getSeedListFromResultset(ResultSet res) throws SQLException { final long seedlistId = res.getLong(1); final String seedlistName = res.getString(2); String seedlistComments = res.getString(3); String seedlistContents = ""; if (DBSpecifics.getInstance().supportsClob()) { Clob clob = res.getClob(4); seedlistContents = clob.getSubString(1, (int) clob.length()); } else { seedlistContents = res.getString(4); } final SeedList seedlist = new SeedList(seedlistName, seedlistContents); seedlist.setComments(seedlistComments); seedlist.setID(seedlistId); return seedlist; }
From source file:com.alibaba.otter.node.etl.common.db.utils.SqlUtils.java
/** * Retrieve a JDBC column value from a ResultSet, using the specified value * type./*from w w w. ja v a 2 s. c o m*/ * <p> * Uses the specifically typed ResultSet accessor methods, falling back to * {@link #getResultSetValue(java.sql.ResultSet, int)} for unknown types. * <p> * Note that the returned value may not be assignable to the specified * required type, in case of an unknown type. Calling code needs to deal * with this case appropriately, e.g. throwing a corresponding exception. * * @param rs is the ResultSet holding the data * @param index is the column index * @param requiredType the required value type (may be <code>null</code>) * @return the value object * @throws SQLException if thrown by the JDBC API */ private static String getResultSetValue(ResultSet rs, int index, Class<?> requiredType) throws SQLException { if (requiredType == null) { return getResultSetValue(rs, index); } Object value = null; boolean wasNullCheck = false; // Explicitly extract typed value, as far as possible. if (String.class.equals(requiredType)) { value = rs.getString(index); } else if (boolean.class.equals(requiredType) || Boolean.class.equals(requiredType)) { value = Boolean.valueOf(rs.getBoolean(index)); wasNullCheck = true; } else if (byte.class.equals(requiredType) || Byte.class.equals(requiredType)) { value = new Byte(rs.getByte(index)); wasNullCheck = true; } else if (short.class.equals(requiredType) || Short.class.equals(requiredType)) { value = new Short(rs.getShort(index)); wasNullCheck = true; } else if (int.class.equals(requiredType) || Integer.class.equals(requiredType)) { value = new Long(rs.getLong(index)); wasNullCheck = true; } else if (long.class.equals(requiredType) || Long.class.equals(requiredType)) { value = rs.getBigDecimal(index); wasNullCheck = true; } else if (float.class.equals(requiredType) || Float.class.equals(requiredType)) { value = new Float(rs.getFloat(index)); wasNullCheck = true; } else if (double.class.equals(requiredType) || Double.class.equals(requiredType) || Number.class.equals(requiredType)) { value = new Double(rs.getDouble(index)); wasNullCheck = true; } else if (java.sql.Time.class.equals(requiredType)) { // try { // value = rs.getTime(index); // } catch (SQLException e) { value = rs.getString(index);// ?string0000Time // if (value == null && !rs.wasNull()) { // value = "00:00:00"; // // mysqlzeroDateTimeBehavior=convertToNull0null // } // } } else if (java.sql.Timestamp.class.equals(requiredType) || java.sql.Date.class.equals(requiredType)) { // try { // value = convertTimestamp(rs.getTimestamp(index)); // } catch (SQLException e) { // ?string0000-00-00 00:00:00Timestamp value = rs.getString(index); // if (value == null && !rs.wasNull()) { // value = "0000:00:00 00:00:00"; // // mysqlzeroDateTimeBehavior=convertToNull0null // } // } } else if (BigDecimal.class.equals(requiredType)) { value = rs.getBigDecimal(index); } else if (BigInteger.class.equals(requiredType)) { value = rs.getBigDecimal(index); } else if (Blob.class.equals(requiredType)) { value = rs.getBlob(index); } else if (Clob.class.equals(requiredType)) { value = rs.getClob(index); } else if (byte[].class.equals(requiredType)) { try { byte[] bytes = rs.getBytes(index); if (bytes == null) { value = null; } else { value = new String(bytes, "ISO-8859-1");// binaryiso-8859-1 } } catch (UnsupportedEncodingException e) { throw new SQLException(e); } } else { // Some unknown type desired -> rely on getObject. value = getResultSetValue(rs, index); } // Perform was-null check if demanded (for results that the // JDBC driver returns as primitives). if (wasNullCheck && (value != null) && rs.wasNull()) { value = null; } return (value == null) ? null : convertUtilsBean.convert(value); }
From source file:edu.harvard.i2b2.ontology.dao.GetTermInfoDao.java
public List findByFullname(final GetTermInfoType termInfoType, List categories, ProjectType projectInfo, final String dbType) throws DataAccessException { DataSource ds = null;/* w w w .j a v a 2 s .c om*/ try { ds = OntologyUtil.getInstance().getDataSource("java:OntologyLocalDS"); } catch (I2B2Exception e2) { // TODO Auto-generated catch block e2.printStackTrace(); } SimpleJdbcTemplate jt = new SimpleJdbcTemplate(ds); // find return parameters String parameters = DEFAULT; if (termInfoType.getType().equals("core")) { parameters = CORE; } else if (termInfoType.getType().equals("all")) { parameters = ALL; } if (termInfoType.isBlob() == true) parameters = parameters + BLOB; //extract table code String tableCd = StringUtil.getTableCd(termInfoType.getSelf()); // 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 tableName = jt.queryForObject(tableSql, map, tableCd); String path = StringUtil.getPath(termInfoType.getSelf()); // if(path.equals("\\Providers")) // path="\\RPDR\\Providers"; String searchPath = path; String hidden = ""; if (termInfoType.isHiddens() == false) hidden = " and c_visualattributes not like '_H%'"; String synonym = ""; if (termInfoType.isSynonyms() == false) synonym = " and c_synonym_cd = 'N'"; String sql = "select " + parameters + " from " + metadataSchema + tableName + " where c_fullname like ? "; sql = sql + hidden + synonym + " order by c_name "; // log.info(sql + path + level); final boolean obfuscatedUserFlag = Roles.getInstance().isRoleOfuscated(projectInfo); ParameterizedRowMapper<ConceptType> mapper = new ParameterizedRowMapper<ConceptType>() { public ConceptType mapRow(ResultSet rs, int rowNum) throws SQLException { ConceptType self = new ConceptType(); self.setName(rs.getString("c_name")); self.setBasecode(rs.getString("c_basecode")); self.setLevel(rs.getInt("c_hlevel")); self.setKey(rs.getString("c_fullname")); self.setSynonymCd(rs.getString("c_synonym_cd")); self.setVisualattributes(rs.getString("c_visualattributes")); Integer totalNum = rs.getInt("c_totalnum"); if (obfuscatedUserFlag == false) { self.setTotalnum(totalNum); } self.setFacttablecolumn(rs.getString("c_facttablecolumn")); self.setTablename(rs.getString("c_tablename")); self.setColumnname(rs.getString("c_columnname")); self.setColumndatatype(rs.getString("c_columndatatype")); self.setOperator(rs.getString("c_operator")); self.setDimcode(rs.getString("c_dimcode")); self.setTooltip(rs.getString("c_tooltip")); if (termInfoType.isBlob() == true) { if (rs.getClob("c_comment") == null) self.setComment(null); else { try { if (dbType.equals("POSTGRESQL")) self.setComment(rs.getString("c_comment")); else self.setComment(JDBCUtil.getClobString(rs.getClob("c_comment"))); } catch (IOException e1) { log.error(e1.getMessage()); self.setComment(null); } } if (rs.getClob("c_metadataxml") == null) { self.setMetadataxml(null); } else { String c_xml = null; try { if (dbType.equals("POSTGRESQL")) c_xml = rs.getString("c_metadataxml"); else c_xml = JDBCUtil.getClobString(rs.getClob("c_metadataxml")); } catch (IOException e1) { log.error(e1.getMessage()); self.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()); self.setMetadataxml(null); } catch (IOException e1) { log.error(e1.getMessage()); self.setMetadataxml(null); } if (rootElement != null) { XmlValueType xml = new XmlValueType(); xml.getAny().add(rootElement); self.setMetadataxml(xml); } } else { self.setMetadataxml(null); } } } if ((termInfoType.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) self.setUpdateDate(null); else self.setUpdateDate(factory.getXMLGregorianCalendar(date.getTime())); date = rs.getDate("download_date"); if (date == null) self.setDownloadDate(null); else self.setDownloadDate(factory.getXMLGregorianCalendar(date.getTime())); date = rs.getDate("import_date"); if (date == null) self.setImportDate(null); else self.setImportDate(factory.getXMLGregorianCalendar(date.getTime())); self.setSourcesystemCd(rs.getString("sourcesystem_cd")); self.setValuetypeCd(rs.getString("valuetype_cd")); } return self; } }; // log.info(sql + " " + path + " " + level); List queryResult = null; try { queryResult = jt.query(sql, mapper, searchPath); } 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 self = (ConceptType) itr.next(); self.setKey("\\\\" + tableCd + self.getKey()); } } return queryResult; }
From source file:com.streamsets.pipeline.lib.jdbc.JdbcUtil.java
public Field resultToField(ResultSetMetaData md, ResultSet rs, int columnIndex, int maxClobSize, int maxBlobSize, DataType userSpecifiedType, UnknownTypeAction unknownTypeAction, boolean timestampToString) throws SQLException, IOException, StageException { Field field;/* w ww .j a va 2 s. c o m*/ if (userSpecifiedType != DataType.USE_COLUMN_TYPE) { // If user specifies the data type, overwrite the column type returned by database. field = Field.create(Field.Type.valueOf(userSpecifiedType.getLabel()), rs.getObject(columnIndex)); } else { // All types as of JDBC 2.0 are here: // https://docs.oracle.com/javase/8/docs/api/constant-values.html#java.sql.Types.ARRAY // Good source of recommended mappings is here: // http://www.cs.mun.ca/java-api-1.5/guide/jdbc/getstart/mapping.html switch (md.getColumnType(columnIndex)) { case Types.BIGINT: field = Field.create(Field.Type.LONG, rs.getObject(columnIndex)); break; case Types.BINARY: case Types.LONGVARBINARY: case Types.VARBINARY: field = Field.create(Field.Type.BYTE_ARRAY, rs.getBytes(columnIndex)); break; case Types.BIT: case Types.BOOLEAN: field = Field.create(Field.Type.BOOLEAN, rs.getObject(columnIndex)); break; case Types.CHAR: case Types.LONGNVARCHAR: case Types.LONGVARCHAR: case Types.NCHAR: case Types.NVARCHAR: case Types.VARCHAR: field = Field.create(Field.Type.STRING, rs.getObject(columnIndex)); break; case Types.CLOB: case Types.NCLOB: field = Field.create(Field.Type.STRING, getClobString(rs.getClob(columnIndex), maxClobSize)); break; case Types.BLOB: field = Field.create(Field.Type.BYTE_ARRAY, getBlobBytes(rs.getBlob(columnIndex), maxBlobSize)); break; case Types.DATE: field = Field.create(Field.Type.DATE, rs.getDate(columnIndex)); break; case Types.DECIMAL: case Types.NUMERIC: field = Field.create(Field.Type.DECIMAL, rs.getBigDecimal(columnIndex)); field.setAttribute(HeaderAttributeConstants.ATTR_SCALE, String.valueOf(rs.getMetaData().getScale(columnIndex))); field.setAttribute(HeaderAttributeConstants.ATTR_PRECISION, String.valueOf(rs.getMetaData().getPrecision(columnIndex))); break; case Types.DOUBLE: field = Field.create(Field.Type.DOUBLE, rs.getObject(columnIndex)); break; case Types.FLOAT: case Types.REAL: field = Field.create(Field.Type.FLOAT, rs.getObject(columnIndex)); break; case Types.INTEGER: field = Field.create(Field.Type.INTEGER, rs.getObject(columnIndex)); break; case Types.ROWID: field = Field.create(Field.Type.STRING, rs.getRowId(columnIndex).toString()); break; case Types.SMALLINT: case Types.TINYINT: field = Field.create(Field.Type.SHORT, rs.getObject(columnIndex)); break; case Types.TIME: field = Field.create(Field.Type.TIME, rs.getObject(columnIndex)); break; case Types.TIMESTAMP: final Timestamp timestamp = rs.getTimestamp(columnIndex); if (timestampToString) { field = Field.create(Field.Type.STRING, timestamp == null ? null : timestamp.toString()); } else { field = Field.create(Field.Type.DATETIME, timestamp); if (timestamp != null) { final long actualNanos = timestamp.getNanos() % NANOS_TO_MILLIS_ADJUSTMENT; if (actualNanos > 0) { field.setAttribute(FIELD_ATTRIBUTE_NANOSECONDS, String.valueOf(actualNanos)); } } } break; // Ugly hack until we can support LocalTime, LocalDate, LocalDateTime, etc. case Types.TIME_WITH_TIMEZONE: OffsetTime offsetTime = rs.getObject(columnIndex, OffsetTime.class); field = Field.create(Field.Type.TIME, Date.from(offsetTime.atDate(LocalDate.MIN).toInstant())); break; case Types.TIMESTAMP_WITH_TIMEZONE: OffsetDateTime offsetDateTime = rs.getObject(columnIndex, OffsetDateTime.class); field = Field.create(Field.Type.ZONED_DATETIME, offsetDateTime.toZonedDateTime()); break; //case Types.REF_CURSOR: // JDK8 only case Types.SQLXML: case Types.STRUCT: case Types.ARRAY: case Types.DATALINK: case Types.DISTINCT: case Types.JAVA_OBJECT: case Types.NULL: case Types.OTHER: case Types.REF: default: if (unknownTypeAction == null) { return null; } switch (unknownTypeAction) { case STOP_PIPELINE: throw new StageException(JdbcErrors.JDBC_37, md.getColumnType(columnIndex), md.getColumnLabel(columnIndex)); case CONVERT_TO_STRING: Object value = rs.getObject(columnIndex); if (value != null) { field = Field.create(Field.Type.STRING, rs.getObject(columnIndex).toString()); } else { field = Field.create(Field.Type.STRING, null); } break; default: throw new IllegalStateException("Unknown action: " + unknownTypeAction); } } } return field; }
From source file:edu.harvard.i2b2.ontology.dao.GetCategoriesDao.java
public List findRootCategories(final GetReturnType returnType, final ProjectType projectInfo) throws DataAccessException, I2B2DAOException { DataSource ds = null;//w w w .j a v a2 s.c o m try { ds = OntologyUtil.getInstance().getDataSource("java:OntologyLocalDS"); } catch (I2B2Exception e2) { log.error(e2.getMessage()); ; } SimpleJdbcTemplate jt = new SimpleJdbcTemplate(ds); // find return parameters String parameters = DEFAULT; if (returnType.getType().equals("core")) { parameters = CORE; } /* else if (returnType.getType().equals("all")){ parameters = ALL; } */ // First step is get metadata schema name from properties file. String metadataSchema = ""; try { metadataSchema = OntologyUtil.getInstance().getMetaDataSchemaName(); } catch (I2B2Exception e1) { log.error(e1.getMessage()); } // First step is to call PM to see what roles/project user belongs to. if (projectInfo.getRole().size() == 0) { log.error("no role found for this user in project: " + projectInfo.getName()); I2B2DAOException e = new I2B2DAOException("No role found for user"); throw e; } String roles = "( '"; Iterator it = projectInfo.getRole().iterator(); while (it.hasNext()) { String role = (String) it.next(); roles = roles + role; if (it.hasNext()) { roles = roles + "', '"; } else { roles = roles + "' )"; } } log.debug(roles); log.debug(projectInfo.getId().toLowerCase()); String tablesSql = "select distinct(c_table_cd), " + parameters + " from " + metadataSchema + "table_access where c_project = ? and c_role in " + roles; final boolean obfuscatedUserFlag = Roles.getInstance().isRoleOfuscated(projectInfo); ParameterizedRowMapper<ConceptType> mapper = new ParameterizedRowMapper<ConceptType>() { public ConceptType mapRow(ResultSet rs, int rowNum) throws SQLException { ConceptType child = new ConceptType(); //TODO fix this for all/+blob child.setKey("\\\\" + rs.getString("c_table_cd") + rs.getString("c_fullname")); child.setName(rs.getString("c_name")); if (returnType.getType().equals("core")) { child.setBasecode(rs.getString("c_basecode")); child.setLevel(rs.getInt("c_hlevel")); child.setSynonymCd(rs.getString("c_synonym_cd")); child.setVisualattributes(rs.getString("c_visualattributes")); Integer totalNum = rs.getInt("c_totalnum"); if (obfuscatedUserFlag == false) { child.setTotalnum(totalNum); } child.setFacttablecolumn(rs.getString("c_facttablecolumn")); child.setTablename(rs.getString("c_tablename")); child.setColumnname(rs.getString("c_columnname")); child.setColumndatatype(rs.getString("c_columndatatype")); child.setOperator(rs.getString("c_operator")); child.setDimcode(rs.getString("c_dimcode")); child.setTooltip(rs.getString("c_tooltip")); } return child; } }; List queryResult = null; try { queryResult = jt.query(tablesSql, mapper, projectInfo.getId().toLowerCase()); } catch (DataAccessException e) { log.error(e.getMessage()); throw e; } log.debug("result size = " + queryResult.size()); if (returnType.isBlob() == true && queryResult != null) { Iterator itr = queryResult.iterator(); while (itr.hasNext()) { ConceptType child = (ConceptType) itr.next(); String clobSql = "select c_metadataxml, c_comment from " + metadataSchema + "table_access where c_name = ? and c_tooltip = ?"; ParameterizedRowMapper<ConceptType> map = new ParameterizedRowMapper<ConceptType>() { public ConceptType mapRow(ResultSet rs, int rowNum) throws SQLException { ConceptType concept = new ConceptType(); // ResultSetMetaData rsmd = rs.getMetaData(); // rsmd.get if (rs.getClob("c_metadataxml") == null) { concept.setMetadataxml(null); } else { String c_xml = null; try { c_xml = JDBCUtil.getClobString(rs.getClob("c_metadataxml")); } catch (IOException e1) { // TODO Auto-generated catch block log.error(e1.getMessage()); } 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); } catch (IOException 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); } } if (rs.getClob("c_comment") == null) { concept.setComment(null); } else { try { 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) { child.setMetadataxml(((ConceptType) (clobResult.get(0))).getMetadataxml()); child.setComment(((ConceptType) (clobResult.get(0))).getComment()); } else { child.setMetadataxml(null); child.setComment(null); } } } return queryResult; }