使用jdbc-odbc删除时间冲突的行 [英] Delete rows with conflicting times using jdbc-odbc
问题描述
我不太确定如何写标题，对此感到抱歉.我正在尝试将一些开始和停止计划时间加载到数据库中，但是有时同一系统的开始和停止时间会重叠，例如一次可能是5:30 to 12:30，然后同一系统的另一次可能是8:30 to 10:30希望避免将数据插入表中.
我正在使用jdbc和odbc桥在Java程序中完成所有这些操作，当用户单击生成计划"时，它将从文本文件中读取所有计划信息，然后将其插入数据库中.当程序读取已经存在的时间之间的时间时，我想跳过插入操作.
我的想法必须是如何比较我从文本文件获得的结束时间与数据库中时间的MAX值之间的比较，如果小于该值，则跳过此插入操作，但是我不知道如何进行绑定将MAX值转换为if语句.另一个想法是在完成所有插入之后，然后删除SCHEDULE_TIME大于SCHEDULE_TIME列中的最小值，而SCHEDULE_TIME小于SCHEDULE_TIME列中的最大值的行./p>
这是表中数据的示例:
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
这些行从开始到结束时间交替显示，其中VALUE_ENUM为1的所有行都是开始时间，而VALUE_ENUM为0的所有行都是停止时间.我正在尝试删除介于其他开始时间和结束时间之间的时间，在这种情况下，请删除第3行和第4行.请记住，此表实际上从文本文件创建了数百行，因此我不能只是手动删除它，而是如果我能找到避免插入的最佳方式.
这是我当前插入方法的副本，忽略我使用的所有多余列，它们与问题无关，它们只是在使用，因此我为正确的系统添加和删除了
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();
}
我希望我已经对此做了足够的解释，如果问题不清楚，抱歉.
我也已经在sql标记下发布了这个问题，以查看是否有人知道仅使用sql的方法.
更新:我之前输入了最后的开始时间和结束时间，然后再输入两次新的时间，就检查了新的开始时间是否大于之前的时间.一个，并且新的结束时间< =上一个结束时间，如果是，我将跳过插入.
但是我不得不对程序进行一些更改，使我无法再获得以前的开始/结束时间，现在我的想法是，如果我可以选择表中的最后2次并将其保存到表中的变量中， Java端，然后进行与之前相同的比较
感谢您的帮助，但最终我只对:进行了一次字符串替换，并将其转换为.然后进行了转换到double并将该时间保存到temp变量中，然后下次我有该系统的时间时，我检查开始时间是否大于最后一个开始时间，以及结束时间是否小于最后一个开始时间，因此如果它们介于我可以避免的范围之内，不是经过100％的测试，但它适用于我尝试的第一个系统
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 been 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
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
这篇关于使用jdbc-odbc删除时间冲突的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
