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.
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.
Back to top