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.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   w ww  .j  a va 2s  .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   ww  w . j  av a  2 s.  com
@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();
            }
        }
    }
}

From source file:DAO.Poll_Tbl_pkg.Poll_TblJDBCTemplate.java

public int create2(int uid, String cid_json, String title, String description, String qtn_json, String ans_json,
        String poll_link, String start_ts, String end_ts, int reward, String poll_type) {
    System.out.println("reached create2");
    CallableStatement st;
    int pid = 0;/*from w  w w .  j a v a2s  . co m*/
    try {

        con = conn.getDataSource().getConnection();
        System.out.println("15 dec 10am");
        String sql = "{call createPoll2 (?, ? , ? , ? ,? ,? ,? ,? ,? ,? ,? ,?)}";
        st = con.prepareCall(sql);

        //Bind IN parameter first, then bind OUT parameter
        st.setInt(1, uid);
        st.setString(2, cid_json);
        st.setString(3, title);
        st.setString(4, description);
        st.setString(5, qtn_json);
        st.setString(6, ans_json);
        st.setString(7, poll_link);
        st.setString(8, start_ts);
        st.setString(9, end_ts);
        st.setInt(10, reward);
        st.setString(11, poll_type);
        st.registerOutParameter(12, java.sql.Types.INTEGER);

        //Use execute method to run stored procedure.
        System.out.println("Executing stored procedure...");
        st.execute();

        pid = st.getInt(12);
        System.out.println("PID mila balle balle" + pid);

        con.close();

        return pid;
    } catch (Exception e) {
        System.out.println("createPoll2 procedure error=" + e);
        return pid;
    }

}

From source file:com.cimmyt.model.dao.impl.AbstractDAO.java

public void callStoreProcedureAddFieldToTemplate(int idTemplateParams, int studytemplateid) {
    SessionImpl session = (SessionImpl) getHibernateTemplate().getSessionFactory().openSession();

    CallableStatement callableStatement = null;
    try {//from ww w  . ja va  2  s.c  o  m
        System.out.println("Session : " + session);
        callableStatement = session.connection().prepareCall("Call sp_add_fields_template_result(?,?)");
        System.out.println("Callable stament : " + callableStatement);
        callableStatement.setInt(1, idTemplateParams);
        callableStatement.setInt(2, studytemplateid);
        callableStatement.execute();
    } catch (HibernateException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IApplicationDataDAO#listApplications(int)
 *///from   w  w  w . j  av a2 s. co m
public synchronized List<String[]> listApplications(final int startRow) throws SQLException {
    final String methodName = IApplicationDataDAO.CNAME
            + "#listApplications(final int startRow) throws SQLException";

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

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String[]> responseData = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL listApplications(?, ?)}");
        stmt.setInt(1, startRow);
        stmt.registerOutParameter(2, Types.INTEGER);

        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<String[]>();

                while (resultSet.next()) {
                    String[] data = new String[] { resultSet.getString(1), // APPLICATION_GUID
                            resultSet.getString(2), // APPLICATION_NAME
                    };

                    if (DEBUG) {
                        DEBUGGER.debug("Value: {}", (Object[]) 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.cws.esolutions.core.dao.impl.ServiceDataDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServiceDataDAO#listServices(int)
 *///from   w  w w  . j  a  v a  2  s.c  o  m
public synchronized List<String[]> listServices(final int startRow) throws SQLException {
    final String methodName = IServiceDataDAO.CNAME + "#listServices(final int startRow) throws SQLException";

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

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String[]> responseData = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL listServices(?)}");
        stmt.setInt(1, 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<String[]>();

                while (resultSet.next()) {
                    String[] data = new String[] { resultSet.getString(1), // GUID
                            resultSet.getString(2), // SERVICE_TYPE
                            resultSet.getString(3), // NAME
                    };

                    responseData.add(data);
                }

                if (DEBUG) {
                    DEBUGGER.debug("List<String>: {}", responseData);
                }
            }
        }
    } catch (SQLException sqx) {
        ERROR_RECORDER.error(sqx.getMessage(), 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.cws.esolutions.security.dao.userauth.impl.SQLAuthenticator.java

/**
 * @see com.cws.esolutions.security.dao.userauth.interfaces.Authenticator#obtainSecurityData(java.lang.String, java.lang.String)
 *//* w w w .j  av a 2s  .  co 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.cws.esolutions.core.dao.impl.ServerDataDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServerDataDAO#listServers(int)
 *//* w  w  w.j  av a 2 s  .  c  om*/
public synchronized List<String[]> listServers(final int startRow) throws SQLException {
    final String methodName = IServerDataDAO.CNAME + "#listServers(final int startRow) throws SQLException";

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

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String[]> responseData = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL retrServerList(?)}");
        stmt.setInt(1, 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<String[]>();

                while (resultSet.next()) {
                    String[] serverData = new String[] { resultSet.getString(1), // T1.SYSTEM_GUID
                            resultSet.getString(2), // T1.SYSTEM_REGION
                            resultSet.getString(3), // T1.NETWORK_PARTITION
                            resultSet.getString(4), // T1.OPER_HOSTNAME
                            resultSet.getString(5), // T1.OWNING_DMGR
                            resultSet.getString(6), // T2.GUID
                            resultSet.getString(7) // T2.NAME
                    };

                    if (DEBUG) {
                        for (Object obj : serverData) {
                            DEBUGGER.debug("Value: {}", obj);
                        }
                    }

                    responseData.add(serverData);
                }

                if (DEBUG) {
                    for (Object[] objArr : responseData) {
                        for (Object obj : objArr) {
                            DEBUGGER.debug("Value: {}", obj);
                        }
                    }
                }
            }
        }
    } 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:es.emergya.bbdd.dao.RoutingHome.java

/**
 * Devuelve la lista de ids de la ruta desde vertice_origen a
 * vertice_destino.//www  .ja  v a2s  .  co  m
 * 
 * Utiliza la funcion shooting_star
 * 
 * @param origin
 * @param goal
 * @return
 */
@Transactional(readOnly = true, rollbackFor = Throwable.class)
private List<Long> shortest_path_shooting_star(final Long origin, final Long goal) {
    final List<Long> lista = new ArrayList<Long>();
    try {
        Session currentSession = getSession();
        CallableStatement consulta = currentSession.connection()
                .prepareCall("{call shortest_path_shooting_star(?,?,?,?,?)}");

        consulta.setString(1,
                "SELECT " + id + "::integer as id, " + source + "::integer as source, " + target
                        + "::integer as target, " + cost + " as cost," + reverse_cost + " as reverse_cost, "
                        + "ST_X(ST_StartPoint(" + the_geom + ")) as x1," + "ST_Y(ST_StartPoint(" + the_geom
                        + ")) as y1," + "ST_X(ST_EndPoint(" + the_geom + ")) as x2," + "ST_Y(ST_EndPoint("
                        + the_geom + ")) as y2," + rule + " as rule, " + to_cost + " as to_cost FROM " + table
        // + " order by " + id
        );
        consulta.setInt(2, origin.intValue());
        consulta.setInt(3, goal.intValue());
        consulta.setBoolean(4, true);
        consulta.setBoolean(5, true);
        log.trace(consulta);
        ResultSet resultado = consulta.executeQuery();

        while (resultado.next())
            lista.add(resultado.getLong("edge_id"));

    } catch (Exception e) {
        log.error("No se pudo calcular la ruta", e);
    }

    return lista;
}

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;//  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;
}