Example usage for java.sql CallableStatement getResultSet

List of usage examples for java.sql CallableStatement getResultSet

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

Introduction

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

Prototype

ResultSet getResultSet() throws SQLException;

Source Link

Document

Retrieves the current result as a ResultSet object.

Usage

From source file:Main.java

public static void main(String args[]) throws Exception {
    String URL = "jdbc:microsoft:sqlserver://yourServer:1433;databasename=pubs";
    String userName = "yourUser";
    String password = "yourPassword";

    Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
    Connection con = DriverManager.getConnection(URL, userName, password);
    CallableStatement callstmt = con
            .prepareCall("INSERT INTO myIdentTable (col2) VALUES (?);SELECT @@IDENTITY");
    callstmt.setString(1, "testInputBatch");
    callstmt.execute();//from www  . j  a  v a 2 s  . c  o m

    int iUpdCount = callstmt.getUpdateCount();
    boolean bMoreResults = true;
    ResultSet rs = null;
    int myIdentVal = -1; // to store the @@IDENTITY

    while (bMoreResults || iUpdCount != -1) {
        rs = callstmt.getResultSet();
        if (rs != null) {
            rs.next();
            myIdentVal = rs.getInt(1);
        }
        bMoreResults = callstmt.getMoreResults();
        iUpdCount = callstmt.getUpdateCount();
    }
    callstmt.close();
    con.close();
}

From source file:jongo.jdbc.JDBCExecutor.java

/**
 * Executes the given stored procedure or function in the RDBMS using the given List 
 * of {@link jongo.jdbc.StoredProcedureParam}.
 * @param database database name or schema where to execute the stored procedure or function
 * @param queryName the name of the stored procedure or function. This gets converted to a {call foo()} statement.
 * @param params a List of {@link jongo.jdbc.StoredProcedureParam} used by the stored procedure or function.
 * @return a List of {@link jongo.rest.xstream.Row} with the results of the stored procedure (if out parameters are given)
 * or the results of the function./*  w  w  w.j a v a 2s .  c  o  m*/
 * @throws SQLException
 */
public static List<Row> executeQuery(final String database, final String queryName,
        final List<StoredProcedureParam> params) throws SQLException {
    l.debug("Executing stored procedure " + database + "." + queryName);

    DatabaseConfiguration dbconf = conf.getDatabaseConfiguration(database);
    QueryRunner run = JDBCConnectionFactory.getQueryRunner(dbconf);
    final String call = JongoUtils.getCallableStatementCallString(queryName, params.size());
    List<Row> rows = new ArrayList<Row>();

    Connection conn = null;
    CallableStatement cs = null;
    try {
        l.debug("Obtain connection from datasource");
        conn = run.getDataSource().getConnection();

        l.debug("Create callable statement for " + call);
        cs = conn.prepareCall(call);

        l.debug("Add parameters to callable statement");
        final List<StoredProcedureParam> outParams = addParameters(cs, params);

        l.debug("Execute callable statement");
        if (cs.execute()) {
            l.debug("Got a result set " + queryName);
            ResultSet rs = cs.getResultSet();
            JongoResultSetHandler handler = new JongoResultSetHandler(true);
            rows = handler.handle(rs);
        } else if (!outParams.isEmpty()) {
            l.debug("No result set, but we are expecting OUT values from " + queryName);
            Map<String, String> results = new HashMap<String, String>();
            for (StoredProcedureParam p : outParams) {
                results.put(p.getName(), cs.getString(p.getIndex())); // thank $deity we only return strings
            }
            rows.add(new Row(0, results));
        }
    } catch (SQLException ex) {
        l.debug(ex.getMessage());
        throw ex;
    } finally {
        try {
            if (cs != null && !cs.isClosed())
                cs.close();
        } catch (SQLException ex) {
            l.debug(ex.getMessage());
        }
        try {
            if (conn != null && !conn.isClosed())
                conn.close();
        } catch (SQLException ex) {
            l.debug(ex.getMessage());
        }
    }
    l.debug("Received " + rows.size() + " results.");
    return rows;
}

From source file:cn.gov.scciq.timer.acceptOrder.FRMDao.java

/**
 * ???//w w  w .  j  a v a  2 s  .  c  o  m
 * @param declNo
 * @return
 */
public static String getDeclProductFromEnt(String declNo) {
    String declProductFromEnt = null;
    Connection conn = null;
    CallableStatement proc = null;
    ResultSet rs = null;
    String call = "{call Pro_GetDeclProductFromEnt(?)}";
    try {
        conn = DBPool.ds.getConnection();
        proc = conn.prepareCall(call);
        proc.setString(1, declNo);
        proc.execute();
        rs = proc.getResultSet();
        while (rs.next()) {
            declProductFromEnt = rs.getString("RESULT_CONTENT");
            break;
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        log.error("N51", e);
    } catch (Exception e) {
        log.error("N52", e);
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (proc != null) {
                proc.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            log.error("N53", e);
        }
    }
    return declProductFromEnt;
}

From source file:org.marccarre.spring.db.testing.FooDao.java

public List<Foo> getByIdCallableStatement(int id) {
    final int fooId = id;
    return jdbcTemplate.execute(new CallableStatementCreator() {
        public CallableStatement createCallableStatement(Connection con) throws SQLException {
            CallableStatement cs = con.prepareCall("{call GetFoosById(?)}");
            cs.setInt(1, fooId);//w w  w. j ava  2  s.  co  m
            return cs;
        }
    }, new CallableStatementCallback<List<Foo>>() {
        public List<Foo> doInCallableStatement(CallableStatement cs) throws SQLException {
            cs.execute();
            List<Foo> foos = new ArrayList<Foo>();

            if (cs.getMoreResults()) {
                ResultSet rs = cs.getResultSet();
                FooRowMapper mapper = new FooRowMapper();
                int rowIndex = 0;
                while (rs.next()) {
                    foos.add(mapper.mapRow(rs, rowIndex));
                    rowIndex++;
                }
            }

            return foos;
        }
    });
}

From source file:org.xsystem.sql2.dml.DmlCommand.java

public Object execute(Connection con, String stmt, List<DmlParams> paramsSpec, Map<String, Object> values,
        boolean singleRow) {

    Object ret = null;/*www .  j a v  a 2 s.  c o m*/
    CallableStatement cs = null;
    ResultSet rs = null;
    try {
        cs = con.prepareCall(stmt);
        setParms(cs, paramsSpec, values);
        if (cs.execute()) {
            rs = cs.getResultSet();
            ret = new ArrayList();
            Map row = null;
            while (rs.next()) {
                row = rowAsMap(rs);
                if (singleRow) {
                    break;
                }
                ((List) ret).add(row);
            }
            if (singleRow) {
                return row;
            }
            ;
            return ret;
        } else {
            ret = buildOutParams(cs, paramsSpec);
            if (ret == null) {
                ret = (Integer) cs.getUpdateCount();
            }
        }
    } catch (SQLException ex) {
        buildError(ex, stmt, paramsSpec, values);
    } finally {
        Auxilary.close(rs);
        Auxilary.close(cs);
    }
    return ret;
}

From source file:com.jbrisbin.vpc.jobsched.sql.SqlMessageHandler.java

public SqlMessage handleMessage(final SqlMessage msg) throws Exception {
    log.debug("handling message: " + msg.toString());

    DataSource ds = appCtx.getBean(msg.getDatasource(), DataSource.class);
    JdbcTemplate tmpl = new JdbcTemplate(ds);

    String sql = msg.getSql();/*from  www.j  a va 2 s.c  o m*/
    CallableStatementCreator stmtCreator = null;
    CallableStatementCallback<SqlMessage> callback = null;
    if (sql.startsWith("plugin:")) {
        // Use a plugin to get the sql
        String pluginName = (sql.contains("?") ? sql.substring(7, sql.indexOf('?')) : sql.substring(7));
        final Plugin plugin = groovyPluginManager.getPlugin(pluginName);
        Map<String, Object> vars = new LinkedHashMap<String, Object>();
        vars.put("message", msg);
        vars.put("datasource", ds);
        vars.put("listen", groovyClosureFactory.createListenClosure(msg));
        vars.put("mapreduce", groovyClosureFactory.createMapReduceClosure(msg));
        plugin.setContext(vars);

        // Execute this plugin
        plugin.run();

        Object o = plugin.get("sql");
        if (null != o && o instanceof Closure) {
            sql = ((Closure) o).call(msg).toString();
        } else if (o instanceof String || o instanceof GString) {
            sql = o.toString();
        } else {
            throw new IllegalStateException("Can't convert " + String.valueOf(o) + " to SQL statement.");
        }
        msg.setSql(sql);

        o = plugin.get("statementCreator");
        if (null != o && o instanceof Closure) {
            stmtCreator = new CallableStatementCreator() {
                public CallableStatement createCallableStatement(Connection con) throws SQLException {
                    Object obj = ((Closure) plugin.get("statementCreator")).call(new Object[] { con, msg });
                    log.debug("from plugin statementCreator: " + String.valueOf(obj));
                    return (CallableStatement) obj;
                }
            };
        } else {
            throw new IllegalStateException("Can't convert " + String.valueOf(o)
                    + " to CallableStatementCreator. Define a closure named 'statementCreator' in your plugin.");
        }

        o = plugin.get("callback");
        if (null != o && o instanceof Closure) {
            callback = new CallableStatementCallback<SqlMessage>() {
                public SqlMessage doInCallableStatement(CallableStatement cs)
                        throws SQLException, DataAccessException {
                    Object obj = ((Closure) plugin.get("callback")).call(new Object[] { cs, msg });
                    log.debug("from plugin callback: " + String.valueOf(obj));
                    return (SqlMessage) obj;
                }
            };
        } else {
            throw new IllegalStateException("Can't convert " + String.valueOf(o)
                    + " to CallableStatementCallback. Define a closure named 'callback' in your plugin.");
        }
    } else {
        stmtCreator = new CallableStatementCreator() {
            public CallableStatement createCallableStatement(Connection connection) throws SQLException {
                CallableStatement stmt = connection.prepareCall(msg.getSql());
                List<Object> params = msg.getParams();
                if (null != params) {
                    int index = 1;
                    for (Object obj : params) {
                        stmt.setObject(index++, obj);
                    }
                }
                return stmt;
            }
        };
        callback = new CallableStatementCallback<SqlMessage>() {
            public SqlMessage doInCallableStatement(CallableStatement callableStatement)
                    throws SQLException, DataAccessException {
                if (null == msg.getResults().getData()) {
                    msg.getResults().setData(new ArrayList<List<Object>>());
                }
                if (callableStatement.execute()) {
                    ResultSet results = callableStatement.getResultSet();

                    // Pull out column names
                    ResultSetMetaData meta = results.getMetaData();
                    String[] columns = new String[meta.getColumnCount()];
                    for (int i = 1; i <= meta.getColumnCount(); i++) {
                        columns[i - 1] = meta.getColumnName(i);
                    }
                    msg.getResults().getColumnNames().addAll(Arrays.asList(columns));

                    int total = 0;
                    while (results.next()) {
                        List<Object> row = new ArrayList<Object>(columns.length);
                        for (int i = 1; i <= columns.length; i++) {
                            row.add(results.getObject(i));
                        }
                        msg.getResults().getData().add(row);
                        total++;
                    }
                    msg.getResults().setTotalRows(total);

                } else {
                    msg.getResults().getColumnNames().add("updateCount");
                    msg.getResults().setTotalRows(1);
                    List<Object> updCnt = new ArrayList<Object>(1);
                    updCnt.add(callableStatement.getUpdateCount());
                    msg.getResults().getData().add(updCnt);
                }
                return msg;
            }
        };
    }
    try {
        tmpl.setExceptionTranslator(appCtx.getBean(SQLExceptionTranslator.class));
    } catch (NoSuchBeanDefinitionException notfound) {
        // IGNORED
    }

    if (null != stmtCreator && null != callback) {
        try {
            tmpl.execute(stmtCreator, callback);
        } catch (Throwable t) {
            log.error(t.getMessage(), t);
            List<String> errors = new ArrayList<String>();
            errors.add(t.getMessage());
            Throwable cause = t.getCause();
            if (null != cause) {
                do {
                    errors.add(cause.getMessage());
                } while (null != (cause = cause.getCause()));
            }
            msg.getResults().setErrors(errors);
        }
    } else {
        log.warn("CallableStatementCreator and/or CallableStatementCallback where empty. "
                + "Make sure your plugin provides these under 'statementCreator' and 'callback' respectively.");
    }
    return msg;
}

From source file:org.opoo.oqs.spring.SpringQuery.java

protected Object doCall() throws QueryException {
    final PreparedStatementSetter pss = new ArgTypePreparedStatementSetter(valueArray(), typeArray());
    final ResultSetExtractor rse = createResultSetExtractor(createListResultSetHandler());
    return jdbcTemplate.execute(getSql(), new CallableStatementCallback() {
        public Object doInCallableStatement(CallableStatement callableStatement)
                throws SQLException, DataAccessException {

            if (getQueryTimeout() > 0) {
                callableStatement.setQueryTimeout(getQueryTimeout());
            }/*from ww  w . j  a  v  a2  s.  c  om*/

            pss.setValues(callableStatement);

            boolean retVal = callableStatement.execute();
            int updateCount = callableStatement.getUpdateCount();
            if (log.isDebugEnabled()) {
                log.debug("CallableStatement.execute() returned '" + retVal + "'");
                log.debug("CallableStatement.getUpdateCount() returned " + updateCount);
            }

            ResultSet rs = callableStatement.getResultSet();
            try {
                if (rs != null && rse != null) {
                    return rse.extractData(rs);
                }
            } finally {
                JdbcUtils.closeResultSet(rs);
            }

            if (updateCount > 0) {
                return new Integer(updateCount);
            }
            return null;
        }
    });
}

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

/**
 * @see com.cws.esolutions.security.dao.reference.interfaces.ISecurityReferenceDAO#obtainApprovedServers()
 *///from  w  w w.j a v  a 2  s  .c om
public synchronized List<String> obtainApprovedServers() throws SQLException {
    final String methodName = ISecurityReferenceDAO.CNAME + "#obtainApprovedServers() throws SQLException";

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

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

    try {
        sqlConn = dataSource.getConnection();

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

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

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

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

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

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

                securityList = new ArrayList<String>();

                while (resultSet.next()) {
                    if (DEBUG) {
                        DEBUGGER.debug(resultSet.getString(1));
                    }

                    // check if column is null
                    securityList.add(resultSet.getString(1));
                }

                if (DEBUG) {
                    DEBUGGER.debug("securityList: {}", securityList);
                }
            }
        }
    } 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 securityList;
}

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

/**
 * @see com.cws.esolutions.security.dao.reference.interfaces.ISecurityReferenceDAO#listAvailableServices()
 *//*from   w  w  w  .  jav  a2s  .  com*/
public synchronized Map<String, String> listAvailableServices() throws SQLException {
    final String methodName = ISecurityReferenceDAO.CNAME + "#listAvailableServices() throws SQLException";

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

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    Map<String, String> serviceMap = null;

    try {
        sqlConn = dataSource.getConnection();

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

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

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

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

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

            if (resultSet.next()) {
                resultSet.beforeFirst();
                serviceMap = new HashMap<String, String>();

                while (resultSet.next()) {
                    serviceMap.put(resultSet.getString(1), resultSet.getString(2));
                }

                if (DEBUG) {
                    DEBUGGER.debug("Map<String, String>: {}", serviceMap);
                }
            }
        }
    } 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 serviceMap;
}

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. j  a  va 2  s  .  c  o 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;
}