Using the Spring Framework with the Teradata Plug-in for Eclipse

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Teradata Employee

Using the Spring Framework with the Teradata Plug-in for Eclipse

This will be a series of articles explaining how the Spring framework can be used with the Teradata Plug-in for Eclipse to create a data access layer for your business services. The Java Bean Wrapper wizard allows the user to quickly generate a Java Bean class for a given SQL statement or stored procedure. Also the Wizard has an option to generate a Java Bean which can be run with the Spring Data Access Object (DAO) framework. By calling a generated Java Bean from a DAO, the Bean will have access to the Spring transaction management. Also the Java Wrapper Beans are reusable components which could be used in different DAOs. The Bean Helper Classes can be used as Spring domain objects. Also the Bean will facilitate the support of OUT parameters for stored procedures and multiple result sets inside of a Spring DAO.

This article will show how to setup a project that uses Spring DAOs with the Teradata Plug-in for Eclipse. This article will also show how to create Java Bean Wrapper classes that can be called from a Spring DAO using the Java Bean Wrapper Wizard.

Prerequisite for this Article

If you have not worked through the guide Getting Started with Teradata Plug-in for Eclipse, do so now before you continue.

Spring DAO Project Setup

The Java Bean Wrapper Wizard creates classes that require the Spring framework. Also in an article later in this series an example will be showing a unit test of a Spring DAO using TestNG. The following is the Teradata recommended project setup to stream line the usage of classes generated by the Teradata Plug-in using the Spring and TestNG frameworks.

Create a Teradata Spring DAO Project

To create the Spring DAO project start Eclipse and open up your workspace.

• Select File -> New -> Project -> Java -> Java Project -> Next

• In the New Java Project Dialog fill in the Project name: “Employee_Project"

• Select the “Next” button

• In the Java Settings dialog, select the "src" directory and select "Remove source folder 'src' from the build path"

• Select the "Create a new source Folder" link.

• Define the Folder name: as "src/java" and select the "Finish" button

Repeat for the rest of the Source Folders we will use: 

• src/config

• src/resources

• test/java

• test/config

Finally change the Default Output folder to “Employee_Project /build” and select the “Finish” button.

Add external Libraries (JAR files) to the "/lib" directory.

Within the Spring DAO project where we wish to use external Libraries (JAR files) we place them in a User Library directory (/lib) that can then be referenced as part of the Class Path of the application.

• Right click on "Employee_Project" and select New > Folder

• Set the Folder name: = 'lib' and Select the “Finish” button.

Now when we need to add an external library such as the Spring jar we can download a copy of the required library (typically these downloads come as a large .ZIP or .TAR file that requires you to extract the required jar files using an approach appropriate to your Operating System, such WinZip on Windows), and save the required jar files to the /lib directory and then add them to the application classpath.

Add spring-framework JARs

We will be using the Spring framework to use with the classes generated by the Java Bean Wrapper Wizard. Download the spring-framework version 2.5.6 with dependencies from http://www.springframework.org/download and extract spring.jar, spring-test.jar, commons-logging.jar and testng-5.8.jar to your /lib directory

The jar locations in the spring-framework-2.5.6-with-dependencies.zip are located in the following directories:

• spring.jar - spring-framework-2.5.6\dist

• spring-test.jar - spring-framework-2.5.6\dist\modules

• commons-logging.jar - spring-framework-2.5.6\lib\jakarta-commons

• testng-5.8-jdk15.jar - spring-framework-2.5.6\lib\testng

Create a User Library classpath reference to the JAR's in /lib

Once all the libraries / JAR files have been added to the /lib directory they can be added to the project classpath within Eclipse, this allows Eclipse to make these classes available within the editing environment and compile all of the code without errors.

• Right click on Employee_Project and select Build Path > Configure Build Path...

• In the Properties for Employee_Project dialog select the "Libraries" tab -> Add Library... -> User Library

• Select the "Next" button, User Libraries..., New... and provide a User library name like “EmployeeProjectLibrary"

• Use the “Add JARs..” button to browse to the "Employee_Project /lib" directory and include all of the JAR files on the classpath (via the EmployeeProjectLibrary).

• The “EmployeeProjectLibrary” and it's associated JAR files will appear in Project Explorer like this.

Adding Packages

You will be creating Java classes with the Java Bean Wrapper Wizard. Your Java classes will require packages as part of good programming practices. Go to select the “src/java” source folder. Right click and select New->Package menu item. This will bring up the new Java Package Wizard. Please enter “employee.repository” for the name of the package.

Once this is done, select the “Finish” button of the Wizard. Repeat this process and create the package “employee.domain”.

 

Create a Table

In this article, a table will need to be created for a schema because the Java Wrapper Beans will be accessing the database using SQL insert and select statements on a table.

Launch Table Creation Dialog

Go to the top of the Eclipse IDE and select the pull down menu Window ->Perspective->Other…->Database Development. Select the database profile in the Data Source Explorer. Right click and select the menu item "Connect". Open the tree in the Explorer and select the “Table” tree node for the schema in which you wish to create your table. Right click on the “Table” tree node and select Teradata->Create Table.

Create Table Dialog

At this point, you should have the “Create Table” dialog up. Enter "Employee" in the table name field in the dialog. Now go to the “Column Name” field and enter "empID". Now select the “INTEGER” data type. Go to the “Value must be unique” toggle and select it. Hit the “Apply” button. Now hit the “New” button in the columns list. Enter "empName" in the “Column Name” field. Select the VARCHAR data type and enter 30 for the size of the type option. Hit the “Apply” button. Now hit the “New” button in the columns list. Enter "empDept" in the “Column Name” field. Select the "VARCHAR" data type and enter 30 for the size of the type option. Hit the “Apply” button. Now hit the “New” button in the columns list. Enter "empJob" in the “Column Name” field. Select the "VARCHAR" data type and enter 300 for the size of the type option. Hit the “Apply” button one final time.

Run SQL

Now select the “SQL Preview” Tab in the Create Table Dialog and select the “Run SQL” button. This will create the table under the selected schema.

Enter Data

The new table now will require data. Go to the DTP tree in the Data Source Explorer and select the "Table" tree node “employee”. Right click the table node and the select Data->Edit menu item. The Edit data table will appear in the Eclipse dialog. Enter the data that appears in the image below. Once you are done right click and select the “Save” menu item.

SQL Queries

This series of articles is going to use the SQL queries below for accessing and updating the “employee” table you just created.

Select * from employee where empId = 100001;

select * from employee where empDept = 'Product Development';

select * from employee;

select count(*) from employee;

update employee set empName ='Mike Smith' , empDept = 'Product Development', empJob = 'Manager' where empId = 100001;

These SQL queries will be entered into the SQL Editor for Teradata Plug-in for Eclipse. Then the queries will be wrapped into a Java Bean class using the Java Bean Wrapper Wizard. Finally, the wrapped Java Beans will be called from a Spring DAO to give you a data access layer for your business process. You should make sure you are logged on to a database where you have full access to the tables used in these queries.

Create SQL File

Go to the Java Default view and select the project “Employee_Project” that you created . Right click and select the menu item New->Other…. This will bring up the Select Wizard. In the Wizard tree, select SQL Development-> SQL File.

Now select the “Next” button. Enter “employee_sql” in the “File name” text box. Now select the “Finish” button.

SQL Editor

The SQL Editor should appear in the Eclipse IDE. Go to the connection profile group and select “Terdata_13.x” from the “Type” combo box. Now select the profile name you have created from the “Name” combo box. Go to the text area in the SQL Editor and enter the queries that were shown previously.

Parameterization

The queries will use the same kind of parameterization JDBC uses, replacing actual values with question marks. This will make the queries reusable and ready to be used with the Java Bean Wrapper Wizard. Please replace the value “100001” in the where clause in the first query with a “?”; Then repeat this process for the rest of the queries. Your queries should now look as follows:

Launch Java Bean Wrapper Wizard

Now select the first SQL Query and right click. Select the menu item “Create a Java Bean…”. You should now see the "Class Specification" page for the Java Bean Wizard.

  • Enter “Employee_Project/src/java” for the Source folder:
  • Enter “employee.repository” for package name.
  • Enter “GetEmployeeById” for class name.

Now select the toggle button “Call From a Spring DAO”.

Generate Helper Classes

Select the "Next" button until you get to the “Generate Helper Classes” Wizard Page. This page uses the meta data from your query to generate helper classes for the Java Bean.

Select the generate button and the "Configure Parameters" dialog will pop up. Enter “1” in the value column of the dialog and select the “OK” button. Now select the "Next" button on the “Generate Helper Classes” Wizard Page.

Edit Classes

You will be now be on the "Edit Classes" Wizard Page. Select “GetEmployeeID” in the “Classes” list. Now go to the table at the bottom of the page and replace the text “parameter1” with the text “id” in the “Member Name” column.

Domain Package

Now select “GetEmployeeDomainObject1” in the Classes list in the Edit Wizard page. Once this is done, select the “Browse …” button for the package of the selected class. The “Select Package” pop up dialog will come up. Please select the package “employee.domain” and hit the “OK” button.

New Domain Class name

All of the Java Bean Wrapper classes are going to share the same domain class. Enter “Employee” for the text of the selected class. This will change the name of the class.

Once this is done, select the “Next” button.

Connection

You should now be on the "Define Connection" Wizard page. The Java Bean Wrapper that is generated will use the data source defined here when it is called from a Spring DAO (Note: the Java Wrapper Bean is generic and it can run independently from Spring). In this case, selecting the connection type is for reuse. Select the “No Authentication” radio button. Once this is done, select the “Finish” button.

Generated Code

Once the “Finish” button is selected for the Java Bean Wrapper Wizard, the generated code for the Java Bean Wrapper will appear in the Java Editor of the Eclipse IDE.

The new Java Bean Generated code implements org.springframework.jdbc.core.CallableStatementCallback interface. An example of this is the following:

This interface allows the Bean to be called from a Spring DAO by using org.springframework.jdbc.core.JdbcTemplate.execute(String callString, CallableStatementCallback action). A sample of this is the following:

Note: The next article will show how the above code will be generated by the Spring DAO Wizard.

Update castReturnValue

The Java Bean Wrapper class now has a static method named castReturnValue that can be used with the Spring DAO Wizard. This method casts the object returned by the execute method from the JDBC template in the DAO class.

The castReturnValue can be customized so it can be mapped to any type returned by the DAO method. Make the following changes shown below in the image to your Java Bean Wrapper class.

Create More Java Bean Wrapper Classes

The example for the next article will need more than one Java Bean Wrapper class. Also typically a data access layer for a business process is usually made up of several queries.

Go back to the SQL Editor and select the next query repeating the process of generating a Java Bean by using the Java Bean Wrapper Wizard.

SELECT *
FROM employee
WHERE empDept = ?;

In the Class Definition Wizard page, use the "package employee.repository" and name the class "GetEmployeesByDept". Also select the “Call From a Spring DAO” check box. On the "Edit Classes" Wizard page, select the main class GetEmployeesByDept and change "parameter1" to "dept". Also select the domain class and change the package to “employee.domain” and class name to “Employee”. When you select the “Finish” button, the "Manage Java Bean Files" dialog will pop up. Select the “Use existing domain objects” radio button. Once this is done, select the “OK” button and your class will be generated.

Go back to the SQL Editor and select the next query repeating the process of generating a Java Bean by using the Java Bean Wrapper Wizard.

SELECT *
FROM employee;

Name the class GetAllEmployees. Use the same packages for your classes and rename your domain class to Employee.

Go back again  to the SQL Editor and select the next query repeating the process of generating a Java Bean by using the Java Bean Wrapper Wizard.

SELECT COUNT(*)
FROM employee;

Name the class GetNumberOfEmployees. Use the same packages for your classes. Rename your domain class to NumberOfEmployees and change the “Count(*)” member name of the domain class to “count”.

Go back again to the SQL Editor and select the next query repeating the process of generating a Java Bean by using the Java Bean Wrapper Wizard.

UPDATE employee SET  empName = ?,
empDept = ?, empJob = ?
WHERE empId = ?;

Name the class UpdateEmployee. Also select the option "Java Bean does not return result sets" on the Class Definition Wizard page. On the Edit class page, update the parameters for the Java Bean class the same as the image below.

Edit GetNumberEmployees

Edit GetNumberEmployee class and change the castReturnValue method the same as the image shown below.

Conclusion

This article has shown how to setup a project that uses Spring DAOs with the Teradata Plug-in for Eclipse. This article also has shown how to create Java Bean Wrapper classes that can be called from a Spring DAO using the Java Bean Wrapper Wizard. 

3 REPLIES
Enthusiast

Re: Using the Spring Framework with the Teradata Plug-in for Eclipse

Extremely helpful.

How about if I want to go a step further and convert my DAOs into web services? Is there any support from the TD plug in for the same? Or other steps that you would recommend?

I mainly want to be able to use the DAOs in form of web services. How do I go about doing that?
Teradata Employee

Re: Using the Spring Framework with the Teradata Plug-in for Eclipse

The Friday Night Project series of articles go over how to build a Web service.

http://developer.teradata.com/applications/articles/the-friday-night-project-8-tza-insuranceservice
Teradata Employee

Re: Using the Spring Framework with the Teradata Plug-in for Eclipse

I am going to come out with the second article of this series which explains how to use the Teradata Spring DAO Wizard, but for people who cannot wait they should read the Help Contents for the Spring DAO Wizard under the topic Teradata Plug-in from inside the Eclipse IDE with our product installed.