@Override public Set<EmpVO> getEmpsByDeptno(Integer deptno) { Set<EmpVO> set = new LinkedHashSet<EmpVO>(); EmpVO empVO = null; Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; try { Class.forName(driver); con = DriverManager.getConnection(url, userid, passwd); pstmt = con.prepareStatement(GET_Emps_ByDeptno_STMT); pstmt.setInt(1, deptno); rs = pstmt.executeQuery(); while (rs.next()) { empVO = new EmpVO(); empVO.setEmpno(rs.getInt("empno")); empVO.setEname(rs.getString("ename")); empVO.setJob(rs.getString("job")); empVO.setHiredate(rs.getDate("hiredate")); empVO.setSal(rs.getDouble("sal")); empVO.setComm(rs.getDouble("comm")); empVO.setDeptno(rs.getInt("deptno")); set.add(empVO); // Store the row in the vector } // Handle any driver errors } catch (ClassNotFoundException e) { throw new RuntimeException("Couldn't load database driver. " + e.getMessage()); // Handle any SQL errors } catch (SQLException se) { throw new RuntimeException("A database error occured. " + se.getMessage()); } finally { if (rs != null) { try { rs.close(); } catch (SQLException se) { se.printStackTrace(System.err); } } if (pstmt != null) { try { pstmt.close(); } catch (SQLException se) { se.printStackTrace(System.err); } } if (con != null) { try { con.close(); } catch (Exception e) { e.printStackTrace(System.err); } } } return set; }
@WebMethod public List<Asset> getFasilitasAset(int idAset) throws SQLException { String query = "SELECT * FROM " + FASILITAS_ASET_TABLE + " JOIN " + ASSET_TABLE + " ON " + FASILITAS_ASET_TABLE + ".id_fasilitas=" + ASSET_TABLE + ".id" + " WHERE id_aset = " + idAset; System.out.println(query); ResultSet rs = executeQuery(query); List<Asset> assets = null; while (rs.next()) { if (rs.isFirst()) assets = new ArrayList<Asset>(); Asset asset = new Asset(); asset.setId(rs.getInt("id")); asset.setNama(rs.getString("nama")); asset.setKategori(rs.getString("kategori")); asset.setKategori(rs.getString("jenis")); asset.setTanggalMasuk(rs.getDate("tanggal_masuk")); asset.setKondisi(AssetCondition.getInstance(rs.getString("kondisi"))); asset.setPemilik(rs.getString("pemilik")); asset.setVendor(getVendorAset(rs.getInt("id_vendor"))); asset.setHarga(rs.getString("harga")); asset.setPublicAsset(rs.getBoolean("is_public")); assets.add(asset); } return assets; }
public ArrayList<airschedule> getAirschedule() { connect(); ArrayList<airschedule> list = new ArrayList<airschedule>(); try { pstmt = con.prepareStatement(SQL); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { airschedule s = new airschedule(); s.setSchedule_id(rs.getInt("schedule_id")); s.setDeparture_date(rs.getDate("departure_date")); s.setArrival_date(rs.getDate("arrival_date")); s.setDeparture_time(rs.getTime("departure_time")); s.setArrival_time(rs.getTime("arrival_time")); s.setAirline_name(rs.getString("airline_name")); result++; list.add(s); } this.setResult(result); rs.close(); } catch (SQLException e) { e.printStackTrace(); } finally { disconnect(); } return list; }
@Test public void testUpsertDateValues() throws Exception { long ts = nextTimestamp(); Date now = new Date(System.currentTimeMillis()); ensureTableCreated(getUrl(), TestUtil.PTSDB_NAME, null, ts - 2); Properties props = new Properties(); props.setProperty( PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1 Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props); String dateString = "1999-01-01 02:00:00"; PreparedStatement upsertStmt = conn.prepareStatement( "upsert into ptsdb(inst,host,date) values('aaa','bbb',to_date('" + dateString + "'))"); int rowsInserted = upsertStmt.executeUpdate(); assertEquals(1, rowsInserted); upsertStmt = conn.prepareStatement( "upsert into ptsdb(inst,host,date) values('ccc','ddd',current_date())"); rowsInserted = upsertStmt.executeUpdate(); assertEquals(1, rowsInserted); conn.commit(); props.setProperty( PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1 conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props); String select = "SELECT date,current_date() FROM ptsdb"; ResultSet rs = conn.createStatement().executeQuery(select); Date then = new Date(System.currentTimeMillis()); assertTrue(rs.next()); Date date = DateUtil.parseDate(dateString); assertEquals(date, rs.getDate(1)); assertTrue(rs.next()); assertTrue(rs.getDate(1).after(now) && rs.getDate(1).before(then)); assertFalse(rs.next()); }
@Override public Project get(int id) { Project result = null; try (Connection connection = dbDataSourceProvider.getConnection()) { try (PreparedStatement statement = connection.prepareStatement(sqlProvider.get4Load())) { statement.setInt(1, id); ResultSet resultSet = statement.executeQuery(); if (resultSet.next()) { result = new Project( resultSet.getString("name"), resultSet.getInt("goal"), resultSet.getDate("deadline_date")); result.setShortDescription(resultSet.getString("description")); result.setBalance(resultSet.getInt("balance")); result.setDemoLink(resultSet.getString("demo_link")); result.setCreateDate(resultSet.getDate("create_date")); result.setId(id); } } connection.commit(); } catch (SQLException e) { throw new RuntimeException(e); } return result; }
private EventRequestBean getFromResultSet(ResultSet resultSet) throws SQLException { EventRequestBean eventRequest; String id = resultSet.getString(1); eventRequest = new EventRequestBean(id); String name = resultSet.getString(2); eventRequest.setName(name); int clientId = resultSet.getInt(3); eventRequest.setClientId(clientId); String type = resultSet.getString(4); eventRequest.setType(type); Date from = resultSet.getDate(5); eventRequest.setFrom(from); Date to = resultSet.getDate(6); eventRequest.setTo(to); String budget = resultSet.getString(7); eventRequest.setBudget(budget); String status = resultSet.getString(8); eventRequest.setStatus(status); Map<String, String> preferences = eventRequest.getPreferences(); preferences.put("details_photo_video", resultSet.getNString(9)); preferences.put("details_music", resultSet.getNString(10)); preferences.put("details_poster", resultSet.getNString(11)); preferences.put("details_decoration", resultSet.getNString(12)); preferences.put("details_network", resultSet.getNString(13)); preferences.put("details_food_beverages", resultSet.getNString(14)); preferences.put("details_waiter", resultSet.getNString(15)); preferences.put("feedback_budget", resultSet.getNString(16)); return eventRequest; }
private void verifyDateResultSet(ResultSet rs, Date date, int rowCount) throws SQLException { for (int i = 0; i < rowCount; i++) { assertTrue(rs.next()); if (tgtPH()) assertEquals(date, rs.getDate(1)); else if (tgtSQ() || tgtTR()) assertEquals(date.toString(), rs.getDate(1).toString()); } assertFalse(rs.next()); }
public JobProtheus retornaJob(Connection conn, JobLote lote) throws SQLException { ResultSet rs = null; PreparedStatement stmt = null; JobProtheus job = null; String sql = "select job , B1_DESC, operacao , produto, "; sql += " dt_release, job_start_date , qtd_release, setor, qtd_transf "; sql += " from job left join " + DB_PROTHEUS.trim() + ".dbo.SB1000 SB1 "; sql += " on(produto collate SQL_Latin1_General_CP1_CI_AS = B1_COD) "; sql += " where job = ? and operacao = ? and SB1.D_E_L_E_T_ = '' "; sql += " order by dt_release, produto "; stmt = conn.prepareStatement(sql); stmt.setString(1, lote.getJob().trim()); stmt.setInt(2, lote.getOperNum()); rs = stmt.executeQuery(); while (rs.next()) { String nJob = rs.getString("job"); int operacao = rs.getInt("operacao"); String produto = rs.getString("produto"); Date dataEmissao = rs.getDate("dt_release"); Date dataPrevisaoInicio = rs.getDate("job_start_date"); String descricaoProduto = rs.getString("B1_DESC"); double quantidade = rs.getDouble("qtd_release"); String wc = rs.getString("setor"); String emissao = dataEmissao != null ? getDateToString(dataEmissao) : ""; String previsaoInicio = dataPrevisaoInicio != null ? getDateToString(dataPrevisaoInicio) : ""; job = new JobProtheus(); job.setStatus(""); job.setJob(nJob); job.setOperacao(operacao); job.setProduto(produto.trim()); job.setDataEmissao(emissao); job.setQuantidadeLiberada(quantidade); job.setDescricaoProduto(descricaoProduto); job.setCentroTrabalho(wc); double qtdTotal = retornaQtdTotal(conn, job); // calcula o total transferido para o job job.setQuantidadeCompleta(qtdTotal); job.setDataPrivisaoInicio(previsaoInicio); job.setQuantidadeFaltando(quantidade - qtdTotal); // seta o valor qtd faltando } return job; // retorna lista de jobs }
/** * Zeige die Daten aller Spiele an, die am ersten Spieltag aller drei Ligen nach 17 Uhr begonnen * haben. * * @param db Hostadresse der Datenbank * @param user Benutzername für Datenbank * @param password Passwort für Datenbank * @throws SQLException Exception für Datenbankzugriffe benötigt * @throws ClassNotFoundException Exception für Postgres-Verbindung benötigt * @see #connect(String, String, String) * @see #disconnect() */ public static void select3(String db, String user, String password) throws SQLException, ClassNotFoundException { connect(db, user, password); System.out.println("Spiele am ersten Spieltag nach 17 Uhr:"); ResultSet rs = stmt.executeQuery("SELECT * FROM bl.spiel WHERE spieltag = 1 AND uhrzeit > '17:00:00';"); while (rs.next()) { int spiel_id = rs.getInt("spiel_id"); int spieltag = rs.getInt("spieltag"); Date datum = rs.getDate("datum"); Time uhrzeit = rs.getTime("uhrzeit"); int heim = rs.getInt("heim"); int gast = rs.getInt("gast"); int tore_heim = rs.getInt("tore_heim"); int tore_gast = rs.getInt("tore_gast"); System.out.print("ID: " + spiel_id + "; \t"); System.out.print("Spieltag: " + spieltag + ";\t"); System.out.print("Datum: " + datum + ";\t"); System.out.print("Uhrzeit: " + uhrzeit + ";\t"); System.out.print("Heim: " + heim + ";\t"); System.out.print("Gast: " + gast + ";\t"); System.out.print("Tore Heim: " + tore_heim + ";\t"); System.out.print("Tore Gast: " + tore_gast + ";"); System.out.println(); } rs.close(); System.out.println(); disconnect(); }
@Override public List<MstGasto> read() { Connection cn; PreparedStatement pst; ResultSet rs; String sql; List<MstGasto> lst = new ArrayList(); try { Class.forName(bd.getDriver()); cn = DriverManager.getConnection(bd.getUrl(), bd.getUser(), bd.getPasswd()); sql = "select * from mst_tipo_gastos order by corr_gasto"; pst = cn.prepareStatement(sql); rs = pst.executeQuery(); while (rs.next()) { lst.add( new MstGasto( rs.getInt("cod_residencial"), rs.getInt("corr_gasto"), rs.getString("desc_gasto"), rs.getString("cod_cta_conta"), rs.getDouble("valor_gasto"), rs.getDate("fecha_creacion"), rs.getString("cod_usuario"), rs.getString("activo"))); } rs.close(); pst.close(); cn.close(); } catch (SQLException e) { log.severe(e.toString()); } catch (Exception e) { log.severe(e.toString()); } return lst; }
private Vector getNextRow(ResultSet rs, ResultSetMetaData rsmd) throws SQLException { Vector currentRow = new Vector(); for (int i = 1; i <= rsmd.getColumnCount(); ++i) switch (rsmd.getColumnType(i)) { case Types.VARCHAR: currentRow.addElement(rs.getString(i)); break; case Types.INTEGER: currentRow.addElement(new Long(rs.getLong(i))); break; case Types.DATE: currentRow.addElement(rs.getDate(i)); break; case Types.FLOAT: currentRow.addElement(rs.getFloat(i)); break; case Types.CHAR: currentRow.addElement(rs.getString(i)); break; case Types.REAL: currentRow.addElement(rs.getFloat(i)); break; default: System.out.println("Type was: " + rsmd.getColumnTypeName(i)); } return currentRow; }
private ServicePrice constructPriceEntity(ResultSet rs) throws SQLException { ServicePrice servicePrice = new ServicePrice(); servicePrice.setPrice(rs.getBigDecimal("price")); servicePrice.setServiceId(rs.getInt("service_id")); servicePrice.setDate(rs.getDate("date").toLocalDate()); return servicePrice; }
public static Card getCard(Connection con, String card_id) throws Exception { Card cardObj = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = null; sql = "select valid,a.card_id,a.regdate, a.card_no, a.member_id, a.card_class, a.op_type, a.moneyPaid, paidOfPro, con_no, class_name, markup " + " from t_card a " + " where a.card_id=?"; pstmt = con.prepareStatement(sql); pstmt.setString(1, card_id); rs = pstmt.executeQuery(); if (rs.next()) { cardObj = new Card(); cardObj.card_id = rs.getString("card_id"); cardObj.regdate = rs.getDate("regdate"); cardObj.card_no = rs.getString("card_no"); cardObj.member_id = rs.getString("member_id"); cardObj.card_class = rs.getString("card_class"); cardObj.op_type = rs.getInt("op_type"); cardObj.setMoneypaid(JUtil.convertNull(rs.getString("moneyPaid"))); cardObj.paidofpro = JUtil.convertNull(rs.getString("paidOfPro")); cardObj.con_no = JUtil.convertNull(rs.getString("con_no")); cardObj.class_name = JUtil.convertNull(rs.getString("class_name")); cardObj.markup = JUtil.convertNull(rs.getString("markup")); cardObj.valid = rs.getInt("valid"); } rs.close(); pstmt.close(); return cardObj; }
@Override public List<Ordine> listaOrdini() throws PersistenceException { // ok Connection connection = this.datasource.getConnection(); PreparedStatement statement = null; List<Ordine> ordini = null; Ordine ordine = null; try { String str = "select ordini.codice as codice,stato,data,cliente,id " + "from ordini left outer join clienti on ordini.cliente=clienti.codice"; statement = connection.prepareStatement(str); ResultSet result = statement.executeQuery(); ordini = new ArrayList<Ordine>(); while (result.next()) { ordine = new Ordine(); ClienteDAOImpl cliente = new ClienteDAOImpl(); ordine.setCliente(cliente.getClienteById(result.getInt("cliente"))); ordine.setCodiceOrdine(result.getString("codice")); ordine.setData(new java.util.Date(result.getDate("data").getDate())); ordine.setStato(result.getString("stato")); ordine.setId(result.getInt("id")); ordini.add(ordine); } } catch (SQLException e) { throw new PersistenceException(e.getMessage()); } finally { try { if (statement != null) statement.close(); if (connection != null) connection.close(); } catch (SQLException e) { throw new PersistenceException(e.getMessage()); } } return ordini; }
@Override public ArrayList<Emp> getList() { ArrayList<Emp> emps = new ArrayList<Emp>(100); if (conn == null) { System.err.println("No connect!!"); return emps; } try { Statement st = conn.createStatement(); ResultSet rs = st.executeQuery( "" + "SELECT e.id,e.lname,e.fname,e.mname,e.d_hire,e.d_fire " + "FROM DDT_EMP AS e"); while (rs.next()) { Emp 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")); emps.add(emp); } rs.close(); } catch (SQLException e) { e.printStackTrace(); } return emps; }
public static void fillTable(String aQuery, JTable aTable, Connector aConnector) throws SQLException { ((DefaultTableModel) aTable.getModel()).setRowCount(0); ((DefaultTableModel) aTable.getModel()).setColumnCount(0); ResultSet results = aConnector.sendQuery(aQuery); ResultSetMetaData metadata = results.getMetaData(); cols = metadata.getColumnCount(); rowData = new Object[cols]; colNames = new String[cols]; for (int i = 0; i < cols; i++) { colNames[i] = metadata.getColumnName(i + 1); } for (int i = 0; i < cols; i++) { ((DefaultTableModel) aTable.getModel()).addColumn(colNames[i]); } while (results.next()) { for (int i = 0; i < cols; i++) { if (metadata.getColumnName(i + 1).equalsIgnoreCase("DatePosted")) { rowData[i] = StrVal.SqlStringToString(results.getDate(i + 1).toString()); rowData[i] += " || "; rowData[i] += results.getTime(i + 1).toString(); } else rowData[i] = results.getString(i + 1); } ((DefaultTableModel) aTable.getModel()).addRow(rowData); } }
@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; }
private boolean validate(int type, String columnName, ResultSet resultSet0, ResultSet resultSet1) throws SQLException { switch (type) { case Types.DATE: return resultSet0.getDate(columnName).equals(resultSet1.getDate(columnName)); case Types.BOOLEAN: return resultSet0.getBoolean(columnName) == resultSet1.getBoolean(columnName); case Types.BIGINT: return resultSet0.getLong(columnName) == resultSet1.getLong(columnName); case Types.INTEGER: return resultSet0.getInt(columnName) == resultSet1.getInt(columnName); case Types.VARCHAR: default: return resultSet0.getString(columnName).equals(resultSet1.getString(columnName)); } }
@Override public List<Ordine> getOrdiniPerCliente(Cliente cliente) throws PersistenceException { // ok Connection connection = this.datasource.getConnection(); PreparedStatement statement = null; Ordine o = null; List<Ordine> lo = new LinkedList<Ordine>(); try { String str = "select codice,data,stato,id from ordini where cliente=?"; statement = connection.prepareStatement(str); statement.setInt(1, cliente.getId()); ResultSet result = statement.executeQuery(); while (result.next()) { o = new Ordine(); o.setCliente(cliente); o.setCodiceOrdine(result.getString("codice")); o.setData(result.getDate("data")); o.setStato(result.getString("stato")); o.setId(result.getInt("id")); lo.add(o); // da completare per restituire ordine completo di righe } } catch (SQLException e) { throw new PersistenceException(e.getMessage()); } finally { try { if (statement != null) statement.close(); if (connection != null) connection.close(); } catch (SQLException e) { throw new PersistenceException(e.getMessage()); } } return lo; }
/** * Method to build a vector from a ResultSet * * @param <code>ResultSet rsResult</code> the result from a query * @return Vector of objects from the ResultSet * @exception Throws Exception on error */ protected Vector buildObjectVector(ResultSet rsResult) throws Exception { // vector for return data Vector vReturn = null; try { // init the vector vReturn = new Vector(super.VECT_INIT_SIZE, super.VECT_GROW_SIZE); // loop through the entire result set while (rsResult.next()) { // create a new one StageUsacForm objTmp = new StageUsacForm(); // set the attributes // ADDING A NEW FIELD "ROWID" objTmp.ROWID = rsResult.getString("ROWID"); objTmp.HDR_SPIN = rsResult.getLong("HDR_SPIN"); objTmp.SPIN_NM = rsResult.getString("SPIN_NM"); objTmp.RCPNT_EMAIL = rsResult.getString("RCPNT_EMAIL"); objTmp.USAC_EMAIL = rsResult.getString("USAC_EMAIL"); objTmp.RFRNC_NMBR = rsResult.getString("RFRNC_NMBR"); objTmp.RCRD_CNT = rsResult.getLong("RCRD_CNT"); objTmp.TOT_PAYMENT = rsResult.getDouble("TOT_PAYMENT"); objTmp.USAC_PRCS_DAT = rsResult.getDate("USAC_PRCS_DAT"); objTmp.RTRCT_FLAG = rsResult.getString("RTRCT_FLAG"); objTmp.DTL_SPIN = rsResult.getLong("DTL_SPIN"); objTmp.FRN = rsResult.getLong("FRN"); objTmp.SDC_INV_NO = rsResult.getString("SDC_INV_NO"); objTmp.AMT_PAID = rsResult.getDouble("AMT_PAID"); objTmp.DSBRSMNT_TXT = rsResult.getString("DSBRSMNT_TXT"); objTmp.EMAIL_DATE = rsResult.getDate("EMAIL_DATE"); objTmp.FILENAME = rsResult.getString("FILENAME"); objTmp.PROCESS_DATE = rsResult.getDate("PROCESS_DATE"); objTmp.STATUS = rsResult.getLong("STATUS"); // add it to the vector vReturn.addElement(objTmp); } return vReturn; } catch (Exception e) { throw new Exception("BuildObjectVector()\n" + e.getMessage()); } }
private String handleDate(ResultSet rs, int columnIndex) throws SQLException { java.sql.Date date = rs.getDate(columnIndex); String value = null; if (date != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy"); value = dateFormat.format(date); } return value; }
@SuppressWarnings("deprecation") @Test public void date() throws SQLException { connectWithJulianDayModeActivated(); Date d1 = new Date(System.currentTimeMillis()); stat.execute("create table t (c1);"); PreparedStatement prep = conn.prepareStatement("insert into t values(?);"); prep.setDate(1, d1); prep.executeUpdate(); ResultSet rs = stat.executeQuery("select c1 from t;"); assertTrue(rs.next()); assertEquals(d1.getYear(), rs.getDate(1).getYear()); assertEquals(d1.getMonth(), rs.getDate(1).getMonth()); assertEquals(d1.getDay(), rs.getDate(1).getDay()); rs.close(); }
/** * aggregation - group by * * @throws Exception */ @Test public void testDateGroupBy() throws Exception { printTestDescription(); Date date = new Date(1); initATableValues(date); try { ResultSet rs; String query = null; if (tgtPH()) query = "SELECT a_date, count(1) FROM atable WHERE organization_id='" + tenantId + "' group by a_date"; else if (tgtSQ() || tgtTR()) query = "SELECT a_date, count(1) FROM atable WHERE organization_id='" + tenantId + "' group by a_date order by 1"; rs = getResultSet(conn, query); /* 3 rows in expected result: * 1969-12-31 3 * 1970-01-01 3 * 1970-01-02 3 * */ assertTrue(rs.next()); if (tgtPH()) assertEquals(date, rs.getDate(1)); else if (tgtSQ() || tgtTR()) assertEquals(date.toString(), rs.getDate(1).toString()); assertEquals(3, rs.getInt(2)); // the following assertions fails assertTrue(rs.next()); assertEquals(3, rs.getInt(2)); assertTrue(rs.next()); assertEquals(3, rs.getInt(2)); assertFalse(rs.next()); } finally { } }
@Test public void date1() throws SQLException { Date d1 = new Date(987654321); stat.execute("create table t (c1);"); PreparedStatement prep = conn.prepareStatement("insert into t values(?);"); prep.setDate(1, d1); prep.executeUpdate(); prep.setDate(1, null); prep.executeUpdate(); ResultSet rs = stat.executeQuery("select c1 from t;"); assertTrue(rs.next()); assertEquals(d1.getTime(), rs.getLong(1)); assertTrue(rs.getDate(1).equals(d1)); assertTrue(rs.next()); assertEquals(null, rs.getDate(1)); rs.close(); }
// ---------------------------------------------------------------// public ArrayList<Project> selectAllProjects() throws SQLException { Connection conn = null; PreparedStatement prepStmt = null; ArrayList<Project> rtrn = null; try { conn = select(); String sql = "SELECT pr.*,cst.CUST_NAME FROM PROJECTS pr,CUSTOMERS cst WHERE cst.CUST_ID=pr.CUST_ID"; prepStmt = conn.prepareStatement(sql); ResultSet rs = prepStmt.executeQuery(); rtrn = new ArrayList<Project>(); while (rs.next()) { Project proj = new Project( rs.getInt("PROJ_ID"), rs.getString("PROJ_NAME"), rs.getInt("CUST_ID"), rs.getString("PROJ_TYPE"), rs.getDate("PROJ_FROM"), rs.getDate("PROJ_TO"), rs.getDate("PROJ_DEADLINE"), (rs.getString("PROJ_ACTIVE")).equals("Y"), rs.getFloat("PROJ_BUDGET"), rs.getString("PROJ_DESCRIPTION"), rs.getTimestamp("INSERTED_AT"), rs.getString("INSERTED_BY"), rs.getTimestamp("MODIFIED_AT"), rs.getString("MODIFIED_BY"), rs.getInt("ROWVERSION"), rs.getInt("PAR_PROJ_ID"), rs.getString("CUST_NAME")); rtrn.add(proj); } rs.close(); } finally { if (prepStmt != null) conn.close(); if (conn != null) conn.close(); } return rtrn; }
public long getDate(int n) { try { ResultSet rs = getResultSet(); if (rs == null) return 0; Date ts = rs.getDate(n); return ts.getTime(); } catch (Exception e) { Debug.warning(e); } return 0; }
public ArrayList<Listing> getAllListing() throws SQLException { start(); rs = stmt.executeQuery("SELECT * FROM listingInfo"); ArrayList<Listing> rtrn = new ArrayList<Listing>(); while (rs.next()) { Listing temp = new Listing(); temp.setUserName(rs.getString(2)); temp.setRacquetID(rs.getInt(3)); temp.setPrice(rs.getDouble(4)); temp.setNewOrOld(rs.getString(5)); temp.setDateListed(rs.getDate(6)); temp.setDateSold(rs.getDate(7)); // temp.setSellerRating(rs.getInt(8)); temp.setDescription(rs.getString(9)); temp.setPicURL(rs.getString(10)); rtrn.add(temp); } close(); return rtrn; }
/** * An welchem Tag fand das erste Spiel der Saison statt? * * @param db Hostadresse der Datenbank * @param user Benutzername für Datenbank * @param password Passwort für Datenbank * @throws SQLException Exception für Datenbankzugriffe benötigt * @throws ClassNotFoundException Exception für Postgres-Verbindung benötigt * @see #connect(String, String, String) * @see #disconnect() */ public static void select1(String db, String user, String password) throws SQLException, ClassNotFoundException { connect(db, user, password); System.out.println("Das erste Spiel der Saison:"); ResultSet rs = stmt.executeQuery("SELECT datum FROM bl.spiel ORDER BY datum ASC LIMIT 1;"); while (rs.next()) { Date datum = rs.getDate("datum"); System.out.println("Das erste Spiel der Saison fand statt am " + datum); } rs.close(); System.out.println(); disconnect(); }
@Test public void testAjoutCreneau() throws SQLException, IOException, ClassNotFoundException { Connection connection = BddConnecteur.getConnection(); BddUtilisateur.ajout("*****@*****.**", "labri"); BddLabo.ajout(1, "Labri"); BddAtelier.ajoutAtelier( 1, "A la poursuite d'ennemis invisibles", "Sciences de la vie ", "Campus Carreire (Hôpital Pellegrin)", "Labo MFP", "", "", "", ""); // Insertion int idAtelier = 1; String jour = "2015-12-23"; String heure = "13:00"; int capacite = 20; BddCreneau.ajoutCreneau(1, jour, heure, capacite); // Vérification String sql = "SELECT * FROM Creneau WHERE idAtelier='" + idAtelier + "'"; Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery(sql); int count = 0; Date jourRecup; Time heureRecup; int capaciteRecup = 0; while (rs.next()) { jourRecup = rs.getDate("jour"); heureRecup = rs.getTime("heure"); capaciteRecup = rs.getInt("capacite"); count++; } assertEquals(count, 1); // assertEquals(jourRecup, jour); // assertEquals(heureRecup, heure); assertEquals(capaciteRecup, capacite); // Fermeture rs.close(); statement.close(); connection.close(); }
// ---------------------------------------------------------------// public ArrayList<Task> selectTaskforProjID(int projID) throws SQLException { Connection conn = null; PreparedStatement prepStmt = null; ArrayList<Task> rtrn = null; try { conn = select(); String sql = "SELECT * FROM TASKS WHERE PROJ_ID=?"; prepStmt = conn.prepareStatement(sql); prepStmt.setInt(1, projID); ResultSet rs = prepStmt.executeQuery(); rtrn = new ArrayList<Task>(); while (rs.next()) { Task task = new Task( rs.getInt("TASK_ID"), rs.getInt("PROJ_ID"), rs.getString("TASK_DESCRIPTION"), rs.getString("TASK_NOTES"), rs.getDate("TASK_DEADLINE"), rs.getDate("TASK_FROM"), rs.getDate("TASK_TO"), rs.getString("TASK_ACTIVE").equals("Y"), rs.getString("TASK_TYPE"), rs.getString("TASK_USER_NOTES"), rs.getInt("ROWVERSION"), rs.getTimestamp("INSERTED_AT"), rs.getTimestamp("MODIFIED_AT"), rs.getString("INSERTED_BY"), rs.getString("MODIFIED_BY")); rtrn.add(task); } rs.close(); } finally { if (prepStmt != null) conn.close(); if (conn != null) conn.close(); } return rtrn; }