Print the data in ResultSet along with column names

Question

I am retrieving columns names from a SQL database through Java  I know I can retrieve columns names from ResultSet too  So I have this sql query    select column name from information schema columns where table name  suppliers    The problem is I don t know how can I get columns names from ResultSet and my code is  public void getAllColumnNames   throws Exception   String sql    SELECT column name from information schema columns where table name  suppliers     PreparedStatement ps   connection prepareStatement sql   ResultSet rs   ps executeQuery sql       extract values from rs

User · Answer

ResultSet resultSet   statement executeQuery  SELECT   from foo    ResultSetMetaData rsmd   resultSet getMetaData    int columnsNumber   rsmd getColumnCount    while  resultSet next          for  int i   1  i  lt   columnsNumber  i              if  i  gt  1  System out print                 String columnValue   resultSet getString i           System out print columnValue         rsmd getColumnName i              System out println          Reference   Printing the result of ResultSet

User · Answer

Have a look at the documentation  You made the following mistakes  Firstly  ps executeQuery   doesn t have any parameters  Instead you passed the SQL query into it   Secondly  regarding the prepared statement  you have to use the   symbol if want to pass any parameters  And later bind it using   setXXX index  value     Here xxx stands for the data type

User · Answer

use further as  rs getString 1   rs getInt 2     1  2 is the column number of table and set int or string as per data-type of coloumn

User · Answer

For what you are trying to do  instead of PreparedStatement you can use Statement  Your code may be modified as-  String sql    SELECT column name from information schema columns where table name  suppliers      Statement s    connection createStatement    ResultSet rs   s executeQuery sql     Hope this helps

User · Answer

1  Instead of PreparedStatement use Statement  2  After executing query in ResultSet  extract values with the help of rs getString   as    Statement st cn createStatement    ResultSet rs st executeQuery sql   while rs next          rs getString 1     or rs getString  column name

User · Answer

For those who wanted more better version of the resultset printing as util class This was really helpful for printing resultset and does many things from a single util    thanks to Hami Torun   In this class printResultSet uses ResultSetMetaData in a generic way have a look at it      import java sql    import java util ArrayList  import java util List  import java util StringJoiner   public final class DBTablePrinter                   Column type category for CHAR  VARCHAR        and similar text columns              public static final int CATEGORY STRING   1                 Column type category for TINYINT  SMALLINT         INT and BIGINT columns              public static final int CATEGORY INTEGER   2                 Column type category for REAL  DOUBLE         and DECIMAL columns              public static final int CATEGORY DOUBLE   3                 Column type category for date and time related columns like        DATE  TIME  TIMESTAMP etc              public static final int CATEGORY DATETIME   4                 Column type category for BOOLEAN columns              public static final int CATEGORY BOOLEAN   5                 Column type category for types for which the type name        will be printed instead of the content  like BLOB         BINARY  ARRAY etc              public static final int CATEGORY OTHER   0                 Default maximum number of rows to query and print              private static final int DEFAULT MAX ROWS   10                 Default maximum width for text columns         like a VARCHAR  column              private static final int DEFAULT MAX TEXT COL WIDTH   150                  Overloaded method that prints rows from table tableName        to standard out using the given database connection        conn  Total number of rows will be limited to          link  DEFAULT MAX ROWS  and          link  DEFAULT MAX TEXT COL WIDTH  will be used to limit        the width of text columns  like a VARCHAR column                  param conn      Database connection object  java sql Connection          param tableName Name of the database table             public static void printTable Connection conn  String tableName            printTable conn  tableName  DEFAULT MAX ROWS  DEFAULT MAX TEXT COL WIDTH                         Overloaded method that prints rows from table tableName        to standard out using the given database connection        conn  Total number of rows will be limited to        maxRows and          link  DEFAULT MAX TEXT COL WIDTH  will be used to limit        the width of text columns  like a VARCHAR column                  param conn      Database connection object  java sql Connection          param tableName Name of the database table         param maxRows   Number of max  rows to query and print             public static void printTable Connection conn  String tableName  int maxRows            printTable conn  tableName  maxRows  DEFAULT MAX TEXT COL WIDTH                         Overloaded method that prints rows from table tableName        to standard out using the given database connection        conn  Total number of rows will be limited to        maxRows and        maxStringColWidth will be used to limit        the width of text columns  like a VARCHAR column                  param conn              Database connection object  java sql Connection          param tableName         Name of the database table         param maxRows           Number of max  rows to query and print         param maxStringColWidth Max  width of text columns             public static void printTable Connection conn  String tableName  int maxRows  int maxStringColWidth            if  conn    null                System err println  DBTablePrinter Error  No connection to database  Connection is null                  return                    if  tableName    null                System err println  DBTablePrinter Error  No table name  tableName is null                  return                    if  tableName length      0                System err println  DBTablePrinter Error  Empty table name                 return                    if  maxRows         ResultSet to standard out using   link  DEFAULT MAX TEXT COL WIDTH         to limit the width of text columns                 param rs The ResultSet to print             public static void printResultSet ResultSet rs            printResultSet rs  DEFAULT MAX TEXT COL WIDTH                         Overloaded method to print rows of a         ResultSet to standard out using maxStringColWidth        to limit the width of text columns                 param rs                The ResultSet to print         param maxStringColWidth Max  width of text columns             public static void printResultSet ResultSet rs  int maxStringColWidth            try               if  rs    null                    System err println  DBTablePrinter Error  Result set is null                     return                            if  rs isClosed                      System err println  DBTablePrinter Error  Result Set is closed                     return                            if  maxStringColWidth  columns   new ArrayList columnCount                   List of table names  Can be more than one if it is a joined                table query             List tableNames   new ArrayList columnCount                   Get the columns and their meta data                 NOTE  columnIndex for rsmd getXXX methods STARTS AT 1 NOT 0             for  int i   1  i  maxStringColWidth                                    value   value substring 0  maxStringColWidth - 3                                                                     break                                                Adjust the column width                     c setWidth value length     c getWidth     value length     c getWidth                         c addValue value                        END of for loop columnCount                 rowCount                      END of while  rs next                              At this point we have gone through meta data  get the             columns and created all Column objects  iterated over the             ResultSet rows  populated the column values and adjusted             the column widths              We cannot start printing just yet because we have to prepare             a row separator String                                  For the fun of it  I will use StringBuilder             StringBuilder strToPrint   new StringBuilder                StringBuilder rowSeparator   new StringBuilder                                Prepare column labels to print as well as the row separator              It should look something like this               -------- ------------ ------------ -----------    row separator                EMP NO   BIRTH DATE   FIRST NAME   LAST NAME     labels row               -------- ------------ ------------ -----------    row separator                                  Iterate over columns             for  Column c   columns                    int width   c getWidth                        Center the column label                 String toPrint                  String name   c getLabel                    int diff   width - name length                     if   diff   2     1                           diff is not divisible by 2  add 1 to width  and diff                         so that we can have equal padding to the left and right                        of the column label                      width                        diff                        c setWidth width                                      int paddingSize   diff   2     InteliJ says casting to int is redundant                      Cool String repeater code thanks to user102008 at stackoverflow com                  String padding   new String new char paddingSize   replace   0                          toPrint          padding   name   padding                           END centering the column label                  strToPrint append toPrint                    rowSeparator append                       rowSeparator append new String new char width   2   replace   0    -                                String lineSeparator   System getProperty  line separator                    Is this really necessary                lineSeparator   lineSeparator    null     n    lineSeparator               rowSeparator append      append lineSeparator                strToPrint append      append lineSeparator               strToPrint insert 0  rowSeparator               strToPrint append rowSeparator                StringJoiner sj   new StringJoiner                    for  String name   tableNames                    sj add name                              String info    Printing     rowCount              info    rowCount   1     rows from       row from                info    tableNames size     1    tables      table                info    sj toString                 System out println info                   Print out the formatted column labels             System out print strToPrint toString                  String format                  Print out the rows             for  int i   0  i         Integers should not be truncated so column widths should        be adjusted without a column width limit  Text columns should be        left justified and can be truncated to a max  column width etc                   See also          java sql Types                param type Generic SQL type         return The category this type belongs to             private static int whichCategory int type            switch  type                case Types BIGINT              case Types TINYINT              case Types SMALLINT              case Types INTEGER                  return CATEGORY INTEGER               case Types REAL              case Types DOUBLE              case Types DECIMAL                  return CATEGORY DOUBLE               case Types DATE              case Types TIME              case Types TIME WITH TIMEZONE              case Types TIMESTAMP              case Types TIMESTAMP WITH TIMEZONE                  return CATEGORY DATETIME               case Types BOOLEAN                  return CATEGORY BOOLEAN               case Types VARCHAR              case Types NVARCHAR              case Types LONGVARCHAR              case Types LONGNVARCHAR              case Types CHAR              case Types NCHAR                  return CATEGORY STRING               default                  return CATEGORY OTHER                                  Represents a database table column              private static class Column                           Column label                      private String label                          Generic SQL type of the column as defined in                        java sql Types                                  private int type                          Generic SQL type name of the column as defined in                        java sql Types                                  private String typeName                          Width of the column that will be adjusted according to column label            and values to be printed                      private int width   0                          Column values from each row of a ResultSet                      private List values   new ArrayList                            Flag for text justification using String format             Empty string      to justify right             dash  -  to justify left                         see  justifyLeft                       private String justifyFlag                               Column type category  The columns will be categorised according            to their column types and specific needs to print them correctly                      private int typeCategory   0                          Constructs a new Column with a column label             generic SQL type and type name  as defined in                        java sql Types                                     param label    Column label or name             param type     Generic SQL type             param typeName Generic SQL type name                     public Column String label  int type  String typeName                this label   label              this type   type              this typeName   typeName                                    Returns the column label                        return Column label                     public String getLabel                 return label                                    Returns the generic SQL type of the column                        return Generic SQL type                     public int getType                 return type                                    Returns the generic SQL type name of the column                        return Generic SQL type name                     public String getTypeName                 return typeName                                    Returns the width of the column                        return Column width                     public int getWidth                 return width                                    Sets the width of the column to width                        param width Width of the column                     public void setWidth int width                this width   width                                    Adds a String representation  value             of a value to this column object s   link  values  list             These values will come from each row of a                        ResultSet             of a database query                         param value The column value to add to   link  values                      public void addValue String value                values add value                                     Returns the column value at row index i             Note that the index starts at 0 so that getValue 0             will get the value for this column from the first row            of a             ResultSet                         param i The index of the column value to get             return The String representation of the value                     public String getValue int i                return values get i                                     Returns the value of the   link  justifyFlag   The column            values will be printed using String format and            this flag will be used to right or left justify the text                         return The   link  justifyFlag  of this column             see  justifyLeft                       public String getJustifyFlag                 return justifyFlag                                    Sets   link  justifyFlag  to  -  so that            the column value will be left justified when printed with            String format  Typically numbers will be right            justified and text will be left justified                      public void justifyLeft                 this justifyFlag    -                                     Returns the generic SQL type category of the column                        return The   link  typeCategory  of the column                     public int getTypeCategory                 return typeCategory                                    Sets the   link  typeCategory  of the column                        param typeCategory The type category                     public void setTypeCategory int typeCategory                this typeCategory   typeCategory                       This is the scala version of doing this    which will print column names and data as well in a generic way     def printQuery res  ResultSet   Unit         val rsmd   res getMetaData     val columnCount   rsmd getColumnCount     var rowCnt   0     val s   StringBuilder newBuilder     while  res next             s clear         if  rowCnt    0            s append               for  i  lt - 1 to columnCount              val name   rsmd getColumnName i            s append name            s append                         s append   n                 rowCnt    1       s append             for  i  lt - 1 to columnCount            if  i  gt  1            s append                s append res getString i                 s append             System out println s            System out println s TOTAL   rowCnt rows

[java] Print the data in ResultSet along with column names

Examples related to java

Examples related to mysql

Examples related to scala

Examples related to jdbc