Example usage for java.sql CallableStatement executeQuery

List of usage examples for java.sql CallableStatement executeQuery

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. CallableStatement
  6. executeQuery

Introduction

In this page you can find the example usage for java.sql CallableStatement executeQuery.

Prototype

ResultSet executeQuery() throws SQLException;

Source Link

Document

Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

Usage

From source file:com.cisco.dvbu.ps.deploytool.services.RegressionManagerUtils.java

/**
 * Similar to the same method in original pubtest utility, but doesn't throw an exception if 0 rows are returned
 * and uses existing(established) JDBC connection corresponding to its published datasource name.
 * /*from  w  w w. j a  v a 2 s  .c o  m*/
 */
public static String executeProcedure(RegressionItem item, HashMap<String, Connection> cisConnections,
        String outputFile, String delimiter, String printOutputType) throws CompositeException {
    // Set the command and action name
    String command = "executeProcedure";
    String actionName = "REGRESSION_TEST";

    int rows = 0;
    String result = null;
    Connection conn = null;
    CallableStatement stmt = null;
    ResultSet rs = null;
    start = System.currentTimeMillis();
    long firstRowLatency = 0L;

    // Don't execute if -noop (NO_OPERATION) has been set otherwise execute under normal operation.
    if (CommonUtils.isExecOperation()) {
        try {
            conn = getJdbcConnection(item.database, cisConnections); // don't need to check for null here.

            String URL = null;
            String userName = null;
            if (conn.getMetaData() != null) {
                if (conn.getMetaData().getURL() != null)
                    URL = conn.getMetaData().getURL();
                if (conn.getMetaData().getUserName() != null)
                    userName = conn.getMetaData().getUserName();
            }
            RegressionManagerUtils.printOutputStr(printOutputType, "debug",
                    "RegressionManagerUtils.executeQuery(item, cisConnections, outputFile, delimiter, printOutputType).  item.database="
                            + item.database + "  cisConnections.URL=" + URL + "  cisConnections.userName="
                            + userName + "  outputFile=" + outputFile + "  delimiter=" + delimiter
                            + "  printOutputType=" + printOutputType,
                    "");
            RegressionManagerUtils.printOutputStr(printOutputType, "debug",
                    "DEBUG: connection to DB successful", "");

            String query = item.input.replaceAll("\n", " ");
            // Convert a CALL statement into a SELECT * FROM statement

            // { CALL SCH1.LookupProduct( 3 ) } --> SCH1.LookupProduce( 3 )
            if (query.toUpperCase().contains("CALL")) {
                query = "SELECT * FROM " + RegressionManagerUtils.getProcedure(query);
                ;
            }

            // Prepare the query
            stmt = (CallableStatement) conn.prepareCall(query);

            // Register output parameter types
            for (int i = 0; i < item.outTypes.length; i++) {
                if (!"-".equals(item.outTypes[i])) {
                    int jdbcType = -1;
                    try {
                        jdbcType = Types.class.getField(item.outTypes[i]).getInt(null);
                    } catch (Exception e) {
                        RegressionManagerUtils.error(item.lineNum, item.outTypes[i],
                                "No such JDBC type in java.sql.Types");
                    }
                    stmt.registerOutParameter(i + 1, jdbcType);
                }
            }
            stmt.executeQuery();

            // Print scalars
            ParameterMetaData pmd = stmt.getParameterMetaData();
            int params = pmd.getParameterCount();
            boolean addSep = false;
            String content = "";
            for (int i = 0; i < params; i++) {
                if (addSep) {
                    content += delimiter;
                }
                if (stmt.getObject(i + 1) != null)
                    content += stmt.getObject(i + 1).toString();
                else
                    content += "";
                addSep = true;
            }
            if (outputFile != null)
                CommonUtils.appendContentToFile(outputFile, content);
            RegressionManagerUtils.printOutputStr(printOutputType, "results", content, "");

            // Get the result cursor and metadata cursor        
            rs = stmt.getResultSet();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columns = rsmd.getColumnCount();

            // Get the column metadata                      
            addSep = false;
            content = "";
            for (int i = 0; i < columns; i++) {
                if (addSep) {
                    content += delimiter;
                }
                if (rsmd.getColumnName(i + 1) != null)
                    content += rsmd.getColumnName(i + 1).toString();
                else
                    content += "";
                addSep = true;
            }
            if (outputFile != null)
                CommonUtils.appendContentToFile(outputFile, content);
            RegressionManagerUtils.printOutputStr(printOutputType, "results", content, "");

            // Print cursors
            boolean firstRow = true;
            while (rs != null) {
                // Read the values
                while (rs.next()) {
                    if (firstRow) {
                        firstRowLatency = System.currentTimeMillis() - start;
                        firstRow = false;
                    }
                    addSep = false;
                    content = "";
                    for (int i = 0; i < columns; i++) {
                        if (addSep) {
                            content += delimiter;
                        }
                        if (rs.getObject(i + 1) != null)
                            content += rs.getObject(i + 1).toString();
                        else
                            content += "";
                        addSep = true;
                    }
                    if (outputFile != null)
                        CommonUtils.appendContentToFile(outputFile, content);
                    RegressionManagerUtils.printOutputStr(printOutputType, "results", content, "");
                    rows++;
                }
                stmt.getMoreResults();
                rs = stmt.getResultSet();
            }
        } catch (SQLException e) {
            throw new CompositeException("executeProcedure(): " + e.getMessage());
        } catch (Exception e) {
            throw new CompositeException("executeProcedure(): " + e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
                rs = null;
                stmt = null;
                throw new CompositeException(
                        "executeProcedure(): unable to close ResultSet or Statement" + e.getMessage());
            }
        }
        RegressionManagerUtils.printOutputStr(printOutputType, "results", "\nCompleted executeProcedure()", "");
    } else {
        logger.info("\n\nWARNING - NO_OPERATION: COMMAND [" + command + "], ACTION [" + actionName
                + "] WAS NOT PERFORMED.\n");
    }

    // <rows>:<firstRowLatency>
    result = "" + rows + ":" + firstRowLatency;
    return result;
    /* Note: to process this result string on the client invocation side use the following pattern:
     * 
     *    String result = RegressionManagerUtils.executeQuery(item, cisConnections, outputFile, delim, printOutputType, "results");
       String results[] = result.split(":");
       if (results.length > 1) {
          rowCount = Integer.valueOf(results[0]);
             firstRowLatency.addAndGet(Long.parseLong(results[1]));                    
       }                  
     */
}

From source file:com.ibm.ioes.dao.ReportsDao_Usage.java

public ArrayList<NewOrderDto> searchOrderStatus(NewOrderDto objDto) throws Exception {
    //Nagarjuna/*from w w  w. j  av a  2  s  . c  o m*/
    String methodName = "searchOrderStatus", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    Connection connection = null;
    CallableStatement callstmt = null;
    ResultSet rs = null;
    NewOrderDto objConDto = new NewOrderDto();
    ArrayList<NewOrderDto> lstOrderStatusList = new ArrayList<NewOrderDto>();
    try {
        connection = DbConnection.getReportsConnectionObject();
        callstmt = connection.prepareCall(sqlSearchOrderStatues);
        callstmt.setString(1, objDto.getOrderStatus());
        rs = callstmt.executeQuery();
        while (rs.next()) {
            objConDto = new NewOrderDto();
            objConDto.setOrderStatus(rs.getString("STAGE"));
            lstOrderStatusList.add(objConDto);
        }
    } catch (Exception ex) {
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);
        //ex.printStackTrace();
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(callstmt);
            DbConnection.freeConnection(connection);
        } catch (SQLException e) {
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);
            //   e.printStackTrace();
        }
    }
    return lstOrderStatusList;
}

From source file:com.ibm.ioes.dao.ReportsDao_Usage.java

public ArrayList<M6OrderStatusDto> viewM6ResponseHistory(M6OrderStatusDto objDto, long m6OrderNo) {
    //Nagarjuna//from   ww  w  .  ja  v  a 2 s . c o m
    String methodName = "viewM6ResponseHistory", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    Connection connection = null;
    CallableStatement getM6ResponseHistory = null;
    ResultSet rsM6ResponseHistory = null;
    ArrayList<M6OrderStatusDto> listM6ResponseHistory = new ArrayList<M6OrderStatusDto>();
    M6OrderStatusDto objNewOrderDto = null;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");

    try {
        connection = DbConnection.getReportsConnectionObject();
        getM6ResponseHistory = connection.prepareCall(sqlGetM6ResponseHistoryReport);

        if (m6OrderNo != 0) {
            getM6ResponseHistory.setLong(1, m6OrderNo);
        } else {
            getM6ResponseHistory.setNull(1, java.sql.Types.BIGINT);
        }
        //   getM6ResponseHistory.setLong(1, Intm6OrderNo);
        rsM6ResponseHistory = getM6ResponseHistory.executeQuery();
        while (rsM6ResponseHistory.next()) {
            objNewOrderDto = new M6OrderStatusDto();
            objNewOrderDto.setOrderNo(rsM6ResponseHistory.getString("orderNo"));
            objNewOrderDto.setM6OrderNo(String.valueOf(m6OrderNo));
            objNewOrderDto.setRemarks(rsM6ResponseHistory.getString("Reason"));
            objNewOrderDto.setCreatedDate((rsM6ResponseHistory.getString("Created_Date")));
            if (!(rsM6ResponseHistory.getString("Created_Date") == null
                    || rsM6ResponseHistory.getString("Created_Date") == "")) {
                objNewOrderDto.setCreatedDate((utility
                        .showDate_Report(new Date(rsM6ResponseHistory.getTimestamp("Created_Date").getTime())))
                                .toUpperCase());
            }

            listM6ResponseHistory.add(objNewOrderDto);
        }
    } catch (Exception ex) {
        //ex.printStackTrace();   
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);
    } finally {
        try {
            DbConnection.closeResultset(rsM6ResponseHistory);
            DbConnection.closeCallableStatement(getM6ResponseHistory);
            DbConnection.freeConnection(connection);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            //e.printStackTrace();
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);
        }
    }
    return listM6ResponseHistory;
}

From source file:com.ibm.ioes.dao.ReportsDao_Usage.java

public ArrayList<ReportsDto> viewMasterHistory(ReportsDto objDto) throws Exception {
    //Nagarjuna/*from  w  w  w.j a  v  a 2 s  .com*/
    String methodName = "viewMasterHistory", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    Connection connection = null;
    CallableStatement callstmt = null;

    ResultSet rs = null;
    ReportsDto objConDto = new ReportsDto();
    ArrayList<ReportsDto> historyList = new ArrayList<ReportsDto>();
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    try {
        connection = DbConnection.getReportsConnectionObject();
        callstmt = connection.prepareCall(sqlGetMasterReportStatus);
        callstmt.setString(1, objDto.getMasterValue());

        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

        callstmt.setString(2, pagingSorting.getSortByColumn());// columnName
        callstmt.setString(3, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        callstmt.setInt(4, pagingSorting.getStartRecordId());// start index
        callstmt.setInt(5, pagingSorting.getEndRecordId());// end index
        callstmt.setInt(6, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end

        rs = callstmt.executeQuery();
        int recordCount = 0;
        while (rs.next()) {
            objConDto = new ReportsDto();
            objConDto.setMasterName(rs.getString("MASTERNAME"));
            objConDto.setColumnName(rs.getString("COLUMNS"));
            objConDto.setOldValues(rs.getString("OLD_VALUES"));
            objConDto.setNewValues(rs.getString("NEW_VALUES"));
            objConDto.setOperationName(rs.getString("OPERATION_NAME"));
            objConDto.setModifiedDate(rs.getString("MODIFIED_DATE"));
            if (rs.getString("MODIFIED_DATE") != null && !"".equals(rs.getString("MODIFIED_DATE"))) {
                objConDto.setModifiedDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("MODIFIED_DATE").getTime())))
                                .toUpperCase());
            }

            objConDto.setModifiedBy(rs.getString("MODIFIED_BY"));
            objConDto.setAttribiuteId(rs.getString("ATTRIBUTE_ID"));
            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            historyList.add(objConDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);
        //ex.printStackTrace();
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(callstmt);
            DbConnection.freeConnection(connection);
        } catch (SQLException e) {
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);
            //e.printStackTrace();
        }
    }
    return historyList;
}

From source file:com.ibm.ioes.dao.ReportsDao_Usage.java

public ArrayList<ReportsDto> viewAttributeDetailsReport(ReportsDto objDto) {
    //   Nagarjuna
    String methodName = "viewAttributeDetailsReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    Connection connection = null;
    CallableStatement proc = null;
    ResultSet rs = null;//from  ww  w  .  ja  v a  2 s . c  o  m
    int recordCount = 0;
    ArrayList<ReportsDto> listAttributeDetailsReport = new ArrayList<ReportsDto>();
    ReportsDto objReportsDto = null;
    try {
        connection = DbConnection.getReportsConnectionObject();
        proc = connection.prepareCall(sqlGetAttributeDetailsReport);

        if (objDto.getCustLogicalSI() != 0 && !"".equals(objDto.getCustLogicalSI())) {
            proc.setInt(1, objDto.getCustLogicalSI());
        } else {
            proc.setNull(1, java.sql.Types.BIGINT);
        }

        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

        proc.setString(2, pagingSorting.getSortByColumn());// columnName
        proc.setString(3, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(4, pagingSorting.getStartRecordId());// start index
        proc.setInt(5, pagingSorting.getEndRecordId());// end index
        proc.setInt(6, (pagingSorting.isPagingToBeDone() ? 1 : 0));
        // index
        rs = proc.executeQuery();
        while (rs.next()) {
            objReportsDto = new ReportsDto();

            objReportsDto.setServiceName(rs.getString("SERVICENAME"));
            objReportsDto.setLinename(rs.getString("LINENAME"));
            objReportsDto.setCrm_att(rs.getString("CRM_ATT"));
            objReportsDto.setM6_label_name(rs.getString("LABEL_NAME"));
            objReportsDto.setM6_label_value(rs.getString("LABEL_VALUE"));
            objReportsDto.setCustLogicalSI(rs.getInt("CUSTOMER_LOGICAL_SI_NO"));
            objReportsDto.setCrm_order_id(rs.getInt("LAST_CRM_ORDER_ID"));

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            listAttributeDetailsReport.add(objReportsDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        //ex.printStackTrace();
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(proc);
            DbConnection.freeConnection(connection);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            //e.printStackTrace();
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna
        }
    }
    return listAttributeDetailsReport;
}

From source file:com.ibm.ioes.dao.ReportsDao_Usage.java

public ArrayList<ReportsDto> viewM6OrderCancelReport(ReportsDto objDto) throws Exception {
    //   Nagarjuna
    String methodName = "viewM6OrderCancelReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    ArrayList<ReportsDto> objUserList = new ArrayList<ReportsDto>();
    Connection conn = null;/*from  w  ww  .  j  a  va 2 s.  c  o m*/
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;
    Utility utility = new Utility();

    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    try {

        conn = DbConnection.getReportsConnectionObject();

        proc = conn.prepareCall(sqlM6OrderCancelReport);
        if (objDto.getCanceldate() != null && !"".equals(objDto.getCanceldate())) {
            proc.setString(1, objDto.getCanceldate().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }

        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

        proc.setString(2, pagingSorting.getSortByColumn());// columnName
        proc.setString(3, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(4, pagingSorting.getStartRecordId());// start index
        proc.setInt(5, pagingSorting.getEndRecordId());// end index
        proc.setInt(6, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end
        // index
        rs = proc.executeQuery();
        int countFlag = 0;
        while (rs.next() != false) {
            countFlag++;

            objDto = new ReportsDto();
            objDto.setOrderNumber(rs.getInt("ORDERNO"));
            objDto.setServiceId(rs.getInt("SERVICEID"));
            objDto.setOrderType(rs.getString("ORDERTYPE"));
            objDto.setServiceType(rs.getString("SERVICETYPE"));
            objDto.setCreatedDate(rs.getString("CREATEDDATE"));
            if (rs.getString("CREATEDDATE") != null && !"".equals(rs.getString("CREATEDDATE"))) {
                objDto.setCreatedDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("CREATEDDATE").getTime())))
                                .toUpperCase());
            }
            objDto.setEffDate(rs.getString("EFFSTARTDATE"));
            if (rs.getString("EFFSTARTDATE") != null && !"".equals(rs.getString("EFFSTARTDATE"))) {
                Date date = df.parse(objDto.getEffDate());
                objDto.setEffDate((utility.showDate_Report(date)).toUpperCase());
            }
            objDto.setRfs_date(rs.getString("RFS_DATE"));
            if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) {
                Date date = df.parse(objDto.getRfs_date());
                objDto.setRfs_date((utility.showDate_Report(date)).toUpperCase());
            }
            objDto.setProductName(rs.getString("PRODUCTNAME"));
            objDto.setSubProductName(rs.getString("SUBPRODUCTNAME"));
            objDto.setServiceStage(rs.getString("SERVICESTAGE"));
            objDto.setCrmAccountId(rs.getInt("CRMACCOUNTNO"));
            objDto.setLogicalSINo(rs.getString("LOGICAL_SI_NO"));
            objDto.setCancelServiceReason(rs.getString("CANCEL_SERVICE_REASON"));
            objDto.setOrdertype_demo(rs.getString("order_type_DEMO"));
            objDto.setBisource(rs.getString("BISOURCE"));
            objDto.setCanceldate(rs.getString("CANCEL_DATE"));
            if (rs.getString("CANCEL_DATE") != null && !"".equals(rs.getString("CANCEL_DATE"))) {
                objDto.setCanceldate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("CANCEL_DATE").getTime())))
                                .toUpperCase());
            }

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");

            }
            objUserList.add(objDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna
        //ex.printStackTrace();
        throw new Exception("SQL Exception : " + ex.getMessage(), ex);
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(proc);
            DbConnection.freeConnection(conn);

        } catch (Exception e) {
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna
            //e.printStackTrace();
            throw new Exception("Exception : " + e.getMessage(), e);
        }
    }
    return objUserList;
}

From source file:com.ibm.ioes.dao.ReportsDao_Usage.java

/**
 * Create a Report to generate LEPM Order Cancel Report
         // ww  w  .  j a va 2 s.  c  o  m
 * @param obj   a DTO which consist all the search parameters
 * @return      a ArrayList of dto which consist all the data of reports 
 * @exception   Sql Exception
 *            
 */
public ArrayList<ReportsDto> viewLEPMOrderCancelReport(ReportsDto objDto) throws Exception {
    //   Nagarjuna
    String methodName = "viewLEPMOrderCancelReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    ArrayList<ReportsDto> objUserList = new ArrayList<ReportsDto>();
    Connection conn = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;
    int countFlag = 0;
    ReportsDto objReportsDto = null;
    ArrayList<ReportsDto> listSearchDetails = new ArrayList<ReportsDto>();

    try {

        conn = DbConnection.getReportsConnectionObject();

        proc = conn.prepareCall(sqlLEPMOrderCancelReport);
        if (objDto.getCanceldate() != null && !"".equals(objDto.getCanceldate())) {
            proc.setString(1, objDto.getCanceldate().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }

        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

        proc.setString(2, pagingSorting.getSortByColumn());// columnName
        proc.setString(3, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(4, pagingSorting.getStartRecordId());// start index
        proc.setInt(5, pagingSorting.getEndRecordId());// end index
        proc.setInt(6, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end
        rs = proc.executeQuery();

        while (rs.next() != false) {
            objDto = new ReportsDto();
            objDto.setPartyName(rs.getString("PARTYNAME"));
            objDto.setOrderNo(rs.getString("ORDERNO"));
            objDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE"));
            if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) {
                objDto.setCopcApproveDate(
                        (Utility.showDate_Report((rs.getTimestamp("COPC_APPROVAL_DATE")))).toUpperCase());
            }
            objDto.setLogicalCircuitId(rs.getString("LOGICALCKTID"));
            objDto.setServiceId(rs.getInt("SERVICENO"));
            objDto.setQuoteNo(rs.getString("QUOTENO"));
            objDto.setProductName(rs.getString("PRODUCTNAME"));
            objDto.setSubProductName(rs.getString("SUBPRODUCTNAME"));
            objDto.setPrimarylocation(rs.getString("FROM_SITE"));
            objDto.setSeclocation(rs.getString("TO_SITE"));
            objDto.setOrdersubtype(rs.getString("ORDERSUBTYPE"));
            objDto.setBandwaidth(rs.getString("BANDWIDTH"));
            objDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT"));
            objDto.setPrjmngname(rs.getString("PMNAME"));
            objDto.setPrjmgremail(rs.getString("PMEMAIL"));
            objDto.setActmngname(rs.getString("ACTMNAME"));
            objDto.setZoneName(rs.getString("ZONENNAME"));
            objDto.setRegionName(rs.getString("REGIONNAME"));
            objDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS"));
            objDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS"));
            objDto.setCustPoDate(rs.getString("CUSTPODATE"));
            if (rs.getString("CUSTPODATE") != null && !"".equals(rs.getString("CUSTPODATE"))) {

                objDto.setCustPoDate((Utility.showDate_Report((rs.getTimestamp("CUSTPODATE")))).toUpperCase());

            }
            objDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER"));
            objDto.setOrderDate(rs.getString("ORDERDATE"));
            if (rs.getString("ORDERDATE") != null && !"".equals(rs.getString("ORDERDATE"))) {

                objDto.setOrderDate((Utility.showDate_Report((rs.getTimestamp("ORDERDATE")))).toUpperCase());

            }
            objDto.setPmApproveDate(rs.getString("PM_APPROVAL_DATE"));
            if (rs.getString("PM_APPROVAL_DATE") != null && !"".equals(rs.getString("PM_APPROVAL_DATE"))) {
                objDto.setPmApproveDate(
                        (Utility.showDate_Report((rs.getTimestamp("PM_APPROVAL_DATE")))).toUpperCase());
            }
            objDto.setAmApproveDate(rs.getString("AM_APPROVAL_DATE"));
            if (rs.getString("AM_APPROVAL_DATE") != null && !"".equals(rs.getString("AM_APPROVAL_DATE"))) {
                objDto.setAmApproveDate(
                        (Utility.showDate_Report((rs.getTimestamp("AM_APPROVAL_DATE")))).toUpperCase());
            }
            objDto.setNio_approve_date(rs.getString("NIO_APPROVAL_DATE"));
            if (rs.getString("NIO_APPROVAL_DATE") != null && !"".equals(rs.getString("NIO_APPROVAL_DATE"))) {
                objDto.setNio_approve_date(
                        (Utility.showDate_Report((rs.getTimestamp("NIO_APPROVAL_DATE")))).toUpperCase());
            }
            objDto.setDemo_infrastartdate(rs.getString("DEMP_INFRA_START_DATE"));
            objDto.setDemo_infra_enddate(rs.getString("DEMO_INFRA_ENDDATE"));
            objDto.setRfs_date(rs.getString("RFS_DATE"));
            if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) {

                objDto.setRfs_date((Utility.showDate_Report((rs.getTimestamp("RFS_DATE")))).toUpperCase());

            }
            objDto.setOrdercategory(rs.getString("ORDERCATEGORY"));
            objDto.setOrderStatus(rs.getString("STATUS"));
            objDto.setLine_desc(rs.getString("LINE_ITEM_DESC"));
            objDto.setLinename(rs.getString("LINENAME"));
            objDto.setSub_linename(rs.getString("ORDER_SUBLINENAME"));
            objDto.setChargeName(rs.getString("CHARGE_NAME"));
            objDto.setChargeinfoID(rs.getString("CHARGEINFOID"));
            objDto.setServiceProductID(rs.getInt("LINENO"));
            objDto.setServiceName(rs.getString("SERVICENAME"));
            objDto.setAccountID(rs.getInt("ACCOUNTID"));
            objDto.setLicCompanyName(rs.getString("LCOMPANYNAME"));
            objDto.setEntity(rs.getString("COMPANYNAME"));
            objDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));
            objDto.setFrequencyName(rs.getString("PAYMENTTERM"));
            objDto.setChargeTypeName(rs.getString("CYCLICNONCYCLIC"));
            if ("NULL".equals(rs.getString("SERVICETYPE"))) {
                objDto.setServiceType("");
            } else {
                objDto.setServiceType(rs.getString("SERVICETYPE"));
            }
            objDto.setUom(rs.getString("UOM"));
            objDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH"));
            objDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM"));
            objDto.setFrom_city(rs.getString("FROM_CITY"));
            objDto.setTo_city(rs.getString("TO_CITY"));
            objDto.setOldordertotal(rs.getString("OLD_ORDER_TOTAL"));
            objDto.setOldlineamt(rs.getString("OLD_LINE_AMT"));
            objDto.setOld_contract_period(rs.getString("OLD_CONTRACTPERIOD"));
            objDto.setRatio(rs.getString("RATIO"));
            objDto.setTaxation(rs.getString("TAXATIONVALUE"));
            objDto.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID"));
            objDto.setDistance(rs.getString("DISTANCE"));
            objDto.setAccountManager(rs.getString("ACTMEMAIL"));
            objDto.setCurrencyCode(rs.getString("CURNAME"));
            objDto.setOrderTotal(rs.getDouble("ORDERTOTAL"));
            objDto.setPoAmount(rs.getString("POAMOUNT"));
            objDto.setBisource(rs.getString("BISOURCE"));
            objDto.setOrderType(rs.getString("ORDERTYPE"));
            objDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS"));
            objDto.setParent_name(rs.getString("PARENTNAME"));
            objDto.setServiceStage(rs.getString("STAGE"));
            objDto.setCanceldate(rs.getString("CANCEL_DATE"));
            if (rs.getString("CANCEL_DATE") != null && !"".equals(rs.getString("CANCEL_DATE"))) {

                objDto.setCanceldate((Utility.showDate_Report((rs.getTimestamp("CANCEL_DATE")))).toUpperCase());

            }
            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            listSearchDetails.add(objDto);
        }
        pagingSorting.setRecordCount(recordCount);

    }

    catch (Exception ex) {
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna
        //ex.printStackTrace();
        throw new Exception("SQL Exception : " + ex.getMessage(), ex);
    } finally {
        try {
            DbConnection.freeConnection(conn);

        } catch (Exception e) {
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna
            //e.printStackTrace();
            throw new Exception("Exception : " + e.getMessage(), e);
        }
    }
    return listSearchDetails;

}

From source file:com.ibm.ioes.dao.ReportsDao_Usage.java

/**
 * method to fetch data for LEPM Owner report.
 * @param objDto/*from www .  j ava2 s  .  co m*/
 * @return
 */
public ArrayList<ReportsDto> viewLEPMOwnerReport(ReportsDto objDto) {
    //   Nagarjuna
    String methodName = "viewLEPMOwnerReport", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    Connection connection = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    int recordCount = 0;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    ArrayList<ReportsDto> listLEPMOwnerReport = new ArrayList<ReportsDto>();
    ReportsDto objReportsDto = null;
    try {
        connection = DbConnection.getReportsConnectionObject();
        proc = connection.prepareCall(sqlGetLEPMOwnerReport);

        if (objDto.getOrderNo() != null && !"".equals(objDto.getOrderNo())) {
            proc.setInt(1, new Integer(objDto.getOrderNo()));
        } else {
            proc.setNull(1, java.sql.Types.BIGINT);
        }
        if (objDto.getCopcApproveDate() != null && !"".equals(objDto.getCopcApproveDate())) {
            proc.setString(2, objDto.getCopcApproveDate());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }
        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

        proc.setString(3, pagingSorting.getSortByColumn());// columnName
        proc.setString(4, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(5, pagingSorting.getStartRecordId());// start index
        proc.setInt(6, pagingSorting.getEndRecordId());// end index
        proc.setInt(7, (pagingSorting.isPagingToBeDone() ? 1 : 0));
        // index
        rs = proc.executeQuery();
        while (rs.next()) {
            objReportsDto = new ReportsDto();
            objReportsDto.setPm_pro_date(rs.getString("ACTUAL_START_DATE"));
            if (!(rs.getString("ACTUAL_START_DATE") == null || rs.getString("ACTUAL_START_DATE") == "")) {

                objReportsDto.setPm_pro_date(
                        (utility.showDate_Report(new Date(rs.getTimestamp("ACTUAL_START_DATE").getTime())))
                                .toUpperCase());

            }
            objReportsDto.setPmapprovaldate(rs.getString("PLANNED_END_DATE"));
            if (!(rs.getString("PLANNED_END_DATE") == null || rs.getString("PLANNED_END_DATE") == "")) {

                objReportsDto.setPmapprovaldate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("PLANNED_END_DATE").getTime())))
                                .toUpperCase());

            }
            objReportsDto.setPmApproveDate(rs.getString("ACTUAL_END_DATE"));
            if (!(rs.getString("ACTUAL_END_DATE") == null || rs.getString("ACTUAL_END_DATE") == "")) {

                objReportsDto.setPmApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("ACTUAL_END_DATE").getTime())))
                                .toUpperCase());

            }
            objReportsDto.setCopcApproveDate(rs.getString("ORDER_APPROVAL_DATE"));
            if (!(rs.getString("ORDER_APPROVAL_DATE") == null || rs.getString("ORDER_APPROVAL_DATE") == "")) {

                objReportsDto.setCopcApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("ORDER_APPROVAL_DATE").getTime())))
                                .toUpperCase());
            }

            objReportsDto.setOrderNo(rs.getString("CRM_ORDER_ID"));
            objReportsDto.setPmName(rs.getString("OWNER_NAME"));
            objReportsDto.setContactCell(rs.getString("OWNER_PHONE"));
            objReportsDto.setEmailId(rs.getString("OWNER_EMAILID"));
            objReportsDto.setUserName(rs.getString("USER_NAME"));
            objReportsDto.setTaskNumber(rs.getInt("TASK_NUMBER"));
            objReportsDto.setTaskName(rs.getString("TASK_NAME"));

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            listLEPMOwnerReport.add(objReportsDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna
        //ex.printStackTrace();   
    } finally {
        try {
            DbConnection.freeConnection(connection);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            //e.printStackTrace();
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna
        }
    }
    return listLEPMOwnerReport;
}

From source file:com.ibm.ioes.dao.ReportsDao_Usage.java

public ArrayList<NetworkLocationDto> viewNetworkLocsList(NetworkLocationDto objDto) throws Exception {
    //Nagarjuna/*from ww w  .j a  v a 2 s.  c o m*/
    String methodName = "viewNetworkLocsList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    ArrayList<NetworkLocationDto> objUserList = new ArrayList<NetworkLocationDto>();
    Connection conn = null;
    ResultSet rs = null;
    CallableStatement getNetworkLocs = null;

    try {
        String userName = "";
        conn = DbConnection.getReportsConnectionObject();
        getNetworkLocs = conn.prepareCall(sqlGetNetworkLocReport);
        String networkLocationIdStr = objDto.getSearchNetworkLocationIdStr();
        String contactNameStr = objDto.getSearchContactNameStr();

        if (networkLocationIdStr == null || networkLocationIdStr.trim().equals("")) {
            getNetworkLocs.setNull(1, java.sql.Types.BIGINT);
        } else {
            getNetworkLocs.setLong(1, Long.parseLong(networkLocationIdStr));
        }

        if (contactNameStr == null || contactNameStr.trim().equals("")) {
            getNetworkLocs.setNull(2, java.sql.Types.VARCHAR);
        } else {
            getNetworkLocs.setString(2, contactNameStr);
        }

        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index

        getNetworkLocs.setString(3, pagingSorting.getSortByColumn());// columnName
        getNetworkLocs.setString(4, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        getNetworkLocs.setInt(5, pagingSorting.getStartRecordId());// start
        // index
        getNetworkLocs.setInt(6, pagingSorting.getEndRecordId());// end
        // index
        getNetworkLocs.setInt(7, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end
        // index

        rs = getNetworkLocs.executeQuery();

        int countFlag = 0;
        int recordCount = 0;
        while (rs.next() != false) {
            countFlag++;

            userName = (rs.getString("FNAME")) + " " + (rs.getString("LNAME"));
            objDto = new NetworkLocationDto();
            objDto.setCustomerName(userName);
            objDto.setNetworkLocationId("" + rs.getInt("LOCATION_CODE"));
            objDto.setFirstname(rs.getString("FNAME"));
            objDto.setLastName(rs.getString("LNAME"));
            objDto.setTelephonePhno(rs.getString("TELEPHONENO"));
            objDto.setEmail_Id(rs.getString("EMAIL_ID"));
            objDto.setAddress1(rs.getString("ADDRESS1"));
            objDto.setAddress2(rs.getString("ADDRESS2"));
            objDto.setAddress3(rs.getString("ADDRESS3"));
            objDto.setAddress4(rs.getString("ADDRESS4"));
            objDto.setFax(rs.getString("FAX"));
            // objDto.setPin(rs.getString("PIN"));
            objDto.setTitle(rs.getString("TITLE"));
            objDto.setPostalCode(rs.getString("POSTAL_CODE"));
            objDto.setCityName(rs.getString("CITY_NAME"));
            objDto.setStateName(rs.getString("STATE_NAME"));
            objDto.setCountryName(rs.getString("COUNTRY_NAME"));

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }

            objUserList.add(objDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);
        //ex.printStackTrace();
        throw new Exception("SQL Exception : " + ex.getMessage(), ex);
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(getNetworkLocs);
            DbConnection.freeConnection(conn);

        } catch (Exception e) {
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);
            //e.printStackTrace();
            throw new Exception("Exception : " + e.getMessage(), e);
        }
    }
    return objUserList;
}

From source file:com.ibm.ioes.dao.ReportsDao_Usage.java

public ArrayList<ReportsDto> getTelemediaOrderList(ReportsDto objDto) {
    //   Nagarjuna
    String methodName = "getTelemediaOrderList", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    //end Nagarjuna
    Connection connection = null;
    CallableStatement proc = null;
    ResultSet rs = null;/*w  w  w  .ja v  a 2  s .  c  o  m*/
    ArrayList<ReportsDto> listSearchDetails = new ArrayList<ReportsDto>();
    ReportsDto objReportsDto = null;
    int recordCount = 0;
    Utility utility = new Utility();
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    try {
        connection = DbConnection.getReportsConnectionObject();
        proc = connection.prepareCall(sqlGetTelemediaOrderReport);
        if (objDto.getFromCopcApprovedDate() != null && !"".equals(objDto.getFromCopcApprovedDate())) {
            proc.setString(1, objDto.getFromCopcApprovedDate().trim());
        } else {
            proc.setNull(1, java.sql.Types.VARCHAR);
        }
        if (objDto.getToCopcApprovedDate() != null && !"".equals(objDto.getToCopcApprovedDate())) {
            proc.setString(2, objDto.getToCopcApprovedDate().trim());
        } else {
            proc.setNull(2, java.sql.Types.VARCHAR);
        }
        PagingSorting pagingSorting = objDto.getPagingSorting();
        pagingSorting.sync();// To calculate start index and Enc Index
        proc.setString(3, pagingSorting.getSortByColumn());// columnName
        proc.setString(4, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order
        proc.setInt(5, pagingSorting.getStartRecordId());// start index
        proc.setInt(6, pagingSorting.getEndRecordId());// end index
        proc.setInt(7, (pagingSorting.isPagingToBeDone() ? 1 : 0));
        // index
        rs = proc.executeQuery();
        while (rs.next()) {
            objReportsDto = new ReportsDto();
            objReportsDto.setPartyName(rs.getString("PARTYNAME"));
            objReportsDto.setOrderNo(rs.getString("ORDERNO"));
            objReportsDto.setCrmAccountNoString(rs.getString("PARENT_ACCOUNT_NUMBER"));
            objReportsDto.setChild_act_no(rs.getString("CHILD_ACCOUNT_NUMBER"));
            objReportsDto.setCopcApproveDate(Utility.showDate_Report(rs.getString("COPC_APPROVED_DATE")));
            if (rs.getString("COPC_APPROVED_DATE") != null && !"".equals(rs.getString("COPC_APPROVED_DATE"))) {
                objReportsDto.setCopcApproveDate(
                        (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVED_DATE").getTime())))
                                .toUpperCase());
            }

            if (pagingSorting.isPagingToBeDone()) {
                recordCount = rs.getInt("FULL_REC_COUNT");
            }
            listSearchDetails.add(objReportsDto);
        }
        pagingSorting.setRecordCount(recordCount);
    } catch (Exception ex) {
        //ex.printStackTrace();   
        Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna
    } finally {
        try {
            DbConnection.closeResultset(rs);
            DbConnection.closeCallableStatement(proc);
            DbConnection.freeConnection(connection);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            //e.printStackTrace();
            Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna
        }
    }
    return listSearchDetails;
}