Configuring JDeveloper with SQL Server

1. As I mentioned in the previous section, the first step is to add the JAR file for the JDBC driver class to the classpath by creating a new library for the SQL Server JAR files. Open JDeveloper and Select Tools>Default Project Properties. In the Default Project Properties dialog select the Libraries node and click the Add Library button (see Figure 8).

Figure 8. Adding a library.

Figure 9. Adding a user library.

Figure 10. Creating a library.

2. In the Add Library dialog, select the User node, and click on the New button (see Figure 9).
3. In the Create Library dialog specify a Library Name, such as "SQLServer." Select the Class Path node and click the Add Entry button. For SQL Server 2000, add the msbase.jar, mssqlserver.jar, and msutil.jar JAR files to this dialog. For SQL Server 2005 Express, add sqljdbc.jar (see Figure 10) and click OK. Click OK several more times until you’ve exited the Libraries dialog (see Figure 11).

   Figure 11. A new library.

   Figure 12. Specifying a connection type.

   Figure 13. Registering a JDBC driver.

4. Next, configure a JDBC connection with the SQL Server database. In the Connections Navigator right-click on the Database node and select New Database Connection to start the Create Database Connection wizard.
5. In the Type dialog specify a Connection Name and select 'Third Party JDBC Driver' in the Connection Type field (see Figure 12). Click on the Next button.
6. In the Authentication dialog specify sa as the Username and the Password as the password used to install the SQL Server database.
7. In the Connection dialog, locate the Driver Class field and specify the driver class, com.microsoft.jdbc.sqlserver.SQLServerDriver (2000), or com.microsoft.sqlserver.jdbc.SQLServerDriver (2005 Express). Click on New to add a driver class. In the Register JDBC Driver dialog, specify driver class in Driver Class field (see Figure 13). In the Library field select the SQLServer library that you configured in the last section (I called mine "SQLServer"). Click OK to add it.
8. In the URL field specify the connection URL as shown in Figure 14. This portion of the process is the same regardless of which application server you are using and the specific URL and port values required are explained in the sidebar "Obtaining the Port Values.")

Figure 14. Specifying connection parameters.

Figure 15. Testing the connection.

Figure 16. A new database connection.

9. Now you can test the connection using the Test Connection button (see Figure 15). If you've done everything correctly, a JDBC connection is established with the database. Click on the Finish button.

You’ve added a node for this connection to the Database node in the Connections Navigator as shown in Figure 16. This connection is also available as jdbc/SQLServerConnectionDS datasource in a JSP or a servlet. To obtain a connection from the datasource in a JSP, add to the web.xml of the JSP application:

<resource-ref>
  <res-ref-name>jdbc/SQLServerConnectionDS</res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
</resource-ref>

You can obtain a JDBC connection in a JSP or a servlet using this code:

InitialContext initialContext = new InitialContext();
 javax.sql.DataSource ds = (javax.sql.DataSource)
 initialContext.lookup("java:comp/env/jdbc/SQLServerConnectionDS");
   java.sql.Connection conn = ds.getConnection();