Introduction

In today's data-driven world, the seamless integration of external databases and mainframe data into web applications is crucial for organizations seeking to harness the full potential of their information resources. Leveraging the power of Microsoft SQL as the database platform and utilizing JDBC for connectivity, this blog explores the essential strategies and techniques that bridge the gap between disparate data sources, enabling dynamic and responsive web applications that deliver valuable insights and drive business success.

Consider a scenario where Customer information is distributed in a Database. You would want to modernize the end-user experience so that they can view the accumulated information on a web dashboard/UI using IBM HATS.

Database 

Table

A sample of data is present in the table.

Stored Procedure

The stored procedure usp_SEL_CustomerInformation is designed to retrieve specific customer information based on a provided CustomerID parameter. We will use this procedure call in our Java program.

Backend Java code to connect with JDBC           

DAOAccessFactory

Create a class like DAOAccessFactory, which allows you to connect with the SQL Database using the details like user name, password, URL, and driver name for the SQL server.

For more details on building MSSQL JDBC URL:

https://learn.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-ver16

These properties have been hardcoded in this Java code for the sample, but you should move these to a property or an XML file.

CustomerInformation Class

The CustomerInformation class is used to represent customer information data.

CustomerInfoDAOJDBC

Provide an implementation to find customer details by calling the SQL procedure ‘usp_SEL_CustomerInformation’. Here is a sample code:

Final Implementation

In your HATS project, create a business logic connecting to the DAO layer and retrieving the Customer details. Assume you have captured the Customer ID in a global variable. This variable can be accepted in multiple ways, like:

As an HTTP parameter passed as a global parameter override to the HATS application.

Extract from the Customer detail emulator green screen as a macro global variable.

Use the DAOFactory class to obtain a data access object and retrieve customer information from the SQL database.

Now, you can include these values from global variables in your transformation or business logic for further modernization. 

This sample implementation describes ways to connect your HATS application with an external data source. You can use a framework like Hibernate to simplify the interaction with the database.