Hello.
I was trying a new apache derby SQL prepardstatement to send numbers incremented form one into three tables.
if it find the number in any of the tables than it is not the number I need to create the next primary key
in my payment table.
I am not sure if the logic is correct and I am not sure if searching three tables is checking all the records.
Does someone know how this could be done?
There is no error. my payment table contains pymt_num=1 already. In my attempt (shown in output) It test the integer one
and it passes. Actually, there is an error when the form continues to insert the payment record where it will
not duplicate the primary key (pymt_num = "1").
It should find any foreign key pymt=1 and primary key pymt_num=1 and trigger the generation of another number. I did not include the integer generator.
uniqueBookingAndTotalsPymtNum: <--statement
CLASS
public class ConnectBookingDAO extends MasterForm implements BookingDAO {:
--> in public boolean bUniquePaymentNumber((1) var: (int num)<--
: SELECT b.pymt,t.pymt,p.pymt_num FROM booking AS b,booking_totals AS t,payment AS p WHERE b.pymt=? OR t.pymt=? OR p.pymt_num=?public boolean bUniquePaymentNumber(int aNum) throws SQLException, FileNotFoundException, IOException, UnknownUserNameException, IncorrectPasswordException, LoginException, ProfileException, InterruptedException {
int num = aNum;
bUniqueBookNum = true;
bUniqueTotalsNum = true;
bUniquePaymentNum=true;
bUnique = true;
//unique means that it is unique (not in table so it is what we are looking for.
//it is not there to be a doubled entry.
conn = connect();
ps = conn.prepareStatement(ModelUtils.getXMLResource("uniqueBookingAndTotalsPymtNum"));
ps.setInt(1, num);
ps.setInt(2,num);
ps.setInt(3,num);
rs = ps.executeQuery();
while (rs.next()) {
bookNum = rs.getInt(1);
totalsNum = rs.getInt(2);
pymtNum=rs.getInt(3);
}
if (bookNum == num) {
bUniqueBookNum = false;
}
if (totalsNum == num) {
bUniqueTotalsNum = false;
}
if (pymtNum == num) {
bUniquePaymentNum = false;
}
if (!bUniqueBookNum || !bUniqueTotalsNum||!bUniquePaymentNum) {
bUnique = false;
}
close(conn, ps);
return bUnique;
}output
CLASS
public class ConnectBookingDAO extends MasterForm implements BookingDAO {:
--> in public boolean bUniquePaymentNumber((1) var: (int num)<--
: CREATE TABLE booking(
book_num SMALLINT PRIMARY KEY,
instr_uid VARCHAR(50),
stu_uid VARCHAR(50),
instructor_password VARCHAR(50),
student_password VARCHAR(50),
payee_password VARCHAR(50),
book_location VARCHAR(50),
payee_uid VARCHAR(50),
book_start_date DATE,
book_end_date DATE,
book_start_time TIME,
book_end_time TIME,
book_instrument_type VARCHAR(50),
book_cancel_date DATE,
book_init_date DATE,
book_refund DECIMAL(5,2),
instr_pay_rate DECIMAL(5,2),
instr_rate DECIMAL(5,2),
initiated VARCHAR(50),
finalized VARCHAR(50),
book_status VARCHAR(60),
payment DECIMAL(5,2),
pymt SMALLINT )
CLASS
public class ConnectBookingDAO extends MasterForm implements BookingDAO {:
--> in public boolean bUniquePaymentNumber((1) var: (int num)<--
: CREATE TABLE booking_totals(
book_num SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1,INCREMENT BY 1),
instr_uid VARCHAR(50),
stu_uid VARCHAR(50),
instructor_password VARCHAR(50),
student_password VARCHAR(50),
payee_password VARCHAR(50),
book_location VARCHAR(50),
payee_uid VARCHAR(50),
book_start_date DATE,
book_end_date DATE,
book_start_time TIME,
book_end_time TIME,
book_instrument_type VARCHAR(50),
book_cancel_date DATE,
book_init_date DATE,
book_refund DECIMAL(5,2),
instr_pay_rate DECIMAL(5,2),
instr_rate DECIMAL(5,2),
initiated VARCHAR(50),
finalized VARCHAR(50),
book_status VARCHAR(60),
payment DECIMAL(5,2),
pymt SMALLINT )
CLASS
public class ConnectBookingDAO extends MasterForm implements BookingDAO {:
--> in public boolean bUniquePaymentNumber((1) var: (int num)<--
: CREATE TABLE payment (
pymt_num SMALLINT,
pymt_method VARCHAR(90),
pymt_amount DOUBLE,
pymt_date DATE,
collector_uid VARCHAR(50),
card_type VARCHAR(50),
debit VARCHAR(20),
name_on_card VARCHAR(60),
card_exp_month INTEGER,
card_exp_year INTEGER,
card_pin INTEGER,
security_code INTEGER,
PRIMARY KEY (pymt_num)
)
CLASS
public class ConnectBookingDAO extends MasterForm implements BookingDAO {:
--> in public boolean bUniquePaymentNumber((1) var: (int num)<--
: SELECT b.pymt,t.pymt,p.pymt_num FROM booking AS b,booking_totals AS t,payment AS p WHERE b.pymt=? OR t.pymt=? OR p.pymt_num=?