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.ServiceDataDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServiceDataDAO#getService(java.lang.String)
 *//*from  w w  w .  j  a va  2  s  .c  o m*/
public synchronized List<String> getService(final String attribute) throws SQLException {
    final String methodName = IServiceDataDAO.CNAME + "#getService(final String attribute) throws SQLException";

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

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

    try {
        sqlConn = dataSource.getConnection();

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

        sqlConn.setAutoCommit(true);

        // we dont know what we have here - it could be a guid or it could be a hostname
        // most commonly it'll be a guid, but we're going to search anyway
        stmt = sqlConn.prepareCall("{ CALL getServiceData(?) }");
        stmt.setString(1, attribute);

        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<String>(Arrays.asList(resultSet.getString(1), // SERVICE_TYPE
                        resultSet.getString(2), // NAME
                        resultSet.getString(3), // REGION
                        resultSet.getString(4), // NWPARTITION
                        resultSet.getString(5), // STATUS
                        resultSet.getString(6), // SERVERS
                        resultSet.getString(7))); // DESCRIPTION

                if (DEBUG) {
                    DEBUGGER.debug("responseData: {}", 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.rosy.bill.dao.hibernate.SimpleHibernateDao.java

@SuppressWarnings("deprecation")
public String callProc(final String proc, final List<Object> paramList, final int outIndex, final int outType) {
    String result = null;//from w  w w  .  jav a  2s  .c om
    java.sql.Connection conn = null;
    java.sql.CallableStatement cstmt = null;
    //Session session = this.getSession();
    try {

        conn = this.getSession().connection();
        conn.setAutoCommit(false);
        cstmt = conn.prepareCall(proc);
        for (int i = 0; paramList != null && i < paramList.size(); i++) {
            if (i + 1 == outIndex) {
                //cstmt.setInt(i + 1,
                //      (Integer.parseInt(paramList.get(i).toString())));
                cstmt.setString(i + 1, paramList.get(i).toString());
            } else {
                cstmt.setInt(i + 1, Integer.valueOf(paramList.get(i).toString()));
            }
        }
        cstmt.registerOutParameter(outIndex, outType);
        cstmt.execute();
        result = cstmt.getString(outIndex);
        conn.commit();
        //session.flush();
        //session.clear();
    } catch (Exception ex) {
        try {
            conn.rollback();
        } catch (SQLException e1) {
            logger.error("[" + proc + "]?" + e1.getMessage());
            e1.printStackTrace();
        }
        ex.printStackTrace();
    } finally {
        if (cstmt != null) {
            try {
                cstmt.close();
            } catch (Exception ex) {
            }
        }
    }
    return result;
}

From source file:com.mobilewallet.common.dao.RegisterDAO.java

public Object[] registerUser(String email, String fname, String lname, String dob, String gender, String pwd,
        String imei, String accounts, String country, String handsetModel, String androidVer, String emulator,
        String gcmId, String androidId, String refCode, String ip, String fbId) {
    Object[] obj = null;//from w w  w . ja va 2s  . co  m
    Connection con = null;
    CallableStatement cstmt = null;
    try {
        con = ds.getConnection();
        cstmt = con.prepareCall("{call REGISTER_USER(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
        cstmt.setString(1, email);
        cstmt.setString(2, fname);
        cstmt.setString(3, lname);
        cstmt.setString(4, dob);
        cstmt.setString(5, gender);
        cstmt.setString(6, pwd);
        cstmt.setString(7, imei);
        cstmt.setString(8, accounts);
        cstmt.setString(9, country);
        cstmt.setString(10, handsetModel);
        cstmt.setString(11, androidVer);
        cstmt.setString(12, emulator);
        cstmt.setString(13, gcmId);
        cstmt.setString(14, androidId);
        cstmt.setString(15, refCode);
        cstmt.setString(16, ip);
        cstmt.setString(17, fbId);
        cstmt.registerOutParameter(18, java.sql.Types.INTEGER);
        cstmt.registerOutParameter(19, java.sql.Types.VARCHAR);
        cstmt.registerOutParameter(20, java.sql.Types.INTEGER);
        cstmt.registerOutParameter(21, java.sql.Types.INTEGER);

        cstmt.execute();

        obj = new Object[4];
        obj[0] = cstmt.getInt(18);//rvalue
        obj[1] = cstmt.getString(19);//user ref code
        obj[2] = cstmt.getFloat(20);//balance
        obj[3] = cstmt.getLong(21);//user id
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        try {
            if (cstmt != null) {
                cstmt.close();
            }
        } catch (Exception ex) {

        }
        try {
            if (con != null) {
                con.close();
            }
        } catch (Exception ex) {

        }
    }
    return obj;
}

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

/**
 * getCustomerList// w  ww .  j  av  a 2 s  .c om
 * 
 * <p/>
 * <p/>
 * <b>DATABASE PROCEDURE:</b>
 *  
 * @code
 *FUNCTION fn_search_customers
 * (
 *    phone      IN VARCHAR2,
 *    first_name IN VARCHAR2,
 *    last_name  IN VARCHAR2,
 *    company    IN VARCHAR2,
 *    cc_num     IN VARCHAR2,
 *    eft_num    IN VARCHAR2,
 *    records    IN INTEGER
 *  )
 *  RETURN ref_cursor;
 * @endcode
 *  
 * <p/>
 * <b>DATABASE RESULT SET:</b>
 * <ul>
 *    <li>ACCOUNT_NO,</li>
 *    <li>FIRST_NAME,</li>
 *    <li>LAST_NAME,</li>
 *    <li>COMPANY,</li>
 *    <li>PHONE,</li>
 *    <li>ADDRESS,</li>
 *    <li>CITY,</li>
  *    <li>STATE,</li>
 *    <li>ZIP,</li>
 *    <li>COUNTRY </li>
 *  </ul>
 *  
 * @param phone - The customer's phone number
 * @param firstName -  A wild-carded first name
 * @param lastName -  A wild-carded last name
 * @param company -  A wild-carded company name
 * @param ccNum -  A four digit last-four of a credit card
 * @param eftNum -  A four digit last-four of a bank account number
 * @param pageSize  :  How many records to retrieve 
 * 
 * @return A list of Customer objects in lastname, company alphabetical order
 *         - if rows returned is greater than pageSize will not return TOO MANY rows erros
 * 
 */
@Override
public List<Customer> getCustomerList(String phone, String firstName, String lastName, String company,
        String ccNum, String eftNum, Integer pageSize)

        throws JSONException {

    Integer startPage = 1;
    List<Customer> customers = new ArrayList<Customer>();

    String query = "begin ? := billing_inquiry.fn_search_customers( ?, ?, ?, ?, ?, ?, ?, ? ); end;";

    Connection conn = null;
    ResultSet rs = 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, phone);
        stmt.setString(3, firstName);
        stmt.setString(4, lastName);
        stmt.setString(5, company);
        stmt.setString(6, ccNum);
        stmt.setString(7, eftNum);
        stmt.setInt(8, startPage);
        stmt.setInt(9, pageSize);

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

        while (rs.next()) {

            Customer c = new Customer();
            c.setAccountNo(rs.getString("ACCOUNT_NO"));
            c.setFirstName(rs.getString("FIRST_NAME"));
            c.setLastName(rs.getString("LAST_NAME"));
            c.setCompany(rs.getString("COMPANY"));
            c.setPhone(rs.getString("PHONE"));
            c.setAddress(rs.getString("ADDRESS"));
            c.setCity(rs.getString("CITY"));
            c.setState(rs.getString("STATE"));
            c.setZip(rs.getString("ZIP"));
            c.setCountry(rs.getString("COUNTRY"));
            customers.add(c);
        }
        conn.close();
        rs.close();

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

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

    return customers;
}

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServerDataDAO#getServer(java.lang.String)
 *///ww w  .j  ava  2  s .  c  o  m
public synchronized List<Object> getServer(final String attribute) throws SQLException {
    final String methodName = IServerDataDAO.CNAME + "#getServer(final String attribute) throws SQLException";

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

    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);

        // we dont know what we have here - it could be a guid or it could be a hostname
        // most commonly it'll be a guid, but we're going to search anyway
        stmt = sqlConn.prepareCall("{ CALL retrServerData(?) }");
        stmt.setString(1, attribute);

        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), // T1.SYSTEM_GUID
                        resultSet.getString(2), // T1.SYSTEM_OSTYPE
                        resultSet.getString(3), // T1.SYSTEM_STATUS
                        resultSet.getString(4), // T1.SYSTEM_REGION
                        resultSet.getString(5), // T1.NETWORK_PARTITION
                        resultSet.getString(6), // T1.SYSTEM_TYPE
                        resultSet.getString(7), // T1.DOMAIN_NAME
                        resultSet.getString(8), // T1.CPU_TYPE
                        resultSet.getInt(9), // T1.CPU_COUNT
                        resultSet.getString(10), // T1.SERVER_RACK
                        resultSet.getString(11), // T1.RACK_POSITION
                        resultSet.getString(12), // T1.SERVER_MODEL
                        resultSet.getString(13), // T1.SERIAL_NUMBER
                        resultSet.getInt(14), // T1.INSTALLED_MEMORY
                        resultSet.getString(15), // T1.OPER_IP
                        resultSet.getString(16), // T1.OPER_HOSTNAME
                        resultSet.getString(17), // T1.MGMT_IP
                        resultSet.getString(18), // T1.MGMT_HOSTNAME
                        resultSet.getString(19), // T1.BKUP_IP
                        resultSet.getString(20), // T1.BKUP_HOSTNAME
                        resultSet.getString(21), // T1.NAS_IP
                        resultSet.getString(22), // T1.NAS_HOSTNAME
                        resultSet.getString(23), // T1.NAT_ADDR
                        resultSet.getString(24), // T1.COMMENTS
                        resultSet.getString(25), // T1.ASSIGNED_ENGINEER
                        resultSet.getTimestamp(26), // T1.ADD_DATE
                        resultSet.getTimestamp(27), // T1.DELETE_DATE
                        resultSet.getInt(28), // T1.DMGR_PORT
                        resultSet.getString(29), // T1.OWNING_DMGR
                        resultSet.getString(30), // T1.MGR_ENTRY
                        resultSet.getString(31), // T2.GUID
                        resultSet.getString(32))); // T2.NAME

                if (DEBUG) {
                    DEBUGGER.debug("responseData: {}", 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.netspective.axiom.sql.StoredProcedure.java

/**
 * Executes the stored procedure and records different statistics such as database connection times,
 * parameetr binding times, and procedure execution times.
 *
 * @param overrideIndexes parameter indexes to override
 * @param overrideValues  parameter override values
 *///  www .j  a v  a  2s .c om
protected QueryResultSet executeAndRecordStatistics(ConnectionContext cc, int[] overrideIndexes,
        Object[] overrideValues, boolean scrollable) throws NamingException, SQLException {
    if (log.isTraceEnabled())
        trace(cc, overrideIndexes, overrideValues);
    QueryExecutionLogEntry logEntry = execLog.createNewEntry(cc, this.getQualifiedName());
    Connection conn = null;
    CallableStatement stmt = null;
    boolean closeConnection = true;
    try {
        logEntry.registerGetConnectionBegin();
        conn = cc.getConnection();
        logEntry.registerGetConnectionEnd(conn);
        String sql = StringUtils.strip(getSqlText(cc));
        if (scrollable)
            stmt = conn.prepareCall(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        else
            stmt = conn.prepareCall(sql);

        logEntry.registerBindParamsBegin();

        if (parameters != null) {
            parameters.apply(cc, stmt, overrideIndexes, overrideValues);
            logEntry.registerBindParamsEnd();

            logEntry.registerExecSqlBegin();
            stmt.execute();
            logEntry.registerExecSqlEndSuccess();
            parameters.extract(cc, stmt);
            StoredProcedureParameter rsParameter = parameters.getResultSetParameter();
            if (rsParameter != null) {
                closeConnection = false;
                Value val = rsParameter.getValue().getValue(cc.getDatabaseValueContext());
                return (QueryResultSet) val.getValue();
            } else
                return null;
        } else {
            logEntry.registerExecSqlBegin();
            stmt.execute();
            logEntry.registerExecSqlEndSuccess();
            return null;
        }
    } catch (SQLException e) {
        logEntry.registerExecSqlEndFailed();
        log.error(createExceptionMessage(cc, overrideIndexes, overrideValues), e);
        throw e;
    }
}

From source file:es.indaba.jdbc.annotations.impl.GenericWork.java

@SuppressWarnings("unchecked")
@Override/* www .j a  va 2  s . c  o m*/
public void execute(Connection con) throws SQLException {
    String procedureCall = procedure.value();
    FieldResult[] fields = proceduresResult == null ? new FieldResult[0] : proceduresResult.value();

    CallableStatement st = null;
    try {
        st = con.prepareCall(procedureCall);
        for (SQLParameter p : parameters) {
            int pos = p.getPosition();
            Object val = p.getValue();
            Class type = p.getType();
            Class sqlType = p.getSqlType();
            Integer jdbcType = SQLTypeMapping.getSqlTypeforClass(type);
            if (jdbcType != null) {
                if (val != null) {
                    SQLTypeMapping.setSqlParameter(st, type, sqlType, pos, val);
                } else {
                    st.setNull(pos, jdbcType);
                }
            }
        }
        for (FieldResult field : fields) {
            int position = field.position();
            Class type = field.sqlType();
            if (type == null || type.equals(Object.class)) {
                type = field.type();
            }
            Integer jdbcType = SQLTypeMapping.getSqlTypeforClass(type);
            if (position != FieldResult.RESULTSET) {
                st.registerOutParameter(position, jdbcType);
            }
        }
        st.execute();

        if (!returnType.equals(void.class)) {
            // Return instance
            resultObject = returnType.newInstance();
            ResultSet rs = st.getResultSet();
            for (FieldResult field : fields) {
                String property = field.name();
                Object result = null;
                if (field.position() == FieldResult.RESULTSET) {
                    rs.next();
                    result = SQLTypeMapping.getSqlResultsetResult(rs, field.type(), field.sqlType(), 1);
                } else {
                    result = SQLTypeMapping.getSqlResult(st, field.type(), field.sqlType(), field.position());
                }
                BeanUtils.setProperty(resultObject, property, result);
            }
        }
    } catch (Exception e) {
        logger.log(Level.SEVERE, e.getMessage(), e);
    }
}

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServiceDataDAO#addService(java.util.List)
 *//* ww  w  .j  av  a  2  s .  c  om*/
public synchronized boolean addService(final List<String> data) throws SQLException {
    final String methodName = IServiceDataDAO.CNAME
            + "#addService(final List<String> data) throws SQLException";

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

        for (Object str : data) {
            DEBUGGER.debug("Value: {}", str);
        }
    }

    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 addNewService(?, ?, ?, ?, ?, ?, ?, ?)}");
        stmt.setString(1, data.get(0)); // guid
        stmt.setString(2, data.get(1)); // serviceType
        stmt.setString(3, data.get(2)); // name
        stmt.setString(4, data.get(3)); // region
        stmt.setString(5, data.get(4)); // nwpartition
        stmt.setString(6, data.get(5)); // status
        stmt.setString(7, data.get(6)); // servers
        stmt.setString(8, data.get(7)); // description

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

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

        if (DEBUG) {
            DEBUGGER.debug("isComplete: {}", isComplete);
        }
    } catch (SQLException 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.cws.esolutions.core.dao.impl.ServiceDataDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServiceDataDAO#updateService(java.util.List)
 *///  w  w w .  j  a v  a  2s . c  om
public synchronized boolean updateService(final List<String> data) throws SQLException {
    final String methodName = IServiceDataDAO.CNAME
            + "#updateService(final List<String> data) throws SQLException";

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

        for (Object str : data) {
            DEBUGGER.debug("Value: {}", str);
        }
    }

    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 updateServiceData(?, ?, ?, ?, ?, ?, ?, ?)}");
        stmt.setString(1, data.get(0)); // guid
        stmt.setString(2, data.get(1)); // serviceType
        stmt.setString(3, data.get(2)); // name
        stmt.setString(4, data.get(3)); // region
        stmt.setString(5, data.get(4)); // nwpartition
        stmt.setString(6, data.get(5)); // status
        stmt.setString(7, data.get(6)); // servers
        stmt.setString(8, data.get(7)); // description

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

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

        if (DEBUG) {
            DEBUGGER.debug("isComplete: {}", isComplete);
        }
    } catch (SQLException 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 boolean healthCheck() {
    Connection conn = null;/* ww  w.  j ava2  s.  c  o m*/
    CallableStatement stmt = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("select now()");

        stmt.execute();

        return true;
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, null);
    }

    return false;
}