Delete rows with conflicting times using jdbc-odbc
I wasnt quite sure how to word the title so sorry about that. I am trying to load some start and stop schedule times into a database but sometimes a start and stop time for the same system will overlap such as one time may be 5:30 to 12:30
and then another time for the same system may be 8:30 to 10:30
I want to avoid inserting that data into the table.
I am using a jdbc and odbc bridge to do this all in a java program, when the user clicks generate schedules it reads all the schedule info from a text file, and then inserts it into the database. When the programs reads the times that are in between already existing times I want to skip doing the insert.
My Ideas so have to be开发者_如何转开发en to some how compared the end time i get from the text file with the MAX value of the times in the database and if its less than that value then skip this insert but I dont know how to tie the MAX value into an if statement. Another idea was after im done all the inserts then just delete the rows where the SCHEDULE_TIME
is greater than the min value in the SCHEDULE_TIME
column and the SCHEDULE_TIME
is less than the max value in the SCHEDULE_TIME
column.
here is an example of what the data in my table looks like:
SITE_ID ------- DEV_ID ------- SCHEDULE_TIME ------- VALUE_ENUM
---------------------------------------------------------------
1 3000 09:30:00 1
1 3000 15:30:00 0
1 3000 12:30:00 1
1 3000 13:30:00 0
1 3000 16:30:00 1
1 3000 18:30:00 0
the rows alternate from start top to stop time all the rows where VALUE_ENUM
are 1 are start times and all the rows where VALUE_ENUM
are 0 are stop times. Im trying to delete the time that falls between a other start and stop times in this case delete rows 3 and 4. Keep in mind this tables actually creates hundreds of rows from the text file so I can not just delete it manually, it would be best if I could find a way to just avoid inserting it.
Here is a copy of my current insert method, ignore all the extra columns I use, they are unrelated to the problem, they are just using so I add and delete for the correct systems.
private void Insert() throws SQLException
{
stmt = conn.createStatement();
String sqlStm = "update ARRAY_BAC_SCH_Schedule set SCHEDULE_TIME = {t '" + finalEnd + "'} WHERE SCHEDULE_TIME >= {t '" + finalStart + "'} AND" +
" SCHEDULE_TIME <= {t '" + finalEnd + "'} AND VALUE_ENUM = 0 AND DEV_ID = " + devID + " and INSTANCE = " + instanceNum;
int updateSuccess = stmt.executeUpdate(sqlStm);
if (updateSuccess < 1)
{
sqlStm = "insert into ARRAY_BAC_SCH_Schedule (SITE_ID, DEV_ID, INSTANCE, DAY, SCHEDULE_TIME, VALUE_ENUM, Value_Type) " +
" values (1, " + devID + ", " + instanceNum + ", " + day + ", {t '" + finalStart + "'}, 1, 'Unsupported')";
stmt.executeUpdate(sqlStm);
sqlStm = "insert into ARRAY_BAC_SCH_Schedule (SITE_ID, DEV_ID, INSTANCE, DAY, SCHEDULE_TIME, VALUE_ENUM, Value_Type) " +
" values (1," + devID + ", " + instanceNum + ", " + day + ", {t '" + finalEnd + "'}, 0, 'Unsupported')";
stmt.executeUpdate(sqlStm);
}
if(stmt!=null)
stmt.close();
}
I hope I explained this enough, sorry if the question is unclear.
I have also posted this question under sql tags to see if someone knows a way to do it with just sql.
Update: I had it working before by taking the last start and end time entering and then when I go to enter 2 new times I checked to see that the new start time was >= the previous one and that the new end time <= the previous end time and if they were I would skip the insert.
However I have had to make some changes to the program which I can no longer get the previous start/end time, my idea now is if I can select the last 2 times in the table and saved them to a variable on the java side and then do the same comparison I did before
I think the problem is with your design. If your text file is relying in the order you shouldn't be saving as is in the database.
I would have store that information in just one record. For example instead of having VALUE_ENUM and two records in the database you will just need one record with the following fields START_SCHEDULE_TIME and END_SCHEDULE_TIME. Now with that design you could just do a select like this:
select count(*) from ARRAY_BAC_SCH_Schedule where finalStart >= START_SCHEDULE_TIME and finalEnd <= END_SCHEDULE_TIME
if the select returns 1 then you just skip and don't insert.
Is it fair to say that the problem area is characterised/identified by 2 or more consecutive start times? I suspect it is.
If that is the case then perhaps just load everything you have into the table, then parse the table in time order. Each time you encounter 2 consecutive start times you know you must delete 'this' start/end pair.
Of course if you encounter a THIRD start time in a row you know you have to delete 2 end times.
so in pseudocode - The dataset is ordered by Dev_Id, schedule_time, value_enum desc
int NestCount = 0;
int CurrDevId = 0;
for each record
{
// Optional DevId Logic Starts Here
if ThisRecord.DevId <> CurrDevId then
{
if NestCount <> 0
{
CurrDevId = 0;
RollBack;
Shriek("Cannot Proceed Because The Data Are Fubar");
}
else
{
CurrDevId = ThisRecord.DevId;
}
} // And Ends Here
if
CurrDevId <> 0 && // Optional DevId Logic
IsAStartRecord()
{
If NestCount > 0
{
DeleteThisRecord();
}
NestCount += 1;
}
else // Is an end record
{
NestCount -= 1;
If NestCount > 0
{
DeleteThisRecord();
}
}
}
If it is possible to implement this as a stored procedure I probably would.
Does that help ?
thanks for the help, but I ended up just doing a string replace on the :
in the time and made it into a .
then converted it to a double
and saved that time into a temp variable and then the next time I had times for that system I checked if the start time was greater than the last start time and if the end time was less than the last one so if they fell between I could avoid adding, not 100% tested but it worked for the first system I tried
精彩评论