/** * Tests the implementation of getCharacterStream(long pos, long length). * * @throws Exception */ public void testGetCharacterStreamLong() throws Exception { String str1 = "This is a test String. This is a test String"; Reader r1 = new java.io.StringReader(str1); PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)"); int id = BlobClobTestSetup.getID(); ps.setInt(1, id); ps.setCharacterStream(2, r1); ps.execute(); ps.close(); Statement st = createStatement(); ResultSet rs = st.executeQuery("select CLOBDATA from " + "BLOBCLOB where ID=" + id); rs.next(); Clob clob = rs.getClob(1); Reader r_1 = clob.getCharacterStream(2L, 5L); String str2 = str1.substring(1, 6); Reader r_2 = new java.io.StringReader(str2); assertEquals(r_2, r_1); rs.close(); st.close(); }
/** * This method queries the database to get the Invoice Number associated with the passes Invid * * @exception SQLException, if query fails * @author */ public long getInvID(String inv_no) { String query; long invID = 0; Statement stmt = null; ResultSet rs = null; query = "select inv_id from inv where inv_no=" + inv_no; try { stmt = conn.createStatement(); rs = stmt.executeQuery(query); if (rs.next()) { invID = rs.getLong(1); } if (rs != null) rs.close(); if (stmt != null) stmt.close(); } catch (SQLException ex) { USFEnv.getLog().writeCrit("Dinvview: The Invoice 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 (invID); }
/** * This method queries the database to get the Invoice Number associated with the passes Invid * * @exception SQLException, if query fails * @author */ public String getInvno(String invid) { String query; String invno = ""; Statement stmt = null; ResultSet rs = null; query = "select inv_no from rhcc_inv where rhcc_inv_id=" + invid; try { stmt = conn.createStatement(); rs = stmt.executeQuery(query); if (rs.next()) { invno = rs.getString(1); } if (rs != null) rs.close(); if (stmt != null) stmt.close(); } catch (SQLException ex) { USFEnv.getLog().writeCrit("RhccDinvview: The Invoice Number 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 invno; }
/** * This method queries the database to get the SPIN associated with the passes Invid * * @exception SQLException, if query fails * @author */ public String getSPINname(String invid) { String query; String spinnm = ""; Statement stmt = null; ResultSet rs = null; // query = "select slc_nm from usw_co,inv where uc_id=uc_id_fk and inv_id="+invid; query = "select srv_provr_nm from srv_provr,rhcc_inv where spin = rhcc_inv.spin_fk and rhcc_inv_id=" + invid; try { stmt = conn.createStatement(); rs = stmt.executeQuery(query); if (rs.next()) { spinnm = rs.getString(1); } if (rs != null) rs.close(); if (stmt != null) stmt.close(); } catch (SQLException ex) { USFEnv.getLog().writeCrit("RhccDinvview: The SPIN Name 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 spinnm; }
/** * 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 validate the passed in FRN for the selected Funding Year * * @exception SQLException, if query fails * @author */ public boolean validateFRN(String frn, String year) { String query; boolean validfrn = false; Statement stmt = null; ResultSet rs = null; query = "select wo_id from wrk_ordr where wrk_ordr_no='" + frn + "' and yr_fk=" + year; USFEnv.getLog().writeCrit("RhccDinvview: FRN query " + query, this, null); try { stmt = conn.createStatement(); rs = stmt.executeQuery(query); if (rs.next()) { validfrn = true; if (rs != null) rs.close(); if (stmt != null) stmt.close(); return true; } } catch (SQLException ex) { USFEnv.getLog().writeCrit("RhccDinvview: FRN not valid for the Year ", 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 false; }
/** * 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); } }
boolean validar(String busqueda, String Tabla, String llave_consulta, String llave_busqueda) throws IOException, SQLException { Connection con = null; ResultSet resultadosConsulta = null; Statement instruccionSQL = null; try { con = DriverManager.getConnection("jdbc:mysql://localhost/rsu_inventario", "root", "inforsu"); // Preparamos la consulta instruccionSQL = con.createStatement(); resultadosConsulta = instruccionSQL.executeQuery( "SELECT " + busqueda + " FROM " + Tabla + " WHERE " + llave_consulta + " = '" + llave_busqueda + "'"); if (resultadosConsulta .next()) { // si es valido el primer reg. hay una fila, tons el usuario y su pw existen con.close(); resultadosConsulta.close(); instruccionSQL.close(); ; return true; } // usuario validado correctamente else { con.close(); resultadosConsulta.close(); instruccionSQL.close(); return false; } // usuario validado incorrectamente } catch (Exception e) { e.printStackTrace(); con.close(); resultadosConsulta.close(); instruccionSQL.close(); return false; } }
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(); } }
/* return status codes for account/device */ public static int[] getStatusCodes(String accountID, String deviceID) throws DBException { /* account-id specified? */ if (StringTools.isBlank(accountID)) { return new int[0]; } /* device-id specified? */ if (StringTools.isBlank(deviceID)) { deviceID = ALL_DEVICES; } /* select */ // DBSelect: SELECT statucCode FROM StatusCode WHERE (accountID='acct') AND (deviceID='*') ORDER // BY statucCode DBSelect<StatusCode> dsel = new DBSelect<StatusCode>(StatusCode.getFactory()); dsel.setSelectedFields(StatusCode.FLD_statusCode); DBWhere dwh = dsel.createDBWhere(); dsel.setWhere( dwh.WHERE_( dwh.AND( dwh.EQ(StatusCode.FLD_accountID, accountID), dwh.EQ(StatusCode.FLD_deviceID, deviceID)))); dsel.setOrderByFields(StatusCode.FLD_statusCode); /* get list */ java.util.List<Integer> codeList = new Vector<Integer>(); Statement stmt = null; ResultSet rs = null; try { stmt = DBConnection.getDefaultConnection().execute(dsel.toString()); rs = stmt.getResultSet(); while (rs.next()) { int code = rs.getInt(StatusCode.FLD_statusCode); codeList.add(new Integer(code)); } } catch (SQLException sqe) { throw new DBException("Getting StatusCode List", sqe); } finally { if (rs != null) { try { rs.close(); } catch (Throwable t) { } } if (stmt != null) { try { stmt.close(); } catch (Throwable t) { } } } /* return array of status codes */ int codeListInt[] = new int[codeList.size()]; for (int i = 0; i < codeListInt.length; i++) { codeListInt[i] = codeList.get(i).intValue(); } return codeListInt; }
// // Examine BLOBs and CLOBs. // private void vetLargeObjects( Connection conn, HashSet<String> unsupportedList, HashSet<String> notUnderstoodList) throws Exception { Statement stmt = conn.createStatement(); stmt.execute("CREATE TABLE t (id INT PRIMARY KEY, " + "b BLOB(10), c CLOB(10))"); stmt.execute( "INSERT INTO t (id, b, c) VALUES (1, " + "CAST (" + TestUtil.stringToHexLiteral("101010001101") + "AS BLOB(10)), CAST ('hello' AS CLOB(10)))"); ResultSet rs = stmt.executeQuery("SELECT id, b, c FROM t"); rs.next(); Blob blob = rs.getBlob(2); Clob clob = rs.getClob(3); vetObject(blob, unsupportedList, notUnderstoodList); vetObject(clob, unsupportedList, notUnderstoodList); stmt.close(); conn.rollback(); }
/** * Test that <code>Clob.getCharacterStream(long,long)</code> works on CLOBs that are streamed from * store. (DERBY-2891) */ public void testGetCharacterStreamLongOnLargeClob() throws Exception { getConnection().setAutoCommit(false); // create large (>32k) clob that can be read from store final int size = 33000; StringBuilder sb = new StringBuilder(size); for (int i = 0; i < size; i += 10) { sb.append("1234567890"); } final int id = BlobClobTestSetup.getID(); PreparedStatement ps = prepareStatement("insert into blobclob(id, clobdata) values (?,cast(? as clob))"); ps.setInt(1, id); ps.setString(2, sb.toString()); ps.executeUpdate(); ps.close(); Statement s = createStatement(); ResultSet rs = s.executeQuery("select clobdata from blobclob where id = " + id); assertTrue(rs.next()); Clob c = rs.getClob(1); // request a small region of the clob BufferedReader r = new BufferedReader(c.getCharacterStream(4L, 3L)); assertEquals("456", r.readLine()); r.close(); c.free(); rs.close(); s.close(); rollback(); }
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(); }
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(); } }
/** * 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; }
/** * 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"); } }
/** * 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"); } }
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()); } } }
/** * 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 void dispose() { try { stmt.close(); con.close(); } catch (SQLException e) { } }
/** * 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"); } }
/** * 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; }
/** private method which actually will do all of our work for the sample */ private void executeSample() { String query = "select anEmployee from staff2"; try { Statement stmt = _con.createStatement(); ; // Execute the query which will return an Employee object // We will cast this using the Person interface. Note the // Person interface class MUST be in your CLASSPATH. You // Do not need Employee in your CLASSPATH. ResultSet rs = stmt.executeQuery(query); output("***Using interface class\n"); while (rs.next()) { Person aPerson = (Person) rs.getObject(1); displayMethods(aPerson.getClass()); output( "The person is: " + aPerson.toString() + "\nFirst Name= " + aPerson.getFirstName() + "\nLast Name= " + aPerson.getLastName() + "\n"); } // Now execute the same query, but this time we will use // reflection to access the class. Again, only the interface // Person is required in the CLASSPATH rs = stmt.executeQuery(query); output("***Using reflection\n"); Object theObj = null; while (rs.next()) { theObj = rs.getObject(1); output("The person is: " + theObj.toString() + "\n"); Class theClass = theObj.getClass(); displayMethods(theClass); Method m1 = theClass.getMethod("toString", new Class[0]); Method m2 = theClass.getMethod("getFirstName", new Class[0]); Method m3 = theClass.getMethod("getLastName", new Class[0]); output( "The person is: " + (Object) m1.invoke(theObj, new Object[0]) + "\nFirst Name= " + (Object) m2.invoke(theObj, new Object[0]) + "\nLast Name= " + (Object) m3.invoke(theObj, new Object[0]) + "\n"); } rs.close(); stmt.close(); } catch (SQLException sqe) { displaySQLEx(sqe); } catch (Exception e) { error("Unexpected exception : " + e.toString() + "\n"); 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, "@"); }
public ExptLocatorTree(Genome g) { super(); try { java.sql.Connection c = DatabaseFactory.getConnection(ExptLocator.dbRole); int species = g.getSpeciesDBID(); int genome = g.getDBID(); Statement s = c.createStatement(); ResultSet rs = null; rs = s.executeQuery( "select e.name, e.version from experiment e, exptToGenome eg where e.active=1 and " + "e.id=eg.experiment and eg.genome=" + genome); while (rs.next()) { String name = rs.getString(1); String version = rs.getString(2); ChipChipLocator loc = new ChipChipLocator(g, name, version); this.addElement(loc.getTreeAddr(), loc); } rs.close(); rs = s.executeQuery( "select ra.name, ra.version from rosettaanalysis ra, rosettaToGenome rg where " + "ra.id = rg.analysis and ra.active=1 and rg.genome=" + genome); while (rs.next()) { String name = rs.getString(1); String version = rs.getString(2); MSPLocator msp = new MSPLocator(g, name, version); this.addElement(msp.getTreeAddr(), msp); } rs.close(); rs = s.executeQuery( "select ra.name, ra.version from bayesanalysis ra, bayesToGenome rg where " + "ra.id = rg.analysis and ra.active=1 and rg.genome=" + genome); while (rs.next()) { String name2 = rs.getString(1); String version2 = rs.getString(2); ExptLocator loc2 = new BayesLocator(g, name2, version2); addElement(loc2.getTreeAddr(), loc2); } rs.close(); s.close(); DatabaseFactory.freeConnection(c); } catch (SQLException se) { se.printStackTrace(System.err); throw new RuntimeException(se); } catch (UnknownRoleException e) { e.printStackTrace(); } }
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 static void main(String args[]) throws SQLException { try { Class.forName("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException e) { System.out.println("Could not load driver"); } String user, pass; user = readEntry("userid : "); pass = readEntry("password: "******"jdbc:oracle:thin:@csoracle.utdallas.edu:1521:student", user, pass); boolean flag = false; while (flag == false) { // Check if student data exists String stu_no = readEntry("\n\nEnter student number : "); String query = "SELECT Name FROM STUDENT WHERE Student_number = " + stu_no; Statement s = conn.createStatement(); ResultSet r = s.executeQuery(query); if (r.next()) { System.out.println("Student data found"); } else { System.out.println("Student not found. Please try again"); } s.close(); System.out.println( "\n\n\n\n\n------------------------Enrollment for Spring 2007--------------------------"); System.out.println("1) Add a class"); System.out.println("2) Drop a class"); System.out.println("3) See my schedule"); String ch = readEntry("Enter your choice : "); switch (ch.charAt(0)) { case '1': System.out.println("\n\n--------Add a class--------"); addCourse(conn, stu_no); break; case '2': System.out.println("\n\n--------Drop a class--------"); dropCourse(conn, stu_no); break; case '3': System.out.println("\n\n--------Your Schedule--------"); viewSchedule(conn, stu_no); break; default: flag = true; } } conn.close(); }
private void loadNewDict(String sql) { Debug.getAppErrLogger().debug("------------------------------------------------load:" + sql); HashMap htemp = new HashMap(); Statement state = null; ResultSet rs_dic = null; try { if (conn != null) { conn.close(); conn = null; } getConnection(); state = conn.createStatement(); rs_dic = state.executeQuery(sql); while (rs_dic.next()) { String text = rs_dic.getString(1); String value = rs_dic.getString(2); if (null != text && value != null) { htemp.put(text.trim(), value.trim()); } } hashCache.put(sql, htemp); rs_dic.close(); state.close(); } catch (Exception e) { e.printStackTrace(); Debug.getAppErrLogger().error(e); } finally { try { if (rs_dic != null) { rs_dic.close(); rs_dic = null; } if (state != null) { state.close(); state = null; } } catch (Exception e) { Debug.getAppErrLogger().error("Inside DictCachService::loadNewDict() - 关闭时出错!\n" + e); } close(); } }
/** * This method queries the database to validate the passed in FRN for the selected Funding Year * has been invoiced or not * * @exception SQLException, if query fails * @author */ public boolean checkFRNinv(String frn, String year) { String query; boolean validfrn = false; Statement stmt = null; ResultSet rs = null; USFEnv.getLog().writeCrit("RhccDinvview: FRN Invoiced" + frn, this, null); USFEnv.getLog().writeCrit("RhccDinvview: FRN Invoiced" + year, this, null); query = " SELECT DISTINCT rhcc_inv_id_fk "; query = query + " FROM wrk_ordr, wrk_ordr_dets, wo_det_hsties "; query = query + " WHERE wo_id = wo_id_fk "; query = query + " AND wod_id = wod_id_fk "; query = query + " AND wdh_stat = 'P' "; query = query + " AND inv_stat = 'I' "; query = query + " AND wrk_ordr_no ='" + frn + "' "; query = query + " AND wrk_ordr.yr_fk =" + year; USFEnv.getLog().writeCrit("RhccDinvview: FRN Invoiced" + query, this, null); try { stmt = conn.createStatement(); rs = stmt.executeQuery(query); USFEnv.getLog().writeCrit("RhccDinvview: FRN Invoiced" + rs.getFetchSize(), this, null); if (rs.next()) { USFEnv.getLog() .writeCrit("RhccDinvview: FRN Invoiced" + rs.getString("rhcc_inv_id_fk"), this, null); validfrn = true; if (rs != null) rs.close(); if (stmt != null) stmt.close(); return true; } } catch (SQLException ex) { USFEnv.getLog().writeCrit("RhccDinvview: FRN not Invoiced", 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 false; }