List of usage examples for java.sql ResultSetMetaData getColumnCount
int getColumnCount() throws SQLException;
ResultSet object. From source file:be.dataminded.nifi.plugins.util.JdbcCommon.java
public static long convertToAvroStream(final ResultSet rs, final OutputStream outStream, String recordName, ResultSetRowCallback callback, final int maxRows, boolean convertNames) throws SQLException, IOException { final Schema schema = createSchema(rs, recordName, convertNames); final GenericRecord rec = new GenericData.Record(schema); final DatumWriter<GenericRecord> datumWriter = new GenericDatumWriter<>(schema); try (final DataFileWriter<GenericRecord> dataFileWriter = new DataFileWriter<>(datumWriter)) { dataFileWriter.create(schema, outStream); final ResultSetMetaData meta = rs.getMetaData(); final int nrOfColumns = meta.getColumnCount(); long nrOfRows = 0; while (rs.next()) { if (callback != null) { callback.processRow(rs); }//from w w w .j a va 2 s .c o m for (int i = 1; i <= nrOfColumns; i++) { final int javaSqlType = meta.getColumnType(i); // Need to handle CLOB and BLOB before getObject() is called, due to ResultSet's maximum portability statement if (javaSqlType == CLOB) { Clob clob = rs.getClob(i); if (clob != null) { long numChars = clob.length(); char[] buffer = new char[(int) numChars]; InputStream is = clob.getAsciiStream(); int index = 0; int c = is.read(); while (c > 0) { buffer[index++] = (char) c; c = is.read(); } rec.put(i - 1, new String(buffer)); clob.free(); } else { rec.put(i - 1, null); } continue; } if (javaSqlType == BLOB) { Blob blob = rs.getBlob(i); if (blob != null) { long numChars = blob.length(); byte[] buffer = new byte[(int) numChars]; InputStream is = blob.getBinaryStream(); int index = 0; int c = is.read(); while (c > 0) { buffer[index++] = (byte) c; c = is.read(); } ByteBuffer bb = ByteBuffer.wrap(buffer); rec.put(i - 1, bb); blob.free(); } else { rec.put(i - 1, null); } continue; } final Object value = rs.getObject(i); if (value == null) { rec.put(i - 1, null); } else if (javaSqlType == BINARY || javaSqlType == VARBINARY || javaSqlType == LONGVARBINARY || javaSqlType == ARRAY) { // bytes requires little bit different handling byte[] bytes = rs.getBytes(i); ByteBuffer bb = ByteBuffer.wrap(bytes); rec.put(i - 1, bb); } else if (value instanceof Byte) { // tinyint(1) type is returned by JDBC driver as java.sql.Types.TINYINT // But value is returned by JDBC as java.lang.Byte // (at least H2 JDBC works this way) // direct put to avro record results: // org.apache.avro.AvroRuntimeException: Unknown datum type java.lang.Byte rec.put(i - 1, ((Byte) value).intValue()); } else if (value instanceof Short) { //MS SQL returns TINYINT as a Java Short, which Avro doesn't understand. rec.put(i - 1, ((Short) value).intValue()); } else if (value instanceof BigDecimal) { // Avro can't handle BigDecimal as a number - it will throw an AvroRuntimeException such as: "Unknown datum type: java.math.BigDecimal: 38" try { int scale = meta.getScale(i); BigDecimal bigDecimal = ((BigDecimal) value); if (scale == 0) { if (meta.getPrecision(i) < 10) { rec.put(i - 1, bigDecimal.intValue()); } else { rec.put(i - 1, bigDecimal.longValue()); } } else { rec.put(i - 1, bigDecimal.doubleValue()); } } catch (Exception e) { rec.put(i - 1, value.toString()); } } else if (value instanceof BigInteger) { // Check the precision of the BIGINT. Some databases allow arbitrary precision (> 19), but Avro won't handle that. // It the SQL type is BIGINT and the precision is between 0 and 19 (inclusive); if so, the BigInteger is likely a // long (and the schema says it will be), so try to get its value as a long. // Otherwise, Avro can't handle BigInteger as a number - it will throw an AvroRuntimeException // such as: "Unknown datum type: java.math.BigInteger: 38". In this case the schema is expecting a string. if (javaSqlType == BIGINT) { int precision = meta.getPrecision(i); if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) { rec.put(i - 1, value.toString()); } else { try { rec.put(i - 1, ((BigInteger) value).longValueExact()); } catch (ArithmeticException ae) { // Since the value won't fit in a long, convert it to a string rec.put(i - 1, value.toString()); } } } else { rec.put(i - 1, value.toString()); } } else if (value instanceof Number || value instanceof Boolean) { if (javaSqlType == BIGINT) { int precision = meta.getPrecision(i); if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) { rec.put(i - 1, value.toString()); } else { rec.put(i - 1, value); } } else { rec.put(i - 1, value); } } else { // The different types that we support are numbers (int, long, double, float), // as well as boolean values and Strings. Since Avro doesn't provide // timestamp types, we want to convert those to Strings. So we will cast anything other // than numbers or booleans to strings by using the toString() method. rec.put(i - 1, value.toString()); } } dataFileWriter.append(rec); nrOfRows += 1; if (maxRows > 0 && nrOfRows == maxRows) break; } return nrOfRows; } }
From source file:com.trackplus.ddl.DataReader.java
private static int getTableData(BufferedWriter writer, BufferedWriter writerClean, BufferedWriter writerUpdate, BufferedWriter writerUpdateClean, Connection con, String tableName, StringValueConverter stringValueConverter) throws DDLException { Statement st = MetaDataBL.createStatement(con); String sql = "SELECT * FROM " + tableName; ResultSet rs;//from www. j a v a 2 s. com try { rs = st.executeQuery(sql); } catch (SQLException e) { throw new DDLException(e.getMessage(), e); } int idx = 0; int idxUpdate = 0; try { ResultSetMetaData md = rs.getMetaData(); String primaryKey = MetaDataBL.getPrimaryKey(tableName, con); int columnCount = md.getColumnCount(); StringBuilder sb = new StringBuilder(); sb.append("INSERT INTO " + tableName + "("); for (int i = 0; i < columnCount; i++) { String columnName = md.getColumnName(i + 1); sb.append(columnName); if (i < columnCount - 1) { sb.append(", "); } } sb.append(") VALUES("); StringBuilder updateSQL; while (rs.next()) { StringBuilder line = new StringBuilder(); line.append(sb); String primaryKeyValue = rs.getString(primaryKey); for (int i = 0; i < columnCount; i++) { String value; String columnName = md.getColumnName(i + 1); try { value = stringValueConverter.getStringValue(md, i + 1, rs, tableName); } catch (DDLException ex) { LOGGER.warn("Error: " + ex.getMessage() + " for column:" + columnName + " in table:" + tableName + " primary key " + primaryKey + "=" + primaryKeyValue + ". Will be set to NULL!"); value = null; } if (value != null && value.length() > MAX_VALUE_STRING) { if ("MAILBODY".equalsIgnoreCase(columnName) || "CLOBVALUE".equalsIgnoreCase(columnName)) { //blob } else { updateSQL = new StringBuilder(); updateSQL.append("UPDATE ").append(tableName).append(" SET ").append(columnName) .append("="); updateSQL.append(value).append("\n WHERE ").append(primaryKey).append("=") .append(primaryKeyValue); updateSQL.append(";"); MetaDataBL.appendLine(writerUpdateClean, updateSQL.toString()); updateSQL.append(LINE_SEPARATOR); MetaDataBL.appendLine(writerUpdate, updateSQL.toString()); idxUpdate++; value = null; } } line.append(value); if (i < columnCount - 1) { line.append(", "); } } line.append(");"); MetaDataBL.appendLine(writerClean, line.toString()); line.append(LINE_SEPARATOR); MetaDataBL.appendLine(writer, line.toString()); idx++; } } catch (SQLException e) { throw new DDLException(e.getMessage(), e); } try { rs.close(); } catch (SQLException e) { throw new DDLException(e.getMessage(), e); } if (idxUpdate > 0) { LOGGER.warn("There was " + idxUpdate + " records with String size>" + MAX_VALUE_STRING + " found in table:" + tableName); } return idx; }
From source file:com.hangum.tadpole.engine.sql.util.QueryUtils.java
/** * query to json// ww w. j a v a 2 s . c o m * * @param userDB * @param strQuery * @param listParam */ @SuppressWarnings("deprecation") public static JsonArray selectToJson(final UserDBDAO userDB, final String strQuery, final List<Object> listParam) throws Exception { final JsonArray jsonArry = new JsonArray(); SqlMapClient client = TadpoleSQLManager.getInstance(userDB); QueryRunner qr = new QueryRunner(client.getDataSource()); qr.query(strQuery, listParam.toArray(), new ResultSetHandler<Object>() { @Override public Object handle(ResultSet rs) throws SQLException { ResultSetMetaData metaData = rs.getMetaData(); while (rs.next()) { JsonObject jsonObj = new JsonObject(); for (int i = 1; i <= metaData.getColumnCount(); i++) { String columnName = metaData.getColumnLabel(i); String value = rs.getString(i) == null ? "" : rs.getString(i); jsonObj.addProperty(columnName.toLowerCase(), value); } jsonArry.add(jsonObj); } return jsonArry; } }); return jsonArry; }
From source file:com.cloudera.impala.testutil.ImpalaJdbcClient.java
/** * Executes one or more queries using the given ImpalaJdbcClient. Multiple queries * should be seperated using semi-colons. * @throws SQLException//from w ww . j av a 2s . c o m */ private static void execQuery(ImpalaJdbcClient client, String queryString) throws SQLException, NumberFormatException { String[] queries = queryString.trim().split(";"); for (String query : queries) { query = query.trim(); if (query.indexOf(" ") > -1) { if (query.substring(0, query.indexOf(" ")).equalsIgnoreCase("use")) { String[] split_query = query.split(" "); String db_name = split_query[split_query.length - 1]; client.changeDatabase(db_name); client.getStatement().close(); continue; } } long startTime = System.currentTimeMillis(); ResultSet res = client.execQuery(query); ResultSetMetaData meta = res.getMetaData(); ArrayList<String> arrayList = Lists.newArrayList(); // This token (and the [END] token) are used to help parsing the result output // for test verification purposes. LOG.info("----[START]----"); int rowCount = 0; while (res.next()) { arrayList.clear(); for (int i = 1; i <= meta.getColumnCount(); ++i) { // Format the value based on the column type String colVal = formatColumnValue(res.getString(i), meta.getColumnTypeName(i)); arrayList.add(colVal); } LOG.info(Joiner.on(",").join(arrayList)); ++rowCount; } LOG.info("----[END]----"); long endTime = System.currentTimeMillis(); float seconds = (endTime - startTime) / 1000F; LOG.info("Returned " + rowCount + " row(s) in " + seconds + "s"); // TODO: To work around a JDBC driver issue (CDH-10035), make sure the Statement // is closed after every query. client.getStatement().close(); } }
From source file:it.greenvulcano.gvesb.utils.ResultSetUtils.java
/** * Returns all values from the ResultSet as an XML. * For instance, if the ResultSet has 3 values, the returned XML will have following fields: * <RowSet> * <data> * <row> * <col>value1</col> * <col>value2</col> * <col>value3</col> * </row> * <row> * <col>value4</col> * <col>value5</col> * <col>value6</col> * </row> * ..//from w ww . j a v a2s. com * <row> * <col>valuex</col> * <col>valuey</col> * <col>valuez</col> * </row> * </data> * </RowSet> * @param rs * @return * @throws Exception */ public static Document getResultSetAsDOM(ResultSet rs) throws Exception { XMLUtils xml = XMLUtils.getParserInstance(); try { Document doc = xml.newDocument("RowSet"); Element docRoot = doc.getDocumentElement(); if (rs != null) { try { ResultSetMetaData metadata = rs.getMetaData(); Element data = null; Element row = null; Element col = null; Text text = null; String textVal = null; while (rs.next()) { boolean restartResultset = false; for (int j = 1; j <= metadata.getColumnCount() && !restartResultset; j++) { col = xml.createElement(doc, "col"); restartResultset = false; switch (metadata.getColumnType(j)) { case Types.CLOB: { Clob clob = rs.getClob(j); if (clob != null) { Reader is = clob.getCharacterStream(); StringWriter strW = new StringWriter(); IOUtils.copy(is, strW); is.close(); textVal = strW.toString(); } else { textVal = ""; } } break; case Types.BLOB: { Blob blob = rs.getBlob(j); if (blob != null) { InputStream is = blob.getBinaryStream(); ByteArrayOutputStream baos = new ByteArrayOutputStream(); IOUtils.copy(is, baos); is.close(); try { byte[] buffer = Arrays.copyOf(baos.toByteArray(), (int) blob.length()); textVal = new String(Base64.getEncoder().encode(buffer)); } catch (SQLFeatureNotSupportedException exc) { textVal = new String(Base64.getEncoder().encode(baos.toByteArray())); } } else { textVal = ""; } } break; case -10: { // OracleTypes.CURSOR Object obj = rs.getObject(j); if (obj instanceof ResultSet) { rs = (ResultSet) obj; metadata = rs.getMetaData(); } restartResultset = true; } break; default: { textVal = rs.getString(j); if (textVal == null) { textVal = ""; } } } if (restartResultset) { continue; } if (row == null || j == 1) { row = xml.createElement(doc, "row"); } if (textVal != null) { text = doc.createTextNode(textVal); col.appendChild(text); } row.appendChild(col); } if (row != null) { if (data == null) { data = xml.createElement(doc, "data"); } data.appendChild(row); } } if (data != null) { docRoot.appendChild(data); } } finally { if (rs != null) { try { rs.close(); } catch (Exception exc) { // do nothing } rs = null; } } } return doc; } finally { XMLUtils.releaseParserInstance(xml); } }
From source file:com.hangum.tadpole.engine.sql.util.QueryUtils.java
/** * query to xml/*from w ww . ja v a 2 s.com*/ * * @param userDB * @param strQuery * @param listParam */ @SuppressWarnings("deprecation") public static String selectToXML(final UserDBDAO userDB, final String strQuery, final List<Object> listParam) throws Exception { final StringWriter stWriter = new StringWriter(); DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); final Document doc = builder.newDocument(); final Element results = doc.createElement("Results"); doc.appendChild(results); SqlMapClient client = TadpoleSQLManager.getInstance(userDB); QueryRunner qr = new QueryRunner(client.getDataSource()); qr.query(strQuery, listParam.toArray(), new ResultSetHandler<Object>() { @Override public Object handle(ResultSet rs) throws SQLException { ResultSetMetaData metaData = rs.getMetaData(); while (rs.next()) { Element row = doc.createElement("Row"); results.appendChild(row); for (int i = 1; i <= metaData.getColumnCount(); i++) { String columnName = metaData.getColumnName(i); Object value = rs.getObject(i) == null ? "" : rs.getObject(i); Element node = doc.createElement(columnName); node.appendChild(doc.createTextNode(value.toString())); row.appendChild(node); } } return stWriter.toString(); } }); DOMSource domSource = new DOMSource(doc); TransformerFactory tf = TransformerFactory.newInstance(); tf.setAttribute("indent-number", 4); Transformer transformer = tf.newTransformer(); transformer.setOutputProperty(OutputKeys.INDENT, "yes"); transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes"); transformer.setOutputProperty(OutputKeys.METHOD, "xml"); transformer.setOutputProperty(OutputKeys.ENCODING, "UTF-8");//"ISO-8859-1"); StreamResult sr = new StreamResult(stWriter); transformer.transform(domSource, sr); return stWriter.toString(); }
From source file:dbs_project.util.Utils.java
public static String resultSetToHtmlTable(java.sql.ResultSet rs) throws SQLException { int rowCount = 0; final StringBuilder result = new StringBuilder(); result.append("<P ALIGN='center'>\n<TABLE BORDER=1>\n"); ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); //header// w w w . jav a 2 s . c o m result.append("\t<TR>\n"); for (int i = 0; i < columnCount; ++i) { result.append("\t\t<TH>").append(rsmd.getColumnLabel(i + 1)).append("</TH>\n"); } result.append("\t</TR>\n"); //data while (rs.next()) { ++rowCount; result.append("\t<TR>\n"); for (int i = 0; i < columnCount; ++i) { String value = rs.getString(i + 1); if (rs.wasNull()) { value = "<null>"; } result.append("\t\t<TD>").append(value).append("</TD>\n"); } result.append("\t</TR>\n"); } result.append("</TABLE>\n</P>\n"); return result.toString(); }
From source file:funcoes.funcoes.java
@SuppressWarnings("rawtypes") public static Vector<Comparable> proximaLinha(ResultSet rs, ResultSetMetaData rsmd) throws SQLException { Vector<Comparable> LinhaAtual = new Vector<Comparable>(); try {//from ww w . j ava2 s .c o m for (int i = 1; i <= rsmd.getColumnCount(); ++i) { switch (rsmd.getColumnType(i)) { case Types.VARCHAR: LinhaAtual.addElement(rs.getString(i)); break; case Types.TIMESTAMP: LinhaAtual.addElement(rs.getDate(i).toLocaleString().substring(0, 10)); break; case Types.INTEGER: LinhaAtual.addElement(rs.getInt(i)); break; case Types.DECIMAL: LinhaAtual.addElement(funcoes.paraFormatoDinheiro(rs.getDouble(i))); break; case Types.DOUBLE: LinhaAtual.addElement(funcoes.paraFormatoDinheiro(rs.getDouble(i))); break; } } } catch (SQLException e) { } return LinhaAtual; }
From source file:edu.ucsb.nceas.MCTestCase.java
protected static Vector<Hashtable<String, Object>> dbSelect(String sqlStatement, String methodName) throws SQLException { Vector<Hashtable<String, Object>> resultVector = new Vector<Hashtable<String, Object>>(); DBConnectionPool connPool = DBConnectionPool.getInstance(); DBConnection dbconn = DBConnectionPool.getDBConnection(methodName); int serialNumber = dbconn.getCheckOutSerialNumber(); PreparedStatement pstmt = null; debug("Selecting from db: " + sqlStatement); pstmt = dbconn.prepareStatement(sqlStatement); pstmt.execute();//from w ww. j a va 2 s. c o m ResultSet resultSet = pstmt.getResultSet(); ResultSetMetaData rsMetaData = resultSet.getMetaData(); int numColumns = rsMetaData.getColumnCount(); debug("Number of data columns: " + numColumns); while (resultSet.next()) { Hashtable<String, Object> hashTable = new Hashtable<String, Object>(); for (int i = 1; i <= numColumns; i++) { if (resultSet.getObject(i) != null) { hashTable.put(rsMetaData.getColumnName(i), resultSet.getObject(i)); } } debug("adding data row to result vector"); resultVector.add(hashTable); } resultSet.close(); pstmt.close(); DBConnectionPool.returnDBConnection(dbconn, serialNumber); return resultVector; }
From source file:com.healthmarketscience.jackcess.util.ImportUtil.java
/** * Returns a List of Column instances converted from the given * ResultSetMetaData (this is the same method used by the various {@code * importResultSet()} methods)./* w w w. j av a 2 s .co m*/ * * @return a List of Columns */ public static List<ColumnBuilder> toColumns(ResultSetMetaData md) throws SQLException { List<ColumnBuilder> columns = new LinkedList<ColumnBuilder>(); for (int i = 1; i <= md.getColumnCount(); i++) { ColumnBuilder column = new ColumnBuilder(md.getColumnName(i)).escapeName(); int lengthInUnits = md.getColumnDisplaySize(i); column.setSQLType(md.getColumnType(i), lengthInUnits); DataType type = column.getType(); // we check for isTrueVariableLength here to avoid setting the length // for a NUMERIC column, which pretends to be var-len, even though it // isn't if (type.isTrueVariableLength() && !type.isLongValue()) { column.setLengthInUnits((short) lengthInUnits); } if (type.getHasScalePrecision()) { int scale = md.getScale(i); int precision = md.getPrecision(i); if (type.isValidScale(scale)) { column.setScale((byte) scale); } if (type.isValidPrecision(precision)) { column.setPrecision((byte) precision); } } columns.add(column); } return columns; }