SDS Java JDBC examples

In my previous blog entry I mentioned that I would post some Java & JDBC examples for connecting to and interacting with the SDS service. Since that post (roughly an hour ago) I’ve been called a, “slacker” by David Robinson one of the PM’s on the team. Therefore, to show that I am not a, “slacker” I’ve decided to just post the code today. Happy Dave :-)

So, in this example I’m using Java 1.6, NetBeans as the editor and the latest JDBC driver for SQL Server which you can download from here. The only jar file I’m currently using to compile (and run with) other than the expected JRE jar files is the, “sqljdbc4.jar” jar file which comes along with the download I’ve linked to above. NOTE: That if you choose to use an earlier version of JDBC it *must* support SSL. This is a requirement for the service.

Now, I’ve purposely made this example to be the exact same as the C# example I did previously so you could focus more on the differences with the client technologies and less on the actual data I’ve created. As you can see the only significant difference relative to SDS is the format of a couple of connection string values (namely the database, user id format and the initial url for the server). Other than that it’s the same code that we would use to connect to locally. 

Now, with no further delay. The Java code for working with SDS.

     public static void main(String[] args)
    {
        String createTableSql = 
                "CREATE TABLE [dbo].[tbl_Person]    " +
                "(                                                    " +
                "     [FirstName] NVARCHAR(64) NOT NULL,              " +
             "     [LastName] NVARCHAR(64) NOT NULL                " +
                "    CONSTRAINT [personName_PK] PRIMARY KEY CLUSTERED " +
                "    (                                                " +
                "        [FirstName] ASC,                             " +
                "        [LastName] ASC                               " +
                "    )                                                " +
                ")";     
        
        String insertSql = 
                "INSERT INTO dbo.tbl_Person(FirstName, LastName) VALUES ('Jeff', 'Currier'); " +
                "INSERT INTO dbo.tbl_Person(FirstName, LastName) VALUES ('Nigel', 'Ellis'); " +
                "INSERT INTO dbo.tbl_Person(FirstName, LastName) VALUES ('David', 'Robinson');"+ 
                "INSERT INTO dbo.tbl_Person(FirstName, LastName) VALUES ('Jeff', 'Smith');";
                
        try
        {
            // First, format the connection string note that the server name here again is, “myserver”.  The database name (my user database name) is mydatabase.  
            String connectionUrl = "jdbc:sqlserver://myserver.data.dev.mscds.com;" +
                    "database=mydatabase;encrypt=true;user=jeff@myserver;password=*****";

            // Next, make the sure the SQL Server Driver is loaded.
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            
            // Then attempt to get a connection.  This will null or throw if we can't
            // get a connection.
            Connection sqlConn = DriverManager.getConnection(connectionUrl);
            if (sqlConn == null)
            {
                System.out.println("Unable to obtain connection.  exiting");
                System.exit(1);
            }

            // Begin by creating the table we'll use.
            Statement sqlStmt = sqlConn.createStatement();
            sqlStmt.execute(createTableSql);

            // Then, insert some rows into the table that we just created.
            sqlStmt.execute(insertSql);

            // Now, select all of the, "Jeff' data from the table and print them out.
            ResultSet results = sqlStmt.executeQuery("select FirstName, LastName from tbl_Person where FirstName='Jeff'");
            while (results.next())
            {
                System.out.println("FirstName: " + results.getString("FirstName") + " LastName: " + results.getString("LastName"));
            }
            // Close the ResultSet up.
            results.close();

            // Finally drop the table and close the conneciton.
            sqlStmt.execute("drop table tbl_Person");
            sqlConn.close();
        } catch (SQLException ex)
        {
            System.out.println("Error: Unable to execute query ");
            ex.printStackTrace();
        } catch(ClassNotFoundException ex)
        {
            System.out.println("Error: Unable to load JDBC Driver!");
        }

    }

 Enjoy,

 --Jeff--