aspose file tools*
The moose likes OO, Patterns, UML and Refactoring and the fly likes Design dilemma:Mysql stored procedure or java business objects? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Engineering » OO, Patterns, UML and Refactoring
Bookmark "Design dilemma:Mysql stored procedure or java business objects?" Watch "Design dilemma:Mysql stored procedure or java business objects?" New topic
Author

Design dilemma:Mysql stored procedure or java business objects?

chiyo matsushita
Greenhorn

Joined: Mar 05, 2011
Posts: 7
Hi,

I am looking for inputs on a design dilemma.

I have a requirement were content stored in TableA has to be modified and stored in TableB. This is a background process and transparent to the user. it will run at off peak hours. TableA has close to 1 million records.
The rules for modifying content have medium complexity. My options are:
1. Read batches of data from Table A into Java objects, program the rules in the business objects, modify the data, persist it to TableB.
2. Write a mysql procedure that does the same.

Given the volume of the content and the lack of user interaction, I am inclined to go with the second option. However I am also a bit hesitant as the approach introduces stored procedures in the mix.

Any advise on why I should pick one over the other?

Thanks in advance for the feedback.

--Chiyo.
Jimmy Clark
Ranch Hand

Joined: Apr 16, 2008
Posts: 2187
Do you think that you can implement all of the business rules using MySQL's version of SQL?
chiyo matsushita
Greenhorn

Joined: Mar 05, 2011
Posts: 7
Yes. The rules themselves are relatively simple so should be relatively easy to implement. It is the volume of content that is the challenge.
Jimmy Clark
Ranch Hand

Joined: Apr 16, 2008
Posts: 2187
Ok. What is the challenge associated with the volume? 1 million records is a small data set and certainly does not present a "challenge." Unless, there is something else that you have not mentioned...
chiyo matsushita
Greenhorn

Joined: Mar 05, 2011
Posts: 7
Jimmy Clark wrote:Ok. What is the challenge associated with the volume? 1 million records is a small data set and certainly does not present a "challenge." Unless, there is something else that you have not mentioned...


Hi Jimmy,

It is reassuring to know that you think 1 mill records is a small data set to handle with java. Can you suggest some best practices/patterns/potential pitfalls that would be useful in handling the volume?

Thanks in advance.
Chiyo
Jimmy Clark
Ranch Hand

Joined: Apr 16, 2008
Posts: 2187
If you know how to write efficient SQL code and all business requirements can be implemented with MySQL's version of SQL, then there is no reason to use Java.
shukla raghav
Ranch Hand

Joined: Aug 03, 2008
Posts: 200
I dont know whether MySQL supports Scheduling otherwise scheduling is the best way to perform this like Oracle Jobs are used for such purposes. Otherwise creating a procedure to serve the purpose is much better approach. because i assume you have create more than a million java objects to handle this.

in case if your modification is petty in one or two columns you can do the following

1. create a function for modification : funct1()
2. insert into new_tab (select col_1, col_2, funct1(col_3) from old_tab);

i hope there is no problem using user derfined functions in select clause of insert statements.


 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Design dilemma:Mysql stored procedure or java business objects?
 
Similar Threads
DAO class design
Hibernate one-to-on id mapping issue
Hibernate 3.1
Hibernate mapping
FOREIGN KEY