Inserting complex objects

[On Purpose]

Hi all

Good evening.

As you all know, mybatis can create complex objects from result sets. I would like to have the other way around, assume i have a complex object, suppose the following Java structure:

class Parent {

  

  /*fields of parent*/

  List<Child> children;

}

and 

class Child {

  /*fields of child*/

}

Assuming, there is a corresponding database table structure a table PARENT and another table CHILD with a FK reference constraint referencing an unique index of PARENT. Wouldn't it be convinient, to configure mybatis such that there is a mapper interfact with a method, say void insertParent(Parent parent) or even void insertParents(List<Parent> parents), that this not only inserts the Parent records but also the children records.

I would like to have this configured the following manner:

  <objectMap id="ParentObjectMap" type="Parent">

  <collection property="children" ofType="Child" insert="insertChild"/>

  </objectMap>

  

  <insert id="insertParent" parameterType="Parent" objectMap="ParentObjectMap">

  INSERT INTO parent (field_1, field_2, ...) VALUES (#{parent.field_1}, #{parent.field_2}, ...)

  </insert>

  

  <!-- I don't want to expose this one into the mapper interface -->

  <insert id="insertChild" parameterType="Child">

  INSERT INTO child (parent_field_1, field_1, field_2, ..) VALUES (#{parent.field_1}, #{child.field_1}, #{child.field_2}, ...)

  </insert>

Based on HEAD from Github, I have hacked XMLMapperBuilder, XMLStatementBuilder, Configuration, and some classes to support this feature into the configuration this night. So in principle it works, and from some other project, I already know how to extended DefaultSqlSession to support the nested insert, but hadn't time to demo it today. Motivation to look into it today, was the mentioned project runs on 3.1.x and well issue #39 is blocking the upgrade path - we are (mis-)using ResultMaps for that, referencing the inserts in the select attributes and not specifying a column attribute. (emacarron: "For a nested select the column attribute is mandatory...")

Before spending more time on this feature, I am going to ask some questions for your assistence.

• Is there any appetite for this feature at all? :-) For the following questions, I assume the answer would be "yes".
• Do you like the chosen naming? Or can you suggest alternatives?
• How does it / should work with annotations?
• I have created classes ObjectMap and ObjectMapping (and ObjectMappingResolver)?
• Any appetite to introduce javadoc comments?
• Whom to ask when being stuck on mybatis internals. Occasionally I would like to read some code comments...but can't find them.
• ObjectMap has quite some content from ResultMap - would you recomment refactoring ResultMap and sub-class it from ObjectMap?
• What about validations?
• What level of unit tests do you suggest? Any chance I could recycle some from ResultMap? If yes, which please?
• How to update the documentation?
• Who could do a code review - suppose during the pull request, right?

Regards,

on purpose!

[Guy Rouillier]

The following is just my opinion, so take it as such.  MyBatis stated role is that of a statement mapper, and specifically *not* an object mapper.  So, it is not attempting to be another Hibernate (which can do what you propose in a straightforward way.)  And MyBatis does very well in the role it has selected for itself.  That limited role allow it to be very small, lightweight, fast and quite easy to understand.

What you are addressing is beyond a simple statement mapping, and venturing into object mapping.  You don't mention what DBMS you are using, but one way to address your scenario without redefining the role of MyBatis is to use a Java stored procedure (available, for example, in PostgreSQL and others.)  Then, MyBatis can continue in its selected role, and you can implement what you describe in the stored procedure.

--

Guy Rouillier

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[On Purpose]

Hi Guy

Thanks a lot for your suggestions. When deploying java into a database I would lock in the database vendor. I am trying to avoid this. I also consider a database as a tool to store data rather than executing code.

Regarding Hibernate, I am uncertain whether this is a solution to my use case. I decided for mybatis as it allows me to have full control over the database structure as well as the queries. Also the amount of data can be quite huge, the Beans are generated by XJC. So I cannot put annotations there, etc.

Any suggestions how to work around with Hibernate?

Requirements would be:
- capabilities to stream
- no modification to the Beans
- no modification to the database structure
- full control over queries/statements and mapping

Regards
On purpose

[Guy Rouillier]

Java stored procedure was just an example. Theoretically, at least, in
the MyBatis XML mapper file, you could, for example, use Oracle's PL/SQL
language with an inline (anonymous) procedure block. Simply start your
SQL statement with BEGIN and end it with END. The use Velocity
scripting to break apart your complex Java object, and execute the
insert on Parent, and then in a loop, execute a series of insert on
Child.

To have any hope of doing this without hacking a bunch of MyBatis
classes (which is the path you started down), you would need to pick
which database you wanted to target, and exploit the capabilities of
it's native procedure language. I don't see how you could do this in a
way that is compatible across databases using a single set of SQL. You
could support multiple specific databases by constructing different XML
files for each one, then selecting the appropriate one at run time.

Good luck. I can't really discuss further without getting more
specific, with code examples.

--
Guy Rouillier

------ Original Message ------
From: "'On Purpose' via mybatis-user" <mybati...@googlegroups.com>
To: "mybatis-user" <mybati...@googlegroups.com>

[Tim]

I'm a little confused by the original motivation. Mybatis can already support that use case.

The 'naive' way to handle it is something like:

<insert id="insertParent" parameterType="Parent" objectMap="ParentObjectMap">

  -- if desired you can BEGIN TRANSACTION here

  INSERT INTO parent (field_1, field_2, ...) VALUES (#{parent.field_1}, #{parent.field_2}, ...)

  <foreach...>

     INSERT INTO child...

     -- or use sql inclusion here per child

  </foreach>

</insert>

I only say 'naive' because this is obviously going to be open to issues such as db parameter limits etc.

The arguably preferable way is to keep them separated and just handle this logic in your dao using transactions or batching. 

This is a great example of a single table bulk insert using batching created by Iwao but adding a parent insert before the children won't change the basic logic:

https://github.com/harawata/mybatis-bulk-insert

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.

[On Purpose]

Thanks Guy,

I actually wanted to contribute to mybatis with a smaller project and then learning the code base better when doing it. Github told me to ask questions here, I am just getting the feeling I am wrong here with that specific sort of questions. I just don't want to walk around and telling people things they anyway don't want to hear. So the final question is, are you in any way contributing to the code base or what is your motivation to keep the scope closed and narrowed? From an effort perspective, I guess I would have not touched more than 5-10 classes. 

Regards

on purpose.

[Guy Rouillier]

I'm not a major contributor, I'm a satisfied user and try to answer questions here when I can.  I don't have any "motivation" regarding the scope of MyBatis, other than a personal desire to see MyBatis remain the great tool it has always been, focused on staying a straightforward statement mapper, and not slowly morphing into another Hibernate (which I don't like.)

You are not wrong asking any questions here; I'm sorry if I somehow gave that impression.  And of course contributions are always welcome.  So if this is something that you want to contribute, code it up and submit a working sample on GitHub.  Working code will always get more responses than a general discussion.

[Guy Rouillier]

Have you actually tried this?  I have not, but what I would expect to happen with this is that MyBatis would pass this statement through the language processor, which would end up with a string something like this:

INSERT INTO parent...

INSERT INTO child...

INSERT INTO child...

INSERT INTO child...

Then MyBatis would pass this entire string onto the DBMS for execution.  But the DBMS is expecting a single statement, so you will like get an error at the start of the second insert, saying "INSERT not expected".  Separating the statements with a semicolon will not help.

As I stated in one of my earlier replies, if you include all these statements with semicolons within a BEGIN-END block (for Oracle and PostgreSQL, for example), then you end up submitting an anonymous procedure (or block), which both of those DBMSs permit.  I have done this successfully.  I may have done it because I tried the first approach initially, and got the reported error.

--

Guy Rouillier

------ Original Message ------

From: "Tim" <che...@gmail.com>

To: mybati...@googlegroups.com

Sent: 4/4/2018 5:46:09 AM

Subject: Re: Re[2]: Inserting complex objects

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.