Example usage for java.sql CallableStatement setString

Introduction

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

Prototype

void setString(String parameterName, String x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java String value.

Usage

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IWebMessagingDAO#deleteMessage(String)
 *///from www  .j  a  va 2  s .  co m
public synchronized boolean deleteMessage(final String messageId) throws SQLException {
    final String methodName = IWebMessagingDAO.CNAME
            + "#deleteMessage(final String messageId) throws SQLException";

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

    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 removeSvcMessage(?)}");
        stmt.setString(1, messageId);

        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.intuit.it.billing.data.BillingDAOImpl.java

/**
 * PaymentInfoEFT//from ww  w.  j  a v a2s  .co m
 * 
 * <p/>
 * <p/>
 * <b>DATABASE PROCEDURE:</b>
 *  
 * @code
 *FUNCTION fn_get_payinfo_eft
 * (
 *    item_no      IN VARCHAR2
 *  )
 *  RETURN ref_cursor
 * @endcode
 * <p/>
 * <b>DATABASE RESULT SET:</b>
 * <ul>
 *    <li>BILLING_PROFILE_ID,</li>
 *    <li>BDOM,</li>
 *    <li>PAYINFO_NAME,</li>
 *    <li>BANK_ACCOUNT_NO,</li>
 *    <li>BANK_NUMBER,</li>
 *  </ul>
 *  
 * @param itemNo - The item number (e.g. P1-111)of the item we want payment info for
 * 
 * @return A single Payment Info record
 * 
 */
@Override
public PaymentInfoEFT getPayinfoEFT(String itemNo) throws JSONException {

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

    Connection conn = null;
    PaymentInfoEFT c = 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, itemNo);

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

        while (rs.next()) {

            c = new PaymentInfoEFT();
            c.setBillingProfile(rs.getString("BILLING_PROFILE_ID"));
            c.setProfileBdom(rs.getInt("BDOM"));
            c.setCustomerName(rs.getString("PAYINFO_NAME"));
            c.setBankAccountNumber(rs.getString("BANK_ACCOUNT_NO"));
            c.setBankNumber(rs.getString("BANK_NUMBER"));
        }

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

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

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

    return c;
}

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

/**
 * PaymentInfoCheck//ww  w. j  a  va2  s  .  co m
 * <p/>
 * <p/>
 * <b>DATABASE PROCEDURE:</b>
 *  
 * @code
 *FUNCTION fn_get_payinfo_check
 * (
 *    item_no      IN VARCHAR2
 *  )
 *  RETURN ref_cursor;
 * @endcode
 * <p/>
 * <b>DATABASE RESULT SET:</b>
 * <ul>
 *    <li>BILLING_PROFILE_ID,</li>
 *    <li>BDOM,</li>
 *    <li>PAYINFO_NAME,</li>
 *    <li>NET_TERMS,</li>
 *    <li>CHECK_NO,</li>
 *  </ul>
 *  
 * @param itemNo - The item number (e.g. P1-111)of the item we want payment info for
 * 
 * @return A single Payment Info record
 * 
 */
@Override
public PaymentInfoCheck getPayinfoCheck(String itemNo) throws JSONException {

    String query = "begin ? := billing_inquiry.fn_get_payinfo_check( ? ); end;"; // TODO: configure strings

    ResultSet rs = null;
    Connection conn = null;
    PaymentInfoCheck c = 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, itemNo);

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

        while (rs.next()) {

            c = new PaymentInfoCheck();
            c.setBillingProfile(rs.getString("BILLING_PROFILE_ID")); // TODO: configure strings
            c.setProfileBdom(rs.getInt("BDOM"));
            c.setCustomerName(rs.getString("PAYINFO_NAME"));
            c.setNetTerms(rs.getString("NET_TERMS"));
            c.setCheckNumber(rs.getString("CHECK_NO"));
        }

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

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

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

    return c;
}

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

/**
  * @section dao_section class BillingDAOImpl
 * getAllocation/*from  ww w  . j a va 2s .  c  o m*/
 * 
 * To be used in a caching method where we are pulling all of the allocations at once.  The way we can do this
 * is to merge a date range based set of billing history records with a date range set of allocations.
 * <p/>
 * <p/>
 * <b>DATABASE PROCEDURE:</b>
 *  
 * @code
 *     FUNCTION fn_get_item_allocations(
 *           item_no IN VARCHAR2 )
 *        RETURN ref_cursor;
 * @endcode
 * <p/>
 * <b>DATABASE RESULT SET:</b>
 * <ul>
 *    <li>ALLOCATION_DATE,</li>
 *    <li>ALLOCATION_T, </li>
 *    <li>ALLOCATION_AMT,</li>
 *    <li>AR_ITEM_NO, </li>
 *    <li>BILL_ITEM_NO, </li>
 *    <li>ITEM_DESCRIPTION, </li>
 *    <li>ITEM_CODE,</li> 
 *    <li>AR_ITEM_DATE, </li>
 *    <li>BILL_ITEM_DATE </li>
 *    <li>LICENSE</li>
 *  </ul>
 *  
 * @param customer  :  The Customer.accountNo account number of the customer who's allocations we need
 * @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
 * 
 * @return A list of Allocation objects. 
 * @throws JSONException 
 * 
 * 
 * 
 */
@Override
public List<Allocation> getAllocation(String itemNo) throws JSONException {

    List<Allocation> allocs = new ArrayList<Allocation>();

    String query = "begin ? := billing_inquiry.fn_get_item_allocations( ? ); end;"; //TODO: configure

    ResultSet rs = null;
    Connection conn = 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, itemNo);

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

        while (rs.next()) {

            Allocation a = new Allocation();
            a.setAllocatedFromItem(rs.getString("AR_ITEM_NO"));
            a.setAllocatedToItem(rs.getString("BILL_ITEM_NO"));
            a.setAllocationAmount(rs.getBigDecimal("ALLOCATION_AMT"));
            a.setAllocationDate(rs.getTimestamp("ALLOCATION_DATE"));
            a.setItemCode(rs.getString("ITEM_CODE"));
            a.setItemDescription(rs.getString("ITEM_DESCRIPTION"));
            a.setLicense(rs.getString("LICENSE"));
            allocs.add(a);
        }
        conn.close();
        rs.close();

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

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

    return allocs;
}

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

/**
 * PaymentInfoCC/* ww w.j a va 2s .  co m*/
 * 
 * <p/>
 * <p/>
 * <b>DATABASE PROCEDURE:</b>
 *  
 * @code
 *FUNCTION fn_get_payinfo_cc
 * (
 *    item_no      IN VARCHAR2
 *  )
 *  RETURN ref_cursor;
 * @endcode
 *  
 * <p/>
 * <b>DATABASE RESULT SET:</b>
 * <ul>
 *    <li>BILLING_PROFILE_ID,</li>
 *    <li>BDOM,</li>
 *    <li>PAYINFO_NAME,</li>
 *    <li>CC_NUMBER,</li>
 *    <li>EXPIRE_MM,</li>
 *    <li>EXPIRE_YYYY,</li>
 *  </ul>
 *  
 * @param itemNo - The item number (e.g. P1-111)of the item we want payment info for
 * 
 * @return A single Payment Info record
 * 
 * TODO 
 * Decide how to determine the card type from the token
 * 
 */
@Override
public PaymentInfoCC getPayinfoCC(String itemNo) throws JSONException {
    // TODO Auto-generated method stub

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

    Connection conn = null;
    ResultSet rs = null;
    PaymentInfoCC c = 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, itemNo);

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

        while (rs.next()) {

            c = new PaymentInfoCC();
            c.setBillingProfile(rs.getString("BILLING_PROFILE_ID"));
            c.setProfileBdom(rs.getInt("BDOM"));
            c.setCardholderName(rs.getString("PAYINFO_NAME"));
            c.setCcNumber(rs.getString("CC_NUMBER"));
            c.setExpiryMonth(rs.getString("EXPIRE_MM"));
            c.setExpiryYear(rs.getString("EXPIRE_YYYY"));
            c.setCardType("TODO");
        }

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

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

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

    return c;
}

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

/**
 * getAllocationsList/*w ww . java  2 s .co  m*/
 * 
 * To be used in a caching method where we are pulling all of the allocations at once.  The way we can do this
 * is to merge a date range based set of billing history records with a date range set of allocations.
 * <p/>
 * <p/>
 * <b>DATABASE PROCEDURE:</b>
 *  
 * @code
 *     FUNCTION fn_get_allocations(
 *           customer IN VARCHAR2,
 *           start_date IN DATE,
 *           end_date   IN DATE )
 *        RETURN ref_cursor;
 * @endcode
 * <p/>
 * <b>DATABASE RESULT SET:</b>
 * <ul>
 *    <li>ALLOCATION_DATE,</li>
 *    <li>ALLOCATION_T, </li>
 *    <li>ALLOCATION_AMT,</li>
 *    <li>AR_ITEM_NO, </li>
 *    <li>BILL_ITEM_NO, </li>
 *    <li>ITEM_DESCRIPTION, </li>
 *    <li>ITEM_CODE,</li> 
 *    <li>AR_ITEM_DATE, </li>
 *    <li>BILL_ITEM_DATE </li>
 *    <li>LICENSE</li>
 *  </ul>
 *  
 * @param customer  :  The Customer.accountNo account number of the customer who's allocations we need
 * @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
 * 
 * @return A list of Allocation objects. 
 * 
 */
@Override
public List<Allocation> getAllocationsList(String customer, Date startDate, Date endDate) throws JSONException {

    List<Allocation> allocs = new ArrayList<Allocation>();

    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_allocations( ?, ?, ? ); 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, customer);
        stmt.setDate(3, sqlStartDate);
        stmt.setDate(4, sqlEndDate);

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

        while (rs.next()) {

            Allocation a = new Allocation();
            a.setAllocatedFromItem(rs.getString("AR_ITEM_NO"));
            a.setAllocatedToItem(rs.getString("BILL_ITEM_NO"));
            a.setAllocationAmount(rs.getBigDecimal("ALLOCATION_AMT"));
            a.setAllocationDate(rs.getTimestamp("ALLOCATION_DATE"));
            a.setItemCode(rs.getString("ITEM_CODE"));
            a.setItemDescription(rs.getString("ITEM_DESCRIPTION"));
            a.setLicense(rs.getString("LICENSE"));
            allocs.add(a);
        }
        conn.close();
        rs.close();

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

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

    return allocs;
}

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

/**
 * getCustomerInfo//from   ww w  .j  av  a 2  s  .co  m
 * <p/>
 * <p/>
 * <b>DATABASE PROCEDURE:</b>
 *  
 * @code
 *  FUNCTION fn_get_customer
 *  (
 *    customer     IN VARCHAR2,
 *    bill_item_no IN VARCHAR2,
 *    order_no     IN VARCHAR2,
 *    pson         IN VARCHAR2
 *  )
 *  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 customer - The Customer.accountNo of the customer we want to find
 * @param billNo -  A bill number (e.g. B1-1111) OR bill line item number (e.g. B1-111,5) of the customer we want to find  - remove everything after the comma
 * @param orderNo - An order number (e.g. 200000011111) OR order line item number (e.g. 200000011111,5) of the customer we want to find - remove everything after the comma
 * @param pson - A BRM trans_id number (Paymenttech merchant number) of the payment event
 * 
 * @return A  Customer object
 * 
 */
@Override
public Customer getCustomerInfo(String customer, String billNo, String orderNo, String pson)
        throws JSONException {

    Customer c = null;

    String query = "begin ? := billing_inquiry.fn_get_customer( ?, ?, ?, ? ); 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, customer);
        stmt.setString(3, billNo);
        stmt.setString(4, orderNo);
        stmt.setString(5, pson);

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

        while (rs.next()) {

            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"));
            break;
        }
        conn.close();
        rs.close();

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

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

    return c;
}

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IWebMessagingDAO#updateMessage(String, List)
 *//*from   ww  w.ja v  a 2  s . co  m*/
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.intuit.it.billing.data.BillingDAOImpl.java

/**
 * getCustomerList//from  w w w  .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.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 ww .  j a v a2s  .c o  m
    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;
}