List of usage examples for java.sql PreparedStatement toString
public String toString()
From source file:com.wso2telco.dep.reportingservice.dao.BillingDAO.java
/** * Gets the payment amounts./*from ww w . j a v a 2s .com*/ * * @param year the year * @param month the month * @param consumerKey the consumer key * @param api_version the api_version * @param operatorId the operator id * @param operation the operation * @param category the category * @param subcategory the subcategory * @return the payment amounts * @throws Exception the exception */ //Retriving amount charged from end-user through payment API public Set<PaymentRequestDTO> getPaymentAmounts(short year, short month, String consumerKey, String api_version, String operatorId, int operation, String category, String subcategory) throws Exception { Connection connection = null; PreparedStatement ps = null; ResultSet results = null; StringBuilder sql = new StringBuilder(); sql.append("SELECT api,userId,consumerKey,chargeAmount,category,subcategory,merchantId,time FROM ") .append(HostObjectConstants.SB_RESPONSE_SUMMARY_TABLE) .append(" WHERE api_version =? and consumerKey=? and operatorId =? and responseCode like '2%' and month=? and year=? and operationType=? and category=? and subcategory=? AND operatorRef NOT IN ") .append(" (SELECT distinct operatorRef FROM ").append(HostObjectConstants.SB_RESPONSE_SUMMARY_TABLE) .append(" WHERE api='refund') "); Set<PaymentRequestDTO> requestSet = new HashSet<PaymentRequestDTO>(); try { connection = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB); ps = connection.prepareStatement(sql.toString()); ps.setString(1, api_version); ps.setString(2, consumerKey); ps.setString(3, operatorId); ps.setShort(4, month); ps.setShort(5, year); ps.setInt(6, operation); ps.setString(7, category); ps.setString(8, subcategory); ps.setString(9, consumerKey); ps.setString(10, operatorId); ps.setShort(11, year); ps.setShort(12, month); log.debug("SQL (PS) st ---> " + ps.toString()); results = ps.executeQuery(); log.debug("SQL (PS) ed ---> "); while (results.next()) { PaymentRequestDTO paymentRequest = new PaymentRequestDTO(); paymentRequest.setUserId(results.getString("userId")); paymentRequest.setConsumerKey(results.getString("consumerKey")); String value = results.getString("chargeAmount"); paymentRequest.setAmount(new BigDecimal(value.replaceAll(",", ""))); paymentRequest.setCategory(results.getString("category")); paymentRequest.setSubcategory(results.getString("subcategory")); paymentRequest.setMerchant(results.getString("merchantId")); String rqdate = results.getString("time"); paymentRequest.setDate(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(rqdate)); requestSet.add(paymentRequest); } } catch (Exception e) { handleException("getPaymentAmounts", e); } finally { DbUtils.closeAllConnections(ps, connection, results); } log.debug("done getPaymentAmounts :" + consumerKey + " :" + api_version + " :" + operatorId); return requestSet; }
From source file:org.deegree.metadata.persistence.ebrim.eo.EbrimEOMDStore.java
@Override public MetadataResultSet<RegistryObject> getRecords(MetadataQuery query) throws MetadataStoreException { PreparedStatement stmt = null; ResultSet rs = null;/*from www. j ava 2 s . c o m*/ ConnectionProvider prov = workspace.getResource(ConnectionProviderProvider.class, connId); Connection conn = getConnection(true); try { EOPropertyNameMapper propMapper = new EOPropertyNameMapper(query.getQueryTypeNames(), useLegacyPredicates); if (query.getFilter() instanceof IdFilter) { throw new MetadataStoreException("ID filters are currently not supported."); } AbstractWhereBuilder wb = new PostGISWhereBuilder(null, propMapper, (OperatorFilter) query.getFilter(), query.getSorting(), false, useLegacyPredicates); AliasedRIMType returnType = propMapper.getReturnType(query.getReturnTypeNames()); StringBuilder idSelect = new StringBuilder("SELECT DISTINCT("); idSelect.append(propMapper.getTableAlias(returnType)); idSelect.append(".internalId) FROM "); idSelect.append(propMapper.getTable(returnType)); idSelect.append(' '); idSelect.append(propMapper.getTableAlias(returnType)); boolean first = true; for (AliasedRIMType queryType : propMapper.getQueryTypes()) { if (queryType != returnType) { if (first) { idSelect.append(" LEFT OUTER JOIN "); } else { idSelect.append(" FULL OUTER JOIN "); } idSelect.append(propMapper.getTable(queryType).name()); idSelect.append(' '); idSelect.append(propMapper.getTableAlias(queryType)); idSelect.append(" ON TRUE"); first = false; } } // cope with rim:RegistryPackage -> rim:RegistryObjectList/* join for (Join additionalJoin : propMapper.getAdditionalJoins()) { if (first) { idSelect.append(" LEFT OUTER JOIN "); } else { idSelect.append(" FULL OUTER JOIN "); } idSelect.append(additionalJoin.getToTable()); idSelect.append(' '); idSelect.append(additionalJoin.getToTableAlias()); idSelect.append(" ON "); idSelect.append(additionalJoin.getSQLJoinCondition()); first = false; } if (wb.getWhere() != null) { idSelect.append(" WHERE ").append(wb.getWhere().getSQL()); } if (wb.getOrderBy() != null) { idSelect.append(" ORDER BY "); idSelect.append(wb.getOrderBy().getSQL()); } if (query != null && query.getStartPosition() != 1) { idSelect.append(" OFFSET ").append(Integer.toString(query.getStartPosition() - 1)); } if (query != null) { idSelect.append(" LIMIT ").append(query.getMaxRecords()); } StringBuilder blobSelect = new StringBuilder("SELECT data FROM "); blobSelect.append(propMapper.getTable(returnType)); blobSelect.append(" WHERE internalId IN ("); blobSelect.append(idSelect); blobSelect.append(")"); stmt = conn.prepareStatement(blobSelect.toString()); stmt.setFetchSize(DEFAULT_FETCH_SIZE); int i = 1; if (wb.getWhere() != null) { for (SQLArgument argument : wb.getWhere().getArguments()) { argument.setArgument(stmt, i++); } } if (wb.getOrderBy() != null) { for (SQLArgument argument : wb.getOrderBy().getArguments()) { argument.setArgument(stmt, i++); } } LOG.debug("Execute: " + stmt.toString()); rs = executeQuery(stmt, prov, queryTimeout); return new EbrimEOMDResultSet(rs, conn, stmt); } catch (Throwable t) { JDBCUtils.close(rs, stmt, conn, LOG); LOG.debug(t.getMessage(), t); throw new MetadataStoreException(t.getMessage(), t); } }
From source file:it.fub.jardin.server.DbUtils.java
public Integer removeObjects(final Integer resultsetId, final List<BaseModelData> records, String username) throws HiddenException, VisibleException { int resCode = 0; Connection connection = this.dbConnectionHandler.getConn(); String query = new String(""), appChiavePrimaria = ""; PreparedStatement ps = null; try {//from www . j a v a 2 s. c o m ResultSetMetaData metadata = this.dbProperties.getResultsetMetadata(connection, resultsetId); String tableName = metadata.getTableName(1); // Ciclo per gestire pi cancellazioni nella stessa invocazione List<BaseModelData> primaryKeyList = this.dbProperties.getPrimaryKeys(tableName); if (primaryKeyList.size() <= 0) { throw new HiddenException("La tabella non contiene chiavi primarie: impossibile operare!"); } for (BaseModelData record : records) { query = ""; // Preparazione della query query = query.concat("DELETE FROM `" + tableName + "` WHERE `"); String separator = "AND `"; for (BaseModelData pk : primaryKeyList) { appChiavePrimaria = pk.get("PK_NAME"); query = query.concat(appChiavePrimaria); if (record.get(appChiavePrimaria) == null) { query = query.concat("` IS NULL "); } else { query = query.concat("` = ? "); } query = query.concat(separator); } query = query.substring(0, query.length() - separator.length()); ps = (PreparedStatement) connection.prepareStatement(query); int i = 1; // for (String property : record.getPropertyNames()) { for (BaseModelData pk : primaryKeyList) { ps.setObject(i, record.get((String) pk.get("PK_NAME"))); i++; } // Log.debug("Query DELETE: " + ps); int num = ps.executeUpdate(); if (num > 0) { // this.log("DELETE (" + ps.toString() + ")"); JardinLogger.debug(username, "DELETE query (" + ps.toString() + ")"); } resCode += num; } } catch (MySQLIntegrityConstraintViolationException ecv) { ecv.printStackTrace(); throw new HiddenException("Errore durante l'eliminazione dei record: " + ecv.getLocalizedMessage()); } catch (Exception e) { // Log.warn("Errore SQL", e); e.printStackTrace(); throw new HiddenException("Errore durante l'eliminazione dei record: " + e.getLocalizedMessage()); } finally { // JardinLogger.info("Objects removed"); this.dbConnectionHandler.closeConn(connection); } return (new Integer(resCode)); }
From source file:org.apereo.portal.layout.dlm.RDBMDistributedLayoutStore.java
@Override protected int saveStructure(Node node, PreparedStatement structStmt, PreparedStatement parmStmt) throws SQLException { if (node == null) { // No more return 0; }/*from w ww . j a v a 2 s.co m*/ if (node.getNodeName().equals("parameter")) { //parameter, skip it and go on to the next node return this.saveStructure(node.getNextSibling(), structStmt, parmStmt); } if (!(node instanceof Element)) { return 0; } final Element structure = (Element) node; if (logger.isDebugEnabled()) { logger.debug("saveStructure XML content: {}", XmlUtilitiesImpl.toString(node)); } // determine the struct_id for storing in the db. For incorporated nodes in // the plf their ID is a system-wide unique ID while their struct_id for // storing in the db is cached in a dlm:plfID attribute. int saveStructId = -1; final String plfID = structure.getAttribute(Constants.ATT_PLF_ID); if (!plfID.equals("")) { saveStructId = Integer.parseInt(plfID.substring(1)); } else { final String id = structure.getAttribute("ID"); saveStructId = Integer.parseInt(id.substring(1)); } int nextStructId = 0; int childStructId = 0; int chanId = -1; IPortletDefinition portletDef = null; final boolean isChannel = node.getNodeName().equals("channel"); if (isChannel) { chanId = Integer.parseInt(node.getAttributes().getNamedItem("chanID").getNodeValue()); portletDef = this.portletDefinitionRegistry.getPortletDefinition(String.valueOf(chanId)); if (portletDef == null) { //Portlet doesn't exist any more, drop the layout node return 0; } } if (node.hasChildNodes()) { childStructId = this.saveStructure(node.getFirstChild(), structStmt, parmStmt); } nextStructId = this.saveStructure(node.getNextSibling(), structStmt, parmStmt); structStmt.clearParameters(); structStmt.setInt(1, saveStructId); structStmt.setInt(2, nextStructId); structStmt.setInt(3, childStructId); final String externalId = structure.getAttribute("external_id"); if (externalId != null && externalId.trim().length() > 0) { final Integer eID = new Integer(externalId); structStmt.setInt(4, eID.intValue()); } else { structStmt.setNull(4, java.sql.Types.NUMERIC); } if (isChannel) { structStmt.setInt(5, chanId); structStmt.setNull(6, java.sql.Types.VARCHAR); } else { structStmt.setNull(5, java.sql.Types.NUMERIC); structStmt.setString(6, structure.getAttribute("name")); } final String structType = structure.getAttribute("type"); structStmt.setString(7, structType); structStmt.setString(8, RDBMServices.dbFlag(xmlBool(structure.getAttribute("hidden")))); structStmt.setString(9, RDBMServices.dbFlag(xmlBool(structure.getAttribute("immutable")))); structStmt.setString(10, RDBMServices.dbFlag(xmlBool(structure.getAttribute("unremovable")))); logger.debug(structStmt.toString()); structStmt.executeUpdate(); // code to persist extension attributes for dlm final NamedNodeMap attribs = node.getAttributes(); for (int i = 0; i < attribs.getLength(); i++) { final Node attrib = attribs.item(i); final String name = attrib.getNodeName(); if (name.startsWith(Constants.NS) && !name.equals(Constants.ATT_PLF_ID) && !name.equals(Constants.ATT_FRAGMENT) && !name.equals(Constants.ATT_PRECEDENCE)) { // a cp extension attribute. Push into param table. parmStmt.clearParameters(); parmStmt.setInt(1, saveStructId); parmStmt.setString(2, name); parmStmt.setString(3, attrib.getNodeValue()); logger.debug(parmStmt.toString()); parmStmt.executeUpdate(); } } final NodeList parameters = node.getChildNodes(); if (parameters != null && isChannel) { for (int i = 0; i < parameters.getLength(); i++) { if (parameters.item(i).getNodeName().equals("parameter")) { final Element parmElement = (Element) parameters.item(i); final NamedNodeMap nm = parmElement.getAttributes(); final String parmName = nm.getNamedItem("name").getNodeValue(); final String parmValue = nm.getNamedItem("value").getNodeValue(); final Node override = nm.getNamedItem("override"); // if no override specified then default to allowed if (override != null && !override.getNodeValue().equals("yes")) { // can't override } else { // override only for adhoc or if diff from chan def final IPortletDefinitionParameter cp = portletDef.getParameter(parmName); if (cp == null || !cp.getValue().equals(parmValue)) { parmStmt.clearParameters(); parmStmt.setInt(1, saveStructId); parmStmt.setString(2, parmName); parmStmt.setString(3, parmValue); logger.debug(parmStmt.toString()); parmStmt.executeUpdate(); } } } } } return saveStructId; }
From source file:com.wso2telco.dep.reportingservice.dao.BillingDAO.java
/** * Gets the customer care report data count. * * @param fromDate the from date/*w w w . ja va 2 s.c om*/ * @param toDate the to date * @param msisdn the msisdn * @param subscriber the subscriber * @param operator the operator * @param app the app * @param api the api * @return the customer care report data count * @throws Exception the exception */ public String getCustomerCareReportDataCount(String fromDate, String toDate, String msisdn, String subscriber, String operator, String app, String api) throws Exception { String consumerKey = ""; String count = ""; if (subscriber.equalsIgnoreCase("__ALL__")) { subscriber = "%"; } if (operator.equalsIgnoreCase("__ALL__")) { operator = "%"; } if ((app.equalsIgnoreCase("__ALL__")) || (app.equalsIgnoreCase("0"))) { consumerKey = "%"; } else { consumerKey = getConsumerKeyByAppId(app); } if (api.equalsIgnoreCase("__ALL__")) { api = "%"; } String[] fromDateArray = fromDate.split("-"); String[] toDateArray = toDate.split("-"); boolean isSameYear = fromDateArray[0].equalsIgnoreCase(toDateArray[0]) ? true : false; boolean isSameMonth = fromDateArray[1].equalsIgnoreCase(toDateArray[1]) ? true : false; Connection conn = null; PreparedStatement ps = null; ResultSet results = null; StringBuilder sql = new StringBuilder(); sql.append("SELECT COUNT(*) FROM ").append(ReportingTable.SB_API_RESPONSE_SUMMARY.getTObject()).append( " WHERE operatorId LIKE ? AND replace(userid,'@carbon.super','') LIKE ? AND api LIKE ? AND consumerKey LIKE ? "); if (isSameYear && isSameMonth) { sql.append("AND (day between ? and ? ) AND (month = ?) AND (year = ?) "); } else { sql.append( "AND STR_TO_DATE(x.time,'%Y-%m-%d') between STR_TO_DATE(?,'%Y-%m-%d') and STR_TO_DATE(?,'%Y-%m-%d') "); } if (!msisdn.isEmpty()) { sql.append("AND msisdn LIKE ? "); } List<String[]> api_request_data = new ArrayList<String[]>(); try { conn = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB); ps = conn.prepareStatement(sql.toString()); ps.setString(1, operator); ps.setString(2, subscriber); ps.setString(3, api); ps.setString(4, consumerKey); if (isSameYear && isSameMonth) { ps.setInt(5, Integer.parseInt(fromDateArray[2])); ps.setInt(6, Integer.parseInt(toDateArray[2])); ps.setInt(7, Integer.parseInt(fromDateArray[1])); ps.setInt(8, Integer.parseInt(fromDateArray[0])); if (!msisdn.isEmpty()) { // ps.setInt(9,Integer.parseInt(msisdn)); ps.setString(9, "%" + msisdn); } } else { ps.setString(5, fromDate); ps.setString(6, toDate); if (!msisdn.isEmpty()) { // ps.setInt(7,Integer.parseInt(msisdn)); ps.setString(7, "%" + msisdn); } } log.debug("getCustomerCareReportData count"); log.debug("SQL (PS) ---> " + ps.toString()); results = ps.executeQuery(); results.next(); count = results.getString(1); } catch (Exception e) { handleException("getCustomerCareReportDataCount", e); } finally { DbUtils.closeAllConnections(ps, conn, results); } log.debug("getCustomerCareReportDataCount :" + count); return count; }
From source file:com.wso2telco.dep.reportingservice.dao.BillingDAO.java
/** * Gets the API wise traffic for report charging. * * @param fromDate the from date/*from w w w . j a v a 2 s .com*/ * @param toDate the to date * @param subscriber the subscriber * @param operator the operator * @param api the api * @return the API wise traffic for report charging * @throws Exception the exception */ public List<String[]> getAPIWiseTrafficForReportCharging(String fromDate, String toDate, String subscriber, String operator, String api, boolean isError) throws Exception { if (subscriber.equals("__ALL__")) { subscriber = "%"; } if (operator.equals("__ALL__")) { operator = "%"; } if (api.equals("__ALL__")) { api = "%"; } String responseStr = "responseCode LIKE '20_' "; if (isError) { responseStr = "responseCode NOT LIKE '20_' "; } String[] fromDateArray = fromDate.split("-"); String[] toDateArray = toDate.split("-"); String userId; boolean isSameYear = fromDateArray[0].equalsIgnoreCase(toDateArray[0]) ? true : false; boolean isSameMonth = fromDateArray[1].equalsIgnoreCase(toDateArray[1]) ? true : false; Connection conn = null; PreparedStatement ps = null; ResultSet results = null; StringBuilder sql = new StringBuilder(); sql.append( "SELECT res.time, res.userId, res.operatorId, res.requestId, res.msisdn, res.chargeAmount, res.responseCode, res.jsonBody, res.resourcePath, res.method, res.purchaseCategoryCode, res.api, res.taxAmount , res.channel , res.onBehalfOf ,res.description, res.transactionOperationStatus , req.transactionOperationStatus FROM ") .append(ReportingTable.SB_API_RESPONSE_SUMMARY.getTObject()).append(" res, ") .append(ReportingTable.SB_API_REQUEST_SUMMARY.getTObject()).append(" req").append(" WHERE ") .append(responseStr) .append(" AND res.operatorId LIKE ? AND replace(res.userid,'@carbon.super','') LIKE ? AND res.api LIKE ? AND res.requestId = req.requestId"); if (isSameYear && isSameMonth) { sql.append("AND (res.day between ? and ? ) AND (res.month = ?) AND (res.year = ?) "); } else { sql.append( "AND STR_TO_DATE(res.time,'%Y-%m-%d') between STR_TO_DATE(?,'%Y-%m-%d') and STR_TO_DATE(?,'%Y-%m-%d') "); } List<String[]> api_request = new ArrayList<String[]>(); try { conn = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB); ps = conn.prepareStatement(sql.toString()); ps.setString(1, operator); ps.setString(2, subscriber); ps.setString(3, api); log.debug(api); if (isSameYear && isSameMonth) { ps.setInt(4, Integer.parseInt(fromDateArray[2])); ps.setInt(5, Integer.parseInt(toDateArray[2])); ps.setInt(6, Integer.parseInt(fromDateArray[1])); ps.setInt(7, Integer.parseInt(fromDateArray[0])); } else { ps.setString(4, fromDate); ps.setString(5, toDate); } log.debug("getAPIWiseTrafficForReportCharging"); log.debug("SQL (PS) ---> " + ps.toString()); results = ps.executeQuery(); while (results.next()) { String jsonBody = results.getString(8); String requestUrl = results.getString(9); String requestMethod = results.getString(10); String requestapi = results.getString(12); if (results.getString(2) != null && results.getString(2).contains("@")) { userId = results.getString(2).split("@")[0]; } else { userId = results.getString(2); } String dateTime = results.getString(1); if (dateTime == null) { dateTime = ""; } String msisdn = ""; String clientCorelator = ""; String currency = ""; String event_type = ""; if (!jsonBody.isEmpty()) { try { JSONObject homejson = new JSONObject(jsonBody); if (!homejson.isNull("amountTransaction")) { JSONObject transactionObject = (JSONObject) homejson.get("amountTransaction"); if (!transactionObject.isNull("endUserId")) { msisdn = transactionObject.getString("endUserId"); } if (!transactionObject.isNull("clientCorrelator")) { clientCorelator = transactionObject.getString("clientCorrelator"); } if (!transactionObject.isNull("paymentAmount")) { JSONObject paymentAmountoObj = (JSONObject) transactionObject.get("paymentAmount"); if (!paymentAmountoObj.isNull("chargingInformation")) { JSONObject chargingInfoObj = (JSONObject) paymentAmountoObj .get("chargingInformation"); if (!chargingInfoObj.isNull("currency")) { currency = chargingInfoObj.getString("currency"); } } } } } catch (Exception ex) { System.out.println("Unable to read JSON body stored in DB :: " + ex); clientCorelator = ""; } } if (!requestUrl.isEmpty()) { String apitype = findAPIType(requestUrl, requestapi, requestMethod); if (apitype.equalsIgnoreCase("send_sms")) { event_type = "Outbound"; } else if (apitype.equalsIgnoreCase("retrive_sms_subscriptions")) { if (requestMethod.equalsIgnoreCase("DELETE")) { event_type = "CancelReceiptService"; } else { event_type = "ReceiptService"; } } else if (apitype.equalsIgnoreCase("retrive_sms")) { event_type = "Inbound "; } else if (apitype.equalsIgnoreCase("payment")) { if (results.getString(18) != null) { event_type = results.getString(18); } else { event_type = ""; } } else if (apitype.equalsIgnoreCase("location")) { event_type = "Location"; } else if (apitype.equalsIgnoreCase("sms_dn_inbound_notifications")) { event_type = "DNCallback "; } else if (apitype.equalsIgnoreCase("sms_mo_inbound_notifications")) { event_type = "MOCallback"; } else if (apitype.equalsIgnoreCase("query_sms")) { event_type = "DNQuery"; } else if (apitype.equalsIgnoreCase("start_outbound_subscription")) { event_type = "DNSubscription"; } else if (apitype.equalsIgnoreCase("stop_outbound_subscription")) { event_type = "DNStopSubscription"; } else if (apitype.equalsIgnoreCase("refund")) { event_type = "Refund"; } } String[] temp = { dateTime, userId, results.getString(3), event_type, results.getString(4), clientCorelator, results.getString(5), results.getString(6), currency, results.getString(7), results.getString(11), results.getString(13), results.getString(14), results.getString(15), results.getString(16), results.getString(17) }; api_request.add(temp); } } catch (Exception e) { handleException("getAPIWiseTrafficForReportCharging", e); } finally { DbUtils.closeAllConnections(ps, conn, results); } log.debug("end getAPIWiseTrafficForReportCharging"); return api_request; }
From source file:sopho.Ofeloumenoi.EditOfeloumenoiController.java
@FXML public void Save(ActionEvent event) throws IOException { if (barcode.getText().isEmpty() || onoma.getText().isEmpty() || eponimo.getText().isEmpty() || patronimo.getText().isEmpty()) { //checking if the user has filled the required fields sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " ? ? . ? ? Barcode, , ? ? ? ?", "error"); cm.showAndWait();/* w w w .java2 s . c o m*/ } else if (!NumberUtils.isNumber(barcode.getText()) && !barcode.getText().isEmpty()) { sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " barcode ? ? ??. ? ? .", "error"); cm.showAndWait(); } else if (!NumberUtils.isNumber(eisodima.getText()) && !eisodima.getText().isEmpty()) { sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " ? ? ??. ? ? .", "error"); cm.showAndWait(); } else {//the user has filled the required fields. We can proceed. sopho.DBClass db = new sopho.DBClass(); Connection conn = null; PreparedStatement pst = null; ResultSet rset = null; String teknaDB = ""; //we create a var to push data to db. for (int i = 0; i < tekna.getItems().size(); i++) {//we are converting the table rows to a single comma separated string to push it to the database in a single entry. tableManager tbl = (tableManager) tekna.getItems().get(i); if (!tbl.getEtos().equals("? ")) { //we are checking if the user has actually entered a number teknaDB += tbl.getEtos() + ","; //we have to call getEtos from the tableManager class to get the actual value. We add the value to teknaDB and seperate with comma. arithmosTeknon++; } } if (arithmosTeknon > 0) {// we need to catch the case that the user has not added any data to the table. teknaDB = teknaDB.substring(0, teknaDB.length() - 1); // we have to remove the last comma. } conn = db.ConnectDB(); try { // we can push the data to database... String sql = "UPDATE ofeloumenoi SET barcode=?, eponimo=?, onoma=?, patronimo=?, mitronimo=?, imGennisis=?, dieuthinsi=?, dimos=?, tilefono=?, anergos=?, epaggelma=?, eisodima=?, eksartiseis=?, photoID=?, afm=?, tautotita=?, ethnikotita=?, metanastis=?, roma=?, oikKatastasi=?, hasTekna=?, arithmosTeknon=?, ilikiesTeknon=?, politeknos=?, monogoneiki=?, mellousaMama=?, amea=?, asfForeas=?, xronios=?, pathisi=?, anoTon60=?, monaxikos=?, emfiliVia=?, spoudastis=?, anenergos=?, loipa=? WHERE barcode=?"; pst = conn.prepareStatement(sql); //now we will set the values to the sql statement pst.setString(1, barcode.getText()); pst.setString(2, eponimo.getText()); pst.setString(3, onoma.getText()); pst.setString(4, patronimo.getText()); pst.setString(5, mitronimo.getText()); //now we have to convert the imGennisis to a suitable format to be able to push it to the database if (imGennisis.getValue() != null) { Date date = Date.from(imGennisis.getValue().atStartOfDay(ZoneId.systemDefault()).toInstant()); java.sql.Date sqlDate = new java.sql.Date(date.getTime()); pst.setDate(6, sqlDate); } else { pst.setDate(6, null); } pst.setString(7, dieuthinsi.getText()); pst.setString(8, dimos.getText()); pst.setString(9, tilefono.getText()); pst.setInt(10, anergos.isSelected() ? 1 : 0); //set 1 if selected and 0 if not. We will use this method for all the checkboxes. pst.setString(11, epaggelma.getText()); pst.setString(12, eisodima.getText()); pst.setString(13, eksartiseis.getText()); pst.setString(14, PhotoID); pst.setString(15, afm.getText()); pst.setString(16, tautotita.getText()); pst.setString(17, ethnikotita.getText()); pst.setInt(18, metanastis.isSelected() ? 1 : 0); pst.setInt(19, roma.isSelected() ? 1 : 0); pst.setInt(20, (int) oikKatastasi.getSelectionModel().getSelectedIndex());//we are pushing to database the selected index pst.setInt(21, arithmosTeknon > 0 ? 1 : 0); //checking number of tekna. if >0 has tekna gets 1 pst.setInt(22, arithmosTeknon); pst.setString(23, teknaDB); //here we use the converted to comma separated values variable in order to save the tableView data using only one field in database. pst.setInt(24, politeknos.isSelected() ? 1 : 0); pst.setInt(25, monogoneiki.isSelected() ? 1 : 0); pst.setInt(26, mellousaMama.isSelected() ? 1 : 0); pst.setInt(27, amea.isSelected() ? 1 : 0); pst.setInt(28, (int) asfForeas.getSelectionModel().getSelectedIndex());//we are pushing to database the selected index pst.setInt(29, xronios.isSelected() ? 1 : 0); pst.setString(30, pathisi.getText()); pst.setInt(31, monaxiko.isSelected() ? 1 : 0); pst.setInt(32, anoTon60.isSelected() ? 1 : 0); pst.setInt(33, emfiliVia.isSelected() ? 1 : 0); pst.setInt(34, spoudastis.isSelected() ? 1 : 0); pst.setInt(35, anenergos.isSelected() ? 1 : 0); pst.setString(36, loipa.getText()); pst.setString(37, oldBarcode); // we update the values to the database table where barcode = oldBarcode System.out.println("the query is:" + pst.toString()); int linesAffected = pst.executeUpdate(); //checking if the data were inserted to the database successfully if (linesAffected > 0) { sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "!", " ? ? , ? .", "confirm"); cm.showAndWait(); if (le.LockEditing(false, selID, "ofeloumenoi")) { Stage stage = (Stage) barcode.getScene().getWindow(); sl.StageLoad("/sopho/Ofeloumenoi/OfeloumenoiMain.fxml", stage, true, false); //resizable true, utility false } else { sopho.Messages.CustomMessageController cm2 = new sopho.Messages.CustomMessageController( null, "?", " ? ? ?. ? ? . ? ? ? ??.", "error"); cm2.showAndWait(); } } else {//problem inserting data... sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " ? ?? . ? ...", "error"); cm.showAndWait(); } } catch (SQLException e) { System.out.println( "? ? ? !" + e); } } }
From source file:org.jasig.portal.layout.dlm.RDBMDistributedLayoutStore.java
@Override protected int saveStructure(Node node, PreparedStatement structStmt, PreparedStatement parmStmt) throws SQLException { if (node == null) { // No more return 0; }/*from ww w .j ava 2s . c om*/ if (node.getNodeName().equals("parameter")) { //parameter, skip it and go on to the next node return this.saveStructure(node.getNextSibling(), structStmt, parmStmt); } if (!(node instanceof Element)) { return 0; } final Element structure = (Element) node; if (LOG.isDebugEnabled()) { LOG.debug("saveStructure XML content: " + XmlUtilitiesImpl.toString(node)); } // determine the struct_id for storing in the db. For incorporated nodes in // the plf their ID is a system-wide unique ID while their struct_id for // storing in the db is cached in a dlm:plfID attribute. int saveStructId = -1; final String plfID = structure.getAttribute(Constants.ATT_PLF_ID); if (!plfID.equals("")) { saveStructId = Integer.parseInt(plfID.substring(1)); } else { final String id = structure.getAttribute("ID"); saveStructId = Integer.parseInt(id.substring(1)); } int nextStructId = 0; int childStructId = 0; int chanId = -1; IPortletDefinition portletDef = null; final boolean isChannel = node.getNodeName().equals("channel"); if (isChannel) { chanId = Integer.parseInt(node.getAttributes().getNamedItem("chanID").getNodeValue()); portletDef = this.portletDefinitionRegistry.getPortletDefinition(String.valueOf(chanId)); if (portletDef == null) { //Portlet doesn't exist any more, drop the layout node return 0; } } if (node.hasChildNodes()) { childStructId = this.saveStructure(node.getFirstChild(), structStmt, parmStmt); } nextStructId = this.saveStructure(node.getNextSibling(), structStmt, parmStmt); structStmt.clearParameters(); structStmt.setInt(1, saveStructId); structStmt.setInt(2, nextStructId); structStmt.setInt(3, childStructId); final String externalId = structure.getAttribute("external_id"); if (externalId != null && externalId.trim().length() > 0) { final Integer eID = new Integer(externalId); structStmt.setInt(4, eID.intValue()); } else { structStmt.setNull(4, java.sql.Types.NUMERIC); } if (isChannel) { structStmt.setInt(5, chanId); structStmt.setNull(6, java.sql.Types.VARCHAR); } else { structStmt.setNull(5, java.sql.Types.NUMERIC); structStmt.setString(6, structure.getAttribute("name")); } final String structType = structure.getAttribute("type"); structStmt.setString(7, structType); structStmt.setString(8, RDBMServices.dbFlag(xmlBool(structure.getAttribute("hidden")))); structStmt.setString(9, RDBMServices.dbFlag(xmlBool(structure.getAttribute("immutable")))); structStmt.setString(10, RDBMServices.dbFlag(xmlBool(structure.getAttribute("unremovable")))); if (LOG.isDebugEnabled()) { LOG.debug(structStmt.toString()); } structStmt.executeUpdate(); // code to persist extension attributes for dlm final NamedNodeMap attribs = node.getAttributes(); for (int i = 0; i < attribs.getLength(); i++) { final Node attrib = attribs.item(i); final String name = attrib.getNodeName(); if (name.startsWith(Constants.NS) && !name.equals(Constants.ATT_PLF_ID) && !name.equals(Constants.ATT_FRAGMENT) && !name.equals(Constants.ATT_PRECEDENCE)) { // a cp extension attribute. Push into param table. parmStmt.clearParameters(); parmStmt.setInt(1, saveStructId); parmStmt.setString(2, name); parmStmt.setString(3, attrib.getNodeValue()); if (LOG.isDebugEnabled()) { LOG.debug(parmStmt.toString()); } parmStmt.executeUpdate(); } } final NodeList parameters = node.getChildNodes(); if (parameters != null && isChannel) { for (int i = 0; i < parameters.getLength(); i++) { if (parameters.item(i).getNodeName().equals("parameter")) { final Element parmElement = (Element) parameters.item(i); final NamedNodeMap nm = parmElement.getAttributes(); final String parmName = nm.getNamedItem("name").getNodeValue(); final String parmValue = nm.getNamedItem("value").getNodeValue(); final Node override = nm.getNamedItem("override"); // if no override specified then default to allowed if (override != null && !override.getNodeValue().equals("yes")) { // can't override } else { // override only for adhoc or if diff from chan def final IPortletDefinitionParameter cp = portletDef.getParameter(parmName); if (cp == null || !cp.getValue().equals(parmValue)) { parmStmt.clearParameters(); parmStmt.setInt(1, saveStructId); parmStmt.setString(2, parmName); parmStmt.setString(3, parmValue); if (LOG.isDebugEnabled()) { LOG.debug(parmStmt); } parmStmt.executeUpdate(); } } } } } return saveStructId; }
From source file:com.wso2telco.dep.reportingservice.dao.BillingDAO.java
/** * Gets the API wise traffic for report. * * @param fromDate the from date//from w w w . j a v a 2 s .c om * @param toDate the to date * @param subscriber the subscriber * @param operator the operator * @param api the api * @return the API wise traffic for report * @throws Exception the exception */ public List<String[]> getAPIWiseTrafficForReport(String fromDate, String toDate, String subscriber, String operator, String api, boolean isError, int applicationId) throws Exception { String consumerKey = null; if (subscriber.equals("__ALL__")) { subscriber = "%"; } if (operator.equals("__ALL__")) { operator = "%"; } if (api.equals("__ALL__")) { api = "%"; } if (applicationId == 0) { consumerKey = "%"; } else { consumerKey = ApiManagerDAO.getConsumerKeyByApplication(applicationId); } String responseStr = "responseCode LIKE '20_' "; if (isError) { responseStr = "responseCode NOT LIKE '20_' "; } String[] fromDateArray = fromDate.split("-"); String[] toDateArray = toDate.split("-"); String userId; boolean isSameYear = fromDateArray[0].equalsIgnoreCase(toDateArray[0]) ? true : false; boolean isSameMonth = fromDateArray[1].equalsIgnoreCase(toDateArray[1]) ? true : false; Connection conn = null; PreparedStatement ps = null; ResultSet results = null; StringBuilder sql = new StringBuilder(); sql.append( "SELECT time, userId, operatorId, requestId, msisdn, response_count, responseCode, jsonBody, resourcePath, method, api, ussdAction, ussdSessionId, destinationAddress, senderAddress, message, date_Time, resourceURL, message_Id,spUserId,spOperatorId FROM ") .append(ReportingTable.SB_API_RESPONSE_SUMMARY.getTObject()).append(" WHERE ").append(responseStr) .append("AND (operatorId LIKE ? OR spOperatorId LIKE ?) AND ((replace(userid,'@carbon.super','') LIKE ?) OR (replace(spUserId,'@carbon.super','') LIKE ?)) AND api LIKE ? AND ( consumerKey LIKE ? OR spConsumerKey LIKE ? ) "); if (isSameYear && isSameMonth) { sql.append("AND (day between ? and ? ) AND (month = ?) AND (year = ?) "); } else { sql.append( "AND STR_TO_DATE(time,'%Y-%m-%d') between STR_TO_DATE(?,'%Y-%m-%d') and STR_TO_DATE(?,'%Y-%m-%d') "); } List<String[]> api_request = new ArrayList<String[]>(); try { conn = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB); ps = conn.prepareStatement(sql.toString()); ps.setString(1, operator); if (api.equalsIgnoreCase("ussd")) { ps.setString(2, operator); } else { ps.setString(2, null); } //ADDED OperatorId ps.setString(3, subscriber); if (api.equalsIgnoreCase("ussd")) { ps.setString(4, subscriber); } else { ps.setString(4, null); } //ADDED UserId ps.setString(5, api); ps.setString(6, consumerKey); if (api.equalsIgnoreCase("ussd")) { ps.setString(7, consumerKey); } else { ps.setString(7, null); } if (isSameYear && isSameMonth) { ps.setInt(8, Integer.parseInt(fromDateArray[2])); ps.setInt(9, Integer.parseInt(toDateArray[2])); ps.setInt(10, Integer.parseInt(fromDateArray[1])); ps.setInt(11, Integer.parseInt(fromDateArray[0])); } else { ps.setString(8, fromDate); ps.setString(9, toDate); } log.debug("getAPIWiseTrafficForReport"); log.debug("SQL (PS) ---> " + ps.toString()); results = ps.executeQuery(); System.out.println("getAPIWiseTrafficForReport---------------------------------"); System.out.print("SQL (PS) ---> " + ps.toString()); while (results.next()) { String dateTime = results.getString(1); if (dateTime == null) { dateTime = ""; } String jsonBody = results.getString(8); String requestUrl = results.getString(9); String requestMethod = results.getString(10); String requestapi = results.getString(11); String operatorId = null; if (results.getString(2) != null && results.getString(2).contains("@")) { userId = results.getString(2).split("@")[0]; } else { userId = results.getString(2); } String event_type = ""; String clientCorelator = ""; if (!jsonBody.isEmpty()) { try { JSONObject homejson = new JSONObject(jsonBody); if (!homejson.isNull("outboundSMSMessageRequest")) { JSONObject smsTransactionObject = (JSONObject) homejson .get("outboundSMSMessageRequest"); if (!smsTransactionObject.isNull("clientCorrelator")) { clientCorelator = smsTransactionObject.getString("clientCorrelator"); } } } catch (Exception ex) { log.debug("Unable to read JSON body stored in DB :: " + ex); clientCorelator = ""; } } if (!requestUrl.isEmpty() && !requestapi.isEmpty()) { String apitype = findAPIType(requestUrl, requestapi, requestMethod); if (apitype.equalsIgnoreCase("send_sms")) { event_type = "Outbound"; } else if (apitype.equalsIgnoreCase("retrive_sms_subscriptions")) { if (requestMethod.equalsIgnoreCase("DELETE")) { event_type = "CancelReceiptService"; } else { event_type = "ReceiptService"; } } else if (apitype.equalsIgnoreCase("retrive_sms")) { event_type = "Inbound "; } else if (apitype.equalsIgnoreCase("payment")) { event_type = "Charge"; } else if (apitype.equalsIgnoreCase("ussd_send")) { event_type = "USSD Outbound"; } else if (apitype.equalsIgnoreCase("ussd_receive")) { if (results.getString(12) != null && (results.getString(12).equalsIgnoreCase("mocont") || results.getString(12).equalsIgnoreCase("mofin"))) { event_type = "USSD MO Callback"; } else if (results.getString(12) != null && (results.getString(12).equalsIgnoreCase("mtcont") || results.getString(12).equalsIgnoreCase("mtcont"))) { event_type = "USSD MT Callback"; } } else if (apitype.equalsIgnoreCase("ussd_subscription")) { event_type = "USSD Subscription"; } else if (apitype.equalsIgnoreCase("stop_ussd_subscription")) { if (requestMethod.equalsIgnoreCase("DELETE")) { event_type = "Stop ussd subscription"; } } else if (apitype.equalsIgnoreCase("location")) { event_type = "Location"; } else if (apitype.equalsIgnoreCase("wallet/payment")) { event_type = "Wallet Payment"; } else if (apitype.equalsIgnoreCase("wallet/refund")) { event_type = "Wallet Refund"; } else if (apitype.equalsIgnoreCase("wallet/list")) { event_type = "Wallet List"; } else if (apitype.equalsIgnoreCase("wallet/balance")) { event_type = "Wallet Balance"; } } operatorId = results.getString(3); if (operatorId == null) { operatorId = results.getString(21); } String[] temp = { dateTime, userId, operatorId, event_type, results.getString(4), clientCorelator, results.getString(5), results.getString(6), results.getString(7), results.getString(13), results.getString(14), results.getString(15), results.getString(16), results.getString(17), results.getString(18), results.getString(19) }; api_request.add(temp); } } catch (Exception e) { handleException("Error occured while getting API wise traffic for report from the database", e); } finally { DbUtils.closeAllConnections(ps, conn, results); } log.debug("end getAPIWiseTrafficForReport"); return api_request; }
From source file:com.wso2telco.dep.reportingservice.dao.BillingDAO.java
/** * Gets the customer care report data./* w w w.j a v a2 s . c om*/ * * @param fromDate the from date * @param toDate the to date * @param msisdn the msisdn * @param subscriber the subscriber * @param operator the operator * @param app the app * @param api the api * @param startLimit the start limit * @param endLimit the end limit * @param timeOffset the time offset * @return the customer care report data * @throws Exception the exception */ public List<String[]> getCustomerCareReportData(String fromDate, String toDate, String msisdn, String subscriber, String operator, String app, String api, String startLimit, String endLimit, String timeOffset) throws Exception { String consumerKey = ""; if (subscriber.equalsIgnoreCase("__ALL__")) { subscriber = "%"; } if (operator.equalsIgnoreCase("__ALL__")) { operator = "%"; } if ((app.equalsIgnoreCase("__ALL__")) || (app.equalsIgnoreCase("0"))) { consumerKey = "%"; } else { consumerKey = getConsumerKeyByAppId(app); } if (api.equalsIgnoreCase("__ALL__")) { api = "%"; } String[] fromDateArray = fromDate.split("-"); String[] toDateArray = toDate.split("-"); boolean isSameYear = fromDateArray[0].equalsIgnoreCase(toDateArray[0]) ? true : false; boolean isSameMonth = fromDateArray[1].equalsIgnoreCase(toDateArray[1]) ? true : false; Connection conn = null; PreparedStatement ps = null; ResultSet results = null; StringBuilder sql = new StringBuilder(); if (isSameYear && isSameMonth) { sql.append("SELECT time, jsonBody, api FROM ") .append(ReportingTable.SB_API_RESPONSE_SUMMARY.getTObject()) .append(" WHERE operatorId LIKE ? AND replace(userid,'@carbon.super','') LIKE ? AND api LIKE ? AND consumerKey LIKE ? AND (day between ? and ? ) AND (month = ?) AND (year = ?) "); } else { sql.append("SELECT x.time, x.jsonBody, x.api FROM ") .append(ReportingTable.SB_API_RESPONSE_SUMMARY.getTObject()) .append(" x WHERE operatorId LIKE ? AND replace(userid,'@carbon.super','') LIKE ? AND api LIKE ? AND consumerKey LIKE ? AND STR_TO_DATE(x.time,'%Y-%m-%d') between STR_TO_DATE(?,'%Y-%m-%d') and STR_TO_DATE(?,'%Y-%m-%d') "); } if (!msisdn.isEmpty()) { sql.append("AND (msisdn LIKE ? or (msisdn LIKE ? or jsonBody like '%senderAddress\":\"").append(msisdn) .append("%')) "); } sql.append("LIMIT ").append(startLimit).append(" ,").append(endLimit); List<String[]> api_request_data = new ArrayList<String[]>(); try { conn = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB); ps = conn.prepareStatement(sql.toString()); ps.setString(1, operator); ps.setString(2, subscriber); ps.setString(3, api); ps.setString(4, consumerKey); if (isSameYear && isSameMonth) { ps.setInt(5, Integer.parseInt(fromDateArray[2])); ps.setInt(6, Integer.parseInt(toDateArray[2])); ps.setInt(7, Integer.parseInt(fromDateArray[1])); ps.setInt(8, Integer.parseInt(fromDateArray[0])); if (!msisdn.isEmpty()) { // ps.setInt(9,Integer.parseInt(msisdn)); ps.setString(9, "%" + msisdn); ps.setString(10, "%" + msisdn); //ps.setString(10, msisdn); } } else { ps.setString(5, fromDate); ps.setString(6, toDate); if (!msisdn.isEmpty()) { // ps.setInt(7,Integer.parseInt(msisdn)); ps.setString(7, "%" + msisdn); ps.setString(8, "%" + msisdn); //ps.setString(8, msisdn); } } log.debug("getCustomerCareReportData"); log.debug("SQL (PS) ---> " + ps.toString()); results = ps.executeQuery(); while (results.next()) { String localTime = convertToLocalTime(timeOffset, results.getString(1)); String[] temp = { localTime, results.getString(2), results.getString(3) }; api_request_data.add(temp); } } catch (Exception e) { handleException("getCustomerCareReportData", e); } finally { DbUtils.closeAllConnections(ps, conn, results); } log.debug("end getCustomerCareReportData: " + api_request_data.size()); return api_request_data; }