public void actionPerformed(ActionEvent evt) {
// 删除原来的JTable(JTable使用scrollPane来包装)
if (scrollPane != null) {
jf.remove(scrollPane);
}
try (
// 根据用户输入的SQL执行查询
ResultSet rs = stmt.executeQuery(sqlField.getText())) {
// 取出ResultSet的MetaData
ResultSetMetaData rsmd = rs.getMetaData();
Vector<String> columnNames = new Vector<>();
Vector<Vector<String>> data = new Vector<>();
// 把ResultSet的所有列名添加到Vector里
for (int i = 0; i < rsmd.getColumnCount(); i++) {
columnNames.add(rsmd.getColumnName(i + 1));
}
// 把ResultSet的所有记录添加到Vector里
while (rs.next()) {
Vector<String> v = new Vector<>();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
v.add(rs.getString(i + 1));
}
data.add(v);
}
// 创建新的JTable
JTable table = new JTable(data, columnNames);
scrollPane = new JScrollPane(table);
// 添加新的Table
jf.add(scrollPane);
// 更新主窗口
jf.validate();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Query a quiz list from the database. If there is no student user name specified, the list will
* contain all quizzes that are used in the instructor's courses. Otherwise, the list will contain
* all quizzes that the student has taken and from the instructor's courses which the student is
* registered. Throws InvalidDBRequestException if any error occured to the database connection.
*
* @param instructor instructor's user name
* @param student student's user name. Can be empty to get a list of all quizzes in the
* instructor's courses.
* @return a vector containing the list of quizzes
* @throws InvalidDBRequestException
*/
public Vector getQuizList(String instructor, String student) throws InvalidDBRequestException {
Vector list = new Vector();
try {
Connection db;
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
ResultSet rs;
if (!student.equals("")) {
// get the list that contains all quizzes that the student has taken and from the
// instructor's courses which the student is registered
rs =
stmt.executeQuery(
"select courseTest.test_name, scores.start_time from scores, courseTest, course "
+ "where courseTest.test_name = scores.test_name "
+ "and courseTest.course_id = course.course_id "
+ "and instructor = '"
+ instructor
+ "' and user_login = '******' "
+ "order by scores.start_time");
while (rs.next()) {
list.add(rs.getString(1) + " <" + rs.getString(2) + ">");
}
} else {
// get the list that contains all quizzes that are used in the instructor's courses
rs =
stmt.executeQuery(
"select test_name from courseTest, course "
+ "where courseTest.course_id = course.course_id "
+ "and instructor = '"
+ instructor
+ "' ");
while (rs.next()) {
list.add(rs.getString(1));
}
}
rs.close();
stmt.close();
db.close();
} catch (SQLException e) {
System.err.println("Invalid SQL in getQuizList: " + e.getMessage());
throw new InvalidDBRequestException("???");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Internal Server Error");
}
return list;
}
public Collection<Experiment> findExpts(CellLine cells, ExptCondition cond) throws SQLException {
LinkedList<Integer> dbids = new LinkedList<Integer>();
String tables = "experiment e";
if (genome != null) {
tables += ", probe_platform_to_genome p2g";
}
String query = "select e.id from " + tables;
Vector<String> conds = new Vector<String>();
if (genome != null) {
int gid = genome.getDBID();
conds.add("e.platform=p2g.platform and p2g.genome=" + gid);
}
if (cells != null) {
conds.add("e.cells=" + cells.getDBID());
}
if (cond != null) {
conds.add("e.condition=" + cond.getDBID());
}
for (int i = 0; i < conds.size(); i++) {
if (i == 0) {
query += " where ";
} else {
query += " and ";
}
query += conds.get(i);
}
System.out.println("Final Query: \"" + query + "\"");
java.sql.Connection cxn = loader.getConnection();
Statement s = cxn.createStatement();
ResultSet rs = s.executeQuery(query);
while (rs.next()) {
int dbid = rs.getInt(1);
dbids.addLast(dbid);
}
rs.close();
s.close();
Collection<Experiment> expts = loader.loadExperiments(dbids);
return expts;
}
/**
* Gets a list of courses that belongs to an instructor. Throws InvalidDBRequestException if any
* error occured to the database connection.
*
* @param name the instructor's user name
* @return a vector containing the list of courses
* @throws InvalidDBRequestException
*/
public Vector getCourseList(String name) throws InvalidDBRequestException {
Vector courseList = new Vector();
try {
Connection db;
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
ResultSet rs;
// get the course list
rs =
stmt.executeQuery(
"select course_num, course_name from course where instructor = '"
+ name
+ "' order by course_num");
while (rs.next()) courseList.add(rs.getString(1) + " - " + rs.getString(2));
rs.close();
stmt.close();
db.close();
} catch (SQLException e) {
System.err.println("Invalid SQL in getCourseList: " + e.getMessage());
throw new InvalidDBRequestException("???");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Server Error");
}
return courseList;
}
public Vector buscar(String pidConsulta) throws java.sql.SQLException, Exception {
Medicina medicina = null;
Vector medicinas;
java.sql.ResultSet rs;
String sql;
sql =
"SELECT id, nombre,dosis, numDias, FK_idConsulta, fechaInicio, fechaFinal, precauciones "
+ "FROM TMedicina "
+ "WHERE FK_idConsulta='"
+ pidConsulta
+ "';";
rs = Conector.getConector().ejecutarSQL(sql, true);
medicinas = new Vector<Medicina>();
if (rs.next()) {
do {
medicina =
new Medicina(
rs.getString("nombre"),
rs.getString("dosis"),
rs.getString("numDias"),
rs.getString("FK_idConsulta"),
rs.getString("fechaInicio"),
rs.getString("fechaFinal"),
rs.getString("precauciones"));
medicinas.add(medicina);
} while (rs.next());
} else {
throw new Exception("No hay examenes de ese paciente.");
}
rs.close();
return medicinas;
}
public Vector<Message> getInboxMessages(String username, int type) {
if (type == 4) username = "******";
ResultSet rs =
con.queryDB(
"SELECT * FROM "
+ MESSAGES
+ " WHERE toName = \""
+ username
+ "\" AND messageType = "
+ type
+ " ORDER BY timeStamp DESC");
Vector<Message> inbox = new Vector<Message>();
try {
while (rs.next()) {
inbox.add(
new Message(
rs.getString("fromName"),
username,
rs.getString("message"),
rs.getInt("messageType"),
rs.getTimestamp("timeStamp")));
}
} catch (SQLException e) {
throw new RuntimeException("SQLException in MessageDatabase::getInboxMessages");
}
return inbox;
}
public static Vector<Integer> strToVector(String s) {
String[] elements = s.substring(1, s.length() - 1).split(",");
Vector<Integer> vc = new Vector<Integer>();
for (String str : elements) vc.add(Integer.parseInt(str.trim()));
return vc;
}
public static Vector<Integer> getValuesInVector(String query) throws SQLException {
Vector<Integer> vc = new Vector<Integer>();
ResultSet rs = Utilfunctions.executeQuery(query);
while (rs.next()) vc.add(rs.getInt(1));
return vc;
}
/** getObject. */
public void test() throws Exception {
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet result = statement.executeQuery();
int size = result.getMetaData().getColumnCount();
Vector rows = new Vector();
while (result.next()) {
Vector row = new Vector(size);
for (int column = 1; column <= size; column++) {
Object value = result.getObject(column);
value = ConversionManager.getDefaultManager().convertObject(value, ClassConstants.SQLDATE);
row.add(value);
}
rows.add(row);
}
result.close();
statement.close();
}
private ConnectionPool(int initCon, int maxCon) throws Exception {
this.initCon = initCon;
this.maxCon = maxCon;
pool = new Vector();
for (int i = 0; i < initCon; i++) {
pool.add(createConnection());
}
}
public void insert(Object[] obj) {
try {
DefaultTableModel dtt = (DefaultTableModel) getModel();
dtt.addRow(obj);
dataBaru.add(getModel().getRowCount() - 1);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* INTERNAL: Execute the query building the objects directly from the database result-set.
*
* @exception DatabaseException - an error has occurred on the database
* @return object - the first object found or null if none.
*/
protected Object executeObjectLevelReadQueryFromResultSet() throws DatabaseException {
UnitOfWorkImpl unitOfWork = (UnitOfWorkImpl) getSession();
DatabaseAccessor accessor = (DatabaseAccessor) unitOfWork.getAccessor();
DatabasePlatform platform = accessor.getPlatform();
DatabaseCall call = (DatabaseCall) getCall().clone();
call.setQuery(this);
call.translate(this.translationRow, null, unitOfWork);
Statement statement = null;
ResultSet resultSet = null;
boolean exceptionOccured = false;
try {
accessor.incrementCallCount(unitOfWork);
statement = call.prepareStatement(accessor, this.translationRow, unitOfWork);
resultSet = accessor.executeSelect(call, statement, unitOfWork);
ResultSetMetaData metaData = resultSet.getMetaData();
Vector results = new Vector();
ObjectBuilder builder = this.descriptor.getObjectBuilder();
while (resultSet.next()) {
results.add(
builder.buildWorkingCopyCloneFromResultSet(
this,
this.joinedAttributeManager,
resultSet,
unitOfWork,
accessor,
metaData,
platform));
}
return results;
} catch (SQLException exception) {
exceptionOccured = true;
DatabaseException commException =
accessor.processExceptionForCommError(session, exception, call);
if (commException != null) throw commException;
throw DatabaseException.sqlException(exception, call, accessor, unitOfWork, false);
} finally {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
accessor.releaseStatement(statement, call.getSQLString(), call, unitOfWork);
}
} catch (SQLException exception) {
if (!exceptionOccured) {
// in the case of an external connection pool the connection may be null after the
// statement release
// if it is null we will be unable to check the connection for a comm error and
// therefore must return as if it was not a comm error.
DatabaseException commException =
accessor.processExceptionForCommError(session, exception, call);
if (commException != null) throw commException;
throw DatabaseException.sqlException(exception, call, accessor, session, false);
}
}
}
}
/**
* Returns the messages which ID starts with the given ID
*
* @param ID message ID
* @return text of the message
*/
public Vector getMessages(String ID) {
Vector res = new Vector();
Enumeration enm = dict.keys();
while (enm.hasMoreElements()) {
String key = "" + enm.nextElement();
if (key.startsWith(ID)) {
res.add(dict.get(key));
}
}
return res;
}
// @karen not completed
public Vector getRecord_Sorted(String sSQL, int type) {
Vector v = new Vector();
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
// int SortType = getSortType();
sSQL = sSQL + " ORDER BY ";
if (type == 1) {
if (SortType == 1) sSQL = sSQL + "CompanyName";
else if (SortType == 2) sSQL = sSQL + "CompanyDesc";
if (Toggle[SortType - 1] == 1) sSQL = sSQL + " DESC";
} else {
if (SortType_org == 1) sSQL = sSQL + "OrganizationName";
else if (SortType_org == 2) sSQL = sSQL + "OrganizationCode";
else if (SortType_org == 3) sSQL = sSQL + "NameSequence";
if (Toggle_org[SortType_org - 1] == 1) sSQL = sSQL + " DESC";
}
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(sSQL);
while (rs.next()) {
votblConsultingCompany vo = new votblConsultingCompany();
vo.setCompanyDesc(rs.getString("CompanyDesc"));
vo.setCompanyName(rs.getString("CompanyName"));
vo.setCompanyID(rs.getInt("CompanyID"));
v.add(vo);
}
} catch (SQLException SE) {
System.err.println("Organization.java - getRecord_Sorted - " + SE.getMessage());
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return v;
}
/**
* Enumerate the methods of the Clob interface and get the list of methods present in the
* interface
*
* @param LOB an instance of the Clob interface implementation
*/
void buildMethodList(Object LOB) throws IllegalAccessException, InvocationTargetException {
// If the given method throws the correct exception
// set this to true and add it to the
boolean valid = true;
// create a list of the methods that fail the test
Vector<Method> methodList = new Vector<Method>();
// The class whose methods are to be verified
Class clazz = Clob.class;
// The list of the methods in the class that need to be invoked
// and verified
Method[] methods = clazz.getMethods();
// Check each of the methods to ensure that
// they throw the required exception
for (int i = 0; i < methods.length; i++) {
if (!checkIfExempted(methods[i])) {
valid = checkIfMethodThrowsSQLException(LOB, methods[i]);
// add the method to the list if the method does
// not throw the required exception
if (valid == false) methodList.add(methods[i]);
// reset valid
valid = true;
}
}
if (!methodList.isEmpty()) {
int c = 0;
String failureMessage = "The Following methods don't throw " + "required exception - ";
for (Method m : methodList) {
c = c + 1;
if (c == methodList.size() && c != 1) failureMessage += " & ";
else if (c != 1) failureMessage += " , ";
failureMessage += m.getName();
}
fail(failureMessage);
}
}
public void run() {
try {
PreparedStatement pstmt =
con.prepareStatement(
"insert into #testConcurrentBatch (v1, v2, v3, v4, v5, v6) values (?, ?, ?, ?, ?, ?)");
for (int i = 0; i < 64; ++i) {
// Make sure we end up with 64 different prepares, use the binary representation of i to
// set each
// of the 6 parameters to either an int or a string.
int mask = i;
for (int j = 1; j <= 6; ++j, mask >>= 1) {
if ((mask & 1) != 0) {
pstmt.setInt(j, i);
} else {
pstmt.setString(j, String.valueOf(i));
}
}
pstmt.addBatch();
}
int x[];
try {
x = pstmt.executeBatch();
} catch (BatchUpdateException e) {
e.printStackTrace();
x = e.getUpdateCounts();
}
if (x.length != 64) {
throw new SQLException("Expected 64 update counts, got " + x.length);
}
for (int i = 0; i < x.length; ++i) {
if (x[i] != 1) {
throw new SQLException("Error at position " + i + ", got " + x[i] + " instead of 1");
}
}
// Rollback the transaction, exposing any race conditions.
con.rollback();
pstmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
exceptions.add(ex);
}
}
public Vector getMappingCodingSchemesEntityParticipatesIn(String code, String namespace) {
Vector v = new Vector();
try {
MappingExtension mappingExtension =
(MappingExtension) lbSvc.getGenericExtension("MappingExtension");
AbsoluteCodingSchemeVersionReferenceList mappingSchemes =
mappingExtension.getMappingCodingSchemesEntityParticipatesIn(code, namespace);
// output is all of the mapping ontologies that this code participates in.
for (AbsoluteCodingSchemeVersionReference ref :
mappingSchemes.getAbsoluteCodingSchemeVersionReference()) {
v.add(ref.getCodingSchemeURN() + "|" + ref.getCodingSchemeVersion());
}
} catch (Exception ex) {
ex.printStackTrace();
}
return v;
}
public static int[] getMSPIDs(MSPLocator loc, Connection c) throws SQLException {
Statement s = c.createStatement();
String name = loc.getNameVersion().name;
String version = loc.getNameVersion().version;
ResultSet rs =
s.executeQuery(
"select id from rosettaanalysis where name='"
+ name
+ "' and "
+ "version = '"
+ version
+ "'");
Vector<Integer> values = new Vector<Integer>();
while (rs.next()) {
values.add(rs.getInt(1));
}
s.close();
int[] array = new int[values.size()];
for (int i = 0; i < values.size(); i++) {
array[i] = values.get(i);
}
Arrays.sort(array);
return array;
}
/*
* Returns a Vector representing all messages in the user's outbox. The most recent
* messages are at the beginning, and the oldest ones are at the end.
*/
public Vector<Message> getOutboxMessages(String username) {
ResultSet rs =
con.queryDB(
"SELECT * FROM "
+ MESSAGES
+ " WHERE fromName = \""
+ username
+ "\" ORDER BY timeStamp DESC");
Vector<Message> outbox = new Vector<Message>();
try {
while (rs.next()) {
outbox.add(
new Message(
username,
rs.getString("toName"),
rs.getString("message"),
rs.getInt("messageType"),
rs.getTimestamp("timeStamp")));
}
} catch (SQLException e) {
throw new RuntimeException("SQLException in MessageDatabase::getOutboxMessages");
}
return outbox;
}
// </editor-fold>
// <editor-fold defaultstate="collapsed" desc="Select Table">
public void selectTabel(DefaultTableModel tableM) {
setModelTable(tableM);
try {
dataAsli = new Vector();
rowSet.setCommand(sql);
rowSet.execute();
DefaultTableModel dtm = (DefaultTableModel) getModel();
int i = 0;
// tipeKolom =
// untukASIU#noKolomJtable#tipeKolomJTable#namaKolomDB#tipeKolom#atributKolom#nilaiKolom
// tipeKolom = boolean, Date, String, combo, Int, Numeric
while (rowSet.next()) {
Object[] dede = new Object[selectKolom.size()];
for (int ii = 0; ii < selectKolom.size(); ii++) {
String temp = selectKolom.get(ii + "");
String data[] = temp.split("#");
int no = 0;
try {
no = Integer.parseInt(data[1].trim());
} catch (Exception e) {
no = 0;
}
if (data[0].trim().toLowerCase().contains("s")) {
if (data[2].trim().toLowerCase().equals("boolean")) {
boolean value = false;
if (data[4].trim().toLowerCase().equals("int")) {
if (rowSet.getInt(data[3].trim()) == Integer.parseInt(data[5].trim())) {
value = true;
} else {
value = false;
}
} else {
if (rowSet.getString(data[3].trim()).equals(data[5].trim())) {
value = true;
} else {
value = false;
}
}
dede[no] = value;
} else if (data[2].trim().toLowerCase().equals("date")) {
java.util.Date value = new java.util.Date();
if (data[4].equals("dd-MM-yyyy")) {
value = Function.dateStringToDate(rowSet.getString(data[3].trim()));
} else if (data[4].equals("MM-yyyy")) {
value = Function.stringToMonth(rowSet.getString(data[3].trim()));
} else if (data[4].equals("yyyy")) {
value = Function.yearToDate(rowSet.getString(data[3].trim()));
}
dede[no] = value;
} else if (data[2].trim().toLowerCase().equals("string")
|| data[2].trim().toLowerCase().equals("combo")
|| data[2].trim().toLowerCase().equals("numeric")) {
String value = "";
if (data[4].trim().toLowerCase().equals("int")) {
value = rowSet.getLong(data[3].trim()) + "";
} else {
value = rowSet.getString(data[3].trim());
}
dede[no] = value;
}
}
}
dtm.addRow(dede);
dataAsli.add(i);
i++;
}
} catch (Exception e) {
e.printStackTrace();
}
}
public storiconoleggio() {
storico = new JFrame("Storico cliente:");
storico.setDefaultCloseOperation(DISPOSE_ON_CLOSE);
storico.setVisible(true);
operazioni = new JPanel();
operazioni.setLayout(new BoxLayout(operazioni, BoxLayout.Y_AXIS));
sotto = new JPanel();
gruppo = Box.createVerticalBox();
indietro = new JButton("Indietro");
ascoltatore = new listenernolcli();
Object righe[][] = {
{"# Noleggio", "ID Bagnino", "# Ombrellone", "Data Noleggio", "Num. Lettini", "ID Cliente"}
};
Object colonne[] = {"", "", "", "", "", ""};
JTable tabella = new JTable(righe, colonne);
try {
String url = "jdbc:mysql://127.0.0.1:3306/lido";
String userid = "root";
String paswordd = "root";
con = DriverManager.getConnection(url, userid, paswordd);
requete = con.createStatement();
rs =
requete.executeQuery(
"SELECT * FROM lido.bagnino_noleggia_ombrellone WHERE cliente_idcliente ="
+ login.getNomeUtente());
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
Vector columns = new Vector(columnCount);
for (int i = 1; i <= columnCount; i++) columns.add(md.getColumnName(i));
Vector data = new Vector();
Vector row;
while (rs.next()) {
row = new Vector(columnCount);
for (int i = 1; i <= columnCount; i++) {
row.add(rs.getString(i));
}
data.add(row);
}
table = new JTable(data, columns);
table.setPreferredScrollableViewportSize(new Dimension(500, 70));
table.setFillsViewportHeight(true);
table.setVisible(true);
table.validate();
} catch (SQLException sqle) {
System.out.println(sqle);
sqle.printStackTrace();
}
indietro.addActionListener(ascoltatore);
operazioni.add(tabella);
operazioni.add(table);
sotto.add(indietro);
gruppo.add(operazioni);
gruppo.add(sotto);
storico.add(gruppo);
storico.pack();
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
ResultSet rs = null;
try {
// SET UP Context environment, to look for Data Pooling Resource
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/TestDB");
Connection dbcon = ds.getConnection();
// ########### SEARCH INPUT PARAMETERS, EXECUTE search
// #####################################################
Vector<String> params = new Vector<String>();
params.add(request.getParameter("fname"));
params.add(request.getParameter("lname"));
params.add(request.getParameter("title"));
params.add(request.getParameter("year"));
params.add(request.getParameter("director"));
List<Movie> movies = new ArrayList<Movie>();
movies = SQLServices.getMovies(params.toArray(new String[params.size()]), dbcon);
// ########## SET DEFAULT SESSION() PARAMETERS ####################################
request.getSession().removeAttribute("movies");
request.getSession().removeAttribute("linkedListMovies");
request.getSession().removeAttribute("hasPaged");
request.getSession().setAttribute("hasPaged", "no");
request.getSession().setAttribute("movies", movies);
request.getSession().setAttribute("currentIndex", "0");
request.getSession().setAttribute("defaultN", "5");
// ########## IF MOVIES FROM SEARCH NON-EMPTY ###########################################
List<String> fields = Movie.fieldNames();
int count = 1;
if (!movies.isEmpty()) {
request.setAttribute("movies", movies);
for (String field : fields) {
request.setAttribute("f" + count++, field);
}
request.getRequestDispatcher("../movieList.jsp").forward(request, response);
} else {
out.println("<html><head><title>error</title></head>");
out.println("<body><h1>could not find any movies, try your search again.</h1>");
out.println("<p> we are terribly sorry, please go back. </p>");
out.println("<table border>");
out.println("</table>");
}
dbcon.close();
} catch (SQLException ex) {
while (ex != null) {
System.out.println("SQL Exception: " + ex.getMessage());
ex = ex.getNextException();
}
} catch (java.lang.Exception ex) {
out.println(
"<html>"
+ "<head><title>"
+ "moviedb: error"
+ "</title></head>\n<body>"
+ "<p>SQL error in doGet: "
+ ex.getMessage()
+ "</p></body></html>");
return;
}
out.close();
}
public Vector getAllOrganizations(int iFKCompanyID) {
Vector v = new Vector();
Connection con = null;
Statement st = null;
ResultSet rs = null;
/*
* Change(s) : Changed the SQL statement to get the company name as well
* Reason(s) : Able to set the selected company name when company selection is changed in OrganizationList.jsp
* Updated By: Gwen Oh
* Updated On: 26 Sep 2011
*/
// String sSQL="SELECT * FROM tblOrganization WHERE FKCompanyID= "+ iFKCompanyID;
String sSQL =
"SELECT tblOrganization.*, tblConsultingCompany.CompanyName FROM "
+ "tblOrganization INNER JOIN tblConsultingCompany ON tblOrganization.FKCompanyID = tblConsultingCompany.CompanyID "
+ "WHERE tblOrganization.FKCompanyID="
+ iFKCompanyID;
try {
/*
* Re-edited by Eric Lu 15-May-08
*
* Added sort and toggle functionality for getting organizations
*/
sSQL = sSQL + " ORDER BY ";
if (SortType_org == 1) sSQL = sSQL + "OrganizationName";
else if (SortType_org == 2) sSQL = sSQL + "OrganizationCode";
else if (SortType_org == 3) sSQL = sSQL + "NameSequence";
if (Toggle_org[SortType_org - 1] == 1) sSQL = sSQL + " DESC";
con = ConnectionBean.getConnection();
st = con.createStatement();
rs = st.executeQuery(sSQL);
while (rs.next()) {
votblOrganization vo = new votblOrganization();
vo.setEmailNom(rs.getString("EmailNom"));
vo.setEmailNomRemind(rs.getString("EmailNomRemind"));
vo.setEmailPart(rs.getString("EmailPart"));
vo.setEmailPartRemind(rs.getString("EmailPartRemind"));
vo.setExtraModule(rs.getInt("ExtraModule"));
vo.setFKCompanyID(rs.getInt("FKCompanyID"));
vo.setNameSequence(rs.getInt("NameSequence"));
vo.setOrganizationCode(rs.getString("OrganizationCode"));
vo.setOrganizationLogo(rs.getString("OrganizationLogo"));
vo.setOrganizationName(rs.getString("OrganizationName"));
vo.setPKOrganization(rs.getInt("PKOrganization"));
// Gwen Oh - 26/09/2011: Set the company name
vo.setCompanyName(rs.getString("CompanyName"));
v.add(vo);
}
} catch (SQLException SE) {
System.err.println("Organization.java - getAllOrganizations - " + SE.getMessage());
} finally {
ConnectionBean.closeRset(rs); // Close ResultSet
ConnectionBean.closeStmt(st); // Close statement
ConnectionBean.close(con); // Close connection
}
return v;
}
/**
* Query students grades for a course on a particular quiz Throws InvalidDBRequestException if
* quiz is not used in the course, or if any error occured to the database connection.
*
* @param courseID course unique id
* @param quizname quiz unique name
* @return a vector that contains the students information and the grades
* @throws InvalidDBRequestException
*/
public Vector getQuizGrades(String courseID, String quizName) throws InvalidDBRequestException {
Vector grades = new Vector();
try {
Connection db;
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
ResultSet rs;
rs =
stmt.executeQuery(
"select valid from courseTest where course_id = '"
+ courseID
+ "' and test_name = '"
+ quizName
+ "'");
if (rs.next()) {
rs =
stmt.executeQuery(
"select login, last_name, first_name, start_time, num_questions, num_correct from student, scores, courseRoster "
+ "where student.login = scores.user_login "
+ "and scores.user_login = courseRoster.user_login "
+ "and scores.test_name = '"
+ quizName
+ "' "
+ "and course_id = '"
+ courseID
+ "' "
+ "order by last_name, first_name, login, scores.start_time");
// use @ as delimiter since the date_time contains space
while (rs.next())
grades.add(
rs.getString(1)
+ "@"
+ rs.getString(2)
+ "@"
+ rs.getString(3)
+ "@"
+ rs.getString(4)
+ "@"
+ rs.getString(5)
+ "@"
+ rs.getString(6));
} else throw new InvalidDBRequestException("Quiz is not used for the course");
rs.close();
stmt.close();
db.close();
} catch (SQLException e) {
System.err.println("Invalid SQL in instructor login: "******"???");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Server Error");
}
return grades;
}
/**
* Query a student list from the database. If quiz name is not specified, the list will contain
* all students who are registered in the instructor's courses. Otherwise, it will contain all
* students who has taken the quiz and are registered to the instructor's course(s) that use the
* quiz Throws InvalidDBRequestException if any error occured to the database connection.
*
* @param name instructor's user name
* @param quiz quiz name. Can be empty String to get the list of all students who are registered
* in the instructor's courses.
* @return a vector that contains the list of students
* @throws InvalidDBRequestException
*/
public Vector getStudentList(String name, String quiz) throws InvalidDBRequestException {
Vector list = new Vector();
try {
Connection db;
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
ResultSet rs;
if (!quiz.equals("")) {
// get the list that contains all students who has taken the quiz and are registered to the
// instructor's course(s) that use the quiz
rs =
stmt.executeQuery(
"select login, last_name, first_name, scores.start_time from student, scores, courseRoster, course "
+ "where student.login = scores.user_login "
+ "and scores.user_login = courseRoster.user_login "
+ "and courseRoster.course_id = course.course_id "
+ "and scores.test_name = '"
+ quiz
+ "' "
+ "and instructor = '"
+ name
+ "' "
+ "order by last_name, first_name, login, scores.start_time");
while (rs.next()) {
list.add(
rs.getString(2)
+ ", "
+ rs.getString(3)
+ " ("
+ rs.getString(1)
+ ") <"
+ rs.getString(4)
+ ">");
}
} else {
// get the list that contains all students who are registered in the instructor's courses
rs =
stmt.executeQuery(
"select distinct login, last_name, first_name from student, courseRoster, course "
+ "where student.login = courseRoster.user_login "
+ "and courseRoster.course_id = course.course_id "
+ "and instructor = '"
+ name
+ "' "
+ "order by last_name, first_name, login");
while (rs.next()) {
list.add(rs.getString(2) + ", " + rs.getString(3) + " (" + rs.getString(1) + ")");
}
}
rs.close();
stmt.close();
db.close();
} catch (SQLException e) {
System.err.println("Invalid SQL in getStudentList: " + e.getMessage());
throw new InvalidDBRequestException("???");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Internal Server Error");
}
return list;
}
public synchronized void releaseConnection(Connection conn) {
pool.add(conn);
users--;
notifyAll();
}
