Example usage for java.sql ResultSetMetaData getColumnDisplaySize

List of usage examples for java.sql ResultSetMetaData getColumnDisplaySize

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

Introduction

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

Prototype

int getColumnDisplaySize(int column) throws SQLException;

Source Link

Document

Indicates the designated column's normal maximum width in characters.

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.//from   w w w  .j  a va2 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:com.bigdata.etl.util.DwUtil.java

public static void bulkInsert(String tableName, List<Map<String, String>> lst) {

    ResultSet rs = null;/*from ww  w  .j  av a  2s . c  om*/
    java.sql.Statement stmt = null;

    try (java.sql.Connection conn = DataSource.getConnection()) {
        stmt = conn.createStatement();
        rs = stmt.executeQuery("select top 0 * from " + tableName);
        try (SQLServerBulkCopy bulk = new SQLServerBulkCopy(url + "user=" + user + ";password=" + password)) {
            SQLServerBulkCopyOptions sqlsbc = new SQLServerBulkCopyOptions();
            sqlsbc.setBulkCopyTimeout(60 * 60 * 1000);
            bulk.setBulkCopyOptions(sqlsbc);
            bulk.setDestinationTableName(tableName);
            ResultSetMetaData rsmd = rs.getMetaData();
            if (lst == null) {
                return;
            }
            // System.out.println(LocalTime.now() + " "+Thread.currentThread().getId()+" "+lst.size());
            try (CachedRowSetImpl x = new CachedRowSetImpl()) {
                x.populate(rs);
                for (int k = 0; k < lst.size(); k++) {
                    Map<String, String> map = lst.get(k);
                    x.last();
                    x.moveToInsertRow();
                    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                        String name = rsmd.getColumnName(i).toUpperCase();
                        int type = rsmd.getColumnType(i);//package java.sql.Type?

                        try {
                            switch (type) {
                            case Types.VARCHAR:
                            case Types.NVARCHAR:
                                int len = rsmd.getColumnDisplaySize(i);
                                String v = map.get(name);
                                if (map.containsKey(name)) {
                                    x.updateString(i, v.length() > len ? v.substring(0, len) : v);
                                } else {
                                    x.updateNull(i);
                                }
                                break;
                            case Types.BIGINT:
                                if (map.containsKey(name) && map.get(name).matches("\\d{1,}")) {
                                    x.updateLong(i, Long.valueOf(map.get(name)));
                                } else {
                                    //   x.updateLong(i, 0);
                                    x.updateNull(i);
                                }
                                break;
                            case Types.FLOAT:
                                if (map.containsKey(name) && map.get(name).matches("([+-]?)\\d*\\.\\d+$")) {
                                    x.updateFloat(i, Float.valueOf(map.get(name)));
                                } else {
                                    x.updateNull(i);

                                }
                                break;
                            case Types.DOUBLE:
                                if (map.containsKey(name) && map.get(name).trim().length() > 0
                                        && StringUtils.isNumeric(map.get(name))) {
                                    x.updateDouble(i, Double.valueOf(map.get(name)));
                                } else {
                                    x.updateNull(i);
                                }
                                break;

                            case Types.INTEGER:
                                if (map.containsKey(name) && map.get(name).matches("\\d{1,}")) {
                                    x.updateInt(i, Integer.valueOf(map.get(name)));
                                } else {
                                    x.updateNull(i);
                                }
                                break;

                            default:
                                throw new RuntimeException("? " + type);
                            }
                            /*
                            if(map.containsKey("SYS_TELECOM"))
                            System.err.println(map.get("SYS_TELECOM"));
                             */
                        } catch (RuntimeException | SQLException e) {
                            Logger.getLogger(DwUtil.class.getName()).log(Level.SEVERE,
                                    "? name=" + name + " v=" + map.get(name), e);
                        }

                    }
                    x.insertRow();
                    x.moveToCurrentRow();
                    //x.acceptChanges();
                }

                long start = System.currentTimeMillis();
                bulk.writeToServer(x);
                long end = System.currentTimeMillis();
                System.out.println(LocalTime.now() + " " + Thread.currentThread().getId() + " "
                        + (end - start) + "ms" + " " + x.size());
            }
        }

    } catch (SQLException e) {
        Logger.getLogger(DwUtil.class.getName()).log(Level.SEVERE, null, e);
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(DwUtil.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
}

From source file:org.apache.hadoop.sqoop.util.ResultSetPrinter.java

/**
 * Format the contents of the ResultSet into something that could be printed
 * neatly; the results are appended to the supplied StringBuilder.
 *//*  ww  w  .j  a v  a 2  s .  co  m*/
public final void printResultSet(OutputStream os, ResultSet results) throws IOException {
    try {
        StringBuilder sbNames = new StringBuilder();
        int cols = results.getMetaData().getColumnCount();

        int[] colWidths = new int[cols];
        ResultSetMetaData metadata = results.getMetaData();
        for (int i = 1; i < cols + 1; i++) {
            String colName = metadata.getColumnName(i);
            colWidths[i - 1] = Math.min(metadata.getColumnDisplaySize(i), MAX_COL_WIDTH);
            if (colName == null || colName.equals("")) {
                colName = metadata.getColumnLabel(i) + "*";
            }
            printPadded(sbNames, colName, colWidths[i - 1]);
            sbNames.append(COL_SEPARATOR);
        }
        sbNames.append('\n');

        StringBuilder sbPad = new StringBuilder();
        for (int i = 0; i < cols; i++) {
            for (int j = 0; j < COL_SEPARATOR.length() + colWidths[i]; j++) {
                sbPad.append('-');
            }
        }
        sbPad.append('\n');

        sendToStream(sbPad, os);
        sendToStream(sbNames, os);
        sendToStream(sbPad, os);

        while (results.next()) {
            StringBuilder sb = new StringBuilder();
            for (int i = 1; i < cols + 1; i++) {
                printPadded(sb, results.getString(i), colWidths[i - 1]);
                sb.append(COL_SEPARATOR);
            }
            sb.append('\n');
            sendToStream(sb, os);
        }

        sendToStream(sbPad, os);
    } catch (SQLException sqlException) {
        LOG.error("Error reading from database: " + sqlException.toString());
    }
}

From source file:com.cloudera.sqoop.util.ResultSetPrinter.java

/**
 * Format the contents of the ResultSet into something that could be printed
 * neatly; the results are appended to the supplied StringBuilder.
 *///from   w w w.j a  v  a2 s .c o  m
public final void printResultSet(PrintWriter pw, ResultSet results) throws IOException {
    try {
        StringBuilder sbNames = new StringBuilder();
        int cols = results.getMetaData().getColumnCount();

        int[] colWidths = new int[cols];
        ResultSetMetaData metadata = results.getMetaData();
        sbNames.append(LEFT_BORDER);
        for (int i = 1; i < cols + 1; i++) {
            String colName = metadata.getColumnName(i);
            colWidths[i - 1] = Math.min(metadata.getColumnDisplaySize(i), MAX_COL_WIDTH);
            if (colName == null || colName.equals("")) {
                colName = metadata.getColumnLabel(i) + "*";
            }
            printPadded(sbNames, colName, colWidths[i - 1]);
            sbNames.append(COL_SEPARATOR);
        }
        sbNames.append('\n');

        StringBuilder sbPad = new StringBuilder();
        for (int i = 0; i < cols; i++) {
            for (int j = 0; j < COL_SEPARATOR.length() + colWidths[i]; j++) {
                sbPad.append('-');
            }
        }
        sbPad.append('-');
        sbPad.append('\n');

        pw.print(sbPad.toString());
        pw.print(sbNames.toString());
        pw.print(sbPad.toString());

        while (results.next()) {
            StringBuilder sb = new StringBuilder();
            sb.append(LEFT_BORDER);
            for (int i = 1; i < cols + 1; i++) {
                printPadded(sb, results.getString(i), colWidths[i - 1]);
                sb.append(COL_SEPARATOR);
            }
            sb.append('\n');
            pw.print(sb.toString());
        }

        pw.print(sbPad.toString());
    } catch (SQLException sqlException) {
        LOG.error("Error reading from database: " + StringUtils.stringifyException(sqlException));
    }
}

From source file:jongo.handler.ResultSetMetaDataHandler.java

@Override
public List<Row> handle(ResultSet rs) throws SQLException {
    List<Row> results = new ArrayList<Row>();
    int rowId = 0;
    ResultSetMetaData metaData = rs.getMetaData();
    Map<String, String> map = null;
    for (int i = 1; i <= metaData.getColumnCount(); i++) {
        map = new HashMap<String, String>(2);
        map.put("tableName", metaData.getTableName(i));
        map.put("columnName", metaData.getColumnName(i));
        map.put("columnLabel", metaData.getColumnLabel(i));
        map.put("columnType", metaData.getColumnTypeName(i));
        map.put("columnSize", String.valueOf(metaData.getColumnDisplaySize(i)));
        map.put("precision", String.valueOf(metaData.getPrecision(i)));
        map.put("scale", String.valueOf(metaData.getScale(i)));

        //            map.put("catalog_name", metaData.getCatalogName(i));
        //            map.put("column_class_name", metaData.getColumnClassName(i));
        //            map.put("schema_name", metaData.getSchemaName(i));
        //            map.put("column_type", String.valueOf(metaData.getColumnType(i)));

        if (map != null)
            results.add(new Row(rowId++, map));
    }// w ww. java  2 s  .  c  om
    return results;
}

From source file:org.apache.calcite.avatica.jdbc.JdbcMeta.java

/**
 * Convert from JDBC metadata to Avatica columns.
 *//*from  w  w w .j  a  v  a  2 s .  c  o  m*/
protected static List<ColumnMetaData> columns(ResultSetMetaData metaData) throws SQLException {
    if (metaData == null) {
        return Collections.emptyList();
    }
    final List<ColumnMetaData> columns = new ArrayList<>();
    for (int i = 1; i <= metaData.getColumnCount(); i++) {
        final SqlType sqlType = SqlType.valueOf(metaData.getColumnType(i));
        final ColumnMetaData.Rep rep = ColumnMetaData.Rep.of(sqlType.internal);
        ColumnMetaData.AvaticaType t = ColumnMetaData.scalar(metaData.getColumnType(i),
                metaData.getColumnTypeName(i), rep);
        ColumnMetaData md = new ColumnMetaData(i - 1, metaData.isAutoIncrement(i), metaData.isCaseSensitive(i),
                metaData.isSearchable(i), metaData.isCurrency(i), metaData.isNullable(i), metaData.isSigned(i),
                metaData.getColumnDisplaySize(i), metaData.getColumnLabel(i), metaData.getColumnName(i),
                metaData.getSchemaName(i), metaData.getPrecision(i), metaData.getScale(i),
                metaData.getTableName(i), metaData.getCatalogName(i), t, metaData.isReadOnly(i),
                metaData.isWritable(i), metaData.isDefinitelyWritable(i), metaData.getColumnClassName(i));
        columns.add(md);
    }
    return columns;
}

From source file:org.apache.kylin.rest.util.HiveReroute.java

private void extractColumnMetadata(ResultSet resultSet, List<SelectedColumnMeta> columnMetas)
        throws SQLException {
    ResultSetMetaData metaData = null;
    int columnCount = 0;

    metaData = resultSet.getMetaData();//  w  w w .  j av  a 2 s.  c om
    columnCount = metaData.getColumnCount();

    // fill in selected column meta
    for (int i = 1; i <= columnCount; ++i) {
        columnMetas.add(new SelectedColumnMeta(metaData.isAutoIncrement(i), metaData.isCaseSensitive(i), false,
                metaData.isCurrency(i), metaData.isNullable(i), false, metaData.getColumnDisplaySize(i),
                metaData.getColumnLabel(i), metaData.getColumnName(i), null, null, null,
                metaData.getPrecision(i), metaData.getScale(i), metaData.getColumnType(i),
                metaData.getColumnTypeName(i), metaData.isReadOnly(i), false, false));
    }
}

From source file:org.apache.kylin.rest.adhoc.AdHocRunnerJdbcImpl.java

@Override
public void executeQuery(String query, List<List<String>> results, List<SelectedColumnMeta> columnMetas)
        throws Exception {
    Statement statement = null;// w  w w . j  a v  a 2 s . c om
    Connection connection = this.getConnection();
    ResultSet resultSet = null;

    try {
        statement = connection.createStatement();
        resultSet = statement.executeQuery(query);
        extractResults(resultSet, results);
    } catch (SQLException sqlException) {
        throw sqlException;
    }

    //extract column metadata
    ResultSetMetaData metaData = null;
    int columnCount = 0;
    try {
        metaData = resultSet.getMetaData();
        columnCount = metaData.getColumnCount();

        // fill in selected column meta
        for (int i = 1; i <= columnCount; ++i) {
            columnMetas.add(new SelectedColumnMeta(metaData.isAutoIncrement(i), metaData.isCaseSensitive(i),
                    false, metaData.isCurrency(i), metaData.isNullable(i), false,
                    metaData.getColumnDisplaySize(i), metaData.getColumnLabel(i), metaData.getColumnName(i),
                    null, null, null, metaData.getPrecision(i), metaData.getScale(i), metaData.getColumnType(i),
                    metaData.getColumnTypeName(i), metaData.isReadOnly(i), false, false));
        }

    } catch (SQLException sqlException) {
        throw sqlException;
    }

    closeConnection(connection);
}

From source file:ViewDB.java

 /**
 * Constructs the data panel.//from  w w w .j  a  v a 2 s  .  c  o m
 * @param rs the result set whose contents this panel displays
 */
public DataPanel(RowSet rs) throws SQLException
{
   fields = new ArrayList<JTextField>();
   setLayout(new GridBagLayout());
   GridBagConstraints gbc = new GridBagConstraints();
   gbc.gridwidth = 1;
   gbc.gridheight = 1;

   ResultSetMetaData rsmd = rs.getMetaData();
   for (int i = 1; i <= rsmd.getColumnCount(); i++)
   {
      gbc.gridy = i - 1;

      String columnName = rsmd.getColumnLabel(i);
      gbc.gridx = 0;
      gbc.anchor = GridBagConstraints.EAST;
      add(new JLabel(columnName), gbc);

      int columnWidth = rsmd.getColumnDisplaySize(i);
      JTextField tb = new JTextField(columnWidth);
      if (!rsmd.getColumnClassName(i).equals("java.lang.String"))
         tb.setEditable(false);
               
      fields.add(tb);

      gbc.gridx = 1;
      gbc.anchor = GridBagConstraints.WEST;
      add(tb, gbc);
   }
}

From source file:com.micromux.cassandra.jdbc.JdbcRegressionTest.java

/**
 * Verify that even with an empty ResultSet; the resultset meta-data can still
 * be queried. Previously, this was <i>Issue 75</i>.
 *///from   ww w.  j  a  v a2 s  . c o  m
@Test
public void testEmptyResultSet() throws Exception {

    Statement stmt = con.createStatement();

    String truncate = "TRUNCATE regressiontest;";
    stmt.execute(truncate);

    String select = "select ivalue from " + TABLE;

    ResultSet result = stmt.executeQuery(select);
    assertFalse("Make sure we have no rows", result.next());
    ResultSetMetaData rsmd = result.getMetaData();
    assertTrue("Make sure we do get a result", rsmd.getColumnDisplaySize(1) != 0);
    assertNotNull("Make sure we do get a label", rsmd.getColumnLabel(1));
    System.out.println(
            "Found a column in ResultsetMetaData even when there are no rows:   " + rsmd.getColumnLabel(1));
    stmt.close();
}