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 Connection connectToDb(String uname, String pword) throws SQLException {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection con =
DriverManager.getConnection(
"jdbc:oracle:thin:hr/[email protected]:1521:orcl", uname.trim(), pword.trim());
return con;
}
public static void connect() {
try {
DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());
link = DriverManager.getConnection(myURL);
} catch (SQLException e) {
System.out.println("Echec de la connexion : " + e.getMessage());
}
}
public static void recieveTrafficRates(String IP, float upload, float download) {
System.out.println("Insertion of Data into Database!");
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String dbName = "traffic_report";
String driverName = "com.mysql.jdbc.Driver";
String userName = "******";
String password = "******";
String IP_address = IP;
float up = upload, down = download;
try {
Class.forName(driverName).newInstance();
con = DriverManager.getConnection(url + dbName, userName, password);
try {
Statement st = con.createStatement();
int val =
st.executeUpdate(
"INSERT current_traffic VALUES('" + IP + "','" + up + "','" + down + "')");
System.out.println("Data Entry process successfully!");
} catch (SQLException s) {
System.out.println("Table all ready exists!" + s);
}
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
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();
}
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);
}
}
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);
}
}
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);
}
}
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;
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
PrintWriter writer = response.getWriter();
String noteName = request.getParameter("noteName");
String note = request.getParameter("note");
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/evernoteDB");
Statement st = con.createStatement();
st.executeUpdate(
"INSERT INTO note (noteName, note) VALUES ('" + noteName + "','" + note + "')");
response.sendRedirect("/userNotes.jsp");
} catch (ClassNotFoundException e) {
writer.println("Couldn't load database driver: " + e.getMessage());
} catch (SQLException e) {
writer.println("SQLException caught: " + e.getMessage());
} catch (Exception e) {
writer.println(e);
}
}
public void carga_default() {
Connection con = null;
Statement st = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection("jdbc:mysql://localhost/rsu_inventario", "root", pass);
try {
st = con.createStatement();
st.executeUpdate(
"INSERT INTO ADMIN VALUES ('174183767','msX','msx','estudiante','santiago',"
+ "'2130','3030303','30303030','los valdios 3030','*****@*****.**',12345);");
} catch (SQLException s) {
SQL_FORM error = new SQL_FORM();
JOptionPane.showMessageDialog(error, "error al ingresar datos");
// SQL_FORM error = new SQL_FORM();
JOptionPane.showMessageDialog(error, s, "alert", JOptionPane.ERROR_MESSAGE);
} finally {
con.close();
st.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 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");
}
}
public static void main(String args[]) {
int myport = 9090;
Socket s;
ServerSocket ss;
BufferedReader fromSoc, fromKbd;
PrintStream toSoc;
String line, msg;
try {
s = new Socket("169.254.63.10", 6016);
System.out.println("enter line");
System.out.println("connected");
toSoc = new PrintStream(s.getOutputStream());
toSoc.println(myport);
String url =
"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}; DBQ=D://db.mdb; DriverID=22;READONLY=true;";
Connection con = DriverManager.getConnection(url, "", "");
Statement st = con.createStatement();
String ip = "169.254.63.10";
int port = 6016;
String video = "rooney";
st.executeUpdate(
" insert into p3 values('" + ip + "' , '" + port + "' , '" + video + "' ); ");
fun();
} catch (Exception e) {
System.out.println("exception" + e);
}
}
private static boolean connectToDatabase() {
String databaseConnectionInfo =
"jdbc:mysql://localhost/authorities?user=authorities&password=authorities&useUnicode=yes&characterEncoding=UTF-8";
if (authoritiesConn == null) {
try {
authoritiesConn = DriverManager.getConnection(databaseConnectionInfo);
getPreferredAuthorByOriginalNameStmt =
authoritiesConn.prepareStatement(
"SELECT viafId, normalizedName from preferred_authors where originalName = ?",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
getPreferredAuthorByNormalizedNameStmt =
authoritiesConn.prepareStatement(
"SELECT viafId, normalizedName from preferred_authors where originalName = ?",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
getPreferredAuthorByAlternateNameStmt =
authoritiesConn.prepareStatement(
"SELECT preferred_authors.viafId, normalizedName FROM `preferred_authors` inner join alternate_authors on preferred_authors.viafId = alternate_authors.viafId where alternateName = ?",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
return true;
} catch (Exception e) {
logger.error("Error connecting to authorities database");
return false;
}
}
return true;
}
public void conectarBDdinamic(String ip) {
if (ip == null) {
ip = "192.168.8.231";
}
url = "jdbc:postgresql://" + ip + "/ventasalm";
// javax.swing.JOptionPane.showMessageDialog(null, url);
String estado;
try {
final String CONTROLADOR = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
Class.forName(CONTROLADOR);
// System.getProperty( "user.dir" )+"/CarpetaBD/NombredelaBasedeDatos.mdb";
conexion = DriverManager.getConnection(url, user, pass);
sentencia = conexion.createStatement();
// estado="Conectado Correctamente";
} catch (ClassNotFoundException ex1) {
ex1.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(null, "Error Carga Driver");
estado = "Servidor no esta disponible";
// System.exit(1);
} catch (SQLException ex) {
ex.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(null, "Error Creacion Statement");
estado = "Servidor no esta disponible";
// System.exit(1);
}
// return estado;
}
public void conectarBaseDeDatos() {
try {
final String CONTROLADOR = "org.postgresql.Driver";
Class.forName(CONTROLADOR);
// System.getProperty( "user.dir" )+"/CarpetaBD/NombredelaBasedeDatos.mdb";
conexion =
DriverManager.getConnection(
"jdbc:postgresql://" + main.ipSeleccionada + "/" + objUtils.nameBD, user, pass);
// conexion = DriverManager.getConnection("jdbc:postgresql://127.0.0.1/goliak_jg", "postgres",
// "majcp071102kaiser");
sentencia = conexion.createStatement();
/*JOptionPane.showMessageDialog(null, "si conecta");*/
} catch (ClassNotFoundException ex1) {
// ex1.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(null, "Error Carga Driver." + ex1.getMessage());
System.exit(1);
} catch (SQLException ex) {
// ex.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(
null,
"Error Creacion Statement."
+ ex.getMessage()
+ " / "
+ url
+ " / "
+ user
+ " / "
+ pass);
System.exit(1);
}
}
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();
}
}
// AQUI VOYA OBTENER EL SERVDUIOR QUEMADO EN UN ARCHIVO DE TEXTO
public void conectarBaseDeDatos2() {
try {
final String CONTROLADOR = "org.postgresql.Driver";
Class.forName(CONTROLADOR);
// System.getProperty( "user.dir" )+"/CarpetaBD/NombredelaBasedeDatos.mdb";
conexion = DriverManager.getConnection(url2, user, pass);
sentencia = conexion.createStatement();
/*JOptionPane.showMessageDialog(null, "si conecta");*/
} catch (ClassNotFoundException ex1) {
// ex1.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(null, "Error Carga Driver." + ex1.getMessage());
System.exit(1);
} catch (SQLException ex) {
// ex.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(
null,
"Error Creacion Statement."
+ ex.getMessage()
+ " / "
+ url2
+ " / "
+ user
+ " / "
+ pass);
System.exit(1);
}
}
/**
* 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;
}
/**
* Delete a student from the database. Also deletes the student's folders. Throws
* InvalidDBRequestException if any error in database connection.
*
* @param username student's user name
* @throws InvalidDBRequestException
*/
private void purgeStudent(String username) throws InvalidDBRequestException {
try {
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
int count;
// delete from scores
count = stmt.executeUpdate("delete from scores where user_login = '******'");
// delete from student
count = stmt.executeUpdate("delete from student where login = '******'");
// delete student's folder
File studentDir = new File("./StudentQuizzes/" + username);
if (!(studentDir.delete())) {
System.err.println("Error in deleting folder for student: " + username);
}
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");
}
}
/**
* 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");
}
}
/**
* 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");
}
}
/**
* 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");
}
}
/* 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 static void installDriver(Driver driver) {
try {
DriverManager.registerDriver(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 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, "@");
}
/**
* Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
String username = request.getParameter("username");
String password = request.getParameter("password");
Statement stmt;
ResultSet rs;
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String connectionUrl = "jdbc:mysql://localhost/myflickr?" + "user=root&password=123456";
con = DriverManager.getConnection(connectionUrl);
if (con != null) {
System.out.println("connected to mysql");
}
} catch (SQLException e) {
System.out.println("SQL Exception: " + e.toString());
} catch (ClassNotFoundException cE) {
System.out.println("Class Not Found Exception: " + cE.toString());
}
try {
stmt = con.createStatement();
System.out.println("SELECT * FROM flickrusers WHERE name='" + username + "'");
rs = stmt.executeQuery("SELECT * FROM flickrusers WHERE name='" + username + "'");
while (rs.next()) {
if (rs.getObject(1).toString().equals(username)) {
out.println("<h1>To username pou epileksate uparxei hdh</h1>");
out.println("<a href=\"project3.html\">parakalw dokimaste kapoio allo.</a>");
stmt.close();
rs.close();
return;
}
}
stmt.close();
rs.close();
stmt = con.createStatement();
if (!stmt.execute("INSERT INTO flickrusers VALUES('" + username + "', '" + password + "')")) {
out.println("<h1>Your registration is completed " + username + "</h1>");
out.println("<a href=\"index.jsp\">go to the login menu</a>");
registerListener.Register(username);
} else {
out.println("<h1>To username pou epileksate uparxei hdh</h1>");
out.println("<a href=\"project3.html\">Register</a>");
}
} catch (SQLException e) {
throw new ServletException("Servlet Could not display records.", e);
}
}
/**
* 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 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!");
}
}
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;
}
