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.aw.core.dao.DAOSql.java

/**
 * Call example/*  ww  w  .j  av a  2 s  .  c om*/
 * execSqlFunction(sqlUpdSqldoActor, Types.NUMERIC, new Object[]{"dss"})
 */
public boolean execSqlProcedure(String sql, Object[] sqlParams) {
    try {
        CallableStatement cstmt = getHibernateConnection().prepareCall(sql);
        if (sqlParams != null)
            for (int i = 0; i < sqlParams.length; i++) {
                cstmt.setObject(i + 1, sqlParams[i]);
            }
        logger.debug("SQL Exec:" + buildSQL(sql, sqlParams));
        boolean result = cstmt.execute();
        cstmt.close();
        return result;
    } catch (SQLException e) {
        logger.error("SQL:" + buildSQL(sql, sqlParams), e);
        throw AWBusinessException.wrapUnhandledException(logger, e);
    }
}

From source file:com.mobilewallet.common.dao.LoginDAO.java

public String getUserPassword(String email, String ip) {
    Connection connection = null;
    CallableStatement cstmt = null;
    String password = null;/*from   w  w  w  .  j  av  a 2s . c o m*/
    try {
        connection = dataSource.getConnection();
        cstmt = connection.prepareCall("{call fp_forgot_password(?,?,?)}");
        cstmt.setString(1, email);
        cstmt.setString(2, ip);
        cstmt.registerOutParameter(3, java.sql.Types.VARCHAR);

        cstmt.execute();

        password = cstmt.getString(3);
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {

        try {
            if (cstmt != null) {
                cstmt.close();
            }
        } catch (Exception ex) {

        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (Exception ex) {

        }
    }
    return password;
}

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

public ArrayList<Object> usuario2(String user, String pass) {

    org.hibernate.Session session = sessionFactory.getCurrentSession();

    final String usuario = user;
    final String password = pass;

    Work work = new Work() {
        @Override//from   w w  w .  j a v  a2s.  c  om
        public void execute(Connection connection) throws SQLException {
            String query = "{call CONTRASENA(?, ?, ?, ?)}";
            CallableStatement statement = connection.prepareCall(query);
            statement.setString(1, usuario);
            statement.setString(2, password);
            statement.registerOutParameter(3, java.sql.Types.VARCHAR);
            statement.registerOutParameter(4, java.sql.Types.VARCHAR);
            statement.execute();

            String correo = statement.getString(3);
            String mensaje = statement.getString(4);
            temp.add(0, correo);
            temp.add(1, mensaje);
            statement.close();
        }
    };

    session.doWork(work);

    return temp;
}

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  ww .  j  a  v a2s  .c om
 * @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:com.mobilewallet.common.dao.ForgotPasswordDAO.java

public int resetPassword(String uuid, String userId, String password, String ip) {
    Connection connection = null;
    CallableStatement cstmt = null;
    int rvalue = -1;
    try {/*  w w  w.j  ava2  s. co  m*/
        connection = dataSource.getConnection();
        cstmt = connection.prepareCall("{call wp_reset_pwd(?,?,?,?,?)}");
        cstmt.setString(1, userId);
        cstmt.setString(2, uuid);
        cstmt.setString(3, password);
        cstmt.setString(4, ip);
        cstmt.registerOutParameter(5, java.sql.Types.INTEGER);

        cstmt.execute();

        rvalue = cstmt.getInt(5);

    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {

        try {
            if (cstmt != null) {
                cstmt.close();
            }
        } catch (Exception ex) {

        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (Exception ex) {

        }
    }
    return rvalue;
}

From source file:com.aw.core.dao.DAOSql.java

protected void dbmsOutputPrint(Connection conn, StringBuffer buf) throws java.sql.SQLException {
    String getLineSql = "begin dbms_output.get_line(?,?); end;";
    CallableStatement stmt = conn.prepareCall(getLineSql);
    boolean hasMore = true;
    stmt.registerOutParameter(1, Types.VARCHAR);
    stmt.registerOutParameter(2, Types.INTEGER);
    while (hasMore) {
        boolean status = stmt.execute();
        hasMore = (stmt.getInt(2) == 0);
        if (hasMore) {
            buf.append(stmt.getString(1)).append("\n");
        }//from w  w  w .  ja  v  a2  s. co m
    }
    stmt.close();
}

From source file:com.mobilewallet.common.dao.ForgotPasswordDAO.java

public int checkResetLink(String uuid, String userId, String ip) {
    Connection connection = null;
    CallableStatement cstmt = null;
    int rvalue = -1;
    try {//w ww.j  a va 2s. co  m
        connection = dataSource.getConnection();
        cstmt = connection.prepareCall("{call wp_check_reset_link(?,?,?,?)}");
        cstmt.setString(1, userId);
        cstmt.setString(2, uuid);
        cstmt.setString(3, ip);
        cstmt.registerOutParameter(4, java.sql.Types.INTEGER);

        cstmt.execute();

        rvalue = cstmt.getInt(4);
        log.info("Rvalue Check ResetLink : " + rvalue);

    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {

        try {
            if (cstmt != null) {
                cstmt.close();
            }
        } catch (Exception ex) {

        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (Exception ex) {

        }
    }
    return rvalue;
}

From source file:com.aw.core.dao.DAOSql.java

/**
 * Call example/*from  ww  w  .j ava 2  s  .com*/
 * execSqlFunction(sqlUpdSqldoActor, Types.NUMERIC, new Object[]{"dss"})
 */
public Object execSqlFunction(String sql, int returnSqlType, Object[] sqlParams) {
    try {
        CallableStatement cstmt = getHibernateConnection().prepareCall(sql);
        cstmt.registerOutParameter(1, returnSqlType);
        if (sqlParams != null)
            for (int i = 0; i < sqlParams.length; i++) {
                cstmt.setObject(i + 2, sqlParams[i]);
            }
        logger.debug("SQL Exec:" + buildSQL(sql, sqlParams));
        cstmt.execute();
        Object returnValue = cstmt.getObject(1);
        cstmt.close();
        return returnValue;
    } catch (SQLException e) {
        logger.error("SQL:" + buildSQL(sql, sqlParams), e);
        throw AWBusinessException.wrapUnhandledException(logger, e);
    }
}

From source file:com.mobilewallet.credits.dao.CreditsDAO.java

public int updateCredits(long userId, String isCorrect, int position) {
    int updated = 0;
    Connection connection = null;
    CallableStatement cstmt = null;
    try {//from ww w  .j a v a  2  s.  c  o  m
        connection = dataSource.getConnection();
        cstmt = connection.prepareCall("{call update_credits_proc(?,?,?,?)}");
        cstmt.setLong(1, userId);
        cstmt.setString(2, isCorrect);
        cstmt.setInt(3, position);
        cstmt.registerOutParameter(4, java.sql.Types.INTEGER);
        cstmt.execute();

        updated = cstmt.getInt(4);
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {

        try {
            if (cstmt != null) {
                cstmt.close();
            }
        } catch (Exception ex) {

        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (Exception ex) {

        }
    }
    return updated;
}

From source file:eionet.cr.util.virtuoso.VirtuosoJdbcDriverTest.java

/**
 * Test if CR uses correct Virtuoso JDBC driver. It shouldn't get "Too many open statements" error.
 *
 * @throws SQLException When problem with connecting to Virtuoso.
 *//*from   w w w  . j  a  v a2s .c  o  m*/
@Test
public void testTooManyOpenStmts() throws SQLException {

    VirtuosoConnectionPoolDataSource dbsource = new VirtuosoConnectionPoolDataSource();

    String testDbURI = GeneralConfig.getRequiredProperty(GeneralConfig.VIRTUOSO_DB_URL);
    URI uri = URI.create(testDbURI.substring(5));

    dbsource.setServerName(uri.getHost());
    dbsource.setPortNumber(uri.getPort());
    dbsource.setPassword(GeneralConfig.getRequiredProperty(GeneralConfig.VIRTUOSO_DB_PWD));
    dbsource.setUser(GeneralConfig.getRequiredProperty(GeneralConfig.VIRTUOSO_DB_USR));
    dbsource.setCharset("UTF-8");
    VirtuosoPooledConnection pooledConnection = (VirtuosoPooledConnection) dbsource.getPooledConnection();
    virtuoso.jdbc4.VirtuosoConnection con = pooledConnection.getVirtuosoConnection();
    String jdbcComp = "DB.DBA.TTLP (?, ?, ?, ?)";
    CallableStatement stmt = null;
    int MAXIT = 10000;
    for (int i = 0; i < MAXIT; i++) {
        try {
            stmt = con.prepareCall(jdbcComp);
            stmt.setString(1, "");
            stmt.setString(2, "");
            stmt.setString(3, DUMMY_GRAPH_URI);
            stmt.setInt(4, 256);
            stmt.execute();
            con.commit();
        } catch (Exception e) {
            e.printStackTrace();
            fail("can't add data to virtuoso. ");
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
    }
}