List of usage examples for java.sql CallableStatement setDate
void setDate(String parameterName, java.sql.Date x) throws SQLException;
java.sql.Date value using the default time zone of the virtual machine that is running the application. From source file:com.ibm.ioes.dao.ReportsDao.java
public ArrayList<ArchivalReportDto> pdReportOrder(ArchivalReportDto reportsDto) { String methodName = "pdReportOrder"; String className = this.getClass().getName(); String msg = ""; boolean logToFile = true, logToConsole = true; Connection connection = null; CallableStatement cstmt = null; ResultSet rs = null;//from www . j a v a2 s . c o m ArrayList docListDetails = new ArrayList(); int recordCount = 0; ArchivalReportBean dto = null; SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy"); String fromDate = reportsDto.getFromdate(); String toDate = reportsDto.getTodate(); Utility utility = new Utility(); try { PagingSorting pagingSorting = reportsDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index connection = DbConnection.getConnectionObject(); cstmt = connection.prepareCall(sqlGetPDReport); if (fromDate != null && !"".equals(fromDate)) { Date fDate = df.parse(fromDate); cstmt.setDate(1, new java.sql.Date(fDate.getTime())); } else { cstmt.setNull(1, java.sql.Types.DATE); } if (toDate != null && !"".equals(toDate)) { Date tDate = df.parse(toDate); cstmt.setDate(2, new java.sql.Date(tDate.getTime())); } else { cstmt.setNull(2, java.sql.Types.DATE); } cstmt.setString(3, reportsDto.getAccount_id()); cstmt.setString(4, reportsDto.getOrder_no()); cstmt.setString(5, reportsDto.getLogical_si_no()); //System.out.println("reportsDto.getLogical_si_no()>>>>"+reportsDto.getLogical_si_no()); cstmt.setString(6, reportsDto.getM6orderno()); cstmt.setString(7, reportsDto.getCircuit_id()); cstmt.setString(8, reportsDto.getOrder_line_no()); cstmt.setString(9, pagingSorting.getSortByColumn()); cstmt.setString(10, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder())); cstmt.setInt(11, pagingSorting.getStartRecordId()); cstmt.setInt(12, pagingSorting.getEndRecordId()); cstmt.setInt(13, (pagingSorting.isPagingToBeDone() ? 1 : 0)); rs = cstmt.executeQuery(); while (rs.next()) { dto = new ArchivalReportBean(); dto.setAccount_mgr(rs.getString("ACCOUNT_MGR")); dto.setAccount_no(rs.getString("ACCOUNT_NO")); dto.setAmt(rs.getString("AMT")); dto.setAnnotation(rs.getString("ANNOTATION")); dto.setAnnual_rate(rs.getString("ANNUAL_RATE")); dto.setBandwidth(rs.getString("BANDWIDTH")); dto.setBandwidth_uom(rs.getString("BANDWIDTH_UOM")); dto.setBill_format(rs.getString("BILL_FORMAT")); dto.setBill_period(rs.getString("BILL_PERIOD")); dto.setBill_trg_Create_date(rs.getString("BILL_TRG_CREATE_DATE")); dto.setBill_type(rs.getString("BILL_TYPE")); dto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); dto.setBilling_bandwidth_uom(rs.getString("BILLING_BANDWIDTH_UOM")); dto.setBilling_level(rs.getString("BILLING_LEVEL")); dto.setBilling_level_number(rs.getString("BILLING_LEVEL_NUMBER")); dto.setBilling_location_from(rs.getString("BILLING_LOCATION_FROM")); dto.setBilling_location_to(rs.getString("BILLING_LOCATION_TO")); dto.setBilling_mode(rs.getString("BILLING_MODE")); dto.setBilling_trig_flag(rs.getString("BILLING_TRIG_FLAG")); dto.setChallen_date(rs.getString("CHALLEN_DATE")); dto.setCharge_end_date(rs.getString("CHARGE_END_DATE")); dto.setCharge_hdr_id(rs.getString("CHARGE_HDR_ID")); dto.setCharge_name(rs.getString("CHARGE_NAME")); dto.setCharge_start_date(rs.getString("CHARGE_START_DATE")); dto.setCharge_status(rs.getString("CHARGE_STATUS")); dto.setCharge_type(rs.getString("CHARGE_TYPE")); dto.setCharge_type_id(rs.getString("CHARGE_TYPE_ID")); dto.setChargeable_distance(rs.getString("CHARGEABLE_DISTANCE")); dto.setChild_acc_fx_status(rs.getString("CHILD_ACC_FX_STATUS")); dto.setChild_acc_no(rs.getString("CHILD_ACC_NO")); dto.setCircuit_id(rs.getString("CIRCUIT_ID")); dto.setCommitment_period(rs.getString("COMMITMENT_PERIOD")); dto.setContract_period_months(rs.getString("CONTRACT_PERIOD_MONTHS")); dto.setCopc_approval_date(rs.getString("COPC_APPROVAL_DATE")); dto.setCredit_period(rs.getString("CREDIT_PERIOD")); dto.setCurrency(rs.getString("CURRENCY")); dto.setCust_acc_id(rs.getString("CUST_ACC_ID")); dto.setCust_logical_si_no(rs.getString("CUST_LOGICAL_SI_NO")); dto.setCust_po_date(rs.getString("CUST_PO_DATE")); dto.setCust_po_number(rs.getString("CUST_PO_NUMBER")); dto.setCust_po_receive_date(rs.getString("CUST_PO_RECEIVE_DATE")); dto.setCustomer_segment(rs.getString("CUSTOMER_SEGMENT")); dto.setCustomer_service_rfs_date(rs.getString("CUSTOMER_SERVICE_RFS_DATE")); dto.setDemo_type(rs.getString("DEMO_TYPE")); dto.setDisconnection_remark(rs.getString("DISCONNECTION_REMARK")); dto.setEnd_date_days(rs.getString("END_DATE_DAYS")); dto.setEnd_date_logic(rs.getString("END_DATE_LOGIC")); dto.setEnd_date_months(rs.getString("END_DATE_MONTHS")); dto.setForm_c_available(rs.getString("FORM_C_AVAILABLE")); dto.setFrequency(rs.getString("FREQUENCY")); dto.setHardware_flag(rs.getString("HARDWARE_FLAG")); dto.setHardware_type(rs.getString("HARDWARE_TYPE")); dto.setIndicative_value(rs.getString("INDICATIVE_VALUE")); dto.setInv_amt(rs.getString("INV_AMT")); dto.setLast_mile_media(rs.getString("LAST_MILE_MEDIA")); dto.setLast_mile_provider(rs.getString("LAST_MILE_PROVIDER")); dto.setLast_mile_remarks(rs.getString("LAST_MILE_REMARKS")); dto.setLegal_entity(rs.getString("LEGAL_ENTITY")); dto.setLicence_company(rs.getString("LICENCE_COMPANY")); dto.setLoc_date(rs.getString("LOC_DATE")); dto.setLoc_number(rs.getString("LOC_NUMBER")); dto.setLogical_circuit_id(rs.getString("LOGICAL_CIRCUIT_ID")); dto.setM6_order_id(rs.getString("M6_ORDER_ID")); dto.setNature_of_sale(rs.getString("NATURE_OF_SALE")); dto.setNew_order_remarks(rs.getString("NEW_ORDER_REMARKS")); dto.setNotice_period(rs.getString("NOTICE_PERIOD")); dto.setOrder_creation_date(rs.getString("ORDER_CREATION_DATE")); if (rs.getString("ORDER_CREATION_DATE") != null && !"".equals(rs.getString("ORDER_CREATION_DATE"))) { dto.setOrder_creation_date( (utility.showDate_Report(dto.getOrder_creation_date())).toUpperCase()); } dto.setOrder_date(rs.getString("ORDER_DATE")); dto.setOrder_line_id(rs.getString("ORDER_LINE_ID")); dto.setOrder_month(rs.getString("ORDER_MONTH")); dto.setOrder_number(rs.getString("ORDER_NUMBER")); dto.setOrder_stage(rs.getString("ORDER_STAGE")); dto.setOrder_type(rs.getString("ORDER_TYPE")); dto.setParty(rs.getString("PARTY")); dto.setParty_id(rs.getString("PARTY_ID")); dto.setPenalty_clause(rs.getString("PENALTY_CLAUSE")); dto.setPeriod_in_month(rs.getString("PERIOD_IN_MONTH")); dto.setPk_chageges_id(rs.getString("PK_CHAGEGES_ID")); dto.setPm_prov_date(rs.getString("PM_PROV_DATE")); dto.setPo_date(rs.getString("PO_DATE")); dto.setPre_crm_order_id(rs.getString("PRE_CRM_ORDER_ID")); dto.setProduct(rs.getString("PRODUCT")); dto.setProduct_name(rs.getString("PRODUCT_NAME")); dto.setRate_code(rs.getString("RATE_CODE")); dto.setRegion(rs.getString("REGION")); dto.setRequest_received_date(rs.getString("REQUEST_RECEIVED_DATE")); dto.setSec_loc(rs.getString("SEC_LOC")); dto.setService_no(rs.getString("SERVICE_NO")); dto.setService_order_type(rs.getString("SERVICE_ORDER_TYPE")); dto.setService_stage(rs.getString("SERVICE_STAGE")); dto.setSr_number(rs.getString("SR_NUMBER")); dto.setStandard_reason(rs.getString("STANDARD_REASON")); dto.setStart_date_days(rs.getString("START_DATE_DAYS")); dto.setStart_date_logic(rs.getString("START_DATE_LOGIC")); dto.setStart_date_months(rs.getString("START_DATE_MONTHS")); dto.setStore(rs.getString("STORE")); dto.setSub_product(rs.getString("SUB_PRODUCT")); dto.setTaxation(rs.getString("TAXATION")); dto.setToken_no(rs.getString("TOKEN_NO")); dto.setTot_po_amt(rs.getString("TOT_PO_AMT")); dto.setTotal_amount(rs.getString("TOTAL_AMOUNT")); dto.setType_of_sale(rs.getString("TYPE_OF_SALE")); dto.setVertical(rs.getString("VERTICAL")); dto.setProject_mgr(rs.getString("PROJECT_MGR")); dto.setProject_mgr_email(rs.getString("PROJECT_MGR_EMAIL")); dto.setProvision_bandwidth(rs.getString("PROVISION_BANDWIDTH")); dto.setQuote_no(rs.getString("QUOTE_NO")); dto.setRatio(rs.getString("RATIO")); dto.setRegion_name(rs.getString("REGION_NAME")); dto.setRe_logged_lsi_no(rs.getString("RE_LOGGED_LSI_NO")); dto.setService_name(rs.getString("SERVICE_NAME")); dto.setService_number(rs.getString("SERVICE_NUMBER")); dto.setSub_product_type(rs.getString("SUB_PRODUCT_TYPE")); dto.setTo_city(rs.getString("TO_CITY")); dto.setTo_site(rs.getString("TO_SITE")); dto.setUom(rs.getString("UOM")); dto.setZone(rs.getString("ZONE")); dto.setDis_sr(rs.getString("DIS_SR")); dto.setDod(rs.getString("DOD")); dto.setOrder_no(rs.getString("ORDER_NO")); dto.setLogical_si_no(rs.getString("LOGICAL_SI_NO")); dto.setLine_it_no(rs.getString("LINE_IT_NO")); dto.setAccount_id(rs.getString("ACCOUNT_ID")); dto.setCkt_id(rs.getString("CKT_ID")); dto.setPackage_id(rs.getString("PACKAGE_ID")); dto.setPackage_name(rs.getString("PACKAGE_NAME")); dto.setComponentinfoid(rs.getString("COMPONENTINFOID")); dto.setComponent_id(rs.getString("COMPONENT_ID")); dto.setComponent_name(rs.getString("COMPONENT_NAME")); dto.setComponent_status(rs.getString("COMPONENT_STATUS")); dto.setComponent_start_logic(rs.getString("COMPONENT_START_LOGIC")); dto.setComponent_start_date(rs.getString("COMPONENT_START_DATE")); dto.setComponent_end_logic(rs.getString("COMPONENT_END_LOGIC")); dto.setComponent_end_date(rs.getString("COMPONENT_END_DATE")); dto.setComp_start_days(rs.getString("COMP_START_DAYS")); dto.setComp_start_days(rs.getString("COMP_START_MONTHS")); dto.setComp_start_months(rs.getString("COMP_END_MONTHS")); dto.setComp_end_days(rs.getString("COMP_END_DAYS")); dto.setComp_end_months(rs.getString("COMP_END_MONTHS")); dto.setComponent_type(rs.getString("COMPONENT_TYPE")); dto.setComponent_instance_id(rs.getString("COMPONENT_INSTANCE_ID")); dto.setStart_component_token_no(rs.getString("START_COMPONENT_TOKEN_NO")); dto.setEnd_component_token_no(rs.getString("END_COMPONENT_TOKEN_NO")); dto.setHardware_type(rs.getString("HARDWARE_TYPE")); dto.setLink_type(rs.getString("LINK_TYPE")); dto.setTaxexcemption_reason(rs.getString("TAXEXEMPTION_REASON")); dto.setRate_code(rs.getString("RATE_CODE")); dto.setPri_loc(rs.getString("PRI_LOC")); dto.setSub_change_type(rs.getString("SUB_CHANGE_TYPE")); dto.setCHALLEN_NO(rs.getString("CHALLEN_NO")); dto.setLINE_NAME(rs.getString("LINE_NAME")); dto.setBilling_trig_date(rs.getString("BILLING_TRIG_DATE")); dto.setDispatch_address(rs.getString("DISPATCH_ADDRESS")); dto.setPo_number(rs.getString("PO_NUMBER")); dto.getInterfaceId(); if (pagingSorting.isPagingToBeDone() && recordCount == 0) { recordCount = rs.getInt("FULL_REC_COUNT"); } docListDetails.add(dto); } pagingSorting.setRecordCount(recordCount); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(cstmt); DbConnection.freeConnection(connection); } catch (Exception e) { } } return docListDetails; }
From source file:nl.nn.adapterframework.jdbc.JdbcQuerySenderBase.java
protected String executePackageQuery(Connection connection, PreparedStatement statement, String message) throws SenderException, JdbcException, IOException, JMSException { Object[] paramArray = new Object[10]; String callMessage = fillParamArray(paramArray, message); ResultSet resultset = null;/* w ww. j ava 2 s. co m*/ try { CallableStatement pstmt = connection.prepareCall(callMessage); if (getMaxRows() > 0) { pstmt.setMaxRows(getMaxRows() + (getStartRow() > 1 ? getStartRow() - 1 : 0)); } int var = 1; for (int i = 0; i < paramArray.length; i++) { if (paramArray[i] instanceof Timestamp) { pstmt.setTimestamp(var, (Timestamp) paramArray[i]); var++; } if (paramArray[i] instanceof java.sql.Date) { pstmt.setDate(var, (java.sql.Date) paramArray[i]); var++; } if (paramArray[i] instanceof String) { pstmt.setString(var, (String) paramArray[i]); var++; } if (paramArray[i] instanceof Integer) { int x = Integer.parseInt(paramArray[i].toString()); pstmt.setInt(var, x); var++; } if (paramArray[i] instanceof Float) { float x = Float.parseFloat(paramArray[i].toString()); pstmt.setFloat(var, x); var++; } } if (message.indexOf('?') != -1) { pstmt.registerOutParameter(var, Types.CLOB); // make sure enough space is available for result... } if ("xml".equalsIgnoreCase(getPackageContent())) { log.debug(getLogPrefix() + "executing a package SQL command"); pstmt.executeUpdate(); String pUitvoer = pstmt.getString(var); return pUitvoer; } log.debug(getLogPrefix() + "executing a package SQL command"); int numRowsAffected = pstmt.executeUpdate(); if (StringUtils.isNotEmpty(getResultQuery())) { Statement resStmt = null; try { resStmt = connection.createStatement(); log.debug("obtaining result from [" + getResultQuery() + "]"); ResultSet rs = resStmt.executeQuery(getResultQuery()); return getResult(rs); } finally { if (resStmt != null) { resStmt.close(); } } } if (getColumnsReturnedList() != null) { return getResult(getReturnedColumns(getColumnsReturnedList(), statement)); } if (isScalar()) { return numRowsAffected + ""; } return "<result><rowsupdated>" + numRowsAffected + "</rowsupdated></result>"; } catch (SQLException sqle) { throw new SenderException(getLogPrefix() + "got exception executing a package SQL command", sqle); } finally { try { if (resultset != null) { resultset.close(); } } catch (SQLException e) { log.warn(new SenderException(getLogPrefix() + "got exception closing resultset", e)); } } }
From source file:org.brucalipto.sqlutil.DB2SQLManager.java
/** * Method useful for using STORED PROCEDURE * @param spib The {@link SPInputBean} bean containing data to execute the stored procedure * @return The {@link SPOutputBean} containing returned values *///from w w w . ja v a2 s .c o m public SPOutputBean executeSP(final SPInputBean spib) throws SQLException { Connection conn = null; CallableStatement call = null; ResultSet resultSet = null; final String procedureName = spib.spName; SPParameter[] inputParameters = spib.inputParams; int[] outputParameters = spib.outputParams; final int inputParametersSize = inputParameters.length; final int outputParametersSize = outputParameters.length; final StringBuffer spName = new StringBuffer("call ").append(procedureName).append('('); int totalParameters = inputParametersSize + outputParametersSize; for (int i = 0; i < totalParameters; i++) { if (i != totalParameters - 1) { spName.append("?,"); } else { spName.append('?'); } } spName.append(")"); try { if (this.dataSource != null) { conn = this.dataSource.getConnection(); } else { conn = this.connection; } call = conn.prepareCall(spName.toString()); for (int i = 0; i < inputParametersSize; i++) { final SPParameter inputParam = inputParameters[i]; final int sqlType = inputParam.sqlType; final Object inputParamValue = inputParam.value; log.debug((i + 1) + ") Setting input value: " + inputParam); if (inputParamValue == null) { call.setNull(i + 1, sqlType); continue; } switch (sqlType) { case Types.VARCHAR: call.setString(i + 1, (String) inputParamValue); break; case Types.INTEGER: if (inputParamValue instanceof Integer) { call.setInt(i + 1, ((Integer) inputParamValue).intValue()); } else if (inputParamValue instanceof Long) { call.setLong(i + 1, ((Long) inputParamValue).longValue()); } break; case Types.DATE: call.setDate(i + 1, (Date) inputParamValue); break; case Types.BOOLEAN: call.setBoolean(i + 1, ((Boolean) inputParamValue).booleanValue()); break; case Types.CHAR: call.setString(i + 1, ((Character) inputParamValue).toString()); break; case Types.DOUBLE: call.setDouble(i + 1, ((Double) inputParamValue).doubleValue()); break; case Types.FLOAT: call.setFloat(i + 1, ((Float) inputParamValue).floatValue()); break; case Types.TIMESTAMP: call.setTimestamp(i + 1, (Timestamp) inputParamValue); break; default: call.setObject(i + 1, inputParamValue); break; } } for (int i = 0; i < outputParametersSize; i++) { int sqlType = outputParameters[i]; log.debug((i + 1) + ") Registering output type 'Types." + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'"); call.registerOutParameter(inputParametersSize + i + 1, sqlType); } log.debug("Going to call: '" + procedureName + "'"); long elapsedTime = System.currentTimeMillis(); boolean hasResultSet = call.execute(); log.debug("SP '" + procedureName + "' executed in " + (System.currentTimeMillis() - elapsedTime) + "millis"); if (hasResultSet) { log.debug("This SP is going to return also a resultSet"); } final SPOutputBean output = new SPOutputBean(); for (int i = 0; i < outputParametersSize; i++) { int sqlType = outputParameters[i]; log.debug((i + 1) + ") Getting output type 'Types." + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'"); final Object spResult = call.getObject(inputParametersSize + i + 1); SPParameter outParam = new SPParameter(sqlType, spResult); output.addResult(outParam); } if (hasResultSet) { RowSetDynaClass rowSetDynaClass = new RowSetDynaClass(call.getResultSet(), false); if (log.isDebugEnabled()) { log.debug("Going to return a RowSetDynaClass with following properties:"); DynaProperty[] properties = rowSetDynaClass.getDynaProperties(); for (int i = 0; i < properties.length; i++) { log.debug("Name: '" + properties[i].getName() + "'; Type: '" + properties[i].getType().getName() + "'"); } } SPParameter outParam = new SPParameter(Types.JAVA_OBJECT, rowSetDynaClass); output.addResult(outParam); } return output; } finally { closeResources(resultSet, call, conn); } }
From source file:org.brucalipto.sqlutil.OracleSQLManager.java
/** * Method useful for using STORED PROCEDURE * @param spib The {@link SPInputBean} bean containing data to execute the stored procedure * @return The {@link SPOutputBean} containing returned values *//*from w w w . java 2s .c o m*/ public SPOutputBean executeSP(final SPInputBean spib) throws SQLException { Connection conn = null; CallableStatement call = null; ResultSet resultSet = null; final String procedureName = spib.spName; SPParameter[] inputParameters = spib.inputParams; int[] outputParameters = spib.outputParams; final int inputParametersSize = inputParameters.length; final int outputParametersSize = outputParameters.length; final StringBuffer spName = new StringBuffer("{ call ").append(procedureName).append('('); int totalParameters = inputParametersSize + outputParametersSize; for (int i = 0; i < totalParameters; i++) { if (i != totalParameters - 1) { spName.append("?,"); } else { spName.append('?'); } } spName.append(") }"); log.debug("Going to call: '" + spName + "'"); try { conn = this.dataSource.getConnection(); call = conn.prepareCall(spName.toString()); for (int i = 0; i < inputParametersSize; i++) { final SPParameter inputParam = inputParameters[i]; final int sqlType = inputParam.sqlType; final Object inputParamValue = inputParam.value; log.debug((i + 1) + ") Setting input value 'Types." + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'-'" + inputParamValue + "'"); if (inputParamValue == null) { call.setNull(i + 1, sqlType); continue; } switch (sqlType) { case Types.VARCHAR: call.setString(i + 1, (String) inputParamValue); break; case Types.INTEGER: if (inputParamValue instanceof Integer) { call.setInt(i + 1, ((Integer) inputParamValue).intValue()); } else if (inputParamValue instanceof Long) { call.setLong(i + 1, ((Long) inputParamValue).longValue()); } break; case Types.DATE: call.setDate(i + 1, (Date) inputParamValue); break; case Types.BOOLEAN: call.setBoolean(i + 1, ((Boolean) inputParamValue).booleanValue()); break; case Types.CHAR: call.setString(i + 1, ((Character) inputParamValue).toString()); break; case Types.DOUBLE: call.setDouble(i + 1, ((Double) inputParamValue).doubleValue()); break; case Types.FLOAT: call.setFloat(i + 1, ((Float) inputParamValue).floatValue()); break; case Types.TIMESTAMP: call.setTimestamp(i + 1, (Timestamp) inputParamValue); break; default: call.setObject(i + 1, inputParamValue); break; } } for (int i = 0; i < outputParametersSize; i++) { int sqlType = outputParameters[i]; log.debug((i + 1) + ") Registering output type 'Types." + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'"); call.registerOutParameter(inputParametersSize + i + 1, sqlType); } call.execute(); final SPOutputBean output = new SPOutputBean(); for (int i = 0; i < outputParametersSize; i++) { int sqlType = outputParameters[i]; log.debug((i + 1) + ") Getting output type 'Types." + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'"); final Object spResult = call.getObject(inputParametersSize + i + 1); SPParameter outParam = null; if (sqlType == SQLUtilTypes.CURSOR) { resultSet = (ResultSet) spResult; RowSetDynaClass rowSetDynaClass = new RowSetDynaClass(resultSet, false); if (log.isDebugEnabled()) { log.debug("Going to return a RowSetDynaClass with following properties:"); DynaProperty[] properties = rowSetDynaClass.getDynaProperties(); for (int j = 0; j < properties.length; j++) { log.debug("Name: '" + properties[j].getName() + "'; Type: '" + properties[j].getType().getName() + "'"); } } outParam = new SPParameter(sqlType, rowSetDynaClass); } else { outParam = new SPParameter(sqlType, spResult); } output.addResult(outParam); } return output; } catch (SQLException sqle) { log.error("Caught SQLException", sqle); } finally { closeResources(resultSet, call, conn); } return null; }
From source file:sk.uniza.fri.pds.spotreba.energie.service.SeHistoriaService.java
@Override public void create(SeHistoria object) { try (Connection connection = OracleJDBCConnector.getConnection();) { CallableStatement stmnt = connection.prepareCall("BEGIN INSERT_SE_HISTORIA(?, ?, ?,?); END;"); stmnt.setInt(1, object.getCisloOdberatela()); stmnt.setInt(2, object.getCisZariadenia()); stmnt.setDate(3, Utils.utilDateToSqlDate(object.getDatumInstalacie())); stmnt.setInt(4, object.getZamestnanecVykonvajuciZmenu()); stmnt.execute();//from w ww . j av a 2 s. c om } catch (SQLException e) { throw new RuntimeException(e); } }
From source file:sk.uniza.fri.pds.spotreba.energie.service.SeHistoriaService.java
@Override public void update(SeHistoria old, SeHistoria object) { try (Connection connection = OracleJDBCConnector.getConnection();) { CallableStatement stmnt = connection.prepareCall("BEGIN ODOBER_ZARIADENIE(?, ?, ?, ?, ?); END;"); stmnt.setInt(1, old.getCisloOdberatela()); stmnt.setInt(2, old.getCisZariadenia()); stmnt.setDate(3, Utils.utilDateToSqlDate(object.getDatumOdobratia())); stmnt.setInt(4, object.getZamestnanecVykonvajuciZmenu()); stmnt.setInt(5, object.getSpotrebaPredOdobratim()); stmnt.execute();//from w w w. j a va 2s .c o m } catch (SQLException e) { throw new RuntimeException(e); } }
From source file:sk.uniza.fri.pds.spotreba.energie.service.SeHistoriaService.java
public List<KrokSpotreby> getSpendingStatistics(SpendingStatisticsParameters params) { try (Connection connection = OracleJDBCConnector.getConnection();) { CallableStatement stmnt = connection .prepareCall("SELECT * FROM TABLE(get_statistika_spotreby(?,?,?,?,?))"); stmnt.setInt(1, params.getIdSpotrebitela()); stmnt.setDate(2, Utils.utilDateToSqlDate(params.getDatumOd())); stmnt.setDate(3, Utils.utilDateToSqlDate(params.getDatumDo())); stmnt.setInt(4, params.getGranularita().val); stmnt.setString(5, params.getVelicina().name().toLowerCase()); ResultSet result = stmnt.executeQuery(); List<KrokSpotreby> output = new LinkedList<>(); while (result.next()) { KrokSpotreby o = new KrokSpotreby(); o.setDatumOd(result.getDate("DATUM_OD")); o.setDatumDo(result.getDate("DATUM_DO")); o.setSpotreba(result.getDouble("SPOTREBA")); output.add(o);// w ww . ja v a 2 s. co m } return output; } catch (SQLException e) { throw new RuntimeException(e); } }
From source file:sk.uniza.fri.pds.spotreba.energie.service.SeHistoriaService.java
public List<ZvysenieSpotreby> getIncreasedSpendingStatistics(IncreasedSpendingStatisticParams params, double loadFactor) { try (Connection connection = OracleJDBCConnector.getConnection();) { String fn = "get_zvysena_miera_spotreby"; if (loadFactor < 1) { fn = "get_znizena_miera_spotreby"; }/*from w w w. j a v a2 s . c om*/ CallableStatement stmnt = connection.prepareCall("SELECT * FROM TABLE(" + fn + "(?,?,?))"); stmnt.setDate(1, Utils.utilDateToSqlDate(params.getDatumOd())); stmnt.setDate(2, Utils.utilDateToSqlDate(params.getDatumDo())); stmnt.setDouble(3, loadFactor); ResultSet result = stmnt.executeQuery(); List<ZvysenieSpotreby> output = new LinkedList<>(); while (result.next()) { ZvysenieSpotreby o = new ZvysenieSpotreby(); o.setMeno(result.getString("MENO")); o.setPriemernaSpotrebaVMinulosti(result.getDouble("PRIEMERNA_SPOTREBA_V_MINULOSTI")); o.setVelicina(MeraciaVelicina.valueOf(result.getString("VELICINA").toUpperCase())); o.setZvysenaSpotreba(result.getDouble("ZVYSENA_SPOTREBA")); output.add(o); } return output; } catch (SQLException e) { throw new RuntimeException(e); } }
From source file:sk.uniza.fri.pds.spotreba.energie.service.SeHistoriaService.java
public List<CelkovaStatistika> getOveralStatistics(StatistikaSpotriebParams params) { try (Connection connection = OracleJDBCConnector.getConnection();) { CallableStatement stmnt = connection .prepareCall("SELECT get_najm_najve_spotreba(?,?,?,?,?,?) from dual"); stmnt.setString(1, params.getTypOdberatela().val.toString()); stmnt.setString(2, params.getKategoriaOdberatela().name()); stmnt.setInt(3, params.getIdRegionu()); stmnt.setString(4, params.getVelicina().name().toLowerCase()); stmnt.setDate(5, Utils.utilDateToSqlDate(params.getDatumOd())); stmnt.setDate(6, Utils.utilDateToSqlDate(params.getDatumDo())); ResultSet result = stmnt.executeQuery(); List<CelkovaStatistika> output = new LinkedList<>(); while (result.next()) { CelkovaStatistika o = new CelkovaStatistika(); Object[] attributes = ((Struct) result.getObject(1)).getAttributes(); o.setMesiacMinimalnejSpotreby(((Timestamp) attributes[0])); o.setMinimalnaSpotreba(((BigDecimal) attributes[1]).intValue()); o.setMesiacMaximalnejSpotreby(((Timestamp) attributes[2])); o.setMaximalnaSpotreba(((BigDecimal) attributes[3]).intValue()); output.add(o);//w ww. j av a 2 s . c om } return output; } catch (SQLException e) { throw new RuntimeException(e); } }
From source file:sk.uniza.fri.pds.spotreba.energie.service.SeHistoriaService.java
public List<NajminajucejsiSpotrebitel> getNajnminajucejsiSpotrebitelia( NajminajucejsiSpotrebiteliaParams params) { try (Connection connection = OracleJDBCConnector.getConnection();) { CallableStatement stmnt = connection .prepareCall("select meno, cislo_odberatela from (select rank() over (\n" + " order by get_spotreba_za_obdobie(cislo_odberatela,?,?,?)) as rn,\n" + " count(*) over() as pocet,\n" + " meno||' '||priezvisko as meno,\n" + " cislo_odberatela \n" + " from SE_ODBERATEL join SE_OSOBA using(rod_cislo)) \n" + "where rn<pocet*0.1"); stmnt.setString(3, params.getVelicina().name().toLowerCase()); stmnt.setDate(1, Utils.utilDateToSqlDate(params.getDatumOd())); stmnt.setDate(2, Utils.utilDateToSqlDate(params.getDatumDo())); ResultSet result = stmnt.executeQuery(); List<NajminajucejsiSpotrebitel> output = new LinkedList<>(); while (result.next()) { NajminajucejsiSpotrebitel o = new NajminajucejsiSpotrebitel(); o.setMeno(result.getString("meno")); o.setCisloOdberatela(result.getInt("cislo_odberatela")); output.add(o);//from ww w. ja v a2 s.c o m } return output; } catch (SQLException e) { throw new RuntimeException(e); } }