Example usage for java.sql CallableStatement close

List of usage examples for java.sql CallableStatement close

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

Introduction

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

Prototype

void close() throws SQLException;

Source Link

Document

Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed.

Usage

From source file:com.cws.esolutions.security.dao.reference.impl.SecurityReferenceDAOImpl.java

/**
 * @see com.cws.esolutions.security.dao.reference.interfaces.ISecurityReferenceDAO#obtainSecurityQuestionList()
 *///from   w  w w.java2  s .  co m
public synchronized List<String> obtainSecurityQuestionList() throws SQLException {
    final String methodName = ISecurityReferenceDAO.CNAME + "#obtainSecurityQuestionList() throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String> questionList = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);
        stmt = sqlConn.prepareCall("{CALL retrieve_user_questions()}");

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();
            resultSet.last();
            int iRowCount = resultSet.getRow();

            if (iRowCount == 0) {
                throw new SQLException("No security questions are currently configured.");
            }

            resultSet.first();
            ResultSetMetaData resultData = resultSet.getMetaData();

            int iColumns = resultData.getColumnCount();

            questionList = new ArrayList<String>();

            for (int x = 1; x < iColumns + 1; x++) {
                if (DEBUG) {
                    DEBUGGER.debug("resultSet.getObject: {}", resultSet.getObject(resultData.getColumnName(x)));
                }

                // check if column is null
                resultSet.getObject(resultData.getColumnName(x));

                // if the column was null, insert n/a, otherwise, insert the column's contents
                questionList.add((String) (resultSet.wasNull() ? "N/A"
                        : resultSet.getObject(resultData.getColumnName(x))));
            }
        }
    } catch (SQLException sqx) {
        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return questionList;
}

From source file:net.sourceforge.msscodefactory.cfacc.v2_0.CFAccOracle.CFAccOracleSecUserTable.java

public void deleteSecUser(CFAccAuthorization Authorization, CFAccSecUserBuff Buff) {
    final String S_ProcName = "deleteSecUser";
    Connection cnx = schema.getCnx();
    CallableStatement stmtDeleteByPKey = null;
    try {//from ww  w . j av a  2 s.c  o  m
        UUID SecUserId = Buff.getRequiredSecUserId();
        stmtDeleteByPKey = cnx.prepareCall("begin " + schema.getLowerSchemaDbName()
                + ".dl_secuser( ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + " ); end;");
        int argIdx = 1;
        stmtDeleteByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtDeleteByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtDeleteByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtDeleteByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtDeleteByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtDeleteByPKey.setString(argIdx++, SecUserId.toString());
        stmtDeleteByPKey.setInt(argIdx++, Buff.getRequiredRevision());
        ;
        stmtDeleteByPKey.execute();
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (stmtDeleteByPKey != null) {
            try {
                stmtDeleteByPKey.close();
            } catch (SQLException e) {
            }
            stmtDeleteByPKey = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfasterisk.v2_0.CFAstOracle.CFAstOracleSecUserTable.java

public void deleteSecUser(CFAstAuthorization Authorization, CFAstSecUserBuff Buff) {
    final String S_ProcName = "deleteSecUser";
    Connection cnx = schema.getCnx();
    CallableStatement stmtDeleteByPKey = null;
    try {//from   w  w  w.j  a v  a  2  s.c om
        UUID SecUserId = Buff.getRequiredSecUserId();
        stmtDeleteByPKey = cnx.prepareCall("begin " + schema.getLowerSchemaDbName()
                + ".dl_secuser( ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + " ); end;");
        int argIdx = 1;
        stmtDeleteByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtDeleteByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtDeleteByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtDeleteByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtDeleteByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtDeleteByPKey.setString(argIdx++, SecUserId.toString());
        stmtDeleteByPKey.setInt(argIdx++, Buff.getRequiredRevision());
        ;
        stmtDeleteByPKey.execute();
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (stmtDeleteByPKey != null) {
            try {
                stmtDeleteByPKey.close();
            } catch (SQLException e) {
            }
            stmtDeleteByPKey = null;
        }
    }
}

From source file:com.cws.us.pws.dao.impl.ProductReferenceDAOImpl.java

/**
 * @see com.cws.us.pws.dao.interfaces.IProductReferenceDAO#getProductData(String, String) throws SQLException
 */// www .  j  a v a  2s  .com
@Override
public List<Object> getProductData(final String productId, final String lang) throws SQLException {
    final String methodName = IProductReferenceDAO.CNAME
            + "#getProductData(final int productId, final String lang) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", productId);
        DEBUGGER.debug("Value: {}", lang);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    List<Object> results = null;
    CallableStatement stmt = null;

    try {
        sqlConn = this.dataSource.getConnection();

        if (DEBUG) {
            DEBUGGER.debug("Connection: {}", sqlConn);
        }

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain connection to application datasource");
        }

        stmt = sqlConn.prepareCall("{ CALL getProductData(?, ?) }");
        stmt.setString(1, productId);
        stmt.setString(2, lang);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (!(stmt.execute())) {
            throw new SQLException("PreparedStatement is null. Cannot execute.");
        }

        resultSet = stmt.getResultSet();

        if (DEBUG) {
            DEBUGGER.debug("ResultSet: {}", resultSet);
        }

        if (resultSet.next()) {
            resultSet.beforeFirst();
            results = new ArrayList<Object>();

            while (resultSet.next()) {
                results.add(resultSet.getString(1)); // PRODUCT_ID
                results.add(resultSet.getString(2)); // PRODUCT_NAME
                results.add(resultSet.getString(3)); // PRODUCT_SHORT_DESC
                results.add(resultSet.getString(4)); // PRODUCT_DESC
                results.add(resultSet.getBigDecimal(5)); // PRODUCT_PRICE
                results.add(resultSet.getBoolean(6)); // IS_FEATURED
            }

            if (DEBUG) {
                DEBUGGER.debug("results: {}", results);
            }
        }
    } catch (SQLException sqx) {
        ERROR_RECORDER.error(sqx.getMessage(), sqx);

        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    if (DEBUG) {
        DEBUGGER.debug("results: {}", results);
    }

    return results;
}

From source file:net.sourceforge.msscodefactory.cfasterisk.v2_1.CFAstSybase.CFAstSybaseSchema.java

public long nextTenantIdGen() {
    if (!inTransaction) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), "nextTenantIdGen",
                "Not in a transaction");
    }//from www.jav a 2s  . co  m
    CallableStatement stmtNext = null;
    try {
        final String sql = "{ call sp_next_tenantidgen( ? ) }";
        stmtNext = cnx.prepareCall(sql);
        stmtNext.registerOutParameter(1, java.sql.Types.BIGINT);
        stmtNext.execute();
        long nextId = stmtNext.getLong(1);
        return (nextId);
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), "nextTenantIdGen", e);
    } finally {
        if (stmtNext != null) {
            try {
                stmtNext.close();
            } catch (SQLException e) {
            }
            stmtNext = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfasterisk.v2_1.CFAstSybase.CFAstSybaseSchema.java

public long nextClusterIdGen() {
    if (!inTransaction) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), "nextClusterIdGen",
                "Not in a transaction");
    }/*from  w  w w .j  a v a 2 s . c o m*/
    CallableStatement stmtNext = null;
    try {
        final String sql = "{ call sp_next_clusteridgen( ? ) }";
        stmtNext = cnx.prepareCall(sql);
        stmtNext.registerOutParameter(1, java.sql.Types.BIGINT);
        stmtNext.execute();
        long nextId = stmtNext.getLong(1);
        return (nextId);
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), "nextClusterIdGen", e);
    } finally {
        if (stmtNext != null) {
            try {
                stmtNext.close();
            } catch (SQLException e) {
            }
            stmtNext = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfasterisk.v2_1.CFAstSybase.CFAstSybaseSchema.java

public int nextMimeTypeIdGen() {
    if (!inTransaction) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), "nextMimeTypeIdGen",
                "Not in a transaction");
    }// w ww . jav a2  s  . c  o  m
    CallableStatement stmtNext = null;
    try {
        final String sql = "{ call sp_next_mimetypeidgen( ? ) }";
        stmtNext = cnx.prepareCall(sql);
        stmtNext.registerOutParameter(1, java.sql.Types.INTEGER);
        stmtNext.execute();
        int nextId = stmtNext.getInt(1);
        return (nextId);
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), "nextMimeTypeIdGen", e);
    } finally {
        if (stmtNext != null) {
            try {
                stmtNext.close();
            } catch (SQLException e) {
            }
            stmtNext = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfasterisk.v2_1.CFAstSybase.CFAstSybaseSchema.java

public int nextServiceTypeIdGen() {
    if (!inTransaction) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), "nextServiceTypeIdGen",
                "Not in a transaction");
    }//  w  ww.j av  a2  s  .co  m
    CallableStatement stmtNext = null;
    try {
        final String sql = "{ call sp_next_servicetypeidgen( ? ) }";
        stmtNext = cnx.prepareCall(sql);
        stmtNext.registerOutParameter(1, java.sql.Types.INTEGER);
        stmtNext.execute();
        int nextId = stmtNext.getInt(1);
        return (nextId);
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), "nextServiceTypeIdGen", e);
    } finally {
        if (stmtNext != null) {
            try {
                stmtNext.close();
            } catch (SQLException e) {
            }
            stmtNext = null;
        }
    }
}

From source file:com.cws.esolutions.core.dao.impl.ApplicationDataDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IApplicationDataDAO#getApplication(java.lang.String)
 *//*from   w w  w .  ja v  a 2 s.  c  o m*/
public synchronized List<Object> getApplication(final String value) throws SQLException {
    final String methodName = IApplicationDataDAO.CNAME
            + "#getApplication(final String value) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("value: {}", value);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<Object> responseData = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);
        stmt = sqlConn.prepareCall("{CALL getApplicationData(?)}");
        stmt.setString(1, value);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();

            if (DEBUG) {
                DEBUGGER.debug("resultSet: {}", resultSet);
            }

            if (resultSet.next()) {
                resultSet.first();

                responseData = new ArrayList<Object>(Arrays.asList(resultSet.getString(1), // APPLICATION_GUID
                        resultSet.getString(2), // APPLICATION_NAME
                        resultSet.getDouble(3), // APPLICATION_VERSION
                        resultSet.getString(4), // INSTALLATION_PATH
                        resultSet.getString(5), // PACKAGE_LOCATION
                        resultSet.getString(6), // PACKAGE_INSTALLER
                        resultSet.getString(7), // INSTALLER_OPTIONS
                        resultSet.getString(8), // LOGS_DIRECTORY
                        resultSet.getString(9) // PLATFORM_GUID
                ));

                if (DEBUG) {
                    DEBUGGER.debug("data: {}", responseData);
                }
            }
        }
    } catch (SQLException sqx) {
        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return responseData;
}

From source file:com.cws.esolutions.security.dao.usermgmt.impl.SQLUserManager.java

/**
 * @see com.cws.esolutions.security.dao.usermgmt.interfaces.UserManager#addUserAccount(java.util.List, java.util.List)
 *//*from   w w  w  . j av a 2 s.c o  m*/
public synchronized boolean addUserAccount(final List<String> userAccount, final List<String> roles)
        throws UserManagementException {
    final String methodName = SQLUserManager.CNAME
            + "#addUserAccount(final List<String> userAccount, final List<String> roles) throws UserManagementException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", userAccount);
        DEBUGGER.debug("Value: {}", roles);
    }

    Connection sqlConn = null;
    boolean isComplete = false;
    CallableStatement stmt = null;

    try {
        sqlConn = SQLUserManager.dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{ CALL addUserAccount(?, ?, ?, ?, ?, ?, ?, ?) }");
        stmt.setString(1, userAccount.get(0)); // guid
        stmt.setString(2, userAccount.get(1)); // username
        stmt.setString(3, userAccount.get(2)); // password
        stmt.setBoolean(4, Boolean.valueOf(userAccount.get(3))); // suspended
        stmt.setString(5, userAccount.get(4)); // surname
        stmt.setString(6, userAccount.get(5)); // givenname
        stmt.setString(7, userAccount.get(6)); // displayname
        stmt.setString(8, userAccount.get(7)); // email

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (!(stmt.execute())) {
            isComplete = true;
        }
    } catch (SQLException sqx) {
        throw new UserManagementException(sqx.getMessage(), sqx);
    } finally {
        try {
            if (stmt != null) {
                stmt.close();
            }

            if (!(sqlConn == null) && (!(sqlConn.isClosed()))) {
                sqlConn.close();
            }
        } catch (SQLException sqx) {
            throw new UserManagementException(sqx.getMessage(), sqx);
        }
    }

    return isComplete;
}