List of usage examples for java.sql ResultSetMetaData getColumnLabel
String getColumnLabel(int column) throws SQLException;
From source file:ExecSQL.java
/** * Prints a result set.//w ww . j ava 2 s . com * @param stat the statement whose result set should be printed */ public static void showResultSet(Statement stat) throws SQLException { ResultSet result = stat.getResultSet(); ResultSetMetaData metaData = result.getMetaData(); int columnCount = metaData.getColumnCount(); for (int i = 1; i <= columnCount; i++) { if (i > 1) System.out.print(", "); System.out.print(metaData.getColumnLabel(i)); } System.out.println(); while (result.next()) { for (int i = 1; i <= columnCount; i++) { if (i > 1) System.out.print(", "); System.out.print(result.getString(i)); } System.out.println(); } result.close(); }
From source file:com.hangum.tadpole.engine.sql.util.QueryUtils.java
/** * query to json//w ww .j a v a2s . co m * * @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:wzw.sql.ResultSetConverter.java
/** * Convert a ResultSet Object to Map.// ww w .j ava2s . co m * @param rs * @return * @throws SQLException */ public static Map<String, Object> toMap(ResultSet rs) throws SQLException { if (rs == null) { return null; } Map<String, Object> map = new Hashtable<String, Object>(); ResultSetMetaData rsmd = rs.getMetaData(); if (rsmd.getColumnCount() >= 0) { if (rs.next()) { for (int j = 1; j <= rsmd.getColumnCount(); j++) { if (rs.getObject(j) != null) { // Null // columnName = (String) rsmd.getColumnName(i); ? // ??,?MySql? map.put(rsmd.getColumnLabel(j).toLowerCase(), rs.getObject(j)); } } } } return map; }
From source file:com.baidu.qa.service.test.util.JdbcUtil.java
protected static List<Map<String, Object>> excuteQuerySql(String sqlStr, String dbname) throws Exception { List<Map<String, Object>> rltList = new ArrayList<Map<String, Object>>(); // ???/*from w w w .j a va 2 s .c o m*/ Connection con = null; Statement sm = null; ResultSet rs = null; try { //? con = MysqlDatabaseManager.getCon(dbname); Assert.assertNotNull("connect to db error:" + dbname, con); //?? sm = con.createStatement(); rs = sm.executeQuery(sqlStr); log.info("[sql:]" + sqlStr); ResultSetMetaData rsmd = rs.getMetaData(); int numberOfColumns = rsmd.getColumnCount(); int count = 0; String key; Object value; // ???? while (rs.next()) { Map<String, Object> expectData = new HashMap<String, Object>(); count = 0; while (count++ < numberOfColumns) { key = rsmd.getColumnLabel(count); value = rs.getObject(key); expectData.put(key, value); } rltList.add(expectData); } return rltList; } catch (Exception e) { throw e; } finally { if (con != null) { con.close(); } if (sm != null) { sm.close(); } if (rs != null) { rs.close(); } } }
From source file:com.espertech.esper.epl.db.DatabasePollingViewableFactory.java
private static Map<String, DBOutputTypeDesc> compileResultMetaData(ResultSetMetaData resultMetaData, ColumnSettings columnSettings) throws SQLException { Map<String, DBOutputTypeDesc> outputProperties = new HashMap<String, DBOutputTypeDesc>(); for (int i = 0; i < resultMetaData.getColumnCount(); i++) { String columnName = resultMetaData.getColumnLabel(i + 1); if (columnName == null) { columnName = resultMetaData.getColumnName(i + 1); }//from w w w . j av a 2 s . c o m int columnType = resultMetaData.getColumnType(i + 1); String javaClass = resultMetaData.getColumnTypeName(i + 1); ConfigurationDBRef.ColumnChangeCaseEnum caseEnum = columnSettings.getColumnCaseConversionEnum(); if ((caseEnum != null) && (caseEnum == ConfigurationDBRef.ColumnChangeCaseEnum.LOWERCASE)) { columnName = columnName.toLowerCase(); } if ((caseEnum != null) && (caseEnum == ConfigurationDBRef.ColumnChangeCaseEnum.UPPERCASE)) { columnName = columnName.toUpperCase(); } DatabaseTypeBinding binding = null; String javaTypeBinding = null; if (columnSettings.getJavaSqlTypeBinding() != null) { javaTypeBinding = columnSettings.getJavaSqlTypeBinding().get(columnType); } if (javaTypeBinding != null) { binding = DatabaseTypeEnum.getEnum(javaTypeBinding).getBinding(); } DBOutputTypeDesc outputType = new DBOutputTypeDesc(columnType, javaClass, binding); outputProperties.put(columnName, outputType); } return outputProperties; }
From source file:org.neo4j.doc.cypherdoc.BlockType.java
private static String executeSql(String sql, Connection sqldb) { StringBuilder builder = new StringBuilder(512); try (Statement statement = sqldb.createStatement()) { if (statement.execute(sql)) { try (ResultSet result = statement.getResultSet()) { ResultSetMetaData meta = result.getMetaData(); int rowCount = 0; int columnCount = meta.getColumnCount(); String line = new String(new char[columnCount]).replace("\0", "+" + LINE_SEGMENT) + "+\n"; builder.append(line);//from w w w .j a v a 2 s. c om for (int i = 1; i <= columnCount; i++) { String output = meta.getColumnLabel(i); printColumn(builder, output); } builder.append("|\n").append(line); while (result.next()) { rowCount++; for (int i = 1; i <= columnCount; i++) { String output = result.getString(i); printColumn(builder, output); } builder.append("|\n"); } builder.append(line).append(rowCount).append(" rows\n"); } } } catch (SQLException sqlException) { throw new RuntimeException(sqlException); } return builder.toString(); }
From source file:dbs_project.util.Utils.java
public static String resultSetToHtmlTable(java.sql.ResultSet rs) throws SQLException { int rowCount = 0; final StringBuilder result = new StringBuilder(); result.append("<P ALIGN='center'>\n<TABLE BORDER=1>\n"); ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); //header/*w w w.j a v a 2 s . co m*/ result.append("\t<TR>\n"); for (int i = 0; i < columnCount; ++i) { result.append("\t\t<TH>").append(rsmd.getColumnLabel(i + 1)).append("</TH>\n"); } result.append("\t</TR>\n"); //data while (rs.next()) { ++rowCount; result.append("\t<TR>\n"); for (int i = 0; i < columnCount; ++i) { String value = rs.getString(i + 1); if (rs.wasNull()) { value = "<null>"; } result.append("\t\t<TD>").append(value).append("</TD>\n"); } result.append("\t</TR>\n"); } result.append("</TABLE>\n</P>\n"); return result.toString(); }
From source file:org.orbisgis.corejdbc.ReadTable.java
/** * Return a concatened and human readable format of provided result set * @param rs result set to read/* w w w. j av a2s .c o m*/ * @param maxFieldLength Maximum field length to print * @param maxPrintedRows Maximum printed rows * @param addColumns Add column header * @param alignColumns Align columns by using padding * @param resultSetFilter Accept or refuse rows by implementing this interface * @return human readable format of provided result set * @throws SQLException */ public static String resultSetToString(ResultSet rs, int maxFieldLength, int maxPrintedRows, boolean addColumns, boolean alignColumns, ResultSetFilter resultSetFilter) throws SQLException { // Print headers ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); StringBuilder lines = new StringBuilder(); StringBuilder formatStringBuilder = new StringBuilder(); String[] header = new String[columnCount]; for (int idColumn = 1; idColumn <= columnCount; idColumn++) { header[idColumn - 1] = metaData.getColumnLabel(idColumn) + "(" + metaData.getColumnTypeName(idColumn) + ")"; if (alignColumns) { formatStringBuilder.append("%-"); formatStringBuilder.append(maxFieldLength); formatStringBuilder.append("s "); } else { formatStringBuilder.append("%s "); } } if (addColumns) { lines.append(String.format(formatStringBuilder.toString(), header)); lines.append("\n"); } int shownLines = 0; NumberFormat decimalFormat = NumberFormat.getInstance(Locale.getDefault()); decimalFormat.setGroupingUsed(false); decimalFormat.setMaximumFractionDigits(16); while (rs.next() && shownLines < maxPrintedRows) { if (resultSetFilter.printRow(rs)) { String[] row = new String[columnCount]; for (int idColumn = 1; idColumn <= columnCount; idColumn++) { Object valObj = rs.getObject(idColumn); String value; if (valObj instanceof Number) { value = decimalFormat.format(valObj); } else { value = rs.getString(idColumn); } if (value != null) { if (columnCount > 1 && value.length() > maxFieldLength) { value = value.substring(0, maxFieldLength - 2) + ".."; } } else { value = "NULL"; } row[idColumn - 1] = value; } shownLines++; lines.append(String.format(formatStringBuilder.toString(), row)); lines.append("\n"); } } if (lines.length() != 0) { return lines.toString(); } else { return I18N.tr("No attributes to show"); } }
From source file:org.jabsorb.ext.DataList.java
/** * Build an array of ColumnMetaData object from a ResultSetMetaData object. * * @param rmd ResultSetMetaData to build ColumnMetaData from. * @return ColumnMetaData array or null if ResultSetMetaData is null. * * @throws SQLException if there is a problem processing the * ResultSetMetaData object./* w ww. j ava2 s.c o m*/ */ public static ColumnMetaData[] buildColumnMetaDataFromResultSetMetaData(ResultSetMetaData rmd) throws SQLException { if (rmd == null) { return null; } int j = rmd.getColumnCount(); ColumnMetaData[] cmd = new ColumnMetaData[j]; for (int i = 1; i <= j; i++) { ColumnMetaData c = new ColumnMetaData(); c.setColumnName(rmd.getColumnName(i)); c.setCatalogName(rmd.getCatalogName(i)); c.setColumnClassName(rmd.getColumnClassName(i)); c.setColumnDisplaySize(rmd.getColumnDisplaySize(i)); c.setColumnLabel(rmd.getColumnLabel(i)); c.setColumnType(rmd.getColumnType(i)); c.setColumnTypeName(rmd.getColumnTypeName(i)); c.setPrecision(rmd.getPrecision(i)); c.setScale(rmd.getScale(i)); c.setSchemaName(rmd.getSchemaName(i)); c.setTableName(rmd.getTableName(i)); c.setAutoIncrement(rmd.isAutoIncrement(i)); c.setCaseSensitive(rmd.isCaseSensitive(i)); c.setCurrency(rmd.isCurrency(i)); c.setNullable(rmd.isNullable(i)); c.setReadOnly(rmd.isReadOnly(i)); c.setSearchable(rmd.isSearchable(i)); c.setSigned(rmd.isSigned(i)); c.setWritable(rmd.isWritable(i)); c.setDefinitelyWritable(rmd.isDefinitelyWritable(i)); cmd[i - 1] = c; } return cmd; }
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 ww.j a v a 2s . co m*/ */ 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(); }