public Vector getUsacFiles(String invoice) { Vector fileList = new Vector(); String query = ""; query = query + " SELECT distinct filename, ' Date:'||to_char(usac_prcs_dat,'MM/DD/YYYY'),trunc(usac_prcs_dat) usac_prcs_dat "; query = query + " FROM stage_usac_form "; query = query + " WHERE rtrim(ltrim(sdc_inv_no)) = ?"; query = query + " ORDER BY usac_prcs_dat "; USFEnv.getLog().writeDebug("getUsacFiles() Query :" + query, this, null); PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = cConn.prepareStatement(query); pstmt.setString(1, invoice); rs = pstmt.executeQuery(); while (rs.next()) { fileList.addElement(rs.getString(1)); fileList.addElement(rs.getString(2)); } // rs.close(); // stmt.close(); if (rs != null) rs.close(); if (pstmt != null) pstmt.close(); } catch (SQLException ex) { try { if (rs != null) rs.close(); if (pstmt != null) pstmt.close(); } catch (Exception e) { USFEnv.getLog().writeCrit("Unable to close ResultSet and statement", this, e); } USFEnv.getLog().writeCrit("getUsacFiles() Failed Query:" + query, this, ex); } /* try { if( rs != null) rs.close(); } catch(Exception e) { USFEnv.getLog().writeCrit("Unable to close ResultSet",this,null); } try { if( pstmt != null) pstmt.close(); } catch(Exception e) { USFEnv.getLog().writeCrit("Unable to close Prepared Statement",this,null); }*/ return fileList; }
private String getMonth(String month) { String query; String i_month = ""; PreparedStatement pstmt = null; ResultSet rs = null; query = "select to_number(to_char(to_date(?,'Month'),'MM')) from dual"; USFEnv.getLog().writeDebug("Dinvjrnl:Get Month - Query" + query, this, null); try { pstmt = conn.prepareStatement(query); pstmt.setString(1, month); rs = pstmt.executeQuery(); if (rs.next()) { i_month = rs.getString(1); } if (rs != null) rs.close(); if (pstmt != null) pstmt.close(); } catch (SQLException ex) { USFEnv.getLog().writeCrit("Dinvjrnl: Month Conversion Failed ", this, ex); try { if (rs != null) rs.close(); if (pstmt != null) pstmt.close(); } catch (SQLException e) { USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e); } } return i_month; }
public 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
private void initFromDatabase() throws SQLException, BlockStoreException { Statement s = conn.get().createStatement(); ResultSet rs; rs = s.executeQuery("SELECT value FROM settings WHERE name = '" + CHAIN_HEAD_SETTING + "'"); if (!rs.next()) { throw new BlockStoreException("corrupt Postgres block store - no chain head pointer"); } Sha256Hash hash = new Sha256Hash(rs.getBytes(1)); rs.close(); this.chainHeadBlock = get(hash); this.chainHeadHash = hash; if (this.chainHeadBlock == null) { throw new BlockStoreException("corrupt Postgres block store - head block not found"); } rs = s.executeQuery( "SELECT value FROM settings WHERE name = '" + VERIFIED_CHAIN_HEAD_SETTING + "'"); if (!rs.next()) { throw new BlockStoreException( "corrupt Postgres block store - no verified chain head pointer"); } hash = new Sha256Hash(rs.getBytes(1)); rs.close(); s.close(); this.verifiedChainHeadBlock = get(hash); this.verifiedChainHeadHash = hash; if (this.verifiedChainHeadBlock == null) { throw new BlockStoreException("corrupt Postgres block store - verified head block not found"); } }
/** * This method queries the database to get the name of the Billing System. * * @exception SQLException, if query fails * @author */ public String getBlgsysnm(String blgsys) { String query; String blgsysnm = ""; PreparedStatement pstmt = null; ResultSet rs = null; query = "select bs_nm from blg_sys where bs_id = ?"; USFEnv.getLog().writeDebug("Dinvjrnl: Billing System Name Query :" + query, this, null); try { pstmt = conn.prepareStatement(query); pstmt.setString(1, blgsys); rs = pstmt.executeQuery(); if (rs.next()) { blgsysnm = rs.getString(1); } if (rs != null) rs.close(); if (pstmt != null) pstmt.close(); } catch (SQLException ex) { USFEnv.getLog().writeCrit("Dinvjrnl: Billing System Name not Retreived ", this, ex); try { if (rs != null) rs.close(); if (pstmt != null) pstmt.close(); } catch (SQLException e) { USFEnv.getLog().writeCrit("Unable to close the resultset/prepare statement", this, e); } } return blgsysnm; }
/** * 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; }
/** * 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 list of the Billing Systems. * * @exception SQLException, if query fails * @author */ public Vector getYears(String year) { String query; Vector years = new Vector(); PreparedStatement pstmt = null; ResultSet rs = null; query = "select yr||'-'||(yr+1),yr from fung_yr where yr > ?"; try { pstmt = conn.prepareStatement(query); pstmt.setString(1, year); rs = pstmt.executeQuery(); while (rs.next()) { years.addElement(rs.getString(1)); years.addElement(rs.getString(2)); } if (rs != null) rs.close(); if (pstmt != null) pstmt.close(); } catch (SQLException ex) { USFEnv.getLog().writeCrit("Dinvjrnl: Years List not Retreived ", this, ex); try { if (rs != null) rs.close(); if (pstmt != null) pstmt.close(); } catch (SQLException e) { USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e); } } return years; }
/** * This method queries the database to get the sequence for the BP_ID. * * @exception SQLException, if query fails * @author */ public String getBpid() { String query; String bpid = ""; Statement stmt = null; ResultSet rs = null; query = "select bp_id_seq.nextval from dual "; try { stmt = conn.createStatement(); rs = stmt.executeQuery(query); if (rs.next()) { bpid = rs.getString(1); } if (rs != null) rs.close(); if (stmt != null) stmt.close(); } catch (SQLException ex) { USFEnv.getLog().writeCrit("Dinvjrnl: Sequence Value for BP_ID not Retreived ", this, ex); try { if (rs != null) rs.close(); if (stmt != null) stmt.close(); } catch (SQLException e) { USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e); } } return bpid; }
/** * This method queries the database to get the details related to the bp_id passed. * * @exception SQLException, if query fails * @author */ public Vector getBpdet(String bpid) { String query; Vector bpdet = new Vector(); PreparedStatement pstmt = null; ResultSet rs = null; query = "select rtrim(bs_id_fk||bp_rgn),bp_month from blg_prd where bp_id = ?"; try { pstmt = conn.prepareStatement(query); pstmt.setString(1, bpid); rs = pstmt.executeQuery(); while (rs.next()) { bpdet.addElement(rs.getString(1)); bpdet.addElement(rs.getString(2)); } if (rs != null) rs.close(); if (pstmt != null) pstmt.close(); } catch (SQLException ex) { USFEnv.getLog().writeCrit("Dinvjrnl: BP_ID details not Retreived ", this, ex); try { if (rs != null) rs.close(); if (pstmt != null) pstmt.close(); } catch (SQLException e) { USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e); } } return bpdet; }
@Test public void testImport() throws Exception { MemoryDatabase memDb = new MemoryDatabase("sample"); memDb.start(); final String jsonFileName = "/sample.json"; memDb.importJSON(getClass(), jsonFileName); JSONParser jsonParser = new JSONParser(); JSONArray tables = (JSONArray) jsonParser.parse(new InputStreamReader(getClass().getResourceAsStream(jsonFileName))); assertEquals(2, tables.size()); Connection conn = memDb.getConnection(); Statement stmt = conn.createStatement(); JSONObject employee = (JSONObject) tables.get(0); ResultSet rs = stmt.executeQuery("SELECT * FROM \"employee\""); verifyTableEquals(employee, rs); rs.close(); JSONObject team = (JSONObject) tables.get(1); rs = stmt.executeQuery("SELECT * FROM \"team\""); verifyTableEquals(team, rs); rs.close(); stmt.close(); conn.close(); memDb.stop(); }
/** * 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; }
/** * 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); } }
/** * This method queries the database to check if the passed start date for the Journal Month starts * exactly after the Previous end date. * * @exception SQLException, if query fails * @author */ public boolean check(String strtdat, String blgsys, String year, String month, String rgn) { if (month.length() > ((new Integer("2")).intValue())) { month = getMonth(month); } String query; PreparedStatement pstmt = null; ResultSet rs = null; query = "select 'true' from (select bp_end_dat,bp_year from blg_prd "; query = query + "where bp_month= decode(to_number(?),1,12,to_number(?)-1) and bs_id_fk = ?"; if ((rgn != null) && !(rgn.equals(""))) { query = query + " and bp_rgn = ?"; } else { query = query + " and bp_rgn is null"; } query = query + " and bp_year=decode(to_number(?),1,to_number(?)-1,to_number(?) ) ) A "; query = query + " where to_date(?,'MM/DD/YYYY')-A.bp_end_dat=1"; USFEnv.getLog().writeDebug("Dinvjrnl:check Date- Query" + query, this, null); try { pstmt = conn.prepareStatement(query); pstmt.setString(1, month); pstmt.setString(2, month); pstmt.setString(3, blgsys); int m = 4; if ((rgn != null) && !(rgn.equals(""))) { pstmt.setString(m, rgn); m = m + 1; } pstmt.setString(m, month); pstmt.setString(m + 1, year); pstmt.setString(m + 2, year); pstmt.setString(m + 3, strtdat); rs = pstmt.executeQuery(); if (rs.next()) { if (rs != null) rs.close(); if (pstmt != null) pstmt.close(); return true; } } catch (SQLException ex) { USFEnv.getLog().writeCrit("Dinvjrnl: Date Check Failed ", this, ex); try { if (rs != null) rs.close(); if (pstmt != null) pstmt.close(); } catch (SQLException e) { USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e); } } return false; }
/** * 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 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(); } }
/** * Determine whether or a not a User with the supplied researcherID exists * * @param username The researcherID to test * @return true if the user exists, false if not * @throws SQLException if a database error was encountered */ public static boolean userExists(int researcherID) throws SQLException { boolean returnVal = false; // Get our connection to the database. Connection conn = DBConnectionManager.getConnection("yrc"); PreparedStatement stmt = null; ResultSet rs = null; try { stmt = conn.prepareStatement("SELECT researcherID FROM tblUsers WHERE researcherID = ?"); stmt.setInt(1, researcherID); rs = stmt.executeQuery(); // No rows returned. if (!rs.next()) { returnVal = false; } else { returnVal = true; } rs.close(); rs = null; stmt.close(); stmt = null; conn.close(); conn = null; } finally { // Always make sure result sets and statements are closed, // and the connection is returned to the pool if (rs != null) { try { rs.close(); } catch (SQLException e) {; } rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException e) {; } stmt = null; } if (conn != null) { try { conn.close(); } catch (SQLException e) {; } conn = null; } } return returnVal; }
public int createRelease(String label, String releaseDesc) { Connection dbConn = null; PreparedStatement prepStmt = null; Statement stmt = null; ResultSet rset = null; try { dbConn = ds.getConnection(); dbConn.setAutoCommit(false); stmt = dbConn.createStatement(); rset = stmt.executeQuery("select nextval('release_id_seq')"); rset.next(); int ret = rset.getInt(1); rset.close(); prepStmt = dbConn.prepareStatement("insert into release values(?, ?, now(), ?)"); prepStmt.setInt(1, ret); prepStmt.setString(2, label); if (releaseDesc == null) { prepStmt.setNull(3, Types.VARCHAR); } else { prepStmt.setString(3, releaseDesc); } prepStmt.execute(); dbConn.commit(); return ret; } catch (SQLException e) { try { dbConn.rollback(); } catch (Exception ee) { } throw new RuntimeException(e); } finally { try { rset.close(); } catch (Exception e) { } try { stmt.close(); } catch (Exception e) { } try { prepStmt.close(); } catch (Exception e) { } try { dbConn.close(); } catch (Exception e) { } } }
/** * Description of the Method * * @param db Description of the Parameter * @return Description of the Return Value * @throws SQLException Description of the Exception */ public DependencyList processDependencies(Connection db) throws SQLException { DependencyList dependencyList = new DependencyList(); // Check for orders associated with this customer product try { PreparedStatement pst = db.prepareStatement( "SELECT COUNT(DISTINCT(order_id)) as orderscount " + "FROM customer_product_history " + "WHERE customer_product_id = ? "); pst.setInt(1, this.getId()); ResultSet rs = pst.executeQuery(); if (rs.next()) { int orderscount = rs.getInt("orderscount"); if (orderscount != 0) { Dependency thisDependency = new Dependency(); thisDependency.setName("orders"); thisDependency.setCount(orderscount); thisDependency.setCanDelete(true); dependencyList.add(thisDependency); } } rs.close(); pst.close(); } catch (SQLException e) { throw new SQLException(e.getMessage()); } // check for quotes associated with this customer product try { PreparedStatement pst = db.prepareStatement( "SELECT COUNT(*) as quotescount FROM order_entry " + "WHERE quote_id > -1 AND order_id IN " + "(SELECT DISTINCT(order_id) " + " FROM customer_product_history " + " WHERE customer_product_id = ? )"); pst.setInt(1, this.getId()); ResultSet rs = pst.executeQuery(); if (rs.next()) { int quotescount = rs.getInt("quotescount"); if (quotescount != 0) { Dependency thisDependency = new Dependency(); thisDependency.setName("quotes"); thisDependency.setCount(quotescount); thisDependency.setCanDelete(true); dependencyList.add(thisDependency); } } rs.close(); pst.close(); } catch (SQLException e) { throw new SQLException(e.getMessage()); } return dependencyList; }
public void run() { m_nTrans = 0; m_nTrans1Sec = 0; m_nTrans2Sec = 0; m_nTimeSum = 0; if (m_nNumRuns <= 0) return; Statement stmt = null; try { stmt = m_conn.createStatement(); ResultSet set; // get branch count log("select max(branch) from " + m_Driver.getBranchName() + "\n", 2); set = stmt.executeQuery("select max(branch) from " + m_Driver.getBranchName()); if (set != null && set.next()) { m_nMaxBranch = set.getInt(1); set.close(); } // get teller count log("select max(teller) from " + m_Driver.getTellerName() + "\n", 2); set = stmt.executeQuery("select max(teller) from " + m_Driver.getTellerName()); if (set != null && set.next()) { m_nMaxTeller = set.getInt(1); set.close(); } // get account count log("select max(account) from " + m_Driver.getAccountName() + "\n", 2); set = stmt.executeQuery("select max(account) from " + m_Driver.getAccountName()); if (set != null && set.next()) { m_nMaxAccount = set.getInt(1); set.close(); } } catch (SQLException e) { log("Error getting table limits : " + e.getMessage() + "\n", 0); } finally { if (stmt != null) try { stmt.close(); } catch (SQLException e) { } stmt = null; } // System.out.println("Thread : " + getName() + " Branch :" + m_nMaxBranch + " Teller : " + // m_nMaxTeller + " Account : " + m_nMaxAccount); if (m_bRunText) runTextTest(); if (m_bRunPrepared) runPrepareTest(); if (m_bRunSProc) runProcTest(); if (m_bCloseConnection) { try { m_conn.close(); log("Thread connection closed", 2); } catch (SQLException e) { } } if (m_log != null) m_log.taskDone(); }
@Test public void testReadWithConnection() throws Exception { PreparedStatement preparedStatement = EasyMock.createStrictMock(PreparedStatement.class); ResultSet resultSet = EasyMock.createStrictMock(ResultSet.class); Connection connection = EasyMock.createStrictMock(Connection.class); SavedReviewerSearch search = new SavedReviewerSearch(); int id = 34; String name = "myName"; SavedReviewerSearchDAO dao = EasyMock.createMockBuilder(SavedReviewerSearchDAO.class) .withConstructor() .addMockedMethod("resultFromResultSet") .createStrictMock(); EasyMock.expect(connection.prepareStatement(SavedReviewerSearchDAO.READ_SQL)) .andReturn(preparedStatement); preparedStatement.setInt(1, id); EasyMock.expectLastCall(); preparedStatement.setString(2, name); EasyMock.expectLastCall(); EasyMock.expect(preparedStatement.executeQuery()).andReturn(resultSet); EasyMock.expect(resultSet.next()).andReturn(true); EasyMock.expect(dao.resultFromResultSet(resultSet)).andReturn(search); resultSet.close(); EasyMock.expectLastCall(); preparedStatement.close(); EasyMock.replay(preparedStatement, resultSet, connection, dao); SavedReviewerSearch result = dao.read(connection, id, name); assertEquals("Wrong search returned", search, result); EasyMock.verify(preparedStatement, resultSet, connection, dao); // failure case EasyMock.reset(preparedStatement, resultSet, connection, dao); EasyMock.expect(connection.prepareStatement(SavedReviewerSearchDAO.READ_SQL)) .andReturn(preparedStatement); preparedStatement.setInt(1, id); EasyMock.expectLastCall(); preparedStatement.setString(2, name); EasyMock.expectLastCall(); EasyMock.expect(preparedStatement.executeQuery()).andReturn(resultSet); EasyMock.expect(resultSet.next()).andReturn(false); resultSet.close(); EasyMock.expectLastCall(); preparedStatement.close(); EasyMock.replay(preparedStatement, resultSet, connection, dao); try { dao.read(connection, id, name); fail("Should have thrown a sql exception."); } catch (SQLException ex) { // expected } EasyMock.verify(preparedStatement, resultSet, connection, dao); }
@Test public void utf() throws SQLException { ResultSet rs = stat.executeQuery( "select '" + utf01 + "','" + utf02 + "','" + utf03 + "','" + utf04 + "','" + utf05 + "','" + utf06 + "','" + utf07 + "','" + utf08 + "';"); assertEquals(utf01, rs.getString(1)); assertEquals(utf02, rs.getString(2)); assertEquals(utf03, rs.getString(3)); assertEquals(utf04, rs.getString(4)); assertEquals(utf05, rs.getString(5)); assertEquals(utf06, rs.getString(6)); assertEquals(utf07, rs.getString(7)); assertEquals(utf08, rs.getString(8)); rs.close(); PreparedStatement prep = conn.prepareStatement("select ?,?,?,?,?,?,?,?;"); prep.setString(1, utf01); prep.setString(2, utf02); prep.setString(3, utf03); prep.setString(4, utf04); prep.setString(5, utf05); prep.setString(6, utf06); prep.setString(7, utf07); prep.setString(8, utf08); rs = prep.executeQuery(); assertTrue(rs.next()); assertEquals(utf01, rs.getString(1)); assertEquals(utf02, rs.getString(2)); assertEquals(utf03, rs.getString(3)); assertEquals(utf04, rs.getString(4)); assertEquals(utf05, rs.getString(5)); assertEquals(utf06, rs.getString(6)); assertEquals(utf07, rs.getString(7)); assertEquals(utf08, rs.getString(8)); rs.close(); }
public void readData() { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); con = DriverManager.getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" + filep); stmnt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); rst = con.getMetaData().getTables(null, null, "%", null); rst.next(); String SheetName = rst.getString(3); String query = "Select distinct * from [" + SheetName + "]"; rs = stmnt.executeQuery(query); if (rs != null) { rs.last(); RowCount = rs.getRow(); rs.beforeFirst(); // stmnt.close(); rst.close(); if (RowCount > 0) { registno = new String[RowCount]; name = new String[RowCount]; kdno = new int[RowCount]; kcno = new double[RowCount]; ccno = new double[RowCount]; seat = new double[RowCount]; i = 0; while (rs.next() && (rs.getString(1) != null)) { // rs.next(); i++; try { registno[i] = rs.getString(1); name[i] = rs.getString(2); kdno[i] = Integer.parseInt(rs.getString(3)); kcno[i] = Double.parseDouble(rs.getString(4)); ccno[i] = Double.parseDouble(rs.getString(5)); seat[i] = Double.parseDouble(rs.getString(6)); System.out.println(i); } catch (Exception e) { e.printStackTrace(); } } } } rs.close(); con.close(); } catch (Exception e) { // System.out.println("fail to get student connection"); } }
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; } }
@Test public void testCreateWithConnection() throws Exception { PreparedStatement preparedStatement = EasyMock.createStrictMock(PreparedStatement.class); ResultSet resultSet = EasyMock.createStrictMock(ResultSet.class); Connection connection = EasyMock.createStrictMock(Connection.class); SavedReviewerSearch search = new SavedReviewerSearch(); Date date = new Date(); SavedReviewerSearchDAO dao = EasyMock.createMockBuilder(SavedReviewerSearchDAO.class) .withConstructor() .addMockedMethod("updatePreparedStatementForCreate") .createStrictMock(); EasyMock.expect(connection.prepareStatement(SavedReviewerSearchDAO.CREATE_SQL)) .andReturn(preparedStatement); dao.updatePreparedStatementForCreate(preparedStatement, search); EasyMock.expectLastCall(); EasyMock.expect(preparedStatement.executeQuery()).andReturn(resultSet); EasyMock.expect(resultSet.next()).andReturn(true); EasyMock.expect(resultSet.getTimestamp(1)).andReturn(new Timestamp(date.getTime())); resultSet.close(); EasyMock.expectLastCall(); preparedStatement.close(); EasyMock.replay(preparedStatement, resultSet, connection, dao); SavedReviewerSearch result = dao.create(connection, search); EasyMock.verify(preparedStatement, resultSet, connection, dao); assertEquals("Wrong created date.", date, result.getCreatedDate()); // failure case EasyMock.reset(preparedStatement, resultSet, connection, dao); EasyMock.expect(connection.prepareStatement(SavedReviewerSearchDAO.CREATE_SQL)) .andReturn(preparedStatement); dao.updatePreparedStatementForCreate(preparedStatement, search); EasyMock.expectLastCall(); EasyMock.expect(preparedStatement.executeQuery()).andReturn(resultSet); EasyMock.expect(resultSet.next()).andReturn(false); resultSet.close(); EasyMock.expectLastCall(); preparedStatement.close(); EasyMock.replay(preparedStatement, resultSet, connection, dao); try { dao.create(connection, search); fail("Should have thrown a sql exception."); } catch (SQLException ex) { // expected } EasyMock.verify(preparedStatement, resultSet, connection, dao); }
// metode updateType atjauno informāciju par tipiem. // Nosaka tipiem norādīto procesu laikus un tos sasummējot ieraksta Process_Type tabulā public static void updateType() { try { Statement stmt = connect.createStatement(); ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM PROCESS_TYPE;"); int counter = 0; // while ( rs.next() ) // { counter = rs.getInt("COUNT(*)"); // } rs.close(); rs = stmt.executeQuery( "SELECT Type_ID, SUM(COUNT_DAY), SUM(COUNT_ALL) FROM PROCESS GROUP BY Type_ID;"); int[] id = new int[counter]; int[] count_day = new int[counter]; int[] count_all = new int[counter]; int i = 0; while (rs.next()) { id[i] = rs.getInt("Type_ID"); count_day[i] = rs.getInt("SUM(COUNT_DAY)"); count_all[i] = rs.getInt("SUM(COUNT_ALL)"); i++; } rs.close(); stmt.executeUpdate("UPDATE PROCESS_TYPE SET Type_Count_Day=0, Type_Count_All=0;"); for (int k = 0; k < counter; k++) { stmt.executeUpdate( "UPDATE PROCESS_TYPE SET Type_Count_Day = '" + count_day[k] + "',Type_Count_All = '" + count_all[k] + "' WHERE PROCESS_TYPE.PT_ID = '" + id[k] + "';"); } stmt.close(); // System.out.println("Type count updated"); } catch (Exception e) { JOptionPane.showMessageDialog( null, e.getClass().getName() + ": " + e.getMessage(), "Error", JOptionPane.ERROR_MESSAGE); // System.exit(0); } }
public void updateScore(List<Player> players) { try { if (connection == null || players.size() <= 0) { return; } List<String> playersName = new ArrayList<String>(); for (Player player : players) { playersName.add("'" + player.getName() + "'"); } String arrs = join(playersName, ","); String strSql = "SELECT * FROM player WHERE name IN (" + arrs + ")"; cacheScores.clear(); Map<String, Object> map; Statement sql = connection.createStatement(); ResultSet result = sql.executeQuery(strSql); while (result.next()) { map = new HashMap<String, Object>(); map.put("kills", result.getInt("kills")); map.put("deaths", result.getInt("deaths")); map.put("mobs", result.getInt("mobs")); map.put("prefix", result.getString("prefix")); cacheScores.put(result.getString("name"), map); } result.close(); sql.close(); } catch (Exception e) { e.printStackTrace(); } }
@Override public Emp get(Integer objectId) { if (conn == null) { System.err.println("No connect!!"); return null; } Emp emp = null; try { final String sql = "SELECT " + "id,lname,fname,mname,d_hire,d_fire " + "FROM DDT_EMP" + " WHERE id = ?"; PreparedStatement st = conn.prepareStatement(sql); st.setInt(1, objectId.intValue()); ResultSet rs = st.executeQuery(); while (rs.next()) { emp = new Emp(); emp.setId(rs.getInt("id")); emp.setLastName(rs.getString("lname")); emp.setFirstName(rs.getString("fname")); emp.setMiddleName(rs.getString("mname")); emp.setHireDate(rs.getDate("d_hire")); emp.setFireDate(rs.getDate("d_fire")); } rs.close(); } catch (SQLException e) { e.printStackTrace(); } return emp; }