Example usage for java.sql ResultSetMetaData getColumnName

List of usage examples for java.sql ResultSetMetaData getColumnName

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSetMetaData
  6. getColumnName

Introduction

In this page you can find the example usage for java.sql ResultSetMetaData getColumnName.

Prototype

String getColumnName(int column) throws SQLException;

Source Link

Document

Get the designated column's name.

Usage

From source file:com.tonbeller.jpivot.mondrian.script.ScriptableMondrianDrillThroughTableModel.java

/**
 * execute sql query/*www  .ja  va2  s .  co m*/
 * @throws Exception
 */
private void executeQuery() {
    Connection con = null;
    try {
        InputStream catExtIs = ScriptableMondrianDrillThroughTableModel.class.getClassLoader()
                .getResourceAsStream("/" + catalogExtension);
        if (catExtIs != null) {
            Digester catExtDigester = new Digester();
            catExtDigester.push(this);
            catExtDigester.addSetProperties("extension");
            catExtDigester.addObjectCreate("extension/script",
                    "com.tonbeller.jpivot.mondrian.script.ScriptColumn");
            catExtDigester.addSetProperties("extension/script");
            catExtDigester.addSetNext("extension/script", "addScript");
            catExtDigester.parse(catExtIs);

            URL scriptsBaseURL = Thread.currentThread().getContextClassLoader().getResource(scriptRootUrl);
            scriptEngine = new GroovyScriptEngine(new URL[] { scriptsBaseURL });
        }
        con = getConnection();
        Statement s = con.createStatement();
        s.setMaxRows(maxResults);
        ResultSet rs = s.executeQuery(sql);
        ResultSetMetaData md = rs.getMetaData();
        int numCols = md.getColumnCount();
        List columnTitlesList = new ArrayList();
        // set column headings
        for (int i = 0; i < numCols; i++) {
            //   columns are 1 based
            columnTitlesList.add(i, md.getColumnName(i + 1));
        }
        // loop on script columns
        for (ListIterator sIt = scripts.listIterator(); sIt.hasNext();) {
            final ScriptColumn sc = (ScriptColumn) sIt.next();
            columnTitlesList.add(sc.getPosition() - 1, sc.getTitle());
        }
        columnTitles = (String[]) columnTitlesList.toArray(new String[0]);
        // loop through rows
        List tempRows = new ArrayList();
        Map scriptInput = new HashMap();
        Binding binding = new Binding();
        while (rs.next()) {
            List rowList = new ArrayList();
            scriptInput.clear();
            // loop on columns, 1 based
            for (int i = 0; i < numCols; i++) {
                rowList.add(i, rs.getObject(i + 1));
                scriptInput.put(columnTitles[i], rs.getObject(i + 1));
            }
            binding.setVariable("input", scriptInput);
            // loop on script columns
            for (ListIterator sIt = scripts.listIterator(); sIt.hasNext();) {
                final ScriptColumn sc = (ScriptColumn) sIt.next();
                scriptEngine.run(sc.getFile(), binding);
                final Object output = binding.getVariable("output");
                if (output instanceof Map) {
                    Map outMap = (Map) output;
                    rowList.add(sc.getPosition() - 1,
                            new DefaultCell((String) outMap.get("URL"), (String) outMap.get("Value")));
                } else if (output instanceof String) {
                    rowList.add(sc.getPosition() - 1, (String) output);
                } else {
                    throw new Exception("Unknown groovy script return type (not a Map nor String).");
                }
            }
            tempRows.add(new DefaultTableRow(rowList.toArray()));
        }
        rs.close();
        rows = (TableRow[]) tempRows.toArray(new TableRow[0]);
    } catch (Exception e) {
        e.printStackTrace();
        logger.error("?", e);
        // problem occured, set table model to zero size
        rows = new TableRow[1];
        columnTitles = new String[1];
        columnTitles[0] = "An error occured";
        Object[] row = new Object[1];
        row[0] = e.toString();
        rows[0] = new DefaultTableRow(row);
        ready = false;
        return;
    } finally {
        try {
            con.close();
        } catch (Exception e1) {
            // ignore
        }
    }
    ready = true;
}

From source file:org.apache.hadoop.chukwa.extraction.engine.datasource.database.DatabaseDS.java

@edu.umd.cs.findbugs.annotations.SuppressWarnings(value = "SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE", justification = "Dynamic based upon tables in the database")
public SearchResult search(SearchResult result, String cluster, String dataSource, long t0, long t1,
        String filter, Token token) throws DataSourceException {
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd kk:mm:ss");
    String timeField = null;/*from  ww  w  . j  a  v a 2 s  .com*/
    TreeMap<Long, List<Record>> records = result.getRecords();

    if (cluster == null) {
        cluster = "demo";
    }

    if (dataSource.equalsIgnoreCase("MRJob")) {
        timeField = "LAUNCH_TIME";
    } else if (dataSource.equalsIgnoreCase("HodJob")) {
        timeField = "StartTime";
    } else {
        timeField = "timestamp";
    }
    String startS = formatter.format(t0);
    String endS = formatter.format(t1);
    Statement stmt = null;
    ResultSet rs = null;
    try {
        String dateclause = timeField + " >= '" + startS + "' and " + timeField + " <= '" + endS + "'";

        // ClusterConfig cc = new ClusterConfig();
        String jdbc = ""; // cc.getURL(cluster);

        Connection conn = org.apache.hadoop.chukwa.util.DriverManagerUtil.getConnection(jdbc);

        stmt = conn.createStatement();
        String query = "";
        query = "select * from " + dataSource + " where " + dateclause + ";";
        rs = stmt.executeQuery(query);
        if (stmt.execute(query)) {
            rs = stmt.getResultSet();
            ResultSetMetaData rmeta = rs.getMetaData();
            int col = rmeta.getColumnCount();
            while (rs.next()) {
                ChukwaRecord event = new ChukwaRecord();
                StringBuilder cell = new StringBuilder();
                ;
                long timestamp = 0;

                for (int i = 1; i < col; i++) {
                    String value = rs.getString(i);
                    if (value != null) {
                        cell.append(" ");
                        cell.append(rmeta.getColumnName(i));
                        cell.append(":");
                        cell.append(value);
                    }
                    if (rmeta.getColumnName(i).equals(timeField)) {
                        timestamp = rs.getLong(i);
                        event.setTime(timestamp);
                    }
                }
                boolean isValid = false;
                if (filter == null || filter.equals("")) {
                    isValid = true;
                } else if (cell.indexOf(filter) > 0) {
                    isValid = true;
                }
                if (!isValid) {
                    continue;
                }

                event.add(Record.bodyField, cell.toString());
                event.add(Record.sourceField, cluster + "." + dataSource);
                if (records.containsKey(timestamp)) {
                    records.get(timestamp).add(event);
                } else {
                    List<Record> list = new LinkedList<Record>();
                    list.add(event);
                    records.put(event.getTime(), list);
                }
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
        throw new DataSourceException(e);
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException sqlEx) {
                log.debug(ExceptionUtil.getStackTrace(sqlEx));
            }
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException sqlEx) {
                log.debug(ExceptionUtil.getStackTrace(sqlEx));
            }
            stmt = null;
        }
    }
    return result;
}

From source file:CSVTools.CsvToolsApi.java

public void writeResultSetIntoCSVFile(CachedRowSet resultSet, String path) {

    PrintWriter csvWriter = null;
    try {/*www .java 2s  .  co  m*/
        csvWriter = new PrintWriter(new File(path));

        ResultSetMetaData meta = resultSet.getMetaData();
        int numberOfColumns = meta.getColumnCount();
        String dataHeaders = "\"" + meta.getColumnName(1) + "\"";
        for (int i = 2; i < numberOfColumns + 1; i++) {
            dataHeaders += ",\"" + meta.getColumnName(i) + "\"";
        }
        csvWriter.println(dataHeaders);
        resultSet.beforeFirst();
        while (resultSet.next()) {
            String row = "\"" + resultSet.getString(1) + "\"";
            for (int i = 2; i < numberOfColumns + 1; i++) {
                row += ",\"" + resultSet.getString(i) + "\"";
            }
            csvWriter.println(row);
        }
        csvWriter.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

From source file:org.cloudgraph.rdb.filter.RDBStatementExecutor.java

@Override
public List<PropertyPair> fetchRow(PlasmaType type, StringBuilder sql) {
    List<PropertyPair> result = new ArrayList<PropertyPair>();
    PreparedStatement statement = null;
    ResultSet rs = null;/*from   ww w .jav  a2  s.c  om*/
    try {
        if (log.isDebugEnabled()) {
            log.debug("fetch: " + sql.toString());
        }
        statement = con.prepareStatement(sql.toString(), ResultSet.TYPE_FORWARD_ONLY, /*
                                                                                       * ResultSet
                                                                                       * .
                                                                                       * TYPE_SCROLL_INSENSITIVE
                                                                                       * ,
                                                                                       */
                ResultSet.CONCUR_READ_ONLY);

        statement.execute();
        rs = statement.getResultSet();
        ResultSetMetaData rsMeta = rs.getMetaData();
        int numcols = rsMeta.getColumnCount();
        int count = 0;
        while (rs.next()) {
            for (int i = 1; i <= numcols; i++) {
                String columnName = rsMeta.getColumnName(i);
                int columnType = rsMeta.getColumnType(i);
                PlasmaProperty prop = (PlasmaProperty) type.getProperty(columnName);
                PlasmaProperty valueProp = prop;
                while (!valueProp.getType().isDataType()) {
                    valueProp = this.statementUtil.getOppositePriKeyProperty(valueProp);
                }
                Object value = converter.fromJDBCDataType(rs, i, columnType, valueProp);
                if (value != null) {
                    PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value);
                    if (!valueProp.equals(prop))
                        pair.setValueProp(valueProp);
                    result.add(pair);
                }
            }
            count++;
        }
        if (log.isDebugEnabled())
            log.debug("returned " + count + " results");
    } catch (Throwable t) {
        throw new DataAccessException(t);
    } finally {
        try {
            if (rs != null)
                rs.close();
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
    }
    return result;
}

From source file:org.cloudgraph.rdb.filter.RDBStatementExecutor.java

@Override
public Map<String, PropertyPair> fetchRowMap(PlasmaType type, StringBuilder sql) {
    Map<String, PropertyPair> result = new HashMap<String, PropertyPair>();
    PreparedStatement statement = null;
    ResultSet rs = null;//from ww  w .  j  a  v a 2s.  co  m
    try {
        if (log.isDebugEnabled()) {
            log.debug("fetch: " + sql.toString());
        }

        statement = con.prepareStatement(sql.toString(), ResultSet.TYPE_FORWARD_ONLY, /*
                                                                                       * ResultSet
                                                                                       * .
                                                                                       * TYPE_SCROLL_INSENSITIVE
                                                                                       * ,
                                                                                       */
                ResultSet.CONCUR_READ_ONLY);

        statement.execute();
        rs = statement.getResultSet();
        ResultSetMetaData rsMeta = rs.getMetaData();
        int numcols = rsMeta.getColumnCount();
        int count = 0;
        while (rs.next()) {
            for (int i = 1; i <= numcols; i++) {
                String columnName = rsMeta.getColumnName(i);
                int columnType = rsMeta.getColumnType(i);
                PlasmaProperty prop = (PlasmaProperty) type.getProperty(columnName);
                PlasmaProperty valueProp = prop;
                while (!valueProp.getType().isDataType()) {
                    valueProp = this.statementUtil.getOppositePriKeyProperty(valueProp);
                }
                Object value = converter.fromJDBCDataType(rs, i, columnType, valueProp);
                if (value != null) {
                    PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value);
                    if (!valueProp.equals(prop))
                        pair.setValueProp(valueProp);
                    result.put(prop.getName(), pair);
                }
            }
            count++;
        }
        if (log.isDebugEnabled())
            log.debug("returned " + count + " results");
    } catch (Throwable t) {
        throw new DataAccessException(t);
    } finally {
        try {
            if (rs != null)
                rs.close();
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
    }
    return result;
}

From source file:com.tesora.dve.sql.util.JdbcConnectionResourceResponse.java

private void assertEqualProxyConnMetadata(String cntxt, ProxyConnectionResourceResponse pcrr) throws Throwable {
    ResultSetMetaData rsmd = results.getMetaData();
    ColumnSet sysColumns = pcrr.getColumns();
    assertEquals(cntxt + " mismatched column set width", rsmd.getColumnCount(),
            sysColumns.getColumnList().size());
    List<ColumnMetadata> sysCols = sysColumns.getColumnList();
    for (int i = 0; i < rsmd.getColumnCount(); i++) {
        ColumnMetadata sc = sysCols.get(i);
        String colcntxt = cntxt + " column " + sc.getAliasName();
        // still don't handle non column labels right
        assertEquals(colcntxt + " mismatched column name", rsmd.getColumnName(i + 1), sc.getName());
        assertEquals(colcntxt + " mismatched column label", rsmd.getColumnLabel(i + 1), sc.getAliasName());
        if (rsmd.getColumnType(i + 1) != sc.getDataType()) {
            // emit names - easier to read
            fail(colcntxt + " mismatched column type.  Expected " + rsmd.getColumnTypeName(i + 1) + " ("
                    + rsmd.getColumnType(i + 1) + ") but found " + sc.getTypeName() + " (" + sc.getDataType()
                    + ")");
        }/*  w  w  w .  jav  a  2s  .c  o  m*/
    }
}

From source file:org.apache.nifi.processors.standard.util.TestJdbcCommon.java

@Test
public void testSignedIntShouldBeInt() throws SQLException, IllegalArgumentException, IllegalAccessException {
    final ResultSetMetaData metadata = mock(ResultSetMetaData.class);
    when(metadata.getColumnCount()).thenReturn(1);
    when(metadata.getColumnType(1)).thenReturn(Types.INTEGER);
    when(metadata.isSigned(1)).thenReturn(true);
    when(metadata.getColumnName(1)).thenReturn("Col1");
    when(metadata.getTableName(1)).thenReturn("Table1");

    final ResultSet rs = mock(ResultSet.class);
    when(rs.getMetaData()).thenReturn(metadata);

    Schema schema = JdbcCommon.createSchema(rs);
    Assert.assertNotNull(schema);//w  w w. j  ava  2 s  . c  o m

    Schema.Field field = schema.getField("Col1");
    Schema fieldSchema = field.schema();
    Assert.assertEquals(2, fieldSchema.getTypes().size());

    boolean foundIntSchema = false;
    boolean foundNullSchema = false;

    for (Schema type : fieldSchema.getTypes()) {
        if (type.getType().equals(Schema.Type.INT)) {
            foundIntSchema = true;
        } else if (type.getType().equals(Schema.Type.NULL)) {
            foundNullSchema = true;
        }
    }

    assertTrue(foundIntSchema);
    assertTrue(foundNullSchema);
}

From source file:com.example.admin.parkingappfinal.MainActivity.java

public void update() {
    ImageView a1on = (ImageView) findViewById(R.id.imageView);
    ImageView a1off = (ImageView) findViewById(R.id.A1off);
    ImageView a2on = (ImageView) findViewById(R.id.A2on);
    ImageView a2off = (ImageView) findViewById(R.id.A2off);
    ImageView b1on = (ImageView) findViewById(R.id.B1on);
    ImageView b1off = (ImageView) findViewById(R.id.B1off);
    try {/*from w  w  w  . j a v a 2 s  .co m*/
        StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
        StrictMode.setThreadPolicy(policy);
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection(url, user, pass);

        String result = "Database connection success\n";
        System.out.println("We made it here");
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("select * from Lot1");
        ResultSetMetaData rsmd = rs.getMetaData();
        //System.out.println(rs.getString(1));
        //System.out.println(rs.getString(2));
        //System.out.println(rs.getString(3));
        while (rs.next()) {
            //System.out.println(rs.getString(1));
            //System.out.println(rs.getString(2));
            //System.out.println(rs.getString(3));
            //result += rsmd.getColumnName(1) + ": " + rs.getString(1) + "\n";
            result += rsmd.getColumnName(2) + ":" + rs.getString(2);
            result += rsmd.getColumnName(3) + ":" + rs.getString(3);
        }
        //System.out.println(result);
        for (int i = 0; i < 3; i++) {
            spaces[i] = result.substring(result.indexOf(":") + 1, result.indexOf(":") + 3);
            String newResult = result.substring(result.indexOf(":") + 3);
            System.out.println(newResult);
            filled[i] = Integer
                    .parseInt(newResult.substring(newResult.indexOf(":") + 1, newResult.indexOf(":") + 2));
            result = newResult.substring(newResult.indexOf(":") + 2);
        }
        System.out.println(Arrays.toString(spaces));
        System.out.println(Arrays.toString(filled));
        //tv.setText(result);
        if (filled[0] == 1) {
            a1on.bringToFront();
        }
        if (filled[0] == 0) {
            a1off.bringToFront();
        }
        if (filled[1] == 1) {
            a2on.bringToFront();
        }
        if (filled[1] == 0) {
            a2off.bringToFront();
        }
        if (filled[2] == 1) {
            b1on.bringToFront();
        }
        if (filled[2] == 0) {
            b1off.bringToFront();
        }
    } catch (Exception e) {
        e.printStackTrace();
        //tv.setText(e.toString());
    }
}

From source file:Statement.Statement.java

private void loadView() {
    fieldData = new Vector<>();
    fieldNames = new Vector();
    //Display Revenue
    try {/* w  ww. j ava 2s  . c om*/
        PreparedStatement st = cnn
                .prepareStatement("SELECT TypeName,Quantity FROM Revenue where ShopID = ? and Date = ?");
        st.setString(1, code);
        st.setString(2, date);
        ResultSet rs = st.executeQuery();

        ResultSetMetaData meta = rs.getMetaData();
        for (int i = 1; i <= meta.getColumnCount(); i++) {
            fieldNames.add(meta.getColumnName(i));
        }
        while (rs.next()) {
            Vector tmp = new Vector();
            tmp.add(rs.getString(1));
            tmp.add(rs.getInt(2));
            fieldData.add(tmp);
        }
        model = new DefaultTableModel(fieldData, fieldNames);
        tbl.setModel(model);
    } catch (Exception e) {
    }
}

From source file:org.apache.nifi.processors.standard.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
 *//*ww  w .j  av a 2s  . 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) || (meta.getPrecision(i) > 0 && meta.getPrecision(i) <= MAX_DIGITS_IN_INT)) {
                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:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().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) + " / "
                    + meta.getColumnTypeName(i) + " (table: " + tableName + ", column: " + columnName
                    + ") cannot be converted to Avro type");
        }
    }

    return builder.endRecord();
}