You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Hycel Taylor <hy...@gmail.com> on 2005/08/11 03:30:11 UTC
Doing Batch Processing with IBatis from DAO Manager
Hi,
I'm converting my home grown custom DAO's over to IBatis sqlMaps and
the IBatis DAO's. I'm finding IBatis a lot simpler to use than my
own. However, I need to do a lot of batch processing and I need to do
it from the DAO tier. With my custom DAO manager, I simply passed the
same connection to all the DAO's and batching was pretty transparent
and simple. For example, I would instantiate my DAO's with the
following code:
private Connection destConnection;
private SchoolDAO schoolDAO;
private MajorDAO majorDAO;
private CourseDAO courseDAO;
=20
destConnection =3D
connectionFactory.getConnection(ServiceConnectionFactory.CUSTOM_CONNECTION)=
;
final ServiceConnection serviceConnection =3D new
ServiceConnection(destConnection, true, "MaxDB");
final DAOFactory daoFactory =3D new DAOFactory(serviceConnection);
schoolDAO =3D (SchoolDAO) daoFactory.getDAO(DAOFactory.SCHOLE_DAO);
majorDAO =3D (MajorDAO) daoFactory.getDAO(DAOFactory.MAJOR_DAO);
courseDAO =3D (CourseDAO) daoFactory.getDAO(DAOFactory.COURSE_DAO);
I would do insertions, updates or deletes using the given DAO's, for N
(say 1000) iterations. Then I would use a method like the following
to execute a batch.
private void doBatchUpdate() throws SQLException {
schoolDAO.executeBatch();
majorDAO.executeBatch();
courseDAO.executeBatch();
destConnection.commit();
destConnection.close();
}
This has been very powerful for me. Because no matter how complex my
model and no matter how many DAO's I happen to be using, I can easily
accomplish my task. I've even moved this capability up to my service
tier. By passing in a connection at the service tier I have
simplified my code even further because a given service class may one
or more DAO's.
I would like to have the same functionality using IBatis Data Access
Objects. Currently, I don't understand how to do this using your DAO
Manager. You have a clear example of how to do batching directly
using SqlMaps. But, I need to be able to do batching at the DAO tier.
Please, could you explain to me how I may do Batch processing using
IBatis DAO's.
Thank you.
Re: Doing Batch Processing with IBatis from DAO Manager
Posted by Hycel Taylor <hy...@gmail.com>.
Got it!
That will make code a lot cleaner too.
Thanks for the advice.
Re: Doing Batch Processing with IBatis from DAO Manager
Posted by Hycel Taylor <hy...@gmail.com>.
Hi again Clinton,
I got some sleep, looked at my code again and I now stand corrected on a
few issues. The insertCampusCourse method, was working properly. There is a
simple rule that I forgot about batching and that it is, you cannot search
for data that is currently being batched because none of the batched
commands have been committed. So, I modified the insertCampusCourse method
accordingly. It now looks like the following:
*Map<String, CampusCourseVO> campusCourseMap = new HashMap<String,
CampusCourseVO>();*
public CampusCourseVO insertCampusCourse(BaseExam exam, String
protectedCourseId) throws Exception {
CampusCourseVO campusCourse = new CampusCourseVO();
final TermVO term = new TermVO();
CampusVO campus = new CampusVO();
CourseVO course = new CourseVO();
term.setTermId(DEFUALT_SYS_TERM_ID);
campus = campusService.findByProtectedId(exam.getEscoId());
course = courseService.findByProtectedId(protectedCourseId);
campusCourse.setTerm(term);
campusCourse.setCampus(campus);
campusCourse.setCourse(course);
Integer termId = campusCourse.getTerm().getTermId();
Integer campusId = campusCourse.getCampus().getCampusId();
Integer courseId = campusCourse.getCourse().getCourseId();
String key = termId.toString() + "|" + campusId.toString() + "|" +
courseId.toString();
*final CampusCourseVO temp = campusCourseMap.get(key);*
if (temp == null) {
campusCourseService.insert(campusCourse);
*campusCourseMap.put(key, campusCourse);*
return campusCourse;
} else {
*return temp;*
}
}
In essence, I'm now storing CampusCourse value objects in a HashMap after
they have been posted. This works quite well because out of the 400,000
student exams that were taken for the given course, only 1500 campuses gave
the exam.
With this being the case, I'm going with the notion that my
BatchControllerService is also working properly. BatchControllerService
calls BatchControllerDao. BatchControllerDao uses
getSqlMapExecutor().startBatch() and getSqlMapExecutor().startBatch() for
batching. So, if I've understood things correctly, SqlMap has the ability to
batch all SQL statements between a sqlMap.startBatch and sqlMap.executeBatch()
(See code below):
public class *BatchControllerServiceImpl* extends ServiceBase implements
BatchControllerService {
BatchControllerDao dao;
public BatchControllerServiceImpl() {
dao = (BatchControllerDao) daoManager.getDao(BatchControllerDao.class);
}
public void startBatch() {
dao.startBatch();
}
public int executeBatch() {
return dao.executeBatch();
}
}
public class *SqlMapBatchControllerDao* extends SqlMapBaseDao implements
BatchControllerDao {
public SqlMapBatchControllerDao(DaoManager daoManager) {
super(daoManager);
}
public void startBatch() {
try {
getSqlMapExecutor().startBatch();
} catch (SQLException e) {
throw new DaoException("Unable to startBatch", e);
}
}
public int executeBatch() {
try {
return getSqlMapExecutor().executeBatch();
} catch (SQLException e) {
throw new DaoException("Unable to startBatch", e);
}
}
}
So, I now only have two questions:
1) Are my above assumption correct?
2) Is this a good way to handle batching using IBatis Dao's?
Thank you for your patients.
Hycel
Re: Doing Batch Processing with IBatis from DAO Manager
Posted by Hycel Taylor <hy...@gmail.com>.
Hi Clinton,
I must ask of you to excuse the verbosity of this email. But, I need to
gain a better understanding about how I might go about building a custom
batch API so that I may get IBatis DAO's to properly do batching. In our
last conversion, you explained the following:
"The batch control DAO is just a convenient, reusable DAO that provides
access to the batch API from the SQL Mapping framework, or your own custom
JDBC batching code."
So, I understand that I need to create a custom batch API. The question is
how I should go about interfacing with IBatis SQL statements and do this
transparent enough that all DAO's simply work when I turn batching on.
Before using IBatis, I had my own custom DAO API. The way I handled
batching was to set a Boolean flag, doBatching, to true. When doBatching was
true, I would create a single Statement from the given connection and
intercept every insert, update or delete command and add each command the
statement with an statement.addBatch(). Later, I could execute all
statements with a statement.executeBatch(). I'm supposing that in my custom
API, I will need to do something similar with IBatis sql statements. I don't
know for sure. I need help here.
Is this the best to go about solving this problem with IBatis? If so, how
might I go about intercepting IBatis sql statements? Is a there and
interface or one or more methods that I can use to communicate with IBatis
sqlMaps to accomplish this task? Or am I looking at this all wrong and
should consider a different approach?
I also admit to being a little confused about all of this because of
statements made on page 41 of the iBATIS-SqlMmaps-2 Developer Guide, which
states the following:
-----------------------------------------------------------
"If you have great number of non-query (insert/update/delete) statements to
execute, you might like to execute them as a batch to minimize network
traffic and allow the JDBC driver to perform additional optimization (e.g.
compression). Using batch is simple with SQLMap API, two simple methods
allow you demarcate the boundaries of the batch:
sqlMap.startBatch();
//… execute statements between
sqlMap.executeBatch();
Upon calling executeBatch(), all batched statements will execute through
the JDBC driver."
-----------------------------------------------------------
In my understanding of the above explanation, I created the following DAO:
public class SqlMapBatchControllerDao extends SqlMapBaseDao implements
BatchControllerDao {
public SqlMapBatchControllerDao(DaoManager daoManager) {
super(daoManager);
}
public void startBatch() {
startBatch();
try {
getSqlMapExecutor().startBatch();
} catch (SQLException e) {
throw new DaoException("Unable to startBatch", e);
}
}
public int executeBatch() {
try {
return getSqlMapExecutor().executeBatch();
} catch (SQLException e) {
throw new DaoException("Unable to startBatch", e);
}
}
}
I have a service class that uses this SqlMapBatchControllerDAO to start
batching. All of my Service classes share the same transaction because all
Service classes extend ServiceBase which uses a transaction manager (the one
the Brian Barnett shared, using a ThreadLocal (Thanks Brian!)). So, in my
migration class I have created to methods for starting and ending batches:
protected startBatch() {
serviceBase.startTransaction();
batchControllerService.startBatch();
}
protected executeBatch() {
batchControllerService.executeBatch();
serviceBase.commintTransaction();
serviceBase.endTransaction();
}
In my migration class, I have a method, called migrate(), that performs the
task of migration the data from an old database to a new database:
public void migrate() {
System.out.println("Migrating exams...");
try {
startBatch();
final ResultSet exams = getExams();
while (exams.next()) {
batchCounter++;
setBaseExamInfomation(exams, exam);
final CampusCourseVO campusCourse =
insertCampusCourse(exam,PUBLIC_COURSE_ID);
if (batchCounter == BATCH_COUNT) {
executeBatch();
startBatch();
updateCounters();
}
}
executeBatch();
updateCounters();
} catch (Exception e) {
e.printStackTrace();
} finally {
serviceBase.endTransaction();
}
}
It's in the method, insertCampusCourse(), where I have discovered that
batching is not working properly. In the method insertCampusCourse, I want
to check if the campusCourse already exists (where termId, campusId and
courseId find a match). If it does not exist, I want to post it. If it does
exist, I want to return it in a CampusCourseVO value object. In batch mode
this does work properly because even if I the campusCourse for the given
termId, campusId and courseId was initially not found and thus inserted to
the database, the next time the same termId, campusId, and courseId are
attained from the resultSet and sent to the insertCampusCourse() method, the
previously inserted record is not found. It is thus inserted again. Upon
running batchControllerService.executeBatch(), a duplicate key exception is
rightfully thrown. If I simply comment out the
batchControllerService.startBatch() and batchControllerService.executeBatch()
statements so that records are posting within a transaction without
batching, a match to a given termId, campusId and courseId will be found.
Thus, only one row for a given termId, campusId and courseId will be found
(See code below).
public CampusCourseVO insertCampusCourse(BaseExam exam, String
protectedCourseId) throws Exception {
CampusCourseVO campusCourse = new CampusCourseVO();
final TermVO term = new TermVO();
CampusVO campus = new CampusVO();
CourseVO course = new CourseVO();
term.setTermId(DEFUALT_SYS_TERM_ID);
campus = campusService.findByProtectedId(exam.getCampus());
course = courseService.findByProtectedId(protectedCourseId);
campusCourse.setTerm(term);
campusCourse.setCampus(campus);
campusCourse.setCourse(course);
try {
campusCourse = campusCourseService.findByTermCampusCourse(campusCourse);
} catch (RequestNotFoundException e) {
campusCourseService.insert(campusCourse);
}
return campusCourse;
}
So, again, I apologize for the length of this email. I may have two related
questions in this email, both concerning how to properly do batching. On one
end, it may be possible to successfully do batching with sqlMaps and I am
just not doing it properly. On the other end, I may need to actually create
my own batch API and need more instructions on how best to implement it. I'm
just not sure, and would appreciate any advice in this matter so that I may
accomplish the task of batching, using IBatis DAO's, as soon as possible.
Thanks.
On 8/13/05, Clinton Begin <cl...@gmail.com> wrote:
>
> If you're in the same transaction, and all of your DAOs are using the same
> database, there's no need to start more than one batch (and in fact it could
> be causing significant problems for you).
>
> The batch control DAO is just a convenient, reusable DAO that provides
> access to the batch API from the SQL Mapping framework, or your own custom
> JDBC batching code.
>
> Clinton
>
> On 8/13/05, Hycel Taylor <hy...@gmail.com> wrote:
> >
> > Interesting. I'm not sure what, batchControlDao, is doing here.
> > Currently, what I've just learned from others on this forum and what
> > I'm currently doing is the following:
> >
> > daoManager.startTransaction();
> >
> > schoolDao.startBatch();
> > majorDao.startBatch();
> > courseDao.startBatch();
> > ...
> >
> > // do work here.
> >
> > schoolDao.executeBatch();
> > majorDao.executeBatch();
> > courseDao.executeBatch();
> > ...
> >
> > daoManager.commitTransaction ();
> > daoManager.endTransaction();
> >
> > This seems to be working. Although, when I am batching this way, an
> > awful lot of locks are made on the database. This wasn't happening
> > when I was doing strait JDBC. So, I'm not sure if I'm using your DAO
> > API with batching properly.
> >
> > What do you suggest?
> >
>
>
Re: Doing Batch Processing with IBatis from DAO Manager
Posted by Clinton Begin <cl...@gmail.com>.
If you're in the same transaction, and all of your DAOs are using the same
database, there's no need to start more than one batch (and in fact it could
be causing significant problems for you).
The batch control DAO is just a convenient, reusable DAO that provides
access to the batch API from the SQL Mapping framework, or your own custom
JDBC batching code.
Clinton
On 8/13/05, Hycel Taylor <hy...@gmail.com> wrote:
>
> Interesting. I'm not sure what, batchControlDao, is doing here.
> Currently, what I've just learned from others on this forum and what
> I'm currently doing is the following:
>
> daoManager.startTransaction();
>
> schoolDao.startBatch();
> majorDao.startBatch();
> courseDao.startBatch();
> ...
>
> // do work here.
>
> schoolDao.executeBatch();
> majorDao.executeBatch();
> courseDao.executeBatch();
> ...
>
> daoManager.commitTransaction();
> daoManager.endTransaction();
>
> This seems to be working. Although, when I am batching this way, an
> awful lot of locks are made on the database. This wasn't happening
> when I was doing strait JDBC. So, I'm not sure if I'm using your DAO
> API with batching properly.
>
> What do you suggest?
>
Re: Doing Batch Processing with IBatis from DAO Manager
Posted by Hycel Taylor <hy...@gmail.com>.
Interesting. I'm not sure what, batchControlDao, is doing here.
Currently, what I've just learned from others on this forum and what
I'm currently doing is the following:
daoManager.startTransaction();
schoolDao.startBatch();
majorDao.startBatch();
courseDao.startBatch();
...
// do work here.
schoolDao.executeBatch();
majorDao.executeBatch();
courseDao.executeBatch();
...
daoManager.commitTransaction();
daoManager.endTransaction();
This seems to be working. Although, when I am batching this way, an
awful lot of locks are made on the database. This wasn't happening
when I was doing strait JDBC. So, I'm not sure if I'm using your DAO
API with batching properly.
What do you suggest?
Re: Doing Batch Processing with IBatis from DAO Manager
Posted by Clinton Begin <cl...@gmail.com>.
Hi,
Our DAO layer is a persistence abstraction layer...that is, it has no
concept of batching, as it's specific to JDBC and relational databases. The
you can batch statements, and control the execution of them by simply
writing your own executeBatch() method.
I recommend writing a single DAO for this, with the batch control methods.
So your code would look something like this:
try {
daoManager.startTransaction();
batchControlDAO.startBatch();
schoolDAO.doWork();
majorDAO.doWork();
courseDAO.doWork();
batchControlDAO.executeBatch();
daoManager.commitTransaction();
} finally {
daoManager.endTransaction();
}
All of your SQLMap DAOs (or JDBC DAOs) will have access to batch control
methods within the DAO.
Hope that helps.
Clinton
On 8/10/05, Hycel Taylor <hy...@gmail.com> wrote:
>
> Hi,
>
> I'm converting my home grown custom DAO's over to IBatis sqlMaps and
> the IBatis DAO's. I'm finding IBatis a lot simpler to use than my
> own. However, I need to do a lot of batch processing and I need to do
> it from the DAO tier. With my custom DAO manager, I simply passed the
> same connection to all the DAO's and batching was pretty transparent
> and simple. For example, I would instantiate my DAO's with the
> following code:
>
> private Connection destConnection;
> private SchoolDAO schoolDAO;
> private MajorDAO majorDAO;
> private CourseDAO courseDAO;
> =20
> destConnection =3D
> connectionFactory.getConnection(ServiceConnectionFactory.CUSTOM_CONNECTION
> )=
> ;
> final ServiceConnection serviceConnection =3D new
> ServiceConnection(destConnection, true, "MaxDB");
> final DAOFactory daoFactory =3D new DAOFactory(serviceConnection);
>
> schoolDAO =3D (SchoolDAO) daoFactory.getDAO(DAOFactory.SCHOLE_DAO);
> majorDAO =3D (MajorDAO) daoFactory.getDAO(DAOFactory.MAJOR_DAO);
> courseDAO =3D (CourseDAO) daoFactory.getDAO(DAOFactory.COURSE_DAO);
>
> I would do insertions, updates or deletes using the given DAO's, for N
> (say 1000) iterations. Then I would use a method like the following
> to execute a batch.
>
> private void doBatchUpdate() throws SQLException {
> schoolDAO.executeBatch();
> majorDAO.executeBatch();
> courseDAO.executeBatch();
> destConnection.commit();
> destConnection.close();
> }
>
> This has been very powerful for me. Because no matter how complex my
> model and no matter how many DAO's I happen to be using, I can easily
> accomplish my task. I've even moved this capability up to my service
> tier. By passing in a connection at the service tier I have
> simplified my code even further because a given service class may one
> or more DAO's.
>
> I would like to have the same functionality using IBatis Data Access
> Objects. Currently, I don't understand how to do this using your DAO
> Manager. You have a clear example of how to do batching directly
> using SqlMaps. But, I need to be able to do batching at the DAO tier.
>
> Please, could you explain to me how I may do Batch processing using
> IBatis DAO's.
>
> Thank you.
>