Export (0) Print
Expand All

Retrieving Result Set Data Sample

This Microsoft JDBC Driver for SQL Server sample application demonstrates how to retrieve a set of data from a SQL Server database, and then display that data.

The code file for this sample is named retrieveRS.java, and it can be found in the following location:

<installation directory>\sqljdbc_<version>\<language>\help\samples\resultsets

To run this sample application, you must set the classpath to include the sqljdbc.jar file or the sqljdbc4.jar file. If the classpath is missing an entry for sqljdbc.jar or sqljdbc4.jar, the sample application will throw the common "Class not found" exception. You will also need access to the SQL Server 2005 AdventureWorks sample database. For more information about how to set the classpath, see Using the JDBC Driver.

Note Note

The Microsoft JDBC Driver for SQL Server provides sqljdbc.jar and sqljdbc4.jar class library files to be used depending on your preferred Java Runtime Environment (JRE) settings. For more information about which JAR file to choose, see System Requirements for the JDBC Driver.

In the following example, the sample code makes a connection to the AdventureWorks sample database. Then, using an SQL statement with the SQLServerStatement object, it runs the SQL statement and places the data that it returns into a SQLServerResultSet object.

Next, the sample code calls the custom displayRow method to iterate through the rows of data that are contained in the result set, and uses the getString method to display some of the data that it contains.

import java.sql.*;

public class retrieveRS {

   public static void main(String[] args) {

      // Create a variable for the connection string.
      String connectionUrl = "jdbc:sqlserver://localhost:1433;" +
            "databaseName=AdventureWorks;integratedSecurity=true;";

      // Declare the JDBC objects.
      Connection con = null;
      Statement stmt = null;
      ResultSet rs = null;

      try {

         // Establish the connection.
         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
         con = DriverManager.getConnection(connectionUrl);

         // Create and execute an SQL statement that returns a
         // set of data and then display it.
         String SQL = "SELECT * FROM Production.Product;";
         stmt = con.createStatement();
         rs = stmt.executeQuery(SQL);
         displayRow("PRODUCTS", rs);
      }

      // Handle any errors that may have occurred.
      catch (Exception e) {
         e.printStackTrace();
      }

      finally {
         if (rs != null) try { rs.close(); } catch(Exception e) {}
         if (stmt != null) try { stmt.close(); } catch(Exception e) {}
         if (con != null) try { con.close(); } catch(Exception e) {}
      }
   }

   private static void displayRow(String title, ResultSet rs) {
      try {
         System.out.println(title);
         while (rs.next()) {
            System.out.println(rs.getString("ProductNumber") + " : " + rs.getString("Name"));
         }
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft