Example usage for java.sql CallableStatement setInt

List of usage examples for java.sql CallableStatement setInt

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

Introduction

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

Prototype

void setInt(String parameterName, int x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java int value.

Usage

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

/**
 * getBillingHistory is to be used to get  get the billing history of a given customer.
 * <p/>//from  ww  w .  j a  v a 2s  .co m
 * <p/>
 * <b>DATABASE PROCEDURE:</b>
 *  
 * @code
 *  FUNCTION fn_get_history(
 *    customer   IN VARCHAR2,
 *    start_date IN DATE,
 *    end_date   IN DATE,
 *    page       IN INTEGER,
 *    records    IN INTEGER )
 *  RETURN ref_cursor;
 *  @endcode
 * <p/>
 * <b>DATABASE RESULT SET:</b>
 * <ul>
 *    <li>ITEM_ID,</li>
 *    <li>BILL_ITEM_NO,</li>
 *    <li>AR_ACCOUNT_NO,</li>
 *    <li>ACCOUNT_NO,</li>
 *    <li>ORDER_NO,</li>
 *    <li>ORDER_LINE_NO,</li>
 *    <li>EVENT_TYPE,</li>
 *    <li>CHARGE_TYPE,</li>
 *    <li> CURRENCY,</li>
 *    <li>CREATED_DATE,</li>
 *    <li>BILL_DATE,</li>
 *    <li>DUE_DATE,</li>
 *    <li>STATUS,</li>
 *    <li>REASON_CODE,</li>
 *    <li>ITEM_TOTAL,</li>
 *    <li>ITEM_DUE,</li>
 *    <li>ITEM_DISPUTED,</li>
 *    <li>ITEM_BASE,</li>
 *    <li>ITEM_TAX,</li>
 *    <li>ITEM_DESCRIPTION,</li>
 *    <li>ITEM_CODE,</li>
 *    <li>LICENSE,</li>
 *    <li>PAY_TYPE,</li>
 *    <li>PAY_DESCR,</li>
 *    <li>PAY_ACCT_TYPE,
 *    <li>PAY_PSON,</li>
 *    <li>QUANTITY </li>
 *  </ul>
 *  
 * @param customer  :  The Customer.accountNo of the customer we want history for
 * @param startDate : The starting date of the allocation - to be merged with a billing history record set
 * @param endDate   :  The ending date of the allocation - to be merged with a billing history record set
 * @param skip      :  Starting record for server side paging
 * @param pageSize  :  How many records to retrieve 
 * 
 * @return A list of LineItem objects in reverse date order sort
 */
@Override
public List<LineItem> getBillingHistory(String cust, Date startDate, Date endDate, Integer startPage,
        Integer pageSize) throws JSONException {

    List<LineItem> history = new ArrayList<LineItem>();

    java.sql.Date sqlStartDate = new java.sql.Date(startDate.getTime());
    java.sql.Date sqlEndDate = new java.sql.Date(endDate.getTime());

    String query = "begin ? := billing_inquiry.fn_get_history( ?, ?, ?, ?, ? ); 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, cust);
        stmt.setDate(3, sqlStartDate);
        stmt.setDate(4, sqlEndDate);
        stmt.setInt(5, startPage);
        stmt.setInt(6, pageSize);

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

        while (rs.next()) {

            LineItem l = new LineItem();
            l.setRowId(rs.getInt("ROW_ID"));
            l.setBaseAmount(rs.getBigDecimal("ITEM_BASE"));
            l.setItemTotal(rs.getBigDecimal("ITEM_TOTAL"));
            l.setItemDue(rs.getBigDecimal("ITEM_DUE"));
            l.setItemDisputed(rs.getBigDecimal("ITEM_DISPUTED"));
            l.setTaxAmount(rs.getBigDecimal("ITEM_TAX"));
            l.setBillDate(rs.getTimestamp("BILL_DATE"));
            l.setBillItemNo(rs.getString("BILL_ITEM_NO"));
            l.setBillTo(rs.getString("AR_ACCOUNT_NO"));
            l.setChargeType(rs.getString("CHARGE_TYPE"));
            l.setCreatedDate(rs.getTimestamp("CREATED_DATE"));
            l.setCurrency(rs.getString("CURRENCY"));
            l.setDueDate(rs.getTimestamp("DUE_DATE"));
            l.setEventType(rs.getString("EVENT_TYPE"));
            l.setItemCode(rs.getString("ITEM_CODE"));
            l.setItemDescription(rs.getString("ITEM_DESCRIPTION"));
            l.setLicense(rs.getString("LICENSE"));
            l.setItemID(rs.getString("ITEM_ID"));
            l.setOrderLine(rs.getString("ORDER_LINE_NO"));
            l.setOrderNo(rs.getString("ORDER_NO"));
            l.setPayAccountType(rs.getString("PAY_ACCT_TYPE"));
            l.setPayDescription(rs.getString("PAY_DESCR"));
            l.setPayType(rs.getString("PAY_TYPE"));
            l.setpSON(rs.getString("PAY_PSON"));
            l.setQuantity(rs.getInt("QUANTITY"));
            l.setReasonCode(rs.getString("REASON_CODE"));
            l.setStatus(rs.getInt("STATUS"));
            history.add(l);
        }

        conn.close();
        rs.close();

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

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

    return history;
}

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

/**
 * getCustomerList//from  w w w.  j  av a 2s. c o  m
 * 
 * <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:it.greenvulcano.gvesb.datahandling.dbo.DBOCallSP.java

/**
 * @param cs//  ww w .  ja va2  s.  c o  m
 * @param num
 * @throws SQLException
 */
private void setInt(CallableStatement cs, int num) throws SQLException {
    if (useName) {
        cs.setInt(currName, num);
    } else {
        cs.setInt(colIdx, num);
    }
}

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)
 */// ww w  .  ja  v  a  2 s.co  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.app.uploads.ImageTest.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods./* w  w  w .  j a v  a  2  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 type = "";
    CallableStatement pro;

    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);
                        type = new String(b);
                    }

                }

                //File uploaded successfully
                Connection connect = OracleConnect.getConnect(Dir.Host, Dir.Port, Dir.Service, Dir.UserName,
                        Dir.PassWord);
                pro = connect.prepareCall("{call STILL_INSERT_TEST(?,?)}");
                pro.setInt(1, 2);
                pro.setString(2, name);
                pro.executeQuery();
                pro.close();
                connect.close();
                if (name != null) {
                    request.setAttribute("type", type);
                    request.setAttribute("success", "ok");
                }
            } catch (Exception ex) {
                request.setAttribute("message", "File Upload Failed due to " + ex);
            }

        } else {
            request.setAttribute("message", "Sorry this Servlet only handles file upload request");
        }

        request.getRequestDispatcher("/SearchEngine.jsp").forward(request, response);
    } finally {
        out.close();
    }
}

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

@Override
public void signout(final int userUid) {
    Connection conn = null;/*from  w ww . ja  v  a 2s  .co m*/
    CallableStatement stmt = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_SIGNOUT (?)}");
        stmt.setInt(1, userUid);

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

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IApplicationDataDAO#getApplicationsByAttribute(java.lang.String, int)
 *//* ww w  .  j  a v  a2  s . c o  m*/
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.mobiaware.auction.data.impl.MySqlDataServiceImpl.java

@Override
public String signin(final int userUid) {
    Connection conn = null;//ww w  .jav  a2 s  . c o m
    CallableStatement stmt = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_SIGNIN (?,?)}");
        stmt.setInt(1, userUid);
        stmt.registerOutParameter(2, Types.VARCHAR);

        stmt.execute();

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

    return null;
}

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)
 */// ww w.  ja v  a  2s.co  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.mobiaware.auction.data.impl.MySqlDataServiceImpl.java

@Override
public int deleteItem(final int uid) {
    int result = -1;

    Connection conn = null;/*  www  .  j av  a2s. com*/
    CallableStatement stmt = null;
    ResultSet rs = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_DELETEITEM (?)}");
        stmt.setInt(1, uid);

        rs = stmt.executeQuery();

        if (rs.next()) {
            result = rs.getInt(1);
        }
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, rs);
    }

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

    return result;
}