iBatis (MyBatis) Custom Data Type 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 (MyBatis) Custom Data Type Wizard

iBatis and MyBatis support custom types to override JDBC and other types when using the iBatis or MyBatis frameworks. A custom data type gives you the ability to deal with any kind of special input and output handling you may need for a database data type. For example, a User Defined Type (UDT)  that represents a point would require an X and Y Position to be entered for input and an X and Y position to be retrieved from the database. The custom data type gives you a clearly defined programmatic mechanism to do this.

The iBatis (MyBatis) Custom Data type Wizard creates a Custom Data type and Custom handler for a selected Teradata database data type to use with the iBatis or MyBatis frameworks. The Generated Custom Type can be used with the following Teradata plug-in for Eclipse iBatis (MyBatis) Wizards to override the Teradata database types:

·  iBatis (MyBatis) SQL Map Wizard

·  iBatis (MyBatis) DAO with Web services Wizard

·  iBatis (MyBatis) CRUD Wizard

·  Spring DAO Wizard

·  iBatis (MyBatis) Stored Procedure Wizard

·  iBatis (MyBatis) Macro Wizard

·  iBatis (MyBatis) View Web service Wizard

In this article we are going to use a simple example of a UDT called Dollar. The Custom data type will allow a “$” to be entered with the amount for a Dollar UDT and  will put a “$” at the beginning of the amount for a Dollar UDT retrieved from the database.

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. Once you know how to produce a Teradata Project, make a Teradata project called AcctProject.

You will also need some database objects before you can start this tutorial. First create the following UDT called Dollar using the SQL Editor from the Teradata Plug-in for eclipse.

CREATE TYPE SYSUDTLIB.DOLLAR AS DECIMAL(10,2) FINAL ;

Now create a table called Account with the following DDL:

CREATE MULTISET TABLE ide."Account" ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
id INTEGER NOT NULL,
name VARCHAR(256) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
balance SYSUDTLIB.DOLLAR NOT NULL)
PRIMARY INDEX ( id );

Launch Wizard

The iBatis (MyBatis) Custom Data type Wizard is launched from the Eclipse package Explorer by right clicking a source folder of a Teradata Project and selecting the Create iBatis (MyBatis) Custom Type menu option.

iBatis (MyBatis) Custom Data Type Wizard

Once the Wizard comes up, enter the source location, package and Name of the Custom Type. You should verify that you selected the correct database profile for the custom type you want. In this example, select the Distinct UDT SYSUDTLIB.DOLLAR for the data type you wish to override.

Generated Custom Type

Once you have completed the Wizard, select the Finish button. The following files will be generated for you:

·  Custom Data Type Java Class

·  Custom Data Type Handler Java Class

·  Custom Data Type Properties file

Custom Data Type

The custom data type is generated as a stub class. It needs to be implemented to represent the data type which is being overridden.

package types;

/**
* This class is a Custom Data Type for a SYSUDTLIB.DOLLAR
*
*
*/
public class Dollar {

/**
* toString
*/
public String toString() {
// TODO add code to marshal class
return super.toString();
}

/**
* Create a Dollar given a String value
* @param val
* @return Dollar
*/
static public Dollar valueOf(String val) {
// TODO add code to create Dollar class from String val
return new Dollar();
}

}

In this case, the data type that is being customized is SYSUDTLIB.DOLLAR. This data type is a distinct UDT which is defined to be a DECIMAL type.

The Dollar Custom type implementation will put a "$" in front of the Decimal number being returned in the toString class method. This class implementation will also strip the "$" out of the string value parameter to create the Custom type in the valueOf class method.

package types;

import java.math.BigDecimal;

/**
* This class is a Custom Data Type for a SYSUDTLIB.DOLLAR
*/
public class Dollar {
BigDecimal dec = null;

public Dollar(BigDecimal dec) {
super();
this.dec = dec;
}

public Dollar() {
}

/**
* Create a Dollar Data Type given a String value
* @param val
* @return Dollar
*/
static public Dollar valueOf(String val) {
if(val == null) {
return null;
}
int index = val.indexOf("$");
if(index != -1) {
val = val.substring(index + 1);
}
return new Dollar(new BigDecimal(val.trim()));
}
/**
* toString
*/
public String toString() {
if(dec == null) {
return null;
} else {
return "$" + dec.toString();
}
}

/**
* @return the dec
*/
public BigDecimal getDec() {
return dec;
}

/**
* @param dec the dec to set
*/
public void setDec(BigDecimal dec) {
this.dec = dec;
}

}

Custom Type Handler

The Custom Type Handler is generated as a stub class implemented either for the following interfaces of iBatis or MyBatis:

·  MyBatis - org.apache.ibatis.type.TypeHandler

·  iBatis - com.ibatis.sqlmap.client.extensions.TypeHandlerCallback

The custom Type Handler needs to be implemented to do special processing for the custom type.

package types;

import org.apache.ibatis.type.TypeHandler;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.CallableStatement;
import org.apache.ibatis.type.JdbcType;

/**
* Extension for iBatis which handles the custom data type Dollar for SYSUDTLIB.DOLLAR
*
*/
public class DollarHandler implements TypeHandler {

/**
* Create a Dollar given a String value
* @param val
* @return Object
*/
public static Object valueOf(String val) {
// TODO add code to create Dollar class from String val
return Dollar.valueOf(val);
}

/**
* Set a SYSUDTLIB.DOLLAR using Dollar
* @param ps - PreparedStatement
* @param parameter index
* @param obj - Dollar object
*/
public void setParameter(PreparedStatement ps, int index, Object obj,
JdbcType type) throws SQLException {
Dollar value = (Dollar )obj;
// TODO use Dollar to set ps.setXXX

}

/**
* Get Dollar results in terms of SYSUDTLIB.DOLLAR
* @param - ResultSet
* @param - column name
* @return Dollar
*/

public Object getResult(ResultSet rs, String col) throws SQLException {

// TODO Get Dollar using rg.getXXX
return null;

}

/**
* Get Dollar results in terms of SYSUDTLIB.DOLLAR
* @param - CallableStatement
* @param - column index
* @return Dollar
*/
public Object getResult(CallableStatement cs, int col)
throws SQLException {
// TODO Get Dollar using cs.getXXX
return null;

}
}

The main methods that need to be implemented are setParameter and getResult. The setParameter uses the custom type to set a parameter in JDBC. The getResult method returns the Custom type which is created from a result returned from JDBC.

package types;

import org.apache.ibatis.type.TypeHandler;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.CallableStatement;
import org.apache.ibatis.type.JdbcType;

/**
* Extension for iBatis which handles the custom data type Dollar for SYSUDTLIB.DOLLAR
*
*/
public class DollarHandler implements TypeHandler {
/**
* Create a Dollar given a String value
* @param val
* @return Object
*/
public static Object valueOf(String val) {

return Dollar.valueOf(val);
}

/**
* Set a SYSUDTLIB.DOLLAR using Dollar
* @param ps - PreparedStatement
* @param parameter index
* @param obj - Dollar object
*/
public void setParameter(PreparedStatement ps, int index, Object obj,
JdbcType type) throws SQLException {

BigDecimal dec = null;
if(obj != null) {
dec = ((Dollar)obj).getDec();
}
ps.setBigDecimal(index, dec);
}
/**
* Get SYSUDTLIB.DOLLAR results in terms of DECIMAL
* @param - ResultSet
* @param - column name
* @return Dollar
*/
public Object getResult(ResultSet rs, String col) throws SQLException {

return new Dollar(rs.getBigDecimal(col));

}

/**
* Get SYSUDTLIB.DOLLAR results in terms of DECIMAL
* @param - CallableStatement
* @param - column index
* @return NewBigDecimal
*/
public Object getResult(CallableStatement cs, int col)
throws SQLException {

return new Dollar(cs.getBigDecimal(col));

}
}

Custom Type Properties

The custom properties file allows the Custom Data Type to be used with the Teradata IDE iBatis (MyBatis) Wizards.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Custom Type properties file version 1.0</comment>
<entry key="SYSUDTLIB.DOLLAR">types.Dollar</entry>
</properties>

iBatis (MyBatis) CRUD Wizard

A Custom Type created by the iBatis (MyBatis) Custom Data Type Wizard can be used with any of the Teradata IDE iBatis (MyBatis) Wizards. In this case, the iBatis (MyBatis) CRUD Wizard will be used.

Launch and run the iBatis(MyBatis) CRUD Wizard for the Account table which contains SYSUDTLIB.DOLLAR UDT.

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

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.

The main purpose of the Custom data type is to do special processing when the custom data type is used. In this case using the Web service client, when the balance is shown, a "$" is put in front of the dollar value.

The iBatis (MyBatis) CRUD wizard will add the Custom Type to the SQL Map file.

The iBatis  (MyBatis) DAO with Web Services Wizard will add the custom type handler to the iBatis  (MyBatis) Configuration file.

The iBatis  (MyBatis) DAO with Web Services wizard uses the Custom Type toString and valueOf methods to convert to and from the Schema Object in the Service using the MyBatis DAO which is generated for the Web Service.

The iBatis (MyBatis) Custom Data Type Wizard is an advanced feature for the Teradata iBatis (MyBatis) Wizards. It creates a Custom Type and Custom Type Handler to do special data type processing for MyBatis or iBatis. It is said that it takes 90% of the time to do the last 10% of any large programming task. The iBatis (MyBatis) Custom Data Type Wizard will reduce the time it takes to do that last 10% because it gives you a tool to deal with those cases which need  special data types.