Example usage for java.sql Connection prepareStatement

List of usage examples for java.sql Connection prepareStatement

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. prepareStatement

Introduction

In this page you can find the example usage for java.sql Connection prepareStatement.

Prototype

PreparedStatement prepareStatement(String sql) throws SQLException;

Source Link

Document

Creates a PreparedStatement object for sending parameterized SQL statements to the database.

Usage

From source file:dsd.dao.DAOProvider.java

/**
 * This is the secure Version of SelectTable. To use this function in the
 * right way, you use ? instead of parameters and give the parameters
 * instead as string into the parameter-array.
 * /*ww  w. j  a  v  a2s.com*/
 * For Example: Instead of : "SELECT * FROM tb1 WHERE user= 'foo' and name
 * ='bar' use "SELECT * FROM tb1 WHERE user= ? and name = ?" and as
 * parameters[0]='foo' and parameters[1]='bar'
 * 
 * The order in the parameters are important!!
 * 
 * @param table
 * @param select
 * @param where
 * @param order
 * @param con
 * @param parameters
 * @return
 * @throws SQLException
 */
public static ResultSet SelectTableSecure(String table, String select, String where, String order,
        Connection con, Object[] parameters) throws SQLException {
    ResultSet resultSet = null;
    try {
        PreparedStatement command = con.prepareStatement(String.format("select %s from %s %s %s", select, table,
                (where.trim().equals("") ? "" : "where " + where),
                (order.trim().equals("") ? "" : "order by " + order)));
        if (parameters != null) {
            for (int i = 0; i < parameters.length; i++) {
                SetParameter(command, parameters[i], i + 1);
            }
        }
        resultSet = command.executeQuery();
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return resultSet;
}

From source file:com.keybox.manage.db.SystemStatusDB.java

/**
 * returns key placement status of system
 *
 * @param systemId system id/*from   ww  w .jav  a  2s  .  c o  m*/
 * @param userId user id
 */
public static HostSystem getSystemStatus(Long systemId, Long userId) {

    Connection con = null;
    HostSystem hostSystem = null;
    try {
        con = DBUtils.getConn();

        PreparedStatement stmt = con.prepareStatement("select * from status where id=? and user_id=?");
        stmt.setLong(1, systemId);
        stmt.setLong(2, userId);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            hostSystem = SystemDB.getSystem(con, rs.getLong("id"));
            hostSystem.setStatusCd(rs.getString(STATUS_CD));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);
    return hostSystem;

}

From source file:com.wso2telco.util.DbUtil.java

public static void updateMultiplePasswordNoOfAttempts(String username, int attempts)
        throws SQLException, AuthenticatorException {

    Connection connection = null;
    PreparedStatement ps = null;//  w  ww. ja va2s  . c o m

    String sql = "update `multiplepasswords` set  attempts=? where  username=?;";

    connection = getConnectDBConnection();

    ps = connection.prepareStatement(sql);

    ps.setInt(1, attempts);
    ps.setString(2, username);
    ps.execute();

    if (connection != null) {
        connection.close();
    }
}

From source file:Emporium.Controle.ContrVpne.java

public static ArrayList<Vpne> listaVpne(String where, String nomeBD, int idCli) {

    String sql = "SELECT * FROM vpne WHERE idCliente = " + idCli + " ";
    sql = sql + where + " ;";
    Connection conn = Conexao.conectar(nomeBD);
    ArrayList<Vpne> listaVpne = new ArrayList<Vpne>();

    try {//www.  j  a  v  a  2s.  c  o m
        PreparedStatement valores = conn.prepareStatement(sql);
        valores.executeQuery();
        ResultSet result = (ResultSet) valores.executeQuery();

        while (result.next()) {

            int idCliente = result.getInt("idCliente");
            int idDepartamento = result.getInt("idDepartamento");
            String nomeDepto = result.getString("nomeDepartamento");

            String sro = result.getString("sro");
            String descricao = result.getString("descricao");
            String valor = result.getString("valor");

            String remetente = result.getString("remetente");
            String cnpj_remetente = result.getString("cnpj_remetente");
            String rlogradouro = result.getString("rlogradouro");
            String rnumero = result.getString("rnumero");
            String rbairro = result.getString("rbairro");
            String rcidade = result.getString("rcidade");
            String ruf = result.getString("ruf");

            String destinatario = result.getString("destinatario");
            String cpf_cnpj_dest = result.getString("cpf_cnpj_dest");
            String dlogradouro = result.getString("dlogradouro");
            String dnumero = result.getString("dnumero");
            String dbairro = result.getString("dbairro");
            String dcidade = result.getString("dcidade");
            String dcep = result.getString("dcep");
            String duf = result.getString("duf");

            String data = result.getString("data");

            Destinatario remVpne = new Destinatario(remetente, cnpj_remetente, rlogradouro, rnumero, rbairro,
                    rcidade, ruf);
            Destinatario destVpne = new Destinatario(destinatario, cpf_cnpj_dest, dlogradouro, dnumero, dbairro,
                    dcidade, dcep, duf);

            Vpne vp = new Vpne(sro, descricao, valor, idCliente, idDepartamento, nomeDepto, data, remVpne,
                    destVpne);
            listaVpne.add(vp);
        }
        valores.close();
        return listaVpne;
    } catch (SQLException e) {
        Logger.getLogger(ContrVpne.class.getName()).log(Level.WARNING, e.getMessage(), e);
        return listaVpne;
    } finally {
        Conexao.desconectar(conn);
    }
}

From source file:com.l2jfree.gameserver.util.IdFactory.java

private static void removeExpired() {
    int removed = 0;

    Connection con = null;
    try {//from w ww  .j av a 2 s  . co m
        con = L2Database.getConnection();

        for (String query : REMOVE_EXPIRED_QUERIES) {
            final PreparedStatement ps = con.prepareStatement(query);
            ps.setLong(1, System.currentTimeMillis());

            removed += ps.executeUpdate();

            ps.close();
        }
    } catch (SQLException e) {
        _log.warn("", e);
    } finally {
        L2Database.close(con);
    }

    _log.info("IdFactory: Removed " + removed + " expired entries from database.");
}

From source file:com.silverpeas.notation.model.RatingDAO.java

private static void populateRatings(Connection con, Map<String, ContributionRating> ratings,
        String componentInstanceId, String contributionType, Collection<String> contributionIds)
        throws SQLException {
    PreparedStatement prepStmt = con.prepareStatement(
            QUERY_GET_RATINGS.replaceAll("@ids@", "'" + StringUtils.join(contributionIds, "','") + "'"));
    prepStmt.setString(1, componentInstanceId);
    prepStmt.setString(2, contributionType);
    ResultSet rs = null;/*  w  w  w.java 2s  .com*/
    try {
        rs = prepStmt.executeQuery();
        while (rs.next()) {
            RatingRow current = resultSet2RatingRow(rs);
            ContributionRating contributionRating = ratings.get(current.getContributionId());
            if (contributionRating == null) {
                contributionRating = new ContributionRating(new ContributionRatingPK(
                        current.getContributionId(), componentInstanceId, contributionType));
                ratings.put(contributionRating.getContributionId(), contributionRating);
            }
            contributionRating.addRaterRating(current.getRaterId(), current.getRating());
        }
    } finally {
        DBUtil.close(rs, prepStmt);
    }
}

From source file:com.wso2telco.util.DbUtil.java

public static void incrementSuccessPinAttempts(String sessionId) throws SQLException, AuthenticatorException {

    Connection connection = null;
    PreparedStatement ps = null;//from w ww  .  j a v  a 2s. c o m

    String sql = "update multiplepasswords set attempts=attempts +1 where ussdsessionid = ?;";

    connection = getConnectDBConnection();

    ps = connection.prepareStatement(sql);

    ps.setString(1, sessionId);
    ps.execute();

    if (connection != null) {
        connection.close();
    }
}

From source file:fll.db.NonNumericNominees.java

/**
 * Get all nominees in the specified category.
 * //w ww  .  j  av a2s.  co  m
 * @throws SQLException
 */
public static Set<Integer> getNominees(final Connection connection, final int tournamentId,
        final String category) throws SQLException {
    final Set<Integer> result = new HashSet<>();
    PreparedStatement get = null;
    ResultSet rs = null;
    try {
        get = connection.prepareStatement(
                "SELECT DISTINCT team_number FROM non_numeric_nominees" + " WHERE tournament = ?" //
                        + " AND category = ?");
        get.setInt(1, tournamentId);
        get.setString(2, category);
        rs = get.executeQuery();
        while (rs.next()) {
            final int team = rs.getInt(1);
            result.add(team);
        }
    } finally {
        SQLFunctions.close(rs);
        SQLFunctions.close(get);
    }

    return result;
}

From source file:com.keybox.manage.db.ProfileSystemsDB.java

/**
 * deletes all systems for a given profile
 *
 * @param profileId profile id/*from w  w  w  .j  a  v a 2s.  c  om*/
 */
public static void deleteAllSystemsFromProfile(Long profileId) {

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement("delete from system_map where profile_id=?");
        stmt.setLong(1, profileId);
        stmt.execute();
        DBUtils.closeStmt(stmt);
    } catch (Exception e) {
        e.printStackTrace();
    }
    DBUtils.closeConn(con);
}

From source file:com.keybox.manage.db.ProfileSystemsDB.java

/**
 * Delete all Entries from system_map where system has not instance_id "---" 
 *///from  ww  w  .j  av a  2 s  . co  m
public static void deleteAWSSystemProfileEntries() {

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(
                "DELETE FROM system_map sm WHERE sm.system_id IN (SELECT s.id FROM system s WHERE s.instance_id NOT LIKE '---')");
        stmt.execute();
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        e.printStackTrace();
    }
    DBUtils.closeConn(con);
}