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:ExecuteSQL.java

/**
 * This method attempts to output the contents of a ResultSet in a textual
 * table. It relies on the ResultSetMetaData class, but a fair bit of the
 * code is simple string manipulation.// www . ja v  a 2  s.  c  om
 */
static void printResultsTable(ResultSet rs, OutputStream output) throws SQLException {
    // Set up the output stream
    PrintWriter out = new PrintWriter(output);

    // Get some "meta data" (column names, etc.) about the results
    ResultSetMetaData metadata = rs.getMetaData();

    // Variables to hold important data about the table to be displayed
    int numcols = metadata.getColumnCount(); // how many columns
    String[] labels = new String[numcols]; // the column labels
    int[] colwidths = new int[numcols]; // the width of each
    int[] colpos = new int[numcols]; // start position of each
    int linewidth; // total width of table

    // Figure out how wide the columns are, where each one begins,
    // how wide each row of the table will be, etc.
    linewidth = 1; // for the initial '|'.
    for (int i = 0; i < numcols; i++) { // for each column
        colpos[i] = linewidth; // save its position
        labels[i] = metadata.getColumnLabel(i + 1); // get its label
        // Get the column width. If the db doesn't report one, guess
        // 30 characters. Then check the length of the label, and use
        // it if it is larger than the column width
        int size = metadata.getColumnDisplaySize(i + 1);
        if (size == -1)
            size = 30; // Some drivers return -1...
        if (size > 500)
            size = 30; // Don't allow unreasonable sizes
        int labelsize = labels[i].length();
        if (labelsize > size)
            size = labelsize;
        colwidths[i] = size + 1; // save the column the size
        linewidth += colwidths[i] + 2; // increment total size
    }

    // Create a horizontal divider line we use in the table.
    // Also create a blank line that is the initial value of each
    // line of the table
    StringBuffer divider = new StringBuffer(linewidth);
    StringBuffer blankline = new StringBuffer(linewidth);
    for (int i = 0; i < linewidth; i++) {
        divider.insert(i, '-');
        blankline.insert(i, " ");
    }
    // Put special marks in the divider line at the column positions
    for (int i = 0; i < numcols; i++)
        divider.setCharAt(colpos[i] - 1, '+');
    divider.setCharAt(linewidth - 1, '+');

    // Begin the table output with a divider line
    out.println(divider);

    // The next line of the table contains the column labels.
    // Begin with a blank line, and put the column names and column
    // divider characters "|" into it. overwrite() is defined below.
    StringBuffer line = new StringBuffer(blankline.toString());
    line.setCharAt(0, '|');
    for (int i = 0; i < numcols; i++) {
        int pos = colpos[i] + 1 + (colwidths[i] - labels[i].length()) / 2;
        overwrite(line, pos, labels[i]);
        overwrite(line, colpos[i] + colwidths[i], " |");
    }

    // Then output the line of column labels and another divider
    out.println(line);
    out.println(divider);

    // Now, output the table data. Loop through the ResultSet, using
    // the next() method to get the rows one at a time. Obtain the
    // value of each column with getObject(), and output it, much as
    // we did for the column labels above.
    while (rs.next()) {
        line = new StringBuffer(blankline.toString());
        line.setCharAt(0, '|');
        for (int i = 0; i < numcols; i++) {
            Object value = rs.getObject(i + 1);
            if (value != null)
                overwrite(line, colpos[i] + 1, value.toString().trim());
            overwrite(line, colpos[i] + colwidths[i], " |");
        }
        out.println(line);
    }

    // Finally, end the table with one last divider line.
    out.println(divider);
    out.flush();
}

From source file:net.antidot.sql.model.core.SQLConnector.java

/**
 * Tool : print in output meta data column names from a result set
 * /*from w  w  w .j  a va2s  .c  o m*/
 * @param tablesSet
 * @throws SQLException
 */
public static void printMetaColumnsFromTable(ResultSet tablesSet) throws SQLException {
    if (log.isInfoEnabled())
        log.info("[printMetaColumnsFromTable] ");
    for (int i = 0; i < tablesSet.getMetaData().getColumnCount(); i++) {
        String columnName = tablesSet.getMetaData().getColumnName(i + 1);
        Object value = tablesSet.getObject(i + 1);
        System.out.println(columnName + " = " + value);
    }
}

From source file:com.hangum.tadpole.engine.sql.util.QueryUtils.java

/**
 * query to json/* w w  w .ja v a2 s. c om*/
 * 
 * @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:at.alladin.rmbt.statisticServer.StatisticsResource.java

private static void fillJSON(final String lang, final ResultSet rs, final JSONArray providers)
        throws SQLException, JSONException {
    final ResultSetMetaData metaData = rs.getMetaData();
    final int columnCount = metaData.getColumnCount();
    while (rs.next()) {
        final JSONObject obj = new JSONObject();
        for (int j = 1; j <= columnCount; j++) {
            final String colName = metaData.getColumnName(j);
            Object data = rs.getObject(j);
            if (colName.equals("name") && data == null)
                if (lang != null && lang.equals("de"))
                    data = "Andere Betreiber";
                else
                    data = "Other operators";
            if (colName.equals("shortname") && data == null) {
                if (lang != null && lang.equals("de"))
                    data = "Andere";
                else
                    data = "Others";
            }//w w  w .  j  ava  2  s .co m
            obj.put(colName, data);
        }
        providers.put(obj);
    }
}

From source file:es.tena.foundation.util.POIUtil.java

private static void generate(Writer out, ResultSet rset, String encoding) throws Exception {
    SimpleDateFormat sdfecha = new SimpleDateFormat("dd/MM/yyyy");
    SpreadSheetWriter sw = new SpreadSheetWriter(out);
    sw.beginSheet();//w w w .j a v a  2  s .  com
    ResultSetMetaData rmd = rset.getMetaData();

    HashMap<Integer, String> columnas = new HashMap<Integer, String>();
    //creamos la cabecera del excel
    sw.insertRow(0);
    int numeroColumnas = rmd.getColumnCount();
    for (int numeroColumna = 1; numeroColumna <= numeroColumnas; numeroColumna++) {
        String nombreColumna = rmd.getColumnName(numeroColumna).toUpperCase();
        columnas.put(numeroColumna, rmd.getColumnTypeName(numeroColumna));
        sw.createCell(numeroColumna - 1, nombreColumna);
    }

    sw.endRow();

    //creamos los datos del excel
    int rowNum = 1;
    while (rset.next()) {
        sw.insertRow(rowNum);
        for (int columna = 1; columna <= numeroColumnas; columna++) {
            if (columnas.get(columna).equals("CHAR") || columnas.get(columna).equals("VARCHAR2")) {
                String valor = "";
                valor = rset.getString(columna);
                valor = fixPOICellValue(valor, encoding);
                if (valor == null || valor.toLowerCase().equals("null")) {
                    valor = "";
                }
                sw.createCell(columna - 1, valor);
            } else if (columnas.get(columna).equals("DATE")) {
                Date fecha = new Date();
                fecha = rset.getDate(columna);
                String valor = (fecha != null) ? sdfecha.format(fecha) : null;
                if (valor == null || valor.toLowerCase().equals("null")) {
                    valor = "";
                }
                sw.createCell(columna - 1, valor);
            } else if (columnas.get(columna).equals("NUMBER")) {
                String valor = "";
                valor = rset.getString(columna);
                if (valor == null || valor.toLowerCase().equals("null")) {
                    valor = "";
                }
                sw.createCell(columna - 1, valor);
            } else {
                String valor = "";
                valor = rset.getString(columna);
                valor = fixPOICellValue(valor, encoding);
                if (valor == null || valor.toLowerCase().equals("null")) {
                    valor = "";
                }
                sw.createCell(columna - 1, valor);
            }
        }
        sw.endRow();
        rowNum++;
    }
    sw.endSheet();
}

From source file:mangotiger.sql.SQL.java

public static List<Object> list(final Connection connection, final String sql, final Object[] parameters)
        throws SQLException {
    ResultSet resultSet = null;
    PreparedStatement statement = null;
    try {// w w  w . j a  v a 2s. c o m
        final List<Object> list = new ArrayList<Object>();
        statement = newPreparedStatement(connection, sql, parameters);
        resultSet = statement.executeQuery();
        final ResultSetMetaData metaData = resultSet.getMetaData();
        final int columnCount = metaData.getColumnCount();
        while (resultSet.next()) {
            switch (columnCount) {
            case 1:
                list.add(resultSet.getObject(1));
                break;
            default:
                final Object[] row = new Object[columnCount];
                for (int i = 0; i < columnCount; ++i) {
                    row[i] = resultSet.getObject(i + 1);
                }
                list.add(row);
                break;
            }
        }
        return list;
    } catch (SQLException e) {
        log().error("unable to execute: " + asString(sql, parameters), e);
        throw e;
    } finally {
        close(statement, resultSet);
    }
}

From source file:com.hangum.tadpole.engine.sql.util.QueryUtils.java

/**
 * query to csv//w  w w .j av  a  2 s .com
 * 
 * @param userDB
 * @param strQuery
 * @param listParam
 * @param isAddHead is true add head title
 * @param strDelimiter if delimite is null default comma(,)
 */
@SuppressWarnings("deprecation")
public static String selectToCSV(final UserDBDAO userDB, final String strQuery, final List<Object> listParam,
        final boolean isAddHead, final String strDelimiter) throws Exception {
    final StringWriter stWriter = new StringWriter();

    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();

            char strDel;
            if ("".equals(strDelimiter)) {
                strDel = ',';
            } else if (StringUtils.equalsIgnoreCase("\t", strDelimiter)) {
                strDel = (char) 9;
            } else {
                strDel = strDelimiter.charAt(0);
            }

            CSVWriter csvWriter = new CSVWriter(stWriter, strDel);
            if (isAddHead) {
                String[] arryString = new String[metaData.getColumnCount()];
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    arryString[i - 1] = metaData.getColumnLabel(i);
                }
                csvWriter.writeNext(arryString);
            }

            while (rs.next()) {
                String[] arryString = new String[metaData.getColumnCount()];
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    arryString[i - 1] = rs.getString(i);
                }
                csvWriter.writeNext(arryString);
            }

            return stWriter.toString();
        }
    });

    return stWriter.toString();
}

From source file:com.hangum.tadpole.engine.sql.util.QueryUtils.java

/**
 * query to xml//ww  w  .j a va  2s  .c om
 * 
 * @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:mangotiger.sql.SQL.java

public static Map<String, List<Object>> map(final Connection connection, final String sql,
        final Object[] parameters) throws SQLException {
    ResultSet resultSet = null;
    PreparedStatement statement = null;
    try {//  ww  w .  ja  v  a 2s .c om
        final Map<String, List<Object>> map = new TreeMap<String, List<Object>>();
        statement = newPreparedStatement(connection, sql, parameters);
        resultSet = statement.executeQuery();
        final ResultSetMetaData metaData = resultSet.getMetaData();
        final int columnCount = metaData.getColumnCount();
        for (int i = 0; i < columnCount; ++i) {
            final String columnName = metaData.getColumnName(i);
            map.put(columnName, new ArrayList<Object>());
        }
        while (resultSet.next()) {
            for (int i = 0; i < columnCount; ++i) {
                final String columnName = metaData.getColumnName(i);
                map.get(columnName).add(resultSet.getObject(1));
            }
        }
        return map;
    } catch (SQLException e) {
        log().error("unable to execute: " + asString(sql, parameters), e);
        throw e;
    } finally {
        close(statement, resultSet);
    }
}

From source file:AIR.Common.DB.AbstractDLL.java

public static boolean hasColumn(ResultSet reader, String columnName) throws SQLException {
    ResultSetMetaData metaData = reader.getMetaData();
    for (int i = 1; i <= metaData.getColumnCount(); ++i) {
        if ((metaData.getColumnName(i).equals(columnName))) {
            return true;
        }//from   w w  w  . java  2 s .c om
    }
    return false;
}