JDBC GS retrieving ResultSet rows

Retrieving data from a ResultSet table using a Statement object.

  1. First you need to Create a Database Connection.
  2. You can then freely choose one of the 3 createStatement() methods that are available to create a Statement object.
  3. The next step is to retrieve the ResultSet object from the Statement object with the executeQuery() method:
  4. Now you can set the cursor to a row using one of the positioning methods in the ResultSet object. Which methods of these to use depends on the scrolling you have selected when you created the Statement object.
  5. When a row in the ResultSet is accessible several ResultSet methods can be used to retrieve column data.
    Here is the most used:
    Methods in ResultSet interface Description
    double getDouble(int)
    double getDouble(String)
    Gets a double from a column with number (1.. N) or database table defined column name.
    float getFloat(int)
    float getFloat(String)
    Gets a float from a column with number (1.. N) or database table defined column name.
    int getInt(int)
    int getInt(String)
    Gets an int from a column with number (1.. N) or database table defined column name.
    long getLong(int)
    long getLong(String)
    Gets a long from a column with number (1.. N) or database table defined column name.
    short getShort(int)
    short getShort(String)
    Gets a short from a column with number (1.. N) or database table defined column name.
    String getString(int)
    String getString(String)
    Gets a String from a column with number (1.. N) or database table defined column name.
    Time getTime(int)
    Time getTime(String)
    Gets a Time from a column with number (1.. N) or database table defined column name.
    Time getTime(int, Calendar)
    Time getTime(String, Calendar)
    Gets a Time from a column with number (1.. N) or database table defined column name. For both methods Calendar object is used in constructing the Time.
    Date getDate(int)
    Date getDate(String)
    Gets a Date from a column with number (1.. N) or database table defined column name.
    Date getDate(int, Calendar)
    Date getDate(String, Calendar)
    Gets a Date from a column with number (1.. N) or database table defined column name. For both methods Calendar object is used in constructing the Date.
    Timestamp getTimestamp(int)
    Timestamp getTimestamp(String)
    Gets a Timestamp from a column with number (1.. N) or database table defined column name.
    Timestamp getTimestamp(int, Calendar )
    Timestamp getTimestamp(String, Calendar )
    Gets a Timestamp from a column with number (1.. N) or database table defined column name. For both methods Calendar object is used in constructing the timestamp.
    boolean getBoolean(int)
    boolean getBoolean(String)
    Gets a boolean from a column with number (1.. N) or database table defined column name.
    byte getByte(int)
    byte getByte(String)
    Gets a byte from a column with number (1.. N) or database table defined column name.
    byte[] getBytes(int)
    byte[] getBytes(String)
    Gets a array of byte from a column with number (1.. N) or database table defined column name.
    Array getArray(int)
    Array getArray(String)
    Gets a Array of objects from a column with number (1.. N) or database table defined column name.
    BigDecimal getBigDecimal(int)
    BigDecimal getBigDecimal(String)
    Gets a BigDecimal from a column with number (1.. N) or database table defined column name.
    Object getObject(int)
    Object getObject(String)
    Gets a Object from a column with number (1.. N) or database table defined column name.
    RowId getRowId(int)
    RowId getRowId(String)
    Gets a RowId from a column with number (1.. N) or database table defined column name.
    InputStream getBinaryStream(int)
    InputStream getBinaryStream(String)
    Gets a InputStream of bytes from a column with number (1.. N) or database table defined column name.
    Reader getCharacterStream(int)
    Reader getCharacterStream(String)
    Gets a Reader containing the column value using the column number (1.. N) or database table defined column name.
    Example of retrieving data from a ResultSet table using a Statement object:
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    
    public class RetrieveResultSet {
      public static void main(String[] args) {
        Connection conn = null;
        Statement stmt= null;
        try {
          // Register a driver for the MySQL database
          Class.forName("com.mysql.jdbc.Driver");
          // Create a url for accessing the MySQL
          // database CarDB
          String url = "jdbc:mysql://localhost:3306/CarDB";
          // user and password to access the database
          String username = "root";
          String password = "root";
          // User the DriverManager to get a Connection to the database
          conn = DriverManager.getConnection(url, username, password);
          // Create a  Statement for a ResultSet that is
          // sensitive for other users updating of tables in the database and
          // the ResultSet is updatable for a selected table in the database.
          stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
          // Create a  ResultSet with a query that returns
          // all the columns  from the dB table Carpark
          ResultSet resultSet = stmt.executeQuery("select * from Carpark ");
          // As cursor is at the before first row position
          // we use the next() method to
          // test and read the first row in the ResultSet.
          if (resultSet.next()) {
            // Then we use a loop to retrieve rows and column data
            // and creates a html coded table output
            System.out.println("<table border='1' >");
            do {
              System.out.println("<tr>");
              System.out.print("<td>" + resultSet.getString("regNo") + "</td>");
              System.out.print("<td>" + resultSet.getString("cartype") + "</td>");
              System.out.println("<td>" + resultSet.getInt("model") + "</td>");
              System.out.println("<td>" + resultSet.getDouble("dayPrice") + "</td>");
              System.out.println("</tr>");
            } while (resultSet.next());
            System.out.println("</table>");
          }
        } catch (ClassNotFoundException ex) {
          Logger.getLogger(RetrieveResultSet.class.getName()).
                           log(Level.SEVERE, null, ex);
        } catch (SQLException e) {
          e.printStackTrace();
        } finally {
          try {
            // Close the Statement, which also close the ResultSet
            stmt.close();
            conn.close();
          } catch (Exception xe) {
            xe.printStackTrace();
          }
        }
      }
    }

    You should use the close() method in the Statement object when you do not need it anymore.

    You should also use the close() method in the Connection object when you do not need it anymore.

    You can download this example here (needed tools can be found in the right menu on this page).

    If we run this application the result should be:
    As pure output Translated by a Browser
    <table border='1' >
    <tr>
    <td>AD23443</td><td>BMW</td><td>2001</td>
    <td>300.0</td>
    </tr>
    <tr>
    <td>AX72345</td><td>AUDI</td><td>2003</td>
    <td>350.0</td>
    </tr>
    <tr>
    <td>CE23473</td><td>AUDI</td><td>2001</td>
    <td>400.0</td>
    </tr>
    <tr>
    <td>DE12345</td><td>AUDI</td><td>2003</td>
    <td>250.0</td>
    </tr>
    <tr>
    <td>DE23453</td><td>FORD</td><td>2002</td>
    <td>375.0</td>
    </tr>
    <tr>
    <td>DE34562</td><td>OPEL</td><td>2001</td>
    <td>340.0</td>
    </tr>
    <tr>
    <td>RE23456</td><td>FORD</td><td>2005</td>
    <td>350.0</td>
    </tr>
    </table>
    AD23443BMW2001 300.0
    AX72345AUDI2003 350.0
    CE23473AUDI2001 400.0
    DE12345AUDI2003 250.0
    DE23453FORD2002 375.0
    DE34562OPEL2001 340.0
    RE23456FORD2005 350.0

© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.