how to insert with an assocation object, not its ID?

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

how to insert with an assocation object, not its ID?

Simon
We  have been using mybatis for about 4 months, but are struggling to
get one assocation example working with our DB.  Currently we are
doing all associations by hand.

Here is a simplified demo exmaple of what we have now.  Here we use
the BlogMapper to select a blog, we then have to use the AuthorMapper
to select the author based on the authorid of the blog if we need it.

CREATE TABLE AUTHOR
(
   ID                   INTEGER                   NOT NULL
   NAME            VARCHAR2(4000)
NOT NULL
)

CREATE TABLE BLOG
(
   ID                   INTEGER                   NOT NULL
   AUTHORID    INTEGER
NOT NULL
   SUBJECT        VARCHAR2(4000)
NOT NULL
   CONTENT       VARCHAR2(4000)
)

AuthorMapper.xml
--------------------------
        <select id="select" resultType="com.mypackage.Author">
            select * from author
            where id = #{value}
        </select>

        <insert id="insert">
               <selectKey resultType="long" order="BEFORE"
keyProperty="id">
                     select SEQ_AUTHOR.nextval as value from dual
                </selectKey>
                     insert into author (name)
                     values ( #{name})
         </insert>

Author.java
----------------
public class Author {
  private long id;
  private String name;
  public Author() {}
  //getters and setters.
}

BlogMapper.xml
------------------------
        <select id="select" resultType="com.mypackage.Blog">
            select * from blog
            where id = #{value}
        </select>

        <insert id="insert">
               <selectKey resultType="long" order="BEFORE"
keyProperty="id">
                     select SEQ_BLOG.nextval as value from dual
                </selectKey>
                     insert into blog (authorid, subject, content)
                     values ( #{authorid}, #{subject}, #{content,
jdbcType="VARCHAR"})
         </insert>

Blog.java
-------------
public class Blog {
  private long id;
  private long authorid;
  private String subject;
  private String content;
  public Blog() {}
  //getters and setters.
}

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Now we try and work out how to do this with an assocation.  We could
start of  like this:

Blog.java
-------------
public class Blog {
  private long id;
  private Author author;      // <<<<<<<<<<<< now dont have athorID
  private String subject;
  private String content;
  public Blog() {}
  //getters and setters.
}

Now is the first crux, without an authorid, how can we insert?

BlogMapper.xml
------------------------
        <select id="select" resultType="Blog">
            select * from blog
            where id = #{value}
        </select>

        <insert id="insert">
               <selectKey resultType="long" order="BEFORE"
keyProperty="id">
                     select SEQ_BLOG.nextval as value from dual
                </selectKey>
                     insert into blog (authorid, subject, content)
                     values ( ??????#{authorid}?????????, #{subject},
#{content, jdbcType="VARCHAR"})
         </insert>

There are lots of examples of ResultMaps and selectParameters in the
Userguide, but nothing on "InsertMap" or "InsertParameters".

 Would this work?

        <insert id="insert">
               <selectKey resultType="long" order="BEFORE"
keyProperty="id">
                     select SEQ_BLOG.nextval as value from dual
                </selectKey>
                     insert into blog (authorid, subject, content)
                     values (#{blog.getAuthor().getId()}, #{subject},
#{content, jdbcType="VARCHAR"})
         </insert>

Or do we need to "hack" the bean thusly:

public class Blog {
  private long id;
  private Author author;      // <<<<<<<<<<<< now dont have athorID
  private String subject;
  private String content;
  public Blog() {}

  // Helper function for insert only...
  public long getAuthorid() {
   if (author == null)
    return null;
  elese
    return author.getId();
  }

  //getters and setters.
}

Or other cunning solution?  Chaning all our ID columns to be table
specific, e.g. AUTHORID and BLOGID etc. is not really an option in
this project.

Crux 2 will be how to read it (I can probably deduce this from
userguide + experimentation).
Crux 3 will be when does the system "select" the dependent association
object?  Ive seem a number of posts which say that the lazy loading
does not work as expected?

Currently, we are actaully having the authorid and the author object
in the bean blog, and we set the author object by hand,and the
authorid using mybatis, which is tricky to keep in sync.

Thanks,
Simon.
Reply | Threaded
Open this post in threaded view
|

Re: how to insert with an assocation object, not its ID?

asherwin
It seems to me that you're making life very difficult on yourself by
not storing the FK ID's in your java Objects...

Also, as far as I know, MyBatis mapper methods are designed to break
down into a single SQL statement, which is why you see ResultMaps but
not an InsertMap.  ResultMaps with associations and collections are
possible through joins, and mapping objects to one another (ideally
through PK's and [likely] indexed FK's).

However, there is no equivalent when inserting data into a database.
Some DB's support a bulk insert syntax, but this is still only for a
single table with multiple rows, not across multiple tables.

In the app I'm working on we have pretty elegant, lightweight ORM like
custom annotation driven setup with DAO services that act on our POJOs
which allow us to write some simple code to support such "deep
inserts" pretty easily.

Is there a reason why you do not want to store foreign keys between
tables, and carry those into Java?  Generally, that's what RDBMS are
for.. you're better off leveraging DB features then working around
them

On Sep 22, 9:42 am, Simon <[hidden email]> wrote:

> We  have been using mybatis for about 4 months, but are struggling to
> get one assocation example working with our DB.  Currently we are
> doing all associations by hand.
>
> Here is a simplified demo exmaple of what we have now.  Here we use
> the BlogMapper to select a blog, we then have to use the AuthorMapper
> to select the author based on the authorid of the blog if we need it.
>
> CREATE TABLE AUTHOR
> (
>    ID                   INTEGER                                                 NOT NULL
>    NAME            VARCHAR2(4000)
> NOT NULL
> )
>
> CREATE TABLE BLOG
> (
>    ID                   INTEGER                                                 NOT NULL
>    AUTHORID    INTEGER
> NOT NULL
>    SUBJECT        VARCHAR2(4000)
> NOT NULL
>    CONTENT       VARCHAR2(4000)
> )
>
> AuthorMapper.xml
> --------------------------
>         <select id="select" resultType="com.mypackage.Author">
>             select * from author
>             where id = #{value}
>         </select>
>
>         <insert id="insert">
>                <selectKey resultType="long" order="BEFORE"
> keyProperty="id">
>                      select SEQ_AUTHOR.nextval as value from dual
>                 </selectKey>
>                      insert into author (name)
>                      values ( #{name})
>          </insert>
>
> Author.java
> ----------------
> public class Author {
>   private long id;
>   private String name;
>   public Author() {}
>   //getters and setters.
>
> }
>
> BlogMapper.xml
> ------------------------
>         <select id="select" resultType="com.mypackage.Blog">
>             select * from blog
>             where id = #{value}
>         </select>
>
>         <insert id="insert">
>                <selectKey resultType="long" order="BEFORE"
> keyProperty="id">
>                      select SEQ_BLOG.nextval as value from dual
>                 </selectKey>
>                      insert into blog (authorid, subject, content)
>                      values ( #{authorid}, #{subject}, #{content,
> jdbcType="VARCHAR"})
>          </insert>
>
> Blog.java
> -------------
> public class Blog {
>   private long id;
>   private long authorid;
>   private String subject;
>   private String content;
>   public Blog() {}
>   //getters and setters.
>
> }
>
> /////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////
>
> Now we try and work out how to do this with an assocation.  We could
> start of  like this:
>
> Blog.java
> -------------
> public class Blog {
>   private long id;
>   private Author author;      // <<<<<<<<<<<< now dont have athorID
>   private String subject;
>   private String content;
>   public Blog() {}
>   //getters and setters.
>
> }
>
> Now is the first crux, without an authorid, how can we insert?
>
> BlogMapper.xml
> ------------------------
>         <select id="select" resultType="Blog">
>             select * from blog
>             where id = #{value}
>         </select>
>
>         <insert id="insert">
>                <selectKey resultType="long" order="BEFORE"
> keyProperty="id">
>                      select SEQ_BLOG.nextval as value from dual
>                 </selectKey>
>                      insert into blog (authorid, subject, content)
>                      values ( ??????#{authorid}?????????, #{subject},
> #{content, jdbcType="VARCHAR"})
>          </insert>
>
> There are lots of examples of ResultMaps and selectParameters in the
> Userguide, but nothing on "InsertMap" or "InsertParameters".
>
>  Would this work?
>
>         <insert id="insert">
>                <selectKey resultType="long" order="BEFORE"
> keyProperty="id">
>                      select SEQ_BLOG.nextval as value from dual
>                 </selectKey>
>                      insert into blog (authorid, subject, content)
>                      values (#{blog.getAuthor().getId()}, #{subject},
> #{content, jdbcType="VARCHAR"})
>          </insert>
>
> Or do we need to "hack" the bean thusly:
>
> public class Blog {
>   private long id;
>   private Author author;      // <<<<<<<<<<<< now dont have athorID
>   private String subject;
>   private String content;
>   public Blog() {}
>
>   // Helper function for insert only...
>   public long getAuthorid() {
>    if (author == null)
>     return null;
>   elese
>     return author.getId();
>   }
>
>   //getters and setters.
>
> }
>
> Or other cunning solution?  Chaning all our ID columns to be table
> specific, e.g. AUTHORID and BLOGID etc. is not really an option in
> this project.
>
> Crux 2 will be how to read it (I can probably deduce this from
> userguide + experimentation).
> Crux 3 will be when does the system "select" the dependent association
> object?  Ive seem a number of posts which say that the lazy loading
> does not work as expected?
>
> Currently, we are actaully having the authorid and the author object
> in the bean blog, and we set the author object by hand,and the
> authorid using mybatis, which is tricky to keep in sync.
>
> Thanks,
> Simon.
Reply | Threaded
Open this post in threaded view
|

Re: how to insert with an assocation object, not its ID?

Simon
Thanks for the reply, but I think you missunderstand the problem.

I AM inserting each row independently

Currently I do have the FK Ids.

But if you have FKids, then you dont have the associated object!  If
you have the Object (i.e. Author), then you dont have the AUTHORID in
the getters and setters, so cant do a simple inert of a single Blog
row (the SQL expect the AUTHORID).

If you want to use associations in selects, then my sweeping assumtion
is that you HAVE to have the associated object (e.g. Author object in
the Blog object in this case), not its ID.

Or am I missing something?

I guess you could have both the FK ID and the F Object, the FK ID is
for inserting, the Object is for selecting, then you have to try and
remember which one is valid in the Blog object at any given time (or
try and keep them in sync manually).

Im sure its not this hard...

Simon.

On Sep 22, 3:54 pm, asherwin <[hidden email]> wrote:

> It seems to me that you're making life very difficult on yourself by
> not storing the FK ID's in your java Objects...
>
> Also, as far as I know, MyBatis mapper methods are designed to break
> down into a single SQL statement, which is why you see ResultMaps but
> not an InsertMap.  ResultMaps with associations and collections are
> possible through joins, and mapping objects to one another (ideally
> through PK's and [likely] indexed FK's).
>
> However, there is no equivalent when inserting data into a database.
> Some DB's support a bulk insert syntax, but this is still only for a
> single table with multiple rows, not across multiple tables.
>
> In the app I'm working on we have pretty elegant, lightweight ORM like
> custom annotation driven setup with DAO services that act on our POJOs
> which allow us to write some simple code to support such "deep
> inserts" pretty easily.
>
> Is there a reason why you do not want to store foreign keys between
> tables, and carry those into Java?  Generally, that's what RDBMS are
> for.. you're better off leveraging DB features then working around
> them
>
> On Sep 22, 9:42 am, Simon <[hidden email]> wrote:
>
> > We  have been using mybatis for about 4 months, but are struggling to
> > get one assocation example working with our DB.  Currently we are
> > doing all associations by hand.
>
> > Here is a simplified demo exmaple of what we have now.  Here we use
> > the BlogMapper to select a blog, we then have to use the AuthorMapper
> > to select the author based on the authorid of the blog if we need it.
>
> > CREATE TABLE AUTHOR
> > (
> >    ID                   INTEGER                                                 NOT NULL
> >    NAME            VARCHAR2(4000)
> > NOT NULL
> > )
>
> > CREATE TABLE BLOG
> > (
> >    ID                   INTEGER                                                 NOT NULL
> >    AUTHORID    INTEGER
> > NOT NULL
> >    SUBJECT        VARCHAR2(4000)
> > NOT NULL
> >    CONTENT       VARCHAR2(4000)
> > )
>
> > AuthorMapper.xml
> > --------------------------
> >         <select id="select" resultType="com.mypackage.Author">
> >             select * from author
> >             where id = #{value}
> >         </select>
>
> >         <insert id="insert">
> >                <selectKey resultType="long" order="BEFORE"
> > keyProperty="id">
> >                      select SEQ_AUTHOR.nextval as value from dual
> >                 </selectKey>
> >                      insert into author (name)
> >                      values ( #{name})
> >          </insert>
>
> > Author.java
> > ----------------
> > public class Author {
> >   private long id;
> >   private String name;
> >   public Author() {}
> >   //getters and setters.
>
> > }
>
> > BlogMapper.xml
> > ------------------------
> >         <select id="select" resultType="com.mypackage.Blog">
> >             select * from blog
> >             where id = #{value}
> >         </select>
>
> >         <insert id="insert">
> >                <selectKey resultType="long" order="BEFORE"
> > keyProperty="id">
> >                      select SEQ_BLOG.nextval as value from dual
> >                 </selectKey>
> >                      insert into blog (authorid, subject, content)
> >                      values ( #{authorid}, #{subject}, #{content,
> > jdbcType="VARCHAR"})
> >          </insert>
>
> > Blog.java
> > -------------
> > public class Blog {
> >   private long id;
> >   private long authorid;
> >   private String subject;
> >   private String content;
> >   public Blog() {}
> >   //getters and setters.
>
> > }
>
> > /////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////
>
> > Now we try and work out how to do this with an assocation.  We could
> > start of  like this:
>
> > Blog.java
> > -------------
> > public class Blog {
> >   private long id;
> >   private Author author;      // <<<<<<<<<<<< now dont have athorID
> >   private String subject;
> >   private String content;
> >   public Blog() {}
> >   //getters and setters.
>
> > }
>
> > Now is the first crux, without an authorid, how can we insert?
>
> > BlogMapper.xml
> > ------------------------
> >         <select id="select" resultType="Blog">
> >             select * from blog
> >             where id = #{value}
> >         </select>
>
> >         <insert id="insert">
> >                <selectKey resultType="long" order="BEFORE"
> > keyProperty="id">
> >                      select SEQ_BLOG.nextval as value from dual
> >                 </selectKey>
> >                      insert into blog (authorid, subject, content)
> >                      values ( ??????#{authorid}?????????, #{subject},
> > #{content, jdbcType="VARCHAR"})
> >          </insert>
>
> > There are lots of examples of ResultMaps and selectParameters in the
> > Userguide, but nothing on "InsertMap" or "InsertParameters".
>
> >  Would this work?
>
> >         <insert id="insert">
> >                <selectKey resultType="long" order="BEFORE"
> > keyProperty="id">
> >                      select SEQ_BLOG.nextval as value from dual
> >                 </selectKey>
> >                      insert into blog (authorid, subject, content)
> >                      values (#{blog.getAuthor().getId()}, #{subject},
> > #{content, jdbcType="VARCHAR"})
> >          </insert>
>
> > Or do we need to "hack" the bean thusly:
>
> > public class Blog {
> >   private long id;
> >   private Author author;      // <<<<<<<<<<<< now dont have athorID
> >   private String subject;
> >   private String content;
> >   public Blog() {}
>
> >   // Helper function for insert only...
> >   public long getAuthorid() {
> >    if (author == null)
> >     return null;
> >   elese
> >     return author.getId();
> >   }
>
> >   //getters and setters.
>
> > }
>
> > Or other cunning solution?  Chaning all our ID columns to be table
> > specific, e.g. AUTHORID and BLOGID etc. is not really an option in
> > this project.
>
> > Crux 2 will be how to read it (I can probably deduce this from
> > userguide + experimentation).
> > Crux 3 will be when does the system "select" the dependent association
> > object?  Ive seem a number of posts which say that the lazy loading
> > does not work as expected?
>
> > Currently, we are actaully having the authorid and the author object
> > in the bean blog, and we set the author object by hand,and the
> > authorid using mybatis, which is tricky to keep in sync.
>
> > Thanks,
> > Simon.
Reply | Threaded
Open this post in threaded view
|

RE: how to insert with an assocation object, not its ID?

Poitras Christian
In reply to this post by Simon
For insert, the easiest way is to get the id from Author object.
        <insert id="insert">
               <selectKey resultType="long" order="BEFORE" keyProperty="id">
                     select SEQ_BLOG.nextval as value from dual
                </selectKey>
                     insert into blog (authorid, subject, content)
                     values (#{author.id}, #{subject}, #{content, jdbcType="VARCHAR"})
         </insert>

There is no need to use a getAuthor() since dot notation calls the getter by default (or use direct field access if object has no getter).

For lazy loading:
If you use a resultMap to specify to load the association (MyBatis will not guess automatically that you want to load an association), then the select is called either in a eager way (by default) or only when the getter of that property is called.
The result map:
<resultMap id="BlogMap" type="Blog">
<!-- some properties -->
<association property="author" column="authorid" select="AuthorMapper.select"/>
</resultMap>

With agressiveLazyLoading setting to true, the AuthorMapper.select will be called as soon as the Blog object is accessed. No need to worry about NPE but MyBatis may execute than you want to.
With agressiveLazyLoading setting to false, the AuthorMapper.select will be called only when getAuthor() is called. This will make it impossible to use this.author in Blog.equals for example.

Christian

-----Message d'origine-----
De : [hidden email] [mailto:[hidden email]] De la part de Simon
Envoyé : September-22-10 9:42 AM
À : mybatis-user
Objet : how to insert with an assocation object, not its ID?

We  have been using mybatis for about 4 months, but are struggling to
get one assocation example working with our DB.  Currently we are
doing all associations by hand.

Here is a simplified demo exmaple of what we have now.  Here we use
the BlogMapper to select a blog, we then have to use the AuthorMapper
to select the author based on the authorid of the blog if we need it.

CREATE TABLE AUTHOR
(
   ID                   INTEGER                   NOT NULL
   NAME            VARCHAR2(4000)
NOT NULL
)

CREATE TABLE BLOG
(
   ID                   INTEGER                   NOT NULL
   AUTHORID    INTEGER
NOT NULL
   SUBJECT        VARCHAR2(4000)
NOT NULL
   CONTENT       VARCHAR2(4000)
)

AuthorMapper.xml
--------------------------
        <select id="select" resultType="com.mypackage.Author">
            select * from author
            where id = #{value}
        </select>

        <insert id="insert">
               <selectKey resultType="long" order="BEFORE"
keyProperty="id">
                     select SEQ_AUTHOR.nextval as value from dual
                </selectKey>
                     insert into author (name)
                     values ( #{name})
         </insert>

Author.java
----------------
public class Author {
  private long id;
  private String name;
  public Author() {}
  //getters and setters.
}

BlogMapper.xml
------------------------
        <select id="select" resultType="com.mypackage.Blog">
            select * from blog
            where id = #{value}
        </select>

        <insert id="insert">
               <selectKey resultType="long" order="BEFORE"
keyProperty="id">
                     select SEQ_BLOG.nextval as value from dual
                </selectKey>
                     insert into blog (authorid, subject, content)
                     values ( #{authorid}, #{subject}, #{content,
jdbcType="VARCHAR"})
         </insert>

Blog.java
-------------
public class Blog {
  private long id;
  private long authorid;
  private String subject;
  private String content;
  public Blog() {}
  //getters and setters.
}

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Now we try and work out how to do this with an assocation.  We could
start of  like this:

Blog.java
-------------
public class Blog {
  private long id;
  private Author author;      // <<<<<<<<<<<< now dont have athorID
  private String subject;
  private String content;
  public Blog() {}
  //getters and setters.
}

Now is the first crux, without an authorid, how can we insert?

BlogMapper.xml
------------------------
        <select id="select" resultType="Blog">
            select * from blog
            where id = #{value}
        </select>

        <insert id="insert">
               <selectKey resultType="long" order="BEFORE"
keyProperty="id">
                     select SEQ_BLOG.nextval as value from dual
                </selectKey>
                     insert into blog (authorid, subject, content)
                     values ( ??????#{authorid}?????????, #{subject},
#{content, jdbcType="VARCHAR"})
         </insert>

There are lots of examples of ResultMaps and selectParameters in the
Userguide, but nothing on "InsertMap" or "InsertParameters".

 Would this work?

        <insert id="insert">
               <selectKey resultType="long" order="BEFORE"
keyProperty="id">
                     select SEQ_BLOG.nextval as value from dual
                </selectKey>
                     insert into blog (authorid, subject, content)
                     values (#{blog.getAuthor().getId()}, #{subject},
#{content, jdbcType="VARCHAR"})
         </insert>

Or do we need to "hack" the bean thusly:

public class Blog {
  private long id;
  private Author author;      // <<<<<<<<<<<< now dont have athorID
  private String subject;
  private String content;
  public Blog() {}

  // Helper function for insert only...
  public long getAuthorid() {
   if (author == null)
    return null;
  elese
    return author.getId();
  }

  //getters and setters.
}

Or other cunning solution?  Chaning all our ID columns to be table
specific, e.g. AUTHORID and BLOGID etc. is not really an option in
this project.

Crux 2 will be how to read it (I can probably deduce this from
userguide + experimentation).
Crux 3 will be when does the system "select" the dependent association
object?  Ive seem a number of posts which say that the lazy loading
does not work as expected?

Currently, we are actaully having the authorid and the author object
in the bean blog, and we set the author object by hand,and the
authorid using mybatis, which is tricky to keep in sync.

Thanks,
Simon.
Reply | Threaded
Open this post in threaded view
|

Re: how to insert with an assocation object, not its ID?

asherwin
In reply to this post by Simon
On my POJO's i have both a fk id field as well as a field which holds
the fk'd object, I.E.:

class A {
  Long id;
  Long bId;
  B b;
}

class B {
  id;
}

What we do for a deep insert on A would be something like..

class ADaoService {

  BDaoService bDaoService;

  A insert(A a) {
    if (null != b && null == a.getBId()) {
      bDaoService.insert(a.getB());
      a.setBId(b.getId()); // the insert for b used a select key to
insert b.id
      // alternatively, the sqlmap for inserting A could perform this
check and choose to use b.id instead of setting it in java code
    }
    getMapper(AMapper.class).insert(a);
  }
}

And the deep result map would use the normal association syntax and
fill in both a.b and a.bId

Are these both the scenarios you're referring to?


On Sep 22, 10:03 am, Simon <[hidden email]> wrote:

> Thanks for the reply, but I think you missunderstand the problem.
>
> I AM inserting each row independently
>
> Currently I do have the FK Ids.
>
> But if you have FKids, then you dont have the associated object!  If
> you have the Object (i.e. Author), then you dont have the AUTHORID in
> the getters and setters, so cant do a simple inert of a single Blog
> row (the SQL expect the AUTHORID).
>
> If you want to use associations in selects, then my sweeping assumtion
> is that you HAVE to have the associated object (e.g. Author object in
> the Blog object in this case), not its ID.
>
> Or am I missing something?
>
> I guess you could have both the FK ID and the F Object, the FK ID is
> for inserting, the Object is for selecting, then you have to try and
> remember which one is valid in the Blog object at any given time (or
> try and keep them in sync manually).
>
> Im sure its not this hard...
>
> Simon.
>
> On Sep 22, 3:54 pm, asherwin <[hidden email]> wrote:
>
>
>
> > It seems to me that you're making life very difficult on yourself by
> > not storing the FK ID's in your java Objects...
>
> > Also, as far as I know, MyBatis mapper methods are designed to break
> > down into a single SQL statement, which is why you see ResultMaps but
> > not an InsertMap.  ResultMaps with associations and collections are
> > possible through joins, and mapping objects to one another (ideally
> > through PK's and [likely] indexed FK's).
>
> > However, there is no equivalent when inserting data into a database.
> > Some DB's support a bulk insert syntax, but this is still only for a
> > single table with multiple rows, not across multiple tables.
>
> > In the app I'm working on we have pretty elegant, lightweight ORM like
> > custom annotation driven setup with DAO services that act on our POJOs
> > which allow us to write some simple code to support such "deep
> > inserts" pretty easily.
>
> > Is there a reason why you do not want to store foreign keys between
> > tables, and carry those into Java?  Generally, that's what RDBMS are
> > for.. you're better off leveraging DB features then working around
> > them
>
> > On Sep 22, 9:42 am, Simon <[hidden email]> wrote:
>
> > > We  have been using mybatis for about 4 months, but are struggling to
> > > get one assocation example working with our DB.  Currently we are
> > > doing all associations by hand.
>
> > > Here is a simplified demo exmaple of what we have now.  Here we use
> > > the BlogMapper to select a blog, we then have to use the AuthorMapper
> > > to select the author based on the authorid of the blog if we need it.
>
> > > CREATE TABLE AUTHOR
> > > (
> > >    ID                   INTEGER                                                 NOT NULL
> > >    NAME            VARCHAR2(4000)
> > > NOT NULL
> > > )
>
> > > CREATE TABLE BLOG
> > > (
> > >    ID                   INTEGER                                                 NOT NULL
> > >    AUTHORID    INTEGER
> > > NOT NULL
> > >    SUBJECT        VARCHAR2(4000)
> > > NOT NULL
> > >    CONTENT       VARCHAR2(4000)
> > > )
>
> > > AuthorMapper.xml
> > > --------------------------
> > >         <select id="select" resultType="com.mypackage.Author">
> > >             select * from author
> > >             where id = #{value}
> > >         </select>
>
> > >         <insert id="insert">
> > >                <selectKey resultType="long" order="BEFORE"
> > > keyProperty="id">
> > >                      select SEQ_AUTHOR.nextval as value from dual
> > >                 </selectKey>
> > >                      insert into author (name)
> > >                      values ( #{name})
> > >          </insert>
>
> > > Author.java
> > > ----------------
> > > public class Author {
> > >   private long id;
> > >   private String name;
> > >   public Author() {}
> > >   //getters and setters.
>
> > > }
>
> > > BlogMapper.xml
> > > ------------------------
> > >         <select id="select" resultType="com.mypackage.Blog">
> > >             select * from blog
> > >             where id = #{value}
> > >         </select>
>
> > >         <insert id="insert">
> > >                <selectKey resultType="long" order="BEFORE"
> > > keyProperty="id">
> > >                      select SEQ_BLOG.nextval as value from dual
> > >                 </selectKey>
> > >                      insert into blog (authorid, subject, content)
> > >                      values ( #{authorid}, #{subject}, #{content,
> > > jdbcType="VARCHAR"})
> > >          </insert>
>
> > > Blog.java
> > > -------------
> > > public class Blog {
> > >   private long id;
> > >   private long authorid;
> > >   private String subject;
> > >   private String content;
> > >   public Blog() {}
> > >   //getters and setters.
>
> > > }
>
> > > /////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////
>
> > > Now we try and work out how to do this with an assocation.  We could
> > > start of  like this:
>
> > > Blog.java
> > > -------------
> > > public class Blog {
> > >   private long id;
> > >   private Author author;      // <<<<<<<<<<<< now dont have athorID
> > >   private String subject;
> > >   private String content;
> > >   public Blog() {}
> > >   //getters and setters.
>
> > > }
>
> > > Now is the first crux, without an authorid, how can we insert?
>
> > > BlogMapper.xml
> > > ------------------------
> > >         <select id="select" resultType="Blog">
> > >             select * from blog
> > >             where id = #{value}
> > >         </select>
>
> > >         <insert id="insert">
> > >                <selectKey resultType="long" order="BEFORE"
> > > keyProperty="id">
> > >                      select SEQ_BLOG.nextval as value from dual
> > >                 </selectKey>
> > >                      insert into blog (authorid, subject, content)
> > >                      values ( ??????#{authorid}?????????, #{subject},
> > > #{content, jdbcType="VARCHAR"})
> > >          </insert>
>
> > > There are lots of examples of ResultMaps and selectParameters in the
> > > Userguide, but nothing on "InsertMap" or "InsertParameters".
>
> > >  Would this work?
>
> > >         <insert id="insert">
> > >                <selectKey resultType="long" order="BEFORE"
> > > keyProperty="id">
> > >                      select SEQ_BLOG.nextval as value from dual
> > >                 </selectKey>
> > >                      insert into blog (authorid, subject, content)
> > >                      values (#{blog.getAuthor().getId()}, #{subject},
> > > #{content, jdbcType="VARCHAR"})
> > >          </insert>
>
> > > Or do we need to "hack" the bean thusly:
>
> > > public class Blog {
> > >   private long id;
> > >   private Author author;      // <<<<<<<<<<<< now dont have athorID
> > >   private String subject;
> > >   private String content;
> > >   public Blog() {}
>
> > >   // Helper function for insert only...
> > >   public long getAuthorid() {
> > >    if (author == null)
> > >     return null;
> > >   elese
> > >     return author.getId();
> > >   }
>
> > >   //getters and setters.
>
> > > }
>
> > > Or other cunning solution?  Chaning all our ID columns to be table
> > > specific, e.g. AUTHORID and BLOGID etc. is not really an option in
> > > this project.
>
> > > Crux 2 will be how to read it (I can probably deduce this from
> > > userguide + experimentation).
> > > Crux 3 will be when does the system "select" the dependent association
> > > object?  Ive seem a number of posts which say that the lazy loading
> > > does not work as expected?
>
> > > Currently, we are actaully having the authorid and the author object
> > > in the bean blog, and we set the author object by hand,and the
> > > authorid using mybatis, which is tricky to keep in sync.
>
> > > Thanks,
> > > Simon.
Reply | Threaded
Open this post in threaded view
|

Re: how to insert with an assocation object, not its ID?

Simon
Very interesting, thanks!

Im not actually trying to insert A and B together, Im happy insering
B, getting its ID, then inserting A.

The problem is, if you have an object instead of an ID as a field for
B on A, you cant insert an A (I think).

So I guess the answer is you have to have both the assocated object
AND its ID as fields in of the parent object.

I was hoping to avoid this due to the extra scope for errors (if one
get set without the other etc).

Simon.



On Sep 22, 4:14 pm, asherwin <[hidden email]> wrote:

> On my POJO's i have both a fk id field as well as a field which holds
> the fk'd object, I.E.:
>
> class A {
>   Long id;
>   Long bId;
>   B b;
>
> }
>
> class B {
>   id;
>
> }
>
> What we do for a deep insert on A would be something like..
>
> class ADaoService {
>
>   BDaoService bDaoService;
>
>   A insert(A a) {
>     if (null != b && null == a.getBId()) {
>       bDaoService.insert(a.getB());
>       a.setBId(b.getId()); // the insert for b used a select key to
> insert b.id
>       // alternatively, the sqlmap for inserting A could perform this
> check and choose to use b.id instead of setting it in java code
>     }
>     getMapper(AMapper.class).insert(a);
>   }
>
> }
>
> And the deep result map would use the normal association syntax and
> fill in both a.b and a.bId
>
> Are these both the scenarios you're referring to?
>
> On Sep 22, 10:03 am, Simon <[hidden email]> wrote:
>
> > Thanks for the reply, but I think you missunderstand the problem.
>
> > I AM inserting each row independently
>
> > Currently I do have the FK Ids.
>
> > But if you have FKids, then you dont have the associated object!  If
> > you have the Object (i.e. Author), then you dont have the AUTHORID in
> > the getters and setters, so cant do a simple inert of a single Blog
> > row (the SQL expect the AUTHORID).
>
> > If you want to use associations in selects, then my sweeping assumtion
> > is that you HAVE to have the associated object (e.g. Author object in
> > the Blog object in this case), not its ID.
>
> > Or am I missing something?
>
> > I guess you could have both the FK ID and the F Object, the FK ID is
> > for inserting, the Object is for selecting, then you have to try and
> > remember which one is valid in the Blog object at any given time (or
> > try and keep them in sync manually).
>
> > Im sure its not this hard...
>
> > Simon.
>
> > On Sep 22, 3:54 pm, asherwin <[hidden email]> wrote:
>
> > > It seems to me that you're making life very difficult on yourself by
> > > not storing the FK ID's in your java Objects...
>
> > > Also, as far as I know, MyBatis mapper methods are designed to break
> > > down into a single SQL statement, which is why you see ResultMaps but
> > > not an InsertMap.  ResultMaps with associations and collections are
> > > possible through joins, and mapping objects to one another (ideally
> > > through PK's and [likely] indexed FK's).
>
> > > However, there is no equivalent when inserting data into a database.
> > > Some DB's support a bulk insert syntax, but this is still only for a
> > > single table with multiple rows, not across multiple tables.
>
> > > In the app I'm working on we have pretty elegant, lightweight ORM like
> > > custom annotation driven setup with DAO services that act on our POJOs
> > > which allow us to write some simple code to support such "deep
> > > inserts" pretty easily.
>
> > > Is there a reason why you do not want to store foreign keys between
> > > tables, and carry those into Java?  Generally, that's what RDBMS are
> > > for.. you're better off leveraging DB features then working around
> > > them
>
> > > On Sep 22, 9:42 am, Simon <[hidden email]> wrote:
>
> > > > We  have been using mybatis for about 4 months, but are struggling to
> > > > get one assocation example working with our DB.  Currently we are
> > > > doing all associations by hand.
>
> > > > Here is a simplified demo exmaple of what we have now.  Here we use
> > > > the BlogMapper to select a blog, we then have to use the AuthorMapper
> > > > to select the author based on the authorid of the blog if we need it.
>
> > > > CREATE TABLE AUTHOR
> > > > (
> > > >    ID                   INTEGER                                                 NOT NULL
> > > >    NAME            VARCHAR2(4000)
> > > > NOT NULL
> > > > )
>
> > > > CREATE TABLE BLOG
> > > > (
> > > >    ID                   INTEGER                                                 NOT NULL
> > > >    AUTHORID    INTEGER
> > > > NOT NULL
> > > >    SUBJECT        VARCHAR2(4000)
> > > > NOT NULL
> > > >    CONTENT       VARCHAR2(4000)
> > > > )
>
> > > > AuthorMapper.xml
> > > > --------------------------
> > > >         <select id="select" resultType="com.mypackage.Author">
> > > >             select * from author
> > > >             where id = #{value}
> > > >         </select>
>
> > > >         <insert id="insert">
> > > >                <selectKey resultType="long" order="BEFORE"
> > > > keyProperty="id">
> > > >                      select SEQ_AUTHOR.nextval as value from dual
> > > >                 </selectKey>
> > > >                      insert into author (name)
> > > >                      values ( #{name})
> > > >          </insert>
>
> > > > Author.java
> > > > ----------------
> > > > public class Author {
> > > >   private long id;
> > > >   private String name;
> > > >   public Author() {}
> > > >   //getters and setters.
>
> > > > }
>
> > > > BlogMapper.xml
> > > > ------------------------
> > > >         <select id="select" resultType="com.mypackage.Blog">
> > > >             select * from blog
> > > >             where id = #{value}
> > > >         </select>
>
> > > >         <insert id="insert">
> > > >                <selectKey resultType="long" order="BEFORE"
> > > > keyProperty="id">
> > > >                      select SEQ_BLOG.nextval as value from dual
> > > >                 </selectKey>
> > > >                      insert into blog (authorid, subject, content)
> > > >                      values ( #{authorid}, #{subject}, #{content,
> > > > jdbcType="VARCHAR"})
> > > >          </insert>
>
> > > > Blog.java
> > > > -------------
> > > > public class Blog {
> > > >   private long id;
> > > >   private long authorid;
> > > >   private String subject;
> > > >   private String content;
> > > >   public Blog() {}
> > > >   //getters and setters.
>
> > > > }
>
> > > > /////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////
>
> > > > Now we try and work out how to do this with an assocation.  We could
> > > > start of  like this:
>
> > > > Blog.java
> > > > -------------
> > > > public class Blog {
> > > >   private long id;
> > > >   private Author author;      // <<<<<<<<<<<< now dont have athorID
> > > >   private String subject;
> > > >   private String content;
> > > >   public Blog() {}
> > > >   //getters and setters.
>
> > > > }
>
> > > > Now is the first crux, without an authorid, how can we insert?
>
> > > > BlogMapper.xml
> > > > ------------------------
> > > >         <select id="select" resultType="Blog">
> > > >             select * from blog
> > > >             where id = #{value}
> > > >         </select>
>
> > > >         <insert id="insert">
> > > >                <selectKey resultType="long" order="BEFORE"
> > > > keyProperty="id">
> > > >                      select SEQ_BLOG.nextval as value from dual
> > > >                 </selectKey>
> > > >                      insert into blog (authorid, subject, content)
> > > >                      values ( ??????#{authorid}?????????, #{subject},
> > > > #{content, jdbcType="VARCHAR"})
> > > >          </insert>
>
> > > > There are lots of examples of ResultMaps and selectParameters in the
> > > > Userguide, but nothing on "InsertMap" or "InsertParameters".
>
> > > >  Would this work?
>
> > > >         <insert id="insert">
> > > >                <selectKey resultType="long" order="BEFORE"
> > > > keyProperty="id">
> > > >                      select SEQ_BLOG.nextval as value from dual
> > > >                 </selectKey>
> > > >                      insert into blog (authorid, subject, content)
> > > >                      values (#{blog.getAuthor().getId()}, #{subject},
> > > > #{content, jdbcType="VARCHAR"})
> > > >          </insert>
>
> > > > Or do we need to "hack" the bean thusly:
>
> > > > public class Blog {
> > > >   private long id;
> > > >   private Author author;      // <<<<<<<<<<<< now dont have athorID
> > > >   private String subject;
> > > >   private String content;
> > > >   public Blog() {}
>
> > > >   // Helper function for insert only...
> > > >   public long getAuthorid() {
> > > >    if (author == null)
> > > >     return null;
> > > >   elese
> > > >     return author.getId();
> > > >   }
>
> > > >   //getters and setters.
>
> > > > }
>
> > > > Or other cunning solution?  Chaning all our ID columns to be table
> > > > specific, e.g. AUTHORID and BLOGID etc. is not really an option in
> > > > this project.
>
> > > > Crux 2 will be how to read it (I can probably deduce this from
> > > > userguide + experimentation).
> > > > Crux 3 will be when does the system "select" the dependent association
> > > > object?  Ive seem a number of posts which say that the lazy loading
> > > > does not work as expected?
>
> > > > Currently, we are actaully having the authorid and the author object
> > > > in the bean blog, and we set the author object by hand,and the
> > > > authorid using mybatis, which is tricky to keep in sync.
>
> > > > Thanks,
> > > > Simon.
Reply | Threaded
Open this post in threaded view
|

Re: how to insert with an assocation object, not its ID?

Raj Nagappan
Hi Simon, as far as I know, in Blog you should store both the Author
object and authorId. This will make the association mapping easy as
you just pass authorId as the column id to use.

This is one key way that Mybatis differs from full ORM like JPA /
Hibernate. I had the opposite problem because I learnt Mybatis (well
it's precursor Ibatis) before I learnt JPA. So when I first tried JPA
I still had the authorId in there as well as the Author object and I
wondered why JPA refused to manage it properly for me.

But this reminds me of another similar issue, I might make a new
thread for it though.

Raj.

On Sep 23, 1:20 am, Simon <[hidden email]> wrote:

> Very interesting, thanks!
>
> Im not actually trying to insert A and B together, Im happy insering
> B, getting its ID, then inserting A.
>
> The problem is, if you have an object instead of an ID as a field for
> B on A, you cant insert an A (I think).
>
> So I guess the answer is you have to have both the assocated object
> AND its ID as fields in of the parent object.
>
> I was hoping to avoid this due to the extra scope for errors (if one
> get set without the other etc).
>
> Simon.
>
> On Sep 22, 4:14 pm, asherwin <[hidden email]> wrote:
>
> > On my POJO's i have both a fk id field as well as a field which holds
> > the fk'd object, I.E.:
>
> > class A {
> >   Long id;
> >   Long bId;
> >   B b;
>
> > }
>
> > class B {
> >   id;
>
> > }
>
> > What we do for a deep insert on A would be something like..
>
> > class ADaoService {
>
> >   BDaoService bDaoService;
>
> >   A insert(A a) {
> >     if (null != b && null == a.getBId()) {
> >       bDaoService.insert(a.getB());
> >       a.setBId(b.getId()); // the insert for b used a select key to
> > insert b.id
> >       // alternatively, the sqlmap for inserting A could perform this
> > check and choose to use b.id instead of setting it in java code
> >     }
> >     getMapper(AMapper.class).insert(a);
> >   }
>
> > }
>
> > And the deep result map would use the normal association syntax and
> > fill in both a.b and a.bId
>
> > Are these both the scenarios you're referring to?
>
> > On Sep 22, 10:03 am, Simon <[hidden email]> wrote:
>
> > > Thanks for the reply, but I think you missunderstand the problem.
>
> > > I AM inserting each row independently
>
> > > Currently I do have the FK Ids.
>
> > > But if you have FKids, then you dont have the associated object!  If
> > > you have the Object (i.e. Author), then you dont have the AUTHORID in
> > > the getters and setters, so cant do a simple inert of a single Blog
> > > row (the SQL expect the AUTHORID).
>
> > > If you want to use associations in selects, then my sweeping assumtion
> > > is that you HAVE to have the associated object (e.g. Author object in
> > > the Blog object in this case), not its ID.
>
> > > Or am I missing something?
>
> > > I guess you could have both the FK ID and the F Object, the FK ID is
> > > for inserting, the Object is for selecting, then you have to try and
> > > remember which one is valid in the Blog object at any given time (or
> > > try and keep them in sync manually).
>
> > > Im sure its not this hard...
>
> > > Simon.
>
> > > On Sep 22, 3:54 pm, asherwin <[hidden email]> wrote:
>
> > > > It seems to me that you're making life very difficult on yourself by
> > > > not storing the FK ID's in your java Objects...
>
> > > > Also, as far as I know, MyBatis mapper methods are designed to break
> > > > down into a single SQL statement, which is why you see ResultMaps but
> > > > not an InsertMap.  ResultMaps with associations and collections are
> > > > possible through joins, and mapping objects to one another (ideally
> > > > through PK's and [likely] indexed FK's).
>
> > > > However, there is no equivalent when inserting data into a database.
> > > > Some DB's support a bulk insert syntax, but this is still only for a
> > > > single table with multiple rows, not across multiple tables.
>
> > > > In the app I'm working on we have pretty elegant, lightweight ORM like
> > > > custom annotation driven setup with DAO services that act on our POJOs
> > > > which allow us to write some simple code to support such "deep
> > > > inserts" pretty easily.
>
> > > > Is there a reason why you do not want to store foreign keys between
> > > > tables, and carry those into Java?  Generally, that's what RDBMS are
> > > > for.. you're better off leveraging DB features then working around
> > > > them
>
> > > > On Sep 22, 9:42 am, Simon <[hidden email]> wrote:
>
> > > > > We  have been using mybatis for about 4 months, but are struggling to
> > > > > get one assocation example working with our DB.  Currently we are
> > > > > doing all associations by hand.
>
> > > > > Here is a simplified demo exmaple of what we have now.  Here we use
> > > > > the BlogMapper to select a blog, we then have to use the AuthorMapper
> > > > > to select the author based on the authorid of the blog if we need it.
>
> > > > > CREATE TABLE AUTHOR
> > > > > (
> > > > >    ID                   INTEGER                                                 NOT NULL
> > > > >    NAME            VARCHAR2(4000)
> > > > > NOT NULL
> > > > > )
>
> > > > > CREATE TABLE BLOG
> > > > > (
> > > > >    ID                   INTEGER                                                 NOT NULL
> > > > >    AUTHORID    INTEGER
> > > > > NOT NULL
> > > > >    SUBJECT        VARCHAR2(4000)
> > > > > NOT NULL
> > > > >    CONTENT       VARCHAR2(4000)
> > > > > )
>
> > > > > AuthorMapper.xml
> > > > > --------------------------
> > > > >         <select id="select" resultType="com.mypackage.Author">
> > > > >             select * from author
> > > > >             where id = #{value}
> > > > >         </select>
>
> > > > >         <insert id="insert">
> > > > >                <selectKey resultType="long" order="BEFORE"
> > > > > keyProperty="id">
> > > > >                      select SEQ_AUTHOR.nextval as value from dual
> > > > >                 </selectKey>
> > > > >                      insert into author (name)
> > > > >                      values ( #{name})
> > > > >          </insert>
>
> > > > > Author.java
> > > > > ----------------
> > > > > public class Author {
> > > > >   private long id;
> > > > >   private String name;
> > > > >   public Author() {}
> > > > >   //getters and setters.
>
> > > > > }
>
> > > > > BlogMapper.xml
> > > > > ------------------------
> > > > >         <select id="select" resultType="com.mypackage.Blog">
> > > > >             select * from blog
> > > > >             where id = #{value}
> > > > >         </select>
>
> > > > >         <insert id="insert">
> > > > >                <selectKey resultType="long" order="BEFORE"
> > > > > keyProperty="id">
> > > > >                      select SEQ_BLOG.nextval as value from dual
> > > > >                 </selectKey>
> > > > >                      insert into blog (authorid, subject, content)
> > > > >                      values ( #{authorid}, #{subject}, #{content,
> > > > > jdbcType="VARCHAR"})
> > > > >          </insert>
>
> > > > > Blog.java
> > > > > -------------
> > > > > public class Blog {
> > > > >   private long id;
> > > > >   private long authorid;
> > > > >   private String subject;
> > > > >   private String content;
> > > > >   public Blog() {}
> > > > >   //getters and setters.
>
> > > > > }
>
> > > > > /////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////
>
> > > > > Now we try and work out how to do this with an assocation.  We could
> > > > > start of  like this:
>
> > > > > Blog.java
> > > > > -------------
> > > > > public class Blog {
> > > > >   private long id;
> > > > >   private Author author;      // <<<<<<<<<<<< now dont have athorID
> > > > >   private String subject;
> > > > >   private String content;
> > > > >   public Blog() {}
> > > > >   //getters and setters.
>
> > > > > }
>
> > > > > Now is the first crux, without an authorid, how can we insert?
>
> > > > > BlogMapper.xml
> > > > > ------------------------
> > > > >         <select id="select" resultType="Blog">
> > > > >             select * from blog
> > > > >             where id = #{value}
> > > > >         </select>
>
> > > > >         <insert id="insert">
> > > > >                <selectKey resultType="long" order="BEFORE"
> > > > > keyProperty="id">
> > > > >                      select SEQ_BLOG.nextval as value from dual
> > > > >                 </selectKey>
> > > > >                      insert into blog (authorid, subject, content)
> > > > >                      values ( ??????#{authorid}?????????, #{subject},
> > > > > #{content, jdbcType="VARCHAR"})
> > > > >          </insert>
>
> > > > > There are lots of examples of ResultMaps and selectParameters in the
> > > > > Userguide, but nothing on "InsertMap" or "InsertParameters".
>
> > > > >  Would this work?
>
> > > > >         <insert id="insert">
> > > > >                <selectKey resultType="long" order="BEFORE"
> > > > > keyProperty="id">
> > > > >                      select SEQ_BLOG.nextval as value from dual
> > > > >                 </selectKey>
> > > > >                      insert into blog (authorid, subject, content)
> > > > >                      values (#{blog.getAuthor().getId()}, #{subject},
> > > > > #{content, jdbcType="VARCHAR"})
> > > > >          </insert>
>
> > > > > Or do we need to "hack" the bean thusly:
>
> > > > > public class Blog {
> > > > >   private long id;
> > > > >   private Author author;      // <<<<<<<<<<<< now dont have athorID
> > > > >   private String subject;
> > > > >   private String content;
> > > > >   public Blog() {}
>
> > > > >   // Helper function for insert only...
> > > > >   public long getAuthorid() {
> > > > >    if (author == null)
> > > > >     return null;
> > > > >   elese
> > > > >     return author.getId();
> > > > >   }
>
> > > > >   //getters and setters.
>
> > > > > }
>
> > > > > Or other cunning solution?  Chaning all our ID columns to be table
> > > > > specific, e.g. AUTHORID and BLOGID etc. is not really an option in
> > > > > this project.
>
> > > > > Crux 2 will be how to read it (I can probably deduce this from
> > > > > userguide + experimentation).
> > > > > Crux 3 will be when does the system "select" the dependent association
> > > > > object?  Ive seem a
>
> ...
>
> read more »
Reply | Threaded
Open this post in threaded view
|

Re: how to insert with an assocation object, not its ID?

Jackie Li
<resultMap type="com.mycompany.daos.User" id="user">
<result column="uid" javaType="string" property="id" />
<result column="uname" javaType="string" property="name" />
<result column="email" javaType="string" property="email" />
</resultMap>
<resultMap type="com.mycompany.daos.Blog" id="blog">
<result column="id" property="id" />
<result column="content" property="content" />
<result column="category" property="category" />
<collection property="author" column="authorId" select="getUser"></collection>
</resultMap>

<select id="getUser" resultMap="user">
<![CDATA[
select uid, upsw, uname, email from users where uid = #{uid}
     ]]>
</select>

<select id="getBlog" resultMap="blog" >
<![CDATA[
select id, authorId, content, category from blogs where id = #{id}
     ]]>
</select>

<insert id="insertBlogs" parameterType="java.util.HashMap">
insert into blogs (id, authorId, content, category) values ( #{id}, #{author.id}, #{content}, #{category}) 
</insert> 

for ORM, i think for hibernate, it's the way we need to maintain the correct state of object, then the database would be updated correctly (like insert, here).
but for myBatis, its the reverse, we create the SQLs, use them for generating objects.
a seperate insert shouldn't be any problem, as for my understanding. as we juz need SQLs to update the DB, no need to sync objects in memory.

Thanks & Regards,
Jackie's 


On Thu, Sep 23, 2010 at 09:12, Raj Nagappan <[hidden email]> wrote:
Hi Simon, as far as I know, in Blog you should store both the Author
object and authorId. This will make the association mapping easy as
you just pass authorId as the column id to use.

This is one key way that Mybatis differs from full ORM like JPA /
Hibernate. I had the opposite problem because I learnt Mybatis (well
it's precursor Ibatis) before I learnt JPA. So when I first tried JPA
I still had the authorId in there as well as the Author object and I
wondered why JPA refused to manage it properly for me.

But this reminds me of another similar issue, I might make a new
thread for it though.

Raj.

On Sep 23, 1:20 am, Simon <[hidden email]> wrote:
> Very interesting, thanks!
>
> Im not actually trying to insert A and B together, Im happy insering
> B, getting its ID, then inserting A.
>
> The problem is, if you have an object instead of an ID as a field for
> B on A, you cant insert an A (I think).
>
> So I guess the answer is you have to have both the assocated object
> AND its ID as fields in of the parent object.
>
> I was hoping to avoid this due to the extra scope for errors (if one
> get set without the other etc).
>
> Simon.
>
> On Sep 22, 4:14 pm, asherwin <[hidden email]> wrote:
>
> > On my POJO's i have both a fk id field as well as a field which holds
> > the fk'd object, I.E.:
>
> > class A {
> >   Long id;
> >   Long bId;
> >   B b;
>
> > }
>
> > class B {
> >   id;
>
> > }
>
> > What we do for a deep insert on A would be something like..
>
> > class ADaoService {
>
> >   BDaoService bDaoService;
>
> >   A insert(A a) {
> >     if (null != b && null == a.getBId()) {
> >       bDaoService.insert(a.getB());
> >       a.setBId(b.getId()); // the insert for b used a select key to
> > insert b.id
> >       // alternatively, the sqlmap for inserting A could perform this
> > check and choose to use b.id instead of setting it in java code
> >     }
> >     getMapper(AMapper.class).insert(a);
> >   }
>
> > }
>
> > And the deep result map would use the normal association syntax and
> > fill in both a.b and a.bId
>
> > Are these both the scenarios you're referring to?
>
> > On Sep 22, 10:03 am, Simon <[hidden email]> wrote:
>
> > > Thanks for the reply, but I think you missunderstand the problem.
>
> > > I AM inserting each row independently
>
> > > Currently I do have the FK Ids.
>
> > > But if you have FKids, then you dont have the associated object!  If
> > > you have the Object (i.e. Author), then you dont have the AUTHORID in
> > > the getters and setters, so cant do a simple inert of a single Blog
> > > row (the SQL expect the AUTHORID).
>
> > > If you want to use associations in selects, then my sweeping assumtion
> > > is that you HAVE to have the associated object (e.g. Author object in
> > > the Blog object in this case), not its ID.
>
> > > Or am I missing something?
>
> > > I guess you could have both the FK ID and the F Object, the FK ID is
> > > for inserting, the Object is for selecting, then you have to try and
> > > remember which one is valid in the Blog object at any given time (or
> > > try and keep them in sync manually).
>
> > > Im sure its not this hard...
>
> > > Simon.
>
> > > On Sep 22, 3:54 pm, asherwin <[hidden email]> wrote:
>
> > > > It seems to me that you're making life very difficult on yourself by
> > > > not storing the FK ID's in your java Objects...
>
> > > > Also, as far as I know, MyBatis mapper methods are designed to break
> > > > down into a single SQL statement, which is why you see ResultMaps but
> > > > not an InsertMap.  ResultMaps with associations and collections are
> > > > possible through joins, and mapping objects to one another (ideally
> > > > through PK's and [likely] indexed FK's).
>
> > > > However, there is no equivalent when inserting data into a database.
> > > > Some DB's support a bulk insert syntax, but this is still only for a
> > > > single table with multiple rows, not across multiple tables.
>
> > > > In the app I'm working on we have pretty elegant, lightweight ORM like
> > > > custom annotation driven setup with DAO services that act on our POJOs
> > > > which allow us to write some simple code to support such "deep
> > > > inserts" pretty easily.
>
> > > > Is there a reason why you do not want to store foreign keys between
> > > > tables, and carry those into Java?  Generally, that's what RDBMS are
> > > > for.. you're better off leveraging DB features then working around
> > > > them
>
> > > > On Sep 22, 9:42 am, Simon <[hidden email]> wrote:
>
> > > > > We  have been using mybatis for about 4 months, but are struggling to
> > > > > get one assocation example working with our DB.  Currently we are
> > > > > doing all associations by hand.
>
> > > > > Here is a simplified demo exmaple of what we have now.  Here we use
> > > > > the BlogMapper to select a blog, we then have to use the AuthorMapper
> > > > > to select the author based on the authorid of the blog if we need it.
>
> > > > > CREATE TABLE AUTHOR
> > > > > (
> > > > >    ID                   INTEGER                                                 NOT NULL
> > > > >    NAME            VARCHAR2(4000)
> > > > > NOT NULL
> > > > > )
>
> > > > > CREATE TABLE BLOG
> > > > > (
> > > > >    ID                   INTEGER                                                 NOT NULL
> > > > >    AUTHORID    INTEGER
> > > > > NOT NULL
> > > > >    SUBJECT        VARCHAR2(4000)
> > > > > NOT NULL
> > > > >    CONTENT       VARCHAR2(4000)
> > > > > )
>
> > > > > AuthorMapper.xml
> > > > > --------------------------
> > > > >         <select id="select" resultType="com.mypackage.Author">
> > > > >             select * from author
> > > > >             where id = #{value}
> > > > >         </select>
>
> > > > >         <insert id="insert">
> > > > >                <selectKey resultType="long" order="BEFORE"
> > > > > keyProperty="id">
> > > > >                      select SEQ_AUTHOR.nextval as value from dual
> > > > >                 </selectKey>
> > > > >                      insert into author (name)
> > > > >                      values ( #{name})
> > > > >          </insert>
>
> > > > > Author.java
> > > > > ----------------
> > > > > public class Author {
> > > > >   private long id;
> > > > >   private String name;
> > > > >   public Author() {}
> > > > >   //getters and setters.
>
> > > > > }
>
> > > > > BlogMapper.xml
> > > > > ------------------------
> > > > >         <select id="select" resultType="com.mypackage.Blog">
> > > > >             select * from blog
> > > > >             where id = #{value}
> > > > >         </select>
>
> > > > >         <insert id="insert">
> > > > >                <selectKey resultType="long" order="BEFORE"
> > > > > keyProperty="id">
> > > > >                      select SEQ_BLOG.nextval as value from dual
> > > > >                 </selectKey>
> > > > >                      insert into blog (authorid, subject, content)
> > > > >                      values ( #{authorid}, #{subject}, #{content,
> > > > > jdbcType="VARCHAR"})
> > > > >          </insert>
>
> > > > > Blog.java
> > > > > -------------
> > > > > public class Blog {
> > > > >   private long id;
> > > > >   private long authorid;
> > > > >   private String subject;
> > > > >   private String content;
> > > > >   public Blog() {}
> > > > >   //getters and setters.
>
> > > > > }
>
> > > > > /////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////
>
> > > > > Now we try and work out how to do this with an assocation.  We could
> > > > > start of  like this:
>
> > > > > Blog.java
> > > > > -------------
> > > > > public class Blog {
> > > > >   private long id;
> > > > >   private Author author;      // <<<<<<<<<<<< now dont have athorID
> > > > >   private String subject;
> > > > >   private String content;
> > > > >   public Blog() {}
> > > > >   //getters and setters.
>
> > > > > }
>
> > > > > Now is the first crux, without an authorid, how can we insert?
>
> > > > > BlogMapper.xml
> > > > > ------------------------
> > > > >         <select id="select" resultType="Blog">
> > > > >             select * from blog
> > > > >             where id = #{value}
> > > > >         </select>
>
> > > > >         <insert id="insert">
> > > > >                <selectKey resultType="long" order="BEFORE"
> > > > > keyProperty="id">
> > > > >                      select SEQ_BLOG.nextval as value from dual
> > > > >                 </selectKey>
> > > > >                      insert into blog (authorid, subject, content)
> > > > >                      values ( ??????#{authorid}?????????, #{subject},
> > > > > #{content, jdbcType="VARCHAR"})
> > > > >          </insert>
>
> > > > > There are lots of examples of ResultMaps and selectParameters in the
> > > > > Userguide, but nothing on "InsertMap" or "InsertParameters".
>
> > > > >  Would this work?
>
> > > > >         <insert id="insert">
> > > > >                <selectKey resultType="long" order="BEFORE"
> > > > > keyProperty="id">
> > > > >                      select SEQ_BLOG.nextval as value from dual
> > > > >                 </selectKey>
> > > > >                      insert into blog (authorid, subject, content)
> > > > >                      values (#{blog.getAuthor().getId()}, #{subject},
> > > > > #{content, jdbcType="VARCHAR"})
> > > > >          </insert>
>
> > > > > Or do we need to "hack" the bean thusly:
>
> > > > > public class Blog {
> > > > >   private long id;
> > > > >   private Author author;      // <<<<<<<<<<<< now dont have athorID
> > > > >   private String subject;
> > > > >   private String content;
> > > > >   public Blog() {}
>
> > > > >   // Helper function for insert only...
> > > > >   public long getAuthorid() {
> > > > >    if (author == null)
> > > > >     return null;
> > > > >   elese
> > > > >     return author.getId();
> > > > >   }
>
> > > > >   //getters and setters.
>
> > > > > }
>
> > > > > Or other cunning solution?  Chaning all our ID columns to be table
> > > > > specific, e.g. AUTHORID and BLOGID etc. is not really an option in
> > > > > this project.
>
> > > > > Crux 2 will be how to read it (I can probably deduce this from
> > > > > userguide + experimentation).
> > > > > Crux 3 will be when does the system "select" the dependent association
> > > > > object?  Ive seem a
>
> ...
>
> read more »