meg meg - 4 months ago 30
SQL Question

SQL jdbc query support required

There is a schema Flight(flightnumber,departure_city,arrival_city, departuretime,arrival_time, is_processed int)
pilot(pilot_number, pilot_name)

To assign a pilot to the flight I first scan the flight table and for each flight number I did a query(inside method called Assignment(Flight flight).

public void select(){
Flight flight = new flight();
query = “select * from flight”;
//for each flight do some query
public void Assignment(Flight flight){
//query with flightnumber = flight.flightnumber

Now the requirement has changed. A new column is_ processed is added to Flight table.

I need to use the column is_processed in Flight table, initial value 0 for each row. So, instead of scanning flight table, I have to use Assignment() (now it has no arguments) and find the first unprocessed flight in flights to do some query and then set is_processed value to 1.

database sql .. java jdbc
How do I do this? Thank you for reading my question. Hope I was clear.


One way to handle this

We can have filter criteria in the select statement

SELECT * FROM flight
WHERE is_processed =0 AND rownum=1 
//returns only one record with the status of `is_processed` as 0 

After the assignment update the is_processed value to 1 for the returned flight details. //do above actions under same transaction

Other way is to combine these steps in a stored-procedure and call the procedure from your java code

Let me know if I misunderstood your question