public boolean checkChartUpdateRequired(String doi, Connection con) throws SQLException { PreparedStatement getUpdateStmt = con.prepareStatement(DEF_GET_UPDATE_QUERY); getUpdateStmt.setString(1, doi); ResultSet rs1 = getUpdateStmt.executeQuery(); int lastNcites = 0; if (rs1.first()) { lastNcites = rs1.getInt("lastNcites"); } else { rs1.close(); getUpdateStmt.close(); return true; } rs1.close(); getUpdateStmt.close(); PreparedStatement getNcitesStmt = con.prepareStatement(DEF_GET_NCITES_QUERY); getNcitesStmt.setString(1, doi); int ncites = 0; ResultSet rs2 = getNcitesStmt.executeQuery(); if (rs2.first()) { ncites = rs2.getInt("ncites"); } rs2.close(); getNcitesStmt.close(); if (ncites != lastNcites) { return true; } else { return false; } } // - checkChartUpdateRequired
public int[] getAd() { int i = 0; int j = 0; int[] ans = null; String str = "SELECT * FROM ad ORDER BY id"; try { row = stm.executeQuery(str); row.last(); i = row.getRow(); row.first(); ans = new int[i + 1]; if (i > 0) { ans[0] = i; do { j++; ans[j] = Integer.parseInt(row.getString("priority")); } while (row.next()); } else { ans[0] = 0; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return ans; }
public void testTemp() throws Exception { Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.execute("CREATE TABLE #temp ( pk INT PRIMARY KEY, f_string VARCHAR(30), f_float FLOAT )"); // populate in the traditional way for (int i = 0; i < 100; i++) { stmt.execute( "INSERT INTO #temp " + "VALUES( " + i + "," + "'The String " + i + "'" + ", " + i + ")"); } dump(stmt.executeQuery("SELECT Count(*) FROM #temp")); // Navigate around ResultSet rs = stmt.executeQuery("SELECT * FROM #temp"); rs.first(); dumpRow(rs); rs.last(); dumpRow(rs); rs.first(); dumpRow(rs); stmt.execute("DROP TABLE #temp"); stmt.close(); }
public static void namejudge(Socket socket, Connection conn, BufferedReader is) { System.out.println("用户查询存在模块"); try { PrintWriter os = new PrintWriter(socket.getOutputStream()); String rec; rec = is.readLine(); // 执行SQL语句 Statement stmt = conn.createStatement(); // 创建语句对象,用以执行sql语言 if (rec.charAt(0) == '1') { ResultSet rs = stmt.executeQuery("select * from teacher_table where logname=" + rec); if (rs.first()) { os.println(rec); os.flush(); } else { os.println("no" + rec); os.flush(); } } else if (rec.charAt(0) == '2') { ResultSet rs = stmt.executeQuery("select * from student_table where logname=" + rec); if (rs.first()) { os.println(rec); os.flush(); } else { os.println("no" + rec); os.flush(); } } else { os.println("no" + rec); os.flush(); } } catch (Exception e) { System.out.println("Error:" + e); } }
public static void show(Socket socket, Connection conn, BufferedReader is) { System.out.println("用户信息输出模块"); try { PrintWriter os = new PrintWriter(socket.getOutputStream()); os.flush(); String rec; rec = is.readLine(); System.out.println("Test:"); Statement stmt = conn.createStatement(); // 创建语句对象,用以执行sql语言 if (rec.charAt(0) == '1') { ResultSet rs = stmt.executeQuery("select * from teacher_table where logname=" + rec); rs.first(); String send; send = rs.getString("name"); os.println(send); os.flush(); send = rs.getString("course"); os.println(send); os.flush(); send = rs.getString("address"); os.println(send); os.flush(); send = rs.getString("phone"); os.println(send); os.flush(); send = rs.getString("email"); os.println(send); os.flush(); } if (rec.charAt(0) == '2') { ResultSet rs = stmt.executeQuery("select * from student_table where logname=" + rec); rs.first(); String send; send = rs.getString("name"); os.println(send); os.flush(); send = rs.getString("academy"); os.println(send); os.flush(); send = rs.getString("address"); os.println(send); os.flush(); send = rs.getString("phone"); os.println(send); os.flush(); send = rs.getString("email"); os.println(send); os.flush(); } } catch (Exception e) { System.out.println("Error:" + e); } }
public Boolean move_first() { try { return results.first(); } catch (Exception ex) { throw new InvalidQueryException("Database access failed"); } }
private void isEdit(Boolean isEdit) { if (isEdit) { try { Connection con = FrameLogin.getConnect(); String sql = "SELECT * FROM Persons WHERE personId = " + Id; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql); if (rs.first()) { String firstname = rs.getString("FirstName"); String lastname = rs.getString("LastName"); String cellphone = rs.getString("CellPhoneNo"); String homephone = rs.getString("HomePhoneNo"); String gradyear = rs.getString("Graduation Year"); String Gender = rs.getString("Gender"); firstName.setText(firstname); lastName.setText(lastname); cellPhone.setText(cellphone); homePhone.setText(homephone); gradYear.setText(gradyear); gender.setSelectedItem(Gender); jLabel7.setVisible(false); studentId.setVisible(false); } else { MessageBox.infoBox("Error: ID not found", "Error"); } } catch (Exception e) { MessageBox.infoBox(e.toString(), "Error in isEdit"); } } FrameLogin.closeConnect(); }
public void insertChartUpdate(String doi, int lastNcites, Connection con) throws SQLException { PreparedStatement getUpdateStmt = con.prepareStatement(DEF_GET_UPDATE_QUERY); getUpdateStmt.setString(1, doi); ResultSet rs = getUpdateStmt.executeQuery(); boolean isNew = true; if (rs.first()) { isNew = false; } rs.close(); getUpdateStmt.close(); PreparedStatement stmt = null; if (isNew) { stmt = con.prepareStatement(DEF_INS_UPDATE_STMT); stmt.setString(1, doi); stmt.setInt(2, lastNcites); } else { stmt = con.prepareStatement(DEF_UPDATE_UPDATE_STMT); stmt.setInt(1, lastNcites); stmt.setString(2, doi); } stmt.executeUpdate(); stmt.close(); } // - insertChartUpdate
/** * Add recruitment request to permanent store. * * @param toAdd request to be added * @return id of added request or -1 if unsuccessful */ public int addRecruitmentRequest(RecruitmentRequestBean toAdd) { String insertStatement = "insert into recruitmentrequests (requestdep, requestdate, jobtitle, contracttype, yearsofexperience, jobdescription, status) values (?,?,?,?,?,?,?);"; try { PreparedStatement statement = connection.prepareStatement(insertStatement, Statement.RETURN_GENERATED_KEYS); statement.setNString(1, toAdd.getRequestDepartment()); statement.setDate(2, new java.sql.Date(toAdd.getRequestDate().getTime())); statement.setNString(3, toAdd.getJobTitle()); statement.setNString(4, toAdd.getContractType()); statement.setInt(5, toAdd.getYearsOfExperience()); statement.setNString(6, toAdd.getJobDescription()); statement.setNString(7, "active"); statement.executeUpdate(); ResultSet rs = statement.getGeneratedKeys(); if (rs.first()) { return rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); } return -1; }
public List<ClientBean> getClients() { String query = "select id, firstname, lastname, email, phonenumber from clients;"; Statement statement = null; try { statement = connection.createStatement(); final ResultSet resultSet = statement.executeQuery(query); List<ClientBean> result = new ArrayList<>(); boolean hasNext = resultSet.first(); while (hasNext) { int id = resultSet.getInt(1); ClientBean client = new ClientBean(id); String firstName = resultSet.getString(1); client.setFirstName(firstName); String lastName = resultSet.getString(2); client.setLastName(lastName); String email = resultSet.getString(3); client.setEmail(email); String phoneNumber = resultSet.getString(4); client.setPhone(phoneNumber); result.add(client); hasNext = resultSet.next(); } return result; } catch (SQLException e) { e.printStackTrace(); } return new ArrayList<>(); }
public int[] searchVideo(String key) { int i = 0; int j = 0; int[] ans = null; String str = "SELECT * FROM video WHERE name LIKE '%" + key + "%'"; try { row = stm.executeQuery(str); row.last(); i = row.getRow(); row.first(); if (i == 0) { ans = new int[1]; } else { ans = new int[i]; } if (i > 0) { do { ans[j] = Integer.parseInt(row.getString("id")); j++; } while (row.next()); } else { ans[0] = 0; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return ans; }
public void workerNameForCurrentWorker() throws Exception { userIdForCurrentUser(); if (Global.user_id == 0) { return; } if (!newConnection()) { return; } try { /* PreparedStatement to issue SQL query */ // Search worker using worker_id preparedStatement = connection.prepareStatement( "select last_name, first_name from workers where id = " + Global.worker_id); resultSet = preparedStatement.executeQuery(); resultSet.first(); String _worker_last_name = resultSet.getString("last_name"); String _worker_first_name = resultSet.getString("first_name"); Global.worker_name = _worker_last_name + ", " + _worker_first_name; } catch (Exception e) { throw e; } finally { closeDataObjects(); } }
public String[] getVideo() { int i = 0; int j = 0; String[] ans = null; String str = "SELECT * FROM video ORDER BY id"; try { row = stm.executeQuery(str); row.last(); i = row.getRow(); row.first(); ans = new String[i + 1]; if (i > 0) { ans[0] = Integer.toString(i); do { j++; ans[j] = row.getString("name"); } while (row.next()); } else { ans[0] = "0"; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return ans; }
private String lookupUser(String credentials) throws Exception { // Parse user and password String[] args = parseCredentials(credentials); String cmd; try { cmd = "SELECT user_id" + " FROM User" + " WHERE username = \"" + args[USERNAME_INDEX] + "\" AND password = \"" + args[PASSWORD_INDEX] + "\";"; } catch (IndexOutOfBoundsException e) { throw new Exception("No password given."); } JSONObject obj = new JSONObject(); try { ResultSet rs = query(cmd); if (rs.first()) { String userID = Integer.toString(rs.getInt(1)); obj.put("user_id", userID); obj.put("username", args[USERNAME_INDEX]); String sessionID = UUID.randomUUID().toString(); updateUserSessionID(sessionID, args[USERNAME_INDEX]); obj.put("session_id", sessionID); obj.put("task_list", lookupUserProgress(userID)); return obj.toString(); } } catch (NullPointerException | SQLException e) { throw new Exception("Error with SQL statement."); } throw new InvalidCredentialsException("Invalid user credentials."); // Look up the tasks the user has completed }
public void workerIdForCurrentUser() throws Exception { userIdForCurrentUser(); if (Global.user_id == 0) { Global.worker_id = 0; return; } if (!newConnection()) { Global.worker_id = 0; return; } try { /* PreparedStatement to issue SQL query */ // Search worker using user_id to extract worker_id preparedStatement = connection.prepareStatement( "select id, last_name, first_name from workers where user_id = " + Global.user_id); resultSet = preparedStatement.executeQuery(); resultSet.first(); Integer _worker_id = resultSet.getInt("id"); if (_worker_id == null) { _worker_id = 0; } Global.worker_id = _worker_id; } catch (Exception e) { throw e; } finally { closeDataObjects(); } }
private String getUserIDFromSessionID(String sessionID) throws SQLException { String cmd = "SELECT user_id FROM User WHERE session_id = \"" + sessionID + "\";"; ResultSet rs = query(cmd); if (rs.first()) { return rs.getString(1); } return null; }
public void ramasserObjet() { boolean vide = true; try { ResultSet rset = stmt.executeQuery( "select idObjet from objet where positionX=" + positionX + " and positionY=" + positionY); Hashtable<String, String> tabObj = new Hashtable<String, String>(); while (rset.next()) { vide = false; String idObj = rset.getString("idObjet"); System.out.println(idObj); tabObj.put(idObj, idObj); } rset.close(); if (!vide) { System.out.println("Quel objet voulez vous ramasser ? : "); String obj = IO.lireChaine(); if (tabObj.containsKey(obj)) { proc = conn.prepareCall("{call rammasser(?,?)}"); proc.setInt(1, idTroll); proc.setString(2, obj); proc.executeUpdate(); proc.close(); rset = stmt.executeQuery("select typeObjet from objet where idObjet='" + obj + "'"); rset.first(); String type = rset.getString("typeObjet"); if (type.equals("potion")) { menu.supprimerPopo(obj); } else { menu.supprimerObjet(obj); } rset = stmt.executeQuery("select paRestants from troll where idTroll=" + idTroll); int ancVal = 0; while (rset.next()) { ancVal = rset.getInt("paRestants"); } stmt.executeUpdate( "update troll SET paRestants = " + (ancVal - 1) + " where idTroll=" + idTroll); paRestants = paRestants - 1; } else { System.out.println("Cet objet ne se trouve pas sur votre case !"); } } else { System.out.println("Il n'y a aucun objet sur votre case"); } } catch (SQLException E) { System.err.println("SQLException: " + E.getMessage()); System.err.println("SQLState: " + E.getSQLState()); } }
public String getUserPosition(String username, String password) { try { Statement getUserPosition = connection.createStatement(); String query = "select position from users where username='******'"; ResultSet result = getUserPosition.executeQuery(query); result.first(); return result.getString(1); } catch (SQLException e) { e.printStackTrace(); } return null; }
public int record_count(String target, String source, String criteria) { try { Statement s = connection.createStatement(); if (criteria.isEmpty()) s.execute("Select count(" + target + ") From " + source); else s.execute("Select count(" + target + ") From " + source + " Where " + criteria); if (results != null) results.close(); results = s.getResultSet(); if (results.first()) return results.getInt(1); else return -1; } catch (Exception ex) { throw new InvalidQueryException("Database count failed"); } }
public Team find(int id) { Team team = new Team(); try { ResultSet result = this.connect .createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY) .executeQuery("SELECT * FROM teams WHERE te_id = " + id); if (result.first()) team = new Team(id, result.getFloat("te_points")); } catch (SQLException e) { e.printStackTrace(); } return team; }
// === USER OPERATIONS === public boolean login(String username, String hash) { try { Statement login = connection.createStatement(); String query = "select password from users where username='******';"; System.out.println(query); ResultSet result = login.executeQuery(query); result.first(); String password = result.getString(1); if (password.equals(hash)) return true; } catch (SQLException e) { e.printStackTrace(); } return false; }
/** * Get event given it's ID * * @return the event as a bean, or null if no matching event found */ public EventRequestBean getEventRequest(int eventId) { String query = "select id, name, clientid, type, fromdate, todate, budget, status, details_photo_video, details_music, details_poster, details_decoration, details_network, details_food_beverages, details_waiter, feedback_budget from eventrequests where id=?;"; try { final PreparedStatement preparedStatement = connection.prepareStatement(query); preparedStatement.setInt(1, eventId); final ResultSet resultSet = preparedStatement.executeQuery(); if (resultSet.first()) { return getFromResultSet(resultSet); } } catch (SQLException e) { e.printStackTrace(); } return new EventRequestBean(); }
/** 为借用 查询所有记录 */ public String[][] searchAllForUse() { Database DB = new Database(); AssetsBean aBean = new AssetsBean(); PersonBean pBean = new PersonBean(); String[][] sn = null; int row = 0; int i = 0; sql = "select * from AssetsTrjn where FromAcc ='设备借用' order by JourNo"; try { DB.OpenConn(); rs = DB.executeQuery(sql); if (rs.last()) { row = rs.getRow(); } if (row == 0) { sn = new String[1][6]; sn[0][0] = " "; sn[0][1] = " "; sn[0][2] = " "; sn[0][3] = " "; sn[0][4] = " "; sn[0][5] = " "; } else { sn = new String[row][6]; rs.first(); rs.previous(); while (rs.next()) { sn[i][0] = rs.getString("JourNo"); sn[i][1] = aBean.getAssetsName(rs.getString("AssetsID")); sn[i][2] = rs.getString("RegDate"); sn[i][3] = pBean.getPersonName(rs.getString("PersonID")); sn[i][4] = rs.getString("Use"); sn[i][5] = rs.getString("Other"); i++; } } } catch (Exception e) { } finally { DB.closeStmt(); DB.closeConn(); } return sn; }
public boolean verificaSenha(int codigo, String senha) { String sql; conecta(); try { sql = "SELECT senha FROM usuario WHERE codigo=" + codigo + ";"; System.out.println(sql); rs = stmt.executeQuery(sql); rs.first(); if (rs.getString("senha").equals(senha)) { return true; } else { return false; } } catch (SQLException e) { return false; } }
public SingleNameReport(WritableWorkbook workbook, Statement new_statement, String[] arguments) { String main_tsn = ""; String main_rank = ""; String main_kingdom = ""; row = 0; try { statement = new_statement; func = new AdditionalFunctions(statement); copy = workbook; kingdom = arguments[0]; hrank = arguments[1]; lrank = arguments[2]; scientificName = arguments[3]; date_from = arguments[4]; date_to = arguments[5]; user = arguments[6]; String temp = ""; temp = "SELECT tsn,rank_id,kingdom_id from Tree where scientificName='" + scientificName + "'"; System.out.println(temp); result = statement.executeQuery(temp); metadata = result.getMetaData(); int numberOfRows = 0; int hrankVal = Integer.parseInt(hrank); int main_rankVal = 0; if (result.last()) numberOfRows = result.getRow(); result.first(); if (numberOfRows > 0 && metadata.getColumnCount() > 0) { main_tsn = result.getString(1); main_rank = result.getString(2); main_rankVal = Integer.parseInt(main_rank); main_kingdom = result.getString(3); } if (HasChildren(main_tsn) == true) FindChildInformation(main_tsn, arguments, func); FileInFrontPage frontPage = new FileInFrontPage(copy, statement, arguments); copy.write(); copy.close(); } catch (Exception e) { e.printStackTrace(); System.exit(1); } } // end of constructor
/** * Handles the HTTP <code>GET</code> method. * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try { Class.forName("com.mysql.jdbc.Driver"); connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/baza", "root", ""); if (connect != null) { statement = connect.createStatement(); HttpSession session = request.getSession(); String Login = (String) session.getAttribute("login"); Integer Id = (Integer) session.getAttribute("id"); out.println("Hello <b>" + Login + "</b>!"); resultSet = statement.executeQuery( "select * from users where Nickname='" + Login + "' and idUsers='" + Id + "'"); resultSet.next(); // /? if (resultSet.first() == true) { String Surname = resultSet.getString("Surname"); String Name = resultSet.getString("Name"); String Password = resultSet.getString("password"); request.setAttribute("Name", Name); request.setAttribute("Surname", Surname); request.setAttribute("Login", Login); request.setAttribute("Password", Password); RequestDispatcher rd = request.getRequestDispatcher("profile.jsp"); rd.forward(request, response); response.sendRedirect("profile.jsp"); } else { connect.close(); request.setAttribute("error", "Something went wrong :/"); RequestDispatcher rd = request.getRequestDispatcher("profile.jsp"); rd.forward(request, response); response.sendRedirect("profile.jsp"); } } } catch (SQLException ex) { Logger.getLogger(Profile.class.getName()).log(Level.SEVERE, null, ex); } catch (ClassNotFoundException ex) { Logger.getLogger(Profile.class.getName()).log(Level.SEVERE, null, ex); } }
public ResultSet buscaUsuario(String login) { String sql; conecta(); try { sql = "SELECT * FROM usuario WHERE login='******';"; System.out.println(sql); rs = stmt.executeQuery(sql); rs.first(); if (rs.getString("login") != null) { return rs; } else { return null; } } catch (SQLException e) { return null; } }
// public method that checks for existance of children public boolean HasChildren(String tsn) { String temp = "SELECT tsn from Tree where parent_tsn='" + tsn + "'"; if (lrank.compareTo("0") != 0 && lrank.compareTo("7") != 0) temp = temp + " AND rank_id<=" + lrank; System.out.println(temp); int numberOfRows = 0; boolean flag = false; try { result = statement.executeQuery(temp); metadata = result.getMetaData(); if (result.last()) numberOfRows = result.getRow(); result.first(); if (numberOfRows > 0 && metadata.getColumnCount() > 0) flag = true; } catch (SQLException sql) { sql.printStackTrace(); System.exit(1); } return flag; } // end of method HasChildren
private String lookupTaskInfo(String param) { String cmd = "SELECT instructions, code_template, test_code FROM Task" + " WHERE task_id = " + param + ";"; JSONObject obj = new JSONObject(); try { ResultSet rs = query(cmd); if (rs.first()) { obj.put("instructions", rs.getString(1)); obj.put("code_template", rs.getString(2)); obj.put("test_code", rs.getString(3)); return obj.toString(); } } catch (SQLException e) { return ""; } return ""; }
public void logado() { String sql; conecta(); try { sql = "SELECT * FROM usuario WHERE logado='s'"; System.out.println(sql); rs = stmt.executeQuery(sql); rs.first(); nom = rs.getString("nome"); codi = Integer.parseInt(rs.getString("codigo")); } catch (SQLException e) { System.out.println("Erro ao executar o comando SQL:" + e.toString()); } }