Hello I am using Apache derby embedded database: I have been using a system to display
records from the database described in "DisplayInstructorAvailableNowJTable.java".
it allows the result set to create a model from a prepared statement.
I have only uncovered the tip of the iceberg concerning JTABLE. I am not able
to manipulate the date and enter row of data in other than the stmt or prepared statement.
for now. Any info on loading the JTABLE other than the stmt or the prepared statement
would be appreciated.
Please find attached: availableTable_screenshot.doc :
I have a table that displays (JTABLE) all to the record from the intr_available table.
that describes all instructors and their availability at all locations. Instructors at locations
at given point in time. "start date and start time". derived from instr_available table
I have a booking table that book displays bookings between instructors and student at locations
at given point in time. "start date and start time". derived from booking table
My new button action will need to display (JTABLE) the filtered availability for said instructor.
I would like to write the SQL to display records from instr_available "ia" that are not
found for the instructor in the booking "b" table. Instructors have availability but are
not available if they are involved in the booking table.
CLASS
public class DisplayInstructorAvailableNowJTable {:
--> in public void createDataFrame(BOOKING , instr1 , cube1 , stu1) var: (String thisTableName,String instructor,String
aLocation,String aStudent)<--
: displayFilteredInstructorAvailableNowMinusStudent : SELECT
ia.instr_uid,ia.instr_location,ia.available_start_date,ia.available_end_date,ia.available_start_time,ia.available_end_time,b.
instr_uid FROM instr_available ia ,booking b WHERE ia.instr_location=? AND ia.instr_uid=? AND ia.instr_uid !=(SELECT
b.instr_uid FROM booking b, instr_available ia WHERE b.instr_uid=ia.instr_uid AND b.book_start_date = ia.available_start_date
AND b.book_start_time = ia.available_start_time) ORDER BY ia.available_start_date,ia.available_start_time
Aug 29, 2012 11:52:41 AM view.master.masterOptionPanels.MasterBookingOptionsPanel actionPerformed
SEVERE: null
java.sql.SQLException: Scalar subquery is only allowed to return a single row.
Why can I not have the Left table instr_available "ia" produce a list that does not allow records to
be displayed if it is found in the right table booking "b" ?
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package view.displayJTables;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import model.ModelUtils;
import model.dao.ConnectInstructorAvailableDAO;
import model.err.IncorrectPasswordException;
import model.err.LoginException;
import model.err.ProfileException;
import model.err.UnknownUserNameException;
import view.MusicSystems.MusicSystemsJDesktopManager;
import view.master.masterForms.MasterForm;
import view.utils.CachingResultSetTableModel;
import view.utils.ScrollingResultSetTableModel;
/**
*
* @author Steves
*/
public class DisplayInstructorAvailableNowJTable extends MasterForm {
public DisplayInstructorAvailableNowJTable(String aTableName, String aInstructor, String aLocation, String aStudent)
throws IOException, FileNotFoundException, SQLException, ProfileException, LoginException,
javax.security.auth.login.LoginException, model.err.LoginException, ClassNotFoundException, InstantiationException,
IllegalAccessException, UnknownUserNameException, IncorrectPasswordException, InterruptedException {
String instructor = aInstructor;
String thisTableName = aTableName;
String location = aLocation;
String student = aStudent;
createDataFrame(thisTableName, instructor, location, student);
}
public void createDataFrame(String aTableName, String aInstructor, String aLocation, String aStudent) throws IOException,
FileNotFoundException, SQLException, ProfileException {
String instructor = aInstructor;
String thisTableName = aTableName;
String location = aLocation;
String student = aStudent;
System.out.println(C + M + AND + thisTableName + ": thisTableName : ");
ConnectInstructorAvailableDAO cia = new ConnectInstructorAvailableDAO();
conn = cia.connect();
meta = conn.getMetaData();
System.out.println(C + M + AND + "displayFilteredInstructorAvailableNowMinusStudent" + AND +
ModelUtils.getXMLResource("displayFilteredInstructorAvailableNowMinusStudent"));
ps = (PreparedStatement) conn.prepareStatement(
ModelUtils.getXMLResource("displayFilteredInstructorAvailableNowMinusStudent"));
ps.setString(1, location);
ps.setString(2, instructor);
//ps.setString(3, student);
rs = ps.executeQuery();
if (SCROLLABLE) {
ScrollingResultSetTableModel model = new ScrollingResultSetTableModel(rs);
table = new JTable(model);
table.setAutoResizeMode(table.AUTO_RESIZE_OFF);
} else {
CachingResultSetTableModel model = new CachingResultSetTableModel(rs);
table = new JTable(model);
table.setAutoResizeMode(table.AUTO_RESIZE_OFF);
}
cia.close(cia.conn, cia.ps);
displayScrollPane = new JScrollPane(table);
displayScrollPane.setViewportView(table);
String title = " " + thisTableName + " With " + instructor + " At " + location;
System.out.println(C + M + AND + ": caller \n--->: SchoolJDesktopPane.createDataInnerFrame(" + displayScrollPane + c
+ thisTableName + ") : var: (displayScrollPane, thisTableName) <-----\n");
MusicSystemsJDesktopManager.createDataInnerFrame(displayScrollPane, title);
}
}
Thanks for any assistance with writing this prepared statement.
I was planning on getting the filtered data to display and then negotiate the next business rule. that will amend the latter.
When the new button action is activated it will know the instructor, student, and the location.
ps.setString(1, location);
ps.setString(2, instructor);
//ps.setString(3, student);
rs = ps.executeQuery();
so the SQL will need to additionally receive the param for the student and further filter the new JTABLE data not to include
said student if is found in the booking table.
student at location
at given point in time. "start date and start time". derived from booking table : derived from booking table
NOTE: The student is not part of the instr_available table : When the data is viewed
it is also a rule that if said student is involved in a booking at the given point in time that data should be filtered out
of the new button action too.
The new statement will have to receive the third unused ps.setString(3,student)
student param :
LIKE ... but does not work either.
<entry key="displayFilteredInstructorAvailableNow">SELECT
ia.instr_uid,ia.instr_location,ia.available_start_date,ia.available_end_date,ia.available_start_time,ia.available_end_time,b.
stu_uid FROM instr_available ia,booking b WHERE ia.instr_location=? AND ia.instr_uid=? AND b.stu_uid=? AND ia.instr_uid !=
(SELECT b.instr_uid FROM booking b, instr_available ia WHERE b.instr_uid=ia.instr_uid AND
b.book_start_date=ia.available_start_date AND b.book_start_time=ia.available_start_time ) AND b.stu_uid !=(SELECT b.stu_uid
FROM booking b, instr_available ia WHERE b.book_start_date=ia.available_start_date AND
b.book_start_time=ia.available_start_time )ORDER BY ia.available_start_date,ia.available_start_time </entry>
public class DisplayInstructorAvailableNowJTable {:
--> in public void createDataFrame(BOOKING , instr1 , cube1 , stu1) var: (String thisTableName,String instructor,String
aLocation,String aStudent)<--
: displayFilteredInstructorAvailableNow : SELECT
ia.instr_uid,ia.instr_location,ia.available_start_date,ia.available_end_date,ia.available_start_time,ia.available_end_time,b.
stu_uid FROM instr_available ia,booking b WHERE ia.instr_location=? AND ia.instr_uid=? AND b.stu_uid=? AND ia.instr_uid !=
(SELECT b.instr_uid FROM booking b, instr_available ia WHERE b.instr_uid=ia.instr_uid AND
b.book_start_date=ia.available_start_date AND b.book_start_time=ia.available_start_time ) AND b.stu_uid !=(SELECT b.stu_uid
FROM booking b, instr_available ia WHERE b.book_start_date=ia.available_start_date AND
b.book_start_time=ia.available_start_time )ORDER BY ia.available_start_date,ia.available_start_time
Aug 30, 2012 7:16:28 AM view.master.masterOptionPanels.MasterBookingOptionsPanel actionPerformed
SEVERE: null
java.sql.SQLException: Scalar subquery is only allowed to return a single row.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
Thank you again for any new information.