@Override public JSONObject getRestaurantsById(String businessId, boolean isVisited) { try { String sql = "SELECT * from restaurants where business_id = ?"; PreparedStatement statement = conn.prepareStatement(sql); statement.setString(1, businessId); ResultSet rs = statement.executeQuery(); if (rs.next()) { Restaurant restaurant = new Restaurant( rs.getString("business_id"), rs.getString("name"), rs.getString("categories"), rs.getString("city"), rs.getString("state"), rs.getString("full_address"), rs.getFloat("stars"), rs.getFloat("latitude"), rs.getFloat("longitude"), rs.getString("image_url"), rs.getString("url")); JSONObject obj = restaurant.toJSONObject(); obj.put("is_visited", isVisited); return obj; } } catch (Exception e) { /* report an error */ System.out.println(e.getMessage()); } return null; }
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 List<BusRecRoute> getRec(ResultSet rset) { List<BusRecRoute> list = new ArrayList<BusRecRoute>(); if (rset == null) return list; BusRecRoute rec; try { while (rset.next()) { rec = new BusRecRoute(); rec.id = rset.getInt("id"); rec.route_key = rset.getString("route_key"); rec.curve_key = rset.getString("curve_key"); rec.bus_type = rset.getInt("type"); rec.company_id = rset.getInt("company_id"); rec.company = rset.getString("company"); rec.bus_line = rset.getString("bus_line"); rec.rate_per_day = rset.getFloat("day"); rec.rate_per_saturday = rset.getFloat("saturday"); rec.rate_per_holiday = rset.getFloat("holiday"); rec.remarks = rset.getString("remarks"); rec.num = rset.getInt("num"); rec.max_lat = rset.getDouble("max_lat"); rec.min_lat = rset.getDouble("min_lat"); rec.max_lon = rset.getDouble("max_lon"); rec.min_lon = rset.getDouble("min_lon"); list.add(rec); } } catch (SQLException e) { e.printStackTrace(); } return list; }
public static void listPolicy() { ResultSet rs1; String str1 = "select * from policies"; DBConnections.query = str1; rs1 = DBConnections.openDbConnectionForSelect(DBConnections.query); try { System.out.println("Current policy"); System.out.println( "-----------------------------------------------------------------------------------------------------------------------\n"); System.out.printf( "%20s%20s%20s%40s%n", "Membership Status", "Review Points", "Purchase Points", "Threshold for memberships"); while (rs1.next()) { System.out.printf("%20s", rs1.getString(1)); System.out.printf("%20.2f", rs1.getFloat(2)); System.out.printf("%20.2f", rs1.getFloat(3)); System.out.printf("%20.2f%n", rs1.getFloat(4)); } System.out.println( "-----------------------------------------------------------------------------------------------------------------------\n"); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DBConnections.closeDbConnection(); } }
// 只限制他可以查询订单商品名称,供应商姓名,时间和saleworker是谁的模糊。 @Override public List<Bill> findBillsByMohu(String... keyWords) throws Exception { List<Bill> bills = new ArrayList<Bill>(); con = BaseDao.getConnection(); String sql = "select * from t_bill where productname like ? AND suppliername like? AND billtime like ? AND saleworker like ?"; pstmt = con.prepareStatement(sql); pstmt.setString(1, "%" + keyWords[0] + "%"); pstmt.setString(2, "%" + keyWords[1] + "%"); pstmt.setString(3, "%" + keyWords[2] + "%"); pstmt.setString(4, "%" + keyWords[3] + "%"); rs = pstmt.executeQuery(); while (rs.next()) { Bill bill = new Bill(); bill.setBill_id(Integer.parseInt(rs.getString("bill_id"))); bill.setproductName(rs.getString("productname")); bill.setAmount(rs.getInt("amount")); bill.setPrice(rs.getFloat("price")); bill.setPay(rs.getFloat("pay")); bill.setsupplierName(rs.getString("suppliername")); bill.setBilltime(rs.getString("billtime")); bill.setsaleworker(rs.getString("saleworker")); bills.add(bill); } BaseDao.closeAll(rs, pstmt, con); return bills; }
private List<Bill> findBillsByPageAndMohuImpl(int currentPage, int pageSize, String[] keyWords) throws Exception { List<Bill> bills = new ArrayList<Bill>(); con = BaseDao.getConnection(); String sql = "select * from t_bill where productname like ? AND suppliername like ? limit ?,?;"; pstmt = con.prepareStatement(sql); pstmt.setString(1, "%" + keyWords[0] + "%"); pstmt.setString(2, "%" + keyWords[1] + "%"); pstmt.setInt(3, (currentPage - 1) * pageSize); pstmt.setInt(4, pageSize); rs = pstmt.executeQuery(); while (rs.next()) { Bill bill = new Bill(); bill.setBill_id(rs.getInt("bill_id")); bill.setproductName(rs.getString("productname")); bill.setAmount(rs.getInt("amount")); bill.setPrice(rs.getFloat("price")); bill.setPay(rs.getFloat("pay")); bill.setsupplierName(rs.getString("suppliername")); bill.setBilltime(rs.getString("billtime")); bill.setsaleworker(rs.getString("saleworker")); bills.add(bill); } BaseDao.closeAll(rs, pstmt, con); return bills; }
@Override protected Threshold doInBackground(Void... params) { // 取最新的阀值,并且thresholdflag = 256 try { String sql = "SELECT uptem,lowtem,upco2,lowco2,uphum,lowhum,upillum,lowillum,updatetime FROM " + "thresholdtb WHERE thresholdflag = 256 ORDER BY updatetime DESC LIMIT 0,1;"; connectionUtil = new DBConnectionUtil(sql); ResultSet resultSet = connectionUtil.pst.executeQuery(); while (resultSet.next()) { threshold = new Threshold( resultSet.getFloat("uptem"), resultSet.getFloat("lowtem"), resultSet.getInt("upco2"), resultSet.getInt("lowco2"), resultSet.getFloat("uphum"), resultSet.getFloat("lowhum"), resultSet.getInt("upillum"), resultSet.getInt("lowillum"), resultSet.getString("updatetime")); } } catch (SQLException e) { e.printStackTrace(); } finally { connectionUtil.close(); } return threshold; }
public DayDataVO getBaseDayData(String paramString, Date paramDate) throws SQLException { DayDataVO localDayDataVO = null; PreparedStatement localPreparedStatement = null; ResultSet localResultSet = null; paramDate.setHours(0); paramDate.setMinutes(0); paramDate.setSeconds(0); try { this.conn = getConn(); String str = "SELECT * FROM (SELECT * FROM HistoryDayData WHERE CommodityID = ? AND TradeDate >= ? ORDER BY TradeDate) WHERE rownum =1"; localPreparedStatement = this.conn.prepareStatement(str); localPreparedStatement.setString(1, paramString); localPreparedStatement.setTimestamp(2, new Timestamp(paramDate.getTime())); localResultSet = localPreparedStatement.executeQuery(); if (localResultSet.next()) { localDayDataVO = new DayDataVO(); localDayDataVO.time = localResultSet.getTimestamp("TradeDate"); localDayDataVO.openPrice = localResultSet.getFloat("OpenPrice"); localDayDataVO.highPrice = localResultSet.getFloat("HighPrice"); localDayDataVO.lowPrice = localResultSet.getFloat("LowPrice"); localDayDataVO.closePrice = localResultSet.getFloat("ClosePrice"); localDayDataVO.balancePrice = localResultSet.getFloat("BalancePrice"); localDayDataVO.totalAmount = localResultSet.getLong("TotalAmount"); localDayDataVO.totalMoney = localResultSet.getFloat("TotalMoney"); localDayDataVO.reserveCount = localResultSet.getInt("ReserveCount"); } } finally { closeStatement(localResultSet, localPreparedStatement, null); } return localDayDataVO; }
@Override protected List<Environment> doInBackground(Void... params) { try { // 查询最新的数据 String sql = "select environmentparaid,temperature,co2,humidity,illuminance,recordtime from environmentparatb " + "where greenhouseid = '" + greenHouse.getId() + "'order by recordtime desc limit 0 , 1"; connectionUtil = new DBConnectionUtil(sql); ResultSet resultSet = connectionUtil.pst.executeQuery(); while (resultSet.next()) { environment = new Environment( resultSet.getInt("environmentparaid"), resultSet.getFloat("temperature") / 10, resultSet.getInt("co2"), resultSet.getFloat("humidity"), resultSet.getInt("illuminance"), resultSet.getString("recordtime")); environments.add(environment); } } catch (SQLException e) { e.printStackTrace(); } finally { connectionUtil.close(); } return environments; }
/** @see SQLEntityAdapter#getEntity(Connection, ResultSet) */ @Override public TaskInstance getEntity(Connection connection, ResultSet resultSet) throws SQLException { PluginInfo pluginInfo = new PluginInfo( resultSet.getString("plugin_id"), resultSet.getString("plugin_name"), resultSet.getFloat("plugin_version"), resultSet.getString("plugin_description"), null); TaskInstance taskInstance = new TaskInstance( resultSet.getString("id"), resultSet.getString("name"), resultSet.getString("description"), resultSet.getString("username"), pluginInfo, resultSet.getString("state"), resultSet.getFloat("completePercentage"), resultSet.getTimestamp("startDate"), resultSet.getTimestamp("finishDate"), resultSet.getString("report")); PluginParameter[] parameters = getTaskInstanceParameters(connection, taskInstance.getId()); pluginInfo.setParameters(parameters); return taskInstance; }
public JSONArray GetRestaurantsNearLoation(double lat, double lon) { try { if (conn == null) { return null; } Statement stmt = conn.createStatement(); String sql = "SELECT business_id, name, full_address, categories, stars, latitude, longitude, city, state from RESTAURANTS LIMIT 10"; // execute query expect result set ResultSet rs = stmt.executeQuery(sql); List<JSONObject> list = new ArrayList<JSONObject>(); while (rs.next()) { JSONObject obj = new JSONObject(); obj.append("business_id", rs.getString("business_id")); obj.append("name", rs.getString("name")); obj.append("stars", rs.getFloat("stars")); obj.append("latitude", rs.getFloat("latitude")); obj.append("longitude", rs.getFloat("longitude")); obj.append("full_address", rs.getString("full_address")); obj.append("city", rs.getString("city")); obj.append("state", rs.getString("state")); obj.append("categories", DBImport.stringToJSONArray(rs.getString("categories"))); list.add(obj); } return new JSONArray(list); } catch (Exception e) { /* report an error */ System.out.println(e.getMessage()); } return null; }
private JSONObject getRestaurantsById(String businessId) { try { Statement stmt = conn.createStatement(); String sql = "SELECT business_id, name, full_address, categories, stars, latitude, longitude, city, state, image_url from " + "RESTAURANTS where business_id='" + businessId + "'" + " ORDER BY stars DESC"; ResultSet rs = stmt.executeQuery(sql); if (rs.next()) { JSONObject obj = new JSONObject(); obj.append("business_id", rs.getString("business_id")); obj.append("name", rs.getString("name")); obj.append("stars", rs.getFloat("stars")); obj.append("latitude", rs.getFloat("latitude")); obj.append("longitude", rs.getFloat("longitude")); obj.append("full_address", rs.getString("full_address")); obj.append("city", rs.getString("city")); obj.append("state", rs.getString("state")); obj.append("categories", DBImport.stringToJSONArray(rs.getString("categories"))); obj.append("image_url", rs.getString("image_url")); return obj; } } catch (Exception e) { /* report an error */ System.out.println(e.getMessage()); } return null; }
public List<ConsumeData> getAllChinaLifeMemberConsumeDateInZXQZYDIY(Connection appconn) throws Exception { log.info("Enter ZhongXinQinZiYouDIY getAllChinaLifeMemberConsumeDateInZXQZYDIY"); boolean createConn = false; if (appconn == null) { Class.forName("oracle.jdbc.driver.OracleDriver"); appconn = DbConnectionFactory.getInstance().getConnection("posapp"); createConn = true; } List<ConsumeData> list = new ArrayList<ConsumeData>(); ResultSet rs = null; PreparedStatement pstmt = null; String sql = "select cp.membercardid,cp.shopname,cp.amountcurrency,cp.point,cp.producttypename,cp.transdate,cp.tempmembertxid from clubpoint cp where cp.membercardid like '95519%' and clubid = '00' and shopid = '5381' order by transdate desc"; log.info("getAllchinalife member in ZXQZYDIY sql is" + sql); pstmt = appconn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); rs = pstmt.executeQuery(); while (rs.next()) { ConsumeData data = new ConsumeData(); String memberCardNo = rs.getString(1); String shopName = rs.getString(2); float consumeMoney = rs.getFloat(3); float point = rs.getFloat(4); String consumeType = rs.getString(5); Date transDate = rs.getDate(6); String memberTxId = rs.getString(7); data.setMemberCardNo(memberCardNo); data.setConsumeMoney(consumeMoney); data.setConsumeType(consumeType); data.setPoint(point); data.setShopName(shopName); data.setTransDate(transDate); data.setMemberTxId(memberTxId); list.add(data); } if (rs != null) rs.close(); if (pstmt != null) pstmt.close(); if (createConn && (appconn != null)) { SqlUtil.close(appconn); appconn = null; } log.info( "Exit ZhongXinQinZiYouDIY getAllChinaLifeMemberConsumeDateInZXQZYDIY list size is " + list.size()); return list; }
/** * 根据查询条件查询发票结果,从视图中查询 * * @param m * @return * @throws SQLException */ public List<ArInvoiceView> list(Map m) throws SQLException { List<ArInvoiceView> list = new ArrayList<ArInvoiceView>(); Connection conn = this.getSession().connection(); // String sql = "select * from accounting.invoiceView a where 1= 1 "; String sql = "select * from accounting.v_ar_invoice_list a where 1= 1 "; if (m != null) { if (m.get("filter_LIKES_invoiceNo") != null && !m.get("filter_LIKES_invoiceNo").equals("")) { sql += " and a.invoice_no like '%" + m.get("filter_LIKES_invoiceNo") + "%'"; } if (m.get("filter_EQI_orderNo") != null && !m.get("filter_EQI_orderNo").equals("")) { sql += " and a.order_no =" + m.get("filter_EQI_orderNo"); } if (m.get("filter_EQI_custNo") != null && !m.get("filter_EQI_custNo").equals("")) { sql += " and a.cust_no =" + m.get("filter_EQI_custNo"); } if (m.get("filter_EQI_shipmentId") != null && !m.get("filter_EQI_shipmentId").equals("")) { sql += " and a.shipment_id = " + m.get("filter_EQI_shipmentId"); } if (m.get("filter_EQS_status") != null && !m.get("filter_EQS_status").equals("")) { sql += " and a.status='" + m.get("filter_EQS_status") + "'"; } if (m.get("filter_GTD_invoiceDate") != null && !m.get("filter_GTD_invoiceDate").equals("")) { sql += " and a.invoice_date>='" + m.get("filter_GTD_invoiceDate") + "'"; } if (m.get("filter_LTD_invoiceDate") != null && !m.get("filter_LTD_invoiceDate").equals("")) { sql += " and a.invoice_date<='" + m.get("filter_LTD_invoiceDate") + "'"; } } PreparedStatement ps = null; ResultSet rs = null; ArInvoiceView a = null; try { ps = conn.prepareStatement(sql); rs = ps.executeQuery(); while (rs.next()) { a = new ArInvoiceView(); a.setInvoiceId(rs.getInt("invoice_id")); a.setInvoiceNo(rs.getString("invoice_no")); a.setStatus(rs.getString("status")); a.setPaidAmt(rs.getFloat("paid_amt")); a.setBalance(rs.getFloat("balance")); a.setOrderNo(rs.getInt("order_no")); a.setShipmentId(rs.getInt("shipment_id")); a.setCustNo(rs.getInt("cust_no")); a.setInvoiceType(rs.getString("invoice_type")); a.setInvoiceDate(new java.util.Date(rs.getTimestamp("invoice_date").getTime())); a.setTotalAmount(rs.getFloat("total_amount")); a.setCurrency(rs.getString("currency")); list.add(a); } } catch (Exception e) { e.printStackTrace(); } finally { rs.close(); ps.close(); conn.close(); } return list; }
private void listaFacturasMouseClicked( java.awt.event.MouseEvent evt) { // GEN-FIRST:event_listaFacturasMouseClicked try { if (rsVenta == null || listaFacturas.getSelectedIndex() == -1) { return; } rsVenta.absolute(listaFacturas.getSelectedIndex() + 1); lbNumeroVenta.setText(rsVenta.getString("id_venta")); lbNombre.setText( rsVenta.getString("nombre_cliente") + " " + rsVenta.getString("apellido_cliente")); lbCedulaRif.setText(rsVenta.getString("cedula_rif")); lbfecha.setText(rsVenta.getDate("fecha_venta").toString()); lbDireccion.setText(rsVenta.getString("domicilio_cliente")); String sql = "SELECT venta.id_venta, detalle_venta.cantidad,detalle_venta.importe, " + "producto.nombre_producto,producto.descripcion_producto, producto.id_producto, producto.precio_producto " + "FROM public.venta " + "INNER JOIN public.detalle_venta ON venta.id_venta=detalle_venta.id_venta " + "INNER JOIN public.producto ON detalle_venta.id_producto=producto.id_producto " + "WHERE venta.id_venta =" + rsVenta.getInt("id_venta") + "ORDER BY id_venta "; Statement st = Conexion.getConnection() .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = st.executeQuery(sql); int cant = modeloTabla.getRowCount(); for (int i = 0; i < cant; i++) { modeloTabla.removeRow(0); } float subTotal = 0f; while (rs.next()) { subTotal += rs.getFloat("precio_producto") * rs.getInt("cantidad"); modeloTabla.addRow( new Object[] { rs.getInt("id_producto"), rs.getString("nombre_producto"), rs.getString("descripcion_producto"), rs.getInt("cantidad"), rs.getFloat("precio_producto"), subTotal }); } tfSubTotal.setText(subTotal + ""); tfIva.setText((subTotal * 0.12) + ""); tfTotal.setText(rsVenta.getFloat("total_venta") + ""); } catch (SQLException ex) { Logger.getLogger(cliente.class.getName()).log(Level.SEVERE, null, ex); } } // GEN-LAST:event_listaFacturasMouseClicked
public static float getSingleValueResult(String sqlQuery) throws SQLException { openConnection(); System.out.println("S-a apelat expresia SQL '" + sqlQuery + "'"); ResultSet result = statement.executeQuery(sqlQuery); result.next(); System.out.println("Rezultat: " + result.getFloat(1)); return result.getFloat(1); }
/** {@inheritDoc} */ @Override public Contratto read(String pk) { Contratto contratto = null; DAOOperatore operatore = null; DAOCliente cliente = null; DAOAgenzia agenziaNoleggio = null; DAOAgenzia agenziaConsegna = null; try { contratto = new Contratto(); operatore = new DAOOperatore(); cliente = new DAOCliente(); agenziaNoleggio = new DAOAgenzia(); agenziaConsegna = new DAOAgenzia(); ResultSet rs = connection.executeReadQuery("select * from contratto where id = " + pk + ";"); while (rs.next()) { contratto.setId(rs.getInt("id")); contratto.setOperatore(operatore.read(rs.getString("operatore"))); contratto.setCliente(cliente.read(rs.getString("cliente"))); contratto.setAgenziaNoleggio( agenziaNoleggio.read(Integer.toString(rs.getInt("agenziaNoleggio")))); contratto.setAgenziaConsegna( agenziaConsegna.read(Integer.toString(rs.getInt("agenziaconsegna")))); contratto.setDataStipula(DateHelper.dateToLocalDate(rs.getDate("datastipula"))); contratto.setDataChiusura(DateHelper.dateToLocalDate(rs.getDate("datachiusura"))); contratto.setDataInizioNoleggio( DateHelper.dateToLocalDate(rs.getDate("datainizionoleggio"))); contratto.setDataFineNoleggio(DateHelper.dateToLocalDate(rs.getDate("dataFineNoleggio"))); contratto.setChilometraggioLimitato(rs.getBoolean("chilometraggiolimitato")); contratto.setChilometriPrevisti(rs.getInt("chilometriprevisti")); contratto.setChilometriPercorsi(rs.getInt("chilometripercorsi")); contratto.setRifornimento(Rifornimento.getRifornimento(rs.getInt("rifornimento"))); contratto.setAcconto(rs.getFloat("acconto")); contratto.setChiuso(rs.getBoolean("chiuso")); contratto.setCosto(rs.getFloat("costo")); contratto.setVettura(new DAOVettura().read(rs.getString("vettura"))); contratto.setAssicurazioneAvanzata(rs.getBoolean("assicurazioneAvanzata")); ResultSet rs_optional = connection.executeReadQuery( "SELECT idOptional from optional_contratto where idContratto = " + rs.getInt(1) + ";"); List<Optional> optionals = new ArrayList<Optional>(); while (rs_optional.next()) { optionals.add(new DAOOptional().read(rs_optional.getString(1))); } contratto.setOptionals(optionals); } } catch (Exception e) { } return contratto; }
public Object mapRow(ResultSet rs, int arg1) throws SQLException { WeeklySalesPO weeklySales = new WeeklySalesPO(); weeklySales.setWeeklySalesId(rs.getString(1)); weeklySales.setProductName(rs.getString(2)); weeklySales.setSpecificationsModel(rs.getString(3)); weeklySales.setUnitPrice(rs.getFloat(4)); weeklySales.setQuantity(rs.getInt(5)); weeklySales.setTotalPrice(rs.getFloat(6)); weeklySales.setWeek(rs.getInt(7)); weeklySales.setYear(rs.getInt(8)); return weeklySales; }
private CompteEpargne buildCompteEpargne(ResultSet resultSet) throws BankonetException { try { int id = resultSet.getInt("ID"); String libelle = resultSet.getString("LIBELLE"); float solde = resultSet.getFloat("SOLDE"); float plafond = resultSet.getFloat("PLAFOND"); float taux = resultSet.getFloat("TAUX"); return new CompteEpargne(id, libelle, solde, taux, plafond); } catch (Exception e) { e.printStackTrace(); throw new BankonetException("erreur dans le parcours du ResultSet"); } }
public void fillFromResultSet(ResultSet rSet) throws SQLException { id = rSet.getInt("idArtikel"); shortDescription = rSet.getString("Bezeichnung"); longDescription = rSet.getString("Beschreibung"); weight = rSet.getFloat("Gewicht"); amount = rSet.getFloat("Anzahl"); vat = rSet.getFloat("MwSt"); grossPrice = rSet.getFloat("Preis_Brutto"); netPrice = rSet.getFloat("Preis_Netto"); expDate = rSet.getDate("AblaufDatum"); idUser = rSet.getInt("idNutzer"); state = rSet.getString("Zustand"); }
@Test public void getNullableFloatWithColumnIndexWorks() throws SQLException { ResultSet mockResultSet = mock(ResultSet.class); when(mockResultSet.getFloat(2)).thenReturn(7.0F); assertEquals(7.0F, ResultSets.getNullableFloat(mockResultSet, 2).floatValue(), 0.0001); assertEquals(7.0F, ResultSets.enhance(mockResultSet).getNullableFloat(2).floatValue(), 0.0001); when(mockResultSet.getFloat(2)).thenReturn(0.0F); when(mockResultSet.wasNull()).thenReturn(true); assertNull(ResultSets.getNullableFloat(mockResultSet, 2)); assertNull(ResultSets.enhance(mockResultSet).getNullableFloat(2)); }
private CompteCourant buildCompteCourant(ResultSet resultSet) throws BankonetException { try { int id = resultSet.getInt("ID"); String libelle = resultSet.getString("LIBELLE"); float solde = resultSet.getFloat("SOLDE"); float plafond = resultSet.getFloat("PLAFOND"); float decouvert = resultSet.getFloat("DECOUVERTAUTORISE"); return new CompteCourant(id, libelle, solde, decouvert); } catch (Exception e) { e.printStackTrace(); throw new BankonetException("erreur dans le parcours du ResultSet"); } }
public void readDataBase(Connection connect) throws Exception { try { // Statements allow to issue SQL queries to the database statement = connect.createStatement(); // Result set get the result of the SQL query rs = statement.executeQuery( "SELECT id, name, address, port, icon, realmflags, timezone, allowedSecurityLevel, population FROM realmlist WHERE (realmflags & 1) = 0 ORDER BY name"); realms.clear(); while (rs.next()) { Realm list = new Realm(); list.setId(rs.getInt("id")); list.setName(rs.getString("Name")); list.setAddress(rs.getString("address")); list.setPort(rs.getShort("port")); list.setIcon(rs.getShort("icon")); list.setRealmflags(rs.getShort("realmflags")); list.setTimezone(rs.getShort("timezone")); list.setAllowedSecurityLevel(rs.getShort("allowedSecurityLevel")); list.setPopulation(rs.getFloat("population")); realms.add(list); } } catch (Exception e) { throw e; } finally { close(); } }
public static void viewTable(Connection con, String dbName) throws SQLException { Statement stmt = null; String query = "select COF_NAME, SUP_ID, PRICE, " + "SALES, TOTAL " + "from " + dbName + ".COFFEES"; try { stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); while (rs.next()) { String coffeeName = rs.getString("COF_NAME"); int supplierID = rs.getInt("SUP_ID"); float price = rs.getFloat("PRICE"); int sales = rs.getInt("SALES"); int total = rs.getInt("TOTAL"); System.out.println( coffeeName + "\t" + supplierID + "\t" + price + "\t" + sales + "\t" + total); } } catch (SQLException e) { // JDBCTutorialUtilities.printSQLException(e); } finally { if (stmt != null) { stmt.close(); } } }
/** * retourne la liste des catégories définies dans la bd * * @param Utilisateur * @return Vector<Categorie> * @throws CategorieException * @throws ExceptionConnexion */ public static Vector<Categorie> getCategorie(Utilisateur user) throws CategorieException, ExceptionConnexion { Vector<Categorie> res = new Vector<Categorie>(); String requete; Statement stmt; ResultSet rs; Connection conn = BDConnexion.getConnexion(user.getLogin(), user.getmdp()); requete = "select nomc, prix from LesCategories order by nomc"; try { stmt = conn.createStatement(); rs = stmt.executeQuery(requete); while (rs.next()) { res.addElement(new Categorie(rs.getString(1), rs.getFloat(2))); } } catch (SQLException e) { throw new CategorieException( " Problème dans l'interrogation des catégories.." + "Code Oracle " + e.getErrorCode() + "Message " + e.getMessage()); } BDConnexion.FermerTout(conn, stmt, rs); return res; }
public List<ItensPedido> getItensByPedido(int pedido) { List<ItensPedido> itensPedido = new ArrayList<ItensPedido>(); PreparedStatement preparedStatement; try { preparedStatement = connection.prepareStatement( "SELECT I.PEDIDO, I.LIVRO, I.QUANTIDADE, L.NOME, L.PRECO " + "FROM ITENSPEDIDO I JOIN LIVRO L ON I.LIVRO = L.ID " // join para retornar nome // dos livros + "WHERE I.PEDIDO = (?)"); preparedStatement.setInt(1, pedido); ResultSet rs = preparedStatement.executeQuery(); while (rs.next()) { ItensPedido item = new ItensPedido(); item.setPedido(rs.getInt("I.PEDIDO")); item.setLivroID(rs.getInt("I.LIVRO")); item.setLivroNome(rs.getString("L.NOME")); item.setQuantidade(rs.getInt("I.QUANTIDADE")); item.setValorTotalItens(rs.getInt("I.QUANTIDADE") * rs.getFloat("L.PRECO")); itensPedido.add(item); } return itensPedido; } catch (SQLException e) { e.printStackTrace(); return itensPedido; } }
/** * Chargement du cache. * * @param dtRfa le RFA du domaine technique * @throws SQLException si un problème survient */ public void chargeCache(String dtRfa) throws SQLException { CACHE_P_INSP = new HashMap<String, Map<String, Float>>(CACHE_INITIAL_CAPACITY); // StopWatch watch = new StopWatch(); // watch.start(); String sql = PInspectionConstante.REQUETE_SELECT_INSP; // LOGGER.debug("Execution de la requete : {}", sql); ResultSet resultSet = this.performQuery(sql, dtRfa); try { while (resultSet.next()) { String nomen_rfa = resultSet.getString(1); String eval_rfa = resultSet.getString(2); Float poids = resultSet.getFloat(3); if (!CACHE_P_INSP.containsKey(nomen_rfa)) { CACHE_P_INSP.put(nomen_rfa, new HashMap<String, Float>(10)); } Map<String, Float> cacheNomen = CACHE_P_INSP.get(nomen_rfa); cacheNomen.put(eval_rfa, poids); LOGGER.info("Cache ++ : ( {}, {} ) |-> {}", new String[] {nomen_rfa, eval_rfa, poids + ""}); } } finally { resultSet.close(); } // watch.stop(); // LOGGER.debug("Temps de chargement du cache PInsp : {}", // watch.toString()); }
public static void main(String args[]) { NumberFormat currency = NumberFormat.getCurrencyInstance(); final String DRIVER = "com.mysql.jdbc.Driver"; final String CONNECTION = "jdbc:mysql://localhost/AccountDatabase"; try { Class.forName(DRIVER).newInstance(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } // UNCOMMENT the lines below.sc try (Connection connection = DriverManager.getConnection(CONNECTION, "root", ""); java.sql.Statement statement = connection.createStatement(); ResultSet resultset = statement.executeQuery("select * from ACCOUNTS")) { while (resultset.next()) { System.out.println(resultset.getString("NAME")); System.out.print(", "); System.out.print(resultset.getString("ADDRESS")); System.out.print(" "); System.out.println(currency.format(resultset.getFloat("BALANCE"))); } } catch (SQLException e) { e.printStackTrace(); } }
@Override public Subscriber getSubById(int id) throws SQLException { Connection connection = null; try { connection = MysqlDaoFactory.getConnection(); String sql = "select * from subscribers where id = " + id; Statement stmt = connection.createStatement(); ResultSet resSet = stmt.executeQuery(sql); if (null != resSet && resSet.next()) { Subscriber sub = new Subscriber(); sub.setId(id); sub.setUserId(resSet.getInt(2)); sub.setScore(resSet.getFloat(3)); sub.setFname(resSet.getString(4)); sub.setLname(resSet.getString(5)); sub.setMname(resSet.getString(6)); sub.setAddress(resSet.getString(7)); sub.setBlocked(resSet.getBoolean(8)); return sub; } } catch (SQLException e) { throw new EJBException("Select failed due " + "SQLException: " + e.getMessage()); } finally { if (connection != null) { MysqlDaoFactory.putBackConnection(connection); } } return null; }
@Override public List<Subscriber> getSubs() throws SQLException { Connection connection = null; List<Subscriber> resList = null; String sql = "select * from subscribers"; try { connection = MysqlDaoFactory.getConnection(); Statement stmt = connection.createStatement(); ResultSet resSet = stmt.executeQuery(sql); resList = new ArrayList<Subscriber>(); while (resSet.next()) { Subscriber sub = new Subscriber(); sub.setId(resSet.getInt(1)); sub.setUserId(resSet.getInt(2)); sub.setScore(resSet.getFloat(3)); sub.setFname(resSet.getString(4)); sub.setLname(resSet.getString(5)); sub.setMname(resSet.getString(6)); sub.setAddress(resSet.getString(7)); sub.setBlocked(resSet.getBoolean(8)); resList.add(sub); } } catch (SQLException e) { throw new EJBException("Select failed due " + "SQLException: " + e.getMessage()); } finally { if (connection != null) { MysqlDaoFactory.putBackConnection(connection); } } if (resList.size() == 0) { return null; } else { return resList; } }