Data Source Sample

This Microsoft SQL Server JDBC Driver sample application demonstrates how to connect to a SQL Server database by using a data source object. It also demonstrates how to retrieve data from a SQL Server database by using a stored procedure.

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

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

Requirements

To run this sample application, you must set the classpath to include the sqljdbc.jar file or 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.

Example

In the following example, the sample code sets various connection properties by using setter methods of the SQLServerDataSource object, and then calls the getConnection method of the SQLServerDataSource object to return a SQLServerConnection object.

Next, the sample code uses the prepareCall method of the SQLServerConnection object to create a SQLServerCallableStatement object, and then the executeQuery method is called to execute the stored procedure.

Finally, the sample uses the SQLServerResultSet object returned from the executeQuery method to iterate through the results returned by the stored procedure.

import java.sql.*;
import com.microsoft.sqlserver.jdbc.*;

public class connectDS {

   public static void main(String[] args) {

      // Declare the JDBC objects.
      Connection con = null;
      CallableStatement cstmt = null;
      ResultSet rs = null;

      try {
         // Establish the connection. 
         SQLServerDataSource ds = new SQLServerDataSource();
         ds.setUser("UserName");
         ds.setPassword("*****");
         ds.setServerName("localhost");
         ds.setPortNumber(1433); 
         ds.setDatabaseName("AdventureWorks");
         con = ds.getConnection();

         // Execute a stored procedure that returns some data.
         cstmt = con.prepareCall("{call dbo.uspGetEmployeeManagers(?)}");
         cstmt.setInt(1, 50);
         rs = cstmt.executeQuery();

         // Iterate through the data in the result set and display it.
         while (rs.next()) {
            System.out.println("EMPLOYEE: " + rs.getString("LastName") + 
               ", " + rs.getString("FirstName"));
            System.out.println("MANAGER: " + rs.getString("ManagerLastName") + 
               ", " + rs.getString("ManagerFirstName"));
            System.out.println();
         }
      }

      // Handle any errors that may have occurred.
      catch (Exception e) {
         e.printStackTrace();
      }
      finally {
         if (rs != null) try { rs.close(); } catch(Exception e) {}
         if (cstmt != null) try { cstmt.close(); } catch(Exception e) {}
         if (con != null) try { con.close(); } catch(Exception e) {}
         System.exit(1);
      }
   }
}

See Also

Other Resources

Connecting and Retrieving Data