Quantcast

How to create a map from a table?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

How to create a map from a table?

s.winterstein
Hi there,

like many others here I'm new to MyBatis and struggeling with the lack
of reference documentation. (the user guide is fine for a start, but
hardly sufficient). I'm trying to convert an existing project from
Hibernate to MyBatis.

After much trial and error, I still can't figure out how to load a
HashMap from a given table.

Consider these two simple tables:

1) AMUSER: id
2) USERDATA: id, propName, propValue

The Java object:

class User {
  String id;
  Map userdata;
}

All I want is that User.userdata  should be filled with all pairs of
(propName, propValue) contained in USERDATA that match the user's id.

Here's my current mapping:

  <mapper namespace="mybatis.mappings.UserMapper">
      <select id="selectUser" parameterType="String"
resultMap="detailedUserResultMap">
          SELECT  U.id as user_id
          FROM AMUser U
          WHERE id = #{userId}
      </select>

      <resultMap id="detailedUserResultMap"
type="org.activemath.webapp.user.User">
          <constructor>
              <idArg column="user_id" javaType="String"/>
          </constructor>
          <collection property="userData" select="selectUserdata"
column="user_id" javaType="java.util.HashMap"/>
       </resultMap>

      <select id="selectUserdata" parameterType="String"
resultType="java.util.HashMap">
          SELECT propName, propValue FROM userData WHERE id =
#{userId}
      </select>
  </mapper>

and the mapper:

    User selectUser(String userId);
    Map<String, Object> selectUserdata(String userId);


Now if I try

  User newUser = mapper.selectUser(userId); // this works
  Map userData = newUser.getUserData(); // fails here

I get "Statement mybatis.mappings.UserMapper.selectUserdata returned
more than one row, where no more than one was expected."

If I try mapper.selectUserdata(userId), I get "Expected one result (or
null) to be returned by selectOne(), but found: 16"

Please help, I'm really stuck.

-Stefan

PS: The debug log:

DEBUG java.sql.Connection  - ooo Connection Opened
DEBUG java.sql.PreparedStatement  - ==>  Executing: SELECT U.id as
user_id FROM AMUser U WHERE id = ?
DEBUG java.sql.PreparedStatement  - ==> Parameters: winter(String)
DEBUG java.sql.ResultSet  - <==    Columns: USER_ID
DEBUG java.sql.ResultSet  - <==        Row: winter
DEBUG java.sql.PreparedStatement  - ==>  Executing: SELECT propName,
propValue FROM userData WHERE id = ?
DEBUG java.sql.PreparedStatement  - ==> Parameters: winter(String)
DEBUG java.sql.ResultSet  - <==    Columns: PROPNAME, PROPVALUE
DEBUG java.sql.ResultSet  - <==        Row: readingskill,
DEBUG java.sql.ResultSet  - <==        Row: progress.surveys,
DEBUG java.sql.ResultSet  - <==        Row: speechoutput, false
DEBUG java.sql.ResultSet  - <==        Row: progress.sequencer,
DEBUG java.sql.ResultSet  - <==        Row: country, deu
DEBUG java.sql.ResultSet  - <==        Row: field, mathematics
DEBUG java.sql.ResultSet  - <==        Row: progress.test,
DEBUG java.sql.ResultSet  - <==        Row: learningcontext,
primary_education
DEBUG java.sql.ResultSet  - <==        Row: userroles,
admin,tutor,author,learner
DEBUG java.sql.ResultSet  - <==        Row: mainmenu.sortby,
collection
DEBUG java.sql.ResultSet  - <==        Row: email, [hidden email]
DEBUG java.sql.ResultSet  - <==        Row: name, Stefan
DEBUG java.sql.ResultSet  - <==        Row: language, en
DEBUG java.sql.ResultSet  - <==        Row: media, beginner
DEBUG java.sql.ResultSet  - <==        Row: fullname, Stefan
Winterstein
DEBUG java.sql.ResultSet  - <==        Row: learnermodel, nop

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: How to create a map from a table?

Poitras Christian
Hi,

MyBatis cannot load a map containing a key-value pair based on 2 columns in an <association>/<collection> element.

MyBatis can load a map based on "column name"-"column value". Simply run a select returning a single line and use resultType="java.util.HashMap".
MyBatis can also load a map using SqlSession.selectMap().
You can also use a ResultHandler to create your map and use SqlSession.select().
An example of ResultHandler is here http://code.google.com/p/mybatis/wiki/ResultHandlerExample

Christian

-----Message d'origine-----
De : [hidden email] [mailto:[hidden email]] De la part de s.winterstein
Envoyé : January-05-11 8:02 AM
À : mybatis-user
Objet : How to create a map from a table?

Hi there,

like many others here I'm new to MyBatis and struggeling with the lack
of reference documentation. (the user guide is fine for a start, but
hardly sufficient). I'm trying to convert an existing project from
Hibernate to MyBatis.

After much trial and error, I still can't figure out how to load a
HashMap from a given table.

Consider these two simple tables:

1) AMUSER: id
2) USERDATA: id, propName, propValue

The Java object:

class User {
  String id;
  Map userdata;
}

All I want is that User.userdata  should be filled with all pairs of
(propName, propValue) contained in USERDATA that match the user's id.

Here's my current mapping:

  <mapper namespace="mybatis.mappings.UserMapper">
      <select id="selectUser" parameterType="String"
resultMap="detailedUserResultMap">
          SELECT  U.id as user_id
          FROM AMUser U
          WHERE id = #{userId}
      </select>

      <resultMap id="detailedUserResultMap"
type="org.activemath.webapp.user.User">
          <constructor>
              <idArg column="user_id" javaType="String"/>
          </constructor>
          <collection property="userData" select="selectUserdata"
column="user_id" javaType="java.util.HashMap"/>
       </resultMap>

      <select id="selectUserdata" parameterType="String"
resultType="java.util.HashMap">
          SELECT propName, propValue FROM userData WHERE id =
#{userId}
      </select>
  </mapper>

and the mapper:

    User selectUser(String userId);
    Map<String, Object> selectUserdata(String userId);


Now if I try

  User newUser = mapper.selectUser(userId); // this works
  Map userData = newUser.getUserData(); // fails here

I get "Statement mybatis.mappings.UserMapper.selectUserdata returned
more than one row, where no more than one was expected."

If I try mapper.selectUserdata(userId), I get "Expected one result (or
null) to be returned by selectOne(), but found: 16"

Please help, I'm really stuck.

-Stefan

PS: The debug log:

DEBUG java.sql.Connection  - ooo Connection Opened
DEBUG java.sql.PreparedStatement  - ==>  Executing: SELECT U.id as
user_id FROM AMUser U WHERE id = ?
DEBUG java.sql.PreparedStatement  - ==> Parameters: winter(String)
DEBUG java.sql.ResultSet  - <==    Columns: USER_ID
DEBUG java.sql.ResultSet  - <==        Row: winter
DEBUG java.sql.PreparedStatement  - ==>  Executing: SELECT propName,
propValue FROM userData WHERE id = ?
DEBUG java.sql.PreparedStatement  - ==> Parameters: winter(String)
DEBUG java.sql.ResultSet  - <==    Columns: PROPNAME, PROPVALUE
DEBUG java.sql.ResultSet  - <==        Row: readingskill,
DEBUG java.sql.ResultSet  - <==        Row: progress.surveys,
DEBUG java.sql.ResultSet  - <==        Row: speechoutput, false
DEBUG java.sql.ResultSet  - <==        Row: progress.sequencer,
DEBUG java.sql.ResultSet  - <==        Row: country, deu
DEBUG java.sql.ResultSet  - <==        Row: field, mathematics
DEBUG java.sql.ResultSet  - <==        Row: progress.test,
DEBUG java.sql.ResultSet  - <==        Row: learningcontext,
primary_education
DEBUG java.sql.ResultSet  - <==        Row: userroles,
admin,tutor,author,learner
DEBUG java.sql.ResultSet  - <==        Row: mainmenu.sortby,
collection
DEBUG java.sql.ResultSet  - <==        Row: email, [hidden email]
DEBUG java.sql.ResultSet  - <==        Row: name, Stefan
DEBUG java.sql.ResultSet  - <==        Row: language, en
DEBUG java.sql.ResultSet  - <==        Row: media, beginner
DEBUG java.sql.ResultSet  - <==        Row: fullname, Stefan
Winterstein
DEBUG java.sql.ResultSet  - <==        Row: learnermodel, nop

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to create a map from a table?

s.winterstein
Hi Christian,

thanks for clearing this up. It the kind of schema you get from using
Hibernate...

Going through a ResultHandler means dealing with a ResultContext (and
friends like MetaObject), all of which are undocumentend too. The
existing example at least hardly covers the basics.

Meanwhile, I've successfully tried a different approach: if MyBatis
won't load my table as a map but returns it as a list, I take that
list and convert it to a map myself.

This code works for me:

     User newUser = mapper.selectUser(userId);
     List userDataList = mapper.selectUserdata(userId);
     newUser.setUserData(list2Map(userDataList));

using this select:
    <select id="selectUserdata" parameterType="String"
resultType="map">
        SELECT propName, propValue FROM userData WHERE id = #{userId}
    </select>

Each list entry contains a map {PROPNAME=<key>,PROPVALUE=<value>}, and
I can walk the list and put each entry in the UserData map.

But thanks for helping me out!

-Stefan

On 5 Jan., 22:27, Poitras Christian <[hidden email]>
wrote:

> Hi,
>
> MyBatis cannot load a map containing a key-value pair based on 2 columns in an <association>/<collection> element.
>
> MyBatis can load a map based on "column name"-"column value". Simply run a select returning a single line and use resultType="java.util.HashMap".
> MyBatis can also load a map using SqlSession.selectMap().
> You can also use a ResultHandler to create your map and use SqlSession.select().
> An example of ResultHandler is herehttp://code.google.com/p/mybatis/wiki/ResultHandlerExample
>
> Christian
Loading...