Example usage for java.sql ResultSet getMetaData

List of usage examples for java.sql ResultSet getMetaData

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSet
  6. getMetaData

Introduction

In this page you can find the example usage for java.sql ResultSet getMetaData.

Prototype

ResultSetMetaData getMetaData() throws SQLException;

Source Link

Document

Retrieves the number, types and properties of this ResultSet object's columns.

Usage

From source file:io.cloudslang.content.database.services.SQLQueryLobService.java

public static boolean executeSqlQueryLob(SQLInputs sqlInputs) throws Exception {
    if (StringUtils.isEmpty(sqlInputs.getSqlCommand())) {
        throw new Exception("command input is empty.");
    }/*from  w  w  w  .j  a  va  2 s .  c o  m*/
    boolean isLOB = false;
    ConnectionService connectionService = new ConnectionService();
    try (final Connection connection = connectionService.setUpConnection(sqlInputs)) {

        StringBuilder strColumns = new StringBuilder(sqlInputs.getStrColumns());

        connection.setReadOnly(true);
        Statement statement = connection.createStatement(sqlInputs.getResultSetType(),
                sqlInputs.getResultSetConcurrency());
        statement.setQueryTimeout(sqlInputs.getTimeout());

        ResultSet results = statement.executeQuery(sqlInputs.getSqlCommand());
        ResultSetMetaData mtd = results.getMetaData();
        int iNumCols = mtd.getColumnCount();
        for (int i = 1; i <= iNumCols; i++) {
            if (i > 1)
                strColumns.append(sqlInputs.getStrDelim());
            strColumns.append(mtd.getColumnLabel(i));
        }
        sqlInputs.setStrColumns(strColumns.toString());
        int nr = -1;
        while (results.next()) {
            nr++;
            final StringBuilder strRowHolder = new StringBuilder();
            for (int i = 1; i <= iNumCols; i++) {
                if (i > 1)
                    strRowHolder.append(sqlInputs.getStrDelim());
                Object columnObject = results.getObject(i);
                if (columnObject != null) {
                    String value;
                    if (columnObject instanceof java.sql.Clob) {
                        isLOB = true;
                        final File tmpFile = File.createTempFile("CLOB_" + mtd.getColumnLabel(i), ".txt");

                        copyInputStreamToFile(
                                new ReaderInputStream(results.getCharacterStream(i), StandardCharsets.UTF_8),
                                tmpFile);

                        if (sqlInputs.getLRowsFiles().size() == nr) {
                            sqlInputs.getLRowsFiles().add(nr, new ArrayList<String>());
                            sqlInputs.getLRowsNames().add(nr, new ArrayList<String>());
                        }
                        sqlInputs.getLRowsFiles().get(nr).add(tmpFile.getAbsolutePath());
                        sqlInputs.getLRowsNames().get(nr).add(mtd.getColumnLabel(i));
                        value = "(CLOB)...";

                    } else {
                        value = results.getString(i);
                        if (sqlInputs.isNetcool())
                            value = SQLUtils.processNullTerminatedString(value);
                    }
                    strRowHolder.append(value);
                } else
                    strRowHolder.append("null");
            }
            sqlInputs.getLRows().add(strRowHolder.toString());
        }
    }

    return isLOB;
}

From source file:it.greenvulcano.gvesb.datahandling.dbo.utils.ResultSetTransformer.java

public static JSONArray toJSONArray(ResultSet resultSet) throws SQLException {

    JSONArray queryResult = new JSONArray();

    int columns = resultSet.getMetaData().getColumnCount();
    List<String> names = new ArrayList<>(columns);
    for (int i = 1; i <= columns; i++) {
        names.add(resultSet.getMetaData().getColumnLabel(i));
    }//from  w  w  w . j a v  a  2  s  .c o  m

    while (resultSet.next()) {

        JSONObject obj = new JSONObject();
        for (String key : names) {

            Object jsonValue = parseValue(resultSet.getObject(key));

            if (key.contains(".")) {
                String[] hieararchy = key.split("\\.");

                JSONObject child = Optional.ofNullable(obj.optJSONObject(hieararchy[0]))
                        .orElse(new JSONObject());
                child.put(hieararchy[1], Optional.ofNullable(jsonValue).orElse(JSONObject.NULL));

                obj.put(hieararchy[0], child);
            } else {
                obj.put(key, Optional.ofNullable(jsonValue).orElse(JSONObject.NULL));
            }
        }

        queryResult.put(obj);
    }

    return queryResult;

}

From source file:net.sf.sprockets.sql.Statements.java

/**
 * Execute the query, get the long values in the first row of the result set, and close the
 * statement./*from  w w  w. j  ava 2 s .c o m*/
 * 
 * @param stmt
 *            must already have parameters set
 * @return null if the result set is empty
 * @since 1.4.0
 */
public static long[] firstLongRow(PreparedStatement stmt) throws SQLException {
    long[] l = null;
    ResultSet rs = stmt.executeQuery();
    if (rs.next()) {
        l = new long[rs.getMetaData().getColumnCount()];
        for (int i = 0; i < l.length; i++) {
            l[i] = rs.getLong(i + 1);
        }
    }
    stmt.close();
    return l;
}

From source file:jongo.handler.JongoResultSetHandler.java

/**
 * Converts a ResultSet to a Map. Important to note that DATE, TIMESTAMP & TIME objects generate
 * a {@linkplain org.joda.time.DateTime} object using {@linkplain org.joda.time.format.ISODateTimeFormat}.
 * @param resultSet a {@linkplain java.sql.ResultSet}
 * @return a Map with the column names as keys and the values. null if something goes wrong.
 *///from  w  w w  . j  ava 2  s  . c  o  m
public static Map<String, String> resultSetToMap(ResultSet resultSet) {
    Map<String, String> map = new HashMap<String, String>();
    try {
        int columnCount = resultSet.getMetaData().getColumnCount();

        l.trace("Mapping a result set with " + columnCount + " columns to a Map");

        ResultSetMetaData meta = resultSet.getMetaData();
        for (int i = 1; i < columnCount + 1; i++) {
            String colName = meta.getColumnName(i).toLowerCase();
            int colType = meta.getColumnType(i);
            String v = resultSet.getString(i);
            if (colType == Types.DATE) {
                v = new DateTime(resultSet.getDate(i)).toString(dateFTR);
                l.trace("Mapped DATE column " + colName + " with value : " + v);
            } else if (colType == Types.TIMESTAMP) {
                v = new DateTime(resultSet.getTimestamp(i)).toString(dateTimeFTR);
                l.trace("Mapped TIMESTAMP column " + colName + " with value : " + v);
            } else if (colType == Types.TIME) {
                v = new DateTime(resultSet.getTimestamp(i)).toString(timeFTR);
                l.trace("Mapped TIME column " + colName + " with value : " + v);
            } else {
                l.trace("Mapped " + meta.getColumnTypeName(i) + " column " + colName + " with value : " + v);
            }
            map.put(colName, v);
        }
    } catch (SQLException e) {
        l.error("Failed to map ResultSet");
        l.error(e.getMessage());
        return null;
    }

    return map;
}

From source file:com.datamoin.tajo.tpcds.TpcDSTestUtil.java

public static String resultSetToString(ResultSet resultSet) throws SQLException {
    StringBuilder sb = new StringBuilder();
    ResultSetMetaData rsmd = resultSet.getMetaData();
    int numOfColumns = rsmd.getColumnCount();

    for (int i = 1; i <= numOfColumns; i++) {
        if (i > 1)
            sb.append(",");
        String columnName = rsmd.getColumnName(i);
        sb.append(columnName);/*w  w w . ja va 2s. com*/
    }
    sb.append("\n-------------------------------\n");

    while (resultSet.next()) {
        for (int i = 1; i <= numOfColumns; i++) {
            if (i > 1)
                sb.append(",");
            String columnValue = resultSet.getString(i);
            if (resultSet.wasNull()) {
                columnValue = "null";
            }
            sb.append(columnValue);
        }
        sb.append("\n");
    }
    return sb.toString();
}

From source file:io.cloudslang.content.database.services.SQLCommandService.java

public static String executeSqlCommand(final SQLInputs sqlInputs) throws Exception {
    final ConnectionService connectionService = new ConnectionService();
    try (final Connection connection = connectionService.setUpConnection(sqlInputs)) {

        connection.setReadOnly(false);//from   w  w  w .  j  ava 2s .  c o  m

        final String dbType = sqlInputs.getDbType();
        if (ORACLE_DB_TYPE.equalsIgnoreCase(dbType)
                && sqlInputs.getSqlCommand().toLowerCase().contains(DBMS_OUTPUT)) {

            final PreparedStatement preparedStatement = connection.prepareStatement(sqlInputs.getSqlCommand());
            preparedStatement.setQueryTimeout(sqlInputs.getTimeout());
            OracleDbmsOutput oracleDbmsOutput = new OracleDbmsOutput(connection);
            preparedStatement.executeQuery();
            sqlInputs.setIUpdateCount(preparedStatement.getUpdateCount());
            preparedStatement.close();
            final String output = oracleDbmsOutput.getOutput();
            oracleDbmsOutput.close();
            return output;
        } else {
            final Statement statement = connection.createStatement(sqlInputs.getResultSetType(),
                    sqlInputs.getResultSetConcurrency());
            statement.setQueryTimeout(sqlInputs.getTimeout());
            try {
                statement.execute(sqlInputs.getSqlCommand());
            } catch (SQLException e) {
                if (SYBASE_DB_TYPE.equalsIgnoreCase(dbType)) {
                    //during a dump sybase sends back status as exceptions.
                    if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dump")) {
                        return SQLUtils.processDumpException(e);
                    } else if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("load")) {
                        return SQLUtils.processLoadException(e);
                    }
                } else {
                    throw e;
                }
            }

            ResultSet rs = statement.getResultSet();
            if (rs != null) {
                ResultSetMetaData rsMtd = rs.getMetaData();
                if (rsMtd != null) {
                    sqlInputs.getLRows().clear();
                    int colCount = rsMtd.getColumnCount();

                    if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) {
                        while (rs.next()) {
                            if (colCount >= 4) {
                                sqlInputs.getLRows().add(rs.getString(4));
                            }
                        }
                    } else {
                        String delimiter = (StringUtils.isNoneEmpty(sqlInputs.getStrDelim()))
                                ? sqlInputs.getStrDelim()
                                : ",";
                        String strRowHolder;
                        while (rs.next()) {
                            strRowHolder = "";
                            for (int i = 1; i <= colCount; i++) {
                                if (i > 1) {
                                    strRowHolder += delimiter;
                                }
                                strRowHolder += rs.getString(i);
                            }
                            sqlInputs.getLRows().add(strRowHolder);
                        }
                    }
                    rs.close();
                }

            }
            //For sybase, when dbcc command is executed, the result is shown in warning message
            else if (dbType.equalsIgnoreCase(SYBASE_DB_TYPE)
                    && sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) {
                SQLWarning warning = statement.getWarnings();
                while (warning != null) {
                    sqlInputs.getLRows().add(warning.getMessage());
                    warning = warning.getNextWarning();
                }
            }

            sqlInputs.setIUpdateCount(statement.getUpdateCount());
        }
    }
    return "Command completed successfully";
}

From source file:desktop.olayinka.file.transfer.model.DerbyJDBCHelper.java

public static JSONArray convert(ResultSet rs) throws SQLException, JSONException {
    ResultSetMetaData rsmd = rs.getMetaData();
    int numColumns = rsmd.getColumnCount();
    JSONArray jsonArray = new JSONArray();
    while (rs.next()) {
        JSONObject obj = new JSONObject();

        for (int i = 1; i < numColumns + 1; i++) {
            String columnName = rsmd.getColumnName(i);
            obj.put(columnName, rs.getObject(i));
        }/*w ww.java 2 s. co m*/
        jsonArray.put(obj);
    }
    return jsonArray;
}

From source file:Main.java

public static List<Map<String, Object>> map(ResultSet rs) throws SQLException {
    List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
    try {// w ww  . ja va 2s . c o m
        if (rs != null) {
            ResultSetMetaData meta = rs.getMetaData();
            int numColumns = meta.getColumnCount();
            while (rs.next()) {
                Map<String, Object> row = new HashMap<String, Object>();
                for (int i = 1; i <= numColumns; ++i) {
                    String name = meta.getColumnName(i);
                    Object value = rs.getObject(i);
                    row.put(name, value);
                }
                results.add(row);
            }
        }
    } finally {
        close(rs);
    }
    return results;
}

From source file:com.hangum.tadpole.commons.sql.util.SQLUtil.java

/**
 * metadata     .// w w  w.  ja v a 2 s .co  m
 * 
 * @param rs
 * @return index? 
 */
public static HashMap<Integer, String> mataDataToMap(ResultSet rs) throws Exception {
    HashMap<Integer, String> map = new HashMap<Integer, String>();

    ResultSetMetaData rsm = rs.getMetaData();
    int columnCount = rsm.getColumnCount();
    for (int i = 0; i < columnCount; i++) {
        map.put(i, rsm.getColumnLabel(i + 1));
    }

    return map;
}

From source file:org.apache.drill.jdbc.test.JdbcAssert.java

private static List<Ord<String>> columnLabels(ResultSet resultSet) throws SQLException {
    int n = resultSet.getMetaData().getColumnCount();
    List<Ord<String>> columns = new ArrayList<>();
    for (int i = 1; i <= n; i++) {
        columns.add(Ord.of(i, resultSet.getMetaData().getColumnLabel(i)));
    }//from   ww  w. j a v  a  2  s  . c o  m
    return columns;
}