Example usage for java.sql Connection prepareCall

List of usage examples for java.sql Connection prepareCall

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. prepareCall

Introduction

In this page you can find the example usage for java.sql Connection prepareCall.

Prototype

CallableStatement prepareCall(String sql) throws SQLException;

Source Link

Document

Creates a CallableStatement object for calling database stored procedures.

Usage

From source file:net.sourceforge.msscodefactory.cfasterisk.v2_1.CFAstOracle.CFAstOracleMimeTypeTable.java

public CFAstMimeTypeBuff lockBuff(CFAstAuthorization Authorization, CFAstMimeTypePKey PKey) {
    final String S_ProcName = "lockBuff";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }/*from w w w  . j a v a  2  s  . c o  m*/
    ResultSet resultSet = null;
    Connection cnx = schema.getCnx();
    CallableStatement stmtLockBuffByPKey = null;
    try {
        int MimeTypeId = PKey.getRequiredMimeTypeId();

        stmtLockBuffByPKey = cnx.prepareCall("begin " + schema.getLowerDbSchemaName()
                + ".lck_mimetype( ?, ?, ?, ?, ?, ?" + ", " + "?" + " ); end;");
        int argIdx = 1;
        stmtLockBuffByPKey.registerOutParameter(argIdx++, OracleTypes.CURSOR);
        stmtLockBuffByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtLockBuffByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtLockBuffByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtLockBuffByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtLockBuffByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtLockBuffByPKey.setInt(argIdx++, MimeTypeId);
        stmtLockBuffByPKey.execute();
        resultSet = (ResultSet) stmtLockBuffByPKey.getObject(1);
        if (resultSet == null) {
            return (null);
        }
        try {
            if (resultSet.next()) {
                CFAstMimeTypeBuff buff = unpackMimeTypeResultSetToBuff(resultSet);
                if (resultSet.next()) {
                    resultSet.last();
                    throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                            "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
                }
                return (buff);
            } else {
                return (null);
            }
        } catch (SQLException e) {
            return (null);
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
        if (stmtLockBuffByPKey != null) {
            try {
                stmtLockBuffByPKey.close();
            } catch (SQLException e) {
            }
            stmtLockBuffByPKey = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfasterisk.v2_1.CFAstOracle.CFAstOracleServiceTypeTable.java

public CFAstServiceTypeBuff readBuff(CFAstAuthorization Authorization, CFAstServiceTypePKey PKey) {
    final String S_ProcName = "readBuff";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }//from  ww  w .  j a v a  2  s.  c  o m
    ResultSet resultSet = null;
    Connection cnx = schema.getCnx();
    CallableStatement stmtReadBuffByPKey = null;
    try {
        int ServiceTypeId = PKey.getRequiredServiceTypeId();

        stmtReadBuffByPKey = cnx.prepareCall("begin " + schema.getLowerDbSchemaName()
                + ".rd_svctype( ?, ?, ?, ?, ?, ?" + ", " + "?" + " ); end;");
        int argIdx = 1;
        stmtReadBuffByPKey.registerOutParameter(argIdx++, OracleTypes.CURSOR);
        stmtReadBuffByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadBuffByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtReadBuffByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtReadBuffByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadBuffByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtReadBuffByPKey.setInt(argIdx++, ServiceTypeId);
        stmtReadBuffByPKey.execute();
        resultSet = (ResultSet) stmtReadBuffByPKey.getObject(1);
        if (resultSet == null) {
            return (null);
        }
        try {
            if (resultSet.next()) {
                CFAstServiceTypeBuff buff = unpackServiceTypeResultSetToBuff(resultSet);
                if (resultSet.next()) {
                    resultSet.last();
                    throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                            "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
                }
                return (buff);
            } else {
                return (null);
            }
        } catch (SQLException e) {
            return (null);
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
        if (stmtReadBuffByPKey != null) {
            try {
                stmtReadBuffByPKey.close();
            } catch (SQLException e) {
            }
            stmtReadBuffByPKey = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfasterisk.v2_1.CFAstOracle.CFAstOracleServiceTypeTable.java

public CFAstServiceTypeBuff lockBuff(CFAstAuthorization Authorization, CFAstServiceTypePKey PKey) {
    final String S_ProcName = "lockBuff";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }/*from   w ww  . jav a 2  s. com*/
    ResultSet resultSet = null;
    Connection cnx = schema.getCnx();
    CallableStatement stmtLockBuffByPKey = null;
    try {
        int ServiceTypeId = PKey.getRequiredServiceTypeId();

        stmtLockBuffByPKey = cnx.prepareCall("begin " + schema.getLowerDbSchemaName()
                + ".lck_svctype( ?, ?, ?, ?, ?, ?" + ", " + "?" + " ); end;");
        int argIdx = 1;
        stmtLockBuffByPKey.registerOutParameter(argIdx++, OracleTypes.CURSOR);
        stmtLockBuffByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtLockBuffByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtLockBuffByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtLockBuffByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtLockBuffByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtLockBuffByPKey.setInt(argIdx++, ServiceTypeId);
        stmtLockBuffByPKey.execute();
        resultSet = (ResultSet) stmtLockBuffByPKey.getObject(1);
        if (resultSet == null) {
            return (null);
        }
        try {
            if (resultSet.next()) {
                CFAstServiceTypeBuff buff = unpackServiceTypeResultSetToBuff(resultSet);
                if (resultSet.next()) {
                    resultSet.last();
                    throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                            "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
                }
                return (buff);
            } else {
                return (null);
            }
        } catch (SQLException e) {
            return (null);
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
        if (stmtLockBuffByPKey != null) {
            try {
                stmtLockBuffByPKey.close();
            } catch (SQLException e) {
            }
            stmtLockBuffByPKey = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cffreeswitch.v2_1.CFFswOracle.CFFswOracleMimeTypeTable.java

public CFFswMimeTypeBuff readBuff(CFFswAuthorization Authorization, CFFswMimeTypePKey PKey) {
    final String S_ProcName = "readBuff";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }/*from w  w w. java  2 s. c  o  m*/
    ResultSet resultSet = null;
    Connection cnx = schema.getCnx();
    CallableStatement stmtReadBuffByPKey = null;
    try {
        int MimeTypeId = PKey.getRequiredMimeTypeId();

        stmtReadBuffByPKey = cnx.prepareCall("begin " + schema.getLowerDbSchemaName()
                + ".rd_mimetype( ?, ?, ?, ?, ?, ?" + ", " + "?" + " ); end;");
        int argIdx = 1;
        stmtReadBuffByPKey.registerOutParameter(argIdx++, OracleTypes.CURSOR);
        stmtReadBuffByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadBuffByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtReadBuffByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtReadBuffByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadBuffByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtReadBuffByPKey.setInt(argIdx++, MimeTypeId);
        stmtReadBuffByPKey.execute();
        resultSet = (ResultSet) stmtReadBuffByPKey.getObject(1);
        if (resultSet == null) {
            return (null);
        }
        try {
            if (resultSet.next()) {
                CFFswMimeTypeBuff buff = unpackMimeTypeResultSetToBuff(resultSet);
                if (resultSet.next()) {
                    resultSet.last();
                    throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                            "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
                }
                return (buff);
            } else {
                return (null);
            }
        } catch (SQLException e) {
            return (null);
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
        if (stmtReadBuffByPKey != null) {
            try {
                stmtReadBuffByPKey.close();
            } catch (SQLException e) {
            }
            stmtReadBuffByPKey = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cffreeswitch.v2_1.CFFswOracle.CFFswOracleMimeTypeTable.java

public CFFswMimeTypeBuff lockBuff(CFFswAuthorization Authorization, CFFswMimeTypePKey PKey) {
    final String S_ProcName = "lockBuff";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }//from   w  w  w .  j  a  v  a  2 s . c  o  m
    ResultSet resultSet = null;
    Connection cnx = schema.getCnx();
    CallableStatement stmtLockBuffByPKey = null;
    try {
        int MimeTypeId = PKey.getRequiredMimeTypeId();

        stmtLockBuffByPKey = cnx.prepareCall("begin " + schema.getLowerDbSchemaName()
                + ".lck_mimetype( ?, ?, ?, ?, ?, ?" + ", " + "?" + " ); end;");
        int argIdx = 1;
        stmtLockBuffByPKey.registerOutParameter(argIdx++, OracleTypes.CURSOR);
        stmtLockBuffByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtLockBuffByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtLockBuffByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtLockBuffByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtLockBuffByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtLockBuffByPKey.setInt(argIdx++, MimeTypeId);
        stmtLockBuffByPKey.execute();
        resultSet = (ResultSet) stmtLockBuffByPKey.getObject(1);
        if (resultSet == null) {
            return (null);
        }
        try {
            if (resultSet.next()) {
                CFFswMimeTypeBuff buff = unpackMimeTypeResultSetToBuff(resultSet);
                if (resultSet.next()) {
                    resultSet.last();
                    throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                            "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
                }
                return (buff);
            } else {
                return (null);
            }
        } catch (SQLException e) {
            return (null);
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
        if (stmtLockBuffByPKey != null) {
            try {
                stmtLockBuffByPKey.close();
            } catch (SQLException e) {
            }
            stmtLockBuffByPKey = null;
        }
    }
}

From source file:com.toxind.benchmark.thrid.ibatis.sqlmap.engine.execution.SqlExecutor.java

private CallableStatement prepareCall(SessionScope sessionScope, Connection conn, String sql)
        throws SQLException {
    SqlMapExecutorDelegate delegate = ((SqlMapClientImpl) sessionScope.getSqlMapExecutor()).getDelegate();
    if (sessionScope.hasPreparedStatementFor(sql)) {
        return (CallableStatement) sessionScope.getPreparedStatement((sql));
    } else {// w  ww.j ava  2  s .  c  o m
        CallableStatement cs = conn.prepareCall(sql);
        sessionScope.putPreparedStatement(delegate, sql, cs);
        return cs;
    }
}

From source file:dbProcs.Getter.java

/**
 * This method prepares the Tournament module menu. This is when Security Shepherd is in "Tournament Mode".
 * Users are presented with a list of that are specified as open. 
 * @param ApplicationRoot The running context of the application.
 * @param userId The user identifier of the user.
 * @param csrfToken The cross site request forgery token
 * @return A HTML menu of a users current module progress and a script for interaction with this menu
 *//*from   www . j av a  2  s  .  c o m*/
public static String getTournamentModules(String ApplicationRoot, String userId, Locale lang) {
    log.debug("*** Getter.getTournamentModules ***");
    String levelMasterList = new String();
    Encoder encoder = ESAPI.encoder();
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    //Getting Translations
    ResourceBundle bundle = ResourceBundle.getBundle("i18n.text", lang);
    ResourceBundle levelNames = ResourceBundle.getBundle("i18n.moduleGenerics.moduleNames", lang);
    try {

        String listEntry = new String();
        //Get the modules
        CallableStatement callstmt = conn.prepareCall("call moduleTournamentOpenInfo(?)");
        callstmt.setString(1, userId);
        log.debug("Gathering moduleTournamentOpenInfo ResultSet for user " + userId);
        ResultSet levels = callstmt.executeQuery();
        log.debug("Opening Result Set from moduleTournamentOpenInfo");
        int currentSection = 0; // Used to identify the first row, as it is slightly different to all other rows for output
        while (levels.next()) {
            //Create Row Entry First
            //log.debug("Adding " + lessons.getString(1));
            listEntry = "<li>";
            //Markers for completion
            if (levels.getString(4) != null) {
                listEntry += "<img src='css/images/completed.png'/>";
            } else {
                listEntry += "<img src='css/images/uncompleted.png'/>";
            }
            //Prepare entry output
            listEntry += "<a class='lesson' id='" + encoder.encodeForHTMLAttribute(levels.getString(3))
                    + "' href='javascript:;'>"
                    + encoder.encodeForHTML(levelNames.getString(levels.getString(1))) + "</a>\n";
            listEntry += "</li>";
            //What section does this belong in? Current or Next?
            if (getTounnamentSectionFromRankNumber(levels.getInt(5)) > currentSection) {
                //This level is not in the same level band as the previous level. So a new Level Band Header is required on the master list before we add the entry.
                //Do we need to close a previous list?
                if (currentSection != 0) //If a Section Select hasn't been made before, we don't need to close any previous sections
                {
                    //We've had a section before, so need to close the previous one before we make this new one
                    levelMasterList += "</ul>\n";
                }
                //Update the current section to the one we have just added to the list
                currentSection = getTounnamentSectionFromRankNumber(levels.getInt(5));
                //Which to Add?
                switch (currentSection) {
                case 1: //fieldTraining
                    //log.debug("Starting Field Training List");
                    levelMasterList += "<a id=\"fieldTrainingList\" href=\"javascript:;\"><div class=\"menuButton\">"
                            + bundle.getString("getter.tournamentRank.1") + "</div></a>"
                            + "<ul id=\"theFieldTrainingList\" style=\"display: none;\" class='levelList'>\n";
                    break;
                case 2: //private
                    //log.debug("Starting Private List");
                    levelMasterList += "<a id=\"privateList\" href=\"javascript:;\"><div class=\"menuButton\">"
                            + bundle.getString("getter.tournamentRank.2") + "</div></a>"
                            + "<ul id=\"thePrivateList\" style=\"display: none;\" class='levelList'>\n";
                    break;
                case 3: //corporal
                    //log.debug("Starting Corporal List");
                    levelMasterList += "<a id=\"corporalList\" href=\"javascript:;\"><div class=\"menuButton\">"
                            + bundle.getString("getter.tournamentRank.3") + "</div></a>"
                            + "<ul id=\"theCorporalList\" style=\"display: none;\" class='levelList'>\n";
                    break;
                case 4: //sergeant
                    //log.debug("Starting Sergeant List");
                    levelMasterList += "<a id=\"sergeantList\" href=\"javascript:;\"><div class=\"menuButton\">"
                            + bundle.getString("getter.tournamentRank.4") + "</div></a>"
                            + "<ul id=\"theSergeantList\" style=\"display: none;\" class='levelList'>\n";
                    break;
                case 5: //Lieutenant
                    //log.debug("Starting Lieutenant List");
                    levelMasterList += "<a id=\"lieutenantList\" href=\"javascript:;\"><div class=\"menuButton\">"
                            + bundle.getString("getter.tournamentRank.5") + "</div></a>"
                            + "<ul id=\"theLieutenantList\" style=\"display: none;\" class='levelList'>\n";
                    break;
                case 6: //major
                    //log.debug("Starting Major List");
                    levelMasterList += "<a id=\"majorList\" href=\"javascript:;\"><div class=\"menuButton\">"
                            + bundle.getString("getter.tournamentRank.6") + "</div></a>"
                            + "<ul id=\"theMajorList\" style=\"display: none;\" class='levelList'>\n";
                    break;
                case 7: //admiral
                    //log.debug("Starting Admiral List");
                    levelMasterList += "<a id=\"admiralList\" href=\"javascript:;\"><div class=\"menuButton\">"
                            + bundle.getString("getter.tournamentRank.7") + "</div></a>"
                            + "<ul id=\"theAdmiralList\" style=\"display: none;\" class='levelList'>\n";
                    break;
                }
            }
            //Now we can add the entry to the level master List and start again
            levelMasterList += listEntry;
            //log.debug("Put level in category: " + currentSection);
        }
        //If no output has been found, return an error message
        if (levelMasterList.isEmpty()) {
            levelMasterList = "<ul><li><a href='javascript:;'>"
                    + bundle.getString("getter.button.noModulesFound") + "</a></li></ul>";
        } else {
            //List is complete, but we need to close the last list we made, which deinfetly exists as the levelmasterList is not empty
            levelMasterList += "</ul>";
            log.debug("Tournament List returned");
        }
    } catch (Exception e) {
        log.error("Tournament List Retrieval: " + e.toString());
    }
    Database.closeConnection(conn);
    return levelMasterList;
}

From source file:net.sourceforge.msscodefactory.cfasterisk.v2_4.CFAsteriskOracle.CFAsteriskOracleISOCountryTable.java

public void createISOCountry(CFSecurityAuthorization Authorization, CFSecurityISOCountryBuff Buff) {
    final String S_ProcName = "createISOCountry";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }/* w w  w.java 2 s. co m*/
    ResultSet resultSet = null;
    CallableStatement stmtCreateByPKey = null;
    try {
        short Id = Buff.getRequiredId();
        String ISOCode = Buff.getRequiredISOCode();
        String Name = Buff.getRequiredName();
        Connection cnx = schema.getCnx();
        stmtCreateByPKey = cnx.prepareCall("begin " + schema.getLowerDbSchemaName()
                + ".crt_iso_cntry( ?, ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?" + " ); end;");
        int argIdx = 1;
        stmtCreateByPKey.registerOutParameter(argIdx++, OracleTypes.CURSOR);
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtCreateByPKey.setString(argIdx++, "ISOC");
        stmtCreateByPKey.setShort(argIdx++, Id);
        stmtCreateByPKey.setString(argIdx++, ISOCode);
        stmtCreateByPKey.setString(argIdx++, Name);
        stmtCreateByPKey.execute();
        resultSet = (ResultSet) stmtCreateByPKey.getObject(1);
        if (resultSet == null) {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "crt_iso_cntry() did not return a result set");
        }
        try {
            if (resultSet.next()) {
                CFSecurityISOCountryBuff createdBuff = unpackISOCountryResultSetToBuff(resultSet);
                if (resultSet.next()) {
                    resultSet.last();
                    throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                            "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
                }
                Buff.setRequiredId(createdBuff.getRequiredId());
                Buff.setRequiredISOCode(createdBuff.getRequiredISOCode());
                Buff.setRequiredName(createdBuff.getRequiredName());
                Buff.setRequiredRevision(createdBuff.getRequiredRevision());
                Buff.setCreatedByUserId(createdBuff.getCreatedByUserId());
                Buff.setCreatedAt(createdBuff.getCreatedAt());
                Buff.setUpdatedByUserId(createdBuff.getUpdatedByUserId());
                Buff.setUpdatedAt(createdBuff.getUpdatedAt());
            } else {
                throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                        "Expected a single-record response, " + resultSet.getRow() + " rows selected");
            }
        } catch (SQLException e) {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "crt_iso_cntry() did not return a valid result set");
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
        if (stmtCreateByPKey != null) {
            try {
                stmtCreateByPKey.close();
            } catch (SQLException e) {
            }
            stmtCreateByPKey = null;
        }
    }
}

From source file:com.intuit.it.billing.data.BillingDAOImpl.java

/**
  * @section dao_section class BillingDAOImpl
 * getAllocation//  ww  w.  j  a  v  a 2s. co m
 * 
 * To be used in a caching method where we are pulling all of the allocations at once.  The way we can do this
 * is to merge a date range based set of billing history records with a date range set of allocations.
 * <p/>
 * <p/>
 * <b>DATABASE PROCEDURE:</b>
 *  
 * @code
 *     FUNCTION fn_get_item_allocations(
 *           item_no IN VARCHAR2 )
 *        RETURN ref_cursor;
 * @endcode
 * <p/>
 * <b>DATABASE RESULT SET:</b>
 * <ul>
 *    <li>ALLOCATION_DATE,</li>
 *    <li>ALLOCATION_T, </li>
 *    <li>ALLOCATION_AMT,</li>
 *    <li>AR_ITEM_NO, </li>
 *    <li>BILL_ITEM_NO, </li>
 *    <li>ITEM_DESCRIPTION, </li>
 *    <li>ITEM_CODE,</li> 
 *    <li>AR_ITEM_DATE, </li>
 *    <li>BILL_ITEM_DATE </li>
 *    <li>LICENSE</li>
 *  </ul>
 *  
 * @param customer  :  The Customer.accountNo account number of the customer who's allocations we need
 * @param startDate : The starting date of the allocation - to be merged with a billing history record set
 * @param endDate  :  The ending date of the allocation - to be merged with a billing history record set
 * 
 * @return A list of Allocation objects. 
 * @throws JSONException 
 * 
 * 
 * 
 */
@Override
public List<Allocation> getAllocation(String itemNo) throws JSONException {

    List<Allocation> allocs = new ArrayList<Allocation>();

    String query = "begin ? := billing_inquiry.fn_get_item_allocations( ? ); end;"; //TODO: configure

    ResultSet rs = null;
    Connection conn = null;

    // DB Connection
    try {
        conn = this.getConnection();
    } catch (SQLException e) {
        throw JSONException.sqlError(e);
    } catch (NamingException e) {
        throw JSONException.namingError(e.toString());
    }

    try {
        CallableStatement stmt = conn.prepareCall(query);
        stmt.registerOutParameter(1, OracleTypes.CURSOR);
        stmt.setString(2, itemNo);

        stmt.execute();
        rs = (ResultSet) stmt.getObject(1);

        while (rs.next()) {

            Allocation a = new Allocation();
            a.setAllocatedFromItem(rs.getString("AR_ITEM_NO"));
            a.setAllocatedToItem(rs.getString("BILL_ITEM_NO"));
            a.setAllocationAmount(rs.getBigDecimal("ALLOCATION_AMT"));
            a.setAllocationDate(rs.getTimestamp("ALLOCATION_DATE"));
            a.setItemCode(rs.getString("ITEM_CODE"));
            a.setItemDescription(rs.getString("ITEM_DESCRIPTION"));
            a.setLicense(rs.getString("LICENSE"));
            allocs.add(a);
        }
        conn.close();
        rs.close();

    } catch (SQLException e) {
        throw JSONException.sqlError(e);
    }

    if (allocs == null || allocs.isEmpty()) {
        throw JSONException.noDataFound("Null set returned - no data found");
    }

    return allocs;
}

From source file:dbProcs.Getter.java

/**
 * Use to return the current progress of a class in JSON format with information like userid, user name and score
 * @param applicationRoot The current running context of the application
 * @param classId The identifier of the class to use in lookup
 * @return A JSON representation of a class's score in the order {id, username, userTitle, score, scale, place, order, 
 * goldmedalcount, goldDisplay, silverMedalCount, silverDisplay, bronzeDisplay, bronzeMedalCount}
 *//*from  www .  j  a v a  2s  .  c o  m*/
@SuppressWarnings("unchecked")
public static String getJsonScore(String applicationRoot, String classId) {
    log.debug("classId: " + classId);
    String result = new String();
    Connection conn = Database.getCoreConnection(applicationRoot);
    try {
        Encoder encoder = ESAPI.encoder();
        //Returns User's: Name, # of Completed modules and Score
        CallableStatement callstmnt = null;
        if (ScoreboardStatus.getScoreboardClass().isEmpty() && !ScoreboardStatus.isClassSpecificScoreboard())
            callstmnt = conn.prepareCall("call totalScoreboard()"); //Open Scoreboard not based on a class
        else {
            callstmnt = conn.prepareCall("call classScoreboard(?)"); //Class Scoreboard based on classId
            callstmnt.setString(1, classId);
        }
        //log.debug("Executing classScoreboard");
        ResultSet resultSet = callstmnt.executeQuery();
        JSONArray json = new JSONArray();
        JSONObject jsonInner = new JSONObject();
        int resultAmount = 0;
        int prevPlace = 0;
        int prevScore = 0;
        int prevGold = 0;
        int prevSilver = 0;
        int prevBronze = 0;
        float baseBarScale = 0; //
        float tieBreaker = 0;
        while (resultSet.next()) //For each user in a class
        {
            resultAmount++;
            jsonInner = new JSONObject();
            if (resultSet.getString(1) != null) {
                int place = resultAmount;
                int score = resultSet.getInt(3);
                int goldMedals = resultSet.getInt(4);
                int silverMedals = resultSet.getInt(5);
                int bronzeMedals = resultSet.getInt(6);
                if (resultAmount == 1) //First Place is Returned First, so this will be the biggest bar on the scoreboard
                {
                    int highscore = score;
                    //log.debug("Current Highscore Listing is " + highscore);
                    //Use the high score to scale the width of the bars for the whole scoreboard
                    float maxBarScale = 1.02f; //High Score bar will have a scale of 1 //This will get used when a scale is added to the scoreboard
                    baseBarScale = highscore * maxBarScale;
                    //setting up variables for Tie Scenario Placings
                    prevPlace = 1;
                    prevScore = score;
                } else {
                    //Does this score line match the one before (Score and Medals)? if so the place shouldnt change
                    if (score == prevScore && goldMedals == prevGold && silverMedals == prevSilver
                            && bronzeMedals == prevBronze) {
                        place = prevPlace;
                        tieBreaker = tieBreaker + 0.01f;
                    } else {
                        prevScore = score;
                        prevPlace = place;
                        prevGold = goldMedals;
                        prevSilver = silverMedals;
                        prevBronze = bronzeMedals;
                        tieBreaker = 0;
                    }
                }
                String displayMedal = new String("display: inline;");
                String goldDisplayStyle = new String("display: none;");
                String silverDisplayStyle = new String("display: none;");
                String bronzeDisplayStyle = new String("display: none;");
                if (goldMedals > 0)
                    goldDisplayStyle = displayMedal;
                if (silverMedals > 0)
                    silverDisplayStyle = displayMedal;
                if (bronzeMedals > 0)
                    bronzeDisplayStyle = displayMedal;

                int barScale = (int) ((score * 100) / baseBarScale); //bar scale is the percentage the bar should be of the row's context (Highest Possible is depends on scale set in maxBarScale. eg: maxBarScale = 1.1 would mean the max scale would be 91% for a single row)

                String userMedalString = new String();
                if (goldMedals > 0 || silverMedals > 0 || bronzeMedals > 0) {
                    userMedalString += " holding ";
                    if (goldMedals > 0)
                        userMedalString += goldMedals + " gold";
                    if (silverMedals > 0) {
                        if (goldMedals > 0) //Medals Before, puncuate
                        {
                            if (bronzeMedals > 0) //more medals after silver? Comma
                            {
                                userMedalString += ", ";
                            } else //Say And
                            {
                                userMedalString += " and ";
                            }
                        }
                        userMedalString += silverMedals + " silver";
                    }
                    if (bronzeMedals > 0) {
                        if (goldMedals > 0 || silverMedals > 0) //Medals Before?
                        {
                            userMedalString += " and ";
                        }
                        userMedalString += bronzeMedals + " bronze";
                    }
                    //Say Medal(s) at the end of the string
                    userMedalString += " medal";
                    if (goldMedals + silverMedals + bronzeMedals > 1)
                        userMedalString += "s";
                }

                jsonInner.put("id", new String(encoder.encodeForHTML(resultSet.getString(1)))); //User Id
                jsonInner.put("username", new String(encoder.encodeForHTML(resultSet.getString(2)))); //User Name
                jsonInner.put("userTitle", new String(encoder.encodeForHTML(resultSet.getString(2)) + " with "
                        + score + " points" + userMedalString)); //User name encoded for title attribute
                jsonInner.put("score", new Integer(score)); //Score
                jsonInner.put("scale", barScale); //Scale of score bar
                jsonInner.put("place", place); //Place on board
                jsonInner.put("order", (place + tieBreaker)); //Order on board
                jsonInner.put("goldMedalCount", new Integer(goldMedals));
                jsonInner.put("goldDisplay", goldDisplayStyle);
                jsonInner.put("silverMedalCount", new Integer(silverMedals));
                jsonInner.put("silverDisplay", silverDisplayStyle);
                jsonInner.put("bronzeMedalCount", new Integer(bronzeMedals));
                jsonInner.put("bronzeDisplay", bronzeDisplayStyle);
                //log.debug("Adding: " + jsonInner.toString());
                json.add(jsonInner);
            }
        }
        if (resultAmount > 0)
            result = json.toString();
        else
            result = new String();
    } catch (SQLException e) {
        log.error("getJsonScore Failure: " + e.toString());
        result = null;
    } catch (Exception e) {
        log.error("getJsonScore Unexpected Failure: " + e.toString());
        result = null;
    }
    Database.closeConnection(conn);
    //log.debug("*** END getJsonScore ***");
    return result;
}