Create an iBatis SQL Map with 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

Create an iBatis SQL Map with Teradata Plug-in for Eclipse

The iBatis framework is a lightweight data mapping framework and persistence API. It couples objects with stored procedures or SQL statements using a XML descriptor. The iBatis SQL Map wizard allows you to quickly generate an iBatis SQL map for a given SQL statement or stored procedure.

Prerequisite for this Article

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.

Launch iBatis SQL Map Wizard

The iBatis SQL Map Wizard is launched by selecting a SQL Statement in the DTP SQL Editor and right clicking. A menu will pop up and the menu item “Create an iBatis SQL Map” must be selected to launch the Wizard.

Create iBatis SQL Mapping XML File Wizard Page

The Create iBatis SQL Mapping XML File screen is the first page of the wizard. This page Defines the location, name of the iBatis SQL mapping file and the mapping name for the selected query. The option of appending the XML mapping to an existing file is default. This page also allows you to set the SQL statement type and Result set options.

Domain Object Source Location Wizard Page

The Domain Object Source Location screen is the next page of the wizard. This page defines the source folder and package of the domain object class for the SQL mapping. The domain object is where the results of the executed query for the SQL map are stored.

Note: This page will only appear if the SQL statement returns domain objects.

Edit SQL Wizard Page

The Edit SQL screen is the next wizard page. This page allows you to edit the selected SQL. This screen allows you to change SQL to use parameter substitution (replace data values with “?'). This will make the SQL more generic and more useful for the iBatis SQL mapping.

Generate Domain Classes Wizard Page

The Generate Domain Classes screen is the next page of the wizard. This page generates the domain objects to hold the results of the selected query. Result set Meta data is required to generate the domain objects. The query must be executed to create the Meta data for the result sets.

Configure Parameters

This Wizard page also allows you to configure parameters for the SQL map. You can change the name of parameters used with the SQL query and enter a data value if the query requires it to generate the result Meta data. Note: If the SQL query has more than one parameter, a “java.util.Map” will be mapped to the parameters.

Parameter Class

If you select the “Parameter Class” button, the Select parameter class pop up dialog will come up.

Select Parameter Class

You can select a new Parameter map class by selecting the “Add” button in the pop up dialog and select a class from the source tree of the “Select Java Bean” Dialog.

Once you select the “OK” button of the “Select Parameter” dialog, the new Mapping class will be used.

You then can select any “getter” method from the new mapping class for the selected query parameters.

Generate Domain Classes complete

You then can select the generate button and the domain classes will be generated and shown in the Generate Domain Objects list.

Edit Classes Wizard Page

The Edit Classes screen is the next page in the Wizard. This page allows you to edit the Java classes which are going to be generated. This page allows you to edit the member names, package names and class names.

 

The example below shows the domain object being renamed to "Employee" in the Edit Classes Wizard page.

 

Note: If the domain object already exists in the iBatis XML descriptor as a result map, the domain object will default to the name that is in the result map.

Review iBatis SQL Map Classes Wizard Page

The Review iBatis SQL Map Classes screen is the next page in the wizard. This page allows you to review the mapping that is going to be generated for the iBatis SQL Map file.

Finish Button

The “Finish” button in the Wizard can be selected once the domain objects have been generated. After the “Finish” button is selected, the iBatis XML mapping file and the domain objects will be generated.

 

iBatis SQL Map Wizard Tutorial Example

You can now try using the iBatis SQL Map Wizard. First create the following table on your Teradata database:

CREATE MULTISET TABLE guest.Employee ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
EmpNo INTEGER NOT NULL,
Name VARCHAR(32) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
DeptNo INTEGER NOT NULL,
JobTitle VARCHAR(100) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
Sex CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
EdLev BYTEINT NOT NULL)
PRIMARY INDEX ( EmpNo );

Once you have created your table, enter the following SQL statements in your SQL Editor in Eclipse.

SELECT *
FROM employee;

INSERT INTO "guest"."Employee"
("EmpNo", "Name", "DeptNo", "JobTitle", "Sex", "EdLev")
VALUES(? ,?, ?, ?, ?,?);

UPDATE "guest"."Employee" SET "DeptNo"=?
WHERE "EmpNo"=?;

SELECT *
FROM employee EmpNo
WHERE EmpNo = ?;

You can now launch the iBatis SQL Map Wizard for each SQL statement. You should call your iBatis SQL Mapping file "EmployeeMapping.xml".

Name the SQL Mapping for the following SQL statement "findAll".

SELECT *
FROM employee;

Name the next SQL Statement mapping “insertEmployee”.

INSERT INTO "guest"."Employee" 
("EmpNo", "Name", "DeptNo", "JobTitle", "Sex", "EdLev")
VALUES(? ,?, ?, ?, ?,?);

Name the next SQL statement SQL mapping “updateDep”.

UPDATE "guest"."Employee" SET "DeptNo"=?
WHERE "EmpNo"=?;

The Last SQL mapping should be called “findEmployee”.

SELECT *
FROM employee EmpNo
WHERE EmpNo = ?;

Once you are done creating your “EmployeeMapping.xml”, it should look like the following:

<?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="emp.repository.EmployeeMapping">

<!-- Define object mapping -->
<resultMap class="emp.domain.Employee" id="Employee">
<result column="EmpNo" jdbcType="INTEGER" property="EmpNo" />
<result column="Name" jdbcType="VARCHAR" property="Name" />
<result column="DeptNo" jdbcType="INTEGER" property="DeptNo" />
<result column="JobTitle" jdbcType="VARCHAR" property="JobTitle" />
<result column="Sex" jdbcType="CHAR" property="Sex" />
<result column="EdLev" jdbcType="BYTEINT" property="EdLev" />
</resultMap>

<!-- Define select SQL statement -->
<select id="findAll" resultMap="Employee">
SELECT *
FROM employee
</select>

<!-- Define insert SQL statement -->
<insert id="insertEmployee" parameterClass="emp.domain.Employee">
INSERT INTO &quot;guest&quot;.&quot;Employee&quot;
(&quot;EmpNo&quot;, &quot;Name&quot;, &quot;DeptNo&quot;, &quot;JobTitle&quot;, &quot;Sex&quot;, &quot;EdLev&quot;)
VALUES(#empNo# ,#name#, #deptNo#, #jobTitle#, #sex#,#edLev#)
<!-- TODO The selectKey iBatis descriptor needs to be added for insert to return a primary key -->

</insert>

<!-- Define update SQL statement -->
<update id="updateDep" parameterClass="emp.domain.Employee">
UPDATE &quot;guest&quot;.&quot;Employee&quot; SET &quot;DeptNo&quot;=#deptNo#
WHERE &quot;EmpNo&quot;=#empNo#
</update>

<!-- Define select SQL statement -->
<select id="findEmployee" parameterClass="int" resultMap="Employee">
SELECT *
FROM employee EmpNo
WHERE EmpNo = #empNo#
</select>

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

If you created the iBatis SQL Map file successfully, you are now ready for the next article in this series "Create an iBatis Spring DAO Wizard with Teradata Plug-in for Eclipse".

2 REPLIES
N/A

Re: Create an iBatis SQL Map with Teradata Plug-in for Eclipse

Hi i have a question. I have installed in Eclipse The Terradata Plugin. After, I configured like i saw here: http://developer.teradata.com/tools/articles/getting-started-with-teradata-plug-in-for-eclipse
Then, i created a Terracota Project and since Data Source Explorer and i connect me to MySql DataBase. My Question is where i can enter to see the iBatis SQL Map Wizard ?, i can not enter yet to generate the Ibatis Mapping. Can you help me ?
Teradata Employee

Re: Create an iBatis SQL Map with Teradata Plug-in for Eclipse

The iBatis SQL Map Wizard is only supported for the Teradata Database. Currently, MySQL is not supported.