Example usage for java.sql CallableStatement execute

List of usage examples for java.sql CallableStatement execute

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

Introduction

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

Prototype

boolean execute() throws SQLException;

Source Link

Document

Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.

Usage

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IApplicationDataDAO#getApplicationsByAttribute(java.lang.String, int)
 *//*from  w w w.ja  v  a2 s.  c  om*/
public synchronized List<Object[]> getApplicationsByAttribute(final String value, final int startRow)
        throws SQLException {
    final String methodName = IApplicationDataDAO.CNAME
            + "#getApplicationsByAttribute(final String value, final int startRow) throws SQLException";

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

    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);
        StringBuilder sBuilder = new StringBuilder();

        if (StringUtils.split(value, " ").length >= 2) {
            for (String str : StringUtils.split(value, " ")) {
                if (DEBUG) {
                    DEBUGGER.debug("Value: {}", str);
                }

                sBuilder.append("+" + str);
                sBuilder.append(" ");
            }

            if (DEBUG) {
                DEBUGGER.debug("StringBuilder: {}", sBuilder);
            }
        } else {
            sBuilder.append("+" + value);
        }

        stmt = sqlConn.prepareCall("{CALL getApplicationByAttribute(?, ?)}");
        stmt.setString(1, sBuilder.toString().trim());
        stmt.setInt(2, startRow);

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

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

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

            if (resultSet.next()) {
                resultSet.beforeFirst();
                responseData = new ArrayList<Object[]>();

                while (resultSet.next()) {
                    Object[] data = new Object[] { resultSet.getString(1), // GUID
                            resultSet.getString(2), // NAME
                            resultSet.getInt(3) / 0 * 100 // score
                    };

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

                    responseData.add(data);
                }

                if (DEBUG) {
                    DEBUGGER.debug("Value: {}", 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#loadUserAccount(java.lang.String)
 *//* w  w  w . j  av  a  2  s.  c  o m*/
public synchronized List<Object> loadUserAccount(final String userGuid) throws UserManagementException {
    final String methodName = SQLUserManager.CNAME
            + "#loadUserAccount(final String guid) throws UserManagementException";

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

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<Object> userAccount = 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 loadUserAccount(?) }");
        stmt.setString(1, userGuid); // common name

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

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

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

            if (resultSet.next()) {
                resultSet.last();
                int x = resultSet.getRow();

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

                if ((x == 0) || (x > 1)) {
                    throw new UserManagementException("No user account was located for the provided data.");
                }

                resultSet.first();

                userAccount = new ArrayList<Object>(
                        Arrays.asList(resultSet.getString(userAttributes.getCommonName()),
                                resultSet.getString(userAttributes.getUserId()),
                                resultSet.getString(securityAttributes.getLockCount()),
                                resultSet.getString(securityAttributes.getLastLogin()),
                                resultSet.getString(securityAttributes.getExpiryDate()),
                                resultSet.getString(userAttributes.getSurname()),
                                resultSet.getString(userAttributes.getGivenName()),
                                resultSet.getString(userAttributes.getDisplayName()),
                                resultSet.getString(userAttributes.getEmailAddr()),
                                resultSet.getString(userAttributes.getTelephoneNumber()),
                                resultSet.getString(userAttributes.getMemberOf()),
                                resultSet.getString(securityAttributes.getIsSuspended()),
                                resultSet.getString(securityAttributes.getOlrSetupReq()),
                                resultSet.getString(securityAttributes.getOlrLocked())));

                if (DEBUG) {
                    DEBUGGER.debug("UserAccount: {}", userAccount);
                }
            }
        } else {
            throw new UserManagementException("No users were located with the provided information");
        }
    } catch (SQLException sqx) {
        throw new UserManagementException(sqx.getMessage(), sqx);
    } finally {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

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

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

    return userAccount;
}

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

/**
 * @see com.cws.esolutions.security.dao.usermgmt.interfaces.UserManager#searchUsers(java.lang.String)
 */// w  ww .ja  va2  s.c o  m
public synchronized List<String[]> searchUsers(final String searchData) throws UserManagementException {
    final String methodName = SQLUserManager.CNAME
            + "#searchUsers(final String searchData) throws UserManagementException";

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

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String[]> results = 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 getUserByAttribute(?, ?) }");
        stmt.setString(1, searchData);
        stmt.setInt(2, 0);

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

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

            if (resultSet.next()) {
                resultSet.beforeFirst();
                results = new ArrayList<String[]>();

                while (resultSet.next()) {
                    String[] userData = new String[] { resultSet.getString("cn"), resultSet.getString("uid") };

                    if (DEBUG) {
                        DEBUGGER.debug("Data: {}", (Object) userData);
                    }

                    results.add(userData);
                }

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

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

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

    return results;
}

From source file:com.mimp.hibernate.HiberNna.java

public InformeNna InformeExpNna(Long idInforme) {

    Session session = sessionFactory.getCurrentSession();
    session.beginTransaction();/*  w  w  w  .  j  a  va2 s  .c  o  m*/
    final Long id = idInforme;
    final InformeNna temp = new InformeNna();

    Work work = new Work() {
        @Override
        public void execute(Connection connection) throws SQLException {

            String hql = "{call HN_GET_INF_EVAL(?,?)}";
            CallableStatement statement = connection.prepareCall(hql);
            statement.setLong(1, id);
            statement.registerOutParameter(2, OracleTypes.CURSOR);
            statement.execute();

            ResultSet rs = (ResultSet) statement.getObject(2);

            if (rs.next()) {
                temp.setIdinformeNna(rs.getLong("IDINFORME_NNA"));
                temp.setNumero(rs.getString("NUMERO"));
                temp.setFecha(rs.getDate("FECHA"));
                temp.setResultado(rs.getString("RESULTADO"));
                temp.setObservaciones(rs.getString("OBSERVACIONES"));
            }

            rs.close();
            statement.close();
        }
    };
    session.doWork(work);
    return temp;
}

From source file:com.mimp.hibernate.HiberNna.java

public ArrayList<InformeNna> listaInformesExpNna(Long idExpNna) {

    Session session = sessionFactory.getCurrentSession();
    session.beginTransaction();/* w  w  w . java 2s .c o m*/
    final Long expNna = idExpNna;
    final ArrayList<InformeNna> lista = new ArrayList();

    Work work = new Work() {
        @Override
        public void execute(Connection connection) throws SQLException {

            String hql = "{call HN_LIST_INF_EVAL(?,?)}";
            CallableStatement statement = connection.prepareCall(hql);
            statement.setLong(1, expNna);
            statement.registerOutParameter(2, OracleTypes.CURSOR);
            statement.execute();

            ResultSet rs = (ResultSet) statement.getObject(2);

            while (rs.next()) {
                InformeNna tempInf = new InformeNna();
                tempInf.setIdinformeNna(rs.getLong("IDINFORME_NNA"));
                tempInf.setNumero(rs.getString("NUMERO"));
                tempInf.setFecha(rs.getDate("FECHA"));
                lista.add(tempInf);

            }

            rs.close();
            statement.close();
        }
    };
    session.doWork(work);
    return lista;
}

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IWebMessagingDAO#updateMessage(String, List)
 *//*from w w w  .  ja  va  2 s  . com*/
public synchronized boolean updateMessage(final String messageId, final List<Object> messageList)
        throws SQLException {
    final String methodName = IWebMessagingDAO.CNAME
            + "#updateMessage(final String messageId, final List<Object> messageList) throws SQLException";

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

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

    try {
        sqlConn = dataSource.getConnection();

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

        sqlConn.setAutoCommit(true);
        stmt = sqlConn.prepareCall("{CALL updateServiceMessage(?, ?, ?, ?, ?, ?, ?, ?)}");
        stmt.setString(1, messageId); // messageId
        stmt.setString(2, (String) messageList.get(0)); // messageTitle
        stmt.setString(3, (String) messageList.get(1)); // messageText
        stmt.setBoolean(4, (Boolean) messageList.get(2)); // active
        stmt.setBoolean(5, (Boolean) messageList.get(3)); // alert
        stmt.setBoolean(6, (Boolean) messageList.get(4)); // expiry
        stmt.setLong(7, (messageList.get(5) == null) ? 0 : (Long) messageList.get(5)); // expiry date
        stmt.setString(8, (String) messageList.get(6)); // modifyAuthor

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

        isComplete = (!(stmt.execute()));

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

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

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

    return isComplete;
}

From source file:com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java

@Override
public int editCategory(final Category category) {
    int uid = -1;

    Connection conn = null;// w w w  .  j a v  a  2  s.c  o m
    CallableStatement stmt = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_EDITCATEGORY (?,?,?)}");
        stmt.registerOutParameter(1, Types.INTEGER);
        stmt.setInt(2, category.getAuctionUid());
        stmt.setString(3, category.getName());

        stmt.execute();

        uid = stmt.getInt(1);
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));

        uid = -1;
    } finally {
        DbUtils.closeQuietly(conn, stmt, null);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("CATEGORY [method:{} result:{}]", new Object[] { "edit", uid });
    }

    return uid;
}

From source file:com.novartis.opensource.yada.adaptor.JDBCAdaptor.java

/**
 * Executes the statemetns stored in the query object.  Results are
 * stored in a data structure inside a {@link YADAQueryResult} object
 * contained by the query object.//www.  j  a  v a 2  s .co m
 * @since 4.0.0
 * @param yq {@link YADAQuery} object containing code to be executed
 * @throws YADAAdaptorExecutionException when the adaptor can't execute the statement or statements stored in the query 
 */
@Override
public void execute(YADAQuery yq) throws YADAAdaptorExecutionException {
    l.debug("Executing query [" + yq.getQname() + "]");
    boolean count = Boolean.valueOf(yq.getYADAQueryParamValue(YADARequest.PS_COUNT)[0]).booleanValue();
    boolean countOnly = Boolean.valueOf(yq.getYADAQueryParamValue(YADARequest.PS_COUNTONLY)[0]).booleanValue();
    int countResult = -1;
    int dataSize = yq.getData().size() > 0 ? yq.getData().size() : 1;
    for (int row = 0; row < dataSize; row++) {
        yq.setResult();
        YADAQueryResult yqr = yq.getResult();
        if (yq.getType().equals(Parser.CALL)) {
            CallableStatement c = yq.getCstmt(row);
            for (int i = 0; i < yq.getParamCount(row); i++) {
                int position = i + 1;
                char dt = yq.getDataTypes(row)[i];
                String val = yq.getVals(row).get(i);
                try {
                    setQueryParameter(c, position, dt, val);
                } catch (YADASQLException e) {
                    String msg = "There was an issue building the JDBC/SQL statement";
                    throw new YADAAdaptorExecutionException(msg, e);
                }
            }

            boolean hasData = false;

            try {
                hasData = c.execute();
            } catch (SQLException e) {
                String msg = "CallableStatement failed to execute";
                throw new YADAAdaptorExecutionException(msg, e);
            }
            ResultSet resultSet = null;
            if (hasData) {
                try {
                    resultSet = c.getResultSet();
                } catch (SQLException e) {
                    String msg = "Unable to get ResultSet from CallableStatement";
                    throw new YADAAdaptorExecutionException(msg, e);
                }
            } else {
                resultSet = new YADAResultSet();
            }
            yqr.addResult(row, resultSet);
            if (count) {
                try {
                    while (resultSet.next()) {
                        countResult++;
                    }
                } catch (SQLException e) {
                    String msg = "There was a problem iterating through the CallableStatement's ResultSet for row count.";
                    throw new YADAAdaptorExecutionException(msg, e);
                }
                yqr.addCountResult(row, new Integer(countResult));
            }
        } else // SELECT, UPDATE, INSERT, DELETE
        {
            PreparedStatement p = yq.getPstmt(row);
            for (int i = 0; i < yq.getParamCount(row); i++) {
                int position = i + 1;
                char dt = yq.getDataTypes(row)[i];
                String val = yq.getVals(row).get(i);
                try {
                    setQueryParameter(p, position, dt, val);
                } catch (YADASQLException e) {
                    String msg = "There was an issue building the JDBC/SQL statement";
                    throw new YADAAdaptorExecutionException(msg, e);
                }
            }
            if (yq.getType().equals(Parser.SELECT)) {
                ResultSet resultSet = null;
                try {
                    if (!countOnly) {
                        resultSet = p.executeQuery();
                        yqr.addResult(row, resultSet);
                    }
                } catch (SQLException e) {
                    String msg = "PreparedStatement for data failed to execute.";
                    throw new YADAAdaptorExecutionException(msg, e);
                }

                if (count || countOnly) {
                    p = yq.getPstmtForCount(p);
                    for (int i = 0; i < yq.getParamCount(row); i++) {
                        int position = i + 1;
                        char dt = yq.getDataTypes(row)[i];
                        String val = yq.getVals(row).get(i);
                        try {
                            setQueryParameter(p, position, dt, val);
                        } catch (YADASQLException e) {
                            String msg = "There was an issue building the JDBC/SQL statement";
                            throw new YADAAdaptorExecutionException(msg, e);
                        }
                    }
                    try {
                        resultSet = p.executeQuery();
                    } catch (SQLException e) {
                        String msg = "PreparedStatement for row count failed to execute.";
                        throw new YADAAdaptorExecutionException(msg, e);
                    }
                    try {
                        while (resultSet.next()) {
                            countResult = resultSet.getInt(SQL_COUNT);
                        }
                    } catch (SQLException e) {
                        String msg = "There was a problem iterating over ResultSet for row count.";
                        throw new YADAAdaptorExecutionException(msg, e);
                    }
                    yqr.addCountResult(row, new Integer(countResult));
                }
            } else // UPDATE, INSERT, DELETE
            {
                try {
                    countResult = p.executeUpdate();
                } catch (SQLException e) {
                    String msg = "Prepared statement for update failed to execute";
                    throw new YADAAdaptorExecutionException(msg, e);
                }
                yqr.addCountResult(row, new Integer(countResult));
            }
        }
    }
}

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

/**
 * Execute a stored procedure that updates data
 * //from   w ww.j  ava2 s  .  c  om
 * @param statementScope
 *            - the request scope
 * @param conn
 *            - the database connection
 * @param sql
 *            - the SQL to call the procedure
 * @param parameters
 *            - the parameters for the procedure
 * @return - the rows impacted by the procedure
 * @throws SQLException
 *             - if the procedure fails
 */
public int executeUpdateProcedure(StatementScope statementScope, Connection conn, String sql,
        Object[] parameters) throws SQLException {
    ErrorContext errorContext = statementScope.getErrorContext();
    errorContext.setActivity("executing update procedure");
    errorContext.setObjectId(sql);
    CallableStatement cs = null;
    setupResultObjectFactory(statementScope);
    int rows = 0;
    try {
        errorContext.setMoreInfo("Check the SQL Statement (preparation failed).");
        cs = prepareCall(statementScope.getSession(), conn, sql);
        setStatementTimeout(statementScope.getStatement(), cs);
        ParameterMap parameterMap = statementScope.getParameterMap();
        ParameterMapping[] mappings = parameterMap.getParameterMappings();
        errorContext.setMoreInfo("Check the output parameters (register output parameters failed).");
        registerOutputParameters(cs, mappings);
        errorContext.setMoreInfo("Check the parameters (set parameters failed).");
        parameterMap.setParameters(statementScope, cs, parameters);
        errorContext.setMoreInfo("Check the statement (update procedure failed).");
        cs.execute();
        rows = cs.getUpdateCount();
        errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
        retrieveOutputParameters(statementScope, cs, mappings, parameters, null);
    } finally {
        closeStatement(statementScope.getSession(), cs);
    }
    return rows;
}

From source file:net.sourceforge.msscodefactory.cfasterisk.v2_4.CFAsteriskSybase.CFAsteriskSybaseClusterTable.java

public int nextSecAppIdGen(CFSecurityAuthorization Authorization, CFSecurityClusterPKey PKey) {
    final String S_ProcName = "nextSecAppIdGen";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Not in a transaction");
    }//w w w  .  java2s.c  om
    Connection cnx = schema.getCnx();
    long Id = PKey.getRequiredId();

    CallableStatement stmtSelectNextSecAppIdGen = null;
    try {
        String sql = "{ call sp_next_secappidgen( ?" + ", " + "?" + " ) }";
        stmtSelectNextSecAppIdGen = cnx.prepareCall(sql);
        int argIdx = 1;
        stmtSelectNextSecAppIdGen.registerOutParameter(argIdx++, java.sql.Types.INTEGER);
        stmtSelectNextSecAppIdGen.setLong(argIdx++, Id);
        stmtSelectNextSecAppIdGen.execute();
        int nextId = stmtSelectNextSecAppIdGen.getInt(1);
        return (nextId);
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (stmtSelectNextSecAppIdGen != null) {
            try {
                stmtSelectNextSecAppIdGen.close();
            } catch (SQLException e) {
            }
            stmtSelectNextSecAppIdGen = null;
        }
    }
}