how can i map this effectively in ibatis?

Question

I have ClientDetails as bean with below structure:

public class ClientDetails{
    protected String id;
    protected String type;
    protected AddressDetails;
    protected PhoneDetails;
    //getters and setters follows...
}

public class AddressDetails{
     protected List<Address> address
    //getters and setters follows...
}

public Address{
    protected String addressType;
    protected String line1;
    protected String line2;
    protected String line3;
    //getters and setters follows...
}

public PhoneDetails{
    protected List<PhoneDetail> phoneDetail;
    //getters and setters follows...
}

public class PhoneDetail {

    protected String type;
    protected String phoneNumber;
   //getters and setters follows...
}

Tables in database: ClientDetails, AddressDetails, PhoneDetails.

I will be recieving List of ClientDetails in service layer and have to always update ClientDetails table. AddressDetails, PhoneDetails will be optionally updated(if not null). How can i map this effectively in ibatis?

It's typical to write a DAO for each table. – Feb 09 '12 at 07:16 — , Feb 09 '12 at 07:16

MalsR · Answer 1 · 2012-02-09T13:15:44.760

Akhilesh this is one way that comes to my mind (see below). I'm also learning Ibatis/MyBatis and gradually getting my grips into it. I take you want several inserts/updates to be included in a single transaction (performance in mind)? Similar to a batch update, wrap the batch in a single transaction. This example is based on IBatis 2.3.4

In your DAO class you can have,

final SqlMapClient sqlMap = getSqlMapClient();
try {
    sqlMap.startTransaction();
    sqlMap.startBatch();
    for (final ClientDetails clientXXX : clientDetailsList) {
        createOrUpdateClientDetails(sqlMapClient, clientXXX);
        createOrUpdateClientAddressDetails(sqlMapClient, clientXXX.getAddressDetails());
        createOrUpdateOtherXXXDetails(clientXXX.getXXXXX); //have similar private method
    }        
    sqlMap.executeBatch();
    sqlMap.commitTransaction();
} catch (final SQLException e) {
    throw new XXXException(e);
} finally {
    try {
        sqlMap.endTransaction();
    } catch (SQLException e) {
        throw new XXXException(e);
    }
}

private void createOrUpdateClientDetails(final SqlMapClient sqlMap, final List<ClientDetails> clientDetails) {
    for (final ClientDetails client: clientDetails) {
      if (client.getId() != null) {
           sqlMap.update("updateClientXXX", client); //your sqlmap method
      } else {
           sqlMap.insert("createClientXXX", client); //your sqlmap method
      }
    }
}

However note that whenever you are using a batched set of statements the database generated keys will not be generated until you have called the executeBatch() method. This means if you are using selectKey to update your objects with the generated key(s) they will return null. If you have any object that requires the newly generated key as part of another dependent insert then you can have this insert/parent insert before the startBatch(). For e.g. AddressDetails and PhoneDetails will have the customer_id as FK?

I would also look at your ClientDetails parent class again and see if if this can be simplified if possible. Again I'm uncertain if this is the approach you are after but I thought of sharing what I had in mind. Thanks

Thanks MalsR for your sincere thoughts. The very first point to agree upon is inserts/updates to be included in a single transaction. As i am using spring framework, so will be annotating the transactions at service class. can i still use executeBatch()? I am looking for the approach towards ibatis mapping and how stored proc can be written, so that an entire ClientDetails list can be passed to ibator layer having met optimal performance results — akhi, Feb 09 '12 at 14:19
@AkhileshShukla ah I see your using Spring, can I ask you're using Spring with Ibatis SqlMaps? As what I suggested was Ibatis DAO/Sqlmaps, Spring is new to me so I cannot answer the question regarding the executeBatch() (although does seem batch methods will not be offered based on my quick search). I found these two links I hope they will be of use to you http://stackoverflow.com/questions/1079114/spring-transactional-annotation-best-practice . Also see (search 'batch') http://static.springsource.org/spring/docs/2.0.x/api/org/springframework/orm/ibatis/SqlMapClientTemplate.html — MalsR, Feb 09 '12 at 15:15
yeah, I am using Spring with Ibatis SqlMaps. I guess executeBatch() will be nice thing to try out and check the limitaions that you mentioned about primary key generation. Actually I wanted spring to take care of entire transaction management and focus on Sql mapping and how the domain object can be passed to stored proc in this context. — akhi, Feb 09 '12 at 17:39

how can i map this effectively in ibatis?

1 Answers1