static void attributeMean() {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
Statement meanQuery = con.createStatement();
ResultSet mean = meanQuery.executeQuery("select AVG(sal) from employ where sal IS NOT NULL");
if (mean.next()) {
fill = con.createStatement();
fill.executeUpdate("UPDATE employ set sal = COALESCE(sal," + mean.getInt(1) + ")");
}
/*fill = con.createStatement();
fill.executeUpdate("UPDATE employ set sal = COALESCE(sal,AVG(sal))");*/
st = con.createStatement();
rs = st.executeQuery("select * from employ");
System.out.println("Sno\tName\tSalary\tGPF\tGrade");
while (rs.next()) {
System.out.println(
">>"
+ rs.getString(1)
+ "\t"
+ rs.getString(2)
+ "\t"
+ rs.getString(3)
+ "\t"
+ rs.getString(4)
+ "\t"
+ rs.getString(5));
}
} catch (Exception e) {
System.out.println("Error: " + e);
}
}
static void probableValue() {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
Statement freqQuery = con.createStatement();
ResultSet freq =
freqQuery.executeQuery(
"select sal,count(*) as total from employ group by sal order by total desc");
if (freq.next()) {
fill = con.createStatement();
fill.executeUpdate("UPDATE employ set sal = COALESCE(sal," + freq.getInt(1) + ")");
}
st = con.createStatement();
rs = st.executeQuery("select * from employ");
System.out.println(">>Sno\tName\tSalary\tGPF\tGrade");
while (rs.next()) {
System.out.println(
">>"
+ rs.getString(1)
+ "\t"
+ rs.getString(2)
+ "\t"
+ rs.getString(3)
+ "\t"
+ rs.getString(4)
+ "\t"
+ rs.getString(5));
}
} catch (Exception e) {
System.out.println("Error: " + e);
}
}
static void globalConstant() {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
fill = con.createStatement();
fill.executeUpdate("UPDATE employ set sal = " + GlobalConstant + " where sal IS NULL");
// fill.executeUpdate("UPDATE employ set sal = COALESCE(sal," + GlobalConstant + ")");
// System.out.println("Entered..!");
st = con.createStatement();
rs = st.executeQuery("select * from employ");
System.out.println("Sno\tName\tSalary\tGPF\tGrade");
while (rs.next()) {
System.out.println(
">>"
+ rs.getString(1)
+ "\t"
+ rs.getString(2)
+ "\t"
+ rs.getString(3)
+ "\t"
+ rs.getString(4)
+ "\t"
+ rs.getString(5));
}
} catch (Exception e) {
System.out.println("Error: " + e);
}
}
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();
}
}
}
public List<Cocinero> getAllCocinero() {
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 cocinero");
rs = st.executeQuery(recordQuery);
while (rs.next()) {
Integer idcoc = rs.getInt("idCocinero");
String years = rs.getString("AñosServicio");
String idemp = rs.getString("Empleados_idEmpleados");
Integer idepp = Integer.parseInt(idemp);
ll.add(new Cocinero(idcoc, years, idepp));
}
} catch (ClassNotFoundException | SQLException ex) {
Logger.getLogger(MenuEmpleados.class.getName()).log(Level.SEVERE, null, ex);
}
return ll;
}
/**
* Remove a student from a particular course. Also Deletes all the quiz vizualisation files in the
* student's directory which relates to the course. Caution: vizualisation file will be deleted
* eventhough it also relates to another course if the student is also registered to that course.
* (FIX ME!) Throws InvalidDBRequestException if the student is not registered in the course,
* error occured during deletion, or other exception occured.
*
* @param username student's user name
* @param courseID course id (course number + instructor name)
* @throws InvalidDBRequestException
*/
public void deleteStudent(String username, String courseID) throws InvalidDBRequestException {
try {
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
ResultSet rs;
int count = 0;
// check if student registered to the course
rs =
stmt.executeQuery(
"select * from courseRoster where course_id = '"
+ courseID
+ "' and user_login = '******'");
if (!rs.next())
throw new InvalidDBRequestException("Student is not registered to the course");
// remove student from the course
count =
stmt.executeUpdate(
"delete from courseRoster where course_id = '"
+ courseID
+ "' and user_login = '******'");
if (count != 1) throw new InvalidDBRequestException("Error occured during deletion!");
// delete the quiz visualization files
rs =
stmt.executeQuery(
"select distinct unique_id, s.test_name from scores s, courseTest t "
+ "where s.test_name = t.test_name "
+ "and course_id = '"
+ courseID
+ "' "
+ "and user_login = '******'");
while (rs.next()) {
deleteVisualization(rs.getString(1), username, rs.getString(2));
count =
stmt.executeUpdate("delete from scores where unique_id = " + rs.getString(1).trim());
}
rs.close();
stmt.close();
db.close();
} catch (SQLException e) {
System.err.println("Invalid SQL in addstudent: " + e.getMessage());
throw new InvalidDBRequestException("???");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Internal Server Error");
}
}
/**
* 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;
}
/**
* 使用 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 specificAttributeMean() {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
fill = con.createStatement();
fill.executeUpdate(
"UPDATE employ a set sal = (select AVG(sal) from employ where sal IS NOT NULL and grade = (select grade from employ b where sal IS NULL and a.grade=b.grade and ROWNUM <= 1)) where sal IS NULL");
st = con.createStatement();
rs = st.executeQuery("select * from employ");
System.out.println(">>Sno\tName\tSalary\tGPF\tGrade");
while (rs.next()) {
System.out.println(
">>"
+ rs.getString(1)
+ "\t"
+ rs.getString(2)
+ "\t"
+ rs.getString(3)
+ "\t"
+ rs.getString(4)
+ "\t"
+ rs.getString(5));
}
} catch (Exception e) {
System.out.println("Error: " + e);
}
}
public void setProperties(ResultSet SYSTEM_CONFIG) throws SQLException {
try {
mailHost = SYSTEM_CONFIG.getString("MailServer");
Source = SYSTEM_CONFIG.getString("SenderMailNameProgram");
WebMaster = SYSTEM_CONFIG.getString("MailWebMaster");
/* Legge la lista amministratori dal file di proprieta */
StringTokenizer myStrTok = getTokenizer(SYSTEM_CONFIG.getString("CarbonCopyMailRecipients"));
StringTokenizer myStrTokName =
getTokenizer(SYSTEM_CONFIG.getString("NameServiceAdministratorsList"));
/* crea un array per la lista degli amministratori */
MailServiceAdministratorsList = new String[myStrTok.countTokens()];
int i = 0;
while (myStrTok.hasMoreTokens()) {
MailServiceAdministratorsList[i++] = myStrTok.nextToken();
}
} catch (SQLException sqle) {
throw sqle;
}
}
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();
}
}
public void actionPerformed(ActionEvent ae) {
try {
if (ae.getSource() == b7) {
rs = st.executeQuery("select count(*) from employeemaster");
if (rs.next()) {
t6.setText(rs.getString(1));
}
rs = st.executeQuery("select * from Head");
if (rs.next()) {
t1.setText(rs.getString(1));
t2.setText(rs.getString(2));
t7.setText(rs.getString(3));
t4.setText(rs.getString(4));
t5.setText(rs.getString(5));
t3.setText(rs.getString(6));
}
} else if (ae.getSource() == b6) {
dispose();
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, "Error is" + e);
}
}
public static void duration() {
connectDb();
String sql = "select min(time), max(time), count(*) from " + tablename + ";";
try {
ResultSet rs = query.executeQuery(sql);
rs.next();
int numCols = rs.getMetaData().getColumnCount();
String minTime = rs.getString(1);
String maxTime = rs.getString(2);
String numPackets = rs.getString(3);
System.out.println(
"Experiment "
+ tablename
+ "\n\tfrom: "
+ minTime
+ "\n\tto: "
+ maxTime
+ "\n\tpackets: "
+ numPackets);
} catch (SQLException e) {
System.out.println("SQL Exception: " + e);
System.exit(1);
}
}
public void doSQL() throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists people;");
stat.executeUpdate("create table people (name, occupation);");
PreparedStatement prep = conn.prepareStatement("insert into people values (?, ?);");
prep.setString(1, "Gandhi");
prep.setString(2, "politics");
prep.addBatch();
prep.setString(1, "Turing");
prep.setString(2, "computers");
prep.addBatch();
prep.setString(1, "Wittgenstein");
prep.setString(2, "smartypants");
prep.addBatch();
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);
ResultSet rs = stat.executeQuery("select * from people;");
while (rs.next()) {
System.out.println("name = " + rs.getString("name"));
System.out.println("job = " + rs.getString("occupation"));
}
rs.close();
conn.close();
}
/**
* Returns the normalized Authority value for an author based on the name passed in. If no
* authority exists, null will be returned.
*
* @param author the author to get the authority information for
* @return the normalized authority information or null if no authority exists.
*/
public static String getNormalizedAuthorAuthorityFromDatabase(String author) {
if (!connectToDatabase()) {
return null;
} else {
try {
getPreferredAuthorByOriginalNameStmt.setString(1, author);
// First check without normalization
ResultSet originalNameResults = getPreferredAuthorByOriginalNameStmt.executeQuery();
if (originalNameResults.next()) {
String authority = originalNameResults.getString("normalizedName");
// Found a match
originalNameResults.close();
return authority;
} else {
// No match, check alternate names for the author
String normalizedAuthor = AuthorNormalizer.getNormalizedName(author);
getPreferredAuthorByAlternateNameStmt.setString(1, normalizedAuthor);
ResultSet alternateNameResults = getPreferredAuthorByAlternateNameStmt.executeQuery();
if (alternateNameResults.next()) {
String authority = alternateNameResults.getString("normalizedName");
alternateNameResults.close();
return authority;
}
}
} catch (Exception e) {
logger.error("Error loading authority information from database", e);
}
}
return null;
}
/**
* 使用 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;
}
/**
* 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;
}
/**
* Obtains information on a quiz that a student has taken, which includes the student's answer to
* the quiz, a unique id of the instance, number of questions in the quiz, number of questions
* answered correctly, and vizualisation type of the quiz. Throws InvalidDBRequestException if
* cannot find the record of the stuent taking the quiz, quiz is not registered in the database,
* or if any error occured to the database connection.
*
* @param student student's user name
* @param quiz quiz name
* @param startTime the time the student started the quiz
* @return a string tokenizer containing: answer,uniqueID, numQuestions, numCorrect, and
* visualType. It uses "@" as the delimiter.
* @throws InvalidDBRequestException
*/
public StringTokenizer getAnswerAndVisualType(String student, String quiz, String startTime)
throws InvalidDBRequestException {
String answer;
String uniqueID;
String numQuestions;
String numCorrect;
String visualType;
try {
Connection db;
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
ResultSet rs;
// get student's answer, unique id, number of questions, and number of questions answered
// correctly
rs =
stmt.executeQuery(
"select transcript, unique_id, num_questions, num_correct from scores "
+ "where test_name = '"
+ quiz
+ "' and user_login = '******' and start_time = '"
+ startTime
+ "'");
if (!rs.next()) throw new InvalidDBRequestException("Student has not taken the quiz");
else {
answer = rs.getString(1).trim();
uniqueID = rs.getString(2).trim();
numQuestions = rs.getString(3).trim();
numCorrect = rs.getString(4).trim();
}
// get quiz vizualisation type
rs = stmt.executeQuery("select visual_type from test where name = '" + quiz + "' ");
if (!rs.next())
throw new InvalidDBRequestException(
"Quiz was not found! Can't retrieve visualization type.");
else {
visualType = rs.getString(1);
}
rs.close();
stmt.close();
db.close();
} catch (SQLException e) {
System.err.println("Invalid SQL in getAnswerAndVisualType: " + e.getMessage());
throw new InvalidDBRequestException("???");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Internal Server Error");
}
return new StringTokenizer(
answer + "@" + uniqueID + "@" + numQuestions + "@" + numCorrect + "@" + visualType, "@");
}
/**
* This method queries the database to get the Journal Dates for the passed in Funding Year
*
* @exception SQLException, if query fails
* @author
*/
public Vector getJrnldts(String year) {
String query;
Vector jrnllst = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query =
"select bp_id,decode(bs_nm,'CRIS','1','IABS','2','BART','3','SOFI','4','INFRANET','5','6') ord, ";
query = query + "rtrim(bs_nm||' '||bp_rgn) nm, bp_month,decode(bp_month,1,'January',";
query =
query
+ "2,'February',3,'March',4,'April',5,'May',6,'June',7,'July',8,'August',9,'September',";
query =
query
+ "10,'October',11,'November',12,'December'), to_char(bp_strt_dat,'MM/DD/YYYY'),to_char(bp_end_dat,'MM/DD/YYYY') ";
query = query + "from blg_prd,blg_sys ";
query = query + "where bs_id=bs_id_fk and ( bp_strt_dat >= ";
query = query + "(select strt_dat from fung_yr where yr = ?) and ";
query = query + "bp_end_dat <= (select end_dat from fung_yr where yr = ?) ";
query = query + "or ( bp_year =to_number(?) and bp_month=7) ) ";
query = query + "order by ord,nm,bp_strt_dat ";
USFEnv.getLog().writeDebug("Dinvjrnl: JRNL Dates Query :" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, year);
pstmt.setString(2, year);
pstmt.setString(3, year);
rs = pstmt.executeQuery();
while (rs.next()) {
Dinvjrnl jrnldts = new Dinvjrnl(null);
jrnldts.strBpid = rs.getString(1);
jrnldts.strBlgsys = rs.getString(3);
jrnldts.strBlgmnth = rs.getString(5);
jrnldts.strBlgstrtdt = rs.getString(6);
jrnldts.strBlgenddt = rs.getString(7);
jrnllst.addElement(jrnldts);
jrnldts = null;
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: JRNL Dates not retreived for the Year ", 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);
}
}
USFEnv.getLog()
.writeDebug("Dinvjrnl: Journal Dates Vector size :" + jrnllst.size(), this, null);
return jrnllst;
}
public void connectItems(String query)
// PRE: query must be initialized
// POST: Updates the list of Items based on the query.
{
String driver = "org.apache.derby.jdbc.ClientDriver"; // Driver for DB
String url = "jdbc:derby://localhost:1527/ShopDataBase"; // Url for DB
String user = "******"; // Username for db
String pass = "******"; // Password for db
Connection myConnection; // Connection obj to db
Statement stmt; // Statement to execute a result appon
ResultSet results; // A set containing the returned results
int rowcount; // Num objects in the resultSet
int i; // Index for the array
try { // Try connection to db
Class.forName(driver).newInstance(); // Create our db driver
myConnection = DriverManager.getConnection(url, user, pass); // Initalize our connection
stmt =
myConnection.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE); // Create a new statement
results = stmt.executeQuery(query); // Store the results of our query
rowcount = 0;
if (results.last()) // Go to the last result
{
rowcount = results.getRow();
results.beforeFirst();
}
itemsArray = new Item[rowcount];
i = 0;
while (results.next()) // Itterate through the results set
{
itemsArray[i] =
new Item(
results.getInt("item_id"),
results.getString("item_name"),
results.getString("item_type"),
results.getInt("price"),
results.getInt("owner_id"),
results.getString("item_path")); // Creat new Item
i++;
}
results.close(); // Close the ResultSet
stmt.close(); // Close the statement
myConnection.close(); // Close the connection to db
} catch (Exception e) // Cannot connect to db
{
System.err.println(e.toString());
System.err.println("Error, something went horribly wrong in connectItems!");
}
}
private void inputLorryWithoutReserv(String[] request) {
ResultSet rs = null;
String[] idList = request[2].split("@");
try {
rs = beanOracle.selection("X, Y", "PARC", "ETAT=0");
} catch (SQLException ex) {
SendMsg("ERR#Base de donnée inaccessible");
System.err.println("Erreur SQL exception input lorry" + ex.getStackTrace());
return;
}
String reponse = "ACK#";
ArrayList emplacement = new ArrayList();
try // on regarde si y'a assez de place et on recupere l'id de ces places.
{
for (int i = 0; i < idList.length; i++) {
if (rs.next()) {
reponse =
reponse + idList[i] + "==>(" + rs.getString("X") + ";" + rs.getString("Y") + ")@";
emplacement.add(rs.getString("X") + ";" + rs.getString("Y"));
} else {
SendMsg("ERR#Erreur pas assez de places");
return;
}
}
} catch (SQLException ex) {
SendMsg("ERR#Base de donnée inaccessible");
System.err.println("Erreur SQL exception input lorry" + ex.getStackTrace());
return;
}
// On insert les containers ajoutés dans la BD et on leur met un numéro de réservation + on
// réserve leurs places
Random rand = new Random();
int resID = rand.nextInt(999999);
for (int i = 0; i < idList.length; i++) {
String[] coord = emplacement.get(i).toString().split(";");
HashMap<String, String> insertion = new HashMap();
HashMap<String, String> update = new HashMap();
insertion.put("ID_CONTAINER", idList[i]);
insertion.put("RESERVATION", Integer.toString(resID));
update.put("ETAT", "1");
try {
beanOracle.ecriture("CONTAINERS", insertion);
beanOracle.miseAJour("PARC", update, "X=" + coord[0] + " AND Y=" + coord[1]);
} catch (requeteException ex) {
System.err.println("Erreur d'insertion ");
}
}
SendMsg(reponse);
}
private void printOutLogs(Connection connection, PrintWriter out) throws SQLException {
Statement select = connection.createStatement();
ResultSet result = select.executeQuery("SELECT * FROM LOGGING ORDER BY DATE ASC");
while (result.next()) {
Timestamp date = result.getTimestamp("DATE");
String ip = result.getString("IP");
String url = result.getString("URL");
out.println(date + "\t\t" + ip + "\t\t" + url);
}
}
stab(ResultSet k) {
super("PATIENT FOUND");
String[] chd = {
"Date",
"Patient ID",
"Name",
"Gender",
"Age",
"Weight",
"Address",
"Contact No.",
"Doctor Name",
"Symptoms",
"Dignosis",
"Fee: Rs.",
"Blood Group"
};
// DefaultTableModel dtm=new DefaultTableModel();
// jt.setModel(new DefaultTableModel(arr,chd));
jt = new JTable();
jsp = new JScrollPane(jt);
btn = new JButton("CLOSE");
btn.addActionListener(this);
int i = 0;
try {
while (k.next()) {
arr[i][0] = k.getString(1);
// System.out.println("ddddddddddddd"+arr[i][0]);
arr[i][1] = "" + k.getInt(2);
arr[i][2] = k.getString(3) + " " + k.getString(4) + " " + k.getString(5);
arr[i][3] = k.getString(6);
arr[i][4] = "" + k.getInt(7);
arr[i][5] = k.getString(8);
arr[i][6] = k.getString(9);
arr[i][7] = k.getString(10);
arr[i][8] = k.getString(11);
arr[i][9] = k.getString(12);
arr[i][10] = k.getString(13);
arr[i][11] = "" + k.getInt(14);
arr[i][12] = k.getString(15);
arr[i][13] = k.getString(16);
// dtm.insertRow(i,new Object[]{patient.rs.getString(1),
// patient.rs.getInt(2),patient.rs.getString(3)+patient.rs.getString(4)+patient.rs.getString(5),patient.rs.getString(6),patient.rs.getInt(7),patient.rs.getString(8),patient.rs.getString(9),patient.rs.getString(10),patient.rs.getString(11),patient.rs.getString(12),patient.rs.getString(13),patient.rs.getInt(14)});
i++;
}
} catch (Exception e12) {
System.out.println("" + e12);
}
jt.setModel(new DefaultTableModel(arr, chd));
Container con = getContentPane();
con.setLayout(null);
jsp.setBounds(20, 20, 1230, 600);
btn.setBounds(685, 630, 100, 30);
add(jsp);
add(btn);
setSize(1330, 800);
setVisible(true);
}
public void updateTables(String updateQuery1, String updateQuery2, String updateQuery3)
// PRE: updateQuery1,updateQuery2, updateQuery3 must be initialized
// POST: Updates the list of Items based on the querys.
{
String driver = "org.apache.derby.jdbc.ClientDriver"; // Driver for DB
String url = "jdbc:derby://localhost:1527/ShopDataBase"; // Url for DB
String user = "******"; // Username for db
String pass = "******"; // Password for db
Connection myConnection; // Connection obj to db
Statement stmt; // Statement to execute a result appon
ResultSet results; // A set containing the returned results
int i; // Index for the array
try // Try connection to db
{
Class.forName(driver).newInstance(); // Create our db driver
myConnection = DriverManager.getConnection(url, user, pass); // Initalize our connection
stmt =
myConnection.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
stmt.executeUpdate(updateQuery1);
stmt.executeUpdate(updateQuery2);
stmt.executeUpdate(updateQuery3);
results = stmt.executeQuery(previous_query); // Call the previous query
i = 0;
while (results.next()) // Itterate through the results set
{
itemsArray[i] =
new Item(
results.getInt("item_id"),
results.getString("item_name"),
results.getString("item_type"),
results.getInt("price"),
results.getInt("owner_id"),
results.getString("item_path"));
i++;
}
results.close(); // Close the ResultSet
stmt.close(); // Close the statement
myConnection.close(); // Close the connection to db
} catch (Exception e) // Cannot connect to db
{
System.err.println(e.toString());
System.err.println("Error, something went horribly wrong! in updateTables");
}
}
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con =
DriverManager.getConnection(Utility.connection, Utility.username, Utility.password);
String email = request.getParameter("email_id");
String number = "";
boolean exists = false;
String user_name = "";
int user_id = -1;
String str1 = "SELECT USER_ID,NAME,PHONE_NUMBER FROM USERS WHERE EMAIL_ID=?";
PreparedStatement prep1 = con.prepareStatement(str1);
prep1.setString(1, email);
ResultSet rs1 = prep1.executeQuery();
if (rs1.next()) {
exists = true;
user_id = rs1.getInt("USER_ID");
user_name = rs1.getString("NAME");
number = rs1.getString("PHONE_NUMBER");
}
int verification = 0;
JSONObject data = new JSONObject();
if (exists) {
verification = (int) (Math.random() * 9535641 % 999999);
System.out.println("Number " + number + "\nVerification: " + verification);
SMSProvider.sendSMS(
number, "Your One Time Verification Code for PeopleConnect Is " + verification);
}
data.put("user_name", user_name);
data.put("user_id", user_id);
data.put("verification_code", "" + verification);
data.put("phone_number", number);
String toSend = data.toJSONString();
out.print(toSend);
System.out.println(toSend);
} catch (Exception e) {
e.printStackTrace();
} finally {
out.close();
}
}
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.setContentType("text/html");
PrintWriter toClient = res.getWriter();
toClient.println("<!DOCTYPE HTML>");
toClient.println("<html>");
toClient.println("<head><title>Books</title></head>");
toClient.println("<body>");
toClient.println("<a href=\"index.html\">Home</A>");
toClient.println("<h2>List of books</h2>");
HttpSession session = req.getSession(false);
if (session != null) {
String name = (String) session.getAttribute("name");
if (name != null) {
toClient.println("<h2>name: " + name + "</h2>");
}
}
toClient.print("<form action=\"bookOpinion\" method=GET>");
toClient.println("<table border='1'>");
String sql = "Select code, title, author FROM books";
System.out.println(sql);
try {
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery(sql);
while (result.next()) {
toClient.println("<tr>");
String codeStr = result.getString("code");
toClient.println(
"<td><input type=\"radio\" name=\"book" + "\" value=\"" + codeStr + "\"></td>");
toClient.println("<td>" + codeStr + "</td>");
toClient.println("<td>" + result.getString("title") + "</td>");
toClient.println("<td>" + result.getString("author") + "</td>");
toClient.println("</tr>");
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println("Resulset: " + sql + " Exception: " + e);
}
toClient.println("</table>");
toClient.println("<textarea rows=\"8\" cols=\"60\" name=\"comment\"></textarea><BR>");
toClient.println("<input type=submit>");
toClient.println("</form>");
toClient.println("</body>");
toClient.println("</html>");
toClient.close();
}
private Athlete parseAthlete(ResultSet athleteSet) throws SQLException {
SimpleDateFormat dateFormat = (SimpleDateFormat) DateFormat.getDateInstance(DateFormat.MEDIUM);
Athlete athlete = new Athlete();
athlete.setName(athleteSet.getString("name"));
try {
Date date = new SimpleDateFormat("yyyy-MM-dd").parse(athleteSet.getString("dob"));
athlete.setBirthday(dateFormat.format(date));
} catch (ParseException e) {
return null;
}
athlete.setCountry(athleteSet.getString("country_code"));
return athlete;
}
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 List<ClientEventEntry> findLogEvents(
List<SearchConstraint> constraints, List<String> orderBy, int offset, int limit, Connection c)
throws SQLException {
StringBuffer sql =
new StringBuffer(
"select e.event_id, e.customer_id, e.user_id, e.event_time, e.description, e.has_log_file from dat_client_log_events e ");
appendWhere(sql, constraints, s_propToColumnMap);
appendOrderBy(sql, orderBy, "e.event_id desc", s_propToColumnMap);
appendLimits(sql, offset, limit);
Statement stmt = null;
ResultSet rs = null;
try {
stmt = c.createStatement();
rs = stmt.executeQuery(sql.toString());
List<ClientEventEntry> results = new ArrayList<ClientEventEntry>();
while (rs.next()) {
ClientEventEntry entry =
new ClientEventEntry(
rs.getInt(1),
rs.getInt(2),
rs.getInt(3),
resolveDate(rs.getTimestamp(4)),
rs.getString(5),
rs.getInt(6) != 0);
results.add(entry);
}
return results;
} finally {
DbUtils.safeClose(rs);
DbUtils.safeClose(stmt);
}
}
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;
}
