public Vector getUsacFiles(String invoice) {
Vector fileList = new Vector();
String query = "";
query =
query
+ " SELECT distinct filename, ' Date:'||to_char(usac_prcs_dat,'MM/DD/YYYY'),trunc(usac_prcs_dat) usac_prcs_dat ";
query = query + " FROM stage_usac_form ";
query = query + " WHERE rtrim(ltrim(sdc_inv_no)) = ?";
query = query + " ORDER BY usac_prcs_dat ";
USFEnv.getLog().writeDebug("getUsacFiles() Query :" + query, this, null);
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = cConn.prepareStatement(query);
pstmt.setString(1, invoice);
rs = pstmt.executeQuery();
while (rs.next()) {
fileList.addElement(rs.getString(1));
fileList.addElement(rs.getString(2));
}
// rs.close();
// stmt.close();
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (Exception e) {
USFEnv.getLog().writeCrit("Unable to close ResultSet and statement", this, e);
}
USFEnv.getLog().writeCrit("getUsacFiles() Failed Query:" + query, this, ex);
}
/* try
{
if( rs != null)
rs.close();
}
catch(Exception e)
{
USFEnv.getLog().writeCrit("Unable to close ResultSet",this,null);
}
try
{
if( pstmt != null)
pstmt.close();
}
catch(Exception e)
{
USFEnv.getLog().writeCrit("Unable to close Prepared Statement",this,null);
}*/
return fileList;
}
private String getMonth(String month) {
String query;
String i_month = "";
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select to_number(to_char(to_date(?,'Month'),'MM')) from dual";
USFEnv.getLog().writeDebug("Dinvjrnl:Get Month - Query" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, month);
rs = pstmt.executeQuery();
if (rs.next()) {
i_month = rs.getString(1);
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Month Conversion Failed ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return i_month;
}
public static void customStartAll(Connection conn) throws SQLException {
String method = "customStartAll";
int location = 1000;
try {
Statement stmt = conn.createStatement();
int id = 0;
int gpfdistPort = 0;
String strSQL = "SELECT id\n";
strSQL += "FROM os.custom_sql";
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
id = rs.getInt(1);
gpfdistPort = GpfdistRunner.customStart(OSProperties.osHome);
strSQL = "INSERT INTO os.ao_custom_sql\n";
strSQL +=
"(id, table_name, columns, column_datatypes, sql_text, source_type, source_server_name, source_instance_name, source_port, source_database_name, source_user_name, source_pass, gpfdist_port)\n";
strSQL +=
"SELECT id, table_name, columns, column_datatypes, sql_text, source_type, source_server_name, source_instance_name, source_port, source_database_name, source_user_name, source_pass, "
+ gpfdistPort
+ "\n";
strSQL += "FROM os.custom_sql\n";
strSQL += "WHERE id = " + id;
stmt.executeUpdate(strSQL);
}
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static String getVariable(Connection conn, String name) throws SQLException {
String method = "getVariable";
int location = 1000;
try {
location = 2000;
String value = "";
location = 2100;
Statement stmt = conn.createStatement();
String strSQL = "SELECT os.fn_get_variable('" + name + "')";
if (debug) Logger.printMsg("Getting Variable: " + strSQL);
location = 2200;
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
value = rs.getString(1);
}
location = 2300;
return value;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static String getVersion(Connection conn) throws SQLException {
String method = "getVersion";
int location = 1000;
try {
location = 2000;
String value = "";
location = 2100;
Statement stmt = conn.createStatement();
String strSQL =
"SELECT CASE "
+ "WHEN POSITION ('HAWQ 2.0.1' in version) > 0 THEN 'HAWQ_2_0_1' "
+ "WHEN POSITION ('HAWQ 2.0.0' in version) > 0 THEN 'HAWQ_2_0_0' "
+ "WHEN POSITION ('HAWQ 1' in version) > 0 THEN 'HAWQ_1' "
+ "WHEN POSITION ('HAWQ' in version) = 0 AND POSITION ('Greenplum Database' IN version) > 0 THEN 'GPDB' "
+ "ELSE 'OTHER' END "
+ "FROM version()";
if (debug) Logger.printMsg("Getting Variable: " + strSQL);
location = 2200;
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
value = rs.getString(1);
}
location = 2300;
return value;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
/**
* This method queries the database to get the details related to the bp_id passed.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getBpdet(String bpid) {
String query;
Vector bpdet = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select rtrim(bs_id_fk||bp_rgn),bp_month from blg_prd where bp_id = ?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, bpid);
rs = pstmt.executeQuery();
while (rs.next()) {
bpdet.addElement(rs.getString(1));
bpdet.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: BP_ID details not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return bpdet;
}
/**
* This method queries the database to get the sequence for the BP_ID.
*
* @exception SQLException, if query fails
* @author
*/
public String getBpid() {
String query;
String bpid = "";
Statement stmt = null;
ResultSet rs = null;
query = "select bp_id_seq.nextval from dual ";
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
bpid = rs.getString(1);
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Sequence Value for BP_ID not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return bpid;
}
/**
* This method queries the database to get the name of the Billing System.
*
* @exception SQLException, if query fails
* @author
*/
public String getBlgsysnm(String blgsys) {
String query;
String blgsysnm = "";
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select bs_nm from blg_sys where bs_id = ?";
USFEnv.getLog().writeDebug("Dinvjrnl: Billing System Name Query :" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, blgsys);
rs = pstmt.executeQuery();
if (rs.next()) {
blgsysnm = rs.getString(1);
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Billing System Name not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepare statement", this, e);
}
}
return blgsysnm;
}
private static void dropCourse(Connection conn, String stu_no) throws SQLException {
String cno = readEntry("Course number: ");
String sno = readEntry("Section number : ");
String chks =
"SELECT g.Section_identifier FROM SECTION s, GRADE_REPORT g where s.Section_identifier=g.Section_identifier AND s.Course_number='"
+ cno
+ "' AND g.Section_identifier="
+ sno
+ " AND s.Semester='Spring' AND s.Year=TO_DATE('2007','YYYY') AND g.Student_number="
+ stu_no;
Statement s3 = conn.createStatement();
ResultSet r3 = s3.executeQuery(chks);
if (r3.next()) {
String del =
"DELETE FROM GRADE_REPORT WHERE Student_number="
+ stu_no
+ " AND Section_identifier="
+ sno;
Statement s4 = conn.createStatement();
try {
s4.executeUpdate(del);
System.out.println("Course dropped");
} catch (SQLException e) {
System.out.println("Cannot drop a course from table");
}
} else {
System.out.println("Enrollment data not found. Check the input and try again.");
}
}
/**
* Deletes students who are older than a certain number of years and not registered to any course.
*
* @param year students older than year are candidates to be deleted
* @throws InvalidDBRequestException
*/
public void deleteOldStudent(int year) throws InvalidDBRequestException {
try {
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
// query all student who have been in the database longer than a number of years and not
// registered to any course
ResultSet rs =
stmt.executeQuery(
"select login, count(course_id) "
+ "from student s left join courseRoster r on login = user_login "
+ "where date_entered < SUBDATE(now(), INTERVAL "
+ new String().valueOf(year).trim()
+ " YEAR) "
+ "group by login, date_entered");
// delete them
while (rs.next()) if (rs.getInt(2) == 0) purgeStudent(rs.getString(1).trim());
rs.close();
stmt.close();
db.close();
} catch (SQLException e) {
System.err.println("Invalid SQL in addCourse: " + e.getMessage());
throw new InvalidDBRequestException("??? ");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Internal Server Error");
}
}
/**
* Check the user's name and password and verify that the user is an instructor. Throws
* InvalidDBRequestException if user is not an instructor, wrong password, or if any error occured
* to the database connection.
*
* @param name user's user name
* @param pass user's password
* @throws InvalidDBRequestException
*/
public void instructorLogin(String name, String pass) throws InvalidDBRequestException {
try {
Connection db;
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
ResultSet rs;
// check if instructor
rs = stmt.executeQuery("select password from instructor where login = '******'");
if (!rs.next()) {
if (debug) System.out.println("User not found in the instructor table");
throw new InvalidDBRequestException("Instructor not registered");
}
// check password
if (!rs.getString(1).equals(pass)) {
if (debug) System.out.println("Invalid password for user: "******"Invalid password for user: "******"Invalid SQL in instructor login: "******"???");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Server Error");
}
}
// execute and get results
private void execute(Connection conn, String text, Writer writer, boolean commaSeparator)
throws SQLException {
BufferedWriter buffer = new BufferedWriter(writer);
Statement stmt = conn.createStatement();
stmt.execute(text);
ResultSet rs = stmt.getResultSet();
ResultSetMetaData metadata = rs.getMetaData();
int nbCols = metadata.getColumnCount();
String[] labels = new String[nbCols];
int[] colwidths = new int[nbCols];
int[] colpos = new int[nbCols];
int linewidth = 1;
// read each occurrence
try {
while (rs.next()) {
for (int i = 0; i < nbCols; i++) {
Object value = rs.getObject(i + 1);
if (value != null) {
buffer.write(value.toString());
if (commaSeparator) buffer.write(",");
}
}
}
buffer.flush();
rs.close();
} catch (IOException ex) {
if (Debug.isDebug()) ex.printStackTrace();
// ok, exit from the loop
} catch (SQLException ex) {
if (Debug.isDebug()) ex.printStackTrace();
}
}
/**
* 使用 ResultSet 中的第 i 行给 Schema 赋值
*
* @param: rs ResultSet
* @param: i int
* @return: boolean
*/
public boolean setSchema(ResultSet rs, int i) {
try {
// rs.absolute(i); // 非滚动游标
if (rs.getString("CalCode") == null) this.calCode = null;
else this.calCode = rs.getString("CalCode").trim();
if (rs.getString("RiskCode") == null) this.riskCode = null;
else this.riskCode = rs.getString("RiskCode").trim();
if (rs.getString("Type") == null) this.type = null;
else this.type = rs.getString("Type").trim();
if (rs.getString("CalSQL") == null) this.calSQL = null;
else this.calSQL = rs.getString("CalSQL").trim();
if (rs.getString("Remark") == null) this.remark = null;
else this.remark = rs.getString("Remark").trim();
} catch (SQLException sqle) {
System.out.println(
"数据库中的LEPCalMode表字段个数和Schema中的字段个数不一致，或者执行db.executeQuery查询时没有使用select * from tables");
// @@错误处理
CError tError = new CError();
tError.moduleName = "LEPCalModeSchema";
tError.functionName = "setSchema";
tError.errorMessage = sqle.toString();
this.mErrors.addOneError(tError);
return false;
}
return true;
}
static void task1() throws FileNotFoundException, IOException, SQLException {
// Read Input
System.out.println("Task1 Started...");
BufferedReader br = new BufferedReader(new FileReader(inputFile));
br.readLine();
String task1Input = br.readLine();
br.close();
double supportPercent =
Double.parseDouble(task1Input.split(":")[1].split("=")[1].split("%")[0].trim());
if (supportPercent >= 0) {
System.out.println("Task1 Support Percent :" + supportPercent);
// Prepare query
String task1Sql =
"select temp.iname,(temp.counttrans/temp2.uniquetrans)*100 as percent"
+ " from (select i.itemname iname,count(t.transid) counttrans from trans t, items i"
+ " where i.itemid = t.itemid group by i.itemname having count(t.transid)>=(select count(distinct transid)*"
+ supportPercent / 100
+ " from trans)"
+ ") temp , (select count(distinct transid) uniquetrans from trans) temp2 order by percent";
PreparedStatement selTask1 = con.prepareStatement(task1Sql);
ResultSet rsTask1 = selTask1.executeQuery();
BufferedWriter bw = new BufferedWriter(new FileWriter("system.out.1"));
while (rsTask1.next()) {
bw.write("{" + rsTask1.getString(1) + "}, s=" + rsTask1.getDouble(2) + "%");
bw.newLine();
}
rsTask1.close();
bw.close();
System.out.println("Task1 Completed...\n");
} else System.out.println("Support percent should be a positive number");
}
private synchronized void init() throws SQLException {
if (isClosed) return;
// do tables exists?
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(TABLE_NAMES_SELECT_STMT);
ArrayList<String> missingTables = new ArrayList(TABLES.keySet());
while (rs.next()) {
String tableName = rs.getString("name");
missingTables.remove(tableName);
}
for (String missingTable : missingTables) {
try {
Statement createStmt = conn.createStatement();
// System.out.println("Adding table "+ missingTable);
createStmt.executeUpdate(TABLES.get(missingTable));
createStmt.close();
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
}
}
}
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();
}
}
/**
* Deletes an instructor from the database. Deletes the instructor's courses by invoking the
* deleteCourse method. Throws InvalidDBRequestException if instructor not in the database or
* other database connection problems.
*
* @see deleteCourse
* @param instructor instructor's user name
* @throws InvalidDBRequestException
*/
public void deleteInstructor(String instructor) throws InvalidDBRequestException {
try {
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
int count;
// delete the instructor's courses
ResultSet rs =
stmt.executeQuery(
"select course_num from course where instructor = '" + instructor + "'");
while (rs.next()) deleteCourse(rs.getString(1).trim(), instructor);
// delete the instructor's record
count = stmt.executeUpdate("delete from instructor where login ='******'");
rs.close();
stmt.close();
db.close();
} catch (SQLException e) {
System.err.println("Invalid SQL in addCourse: " + e.getMessage());
throw new InvalidDBRequestException("??? ");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Internal Server Error");
}
}
private static void addCourse(Connection conn, String stu_no) throws SQLException {
String cno = readEntry("Course number : ");
String sno = readEntry("Section number : ");
String course_query =
"SELECT Section_identifier FROM SECTION WHERE Section_identifier="
+ sno
+ " AND Course_number='"
+ cno
+ "' AND Semester='Spring' AND YEAR=TO_DATE('2007','YYYY')";
// System.out.println(course_query);
Statement s1 = conn.createStatement();
ResultSet r1 = s1.executeQuery(course_query);
if (r1.next()) {
String addc =
"INSERT INTO GRADE_REPORT (Student_number, Section_identifier, Grade) VALUES ("
+ stu_no
+ ", "
+ sno
+ ", 'NULL')";
Statement s2 = conn.createStatement();
System.out.println(addc);
try {
s2.executeUpdate(addc);
System.out.println("Enrollment successfull");
} catch (SQLException e) {
System.out.println("Cannot add course into table");
}
} else {
System.out.println("Course not found");
}
s1.close();
}
/**
* 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;
}
/**
* This method queries the database to get the list of the Billing Systems.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getYears(String year) {
String query;
Vector years = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select yr||'-'||(yr+1),yr from fung_yr where yr > ?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, year);
rs = pstmt.executeQuery();
while (rs.next()) {
years.addElement(rs.getString(1));
years.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Years List not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return years;
}
/**
* 使用 ResultSet 中的第 i 行给 Schema 赋值
*
* @param: rs ResultSet
* @param: i int
* @return: boolean
*/
public boolean setSchema(ResultSet rs, int i) {
try {
// rs.absolute(i); // 非滚动游标
if (rs.getString("RiskCode") == null) this.riskCode = null;
else this.riskCode = rs.getString("RiskCode").trim();
if (rs.getString("RiskVer") == null) this.riskVer = null;
else this.riskVer = rs.getString("RiskVer").trim();
if (rs.getString("DutyCode") == null) this.dutyCode = null;
else this.dutyCode = rs.getString("DutyCode").trim();
if (rs.getString("ChoFlag") == null) this.choFlag = null;
else this.choFlag = rs.getString("ChoFlag").trim();
if (rs.getString("SpecFlag") == null) this.specFlag = null;
else this.specFlag = rs.getString("SpecFlag").trim();
} catch (SQLException sqle) {
System.out.println(
"数据库中的LEPRiskDuty表字段个数和Schema中的字段个数不一致，或者执行db.executeQuery查询时没有使用select * from tables");
// @@错误处理
CError tError = new CError();
tError.moduleName = "LEPRiskDutySchema";
tError.functionName = "setSchema";
tError.errorMessage = sqle.toString();
this.mErrors.addOneError(tError);
return false;
}
return true;
}
public void setQuery(String q) {
cache = new Vector();
try {
ResultSet rs = statement.executeQuery(q);
ResultSetMetaData meta = rs.getMetaData();
colCount = meta.getColumnCount();
headers = new String[colCount];
for (int h = 1; h <= colCount; h++) {
headers[h - 1] = meta.getColumnName(h);
}
while (rs.next()) {
String[] record = new String[colCount];
for (int i = 0; i < colCount; i++) {
record[i] = rs.getString(i + 1);
}
cache.addElement(record);
} // while sonu
fireTableChanged(null);
} // try sonu
catch (Exception e) {
cache = new Vector();
e.printStackTrace();
}
} // setQuery sonu
public String checkValidLogin(String myUserName, String myPW) {
try {
Class.forName(javaSQLDriverPath);
Connection conn =
(Connection) DriverManager.getConnection(ConnectionPath, ConnectionUser, ConnectionPW);
Statement st = conn.createStatement();
String query = "Select * from User";
ResultSet rs = st.executeQuery(query);
while (rs.next()) {
// return rs.getString("Username");
if (myUserName.equals(rs.getString("Username"))) {
if (myPW.equals(rs.getString("Password"))) {
setUserVariables(myUserName);
return "success";
} else {
return "wrongPassword";
}
}
}
rs.close();
st.close();
conn.close();
return "userNotFound";
} catch (Exception e) {
return e.getMessage();
}
}
private boolean login(String[] part) {
ResultSet rs = null;
try {
rs = beanOracle.selection("PASSWORD", "UTILISATEURS", "LOGIN = '******'");
} catch (SQLException e) {
System.err.println(e.getStackTrace());
}
String pwd = null;
try {
if (!rs.next()) {
SendMsg("ERR#Login invalide");
} else pwd = rs.getString("PASSWORD");
} catch (SQLException ex) {
System.err.println(ex.getStackTrace());
}
if (pwd.equals(part[2])) {
SendMsg("ACK");
return true;
} else SendMsg("ERR#Mot de passe incorrecte");
return false;
}
public MetadataBlob[] select(String selectString) {
PreparedStatement stmt = null;
try {
stmt =
conn.prepareStatement(
"select writingKey, mapkey, blobdata from metadatablobs " + selectString + ";");
ResultSet rs = stmt.executeQuery();
List<MetadataBlob> list = new ArrayList<MetadataBlob>();
while (rs.next()) {
MetadataBlob f =
new MetadataBlob(
rs.getString("writingkey"), rs.getString("mapkey"), rs.getString("blobdata"));
list.add(f);
}
return list.toArray(new MetadataBlob[0]);
} catch (SQLException sqe) {
System.err.println("Error selecting: " + selectString);
sqe.printStackTrace();
return null;
} finally {
if (stmt != null)
try {
stmt.close();
} catch (SQLException sqe2) {
sqe2.printStackTrace();
}
}
}
/* to search the DB table for req. video*/
boolean search(String file) {
boolean found = false;
Connection con = null;
String url =
"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}; DBQ=D://db.mdb; DriverID=22;READONLY=true;";
try {
con = DriverManager.getConnection(url, "", "");
try {
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("SELECT video FROM p3");
while (rs.next()) {
String colvalue = rs.getString("video");
if (colvalue.equalsIgnoreCase(file)) found = true;
}
} catch (SQLException s) {
System.out.println("SQL statement is not executed!");
}
} catch (Exception e) {
System.out.println(e);
}
return found;
}
public void deleteOption(String modelName, String optionSetName, String option) {
try {
int autoid = 0;
String sql = "select id from automobile where name ='" + modelName + "';";
ResultSet rs;
rs = statement.executeQuery(sql);
while (rs.next()) {
autoid = rs.getInt("id"); // get auto_id
}
int opsid = 0;
sql =
"select id from optionset where name ='"
+ optionSetName
+ "' and auto_id= "
+ autoid
+ ";";
rs = statement.executeQuery(sql);
while (rs.next()) {
opsid = rs.getInt("id"); // get option_id
}
sql = "delete from options where name= '" + option + "' and option_id= " + opsid;
statement.executeUpdate(sql); // delete it using name and option_id
} catch (SQLException e) {
e.printStackTrace();
}
}
public void updateOptionPrice(
String modelName, String optionSetName, String optionName, float newPrice) {
try {
int autoid = 0;
String sql = "select id from automobile where name ='" + modelName + "';";
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
autoid = rs.getInt("id"); // get auto_id
}
int opsid = 0;
sql =
"select id from optionset where name ='"
+ optionSetName
+ "' and auto_id= "
+ autoid
+ ";";
rs = statement.executeQuery(sql);
while (rs.next()) {
opsid = rs.getInt("id"); // get option_id
}
sql =
"update options set price = "
+ newPrice
+ " where name= '"
+ optionName
+ "' and option_id= "
+ opsid
+ ";";
statement.executeUpdate(sql); // update it with name and option_id
} catch (SQLException e) {
e.printStackTrace();
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
PrintWriter writer = response.getWriter();
HttpSession session = request.getSession();
String username = request.getParameter("username");
String password = request.getParameter("password");
String type = request.getParameter("type");
System.out.println(username + password + type);
session.setAttribute("user", username);
try {
writer.println("<html>");
writer.println("<body bgcolor=green>");
writer.println("<center>");
ps.setString(1, username);
ps.setString(2, password);
ps.setString(3, type);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
writer.println("<h1>LOGIN SUCCESSFUL</h1><br><br>");
writer.println("<a href=account.html>click here to see your account</a>");
} else {
writer.println("<h1>LOGIN FAILED</h1><br><br>");
writer.println("<a href=login.html>click here to login again</a>");
}
writer.println("</center>");
writer.println("</body>");
writer.println("</html>");
} catch (Exception e) {
e.printStackTrace();
}
}
public List<Empleado> getAllEmpleados() {
List ll = new LinkedList();
Statement st;
ResultSet rs;
String url = "jdbc:mysql://localhost:3306/food";
String user = "******";
String pass = "******";
String driver = "com.mysql.jdbc.Driver";
try (Connection connection = DriverManager.getConnection(url, user, pass)) {
Class.forName(driver);
st = connection.createStatement();
String recordQuery = ("Select * from empleados");
rs = st.executeQuery(recordQuery);
while (rs.next()) {
Integer id = rs.getInt("idEmpleados");
String name = rs.getString("Nombre");
String telf = rs.getString("Telefono");
ll.add(new Empleado(id, name, telf));
/// System.out.println(id +","+ name +","+ apellido +","+ cedula +","+ telf +"
// "+direccion+"");
}
} catch (ClassNotFoundException | SQLException ex) {
Logger.getLogger(MenuEmpleados.class.getName()).log(Level.SEVERE, null, ex);
}
return ll;
}
