List of usage examples for java.sql ResultSetMetaData getColumnName
String getColumnName(int column) throws SQLException;
From source file:com.mapd.utility.SQLImporter.java
private void createMapDTable(ResultSetMetaData metaData) { StringBuilder sb = new StringBuilder(); sb.append("Create table ").append(cmd.getOptionValue("targetTable")).append("("); // Now iterate the metadata try {/* w ww . ja v a2 s. co m*/ for (int i = 1; i <= metaData.getColumnCount(); i++) { if (i > 1) { sb.append(","); } LOGGER.debug("Column name is " + metaData.getColumnName(i)); LOGGER.debug("Column type is " + metaData.getColumnTypeName(i)); LOGGER.debug("Column type is " + metaData.getColumnType(i)); sb.append(metaData.getColumnName(i)).append(" "); sb.append(getColType(metaData.getColumnType(i), metaData.getPrecision(i), metaData.getScale(i))); } sb.append(")"); if (Integer.valueOf(cmd.getOptionValue("fragmentSize", "0")) > 0) { sb.append(" with (fragment_size = "); sb.append(cmd.getOptionValue("fragmentSize", "0")); sb.append(")"); } } catch (SQLException ex) { LOGGER.error("Error processing the metadata - " + ex.toString()); exit(1); } executeMapDCommand(sb.toString()); }
From source file:WeblogicDbServlet.java
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, java.io.IOException { String sql = "select * from athlete"; Connection conn = null;// www . j av a 2s .com Statement stmt = null; ResultSet rs = null; ResultSetMetaData rsm = null; response.setContentType("text/html"); java.io.PrintWriter out = response.getWriter(); out.println("<html><head><title>Weblogic Database Access</title></head><body>"); out.println("<h2>Database info</h2>"); out.println("<table border='1'><tr>"); try { conn = pool.getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); rsm = rs.getMetaData(); int colCount = rsm.getColumnCount(); //print column names for (int i = 1; i <= colCount; ++i) { out.println("<th>" + rsm.getColumnName(i) + "</th>"); } out.println("</tr>"); while (rs.next()) { out.println("<tr>"); for (int i = 1; i <= colCount; ++i) out.println("<td>" + rs.getString(i) + "</td>"); out.println("</tr>"); } } catch (Exception e) { throw new ServletException(e.getMessage()); } finally { try { stmt.close(); conn.close(); } catch (SQLException sqle) { } } out.println("</table></body></html>"); out.close(); }
From source file:be.dataminded.nifi.plugins.util.JdbcCommon.java
/** * Creates an Avro schema from a result set. If the table/record name is known a priori and provided, use that as a * fallback for the record name if it cannot be retrieved from the result set, and finally fall back to a default value. * * @param rs The result set to convert to Avro * @param recordName The a priori record name to use if it cannot be determined from the result set. * @return A Schema object representing the result set converted to an Avro record * @throws SQLException if any error occurs during conversion */// www.j ava 2 s . c o m public static Schema createSchema(final ResultSet rs, String recordName, boolean convertNames) throws SQLException { final ResultSetMetaData meta = rs.getMetaData(); final int nrOfColumns = meta.getColumnCount(); String tableName = StringUtils.isEmpty(recordName) ? "NiFi_ExecuteSQL_Record" : recordName; if (nrOfColumns > 0) { String tableNameFromMeta = meta.getTableName(1); if (!StringUtils.isBlank(tableNameFromMeta)) { tableName = tableNameFromMeta; } } if (convertNames) { tableName = normalizeNameForAvro(tableName); } final FieldAssembler<Schema> builder = SchemaBuilder.record(tableName).namespace("any.data").fields(); /** * Some missing Avro types - Decimal, Date types. May need some additional work. */ for (int i = 1; i <= nrOfColumns; i++) { /** * as per jdbc 4 specs, getColumnLabel will have the alias for the column, if not it will have the column name. * so it may be a better option to check for columnlabel first and if in case it is null is someimplementation, * check for alias. Postgres is the one that has the null column names for calculated fields. */ String nameOrLabel = StringUtils.isNotEmpty(meta.getColumnLabel(i)) ? meta.getColumnLabel(i) : meta.getColumnName(i); String columnName = convertNames ? normalizeNameForAvro(nameOrLabel) : nameOrLabel; switch (meta.getColumnType(i)) { case CHAR: case LONGNVARCHAR: case LONGVARCHAR: case NCHAR: case NVARCHAR: case VARCHAR: case CLOB: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion() .noDefault(); break; case BIT: case BOOLEAN: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().booleanType().endUnion() .noDefault(); break; case INTEGER: if (meta.isSigned(i)) { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion() .noDefault(); } else { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType().endUnion() .noDefault(); } break; case SMALLINT: case TINYINT: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion() .noDefault(); break; case BIGINT: // Check the precision of the BIGINT. Some databases allow arbitrary precision (> 19), but Avro won't handle that. // If the precision > 19 (or is negative), use a string for the type, otherwise use a long. The object(s) will be converted // to strings as necessary int precision = meta.getPrecision(i); if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion() .noDefault(); } else { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType().endUnion() .noDefault(); } break; // java.sql.RowId is interface, is seems to be database // implementation specific, let's convert to String case ROWID: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion() .noDefault(); break; case FLOAT: case REAL: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().floatType().endUnion() .noDefault(); break; case DOUBLE: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().doubleType().endUnion() .noDefault(); break; // Did not find direct suitable type, need to be clarified!!!! case DECIMAL: case NUMERIC: int scale = meta.getScale(i); if (scale == 0) { if (meta.getPrecision(i) < 10) { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion() .noDefault(); } else { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType() .endUnion().noDefault(); } } else { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().doubleType().endUnion() .noDefault(); } break; // Did not find direct suitable type, need to be clarified!!!! case DATE: case TIME: case TIMESTAMP: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion() .noDefault(); break; case BINARY: case VARBINARY: case LONGVARBINARY: case ARRAY: case BLOB: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().bytesType().endUnion() .noDefault(); break; default: throw new IllegalArgumentException("createSchema: Unknown SQL type " + meta.getColumnType(i) + " cannot be converted to Avro type"); } } return builder.endRecord(); }
From source file:jp.mathes.databaseWiki.db.postgres.PostgresBackend.java
@SuppressWarnings({ "rawtypes", "unchecked" })
@Override//from ww w .jav a2 s .c o m
public Document getDocument(final String user, final String password, final String db, final String table,
final String name, final boolean allowEmpty, final Map<String, String[]> defaultFieldValues)
throws BackendException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = this.connectToDB(user, password, db);
PostgresDocument doc = this.createEmptyDocument(conn, table, name, db);
String nameField = this.getNameField(conn, table, db);
st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
String queryString = String.format("select * from \"%s\".\"%s\" where \"%s\"='%s'",
this.getSchemaName(table, db), this.getPlainTableName(table), nameField, name);
this.logString(queryString, user);
rs = st.executeQuery(queryString);
if (this.getNumRows(rs) == 0) {
if (allowEmpty) {
if (defaultFieldValues != null) {
for (String key : defaultFieldValues.keySet()) {
Field field = doc.getAllFields().get(key);
if (field != null) {
if (field.getType() == FieldType.string || field.getType() == FieldType.text) {
field.setValue(defaultFieldValues.get(key)[0]);
} else if (field.getType() == FieldType.date) {
try {
field.setValue(new SimpleDateFormat("y-M-d")
.parse(defaultFieldValues.get(key)[0]));
} catch (ParseException e) {
throw new BackendException(e);
}
} else if (field.getType() == FieldType.dec) {
field.setValue(Integer.valueOf(defaultFieldValues.get(key)[0]));
} else if (field.getType() == FieldType.num) {
field.setValue(Double.valueOf(defaultFieldValues.get(key)[0]));
}
}
}
}
return doc;
} else {
throw new DocumentNotFoundException(String.format("Document '%s' not found in table '%s.%s'.",
name, this.getSchemaName(table, db), this.getPlainTableName(table)));
}
}
rs.next();
ResultSetMetaData md = rs.getMetaData();
for (int i = 1; i <= md.getColumnCount(); i++) {
Field field = doc.getAllFields().get(md.getColumnName(i));
if (field.getType() == FieldType.string || field.getType() == FieldType.text) {
field.setValue(rs.getString(i));
} else if (field.getType() == FieldType.date) {
field.setValue(rs.getDate(i));
} else if (field.getType() == FieldType.dec) {
field.setValue(rs.getInt(i));
} else if (field.getType() == FieldType.num) {
field.setValue(rs.getDouble(i));
}
}
return doc;
} catch (SQLException e) {
throw new BackendException(e);
} catch (ClassNotFoundException e) {
throw new BackendException(e);
} finally {
DbUtils.closeQuietly(rs);
DbUtils.closeQuietly(st);
DbUtils.closeQuietly(conn);
}
}
From source file:hw.java
public static DefaultTableModel buildTableModel(ResultSet rs) throws SQLException { ResultSetMetaData metaData = rs.getMetaData(); Vector<String> columnNames = new Vector<String>(); int columnCount = metaData.getColumnCount(); for (int column = 1; column <= columnCount; column++) { columnNames.add(metaData.getColumnName(column)); }/* ww w. j a v a2 s. co m*/ Vector<Vector<Object>> data = new Vector<Vector<Object>>(); while (rs.next()) { Vector<Object> vector = new Vector<Object>(); for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) { vector.add(rs.getObject(columnIndex)); } data.add(vector); } return new DefaultTableModel(data, columnNames); }
From source file:it.greenvulcano.gvesb.datahandling.dbo.utils.StandardRowSetBuilder.java
private FieldFormatter[] buildFormatterArray(ResultSetMetaData rsm, Map<String, FieldFormatter> fieldNameToFormatter, Map<String, FieldFormatter> fieldIdToFormatter) throws Exception { FieldFormatter[] fFA = new FieldFormatter[rsm.getColumnCount() + 1]; for (int i = 1; i < fFA.length; i++) { FieldFormatter fF = fieldNameToFormatter.get(rsm.getColumnName(i)); if (fF == null) { fF = fieldIdToFormatter.get("" + i); }/*w w w . ja v a 2 s. co m*/ fFA[i] = fF; } return fFA; }
From source file:org.apache.jmeter.protocol.jdbc.sampler.JDBCSampler.java
/** * Gets a Data object from a ResultSet.//w w w. ja v a2s . c om * * @param rs * ResultSet passed in from a database query * @return a Data object * @throws java.sql.SQLException * @throws UnsupportedEncodingException */ private String getStringFromResultSet(ResultSet rs) throws SQLException, UnsupportedEncodingException { ResultSetMetaData meta = rs.getMetaData(); StrBuilder sb = new StrBuilder(); int numColumns = meta.getColumnCount(); for (int i = 1; i <= numColumns; i++) { sb.append(meta.getColumnName(i)); if (i == numColumns) { sb.append('\n'); } else { sb.append('\t'); } } JMeterVariables jmvars = null; String varnames[] = getVariableNames().split(COMMA); if (varnames.length > 0) { jmvars = getThreadContext().getVariables(); } int j = 0; while (rs.next()) { j++; for (int i = 1; i <= numColumns; i++) { Object o = rs.getObject(i); if (o instanceof byte[]) { o = new String((byte[]) o, ENCODING); } sb.append(o); if (i == numColumns) { sb.append('\n'); } else { sb.append('\t'); } if (jmvars != null && i <= varnames.length) { String name = varnames[i - 1].trim(); if (name.length() > 0) { // Save the value in the variable if present jmvars.put(name + UNDERSCORE + j, o == null ? null : o.toString()); } } } } // Remove any additional values from previous sample for (int i = 0; i < varnames.length; i++) { String name = varnames[i].trim(); if (name.length() > 0 && jmvars != null) { final String varCount = name + "_#"; // $NON-NLS-1$ // Get the previous count String prevCount = jmvars.get(varCount); if (prevCount != null) { int prev = Integer.parseInt(prevCount); for (int n = j + 1; n <= prev; n++) { jmvars.remove(name + UNDERSCORE + n); } } jmvars.put(varCount, Integer.toString(j)); // save the current count } } return sb.toString(); }
From source file:edu.education.ucsb.muster.MusterServlet.java
private String getOutputAsJson(String database, String query, long limit) throws SQLException { // The output string StringBuffer out = new StringBuffer(); // Cache StringBuffer length as needed int len;//from w w w. j a v a 2 s .co m // Database operations DatabaseDefinition db = conf.getDatabase(database); // //register the driver registerDriver(db.driver, db.url); // // Connect to the database Connection connection = DriverManager.getConnection(db.url, db.username, db.password); // // Perform the query PreparedStatement statement = connection.prepareStatement(query); statement.execute(); ResultSet results = statement.getResultSet(); // Get and write the column names ResultSetMetaData meta = results.getMetaData(); int columnCount = meta.getColumnCount(); LinkedList<String> columns = new LinkedList<String>(); for (int i = 1; i < columnCount + 1; i++) { // We're only dealing with JSON, so the column names should be // JavaScript-friendly. columns.add(StringEscapeUtils.escapeJavaScript(meta.getColumnName(i))); } out.append("{\n \"columns\" : [ "); // Add column names in JSON format for (String column : columns) { out.append('"' + column + "\", "); } // remove the trailing ", " and add a line break and close the array len = out.length(); out.delete(len - 2, len); out.append(" ],\n"); // Add column values out.append(" \"results\" : [ \n"); for (int i = 0; i < limit && results.next(); i++) { out.append(rowAsJson(results, columns)); } // remove the trailing ", " len = out.length(); out.delete(len - 2, len); out.append("\n ]\n"); out.append("}"); return out.toString(); }
From source file:CSVWriter.java
protected void writeColumnNames(ResultSetMetaData metadata) throws SQLException { // w ww. ja v a 2 s .com int columnCount = metadata.getColumnCount(); String[] nextLine = new String[columnCount]; for (int i = 0; i < columnCount; i++) { nextLine[i] = metadata.getColumnName(i + 1); } writeNext(nextLine); }
From source file:org.apache.sqoop.connector.jdbc.GenericJdbcFromInitializer.java
@Override public Schema getSchema(InitializerContext context, LinkConfiguration linkConfig, FromJobConfiguration fromJobConfig) { configureJdbcProperties(context.getContext(), linkConfig, fromJobConfig); String schemaName = fromJobConfig.fromJobConfig.tableName; if (schemaName == null) { schemaName = "Query"; } else if (fromJobConfig.fromJobConfig.schemaName != null) { schemaName = fromJobConfig.fromJobConfig.schemaName + "." + schemaName; }/*from w w w .j a v a 2 s . com*/ Schema schema = new Schema(schemaName); ResultSet rs = null; ResultSetMetaData rsmt = null; try { rs = executor.executeQuery(context.getString(GenericJdbcConnectorConstants.CONNECTOR_JDBC_FROM_DATA_SQL) .replace(GenericJdbcConnectorConstants.SQL_CONDITIONS_TOKEN, "1 = 0")); rsmt = rs.getMetaData(); for (int i = 1; i <= rsmt.getColumnCount(); i++) { Column column = SqlTypesUtils.sqlTypeToAbstractType(rsmt.getColumnType(i)); String columnName = rsmt.getColumnName(i); if (columnName == null || columnName.equals("")) { columnName = rsmt.getColumnLabel(i); if (null == columnName) { columnName = "Column " + i; } } column.setName(columnName); schema.addColumn(column); } return schema; } catch (SQLException e) { throw new SqoopException(GenericJdbcConnectorError.GENERIC_JDBC_CONNECTOR_0016, e); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { LOG.info("Ignoring exception while closing ResultSet", e); } } if (executor != null) { executor.close(); } } }