Chapter 14 - Developing: Applications - Migrating Java

Article
12/05/2007

Introduction and Goals

This chapter contains a detailed discussion of changes that must be made to Java applications when the database is migrated from Oracle to Microsoft® SQL Server™. At the conclusion of this chapter, the Java application should be capable to successfully connect to the SQL Server database that was migrated from Oracle. The solution can then be tested.

Java is a portable language. The capability for the Java programming language to interact with an RDBMS such as Oracle and SQL Server is provided by the Java Database Connectivity (JDBC) API. Applications written in Java can execute SQL statements, retrieve results, and propagate changes back to the underlying data source using the JDBC API.

The combination of the Java platform and JDBC API allows a program to operate on a number of platforms, and with any number of SQL-supported databases, without modifying the source code.

When migrating from an Oracle to a SQL Server environment, there will still be some alterations made to the application. These basic changes will need to be made whether the Java application interoperates with SQL Server from a UNIX environment, or if the Java application is ported to Windows®.

As discussed in the "Define the Solution Concept" section of Chapter 2, "Envisioning Phase," there are four different strategies available for transitioning applications in an Oracle to SQL Server migration project. The strategies are:

Interoperate the application with UNIX
Port or rewrite the application to the Microsoft .NET platform
Port or rewrite the application to the Microsoft Win32® platform
Quick port using the Microsoft Windows Services for UNIX 3.5 platform

Because of some of the unique characteristics of Java, some of these strategies are more logical than others. For instance, because Java can be ported to Windows, there is no need to rewrite the application in the .NET framework or for the Win32 environment. Based on the available migration strategies, two scenarios can be developed to migrate the Java application. These scenarios are:

Scenario 1: Interoperating Java on UNIX with SQL Server

If the business requirements do not include eliminating the UNIX environment, an interoperation strategy can be implemented quickly. Few, if any, changes need to be made to the source code and it may be necessary to install a new driver to allow the Java application to connect to a SQL Server database. If the migration is performed in phases or for multiple applications, interoperation can be used as an interim step.
Scenario 2: Porting the Application to Win32

Java applications can also be ported to run natively on the Windows platform. As with interoperation, few changes need to be made to the source code.

Scenario 1: Interoperating Java on UNIX with SQL Server

Interoperation between a UNIX-based Java application and a SQL Server database is possible because of the availability of a driver that interfaces between these technologies. The JDBC driver provides the cross-RDBMS connectivity needed.

Case 1: Interoperating a Java Application Using the JDBC Driver

Because of the construction of the JDBC API, which exposes a common set of methods that are not affected by the change of source database, this type of migration is usually a simple process. Most changes made relate to the connectivity.

To interoperate a Java application using the existing JDBC driver, follow these steps:

Evaluate the current driver.

Before beginning the migration process, evaluate the existing JDBC driver that is in use and its capability to operate with SQL Server. Some considerations include:
- Will the current JDBC driver interoperate with SQL Server? If this information is not known, check with the vendor. Sun Microsystems also maintains a database of SQL Server compliant JDBC drivers. For more information, refer to https://servlet.java.sun.com/products/jdbc/drivers.
  - If the current JDBC driver will interoperate with SQL Server, what are the performance characteristics of the driver in the SQL Server environment? Try to perform prototype tests of the driver to ensure that the level of performance is acceptable.
  - What features of the driver are utilized by the Java application, and are these supported for SQL Server? For more information on specific Java extensions used by your JDBC driver, refer to your vendor documentation.
- What type of JDBC driver is required (Type 1, 2, 3, 4)?
  
  If a new driver is needed, there are several different JDBC drivers commercially available. Each has slightly different features and characteristics.
  
  Some commonly used JDBC drivers include:
  - JSQLConnect from JNetDirect, which is available at https://www.jnetdirect.com/products.php?op=jsqlconnect.
  - SQL Server 2000 Driver for JDBC. Microsoft offers this native driver which will work on both UNIX and Windows. This driver is available at https://www.microsoft.com/downloads/details.aspx?FamilyId=07287B11-0502-461A-B138-2AA54BFDC03A&displaylang=en.
Determine how the application connects to the data source. Connections can be achieved using the DriverManager class or with a datasource implementation.
- If connectivity is obtained using the DriverManager class, two changes are needed to connect with the SQL Server database. These changes are:
  - Loading the JDBC driver
  - Making the connection
    
    The following example shows these changes:
```
// Loading the driver
```

Class.forName("DriverClassName"); // Making the Connection String url = "jdbc:odbc:DSN"; String user = "sUser"; String password = "sPwd"; Connection con = DriverManager.getConnection(url, user, password); //............... //............ // Closing the Connection con.close();

        In this example code, the URL uses the syntax
        
        <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">jdbc:&lt;subprotocol&gt;:&lt;subname&gt;</pre>

. Using the connection object FakePre-e4ee40d5b209401393815b8d64c1916b-0cc174676344497f8c6af5c70ad3a58b, you can create JDBC statements and pass them to the RDBMS.

  - If connectivity is obtained through a datasource, the application server should be appropriately configured.
    
    Before using a datasource object, it must be deployed. The following list describes the deployment steps:
    
    1.  Create an instance of the DataSource class.
    
    2.  Set the datasource properties.
    
    3.  Register the datasource with a naming service that uses the Java Naming and Directory Interface (JNDI) API.
        
        Datasource deployment is usually performed by the system administrator. A JDBC driver that supports JDBC 2.0 API will provide different types of datasource implementations which can be used for connection pooling and distributed transactions.
        
        To obtain connectivity, the Java application performs a lookup of the logicalName. This information can be retrieved by passing the user name and password through the **getConnection()** method. An example of this method is presented as follows:
        
        <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">Context ctx = new InitialContext();

DataSource ds = (DataSource)ctx.lookup("jdbc/dbServer"); Connection con = ds.getConnection("sUser","sPwd");

Configure the application server.

If third-party JDBC drivers are used to connect to SQL Server, the drivers must be set in the CLASSPATH variable so the driver classes can be loaded. The JDBC driver installed for SQL Server will install a driver jar file, which contains driver classes for the database interaction. If the jar file is missing in the classpath, then the application will flag a 'class not found' exception.

Based on the application's architecture, the CLASSPATH must be set. The syntax of this setting can vary between applications, servlets, JSPs, EJBs, and applets.
1. For applications run directly from the operating system prompt, make sure that the driver can be found in the system's CLASSPATH. This can be performed by moving the file to its original location, which is already listed in the classpath, or adding the driver's current location to the CLASSPATH.
2. For applications run within the IDE, the driver classpath has to be set in the vendor's IDE classpath. Refer to your vendor’s IDE documentation.
3. For servlets and JSPs run within a servlet/JSP engine such as Tomcat, the classpath can be set in the vendor's classpath setup screens or by copying the driver jar file into the LIB folder of the engine’s installation.
4. For EJBs, the JDBC driver jar file should also be set in the EJB vendor's classpath.
5. For applets that run within a Web browser, copy the JDBC driver jar to the Web server root and specify the driver jar file in the applet’s HTML archive tab. The following HTML code example shows this option used on an applet tag.
```
<applet ... archive="JSQLConnect.jar">
```
If the SQL statements passed to the JDBC methods use any Oracle-specific syntax, then these items will need to be updated to reflect T-SQL syntax. Migration of PL/SQL to T-SQL is covered in detail in Chapter 11, "Developing: Applications — Migrating Oracle SQL and PL/SQL."
The metadata information returned by the metadata methods should refer to SQL Server and the new JDBC Driver.

To discover information about a database, a DatabaseMetaData object must be obtained. ResultSetMetaData provides information about the ResultSet, such as number of columns returned, an individual column's suggested display size, column names, and column types. If any of these metadata are used in the application, they have to be validated for integrity of the return values.

To discover information about a database, a DatabaseMetaData object must be obtained. The getMetaData method on the Connection object returns a DatabaseMetaData object.
```
DatabaseMetaData dbmd = con.getMetaData();
```
ResultSetMetaData provides information about the ResultSet. Use the getMetaData method on the resultSet to get the ResultSetMetaData.
```
ResultSetMetaData rsmd = rs.getMetaData();
```
After the application changes are complete, create a new release build and fully test the application.

Scenario 2 — Porting the Application to Win32

Java is portable, so when the application is migrated to Windows, the Java language features remain the same. Changes should be minimal. Most of the changes listed in the following procedure are similar to those performed under the interoperation scenario described in the "Scenario 1: Interoperating Java on UNIX with SQL Server " section earlier in this chapter.

To port the application to Win32, follow these common steps:

Install Java on the Windows server.

If the migration path includes porting the Java application to the Windows environment, then the Java application server should be installed on Windows. Ensure that the hardware satisfies the installation prerequisites for the application server.
Determine if the existing JDBC driver will perform the needed functions, or if a new JDBC driver will be required.
Configure the application server for use with Windows. Update the path and classpath information.
Test the installation of the application server using the test utility provided with the application server.
Determine how the application connects to the data source.

SQL Server has two types of authentication, Windows authentication and SQL Server authentication. When the application exists on the UNIX platform, the Windows authentication method is not possible without the installation of additional software such as the Vintela Authentication Services (VAS) product from Vintela (https://www.vintela.com/products/vas/). However, with the application migrated to the Windows platform, the Windows authentication feature of SQL Server can be taken advantage of.

Windows authentication is specified using the connection property trustedAuthentication. The property may be set in either a driver manager connection string or a datasource. There is no need to specify the user and password properties when using trusted authentication.

Windows authentication can be used by the JDBC clients on Windows only.
Move the Java code from UNIX to Windows. All the files and folders which the Java application interacts with should be migrated to Windows. Make sure that the paths in the Java application and the configuration files point to the corresponding folders in the Windows file system. If the Java application uses any native code written in other languages, then that code also has to be migrated to Windows.
Oracle-specific SQL and stored programs will need to be rewritten in T-SQL syntax. Migration of PL/SQL to T-SQL is covered in detail in Chapter 11, "Developing: Applications — Migrating Oracle SQL and PL/SQL."
The metadata information returned by the metadata methods should refer to SQL Server and the new JDBC Driver.
After the application changes are complete, create a new release build and fully test the application.

Chapter 14 - Developing: Applications - Migrating Java

On This Page

Introduction and Goals

Scenario 1: Interoperating Java on UNIX with SQL Server

Case 1: Interoperating a Java Application Using the JDBC Driver

Scenario 2 — Porting the Application to Win32

Additional resources