Developer Product Briefs

Make a SQL-to-Java Connection

SQL Server 2005's Type 4 JDBC driver provides database connectivity from a Java application. Though the driver class and connection URL are a bit different in this version, connectivity is a snap.

• By Deepak Vohra
• 05/01/2006

Make a SQL-to-Java Connection
Apply SQL Server's Type 4 JDBC driver to connect Java applications to the database server.
by Deepak Vohra

May 1, 2006

The SQL Server 2005 database is available in various editions: Enterprise, Standard, Workgroup, Express, Developer, and Mobile. The Enterprise Edition is for enterprise-level applications that require high database scalability and availability. The Standard Edition is tailored for small- and medium-sized organizations, and the Workgroup Edition is targeted for organizations and workgroups that require no limitations on the number of users or the size of the database. The Express edition, which we'll focus on here is freely available through download (see Resources). It is easy to use and suitable when protecting and managing information inside and outside of applications is important for your organization.

Many developers and developer teams require connecting to a SQL database from a Java-based application. Java Database Connectivity (JDBC) is a Java API that allows Java applications to connect to a relational database. SQL Server 2005 provides a Type 4 JDBC driver to connect to the SQL Server 2005 database server. By connecting to the database tables created in the database data can be added and retrieved from those tables.

A SQL Server 2005 connection can be established either from a JDBC application or from a Java EE (formerly, J2EE) application server. The JDBC API is implemented on the Java SE (formerly, J2SE) platform's java.sql package. If a connection is obtained from an application server, a datasource JNDI name is used to obtain a connection. The datasource API is implemented in the javax.sql package. A JDBC connection is represented with a java.sql.Connection object, and a datasource is represented with a javax.sql.Datasource object.

Most application servers, such as JBoss application server, WebLogic server, and WebSphere application server may be configured with SQL Server 2005 using a datasource. To connect to a SQL Server 2005 database, you need to add the SQL Server 2005 JDBC JAR file to the classpath, start the database, and establish a connection with the database.

In this discussion we'll connect with a SQL Server 2005 Express database using the JDBC API, create a table example in the database, add data to the database table, and retrieve data from it. SQL Server 2005 provides a JDBC driver that supports the JDBC 3.0 specification and is a Type 4 JDBC driver.

The Setup
To begin you'll need to download the software required to install SQL Server 2005 (see Resources). If your operating system version is Microsoft Windows Server 2003 SP1 or Microsoft Windows XP SP2, the Microsoft Windows Installer 3.0 should already be preinstalled. You'll also need to download and install Microsoft .NET Framework 2.0; there is a version for the 32-bit platform and a version for the 64-bit platform. Also download SQL Server 2005 Express Edition.

To install the JDBC driver for SQL Server 2005, double-click the sqljdbc_1.0.809.102_enu application, and specify a folder to where you want to extract the application files. The default folder to extract the JDBC driver files to is Microsoft SQL Server 2005 JDBC Driver. SQL Server 2005 Express may be installed on Microsoft Windows 2000 SP4, Windows Server 2003 SP1, and Windows XP SP2. Also, be sure to install Java SE 1.4.2 or 5.0. (See the sidebar, "Installing SQL Server 2005 Express Edition" for the server's install procedure.)

Enable the TCP/IP protocol to connect from a JDBC application. Start the SQL Server Configuration Manager by selecting Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager. In the SQL Server Configuration Manager select SQL Server 2005 Network Configuration > Protocols for SQLEXPRESS. Right-click the TCP/IP node, and select Enable (see Figure 1).

To implement the protocol settings, restart the SQL Server (SQLEXPRESS) service by right-clicking the SQL Server (SQLEXPRESS) service, selecting Administrative Tools > Services, and then selecting Restart.

You'll also need to download and install the SQLServer2005_SSMSEE application to manage the SQL Server 2005. Double-click the application to install SQL Server Management Studio. To connect to the SQL Server 2005 Express database from a JDBC application, create a table in the database, add data to the table, and subsequently retrieve data from the table. Begin by adding the SQL Server 2005 JDBC driver JAR file <Microsoft SQL Server 2005 JDBCDriver>/sqljdbc_1.0/enu/sqljdbc.jar to classpath.

The <Microsoft SQL Server 2005 JDBC Driver> variable is the directory in which the SQL Server 2005 JDBC driver's ZIP file is installed. The driver class for SQL Server 2005 has been slightly modified from the SQL Server 2000 version. In SQL Server 2000 the driver class is com.microsoft.jdbc.sqlserver.SQLServerDriver; in SQL Server 2005 the driver class is com.microsoft.sqlserver.jdbc.SQLServerDriver. Another difference from the SQL Server 2000 version to be aware of is the port number. In SQL Server 2000 the port number is 1433. In SQL Server 2005 the port number is obtained from the SQL Server Configuration Manager. The port number gets reset when the SQLEXPRESS service is restarted. The connection URL for the default SQL Server 2005 database is jdbc:sqlserver://localhost:<port>.

As mentioned previously, <port> is obtained from the SQL Server Configuration Manager. To obtain the <port>, select SQL Server 2005 Network Configuration > Protocols for SQLEXPRESS in SQL Server Configuration Manager. Right-click the TCP/IP node, and select Properties. Select the IP Addresses tab in the TCP/IP dialog. In the IP ALL section, the TCP Dynamic Ports specifies the <port> value (see Figure 2).

Now create a JDBC connection to the SQL Server 2005 database from the Java application, SQLServerJDBC.java. Import the JDBC API classes in the java.sql package. In the JDBC application, load the SQL Server JDBC class:

Class.forName(
  "com.microsoft.sqlserver.jdbc.
  SQLServerDriver");

In the Driver's Seat
Loading the driver also creates an instance of the driver class and registers the driver class instance with the DriverManager. Next, specify the connection URL to connect to the SQL Server 2005 database:

String url = 
  "jdbc:sqlserver://localhost:
  1879";

Obtain a connection with the SQL Server database using the getConnection(String url, String username, String password) method of the DriverManager class:

Connection conn = DriverManager.
  getConnection(
  url, "sa", "sqlserver");

Create a Statement object from the Connection object using the createStatement() method:

Statement stmt = 
  conn.createStatement();

Specify a DDL SQL statement to create a database table named Catalog, and then run the SQL with the execute(String sql) method:

String sql = 
  "CREATE TABLE Catalog(
  CatalogId VARCHAR(25) 
  PRIMARY KEY," +
  "Journal VARCHAR(25), 
  Publisher VARCHAR(25),
  Edition VARCHAR(25), 
  Title Varchar(75), 
  Author Varchar(25))";
stmt.execute(sql);

Create a Statement object and define a DML SQL statement to add a row of data to the Catalog database table. Run the SQL statement to create a table row:

stmt = conn.createStatement();
sql = "INSERT INTO Catalog VALUES(
  'catalog1', 'MSDN Magazine',  
  'MSDN', 'January 2006'," +
  "'Create Reusable Project And 
  Item Templates For Your D
  evelopment Team', 
  'Matt Milner')";
stmt.execute(sql);

Next, retrieve a result set from the Catalog database table with a SQL query, which you define with a SELECT statement. Run the SQL query with the executeQuery(String query) method to obtain a ResultSet object:

String query = 
  "SELECT * from Catalog";
ResultSet rs = 
  stmt.executeQuery(query);

Iterate over the result set to output values of the different columns in the database table:

while (rs.next()) {
  System.out.println(
    "Catalog Id: " + 
    rs.getString("CatalogId"));
  System.out.println("Journal: " + 
    rs.getString("Journal"));
  System.out.println(
    "Publisher: " + rs.getString(
    "Publisher"));
  System.out.println("Edition: " + 
    rs.getString("Edition"));
  System.out.println("Title: " + 
    rs.getString("Title"));
  System.out.println("Author: " + 
    rs.getString("Author"));
  }

After the SQL statements have been run, close the ResultSet object, the Statement object, and the Connection object:

rs.close();
stmt.close();
conn.close();

Listing 1 shows a code example for creating a JDBC connection for the JDBC application, SQLServerJDBC.java; creating a database table; adding data to the database table; and then retrieving data from the database table. SQLServerJDBC.java may be run as a command-line application or in an IDE such as Eclipse. The output from the SQLServerJDBC.java application will look like this:

Catalog Id: catalog1
Journal: MSDN Magazine
Publisher: MSDN
Edition: January 2006
Title: Create Reusable Project And 
  Item Templates For Your 
  Development Team
Author: Matt Milner

The Catalog database table gets created in the default database, named master, in the default schema, dbo (see Figure 3).

A Connection object was obtained using the JDBC API in the JDBC application. A JDBC connection may also be obtained from a DataSource object, which represents a datasource JNDI in an application server. To obtain a connection from a datasource, configure a datasource in an application server. For example, if the datasource JNDI is SQLServerDS, first obtain a DataSource object, and subsequently obtain a Connection object from the DataSource object:

InitialContext initialContext = 
  new InitialContext();
javax.sql.DataSource ds = (
  javax.sql.DataSource)
initialContext.lookup(
  "SQLServerDS");
java.sql.Connection conn = 
  ds.getConnection();

SQL Server 2005 provides database connectivity from a JDBC application with a Type 4 JDBC driver. The driver class and connection URL in SQL Server 2005 are slightly different from SQL Server 2000.

About the Author
Deepak Vohra, a Sun-certified Java programmer and Sun-certified Web component developer, has published numerous articles in industry publications and journals. Contact Deepak at [email protected].