Example usage for java.sql CallableStatement executeQuery

List of usage examples for java.sql CallableStatement executeQuery

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

Introduction

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

Prototype

ResultSet executeQuery() throws SQLException;

Source Link

Document

Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

Usage

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

/**
 * @see com.cws.esolutions.security.dao.usermgmt.interfaces.UserManager#validateUserAccount(java.lang.String, java.lang.String)
 *///from  w w  w  .j  a v a  2s. c o  m
public synchronized boolean validateUserAccount(final String userId, final String userGuid)
        throws UserManagementException {
    final String methodName = SQLUserManager.CNAME
            + "#validateUserAccount(final String userId, final String userGuid) throws UserManagementException";

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

    boolean isValid = false;
    Connection sqlConn = null;
    ResultSet resultSet = null;
    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 getUserByAttribute(?, ?) }");
        stmt.setString(1, userId);
        stmt.setInt(2, 0);

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

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

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

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

                while (resultSet.next()) {
                    if ((StringUtils.equals(resultSet.getString(1), userGuid))
                            || (StringUtils.equals(resultSet.getString(2), userId))) {
                        resultSet.close();
                        stmt.close();
                        sqlConn.close();

                        throw new UserManagementException(
                                "A user currently exists 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 isValid;
}

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

@Override
public List<Auction> getAuctions(final int start, final int length, final String sort, final String dir) {
    List<Auction> objs = Lists.newArrayList();

    Connection conn = null;/*  www. java  2  s.  c  o  m*/
    CallableStatement stmt = null;
    ResultSet rs = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_GETAUCTIONS (?,?,?,?)}");
        stmt.setInt(1, start);
        stmt.setInt(2, length);
        stmt.setString(3, sort);
        stmt.setString(4, dir);

        rs = stmt.executeQuery();

        while (rs.next()) {
            AuctionBuilder builder = Auction.newBuilder().setUid(rs.getInt("UID"))
                    .setName(rs.getString("NAME"));

            Date startdate = rs.getDate("STARTDATE");
            if (startdate != null) {
                builder.setStartDate(startdate.getTime());
            }

            Date enddate = rs.getDate("ENDDATE");
            if (enddate != null) {
                builder.setEndDate(enddate.getTime());
            }

            builder.setLogoUrl(rs.getString("LOGOURL"));
            builder.setColor(rs.getString("COLOR"));

            objs.add(builder.build());
        }
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, rs);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("AUCTION [method:{} result:{}]", new Object[] { "get", objs.size() });
    }

    return ImmutableList.copyOf(objs);
}

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

@Override
public List<Category> getCategories(final int auctionUid, final int start, final int length, final String sort,
        final String dir) {
    List<Category> objs = Lists.newArrayList();

    Connection conn = null;//from   w  ww  .jav  a2s.com
    CallableStatement stmt = null;
    ResultSet rs = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_GETCATEGORIES (?,?,?,?,?)}");
        stmt.setInt(1, auctionUid);
        stmt.setInt(2, start);
        stmt.setInt(3, length);
        stmt.setString(4, sort);
        stmt.setString(5, dir);

        rs = stmt.executeQuery();

        while (rs.next()) {
            CategoryBuilder builder = Category.newBuilder().setUid(rs.getInt("UID"))
                    .setAuctionUid(rs.getInt("AUCTIONUID")).setName(rs.getString("NAME"));

            objs.add(builder.build());
        }
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, rs);
    }

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

    return ImmutableList.copyOf(objs);
}

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

@Override
public List<User> getUsers(final int auctionUid, final int start, final int length, final String sort,
        final String dir) {
    List<User> objs = Lists.newArrayList();

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

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_GETUSERS (?,?,?,?,?)}");
        stmt.setInt(1, auctionUid);
        stmt.setInt(2, start);
        stmt.setInt(3, length);
        stmt.setString(4, sort);
        stmt.setString(5, dir);

        rs = stmt.executeQuery();

        while (rs.next()) {
            UserBuilder builder = User.newBuilder().setUid(rs.getInt("UID"))
                    .setAuctionUid(rs.getInt("AUCTIONUID")).setBidderNumber(rs.getString("BIDDERNUMBER"))
                    .setFirstName(rs.getString("FIRSTNAME")).setLastName(rs.getString("LASTNAME"))
                    .setRole(Roles.getId(rs.getInt("ROLE")));

            User obj = builder.build();
            obj.setPasswordHash(rs.getString("PASSWORDHASH"));

            objs.add(obj);
        }
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, rs);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("USER [method:{} result:{}]", new Object[] { "get", objs.size() });
    }

    return ImmutableList.copyOf(objs);
}

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

@Override
public List<Item> getItems(final int auctionUid, final int categoryuid, final int start, final int length,
        final String sort, final String dir) {
    List<Item> objs = Lists.newArrayList();

    Connection conn = null;//from   w  w w  .ja va 2  s.c  o  m
    CallableStatement stmt = null;
    ResultSet rs = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_GETITEMS (?,?,?,?,?,?)}");
        stmt.setInt(1, auctionUid);
        stmt.setInt(2, categoryuid);
        stmt.setInt(3, start);
        stmt.setInt(4, length);
        stmt.setString(5, sort);
        stmt.setString(6, dir);

        rs = stmt.executeQuery();

        while (rs.next()) {
            ItemBuilder builder = Item.newBuilder().setUid(rs.getInt("UID"))
                    .setAuctionUid(rs.getInt("AUCTIONUID")).setItemNumber(rs.getString("ITEMNUMBER"))
                    .setName(rs.getString("NAME")).setDescription(rs.getString("DESCRIPTION"))
                    .setCategory(rs.getString("CATEGORY")).setSeller(rs.getString("SELLER"))
                    .setValPrice(rs.getDouble("VALPRICE")).setMinPrice(rs.getDouble("MINPRICE"))
                    .setIncPrice(rs.getDouble("INCPRICE")).sertCurPrice(rs.getDouble("CURPRICE"))
                    .setWinner(rs.getString("WINNER")).setBidCount(rs.getInt("BIDCOUNT"))
                    .setWatchCount(rs.getInt("WATCHCOUNT")).setUrl(rs.getString("URL"))
                    .setMultiSale(rs.getBoolean("MULTI"));

            objs.add(builder.build());
        }
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, rs);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("ITEM [method:{} result:{}]", new Object[] { "get", objs.size() });
    }

    return ImmutableList.copyOf(objs);
}

From source file:com.app.uploads.ImageUploads.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods./*from www  .  ja va2  s .  c  om*/
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");
    PrintWriter out = response.getWriter();
    String[] Fielname = new String[2];
    CallableStatement pro;
    int i = 0;
    String UPLOAD_DIRECTORY = getServletContext().getRealPath("\\uploads\\");
    try {
        if (ServletFileUpload.isMultipartContent(request)) {
            try {
                String name = "";
                List<FileItem> multiparts;
                multiparts = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request);

                for (FileItem item : multiparts) {
                    if (!item.isFormField()) {
                        name = new File(item.getName()).getName();
                        item.write(new File(UPLOAD_DIRECTORY + File.separator + name));
                    } else if (item.isFormField()) {
                        String fiel = item.getFieldName();
                        InputStream is = item.getInputStream();
                        byte[] b = new byte[is.available()];
                        is.read(b);
                        if (i == 0) {
                            Fielname[0] = new String(b);
                        } else {
                            Fielname[1] = new String(b);
                        }
                        i++;
                    }

                }

                //File uploaded successfully
                Connection connect = OracleConnect.getConnect(Dir.Host, Dir.Port, Dir.Service, Dir.UserName,
                        Dir.PassWord);
                pro = connect.prepareCall("{call STILL_INSERT(?,?,?)}");
                pro.setString(1, name);
                pro.setString(2, Fielname[0]);
                pro.setString(3, Fielname[1]);
                pro.executeQuery();
                pro.close();
                connect.close();
                request.setAttribute("message", "File Uploaded Successfully");
                request.setAttribute("name", name);
            } catch (Exception ex) {
                request.setAttribute("message", "File Upload Failed due to " + ex);
            }

        } else {
            request.setAttribute("message", "Sorry this Servlet only handles file upload request");
        }
        out.print("Description:" + Fielname[0]);
        out.print("Locator:" + Fielname[1]);
        String pathReal = getServletContext().getRealPath("\\uploads\\");
        request.setAttribute("Description", Fielname[0]);
        request.setAttribute("Locator", Fielname[1]);
        request.setAttribute("path", pathReal);
        request.getRequestDispatcher("/result.jsp").forward(request, response);
    } finally {
        out.close();
    }
}

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

@Override
public Item addWatch(final Watch watch) {
    Item obj = null;//www .  jav  a2s  . co m

    Connection conn = null;
    CallableStatement stmt = null;
    ResultSet rs = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_EDITWATCH (?,?,?)}");
        stmt.registerOutParameter(1, Types.INTEGER);
        stmt.setInt(2, watch.getItemUid());
        stmt.setInt(3, watch.getUserUid());

        stmt.execute();
        stmt.close(); // close statement to prevent leak

        stmt = conn.prepareCall("{call SP_GETITEM (?)}");
        stmt.setInt(1, watch.getItemUid());

        rs = stmt.executeQuery();

        if (rs.next()) {
            ItemBuilder builder = Item.newBuilder().setUid(rs.getInt("UID"))
                    .setAuctionUid(rs.getInt("AUCTIONUID")).setItemNumber(rs.getString("ITEMNUMBER"))
                    .setName(rs.getString("NAME")).sertCurPrice(rs.getDouble("CURPRICE"))
                    .setWinner(rs.getString("WINNER")).setBidCount(rs.getInt("BIDCOUNT"))
                    .setWatchCount(rs.getInt("WATCHCOUNT"));

            obj = builder.build();
        }
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, rs);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("WATCH [method:{} result:{}]",
                new Object[] { "add", obj != null ? obj.toString() : "[error]" });
    }

    return obj;
}

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

@Override
public Item addBid(final Bid bid) {
    Item obj = null;/*from w w  w.ja v  a 2  s. c  o  m*/

    Connection conn = null;
    CallableStatement stmt = null;
    ResultSet rs = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_EDITBID (?,?,?,?)}");
        stmt.registerOutParameter(1, Types.INTEGER);
        stmt.setInt(2, bid.getItemUid());
        stmt.setInt(3, bid.getUserUid());
        stmt.setDouble(4, bid.getBidPrice());

        stmt.execute();
        stmt.close(); // close statement to prevent leak

        stmt = conn.prepareCall("{call SP_GETITEM (?)}");
        stmt.setInt(1, bid.getItemUid());

        rs = stmt.executeQuery();

        if (rs.next()) {
            ItemBuilder builder = Item.newBuilder().setUid(rs.getInt("UID"))
                    .setAuctionUid(rs.getInt("AUCTIONUID")).setItemNumber(rs.getString("ITEMNUMBER"))
                    .setName(rs.getString("NAME")).sertCurPrice(rs.getDouble("CURPRICE"))
                    .setWinner(rs.getString("WINNER")).setBidCount(rs.getInt("BIDCOUNT"))
                    .setWatchCount(rs.getInt("WATCHCOUNT"));

            obj = builder.build();
        }
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, rs);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("BID [method:{} result:{}]",
                new Object[] { "add", obj != null ? obj.toString() : "[error]" });
    }

    return obj;
}

From source file:com.wabacus.system.dataset.select.rationaldbassistant.GetDataSetBySP.java

protected Object doGetResultSet(SPDataSetValueBean spBean, Object typeObj, StringBuffer systemParamsBuf) {
    log.debug(systemParamsBuf.toString());
    String procedure = spBean.getProcedure();
    if (rbean.getInterceptor() != null) {
        Object obj = rbean.getInterceptor().beforeLoadData(rrequest, rbean, typeObj, procedure);
        if (!(obj instanceof String)) {
            return obj;
        }// ww  w .ja  va 2s  .  c o m
        procedure = (String) obj;
    }
    if (Config.show_sql)
        log.info("Execute sql: " + procedure);
    CallableStatement cstmt = null;
    try {
        cstmt = rrequest.getConnection(spBean.getOwnerSpProvider().getDatasource()).prepareCall(procedure);
        AbsDatabaseType dbtype = rrequest.getDbType(spBean.getOwnerSpProvider().getDatasource());
        VarcharType varcharObj = (VarcharType) Config.getInstance().getDataTypeByClass(VarcharType.class);
        int idx = 1;
        if (spBean.getLstStoreProcedureParams() != null && spBean.getLstStoreProcedureParams().size() > 0) {
            for (String paramTmp : spBean.getLstStoreProcedureParams()) {
                if (WabacusAssistant.getInstance().isGetRequestContextValue(paramTmp)) {//request/session?
                    varcharObj.setPreparedStatementValue(idx,
                            WabacusAssistant.getInstance().getRequestContextStringValue(rrequest, paramTmp, ""),
                            cstmt, dbtype);
                } else if (Tools.isDefineKey("condition", paramTmp)) {
                    setConditionValue(rrequest, spBean, cstmt, dbtype, idx,
                            Tools.getRealKeyByDefine("condition", paramTmp), varcharObj);
                } else {
                    varcharObj.setPreparedStatementValue(idx, paramTmp, cstmt, dbtype);
                }
                idx++;
            }
        }
        if (spBean.getOwnerSpProvider().isUseSystemParams()) {
            if (systemParamsBuf == null)
                systemParamsBuf = new StringBuffer();
            cstmt.setString(idx++, systemParamsBuf.toString());//??
        }
        if (dbtype instanceof Oracle) {
            cstmt.registerOutParameter(idx, OracleTypes.CURSOR);
        }
        rrequest.addUsedStatement(cstmt);
        cstmt.executeQuery();
        ResultSet rs = null;
        if (dbtype instanceof Oracle) {
            rs = (ResultSet) cstmt.getObject(idx);
        } else {
            rs = cstmt.getResultSet();
        }
        return rs;
    } catch (SQLException e) {
        throw new WabacusRuntimeException(
                "??" + rbean.getPath() + "?SQL" + procedure + "", e);
    }
}

From source file:weave.utils.SQLUtils.java

/**
 * @param conn An existing SQL Connection
 * @param selectColumns The list of column names 
 * @param fromSchema The schema containing the table to perform the SELECT statement on.
 * @param fromTable The table to perform the SELECT statement on.
 * @param whereParams A map of column names to String values used to construct a WHERE clause.
 * @return The resulting rows returned by the query.
 * @throws SQLException If the query fails.
 *///from  www.  ja v a 2  s  .c o m
public static List<Map<String, String>> getRecordsFromQuery(Connection conn, List<String> selectColumns,
        String fromSchema, String fromTable, Map<String, String> whereParams) throws SQLException {
    CallableStatement cstmt = null;
    ResultSet rs = null;
    String query = null;
    try {
        // create list of columns to use in SELECT statement
        String columnQuery = "";
        for (int i = 0; selectColumns != null && i < selectColumns.size(); i++) {
            if (i > 0)
                columnQuery += ",";
            columnQuery += quoteSymbol(conn, selectColumns.get(i));
        }
        if (columnQuery.length() == 0)
            columnQuery = "*"; // select all columns

        // build WHERE clause
        String whereQuery = "";
        int i = 0;
        Iterator<Entry<String, String>> paramsIter = whereParams.entrySet().iterator();
        while (paramsIter.hasNext()) {
            Entry<String, String> pair = paramsIter.next();
            String key = pair.getKey();
            if (i > 0)
                whereQuery += " AND ";
            whereQuery += caseSensitiveCompare(conn, quoteSymbol(conn, key), "?");
            i++;
        }
        if (whereQuery.length() > 0)
            whereQuery = "WHERE " + whereQuery;

        // build complete query
        query = String.format("SELECT %s FROM %s %s", columnQuery,
                quoteSchemaTable(conn, fromSchema, fromTable), whereQuery);
        cstmt = conn.prepareCall(query);

        // set query parameters
        i = 1;
        paramsIter = whereParams.entrySet().iterator();
        while (paramsIter.hasNext()) {
            Map.Entry<String, String> pairs = (Map.Entry<String, String>) paramsIter.next();
            String value = pairs.getValue();
            cstmt.setString(i, value);
            i++;
        }

        rs = cstmt.executeQuery();

        return getRecordsFromResultSet(rs);
    } catch (SQLException e) {
        System.out.println(query);
        throw e;
    } finally {
        // close everything in reverse order
        cleanup(rs);
        cleanup(cstmt);
    }
}