Invalid character string format for type INTEGER. ?

[willie]

Hi,
i'm just migrating an application from iBatis 2.3.4.726 to MyBatis
3.0.4. The rewriting of the mappers works, but i have a problem with
parameter maps. My database is an embeded derby db and i get an
exception, if i try to select an entry from the databse.

The select stament is very simple:
<select id="selectUserEntryByName" parameterType="map"
resultMap="UserGroupResult">
select * from usergroups
where ((username = '#{username}') AND (usertype = #{usertype}))
</select>

username is defined as VARCHAR (128) usertype as INT
I add the parameter to a Hashmap simply like
options.put("username", username); // String username
options.put("usertype", usertype); // int usertype

With 2.x this works fine, now i get an

org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: java.sql.SQLDataException:
Invalid character string format for type INTEGER.
### The error may involve
de.mcs.smallarchive.account.UserMapper.selectUserEntryByName-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLDataException: Invalid character string format
for type INTEGER.
at
org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:
8)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
77)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
69)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:
40)
at de.mcs.smallarchive.account.UserGroupDB.getUser(UserGroupDB.java:
227)

When I debug the code, I thing MyBatis is struggeling at the first
parameter (username).

I just had tried some other statments like

<select id="selectUserEntryByName" parameterType="map"
resultMap="UserGroupResult">
select * from usergroups
where ((username = '#{username,javaType=string,jdbcType=VARCHAR}')
AND (usertype = #{usertype,javaType=integer,jdbcType=INTEGER}))
</select>

with the same result. What do i do wrong?

[Larry Meadors]

You want this:

where ((username = #{username}) AND (usertype = #{usertype}))

(Remove the ' characters, you don't need or want them.)

Larry

[Guy Rouillier]

I just tried with the contact_manager_cli sample and it worked fine for
me. The id column is of jdbcType INTEGER.

mapper.xml:
<select id="selectById" resultType="contact" parameterType="map">
select
id, lastName, firstName, phone, email
from contact
where id = #{id}
</select>

mapper.java:
Contact selectById(HashMap hmParams);

test.java:
HashMap hmParams = new HashMap();
hmParams.put("id", 1);
Contact c = mapper.selectById(hmParams);

BTW, with Mybatis 3, you should stop using HashMaps for parameters, and
instead use mapper interfaces with individual parameters. The approach
is much clearer and is type safe.

--
Guy Rouillier

[willie]

I have tried tonight with a parameter object and i get the same
message. (same without ') So it's independend of using maps or
objects.

java code:
public UserGroup getUser(String username, int usertype) throws
PersistenceException {
UserGroup user = new UserGroup();
user.setUsername(username);
user.setUsertype(usertype);
SqlSession sqlSession = storageDB.getSqlSession();
try {
user = (UserGroup)
sqlSession.selectOne("selectUserEntryByName", user);
} finally {
sqlSession.close();
}
return user;
}

(StorageDB is my main storage manager.)

mapper:
<select id="selectUserEntryByEntry" parameterType="UserGroup"

resultMap="UserGroupResult">
select * from usergroups
where ((username = #{username}) AND (usertype = #{usertype}))
</select>

I think the problem is, that i'm using two parameters with different
types.

Wilfried

[Nathan Maves]

Turn on logging to see the sql that mybatis is generating.  It will also show us the parameters being sent.

On Sun, Jan 9, 2011 at 4:47 PM, willie <w.k...@gmx.de> wrote:

[willie]

When i switch on debugging i see 4 lines of MyBatis...
0 [main] DEBUG Slf4jImpl#debug: Logging initialized using
'org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
47 [main] DEBUG Log4jImpl#debug: Logging initialized using
'org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
...
25172 [main] DEBUG Log4jImpl#debug: ooo Connection Opened
33391 [main] DEBUG Log4jImpl#debug: xxx Connection Closed

nothing else.

---
Willie

On 10 Jan., 15:52, Nathan Maves <nathan.ma...@gmail.com> wrote:
> Turn on logging to see the sql that mybatis is generating.  It will also
> show us the parameters being sent.
>

[Larry Meadors]

Looks like you're missing some log4j properties - need to set java.sql
to DEBUG, too.

Larry

[willie]

Resolved.
This message appears, in combination of the ' and the resultMap
parameter.
If i use resultType everything works fine.
Thanls for you effort.

---
WIllie

On 11 Jan., 13:27, Larry Meadors <larry.mead...@gmail.com> wrote:
> Looks like you're missing some log4j properties - need to set java.sql
> to DEBUG, too.
>
> Larry
>