File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes how to solve this insert record problem ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "how to solve this insert record problem ?" Watch "how to solve this insert record problem ?" New topic

how to solve this insert record problem ?

Nakata kokuyo
Ranch Hand

Joined: Apr 13, 2005
Posts: 465
hi, good day, i having a problem, hope some one can provide me some good idea ,

problem description :
data field suppose insert into DB
- product_code , start_date, end_date

however, when insert into DB, i need to make sure start_date and end_date of record should not fall into date range which have same product_code

for example,

records in DB :

if another new record as below

this record cannot be save because the date range is fall into date range of product1 in DB

what i should do in this scenario, how to detect the date is fall into date range, so the record not to save ? really need your guide on this , thank you very much
[ February 09, 2006: Message edited by: Nakata kokuyo ]
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33132

You'll want to do this in two steps. Preferably within a transaction.

The first step is to query for the date range:
select count(*)
from table
where product_code = ?
and start_date >= ?
and end_date <= ?

Then if the count is zero, you can insert the record.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
pradeep jaladi
Ranch Hand

Joined: Nov 21, 2004
Posts: 65

I think you need to check 4 cases

1) start_date <= & end_date <=
2) start_date <= & end_date >=
3) start_date >= & end_date <=
4)start_date >= & end_date >=

Pradeep Jaladi

"rise above the Rest"

I agree. Here's the link:
subject: how to solve this insert record problem ?
jQuery in Action, 3rd edition