Re: Why Insert Fails when Select Succeeds with samollowse sqlmap

[Raja]

In complete message sorry posted by mistake here is remaining part


my test class is as follows

public class TestTypeHandlers extends TestCase {

private IBatisTestHelper testHelper;
private SqlMapClient sqlMapClient;
private TypeHandlersRec rec;

protected void setUp() throws Exception {
super.setUp();
try {
System.out.println("Default Time Zone " + TimeZone.getDefault());

testHelper = new IBatisTestHelper();
System.out.println("initDatabase");
testHelper.initDatabase("com/matrimony/utils/ibatis/
typeHandler_CreateDB.sql");

System.out.println("initSqlMapClient");
testHelper.initSqlMapClient("com/matrimony/utils/ibatis/
TypeHandlerSqlMapConfig.xml", null);
sqlMapClient = testHelper.getSqlMapClient();
}
catch (Exception e) {
fail("Type Handler Test Setup Failed " + e.getMessage());
}
}

public void testSelect() {

// SimpleDateFormat fmt = new SimpleDateFormat("MMM dd yyyy
HH:mm:ss");

SimpleDateFormat fmtDt = new SimpleDateFormat("MMM dd yyyy");
SimpleDateFormat fmtTm = new SimpleDateFormat("MMM dd yyyy
HH:mm:ss");

fmtDt.setTimeZone(TimeZone.getTimeZone("GMT+05:30"));
fmtTm.setTimeZone(TimeZone.getTimeZone("GMT+05:30"));

try {
rec = null;
rec = (TypeHandlersRec)
sqlMapClient.queryForObject("TYPE_HANDLER.selectByPK",
new TypeHandlersRec(1, null, null, null));
assertNull("Empty Table Should Not Select Record", rec);
}
catch (Exception e) {
System.out.println("Exception Raised" + e.getMessage());
fail("Bool Test Failed " + e.getMessage());
}
}

public void testBasicOperation() {
try {
Date dt = new Date();
Timestamp ts = new Timestamp(dt.getTime());
Boolean bl = new Boolean(true);

testHelper.initDatabase("com/matrimony/utils/ibatis/
typeHandler_EmptyDB.sql");

rec = new TypeHandlersRec(1, bl, dt, ts);

System.out.println("Inserting Record");
sqlMapClient.insert("TYPE_HANDLER.insertRec", rec);
System.out.println("Record Inserted");

}
catch (Exception e) {
System.out.println("Exception Raised" + e.getMessage());
fail("Bool Test Failed " + e.getMessage());
}

}
}


THE SQLMAP IS AS FOLLOWS

<sqlMap namespace="TYPE_HANDLER" >
<resultMap id="TypeHandlersResultMap"
class="com.matrimony.utils.ibatis.TypeHandlersRec" >

<result column="pk_id" property="pk_id" javaType="int"
jdbcType="INT" />
<result column="boolVal" property="boolVal"
javaType="java.lang.Boolean" jdbcType="CHAR"
typeHandler="com.matrimony.utils.ibatis.boolTypeHandlerTF"/>
<result column="dateField" property="dateField"
javaType="java.util.Date" jdbcType="DATE"
typeHandler="com.matrimony.utils.ibatis.DateTypeHandler"/>
<result column="timestampField" property="timestampField"
javaType="java.sql.Timestamp" jdbcType="TIMESTAMP"
typeHandler="com.matrimony.utils.ibatis.TimeStampTypeHandler"/>
</resultMap>

<insert id="insertRec"
parameterClass="com.matrimony.utils.ibatis.TypeHandlersRec" >
insert into TYPE_HANDLER
(pk_id, boolVal, dateField, timestampField)
values
(#pk_id:INT#, #boolVal:CHAR#, #dateField:DATE#,
#timestampField:TIMESTAMP#)
</insert>

<select id="selectByPK" resultMap="TypeHandlersResultMap"
parameterClass="com.matrimony.utils.ibatis.TypeHandlersRec" >
select
pk_id, boolVal, dateField, timestampField
from
TYPE_HANDLER
where
pk_id = #pk_id:INT#
</select>

<delete id="deleteByPK"
parameterClass="com.matrimony.utils.ibatis.TypeHandlersRec" >
delete from
TYPE_HANDLER
where
pk_id = #pk_id:INT#
</delete>

<update id="updateByPK"
parameterClass="com.matrimony.utils.ibatis.TypeHandlersRec" >
update
TYPE_HANDLER
set
pk_id = #pk_id:INT#,
boolVal = #boolVal:CHAR#,
dateField = #dateField:DATE#,
timestampField = #timestampField:TIMESTAMP#
where
pk_id = #pk_id:INT#
</update>
</sqlMap>

in this example my junit test for TYPE_HANDLER.selectByPK suceeds but
TYPE_HANDLER.insertRec fails with following message

initDatabase
initSqlMapClient
Inserting Record
Exception Raised
--- The error occurred in com/matrimony/utils/ibatis/
TypeHandlerSqlMap.xml.
--- The error occurred while applying a parameter map.
--- Check the TYPE_HANDLER.insertRec-InlineParameterMap.
--- Check the parameter mapping for the 'boolVal' property.
--- Cause: java.sql.SQLDataException: data exception: string data,
right truncation

I am wondering when same sqlmap succeeds with select why it should
fails for Insert ?

thanks and best regards

raja

[Nathan Maves]

you need to specify your custom type handler for your insert which is
using an inline parameter map.

#boolVal:CHAR#

you will find this in the developer guide.

nathan

[Raja]

Thanks Nathan for speedy reply,

I will refer and come back here if necessary.

Raja

[Raja]

Well I have changed sqlmap.xml as follows earlier error gone but new
Error poped up

<parameterMap class="com.matrimony.utils.ibatis.TypeHandlersRec"
id="typeHandlerMap" >
<parameter property="pk_id" javaType="int" jdbcType="INT"/>
<parameter property="boolVal" javaType="java.lang.Boolean"

jdbcType="CHAR"
typeHandler="com.matrimony.utils.ibatis.boolTypeHandlerTF"/>

<parameter property="dateField" javaType="java.util.Date"

jdbcType="DATE"
typeHandler="com.matrimony.utils.ibatis.DateTypeHandler"/>

<parameter property="timestampField" javaType="java.sql.Timestamp"

jdbcType="TIMESTAMP"
typeHandler="com.matrimony.utils.ibatis.TimeStampTypeHandler"/>

</parameterMap>

<insert id="insertRec"
parameterClass="com.matrimony.utils.ibatis.TypeHandlersRec"

parameterMap="typeHandlerMap">

insert into TYPE_HANDLER (pk_id, boolVal, dateField, timestampField)
values (#pk_id:INT#, #boolVal:CHAR#, #dateField:DATE#,
#timestampField:TIMESTAMP#)
</insert>

the error is

--- The error occurred in com/matrimony/utils/ibatis/
TypeHandlerSqlMap.xml.

--- The error occurred while executing update.
--- Check the insert into TYPE_HANDLER (pk_id, boolVal, dateField,

timestampField) values (#pk_id:INT#, #boolVal:CHAR#,

#dateField:DATE#, #timestampField:TIMESTAMP#) .
--- Check the SQL Statement (preparation failed).
--- Cause: java.sql.SQLException: unknown token

what is going wrong here ?

Thanks And regards

Raja

[Nathan Maves]

Well I think you might have read one page in the developers guide... :)

All kidding aside we(mybatis team) cannot stress how much it helps to
read the guide. Reading means start at the begin and end at the end.
Once you have read it then use it as a reference guide. It is titled
a developer guide to help guide you though the learning process.

You have now chosen to use a parameter map over inline. When doing
this you need to use standard jdbc like parameters in your sqlmap.

values (?, ?, ?,?)

[Clinton Begin]

If you're starting with MyBatis for the first time, you should be using 3.0.

Clinton

On Fri, Jun 4, 2010 at 12:38 PM, Raja <kpr.r...@gmail.com> wrote:

[Raja]

Yes Nathan,

Its true that I just browsed the guide and jumped to solution since it
was late night.
Today I will read it and come back if necessary.

Anyways thanks for help extended.

Raja

[Raja]

Well Clinton,

> If you're starting with MyBatis for the first time, you should be using 3.0.
>

I tried that but since I am also using spring 3.0 which do not support
myBatis 3.
I have to wait till spring 3.2 where it will be officially supported

Thanks

Raja

[fxbird.gmail]

Hi Nathan Maves :

    I think 3.0 user guide is not very detailed. Must we use a javabean for passing multiple parameters for select query?

 

 

fxbird.gmail, fxb...@gmail.com

2010-06-05

----- Receiving the following content -----

From: Nathan Maves

Receiver: mybatis-user

Time: 2010-06-05, 03:13:21

Subject: Re: Why Insert Fails when Select Succeeds with samollowse sqlmap

[Raja]

Hi Nathan,

Problem Solved with your kind Help. Now My jUnit Testcase is running
Smoothly.
Just for sake of curiosity i have a question. Please refer my SQLMAP
config file for the test.

<sqlMapConfig>

<settings useStatementNamespaces="true" />


<typeHandler javaType="java.lang.Boolean" jdbcType="CHAR"
callback="com.matrimony.utils.ibatis.boolTypeHandlerTF"/>
<typeHandler javaType="java.util.Date" jdbcType="DATE"
callback="com.matrimony.utils.ibatis.DateTypeHandler"/>
<typeHandler javaType="java.sql.Timestamp" jdbcType="TIMESTAMP"
callback="com.matrimony.utils.ibatis.TimeStampTypeHandler"/>
<typeHandler javaType="java.sql.Date" jdbcType="DATE"
callback="com.matrimony.utils.ibatis.DateTypeHandler"/>
<typeHandler javaType="java.sql.Time" jdbcType="TIME"
callback="com.matrimony.utils.ibatis.TimeTypeHandler"/>

<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="org.hsqldb.jdbcDriver"/>
<property name="JDBC.ConnectionURL" value="jdbc:hsqldb:mem:aname"/>
<property name="JDBC.Username" value="sa"/>
<property name="JDBC.Password" value=""/>
<property name="JDBC.DefaultAutoCommit" value="true"/>
</dataSource>
</transactionManager>

<!-- If TypeHandlers are put here exception is raised -->

<sqlMap resource="com/matrimony/utils/ibatis/TypeHandlerSqlMap.xml" /
>

</sqlMapConfig>

If i put typehandler declarations at place as shown above a exception
is raised. I would like to know why ?

At one more point I would like to have help.

How to do stand alone unit Test custom TypeHandlers like below.

My concern is how to mock ResultGetter and ParameterSetter and
see what value will go to Database Table as well with some value from
Database what
value java object will get. As you have seen above config file
there are 4 custom type handlers and I would like to unit test them
individually
since those are being referred by many table record classes and daos.

With date and timestamp handlers I am trying save the dates in
a specific timezone like GMT+05:30 irrespective of JVMs default
timezone.
How can I unit test that, dates are being saved to table in given
specific time zone.

Thanks and regards

Raja.


public class boolTypeHandlerTF implements TypeHandlerCallback {

private static final String YES = "T";
private static final String NO = "F";

@Override
public Object getResult(ResultGetter getter) throws SQLException {

String s = getter.getString().trim();
Boolean bl = yesNoToBoolean(s);

return bl;
}

@Override
public void setParameter(ParameterSetter setter, Object parameter)
throws SQLException {

Boolean bl = (Boolean) parameter;
String s = booleanToYesNo(bl).trim();

setter.setString(s);
}

@Override
public Object valueOf(String s) {

return yesNoToBoolean(s);
}

private String booleanToYesNo(Boolean b) {

if (b == null)
throw new IllegalArgumentException("Could not convert null to a
boolean value. "
+ "Valid arguments are 'true' and 'false'.");
else if (b.booleanValue())
return YES;
else
return NO;
}

private Boolean yesNoToBoolean(String s) {
s = s.trim();
if (YES.equalsIgnoreCase(s)) {
return Boolean.TRUE;
}
else if (NO.equalsIgnoreCase(s)) {
return Boolean.FALSE;
}
else {
throw new IllegalArgumentException("Could not convert " + s + " to
a boolean value. "
+ "Valid arguments are '" + YES + "' and '" + NO +
"'.");
}
}
}

[Nathan Maves]

no you don't have to use a java bean.

you can just mark your mapper parameters with the @Param("name")

nathan

[Raja]

Hi Nathan,

I have not understood following in which context it is ?

>
> you can just mark your mapper parameters with the @Param("name")
>

>

> > If i put typehandler declarations at place as shown above a exception
> > is raised. I would like to know why ?
>
> > At one more point I would like to have help.
>
> > How to do stand alone unit Test custom TypeHandlers like below.
>
> > My concern is how to mock ResultGetter and ParameterSetter and
> > see what value will go to Database Table as well with some value from
> > Database what
> > value java object will get. As you have seen above config file
> > there are 4 custom type handlers and I would like to unit test them
> > individually
> > since those are being referred by many table record classes and daos
>

> > With date and timestamp handlers I am trying save the dates in
> > a specific timezone like GMT+05:30 irrespective of JVMs default
> > timezone.
> > How can I unit test that, dates are being saved to table in given
> > specific time zone.
>

Can u guide me about above points.

Thanks and best regards

Raja

[Guy Rouillier]

On 6/5/2010 4:53 AM, fxbird.gmail wrote:
> Hi Nathan Maves :
> I think 3.0 user guide is not very detailed. Must we use a javabean for
> passing multiple parameters for select query?

No, see the section "Using Mappers" in the documentation. To quote:

"You can pass multiple parameters to a mapper method. If you do, they
will be named by their position in the parameter list by default, for
example: #{1}, #{2} etc. If you wish to change the name of the
parameters (multiple only), then you can use the @Param(锟斤拷paramName锟斤拷)
annotation on the parameter."

To the uninitiated, an example would be helpful at this point in the
docs. Here is one from some working code. In the mapper Java file, I
have this:

List<Site> selectVpnSites(@Param("aCustId") String aCustId,
@Param("aServId") String aServId);

and in the XML file, the corresponding SQL statement is declared like this:

<select id="selectVpnSites" parameterType="map" resultMap="siteMap">
...
where
cust_id = #{aCustId}
...
</select>

In short, if your mapper Java method passes multiple parameters, then
the parameterType in your XML will always be map (for java.util.Map).

--
Guy Rouillier