Example usage for java.sql CallableStatement close

List of usage examples for java.sql CallableStatement close

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

Introduction

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

Prototype

void close() throws SQLException;

Source Link

Document

Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed.

Usage

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

@Override
public Item addWatch(final Watch watch) {
    Item obj = null;/*from  w ww . ja  va2 s .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.cws.esolutions.security.dao.reference.impl.SecurityReferenceDAOImpl.java

/**
 * @see com.cws.esolutions.security.dao.reference.interfaces.ISecurityReferenceDAO#listAvailableServices()
 *//*  www .  ja  v  a  2  s .co  m*/
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.mimp.hibernate.HiberNna.java

public void crearExpNna2(ExpedienteNna temp) {

    Session session = sessionFactory.getCurrentSession();
    session.beginTransaction();/*from  w  w w .  jav a  2 s. co  m*/
    final ExpedienteNna expnna = temp;

    Work work = new Work() {
        @Override
        public void execute(Connection connection) throws SQLException {

            String hql = "{call HN_SAVE_EXP_NNA2(?,?,?)}";
            CallableStatement statement = connection.prepareCall(hql);
            statement.setLong(1, expnna.getNna().getIdnna());
            statement.setLong(2, expnna.getUnidad().getIdunidad());
            statement.setString(3, expnna.getNumero());

            statement.execute();
            statement.close();
        }
    };
    session.doWork(work);
}

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 a2 s.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.mobiaware.auction.data.impl.MySqlDataServiceImpl.java

@Override
public Item addBid(final Bid bid) {
    Item obj = null;/*  ww  w.j  a 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.cws.esolutions.security.dao.usermgmt.impl.SQLUserManager.java

/**
 * @see com.cws.esolutions.security.dao.usermgmt.interfaces.UserManager#removeUserAccount(java.lang.String)
 */// ww  w  .  j  a va  2 s.c  om
public synchronized boolean removeUserAccount(final String userId) throws UserManagementException {
    final String methodName = SQLUserManager.CNAME
            + "#removeUserAccount(final String userId) throws UserManagementException";

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

    Connection sqlConn = null;
    boolean isComplete = false;
    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 removeUserAccount(?) }");
        stmt.setString(1, userId);

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

        if (!(stmt.execute())) {
            isComplete = true;
        }
    } catch (SQLException sqx) {
        throw new UserManagementException(sqx.getMessage(), sqx);
    } finally {
        try {
            if (stmt != null) {
                stmt.close();
            }

            if (!(sqlConn == null) && (!(sqlConn.isClosed()))) {
                sqlConn.close();
            }
        } catch (SQLException sqx) {
            throw new UserManagementException(sqx.getMessage(), sqx);
        }
    }

    return isComplete;
}

From source file:com.cws.esolutions.security.dao.userauth.impl.SQLAuthenticator.java

/**
 * @see com.cws.esolutions.security.dao.userauth.interfaces.Authenticator#obtainSecurityData(java.lang.String, java.lang.String)
 *///from   ww  w .  ja  va2 s. c o  m
public synchronized List<String> obtainSecurityData(final String userName, final String userGuid)
        throws AuthenticatorException {
    final String methodName = SQLAuthenticator.CNAME
            + "#obtainSecurityData(final String userName, final String userGuid) throws AuthenticatorException";

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

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

    try {
        sqlConn = SQLAuthenticator.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, userName); // guid
        stmt.setInt(2, 0); // count

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

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

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

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

                while (resultSet.next()) {
                    if (StringUtils.equals(resultSet.getString(2), userName)) {
                        String cn = resultSet.getString(1);
                        String username = resultSet.getString(2);

                        if (DEBUG) {
                            DEBUGGER.debug("String: {}", cn);
                            DEBUGGER.debug("String: {}", username);
                        }

                        resultSet.close();
                        stmt.close();

                        // found the user we want
                        stmt = sqlConn.prepareCall("{ CALL getSecurityQuestions(?, ?) }");
                        stmt.setString(1, username); // common name
                        stmt.setString(2, cn);

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

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

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

                            if (resultSet.next()) {
                                userSecurity = new ArrayList<String>(
                                        Arrays.asList(resultSet.getString(1), resultSet.getString(2)));

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

            }
        }
    } catch (SQLException sqx) {
        throw new AuthenticatorException(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 AuthenticatorException(sqx.getMessage(), sqx);
        }
    }

    return userSecurity;
}

From source file:com.mimp.hibernate.HiberNna.java

public void crearInforme(InformeNna tempInf, Long idExp) {

    Session session = sessionFactory.getCurrentSession();
    session.beginTransaction();/*  w w w  . j  a va 2 s  .  co m*/

    final InformeNna inf = tempInf;
    final Long id = idExp;

    Work work = new Work() {
        @Override
        public void execute(Connection connection) throws SQLException {

            String hql = "{call HN_INSERT_INF(?,?,?,?,?)}";
            CallableStatement statement = connection.prepareCall(hql);
            statement.setLong(1, id);
            statement.setString(2, inf.getNumero());
            statement.setDate(3, (java.sql.Date) inf.getFecha());
            statement.setString(4, inf.getResultado());
            statement.setString(5, inf.getObservaciones());

            statement.execute();
            statement.close();
        }
    };

    session.doWork(work);

}

From source file:com.mimp.hibernate.HiberNna.java

public String get_Last_numero_expediente() {
    Session session = sessionFactory.getCurrentSession();
    Work work;//  www.ja  v a2  s .  c  o m
    work = new Work() {
        @Override
        public void execute(Connection connection) throws SQLException {
            numero_last = null;
            ResultSet temp_numero;
            String hql = "{call HN_GET_LAST_EXPEDIENTE_NNA(?)}";
            CallableStatement statement = connection.prepareCall(hql);
            statement.registerOutParameter(1, OracleTypes.CURSOR);
            statement.execute();

            temp_numero = (ResultSet) statement.getObject(1);
            while (temp_numero.next()) {
                numero_last = temp_numero.getString(2);
            }
            temp_numero.close();
            statement.close();
        }
    };
    session.doWork(work);

    return numero_last;
}

From source file:com.mimp.hibernate.HiberNna.java

public void updateInforme(InformeNna temp) {

    Session session = sessionFactory.getCurrentSession();
    session.beginTransaction();/*from ww w.java2s.  co  m*/

    final InformeNna inf = temp;

    Work work = new Work() {
        @Override
        public void execute(Connection connection) throws SQLException {

            String hql = "{call HN_UPDATE_INF(?,?,?,?,?)}";
            CallableStatement statement = connection.prepareCall(hql);
            statement.setLong(1, inf.getIdinformeNna());
            statement.setString(2, inf.getNumero());
            statement.setDate(3, (java.sql.Date) inf.getFecha());
            statement.setString(4, inf.getResultado());
            statement.setString(5, inf.getObservaciones());

            statement.execute();
            statement.close();
        }
    };

    session.doWork(work);

}