List of usage examples for java.sql ResultSet getDouble
double getDouble(String columnLabel) throws SQLException;
ResultSet object as a double in the Java programming language. From source file:com.cmart.DB.CassandraDBQuery.java
public ArrayList<Item> getOldSellingItems(long userID, long ts) { ArrayList<Item> items = new ArrayList<Item>(); if (userID < 1) return items; int attemptsRemaining = SQL_RETRIES; do {//from ww w . ja v a2 s .c o m Connection conn = this.getConnection(); if (conn != null) { PreparedStatement statement = null; try { // Create the SQL statement to get items the user is selling statement = conn .prepareStatement("SELECT * FROM olditems WHERE sellerid=" + userID + " AND ts>=" + ts); ResultSet rs = statement.executeQuery(); while (rs.next()) { // Make sure they are real long seller = 0; try { Long temp = rs.getLong("sellerid"); if (temp != null) seller = temp; } catch (Exception e) { } if (seller != 0) { Item currentItem = new Item(rs.getLong("itemid"), rs.getString("name"), rs.getString("description"), rs.getInt("quantity"), rs.getDouble("startprice"), rs.getDouble("reserveprice"), rs.getDouble("buynowprice"), rs.getDouble("curbid"), rs.getDouble("maxbid"), rs.getInt("noofbids"), new Date(rs.getLong("startdate")), new Date(rs.getLong("enddate")), rs.getLong("sellerid"), rs.getLong("categoryid"), rs.getString("thumbnail"), new ArrayList<Image>()); items.add(currentItem); } } rs.close(); attemptsRemaining = 0; } catch (CommunicationsException e) { this.forceCloseConnection(conn); this.checkConnections(); } catch (MySQLNonTransientConnectionException e) { this.forceCloseConnection(conn); this.checkConnections(); } catch (Exception e) { System.out.println("CassandraQuery (getOldSellingItems): Could not get the items"); e.printStackTrace(); } finally { this.closeSmt(statement); this.closeConnection(conn); } } attemptsRemaining--; } while (attemptsRemaining >= 0); return items; }
From source file:com.glaf.core.jdbc.QueryHelper.java
/** * @param conn//from w ww .j a v a2s . co m * ? * @param start * 0 * @param pageSize * ? * @param sql * ? * @param paramMap * ? * @return */ @SuppressWarnings("unchecked") public ResultModel getResultList(Connection conn, String sql, Map<String, Object> paramMap, int start, int pageSize) { if (!DBUtils.isLegalQuerySql(sql)) { throw new RuntimeException(" SQL statement illegal "); } ResultModel resultModel = new ResultModel(); boolean supportsPhysicalPage = false; PreparedStatement psmt = null; ResultSetMetaData rsmd = null; ResultSet rs = null; Dialect dialect = null; try { dialect = DBConfiguration.getDatabaseDialect(conn); if (dialect != null && dialect.supportsPhysicalPage()) { logger.debug("sql=" + sql); supportsPhysicalPage = dialect.supportsPhysicalPage(); sql = dialect.getLimitString(sql, start, pageSize); logger.debug(">>sql=" + sql); } List<Object> values = null; if (paramMap != null) { SqlExecutor sqlExecutor = DBUtils.replaceSQL(sql, paramMap); sql = sqlExecutor.getSql(); values = (List<Object>) sqlExecutor.getParameter(); } logger.debug("sql:\n" + sql); logger.debug("values:" + values); psmt = conn.prepareStatement(sql); if (values != null && !values.isEmpty()) { JdbcUtils.fillStatement(psmt, values); } List<ColumnDefinition> columns = new ArrayList<ColumnDefinition>(); rs = psmt.executeQuery(); rsmd = rs.getMetaData(); int count = rsmd.getColumnCount(); for (int i = 1; i <= count; i++) { int sqlType = rsmd.getColumnType(i); ColumnDefinition column = new ColumnDefinition(); column.setIndex(i); column.setColumnName(rsmd.getColumnName(i)); column.setColumnLabel(rsmd.getColumnLabel(i)); column.setJavaType(FieldType.getJavaType(sqlType)); column.setPrecision(rsmd.getPrecision(i)); column.setScale(rsmd.getScale(i)); if (column.getScale() == 0 && sqlType == Types.NUMERIC) { column.setJavaType("Long"); } column.setName(StringTools.lower(StringTools.camelStyle(column.getColumnLabel()))); columns.add(column); } resultModel.setHeaders(columns); if (!supportsPhysicalPage) { this.skipRows(rs, start); } int k = 0; while (rs.next() && k++ < pageSize) { int index = 0; RowModel rowModel = new RowModel(); Iterator<ColumnDefinition> iterator = columns.iterator(); while (iterator.hasNext()) { ColumnDefinition column = iterator.next(); ColumnDefinition c = new ColumnDefinition(); c.setColumnName(column.getColumnName()); c.setColumnLabel(column.getColumnLabel()); c.setName(column.getName()); c.setJavaType(column.getJavaType()); c.setPrecision(column.getPrecision()); c.setScale(column.getScale()); String javaType = column.getJavaType(); index = index + 1; if ("String".equals(javaType)) { String value = rs.getString(column.getIndex()); c.setValue(value); } else if ("Integer".equals(javaType)) { try { Integer value = rs.getInt(column.getIndex()); c.setValue(value); } catch (Exception e) { String str = rs.getString(column.getIndex()); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); c.setValue(num.intValue()); } } else if ("Long".equals(javaType)) { try { Long value = rs.getLong(column.getIndex()); c.setValue(value); } catch (Exception e) { String str = rs.getString(column.getIndex()); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); c.setValue(num.longValue()); } } else if ("Double".equals(javaType)) { try { Double value = rs.getDouble(column.getIndex()); c.setValue(value); } catch (Exception e) { String str = rs.getString(column.getIndex()); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); c.setValue(num.doubleValue()); } } else if ("Boolean".equals(javaType)) { Boolean value = rs.getBoolean(column.getIndex()); c.setValue(value); } else if ("Date".equals(javaType)) { Timestamp value = rs.getTimestamp(column.getIndex()); c.setValue(value); } else { c.setValue(rs.getObject(column.getIndex())); } rowModel.addColumn(c); } resultModel.addRow(rowModel); } } catch (Exception ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(psmt); JdbcUtils.close(rs); } return resultModel; }
From source file:com.cmart.DB.CassandraDBQuery.java
public ArrayList<Bid> getOldBids(long userID, long ts) { ArrayList<Bid> bids = new ArrayList<Bid>(); if (userID < 1) return bids; int attemptsRemaining = SQL_RETRIES; ArrayList<Long> itemIDs = new ArrayList<Long>(); do {/*from www . j a v a 2s . c o m*/ Connection conn = this.getConnection(); if (conn != null) { PreparedStatement statement = null; try { statement = conn.prepareStatement( "SELECT * FROM maxoldbids WHERE userid = " + userID + " AND ts>=" + ts); ResultSet rs = statement.executeQuery(); User user = null; while (rs.next()) { //Item currentItem; long ui = 0; try { ui = rs.getLong("userid"); } catch (Exception e) { ui = 0; } if (ui > 0) { if (user == null) user = getUser(rs.getLong("userid")); Bid currentBid = new Bid(rs.getLong("bidkey"), rs.getLong("userid"), (int) rs.getLong("quantity"), rs.getDouble("bid"), rs.getDouble("maxbid"), new Date(rs.getLong("biddate")), null, user); bids.add(currentBid); // Get the itemid to get and set the bid item id long itemID = rs.getLong("itemid"); currentBid.setItemID(itemID); itemIDs.add(itemID); } } rs.close(); // Get the items HashMap<Long, Item> items = getItemsByID(itemIDs, "olditems"); // Put the items in the bids for (Bid b : bids) { if (items.containsKey(b.getItemID())) b.setItem(items.get(b.getItemID())); } attemptsRemaining = 0; } catch (CommunicationsException e) { this.forceCloseConnection(conn); this.checkConnections(); } catch (MySQLNonTransientConnectionException e) { this.forceCloseConnection(conn); this.checkConnections(); } catch (Exception e) { System.out.println("CassandraQuery (getOldBids): Could not get the bids from old"); e.printStackTrace(); } finally { this.closeSmt(statement); this.closeConnection(conn); } } attemptsRemaining--; } while (attemptsRemaining >= 0); return bids; }
From source file:com.cmart.DB.CassandraDBQuery.java
protected Boolean buyItemNowDB(long userID, long itemID, int quantity, Account account, Address address) { if (userID < 1 || itemID < 1) return Boolean.FALSE; if (account == null || address == null) return Boolean.FALSE; boolean purchased = false; Connection conn = this.getConnection(); if (conn != null && account != null) { PreparedStatement getQuantityStatement = null; PreparedStatement deleteBidsStatement = null; PreparedStatement purchaseItemStatement = null; PreparedStatement paymentItemStatement = null; PreparedStatement setQuantityStatement = null; PreparedStatement getStatement = null; PreparedStatement copyItemStatement = null; PreparedStatement deleteItemStatement = null; PreparedStatement copyBidsStatement = null; PreparedStatement getBidsStatement = null; PreparedStatement getMaxBidsStatement = null; PreparedStatement deleteMaxBidsStatement = null; PreparedStatement deleteItemPrice = null; try {/*from w w w . ja v a 2s . c o m*/ /* * 1. Make suer there is enough quantity * 2. insert payment * 3. update the items quantity * 4. insert purchase * 5. if the quantity is now zero * move item to old * move bids to old * move max old bids * delete from itemprice and revitemprice and revitem */ // Check there is sufficient quantity getQuantityStatement = conn .prepareStatement("SELECT quantity, buynowprice FROM items WHERE KEY=" + itemID); ResultSet rs = getQuantityStatement.executeQuery(); if (rs.next()) { // Make sure the row is real long quant = 0; try { quant = rs.getLong("quantity"); } catch (Exception e) { quant = 0; } if (quant > 0) { // Get the quantity and price int dbQuantity = (int) rs.getLong("quantity"); double dbBuyNowPrice = rs.getDouble("buynowprice"); rs.close(); // If there is sufficient quantity, add the payment and purchase // then look in to decreasing the quantity if ((dbQuantity - quantity) >= 0 && dbBuyNowPrice > 0.0) { // MAKE KEY long key1 = System.currentTimeMillis() * shortback + System.nanoTime() % shortback; purchaseItemStatement = conn.prepareStatement( "INSERT INTO purchased (KEY,userid, itemid, quantity, price, purchasedate, accountid, paid, paiddate, ts) " + "VALUES (" + key1 + "," + userID + "," + itemID + "," + quantity + ",'" + (dbBuyNowPrice * quantity) + "'," + System.currentTimeMillis() + "," + account.getAccountID() + ",'true'," + System.currentTimeMillis() + "," + System.currentTimeMillis() + ") USING CONSISTENCY " + consistency); paymentItemStatement = conn.prepareStatement( "INSERT INTO payments (KEY, userid, itemid, quantity, price, paiddate, street, town, zip, state, nameoncard, creditcardno, cvv, expirationdate) " + "VALUES (" + key1 + "," + userID + "," + itemID + "," + quantity + ",'" + (dbBuyNowPrice * quantity) + "'," + System.currentTimeMillis() + ",'" + safe(address.getStreet()) + "','" + safe(address.getTown()) + "','" + address.getZip() + "','" + safe(getStateName(address.getState())) + "','" + safe(account.getNameOnCard()) + "','" + account.getCreditCardNo() + "','" + account.getCVV() + "'," + account.getExpirationDate().getTime() + ") USING CONSISTENCY " + consistency); paymentItemStatement.executeUpdate(); purchaseItemStatement.executeUpdate(); purchased = true; // Now update the item quantity setQuantityStatement = conn.prepareStatement("UPDATE items USING CONSISTENCY " + consistency + " SET quantity=" + (dbQuantity - quantity) + ", ts=" + System.currentTimeMillis() + " WHERE KEY=" + itemID); setQuantityStatement.executeUpdate(); } // If there are no more of the item, move the item and the bids to the old tables if (purchased && dbQuantity - quantity <= 0) { Long pikey = null; // Copy the item if it still exists getStatement = conn.prepareStatement("SELECT * FROM items WHERE KEY=" + itemID); ResultSet getRs = getStatement.executeQuery(); // Make sure it is real if (getRs.next()) { long tempid2 = 0; try { tempid2 = getRs.getLong("sellerid"); pikey = getRs.getLong("pikey"); } catch (Exception e) { } if (tempid2 != 0) { copyItemStatement = conn.prepareStatement( "INSERT INTO olditems (KEY,itemid,name,description,quantity,startprice,reserveprice,buynowprice,curbid,maxbid,noofbids,startdate,enddate,sellerid,categoryid,thumbnail,ts)" + " VALUES (" + itemID + "," + itemID + ",'" + safe(getRs.getString("name")) + "','" + safe(getRs.getString("description")) + "'," + getRs.getLong("quantity") + ",'" + getRs.getString("startprice") + "','" + getRs.getString("reserveprice") + "','" + getRs.getString("buynowprice") + "','" + getRs.getString("curbid") + "','" + getRs.getString("maxbid") + "'," + getRs.getLong("noofbids") + "," + getRs.getLong("startdate") + "," + getRs.getLong("enddate") + "," + getRs.getLong("sellerid") + "," + getRs.getLong("categoryid") + ",'" + getRs.getString("thumbnail") + "'," + System.currentTimeMillis() + ") USING CONSISTENCY " + consistency); copyItemStatement.executeUpdate(); deleteItemStatement = conn .prepareStatement("DELETE FROM items WHERE KEY =" + itemID); deleteItemStatement.executeUpdate(); } } getRs.close(); // Copy the bids, then delete them getBidsStatement = conn.prepareStatement("SELECT * FROM bids WHERE itemid=" + itemID); ResultSet bidsRs = getBidsStatement.executeQuery(); StringBuffer toOldCQL = new StringBuffer(); // CQL TO INSERT IN TO 'OLDBIDS' toOldCQL.append("BEGIN BATCH\n "); boolean added = false; StringBuffer deleteIDs = new StringBuffer(); // TO DELETE COPIED ROWS deleteIDs.append("'0'"); // Add all of the bids to move while (bidsRs.next()) { long tempid3 = 0; try { tempid3 = bidsRs.getLong("userid"); } catch (Exception e) { } if (tempid3 > 0) { added = true; toOldCQL.append( "INSERT INTO oldbids (KEY,bid,biddate,itemid,maxbid,quantity,userid,ts) VALUES ('" + bidsRs.getLong("KEY") + "','" + bidsRs.getString("bid") + "'," + bidsRs.getLong("biddate") + "," + bidsRs.getLong("itemid") + ",'" + bidsRs.getString("maxbid") + "','" + bidsRs.getString("quantity") + "'," + bidsRs.getLong("userid") + "," + System.currentTimeMillis() + ");\n"); deleteIDs.append(",'" + bidsRs.getLong("KEY") + "'"); } } bidsRs.close(); // Add all of the maxbids to move getMaxBidsStatement = conn .prepareStatement("SELECT * FROM maxbids WHERE itemid=" + itemID); ResultSet maxBidsRs = getMaxBidsStatement.executeQuery(); StringBuffer deleteMaxIDs = new StringBuffer(); deleteMaxIDs.append("'0'"); // Add all of the maxbids to move while (maxBidsRs.next()) { long tempid3 = 0; try { tempid3 = maxBidsRs.getLong("userid"); } catch (Exception e) { } if (tempid3 > 0) { added = true; toOldCQL.append( "INSERT INTO maxoldbids (KEY,bidkey,bid,biddate,itemid,maxbid,quantity,userid,ts) VALUES ('" + maxBidsRs.getString("KEY") + "','" + maxBidsRs.getLong("bidkey") + "','" + maxBidsRs.getString("bid") + "'," + maxBidsRs.getLong("biddate") + "," + maxBidsRs.getLong("itemid") + ",'" + maxBidsRs.getString("maxbid") + "','" + maxBidsRs.getString("quantity") + "'," + maxBidsRs.getLong("userid") + "," + System.currentTimeMillis() + ");\n"); deleteMaxIDs.append(",'" + maxBidsRs.getString("KEY") + "'"); } } maxBidsRs.close(); toOldCQL.append("\nAPPLY BATCH;"); // finish the insert batch // If there are bids/max bids to move, do it if (added) { copyBidsStatement = conn.prepareStatement(toOldCQL.toString()); copyBidsStatement.executeUpdate(); deleteBidsStatement = conn.prepareStatement( "DELETE FROM bids WHERE KEY IN (" + deleteIDs.toString() + ")"); deleteBidsStatement.executeUpdate(); deleteMaxBidsStatement = conn.prepareStatement( "DELETE FROM maxbids WHERE KEY IN (" + deleteMaxIDs.toString() + ")"); deleteMaxBidsStatement.executeUpdate(); } // Delete the other sorting things deleteItemPrice = conn.prepareStatement("DELETE FROM priceitems WHERE KEY = " + pikey); deleteItemPrice.executeUpdate(); deleteItemPrice.close(); deleteItemPrice = conn.prepareStatement( "DELETE FROM revpriceitems WHERE KEY = " + (Long.MAX_VALUE - pikey)); deleteItemPrice.executeUpdate(); deleteItemPrice.close(); deleteItemPrice = conn.prepareStatement( "DELETE FROM revtimeitems WHERE KEY = " + (Long.MAX_VALUE - itemID)); deleteItemPrice.executeUpdate(); } } } else { // This should happen only in a race condition System.err.println( "someone has bought the item between your request or the buy now price is zero!"); } } catch (Exception e) { System.err.println("CassandraQuery (buyItemNow): Could not insert purchase"); e.printStackTrace(); } finally { this.closeSmt(getQuantityStatement); this.closeSmt(deleteBidsStatement); this.closeSmt(deleteMaxBidsStatement); this.closeSmt(purchaseItemStatement); this.closeSmt(paymentItemStatement); this.closeSmt(setQuantityStatement); this.closeSmt(getStatement); this.closeSmt(copyItemStatement); this.closeSmt(deleteItemStatement); this.closeSmt(getBidsStatement); this.closeSmt(getMaxBidsStatement); this.closeSmt(copyBidsStatement); this.closeSmt(deleteItemPrice); this.closeConnection(conn); } } return purchased; }
From source file:com.cmart.DB.CassandraDBQuery.java
public ArrayList<Item> getCurrentSellingItems(long userID, long ts) { ArrayList<Item> items = new ArrayList<Item>(); if (userID < 1) return items; int attemptsRemaining = SQL_RETRIES; do {//from w w w.ja v a2 s . co m Connection conn = this.getConnection(); if (conn != null) { PreparedStatement statement = null; try { // Create the SQL statement to get items the user is selling statement = conn .prepareStatement("SELECT * FROM items WHERE sellerid=" + userID + " AND ts>=" + ts); ResultSet rs = statement.executeQuery(); while (rs.next()) { // Make sure they are real long seller = 0; try { Long temp = rs.getLong("sellerid"); if (temp != null) seller = temp; } catch (Exception e) { } if (seller != 0) { ArrayList<Image> images = this.getItemImages(rs.getLong("itemid")); Item currentItem = new Item(rs.getLong("itemid"), rs.getString("name"), rs.getString("description"), rs.getInt("quantity"), rs.getDouble("startprice"), rs.getDouble("reserveprice"), rs.getDouble("buynowprice"), rs.getDouble("curbid"), rs.getDouble("maxbid"), rs.getInt("noofbids"), new Date(rs.getLong("startdate")), new Date(rs.getLong("enddate")), rs.getLong("sellerid"), rs.getLong("categoryid"), rs.getString("thumbnail"), images); items.add(currentItem); } } rs.close(); attemptsRemaining = 0; } catch (CommunicationsException e) { this.forceCloseConnection(conn); this.checkConnections(); } catch (MySQLNonTransientConnectionException e) { this.forceCloseConnection(conn); this.checkConnections(); } catch (Exception e) { System.out.println("CassandraQuery (getCurrentSellingItems): Could not get the items"); e.printStackTrace(); } finally { this.closeSmt(statement); this.closeConnection(conn); } } attemptsRemaining--; } while (attemptsRemaining >= 0); return items; }
From source file:com.cmart.DB.CassandraDBQuery.java
public ArrayList<Bid> getCurrentBids(long userID, long ts) { ArrayList<Bid> bids = new ArrayList<Bid>(); if (userID < 1) return bids; int attemptsRemaining = SQL_RETRIES; ArrayList<Long> itemIDs = new ArrayList<Long>(); do {/* ww w. j ava 2s.c o m*/ Connection conn = this.getConnection(); if (conn != null) { PreparedStatement statement = null; try { statement = conn .prepareStatement("SELECT * FROM maxbids WHERE userid = " + userID + " AND ts>=" + ts); ResultSet rs = statement.executeQuery(); User user = null; // Make the bids while (rs.next()) { long ui = 0; try { ui = rs.getLong("userid"); } catch (Exception e) { ui = 0; } if (ui > 0) { if (user == null) user = getUser(rs.getLong("userid")); // We'll add items after Bid currentBid = new Bid(rs.getLong("bidkey"), rs.getLong("userid"), (int) rs.getLong("quantity"), rs.getDouble("bid"), rs.getDouble("maxbid"), new Date(rs.getLong("biddate")), null, user); bids.add(currentBid); // Set the bid itemID and add it of the list of IDs to get long itemID = rs.getLong("itemid"); currentBid.setItemID(itemID); itemIDs.add(itemID); } } // Now get the items for those bids rs.close(); // Get the items HashMap<Long, Item> items = getItemsByID(itemIDs, "items"); // Put the items in the bids for (Bid b : bids) { if (items.containsKey(b.getItemID())) b.setItem(items.get(b.getItemID())); } attemptsRemaining = 0; } catch (CommunicationsException e) { this.forceCloseConnection(conn); this.checkConnections(); } catch (MySQLNonTransientConnectionException e) { this.forceCloseConnection(conn); this.checkConnections(); } catch (Exception e) { System.out.println("CassandraQuery (getCurrentBids): Could not get the bids from bids"); e.printStackTrace(); } finally { this.closeSmt(statement); this.closeConnection(conn); } } attemptsRemaining--; } while (attemptsRemaining >= 0); return bids; }
From source file:com.cmart.DB.CassandraDBQuery.java
public ArrayList<Bid> getBids(long itemID) { ArrayList<Bid> bids = new ArrayList<Bid>(); if (itemID < 1) return bids; int attemptsRemaining = SQL_RETRIES; ArrayList<Long> itemIDs = new ArrayList<Long>(); do {/* w w w. j av a2 s . com*/ Connection conn = this.getConnection(); if (conn != null) { PreparedStatement statement = null; try { for (int i = 0; i <= 1; i++) { String table = null; String itemTable = null; // We don't know if it is new or old, so try both if (i == 0) { table = "bids"; itemTable = "items"; } else if (i == 1) { table = "oldBids"; itemTable = "oldItems"; } statement = conn.prepareStatement("SELECT * FROM " + table + " WHERE itemid = " + itemID); ResultSet rs = statement.executeQuery(); User user = null; while (rs.next()) { //Item currentItem; long ui = 0; try { ui = rs.getLong("userid"); } catch (Exception e) { ui = 0; } if (ui > 0) { if (user == null) user = getUser(rs.getLong("userid")); Bid currentBid = new Bid(rs.getLong("bidkey"), rs.getLong("userid"), (int) rs.getLong("quantity"), rs.getDouble("bid"), rs.getDouble("maxbid"), new Date(rs.getLong("biddate")), null, user); bids.add(currentBid); // Set the bid item id and add the item to get from the db currentBid.setItemID(itemID); itemIDs.add(itemID); } } rs.close(); // Get the items HashMap<Long, Item> items = getItemsByID(itemIDs, itemTable); // Put the items in the bids for (Bid b : bids) { if (items.containsKey(b.getItemID())) b.setItem(items.get(b.getItemID())); } } attemptsRemaining = 0; } catch (CommunicationsException e) { this.forceCloseConnection(conn); this.checkConnections(); } catch (MySQLNonTransientConnectionException e) { this.forceCloseConnection(conn); this.checkConnections(); } catch (Exception e) { System.out.println("CassandraDBQuery (getBids): Could not get the bids from bids or oldBids"); e.printStackTrace(); } finally { this.closeSmt(statement); this.closeConnection(conn); } } attemptsRemaining--; } while (attemptsRemaining >= 0); return bids; }
From source file:com.cmart.DB.CassandraDBQuery.java
public ArrayList<Purchase> getPurchases(long userID, long ts) { ArrayList<Purchase> purchases = new ArrayList<Purchase>(); if (userID < 1) return purchases; int attemptsRemaining = SQL_RETRIES; do {// w w w . ja v a 2 s . c o m Connection conn = this.getConnection(); if (conn != null) { PreparedStatement statement = null; PreparedStatement itemstatement = null; try { // Get the purchases statement = conn.prepareStatement( "SELECT itemid FROM purchased WHERE userid=" + userID + " AND ts>=" + ts); ResultSet rs = statement.executeQuery(); // Get ids of items we need StringBuilder ids = new StringBuilder(); ids.append("('0"); while (rs.next()) { ids.append("','"); ids.append(rs.getLong("itemid")); } ids.append("')"); rs.close(); statement.close(); // Get the items itemstatement = conn.prepareStatement("SELECT * FROM olditems WHERE KEY IN " + ids.toString()); ResultSet itemrs = itemstatement.executeQuery(); TreeMap<Long, Item> items = new TreeMap<Long, Item>(); while (itemrs.next()) { String name = null; try { name = itemrs.getString("name"); } catch (Exception e) { } if (name != null) { ArrayList<Image> images = this.getItemImages(itemrs.getLong("KEY")); Item currentItem = new Item(itemrs.getLong("KEY"), name, new String(itemrs.getString("description")), new Integer((int) itemrs.getLong("quantity")), itemrs.getDouble("startprice"), itemrs.getDouble("reserveprice"), itemrs.getDouble("buynowprice"), itemrs.getDouble("curbid"), itemrs.getDouble("maxbid"), new Integer((int) itemrs.getLong("noofbids")), new Date(itemrs.getLong("startdate")), new Date(itemrs.getLong("enddate")), itemrs.getLong("sellerid"), itemrs.getLong("categoryid"), new String(itemrs.getString("thumbnail")), images); items.put(itemrs.getLong("KEY"), currentItem); } } itemrs.close(); itemstatement.close(); // Get the purchases and make them statement = conn .prepareStatement("SELECT * FROM purchased WHERE userid=" + userID + " AND ts>=" + ts); rs = statement.executeQuery(); while (rs.next()) { // Make sure it is real long pKey = 0; try { Long temp = rs.getLong("KEY"); pKey = temp; } catch (Exception e) { } if (pKey > 0) { purchases.add(new Purchase(rs.getLong("KEY"), items.get(rs.getLong("itemid")), (int) rs.getLong("quantity"), rs.getDouble("price"), new Boolean(rs.getString("paid")))); } } rs.close(); attemptsRemaining = 0; } catch (CommunicationsException e) { this.forceCloseConnection(conn); this.checkConnections(); } catch (MySQLNonTransientConnectionException e) { this.forceCloseConnection(conn); this.checkConnections(); } catch (Exception e) { System.out.println("MySQLQuery (getPurchases): Could not get the purchases"); e.printStackTrace(); } finally { this.closeSmt(statement); this.closeConnection(conn); } } attemptsRemaining--; } while (attemptsRemaining >= 0); return purchases; }
From source file:Manager.managerFunctions.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./* w ww . j a va2 s . co m*/ * * @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 { //json to pass back to our ajax request JSONArray jsonArray = new JSONArray(); Connection con = null; try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); con = DriverManager .getConnection("jdbc:sqlserver://localhost;user=sa;password=nopw;allowMultiQueries=true"); Statement st = con.createStatement(); if (request.getParameter("func").equals("getRev")) { String query = "SELECT * FROM [MatchesFromAbove].[dbo].[DATE] WHERE DATEPART(month, DATE_TIME) = " + request.getParameter("month") + " AND DATEPART(year, DATE_TIME) = " + request.getParameter("year"); ResultSet rs = st.executeQuery(query); //loop through result set and create the json objects while (rs.next()) { JSONObject dateToAdd = new JSONObject(); dateToAdd.put("fee", rs.getString("Fee")); dateToAdd.put("time", rs.getDate("Date_Time").toString()); //add the json object that we're passing into the json array jsonArray.add(dateToAdd); } //set the content type of our response response.setContentType("application/json"); //printout prints it to our ajax call and it shows up there as data. you can use this data in the success function. PrintWriter printout = response.getWriter(); printout.print(jsonArray); printout.flush(); System.out.println("rev generated"); } if (request.getParameter("func").equals("getRevByDate")) { double totalRev = 0; String[] arrr = request.getParameter("date").split("/"); String query = "SELECT * FROM [MatchesFromAbove].[dbo].[DATE] WHERE DATEPART(month, DATE_TIME) = " + arrr[0] + " AND DATEPART(year, DATE_TIME) = " + arrr[2] + " AND DATEPART(day, DATE_TIME) = " + arrr[1]; ResultSet rs = st.executeQuery(query); //loop through result set and create the json objects while (rs.next()) { totalRev = totalRev + Double.parseDouble(rs.getString("Fee")); JSONObject dateToAdd = new JSONObject(); dateToAdd.put("fee", rs.getString("Fee")); dateToAdd.put("time", rs.getDate("Date_Time").toString()); //add the json object that we're passing into the json array jsonArray.add(dateToAdd); } JSONObject dateToAdd = new JSONObject(); dateToAdd.put("total", totalRev); jsonArray.add(dateToAdd); //set the content type of our response response.setContentType("application/json"); //printout prints it to our ajax call and it shows up there as data. you can use this data in the success function. PrintWriter printout = response.getWriter(); printout.print(jsonArray); printout.flush(); } if (request.getParameter("func").equals("getRevBySSN")) { double totalRev = 0; String ssn = request.getParameter("SSN"); String query = "SELECT *" + "FROM [MatchesFromAbove].[dbo].[DATE],[MatchesFromAbove].[dbo].[Profile]" + "WHERE ([MatchesFromAbove].[dbo].[DATE].Profile1Id = [MatchesFromAbove].[dbo].[Profile].ProfileId AND [MatchesFromAbove].[dbo].[Profile].OwnerSSN ='" + ssn + "') OR " + "([MatchesFromAbove].[dbo].[DATE].Profile2Id = [MatchesFromAbove].[dbo].[Profile].ProfileId AND [MatchesFromAbove].[dbo].[Profile].OwnerSSN ='" + ssn + "')"; ResultSet rs = st.executeQuery(query); //loop through result set and create the json objects while (rs.next()) { totalRev = totalRev + Double.parseDouble(rs.getString("Fee")); JSONObject dateToAdd = new JSONObject(); dateToAdd.put("fee", rs.getString("Fee")); dateToAdd.put("time", rs.getDate("Date_Time").toString()); //add the json object that we're passing into the json array jsonArray.add(dateToAdd); } JSONObject dateToAdd = new JSONObject(); dateToAdd.put("total", totalRev); jsonArray.add(dateToAdd); //set the content type of our response response.setContentType("application/json"); //printout prints it to our ajax call and it shows up there as data. you can use this data in the success function. PrintWriter printout = response.getWriter(); printout.print(jsonArray); printout.flush(); } if (request.getParameter("func").equals("getBestRep")) { try { st.execute( "CREATE VIEW booob AS SELECT [MatchesFromAbove].[dbo].[DATE].CustomerRep, SUM([MatchesFromAbove].[dbo].[DATE].Fee) AS sumFee FROM [MatchesFromAbove].[dbo].[DATE] GROUP BY [MatchesFromAbove].[dbo].[DATE].CustomerRep"); } catch (Exception e) { System.out.println("view exists "); st.execute("DROP VIEW booob"); st.execute( "CREATE VIEW booob AS SELECT [MatchesFromAbove].[dbo].[DATE].CustomerRep, SUM([MatchesFromAbove].[dbo].[DATE].Fee) AS sumFee FROM [MatchesFromAbove].[dbo].[DATE] GROUP BY [MatchesFromAbove].[dbo].[DATE].CustomerRep"); } String query = "SELECT * FROM booob"; ResultSet rs = st.executeQuery(query); //loop through result set and create the json objects Double max = 0.0; String name = ""; while (rs.next()) { double b = rs.getDouble("sumFee"); System.out.println(b + " " + rs.getString("CustomerRep")); if (b > max) { max = b; name = rs.getString("CustomerRep"); } } String query2 = "SELECT * FROM PERSON WHERE SSN = '" + name + "'"; ResultSet rs2 = st.executeQuery(query2); while (rs2.next()) { name = (rs2).getString("FirstName") + " " + (rs2).getString("LastName"); } //set the content type of our response response.setContentType("text/html"); //printout prints it to our ajax call and it shows up there as data. you can use this data in the success function. PrintWriter printout = response.getWriter(); printout.print("NAME: " + name + " Revenue Generated: $" + (long) (max * 100 + 0.5) / 100.0); printout.flush(); } if (request.getParameter("func").equals("getBestCust")) { ResultSet rs = st.executeQuery( " SELECT [MatchesFromAbove].[dbo].[PROFILE].OwnerSSN, SUM([MatchesFromAbove].[dbo].[DATE].Fee) AS sumFee " + " FROM [MatchesFromAbove].[dbo].[PROFILE], [MatchesFromAbove].[dbo].[DATE] " + " WHERE [MatchesFromAbove].[dbo].[DATE].Profile1Id = [MatchesFromAbove].[dbo].[PROFILE].ProfileId " + " GROUP BY OwnerSSN "); Map<String, Integer> my = new HashMap<String, Integer>(); while (rs.next()) { System.out.println("tack"); if (!my.containsKey(rs.getString("OwnerSSN"))) { my.put(rs.getString("OwnerSSN"), rs.getInt("sumFee")); } else { my.put(rs.getString("OwnerSSN"), (rs.getInt("sumFee")) + my.get(rs.getString("OwnerSSN"))); } System.out.println("2"); } ResultSet rs1 = st.executeQuery( " SELECT [MatchesFromAbove].[dbo].[PROFILE].OwnerSSN, SUM([MatchesFromAbove].[dbo].[DATE].Fee) AS sumFee " + " FROM [MatchesFromAbove].[dbo].[PROFILE], [MatchesFromAbove].[dbo].[DATE] " + " WHERE [MatchesFromAbove].[dbo].[DATE].Profile2Id = [MatchesFromAbove].[dbo].[PROFILE].ProfileId " + " GROUP BY OwnerSSN "); System.out.println("lalaland"); if (rs1 != null) while (rs1.next()) { System.out.println("tack"); if (!my.containsKey(rs1.getString("OwnerSSN"))) { my.put(rs1.getString("OwnerSSN"), rs1.getInt("sumFee")); } else { my.put(rs1.getString("OwnerSSN"), (rs1.getInt("sumFee")) + my.get(rs1.getString("OwnerSSN"))); } System.out.println("2"); } String ssn = ""; int max = 0; Iterator it = my.entrySet().iterator(); while (it.hasNext()) { Map.Entry pairs = (Map.Entry) it.next(); System.out.println(pairs.getKey() + " " + pairs.getValue()); if ((Integer) pairs.getValue() > max) { ssn = (String) pairs.getKey(); max = (Integer) pairs.getValue(); } it.remove(); // avoids a ConcurrentModificationException } String query = "SELECT * FROM PERSON WHERE SSN = '" + ssn + "'"; ResultSet t = st.executeQuery(query); t.next(); String name = t.getString("FirstName") + " " + t.getString("LastName"); System.out.println(my); //set the content type of our response response.setContentType("text/html"); //printout prints it to our ajax call and it shows up there as data. you can use this data in the success function. PrintWriter printout = response.getWriter(); printout.print("NAME: " + name + " Revenue Generated: $" + max); printout.flush(); } if (request.getParameter("func").equals("bestRatedCust")) { String query = "SELECT * FROM Customer C WHERE Rating > 3"; ResultSet rs = st.executeQuery(query); response.setContentType("text/html"); //printout prints it to our ajax call and it shows up there as data. you can use this data in the success function. PrintWriter printout = response.getWriter(); //loop through result set and create the json objects Double max = 0.0; String name = ""; String rating = ""; while (rs.next()) { name = rs.getString("SSN"); rating = rs.getString("Rating"); String query2 = "SELECT * FROM PERSON WHERE SSN = '" + name + "'"; ResultSet rs2 = st.executeQuery(query2); while (rs2.next()) { name = (rs2).getString("FirstName") + " " + (rs2).getString("LastName"); printout.print("<p>NAME: " + name + " Rating for Customer is: " + rating + "</p>"); } } } if (request.getParameter("func").equals("bestDateDays")) { String query = "SELECT CAST([MatchesFromAbove].[dbo].[DATE].Date_Time AS DATE) as Date, SUM([MatchesFromAbove].[dbo].[DATE].User1Rating+[MatchesFromAbove].[dbo].[DATE].User2Rating) as Score FROM [MatchesFromAbove].[dbo].[PROFILE], [MatchesFromAbove].[dbo].[DATE] GROUP BY CAST([MatchesFromAbove].[dbo].[DATE].Date_Time AS DATE) HAVING (SUM([MatchesFromAbove].[dbo].[DATE].User1Rating+[MatchesFromAbove].[dbo].[DATE].User2Rating) >= 1) ORDER BY Score DESC"; ResultSet rs = st.executeQuery(query); response.setContentType("text/html"); //printout prints it to our ajax call and it shows up there as data. you can use this data in the success function. PrintWriter printout = response.getWriter(); int i = 0; while (rs.next()) { i++; if (i == 4) { break; } printout.print("<p>RANK: " + i + "________ DAY FOR DATE: " + rs.getString("Date").substring(5) + "________ SCORE FOR THIS DAY: " + rs.getString("Score")); } //set the content type of our response printout.flush(); } if (request.getParameter("func").equals("mostActCust")) { String query; query = "SELECT [MatchesFromAbove].[dbo].[PROFILE].OwnerSSN, COUNT(*) AS numDates\n" + " FROM [MatchesFromAbove].[dbo].[PROFILE], [MatchesFromAbove].[dbo].[DATE]\n" + " WHERE [MatchesFromAbove].[dbo].[DATE].Profile1Id = [MatchesFromAbove].[dbo].[PROFILE].ProfileId\n" + " GROUP BY OwnerSSN\n" + " UNION ALL\n" + " SELECT [MatchesFromAbove].[dbo].[PROFILE].OwnerSSN, COUNT(*) AS numDates\n" + " FROM [MatchesFromAbove].[dbo].[PROFILE], [MatchesFromAbove].[dbo].[DATE]\n" + " WHERE [MatchesFromAbove].[dbo].[DATE].Profile2Id = [MatchesFromAbove].[dbo].[PROFILE].ProfileId\n" + " GROUP BY OwnerSSN"; System.out.println(query); ResultSet rs = st.executeQuery(query); Map<String, Integer> my = new HashMap<String, Integer>(); while (rs.next()) { if (!my.containsKey(rs.getString("OwnerSSN"))) { my.put(rs.getString("OwnerSSN"), 1); } else { my.put(rs.getString("OwnerSSN"), (Integer) my.get(rs.getString("OwnerSSN")) + 1); } } System.out.println(my); query = "SELECT [MatchesFromAbove].[dbo].[PROFILE].OwnerSSN, COUNT(*) AS numLikes\n" + " FROM [MatchesFromAbove].[dbo].[PROFILE], [MatchesFromAbove].[dbo].[LIKES]\n" + " WHERE [MatchesFromAbove].[dbo].[LIKES].LikerId = [MatchesFromAbove].[dbo].[PROFILE].ProfileId\n" + " GROUP BY OwnerSSN"; rs = st.executeQuery(query); while (rs.next()) { if (!my.containsKey(rs.getString("OwnerSSN"))) { my.put(rs.getString("OwnerSSN"), 1); } else { my.put(rs.getString("OwnerSSN"), (Integer) my.get(rs.getString("OwnerSSN")) + 1); } } response.setContentType("application/json"); //printout prints it to our ajax call and it shows up there as data. you can use this data in the success function. PrintWriter printout = response.getWriter(); // String s = " buns"; // while (rs.next()) { // System.out.println("bub"); // s = s+" "+ rs.getString("OwnerSSN"); // // } Iterator it = my.entrySet().iterator(); while (it.hasNext()) { Map.Entry pairs = (Map.Entry) it.next(); JSONObject dateToAdd = new JSONObject(); query = "SELECT * FROM PERSON WHERE SSN = '" + pairs.getKey() + "'"; ResultSet t = st.executeQuery(query); t.next(); dateToAdd.put("name", t.getString("FirstName") + " " + t.getString("LastName")); dateToAdd.put("level", pairs.getValue()); if ((Integer) pairs.getValue() > 1) jsonArray.add(dateToAdd); it.remove(); // avoids a ConcurrentModificationException } printout.print(jsonArray); //set the content type of our response printout.flush(); } con.close(); } catch (Exception e) { System.out.println(e.getMessage() + "managerFuncetionsClass"); if (con != null) try { con.close(); } catch (SQLException ex) { Logger.getLogger(managerFunctions.class.getName()).log(Level.SEVERE, null, ex); } } }
From source file:net.sourceforge.msscodefactory.v1_10.MSSBamPg8.MSSBamPg8TableDoubleTable.java
protected MSSBamTableDoubleBuff unpackTableDoubleResultSetToBuff(ResultSet resultSet) throws SQLException { final String S_ProcName = "unpackTableDoubleResultSetToBuff"; int idxcol = 1; String classCode = resultSet.getString(idxcol); idxcol++;// ww w . j a va 2 s .c om MSSBamTableDoubleBuff buff; if (classCode.equals("TDBL")) { buff = schema.getFactoryTableDouble().newBuff(); } else { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Unrecognized class code \"" + classCode + "\""); } buff.setRequiredId(resultSet.getLong(idxcol)); idxcol++; buff.setRequiredTenantId(resultSet.getLong(idxcol)); idxcol++; { long colVal = resultSet.getLong(idxcol); if (resultSet.wasNull()) { buff.setOptionalScopeId(null); } else { buff.setOptionalScopeId(colVal); } } idxcol++; buff.setRequiredName(resultSet.getString(idxcol)); idxcol++; { String colVal = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setOptionalShortName(null); } else { buff.setOptionalShortName(colVal); } } idxcol++; { String colVal = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setOptionalLabel(null); } else { buff.setOptionalLabel(colVal); } } idxcol++; { String colVal = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setOptionalShortDescription(null); } else { buff.setOptionalShortDescription(colVal); } } idxcol++; { String colVal = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setOptionalDescription(null); } else { buff.setOptionalDescription(colVal); } } idxcol++; { long colVal = resultSet.getLong(idxcol); if (resultSet.wasNull()) { buff.setOptionalAuthorId(null); } else { buff.setOptionalAuthorId(colVal); } } idxcol++; buff.setRequiredValueContainerId(resultSet.getLong(idxcol)); idxcol++; buff.setRequiredIsNullable(resultSet.getBoolean(idxcol)); idxcol++; { boolean colVal = resultSet.getBoolean(idxcol); if (resultSet.wasNull()) { buff.setOptionalGenerateId(null); } else { buff.setOptionalGenerateId(colVal); } } idxcol++; { short colVal = resultSet.getShort(idxcol); if (resultSet.wasNull()) { buff.setOptionalDataScopeId(null); } else { buff.setOptionalDataScopeId(colVal); } } idxcol++; { short colVal = resultSet.getShort(idxcol); if (resultSet.wasNull()) { buff.setOptionalViewAccessSecurityId(null); } else { buff.setOptionalViewAccessSecurityId(colVal); } } idxcol++; { short colVal = resultSet.getShort(idxcol); if (resultSet.wasNull()) { buff.setOptionalEditAccessSecurityId(null); } else { buff.setOptionalEditAccessSecurityId(colVal); } } idxcol++; { short colVal = resultSet.getShort(idxcol); if (resultSet.wasNull()) { buff.setOptionalViewAccessFrequencyId(null); } else { buff.setOptionalViewAccessFrequencyId(colVal); } } idxcol++; { short colVal = resultSet.getShort(idxcol); if (resultSet.wasNull()) { buff.setOptionalEditAccessFrequencyId(null); } else { buff.setOptionalEditAccessFrequencyId(colVal); } } idxcol++; { long colVal = resultSet.getLong(idxcol); if (resultSet.wasNull()) { buff.setOptionalPrevId(null); } else { buff.setOptionalPrevId(colVal); } } idxcol++; { long colVal = resultSet.getLong(idxcol); if (resultSet.wasNull()) { buff.setOptionalNextId(null); } else { buff.setOptionalNextId(colVal); } } idxcol++; { String colVal = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setOptionalDbName(null); } else { buff.setOptionalDbName(colVal); } } idxcol++; { double colVal = resultSet.getDouble(idxcol); if (resultSet.wasNull()) { buff.setOptionalInitValue(null); } else { buff.setOptionalInitValue(colVal); } } idxcol++; { double colVal = resultSet.getDouble(idxcol); if (resultSet.wasNull()) { buff.setOptionalDefaultValue(null); } else { buff.setOptionalDefaultValue(colVal); } } idxcol++; { double colVal = resultSet.getDouble(idxcol); if (resultSet.wasNull()) { buff.setOptionalMinValue(null); } else { buff.setOptionalMinValue(colVal); } } idxcol++; { double colVal = resultSet.getDouble(idxcol); if (resultSet.wasNull()) { buff.setOptionalMaxValue(null); } else { buff.setOptionalMaxValue(colVal); } } idxcol++; { double colVal = resultSet.getDouble(idxcol); if (resultSet.wasNull()) { buff.setOptionalNullValue(null); } else { buff.setOptionalNullValue(colVal); } } idxcol++; { double colVal = resultSet.getDouble(idxcol); if (resultSet.wasNull()) { buff.setOptionalUnknownValue(null); } else { buff.setOptionalUnknownValue(colVal); } } idxcol++; buff.setRequiredContainerId(resultSet.getLong(idxcol)); idxcol++; buff.setRequiredRevision(resultSet.getInt(idxcol)); return (buff); }