Example usage for java.sql CallableStatement setString

List of usage examples for java.sql CallableStatement setString

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. CallableStatement
  6. 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.mobilewallet.users.dao.PushNotificationsDAO.java

public int updateNotification(long userId, String status, String type) {
    int updated = 0;
    Connection connection = null;
    CallableStatement cstmt = null;
    try {/*from www.j a  v  a  2s. co m*/
        connection = dataSource.getConnection();
        cstmt = connection.prepareCall("{call UPDATE_PUSH_NOTIFICATIONS(?,?,?,?)}");
        cstmt.setLong(1, userId);
        cstmt.setString(2, status);
        cstmt.setString(3, type);
        cstmt.registerOutParameter(4, java.sql.Types.INTEGER);
        cstmt.execute();

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

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

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

        }
    }
    return updated;
}

From source file:com.mobilewallet.users.dao.NotificationsDAO.java

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

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

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

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

        }
    }
    return updated;
}

From source file:DbServletTrans.java

public void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, java.io.IOException {

    Connection conn = null;//w  w  w  .  ja va2s  . co m
    Statement stmt = null;

    response.setContentType("text/html");
    java.io.PrintWriter out = response.getWriter();
    out.println("<html><head><title>Using transactions</title></head><body>");
    out.println("<h2>These SQL statements are part of a transaction</h2>");
    out.println("CallableStatement.executeUpdate()");
    out.println("<br><br>");
    out.println("Statement.executeUpdate()");
    out.println("<br><br>");

    try {

        conn = pool.getConnection();

        out.println("AutoCommit before setAutoCommit(): " + conn.getAutoCommit() + "<br><br>");

        out.println("Transaction isolation level: ");

        switch (conn.getTransactionIsolation()) {

        case 0:
            out.println("TRANSACTION_NONE<br><br>");
            break;
        case 1:
            out.println("TRANSACTION_READ_UNCOMMITTED<br><br>");
            break;
        case 2:
            out.println("TRANSACTION_READ_COMMITTED<br><br>");
            break;
        case 4:
            out.println("TRANSACTION_REPEATABLE_READ<br><br>");
            break;
        case 8:
            out.println("TRANSACTION_SERIALIZABLE<br><br>");
            break;
        default:
            out.println("UNKNOWN<br><br>");

        }
        conn.setAutoCommit(false);

        CallableStatement cs = null;

        //Create an instance of the CallableStatement
        cs = conn.prepareCall("{call addEvent (?,?,?)}");

        cs.setString(1, "Salisbury Beach 5-Miler");
        cs.setString(2, "Salisbury MA");
        cs.setString(3, "14-Aug-2003");

        //Call the inherited PreparedStatement.executeUpdate() method
        cs.executeUpdate();

        String sql = "update raceevent set racedate='13-Aug-2003' " + "where name='Salisbury Beach 5-Miler'";

        int res = 0;

        stmt = conn.createStatement();

        res = stmt.executeUpdate(sql);

        //commit the two SQL statements
        conn.commit();

    } catch (Exception e) {

        try {
            //rollback the transaction in case of a problem
            conn.rollback();

        } catch (SQLException sqle) {
        }

        throw new ServletException(e.getMessage());

    } finally {

        try {

            if (stmt != null)
                stmt.close();

            if (conn != null)
                conn.close();//this returns the Connection to the
                             // Connection pool

        } catch (SQLException sqle) {
        }

    }
    out.println("</table></body></html>");
    out.close();

}

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 {/*from w w  w. j a v a2  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.mobilewallet.common.dao.ForgotPasswordDAO.java

public int checkResetLink(String uuid, String userId, String ip) {
    Connection connection = null;
    CallableStatement cstmt = null;
    int rvalue = -1;
    try {//from   w ww. java2 s .c o 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.ing.connector.newbus.WPendingOverridesImpl.java

/**
 * This method will query the database and create an Odd string that
 * represents the collection of agent overrides.
 *//*from   www  . j  a v  a2 s. co  m*/
public WResult getObject(WObjectKeys keys) {
    String policyId = keys.getStringKey(1);
    String agentSystemSource = keys.getStringKey(2); //JFG2.0
    String companyId = keys.getStringKey(3); //JFG4.0
    String agentParm = keys.getStringKey(4); //CSST400
    String driverSystemSource = getDriver().getSystemSource(); //JFG2.0
    String driverTableType = getDriver().getTableType(); //DIL3.0
    String databaseName = "scldbali"; //DIL3.0
    String procedureName = null; //DIL3.0
    String userType = keys.getStringKey(5);
    String agentName;

    com.ing.connector.Registrar.logInfoMessage("wpendingoverridesimpl - userType" + userType);

    WResult result = new WResult();

    WCollection collection = new WCollection("com.ing.connector.model.WAgentOverrideExt");

    result.setModelObject(collection);

    if (!agentSystemSource.equals(driverSystemSource)) //JFG2.0
    {
        result.setStatus(WResult.OBJECT_NOT_FOUND);
        return result;
    }

    if (userType != null && userType.equalsIgnoreCase("internalUser"))
        databaseName = LookUp.getInstance().getStringProperty("Connector.History.Database");
    ;

    // March 2004 deleted int connNo = line

    Connection con = getConnection();
    // March 2004 removed param

    if (driverTableType.equals(TABLETYPECOPY)) //DIL3.0
    { //DIL3.0

        procedureName = "bali_WAgentOverrides_get_p"; //DIL3.0
    } //DIL3.0
    else //DIL3.0
    { //DIL3.0
        //databaseName = "scldpt";                        //RL-004454 RSD 

        procedureName = "bali_WAgentOverridesImpl"; //DIL3.0
    } //DIL3.0
    com.ing.connector.Registrar.logInfoMessage("wpendingoverridesimpl - databaseName" + databaseName);
    com.ing.connector.Registrar.logInfoMessage("wpendingoverridesimpl - procedureName" + procedureName);
    try {
        Statement stmt = con.createStatement();
        //DIL3.0            stmt.execute("use " + "scldpt");
        stmt.execute("use " + databaseName);
        Registrar.logInfoMessage("exec " + procedureName + " " + policyId + "," + agentParm);
        //DIL3.0            CallableStatement cstmt = con.prepareCall("{call bali_WAgentOverridesImpl(?)}");
        CallableStatement cstmt = con.prepareCall("{call " + procedureName + "(?,?)}"); //CSST400
        cstmt.setString(1, policyId);
        cstmt.setString(2, agentParm); //CSST400

        ResultSet rs = cstmt.executeQuery();

        WDateIsoFormat isoFormat = new WDateIsoFormat();

        while (rs.next()) {

            WAgentOverrideExt single = new WAgentOverrideExt(getDriverName(), getImplTypeName());

            single.setAgentId(rs.getString("agent_number"));
            agentName = new String(rs.getString("agent_name"));
            //RL-009402 - SXL - START
            String formattedAgentName = WStringUtil.formatAgentName(agentName);

            single.setFormattedAgentName(formattedAgentName);

            if (StringUtils.countMatches(agentName, ",") == 2) {
                String lastName = agentName.substring(0, agentName.indexOf(",")).trim();
                String firstName = (agentName.substring(agentName.indexOf(",") + 1, agentName.lastIndexOf(",")))
                        .trim();
                String middleName = (agentName.substring(agentName.lastIndexOf(",") + 1)).trim();
                single.setLastName(lastName);
                single.setFirstName(firstName);
                single.setMiddleName(middleName);
            } else {
                //RL-009402 - SXL - END  
                int i = agentName.indexOf(',');
                if (i != -1) {
                    single.setLastName(agentName.substring(0, i).trim());
                    single.setFirstName(agentName.substring(i + 1).trim());
                } else {
                    single.setLastName(agentName);
                }
            }

            single.setOverridePercent(rs.getDouble("commission_split"));
            single.setContractType(rs.getString("agent_contract_typ"));
            single.setOverrideId(rs.getString("ovr_agent_number")); //CSST400
            single.setOverrideAgentName(rs.getString("ovr_agent_name")); //   KLN/02/13/07//RL-001655    
            single.setLevel(rs.getString("Hierarchy_Type")); //RL-005642 LXJ added
            collection.addElement(single);
        }

        rs.close();
        cstmt.close();
        stmt.close();

        result.setModelObject(collection);

        if (collection.size() == 0) {
            result.setStatus(WResult.OBJECT_NOT_FOUND);
        }

    } catch (SQLException sqle) {
        result.setException(sqle);
        com.ing.connector.Registrar.logError(getClass().getName() + ": " + sqle.getMessage());
    } catch (Exception exc) {
        result.setException(exc);
        com.ing.connector.Registrar.logError(getClass().getName() + ": " + exc.getMessage());
    }

    releaseConnection(con);
    //March 2004 changed param from connNo to con

    return result;

}

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. ja  v a 2 s .  c om*/
    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:es.emergya.bbdd.dao.RoutingHome.java

/**
 * Devuelve la lista de ids de la ruta desde vertice_origen a
 * vertice_destino//from ww  w  .  j ava  2  s . c o  m
 * 
 * @param origin
 * @param goal
 * @return
 */
@Transactional(readOnly = true, rollbackFor = Throwable.class)
private List<Long> getSimpleGid(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(?,?,?,?,?)}");
        consulta.setString(1, "SELECT id, " + source + "::int4, " + target + "::int4, " + "ST_length2d("
                + the_geom + ")::float8 as cost FROM " + table);
        consulta.setLong(2, origin);
        consulta.setLong(3, goal);
        consulta.setBoolean(4, false);
        consulta.setBoolean(5, false);
        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.mobilewallet.common.dao.ForgotPasswordDAO.java

public Object[] getResetPasswordLink(String email, String uuid, String ip) {
    Connection connection = null;
    CallableStatement cstmt = null;
    Object[] obj = null;/* w  w w .  j  av a 2  s .co  m*/
    int rvalue = -1;
    long userId = 0;
    try {
        connection = dataSource.getConnection();
        cstmt = connection.prepareCall("{call wp_forgot_pwd_reset_link(?,?,?,?,?)}");
        cstmt.setString(1, email);
        cstmt.setString(2, uuid);
        cstmt.setString(3, ip);
        cstmt.registerOutParameter(4, java.sql.Types.INTEGER);
        cstmt.registerOutParameter(5, java.sql.Types.INTEGER);

        cstmt.execute();

        rvalue = cstmt.getInt(4);
        userId = cstmt.getLong(5);

        obj = new Object[2];
        obj[0] = rvalue;
        obj[1] = userId;
    } 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 obj;
}

From source file:com.oracle.tutorial.jdbc.StoredProcedureMySQLSample.java

public void runStoredProcedures(String coffeeNameArg, float maximumPercentageArg, float newPriceArg)
        throws SQLException {
    CallableStatement cs = null;

    try {//from  w  ww .ja  va 2s. com

        System.out.println("\nCalling the procedure GET_SUPPLIER_OF_COFFEE");
        cs = this.con.prepareCall("{call GET_SUPPLIER_OF_COFFEE(?, ?)}");
        cs.setString(1, coffeeNameArg);
        cs.registerOutParameter(2, Types.VARCHAR);
        cs.executeQuery();

        String supplierName = cs.getString(2);

        if (supplierName != null) {
            System.out.println("\nSupplier of the coffee " + coffeeNameArg + ": " + supplierName);
        } else {
            System.out.println("\nUnable to find the coffee " + coffeeNameArg);
        }

        System.out.println("\nCalling the procedure SHOW_SUPPLIERS");
        cs = this.con.prepareCall("{call SHOW_SUPPLIERS}");
        ResultSet rs = cs.executeQuery();

        while (rs.next()) {
            String supplier = rs.getString("SUP_NAME");
            String coffee = rs.getString("COF_NAME");
            System.out.println(supplier + ": " + coffee);
        }

        System.out.println("\nContents of COFFEES table before calling RAISE_PRICE:");
        CoffeesTable.viewTable(this.con);

        System.out.println("\nCalling the procedure RAISE_PRICE");
        cs = this.con.prepareCall("{call RAISE_PRICE(?,?,?)}");
        cs.setString(1, coffeeNameArg);
        cs.setFloat(2, maximumPercentageArg);
        cs.registerOutParameter(3, Types.NUMERIC);
        cs.setFloat(3, newPriceArg);

        cs.execute();

        System.out.println("\nValue of newPrice after calling RAISE_PRICE: " + cs.getFloat(3));

        System.out.println("\nContents of COFFEES table after calling RAISE_PRICE:");
        CoffeesTable.viewTable(this.con);

    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (cs != null) {
            cs.close();
        }
    }
}