Creating a Web Service with MyBatis and User Generated Keys

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

Creating a Web Service with MyBatis and User Generated Keys

iBatis is now called MyBatis (iBatis 3.0). MyBatis is no longer sponsored by Apache. It is now supported on Google code. The MyBatis framework is a lightweight data mapping framework and persistence API. It couples objects with stored procedures or SQL statements using an XML descriptor.  The Teradata Plug-in for Eclipse allows you to switch between creating projects that use MyBatis or iBatis using the Teradata Project preferences. When you switch to use MyBatis, you can use new features like User Generated Keys.

User Generated Keys are unique identifiers returned from MyBatis during an insert operation. This tutorial will go through creating a Web service using user generated keys with MyBatis.

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.

You should first select Spring 3.05 in the Teradata Project Preferences to use MyBatis. The Teradata Project preferences is accessed by first selecting the “Window” menu item in the top menu bar of the main Eclipse window and then selecting the Preferences menu item. The Teradata Project Preferences is under the Teradata Data tools Preferences in the tree of the Preferences popup dialog.

Once you have setup your Teradata Project Preferences, create a Teradata project called SalesProductPrj (using the same method as described in Create a Teradata Project using the Teradata Plug-in for Eclipse).

You will also need to create the following Table called SalesProduct using the SQL Editor from the Teradata Plug-in for eclipse.

CREATE TABLE SalesProduct(

id INTEGER GENERATED ALWAYS AS IDENTITY
(START WITH 1 INCREMENT BY 1),

description varchar(255),
salesPeriod PERIOD(TIMESTAMP(6)) NOT NULL,
price decimal(15,2)
);

The table above uses an identity column. This type column is made up of values generated by the database when rows are inserted . MyBatis returns these values as user generated keys during an insert operation.

Launch Wizard

Once you have created the SalesProduct Table, you can launch the iBatis (MyBatis) CRUD wizard by right clicking the SalesProduct Table Tree node in the Data Source Explorer and selecting "Create a CRUD iBatis(MyBatis) SQL Map..." menu option.

iBatis (MyBatis) CRUD Wizard

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

·      “/SalesProductPrj/src/resource” for your “MyBatis XML Folder”

·      “repository” for your “MyBatis XML Package”

·      “SalesProductMap” for your “MyBatis XML file name”

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 as the 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 wizard page is also where you can setup a user generated key. Select the User generated Keys option and the wizard will find and select the identity column for you.

Generated Code

Once all of the required information is entered into the Wizard, the Finish button can be selected and the MyBatis SQL Map is generated. The insert statement in the XML map will have two properties for the user generated keys. One property is called useGeneratedKeys. This property will be set to true. The other property is called keyProperty. This will identify the identity column. These properties will tell MyBatis to return the user generated key when the insert operation occurs.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="repository.SalesProductMap">

<!-- Define object mapping -->
<resultMap type="domain.SalesProduct" id="SalesProduct">
<result column="id" jdbcType="INTEGER" javaType="java.lang.Integer" property="id" />
<result column="description" jdbcType="VARCHAR" javaType="java.lang.String" property="description" />
<result column="price" jdbcType="DECIMAL" javaType="java.math.BigDecimal" property="price" />
</resultMap>

<!-- Define select SQL statement -->
<select id="findAllSalesProduct" resultMap="SalesProduct">
select * from "guest"."SalesProduct"
</select>

<!-- Define insert SQL statement using iBatis update -->
<insert id="insertSalesProduct" parameterType="domain.SalesProduct" useGeneratedKeys="true" keyProperty="id">
INSERT INTO "guest"."SalesProduct" ("description", "price") values(#{description}, #{price} )
</insert>

<!-- Define select SQL statement -->
<select id="findSalesProductById" parameterType="java.lang.Integer" resultMap="SalesProduct">
select * from "guest"."SalesProduct" where "id" = #{id}
</select>

<!-- Define update SQL statement -->
<update id="updateSalesProductById" parameterType="domain.SalesProduct">
update "guest"."SalesProduct" set "description"=#{description}, "price"=#{price} where "id"=#{id}
</update>

<!-- Define delete SQL statement -->
<delete id="deleteSalesProductById" parameterType="java.lang.Integer">
delete "guest"."SalesProduct" where "id" = #{id}
</delete>

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

 iBatis (MyBatis) DAO with Web Services Wizard

The iBatis (MyBatis) CRUD Wizard will launch the iBatis(MyBatis) DAO with Web Services Wizard . This Wizard will create a Web service from a SQL Map generated from the iBatis (MyBatis) CRUD Wizard. Enter all of the required information needed to create a DAO and Web service. On the final page of the Wizard, you should be sure to set your Client to test.

Web service Client

Once you hit the Finish button, your Web service and Web service client will be created for you. You can select the insertSaleProducts method in the client. You will be prompted for the price and description. You do not need to enter in the id because it is the identity column. It will be returned in your result from your Web service.

Conclusion

The Teradata IDE wizards allow you to switch between MyBatis and iBatis for your underlying database mapping framework. This will help you to transition from iBatis to MyBatis. All new development and bug fixes will only occur for MyBatis so it is important you start using it. Once you have made the transition to MyBatis, you can start using Spring 3.05 and new features like user generated keys.