iBatis CRUD Wizard

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

iBatis CRUD Wizard

CRUD is defined as the following functions of persistent storage:

  • Create — Insert a row into a Database Table
  • Read — Selecting information from a Database Table
  • Update — Update a row for a Database Table
  • Delete — Remove a row from a Database Table

The iBatis CRUD Wizard will generate the SQL and the iBatis code for all the CRUD operations for a selected database table in the Teradata Plug-in for Eclipse.  The generated iBatis SQL map can then be used to create a Web Service or used to create a Java application that uses the iBatis frame work. This will give you a quick and easy way to create an application that can do basic operations on a Teradata database table.

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. You should use the single download, Eclipse IDE for Java EE Developers for your installed Eclipse. This download includes additional Eclipse packages for Web application development.

Install Server

An application server must be installed in Eclipse before you can create a Web Service with the iBatis CRUD Wizard. Open the Web perspective in Eclipse and go to the Servers view. Right click in the view and select the New->Server drop down menu.

Select the Apache Tomcat v6.0 Server in the New Server Wizard.

Install the new Apache Tomcat server if required. Follow all installation instructions from Apache and Eclipse.

Create a Teradata Project

The iBatis CRUD Wizard requires a Teradata Project. First, if you have not worked through the article Create a Teradata Project using the Teradata Plug-in for Eclipse, do so now before you continue. Now, select File->New->Other.. from the top menu bar in Eclipse. Then select the Teradata Wizards->Teradata Project from the pop-up dialog. Enter “EmployeeProject” for the name of your new Project in the Teradata Project Wizard. Once this is done select the Finish button in the Wizard and a Teradata Project will be created.

Launch Wizard

The iBatis CRUD Wizard is launched from the DTP Data Source Explorer by right clicking on a table tree node in the explorer and selecting the “Create a CRUD iBatis SQL Map...” menu option.  Select the Employee Table and launch the iBatis CRUD Wizard.

iBatis CRUD Wizard

The first page of the Wizard lets the user specify the location and Name of the iBatis SQL Map to be generated. This page also specifies the options for the CRUD operations to be used in the iBatis SQL Map.  Enter the following values to define your iBatis SQL Map:   

  • “/EmployeeProject/src/resource” for your “iBatis XML Folder”
  • “repository” for your “iBatis XML Package”
  • “EmployeeMap” for your “iBatis XML file name”

The CRUD options by default are all selected. Use the default selections.

The next page of the Wizard is the Domain Object Class Specification screen. This Wizard page lets the user specify the location and Name of the Domain Object class to be generated. This page also specifies the options for the creation of the Domain Object. Enter "domain" for your "Package". The database table name is used for your default name for your domain object.

The final page of the wizard is the Domain Object Members Specification screen. This Wizard page allows modification to the domain object members that represent the columns of the selected table. This screen contains a table with four columns. The first column allows the member names to be modified.  The second column shows the SQL type of the member. The third column shows the Java data type of the SQL type of the original column the Java member represents. The fourth column of the table sets search criteria for Read, Update and Delete CRUD operations. Select the members “id” and “empage” for your search criteria for your CRUD operations.

Generated Code

Once all of the required information is entered into the Wizard, the Finish button can be selected and the iBatis SQL Map is generated.

iBatis DAO with Web Services Wizard

The iBatis DAO with Web Services Wizard will be launched if the option was selected from the iBatis CRUD Wizard. This Wizard will create a DAO and a Web Service derived from the generated iBatis SQL Map.

 

The First page of the Wizard defines the new DAO and options to create a Web Service.

Select the following options:

  • Create WSDL
  • Create Web Service
  • Save Password

The  “Source Folder”, “Package” and “Name” fields will  have default value set for you.

Now hit the Next button.

iBatis DAO Methods

The iBatis DAO Methods Wizard Page allows you to select which SQL actions from your iBatis Map file to be used in your Web service. You can change your return type from returning a single result set object to returning a list instead. Once you hit the next button, Your DAO and Web Service Definition files will be created.

Web Service Creation

The next page is the standard WTP Web Services Wizard. Set your Client to test. Once you hit the Finish button your Stubs and Skeletons will be created for your Web Service. The Implementation stub will be modified to use the new DAO you just created.

Web Service Client 

The Web Service client will come up ready to use and connected to your Teradata database.

The iBatis CRUD Wizard in just a few steps has created a functional Web application for the Employee Table. The iBatis View Web Service Wizard is a subset of the iBatis CRUD Wizard. The iBatis View Web Service Wizard generates an iBatis SQL Map which performs SQL select statements on a selected view. The select statements will have different search criteria depending on what is specified in the Wizard. The generated iBatis SQL Map can then be used to create a Web Service or used to create a Java application that uses the iBatis frame work.These Wizards take the grunt work out of programming and gives you a head start on enabling an application for your Database.  You can now spend more time implementing your business logic and less time creating the plumbing and the basic elements of your application.

Appendix A

Sample files

EmployeeTable.sql

CREATE SET TABLE ACM.Employee ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
id INTEGER NOT NULL,
empname VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
dob DATE FORMAT 'YY/MM/DD' NOT NULL,
empage INTEGER)
PRIMARY INDEX ( id );

EmployeeMap.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="repoistory.EmployeeMap">

<!-- Define object mapping -->
<resultMap class="domain.Employee" id="Employee">
<result column="id" jdbcType="INTEGER" property="id" />
<result column="empname" jdbcType="VARCHAR" property="empname" />
<result column="dob" jdbcType="DATE" property="dob" />
<result column="empage" jdbcType="INTEGER" property="empage" />
</resultMap>

<!-- Define select SQL statement -->
<select id="findAllEmployee" resultMap="Employee">
select * from "ACM"."Employee"
</select>

<!-- Define insert SQL statement using iBatis update -->
<update id="insertEmployee" parameterClass="domain.Employee">
INSERT INTO "ACM"."Employee" ("id", "empname", "dob", "empage") values(#id#, #empname#, #dob#, #empage# )
</update>

<!-- Define select SQL statement -->
<select id="findEmployeeById" parameterClass="java.lang.Integer" resultMap="Employee">
select * from "ACM"."Employee" where "id" = #id#
</select>

<!-- Define update SQL statement -->
<update id="updateEmployeeById" parameterClass="domain.Employee">
update "ACM"."Employee" set "empname"=#empname#, "dob"=#dob#, "empage"=#empage# where "id"=#id#
</update>

<!-- Define delete SQL statement -->
<delete id="deleteEmployeeById" parameterClass="java.lang.Integer">
delete "ACM"."Employee" where "id" = #id#
</delete>
<!-- Define select SQL statement -->
<select id="findEmployeeByEmpage" parameterClass="java.lang.Integer" resultMap="Employee">
select * from "ACM"."Employee" where "empage" = #empage#
</select>

<!-- Define update SQL statement -->
<update id="updateEmployeeByEmpage" parameterClass="domain.Employee">
update "ACM"."Employee" set "id"=#id#, "empname"=#empname#, "dob"=#dob# where "empage"=#empage#
</update>

<!-- Define delete SQL statement -->
<delete id="deleteEmployeeByEmpage" parameterClass="java.lang.Integer">
delete "ACM"."Employee" where "empage" = #empage#
</delete>

</sqlMap> <!-- Do not edit or add anything below this comment -->

4 REPLIES
Teradata Employee

Re: iBatis CRUD Wizard

I found if you increase the memory Eclipse uses by increasing the memory settings in the eclipse.ini file, the Web Services Wizard runs much better. (The more the better)

-startup
plugins/ org.eclipse.equinox.launcher_1.1.1.R36x_v20101122_1400 .jar
--launcher.library
plugins/ org.eclipse.equinox.launcher.win32.win32.x86_64_1.1.2. R36x_v20101222
-product
org.eclipse.epp.package.jee.product
--launcher.defaultAction
openFile
--launcher.XXMaxPermSize
512M
-showsplash
org.eclipse.platform
--launcher.XXMaxPermSize
512m
--launcher.defaultAction
openFile
-vmargs
-Dosgi.requiredJavaVersion=1.5
-Xms512m
-Xmx1024m
Teradata Employee

Re: iBatis CRUD Wizard

If you have a 64 bit machine, you should take advantage of it. The WTP is very memory intensive when creating a Web Service. You can increase how much memory is used with Eclipse if you install the 64 bit versions of Java, Eclipse and the Apache Tom cat server. Once you have done all this, you can increase the memory settings beyond the limitations of a 32 bit machine in your eclipse.ini file.

-startup
plugins/ org.eclipse.equinox.launcher_1.1.1.R36x_v20101122_1400 .jar
--launcher.library
plugins/ org.eclipse.equinox.launcher.win32.win32.x86_64_1.1.2. R36x_v20101222
-product
org.eclipse.epp.package.jee.product
--launcher.defaultAction
openFile
--launcher.XXMaxPermSize
1024M
-showsplash
org.eclipse.platform
--launcher.XXMaxPermSize
1024m
--launcher.defaultAction
openFile
-vmargs
-Dosgi.requiredJavaVersion=1.5
-Xms512m
-Xmx2048m
Teradata Employee

Re: iBatis CRUD Wizard

WSDL and JSP validation can slow down the Web Services Wizard in Eclipse. You can disable WSDL and JSP validation in Eclipse preferences if validation is slowing your system down.

Re: iBatis CRUD Wizard

Nice one