public void assign(ResultSet rs) throws SQLException { currencyid = rs.getLong("currencyid"); facilityid = rs.getLong("facilityid"); ratevts = rs.getLong("ratevts"); ratexts = rs.getLong("ratexts"); rateyts = rs.getLong("rateyts"); ratezts = rs.getLong("ratezts"); tariffid = rs.getLong("tariffid"); vendorid = rs.getLong("vendorid"); vendorvts = rs.getLong("vendorvts"); vendorxts = rs.getLong("vendorxts"); vendoryts = rs.getLong("vendoryts"); vendorzts = rs.getLong("vendorzts"); handlingisperc = rs.getBoolean("handlingisperc"); othersisperc = rs.getBoolean("othersisperc"); specialisperc = rs.getBoolean("specialisperc"); specialrate = rs.getLong("specialrate"); surcharge1isperc = rs.getBoolean("surcharge1isperc"); surcharge1rate = rs.getLong("surcharge1rate"); surcharge2isperc = rs.getBoolean("surcharge2isperc"); surcharge2rate = rs.getLong("surcharge2rate"); currencyname = rs.getString("currencyname"); departure = rs.getLong("departure"); handlingrate = rs.getLong("handlingrate"); othersrate = rs.getLong("othersrate"); portid = rs.getLong("portid"); surcharge1name = rs.getString("surcharge1name"); surcharge2name = rs.getString("surcharge2name"); terminalid = rs.getLong("terminalid"); vendorname = rs.getString("vendorname"); vendorpriority = rs.getString("vendorpriority"); durationfrom = rs.getDate("durationfrom"); durationto = rs.getDate("durationto"); transittimedays = rs.getLong("transittimedays"); }
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; }
public static Transfer getTransfer(int playerId) { int teamId = HOVerwaltung.instance().getModel().getBasics().getTeamId(); String query = "Select price, BUYERID, sellerid, date from transfer WHERE PLAYERID=" + playerId + " and (BUYERID=" + teamId + " OR SELLERID=" + teamId + ")"; ResultSet rs = DBManager.instance().getAdapter().executeQuery(query); Transfer t = new Transfer(); try { while (rs.next()) { // TODO player might by bought/sold multiple times by the same team if (rs.getInt("BUYERID") == teamId) { t.purchasePrice = rs.getInt("price"); t.purchaseDate = rs.getDate("date"); } if (rs.getInt("sellerid") == teamId) { t.sellingPrice = rs.getInt("price"); t.sellingDate = rs.getDate("date"); } } } catch (SQLException e) { e.printStackTrace(); } return t; }
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; }
/** * query one notification according to id * * @param id the id of the wanted notification * @return an object of Notification you wanted */ public Notification queryOne(String id) { if (DBConnection.conn == null) { DBConnection.openConn(); } Notification notification = new Notification(); try { String sql = "select * from CorrectionNotification where id =?"; ps = DBConnection.conn.prepareStatement(sql); ps.setString(1, id); rs = ps.executeQuery(); while (rs.next()) { notification.setId(rs.getString(1)); notification.setTitle(rs.getString(2)); notification.setContent(rs.getString(3)); notification.setPublishDept(rs.getString(4)); notification.setAccordingTo(rs.getString(5)); notification.setHasImgs(rs.getInt(6)); notification.setHasVedio(rs.getInt(7)); notification.setHasText(rs.getInt(8)); notification.setPublishDate(rs.getDate(11)); notification.setDeadline(rs.getDate(12)); notification.setIsFeedback(rs.getInt(13)); notification.setConstructionName(rs.getString(14)); notification.setFeedbackId(rs.getString(15)); notification.setStatus(rs.getInt(16)); } DBConnection.closeResultSet(rs); DBConnection.closeStatement(ps); DBConnection.closeConn(); return notification; } catch (SQLException e) { e.printStackTrace(); return null; } }
@Override public void run() { String statement = "SELECT * FROM " + table + " WHERE name='" + syncingName + "'"; ResultSet rs = sql.executeQuery(statement); if (rs != null) { try { if (rs.getDate("modified") == null || rs.getDate("modified").before(Calendar.getInstance().getTime())) { // database is out of date setDBTime(); } else { // database is more recent data.set(syncingName, getDBTime(rs)); } } catch (SQLException e) { System.out.println("Playtimes.run"); System.out.println("SQLException: " + e.getMessage()); System.out.println("SQLState: " + e.getSQLState()); System.out.println("VendorError: " + e.getErrorCode()); } } else { // non existing entry setDBTime(); } }
/** Populates a DTO with data from a ResultSet */ protected void populateDto(Projects dto, ResultSet rs) throws SQLException { dto.setIdprojects(rs.getInt(COLUMN_IDPROJECTS)); dto.setName(rs.getString(COLUMN_NAME)); dto.setDescription(rs.getString(COLUMN_DESCRIPTION)); dto.setStartDate(rs.getDate(COLUMN_START_DATE)); dto.setEndDate(rs.getDate(COLUMN_END_DATE)); dto.setStatus(rs.getInt(COLUMN_STATUS)); if (rs.wasNull()) { dto.setStatusNull(true); } dto.setType(rs.getInt(COLUMN_TYPE)); if (rs.wasNull()) { dto.setTypeNull(true); } dto.setCompanyId(rs.getInt(COLUMN_COMPANY_ID)); if (rs.wasNull()) { dto.setCompanyIdNull(true); } dto.setBranchId(rs.getInt(COLUMN_BRANCH_ID)); if (rs.wasNull()) { dto.setBranchIdNull(true); } }
public List<ItemBacklogSprint> selectBacklogSprint(Sprint sprint) throws SQLException { List<ItemBacklogSprint> itens = new ArrayList<ItemBacklogSprint>(); PreparedStatement statement = getConn() .prepareStatement( "SELECT * FROM sprint_backlog" + " join backlog on id_backlog = idbacklog" + " join status on status = idstatus" + " where id_sprint = ? ORDER BY status ASC;"); statement.setLong(1, sprint.getId()); ResultSet result = statement.executeQuery(); while (result.next()) { ItemBacklogSprint item = new ItemBacklogSprint(); item.setId(result.getLong("idsprint_backlog")); item.setSprint(sprint); item.setItem(getItemBacklog(sprint.getProjeto(), result)); item.setStatus(getStatus(result)); if (result.getDate("data_concluido") != null) { item.setDataConclusao(new java.util.Date(result.getDate("data_concluido").getTime())); } itens.add(item); } return itens; }
public PersonnelDetails getPersonnelDetails(String personnelID) { PersonnelDetails pd = new PersonnelDetails(); PreparedStatement ps; ResultSet rs; if (dbc.connect()) { try { String sqlQuery = "select * from " + PERSONNEL_INFO + " where MaNV = '" + personnelID + "'"; ps = dbc.getConnection().prepareStatement(sqlQuery); rs = ps.executeQuery(); // retrieve data from db by PersonnelID while (rs.next()) { pd.setPersonnelID(rs.getString(1)); pd.setlName(rs.getString(2)); pd.setfName(rs.getString(3)); pd.setNickName(rs.getString(4)); pd.setStatusID(rs.getShort(5)); pd.setKindID(rs.getShort(6)); pd.setContractID(rs.getShort(7)); pd.setPosID(rs.getShort(8)); pd.setOfficeID(rs.getShort(9)); pd.setEnterDate(rs.getDate(10)); pd.setStartDate(rs.getDate(11)); pd.setSex(rs.getBoolean(12)); pd.setPhoneNum(rs.getString(13)); pd.setCompEmail(rs.getString(14)); pd.setEndTraining(rs.getDate(15)); pd.setPic(rs.getBytes(16)); pd.setBirth(rs.getDate(17)); pd.setPlaceOB(rs.getString(18)); pd.setRecentAdd(rs.getString(19)); pd.setFolkID(rs.getShort(20)); pd.setReligionID(rs.getShort(21)); pd.setNationID(rs.getShort(22)); pd.setPersonalEmail(rs.getString(23)); pd.setMariage(rs.getBoolean(24)); pd.setEducation(rs.getString(25)); pd.setAdd(rs.getString(26)); pd.setIDNum(rs.getString(27)); pd.setIDDay(rs.getDate(28)); pd.setRegionID(rs.getShort(29)); // System.out.println(rs.getShort(29)); pd.setBankCode(rs.getString(30)); pd.setBankID(rs.getShort(31)); pd.setIDPlace(rs.getString(32)); pd.setTaxCode(rs.getString(33)); } dbc.close(ps, rs); } catch (SQLException ex) { Logger.getLogger(PersonnelDAO.class.getName()).log(Level.SEVERE, null, ex); } } return pd; }
@Override public List<Booking> loadActiveBookingsByAccountId(Integer accountId) { Connection connect = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; // boolean isAuthenticated = false; List<Booking> bookings = new ArrayList<Booking>(); // Date currentDate = new Date(new java.util.Date().getTime()); try { connect = ConnectionPool.getPool().getConnection(); preparedStatement = connect.prepareStatement(SELECT_BOOKINGS_BY_ACCOUNT_ID); preparedStatement.setInt(1, accountId); // preparedStatement.setDate(2, currentDate); // preparedStatement.setDate(3, currentDate); resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { Booking booking = new Booking(); booking.setId(resultSet.getInt(BookingColumns.Id.getName())); booking.setRoomId(resultSet.getInt(BookingColumns.roomId.getName())); booking.setCheckinDate(resultSet.getDate(BookingColumns.checkinDate.getName())); booking.setCheckoutDate(resultSet.getDate(BookingColumns.checkoutDate.getName())); System.out.println(booking.getId()); System.out.println(booking.getRoomId()); System.out.println(booking.getCheckinDate()); System.out.println(booking.getCheckoutDate()); bookings.add(booking); } } catch (Exception e) { throw new DAOException(e); } finally { ConnectionPool.releaseConnection(connect, preparedStatement, resultSet); } return bookings; }
private CategoryDataset createDataset() { // row keys... final String series1 = "Closing Qty"; final String series2 = "Issued Qty"; final String series3 = "Received Qty"; // column keys... final DefaultCategoryDataset dataset = new DefaultCategoryDataset(); String dbUrl = "jdbc:mysql://localhost/ventura_enterpride"; String query = "Select * from daily_stock"; try { Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection(dbUrl, "root", "admin"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); while (rs.next()) { dataset.addValue(rs.getDouble(2), series1, rs.getDate(3) + "Item Id :" + rs.getInt(8)); dataset.addValue(rs.getDouble(4), series2, rs.getDate(3) + "Item Id :" + rs.getInt(8)); dataset.addValue(rs.getDouble(6), series3, rs.getDate(3) + "Item Id :" + rs.getInt(8)); } // end while con.close(); } // end try catch (Exception e) { e.printStackTrace(); } return dataset; }
public String execute() throws Exception { try { Class.forName("oracle.jdbc.driver.OracleDriver"); /*Establish a connection with a data source*/ Connection con1 = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "yash", "yash"); String str = "SELECT * FROM employee"; java.sql.Statement stmt = con1.createStatement(); ResultSet rs = stmt.executeQuery(str); while (rs.next()) { if (id.equals(rs.getString(1))) { name = rs.getString(2); gen = rs.getString(9); address = rs.getString(4); phone = rs.getString(5); email = rs.getString(6); salary = rs.getString(7); dob = rs.getDate(8); doj = rs.getDate(10); } } con1.close(); } catch (Exception e) { } return MANAGEEDIT; }
/** * 根据商品编码从表promotion_vip里查询出商品的会员价 * * @param connection 到数据库的连接 * @param code 商品编码 * @return 普通促销信息 * @throws SQLException */ public DiscCriteria getProm4Member(Connection connection, String code) throws SQLException { DiscCriteria result = null; String sql = " SELECT vgno, promlevel, promprice, " + " startdate, enddate, starttime, endtime " + " FROM promotion_vip where vgno=?; "; PreparedStatement stmt = connection.prepareStatement(sql); stmt.setString(1, code); ResultSet rs = stmt.executeQuery(); if (rs.next()) { String vgno = Formatter.mytrim(rs.getString("vgno")); int promlevel = rs.getInt("promlevel"); double promprice = rs.getDouble("promprice"); Date startdate = rs.getDate("startdate"); Date enddate = rs.getDate("enddate"); Date starttime = rs.getDate("starttime"); Date endtime = rs.getDate("endtime"); GregorianCalendar g_start = new GregorianCalendar(); g_start.setTime(starttime); GregorianCalendar g_end = new GregorianCalendar(); g_end.setTime(endtime); result = new Prom4Member(vgno, (int) (100 * promprice), promlevel, g_start, g_end); } return result; }
public DiscCriteria getDisc4MemberDept(Connection connection, String code) throws SQLException { DiscCriteria result = null; String sql = " SELECT DeptID, DiscLevel, DiscRate," + " StartTime, EndTime " + " FROM discDeptMember,price_lst where price_lst.vgno=? and price_lst.deptno=CONVERT(char(15),discDeptMember.DeptID); "; PreparedStatement stmt = connection.prepareStatement(sql); stmt.setString(1, code); ResultSet rs = stmt.executeQuery(); if (rs.next()) { int deptID = rs.getInt("DeptID"); int discLevel = rs.getInt("DiscLevel"); int discRate = rs.getInt("DiscRate"); Date starttime = rs.getDate("StartTime"); Date endtime = rs.getDate("EndTime"); GregorianCalendar g_start = new GregorianCalendar(); g_start.setTime(starttime); GregorianCalendar g_end = new GregorianCalendar(); g_end.setTime(endtime); result = new Disc4MemberDept(deptID, discLevel, discRate, g_start, g_end); } return result; }
protected static boolean isDay(ResultSet rs) throws Exception { GregorianCalendar current = new GregorianCalendar(); Date begin = rs.getDate("begin"); Date end = rs.getDate("end"); if (current.getTime().after(begin) && current.getTime().before(end)) { CronSubExpression dom = new CronSubExpression(rs.getString("dom"), "dom"); // Days of months are 1-31 if (dom.isValid(current.get(Calendar.DAY_OF_MONTH))) { CronSubExpression mon = new CronSubExpression(rs.getString("mon"), "mon"); // Months are 0-11 if (mon.isValid(current.get(Calendar.MONTH) + 1)) { CronSubExpression dow = new CronSubExpression(rs.getString("dow"), "dow"); // Sundays are 1, Saturdays 7 if (dow.isValid(current.get(Calendar.DAY_OF_WEEK))) { CronSubExpression year = new CronSubExpression(rs.getString("year"), "year"); // Year is exact if (year.isValid(current.get(Calendar.YEAR))) { return true; } } } } } return false; }
@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()); }
public void testCurrentDate() throws Exception { String selectSql = "SELECT current_date() FROM " + FULL_TABLE_NAME; try (Connection conn = DriverManager.getConnection(getUrl())) { conn.setAutoCommit(false); ResultSet rs = conn.createStatement().executeQuery(selectSql); assertFalse(rs.next()); String upsert = "UPSERT INTO " + FULL_TABLE_NAME + "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk) VALUES(?, ?, ?, ?, ?, ?)"; PreparedStatement stmt = conn.prepareStatement(upsert); // upsert two rows TestUtil.setRowKeyColumns(stmt, 1); stmt.execute(); conn.commit(); rs = conn.createStatement().executeQuery(selectSql); assertTrue(rs.next()); Date date1 = rs.getDate(1); assertFalse(rs.next()); Thread.sleep(1000); rs = conn.createStatement().executeQuery(selectSql); assertTrue(rs.next()); Date date2 = rs.getDate(1); assertFalse(rs.next()); assertTrue( "current_date() should change while executing multiple statements", date2.getTime() > date1.getTime()); } }
public static Vector<FinancialPredictionPerformanceItem> convertRStoFinancialPredictionPerformanceItems(ResultSet aRs) throws SQLException { Vector<FinancialPredictionPerformanceItem> resultList = new Vector<FinancialPredictionPerformanceItem>(); while (aRs.next()) { FinancialPredictionPerformanceItem item = new FinancialPredictionPerformanceItem(); item.prediction_data_item_id = aRs.getInt("prediction_data_item_id"); item.prediction_task_id = aRs.getInt("prediction_task_id"); item.company_name = aRs.getString("company_name"); item.predicton_type = aRs.getString("predicton_type"); item.date = aRs.getDate("date"); item.predicted_from_date = aRs.getDate("predicted_from_date"); item.actual_financial_change_value = aRs.getDouble("actual_financial_change_value"); item.actual_financial_change_percent = aRs.getDouble("actual_financial_change_percent"); item.actual_financial_direction = aRs.getString("actual_financial_direction"); item.predicted_financial_direction = aRs.getString("predicted_financial_direction"); item.predicted_financial_change = aRs.getDouble("predicted_financial_change"); item.predicted_financial_up_prob = aRs.getDouble("predicted_financial_up_prob"); item.predicted_financial_down_prob = aRs.getDouble("predicted_financial_down_prob"); item.predicted_financial_normalised_prob = aRs.getDouble("predicted_financial_normalised_prob"); resultList.add(item); } return resultList; }
/* * Return a computer using its id */ public Computer get(Long computerId) throws SQLException, ParseException { logger.debug("Enterring get(Long computerId) in ComputerDAO."); Connection connection = DataSourceUtils.getConnection(datasource); String query = "SELECT * FROM `computer-database-db`.`computer` AS computer LEFT OUTER JOIN `computer-database-db`.`company` AS company ON computer.company_id=company.id WHERE computer.id=?;"; PreparedStatement statement = connection.prepareStatement(query); statement.setLong(1, computerId); ResultSet resultSet = statement.executeQuery(); Computer computer = null; while (resultSet.next()) { Company company = Company.builder().id(resultSet.getLong(5)).name(resultSet.getString(7)).build(); computer = Computer.builder() .id(resultSet.getLong(1)) .name(resultSet.getString(2)) .company(company) .build(); if (resultSet.getDate(3) != null) { computer.setIntroduced(new DateTime(resultSet.getDate(3))); } if (resultSet.getDate(4) != null) { computer.setDiscontinued(new DateTime(resultSet.getDate(4))); } } if (resultSet != null) resultSet.close(); if (statement != null) statement.close(); logger.debug("Leaving get(Long computerId) in ComputerDAO."); return computer; }
public ArrayList<Device> getDevice() { ArrayList<Device> devs = new ArrayList(); try { Connection connect = conn.use(); Statement stmt = connect.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM device"); while (rs.next()) { devs.add( new Device( rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getInt(8), rs.getDate(9), rs.getDate(10), rs.getInt(11))); } conn.release(connect); } catch (SQLException ex) { System.out.println("Error in get device: "); Logger.getLogger(DeviceBroker.class.getName()).log(Level.SEVERE, null, ex); } return devs; }
public List<ProdutoPronto> listar() throws Exception { String sql = "SELECT * FROM produtopronto"; PreparedStatement ps = null; ResultSet rs = null; List<ProdutoPronto> listaDeProdutoPronto = null; try (Connection conn = ConnectionProvider.getInstance().getConnection()) { ps = conn.prepareStatement(sql); rs = ps.executeQuery(); listaDeProdutoPronto = new ArrayList<ProdutoPronto>(); while (rs.next()) { ProdutoPronto pPronto = new ProdutoPronto(); pPronto.setId(rs.getLong("id")); pPronto.setProdutoId(rs.getInt("produtoId")); pPronto.setEncomendaId(rs.getInt("encomendaId")); pPronto.setFinalizado(rs.getDate("finalizado").toLocalDate()); pPronto.setDataValidade(rs.getDate("dataValidade").toLocalDate()); pPronto.setCodigo(rs.getString("codigo")); listaDeProdutoPronto.add(pPronto); for (int i = 0; i < listaDeProdutoPronto.size(); i++) { // enquanto i for menor, não maior System.out.println(listaDeProdutoPronto.get(i)); } } ps.close(); conn.close(); } catch (SQLException e) { System.out.println("Erro ao listar os produtos Prontos\n" + e); } return listaDeProdutoPronto; }
private static Date getDate(SQLDialect dialect, ResultSet rs, int index) throws SQLException { // SQLite's type affinity needs special care... if (dialect == SQLDialect.SQLITE) { String date = rs.getString(index); if (date != null) { return new Date(parse("yyyy-MM-dd", date)); } return null; } // Cubrid SQL dates are incorrectly fetched. Reset milliseconds... // See http://jira.cubrid.org/browse/APIS-159 // See https://sourceforge.net/apps/trac/cubridinterface/ticket/140 else if (dialect == CUBRID) { Date date = rs.getDate(index); if (date != null) { Calendar cal = Calendar.getInstance(); cal.setTimeInMillis(date.getTime()); cal.set(Calendar.MILLISECOND, 0); date = new Date(cal.getTimeInMillis()); } return date; } else { return rs.getDate(index); } }
@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; }
public ArrayList<Appointment> viewAppoinment(String vendorId) throws SQLException { /** * fetching appointment details of particular vendor * * @param poNumber * @return * @throws SQLException */ DBConnection dbConnection = new DBConnection(); Connection connection = dbConnection.getConnection(); logger.debug("fetching appointment details of particular vendor" + vendorId); PreparedStatement preparedStatement = connection.prepareStatement( "SELECT * FROM APPOINTMENT WHERE V_NO=? AND BILL_NO IN(SELECT BILL_NO FROM VENDOR_BILL WHERE READY_FOR_SHIPMENT='NO') ORDER BY STATUS ,BILL_NO ASC"); preparedStatement.setString(1, vendorId); ResultSet resultSet = preparedStatement.executeQuery(); ArrayList<Appointment> appointments = new ArrayList<Appointment>(); while (resultSet.next()) { String poNo = resultSet.getString(1); String dcNo = resultSet.getString(2); String vNo = resultSet.getString(3); Date expectedDate = resultSet.getDate(4); Date fixedDate = resultSet.getDate(5); String status = resultSet.getString(6); Appointment appointment = new Appointment(poNo, dcNo, vNo, expectedDate, fixedDate, status); appointments.add(appointment); } return appointments; }
public PurchaseOrder viewPONUmber(String poNumber) throws SQLException { /** * For viewing the details of purchase order corresponding to a particular poNumber * * @param poNumber * @return * @throws SQLException */ DBConnection dbConnection = new DBConnection(); Connection connection = dbConnection.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(Selection.selectPoFromPo); logger.debug( "fetching details of purchase order corresponding to a particular poNumber" + poNumber); preparedStatement.setString(1, poNumber); ResultSet resultSet = preparedStatement.executeQuery(); if (resultSet.next()) { String poNo = resultSet.getString(1); Date orderingDate = resultSet.getDate(2); String retailerName = resultSet.getString(3); Date expectedDate = resultSet.getDate(4); String orderBy = resultSet.getString(5); PurchaseOrder purchaseOrder = new PurchaseOrder(poNo, orderingDate, retailerName, expectedDate, orderBy); return purchaseOrder; } return null; }
public ArrayList<PurchaseOrder> viewPO(String vendorId) throws SQLException { /** * For viewing the details of purchase order corresponding to particular vendor * * @param vendorId * @return ArrayList * @throws SQLException */ DBConnection dbConnection = new DBConnection(); Connection connection = dbConnection.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(Selection.selectPoOfVendor); logger.debug( "For fetching the details of purchase order corresponding to particular vendor" + vendorId); preparedStatement.setString(1, vendorId); preparedStatement.setString(2, "APPROVED"); ResultSet resultSet = preparedStatement.executeQuery(); ArrayList<PurchaseOrder> purchaseOrders = new ArrayList<PurchaseOrder>(); while (resultSet.next()) { String poNo = resultSet.getString(1); Date orderingDate = resultSet.getDate(2); String retailerName = resultSet.getString(3); Date expectedDate = resultSet.getDate(4); String orderBy = resultSet.getString(5); PurchaseOrder purchaseOrder = new PurchaseOrder(poNo, orderingDate, retailerName, expectedDate, orderBy); purchaseOrders.add(purchaseOrder); } return purchaseOrders; }
public orgen_ta_subdependencia getSubDependencia(String in_codigo_subdepen) { System.out.println("metodo getDependencia"); orgen_ta_subdependencia obj = new orgen_ta_subdependencia(); try { cn = ds.getConnection(); String sql = "SELECT IN_CODIGO_SUBDEPENDENCIA,VC_NOMBRE,VC_DESCRIPCION,CH_ESTADO,VC_USUARIO_CREA,DT_FECHA_CREA,VC_USUARIO_MODIFICA,DT_USUARIO_MODIFICA,IN_CODIGO_DEPENDENCIA FROM ORGEN_TA_SUBDEPENDENCIA WHERE IN_CODIGO_SUBDEPENDENCIA =?"; PreparedStatement pstm = cn.prepareStatement(sql); pstm.setInt(1, Integer.parseInt(in_codigo_subdepen)); ResultSet rs = pstm.executeQuery(); while (rs.next()) { obj.setIn_codigo_subdependencia(rs.getInt("IN_CODIGO_SUBDEPENDENCIA")); obj.setVc_nombre(rs.getString("VC_NOMBRE")); obj.setVc_descripcion(rs.getString("VC_DESCRIPCION")); obj.setCh_estado(rs.getString("CH_ESTADO")); obj.setVc_usuario_crea(rs.getString("VC_USUARIO_CREA")); obj.setDt_fecha_crea(String.valueOf(rs.getDate("DT_FECHA_CREA"))); obj.setVc_usuario_modifica(rs.getString("VC_USUARIO_MODIFICA")); obj.setVc_usuario_modifica(String.valueOf(rs.getDate("DT_USUARIO_MODIFICA"))); obj.setIn_codigo_dependecia(rs.getInt("IN_CODIGO_DEPENDENCIA")); } pstm.close(); } catch (Exception e) { System.out.println("Failed to execute a JDBC task: " + e); } finally { try { cn.close(); } catch (Exception ex) { System.out.println("Failed to finalize JDBC task: " + ex); } } return obj; }
/** * Creates a list of ContactListItem from a result set * * @param rs - result set from getAllContactsSQL */ public static List<ContactListItem> processResults(ResultSet rs) { List<ContactListItem> contacts = new ArrayList<ContactListItem>(); try { while (rs.next()) { ContactListItem contactListItem = new ContactListItem(); contactListItem.setName(rs.getString("name")); contactListItem.setUserId(rs.getString("user_id")); contactListItem.setTitle(rs.getString("title")); contactListItem.setMemberId(rs.getLong("member_id")); contactListItem.setCompany(rs.getString("company_name")); contactListItem.setJoinedOn(rs.getDate("joined_on")); contactListItem.setLastVisited(rs.getDate("last_visited")); contactListItem.setLastAttended(rs.getDate("last_attended")); contactListItem.setTotalRSVP(rs.getLong("total_rsvp")); contactListItem.setRSVPYes(rs.getLong("rsvp_yes")); contactListItem.setRSVPMaybe(rs.getLong("rsvp_maybe")); contactListItem.setRSVPNo(rs.getLong("rsvp_no")); contactListItem.setAttended(rs.getLong("attended")); contactListItem.setNoShow(rs.getLong("no_show")); contactListItem.setIntro(rs.getBoolean("intro")); contactListItem.setPhoto(rs.getBoolean("photo")); contactListItem.setAssistant(rs.getBoolean("assistant_org")); contactListItem.setMailingList(rs.getString("mailing_list")); contactListItem.setUrl(rs.getString("url")); contacts.add(contactListItem); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return contacts; }
final void initHuman() { String sql = "Select * from tblHuman where id = ?"; try (Connection cn = Tools.getConn(); PreparedStatement pst = cn.prepareStatement(sql); ) { pst.setInt(1, Integer.parseInt(vRow.get(0).toString())); ResultSet rs = pst.executeQuery(); if (rs.next()) { txtUserName.setText(rs.getString(2)); txtBirthDay.setDate(rs.getDate(3)); txtGender.setText(rs.getString(4)); txtBirthPlace.setText(rs.getString(5)); txtNativeCountry.setText(rs.getString(6)); txtNation.setText(rs.getString(7)); txtReligion.setText(rs.getString(8)); txtOccupation.setText(rs.getString(9)); txtWorkPlace.setText(rs.getString(10)); txtIDCard.setText(rs.getString(11)); txtArrivalDate.setDate(rs.getDate(12)); byte[] b = rs.getBytes(13); ImageIcon icon = new ImageIcon(b); Image img = icon.getImage(); img = img.getScaledInstance(lblImage.getWidth(), lblImage.getHeight(), Image.SCALE_SMOOTH); lblImage.setIcon(new ImageIcon(img)); txtRoomID.setText(rs.getString(14)); txtEmail.setText(rs.getString(15)); } } catch (SQLException ex) { Logger.getLogger(EditHumanDialog.class.getName()).log(Level.SEVERE, null, ex); } }
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()); }