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:com.oracle.tutorial.jdbc.StoredProcedureJavaDBSample.java

public static void getSupplierOfCoffee(String coffeeName, String[] supplierName) throws SQLException {
    Connection con = DriverManager.getConnection("jdbc:default:connection");
    PreparedStatement pstmt = null;
    ResultSet rs = null;//w w  w  . j a v  a  2 s. c  om

    String query = "select SUPPLIERS.SUP_NAME " + "from SUPPLIERS, COFFEES "
            + "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " + "and ? = COFFEES.COF_NAME";

    pstmt = con.prepareStatement(query);
    pstmt.setString(1, coffeeName);
    rs = pstmt.executeQuery();

    if (rs.next()) {
        supplierName[0] = rs.getString(1);
    } else {
        supplierName[0] = null;
    }
}

From source file:com.tethrnet.manage.db.SystemDB.java

/**
 * returns system by id/*from  ww  w  .  j  a v a 2s  . co m*/
 *
 * @param con DB connection
 * @param id  system id
 * @return system
 */
public static HostSystem getSystem(Connection con, Long id) {

    HostSystem hostSystem = null;

    try {

        PreparedStatement stmt = con.prepareStatement("select * from  system where id=?");
        stmt.setLong(1, id);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            hostSystem = new HostSystem();
            hostSystem.setId(rs.getLong("id"));
            hostSystem.setDisplayNm(rs.getString("display_nm"));
            hostSystem.setUser(rs.getString("user"));
            hostSystem.setHost(rs.getString("host"));
            hostSystem.setPort(rs.getInt("port"));
            hostSystem.setAuthorizedKeys(rs.getString("authorized_keys"));
            hostSystem.setStatusCd(rs.getString("status_cd"));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }

    return hostSystem;
}

From source file:com.tethrnet.manage.db.SystemDB.java

/**
 * returns all system ids/*from  w w w .jav  a 2 s  .c  om*/
 *
 * @param con DB connection
 * @return system
 */
public static List<Long> getAllSystemIds(Connection con) {

    List<Long> systemIdList = new ArrayList<Long>();

    try {
        PreparedStatement stmt = con.prepareStatement("select * from  system");
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            systemIdList.add(rs.getLong("id"));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }

    return systemIdList;

}

From source file:gridool.util.jdbc.JDBCUtils.java

/**
 * Execute an SQL SELECT query with replacement parameters.  The
 * caller is responsible for closing the connection.
 * //from  w  w  w .  j  a v  a  2s.c  o m
 * @param conn The connection to execute the query in.
 * @param sql The query to execute.
 * @param params The replacement parameters.
 * @return The object represents ResultSet.
 */
public static ResultSet fetch(Connection conn, String sql, Object[] params) throws SQLException {
    ResultSet rs = null;
    try {
        PreparedStatement stmt = conn.prepareStatement(sql);
        fillStatement(stmt, params);
        verboseQuery(sql, params);
        rs = stmt.executeQuery();
    } catch (SQLException e) {
        rethrow(e, sql, params);
    }
    return rs;
}

From source file:fll.db.NonNumericNominees.java

/**
 * Add a set of nominees to the database. If the nominee already exsts, there
 * is no error./*from   w w  w. j  av  a  2 s  . co  m*/
 * 
 * @throws SQLException
 */
public static void addNominees(final Connection connection, final int tournamentId, final String category,
        final Set<Integer> teamNumbers) throws SQLException {
    PreparedStatement check = null;
    ResultSet checkResult = null;
    PreparedStatement insert = null;
    final boolean autoCommit = connection.getAutoCommit();
    try {
        connection.setAutoCommit(false);

        check = connection.prepareStatement("SELECT team_number FROM non_numeric_nominees" //
                + " WHERE tournament = ?" //
                + "   AND category = ?" //
                + "   AND team_number = ?");
        check.setInt(1, tournamentId);
        check.setString(2, category);

        insert = connection.prepareStatement("INSERT INTO non_numeric_nominees" //
                + " (tournament, category, team_number) VALUES(?, ?, ?)");
        insert.setInt(1, tournamentId);
        insert.setString(2, category);

        for (final int teamNumber : teamNumbers) {
            check.setInt(3, teamNumber);
            insert.setInt(3, teamNumber);

            checkResult = check.executeQuery();
            if (!checkResult.next()) {
                insert.executeUpdate();
            }
        }

        connection.commit();
    } finally {
        connection.setAutoCommit(autoCommit);

        SQLFunctions.close(checkResult);
        SQLFunctions.close(check);
        SQLFunctions.close(insert);
    }
}

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

/**
 * returns all system ids/* w  w w  . j a va 2s  . co  m*/
 *
 * @param con DB connection
 * @return system
 */
public static List<Long> getAllSystemIds(Connection con) {

    List<Long> systemIdList = new ArrayList<>();

    try {
        PreparedStatement stmt = con.prepareStatement("select * from  system");
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            systemIdList.add(rs.getLong("id"));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }

    return systemIdList;

}

From source file:genericepayadmin.AddIpBean.java

public static ArrayList getTreasury(Connection con) throws Exception {
    PreparedStatement ps = null;//from  w w  w .  ja  v  a  2 s  . co  m
    ResultSet rs = null;
    ArrayList al = new ArrayList();

    try {
        String sql = "select wbser.active, wbser.id, gdep.dept_name, wbser.ipaddress,wbser.checksum from webservice_validator wbser,generic_dept gdep "
                + " where gdep.DEPT_ID=wbser.deptid ";
        ps = con.prepareStatement(sql);

        rs = ps.executeQuery();
        while (rs.next()) {
            AddIpBean tbean = new AddIpBean();
            tbean.setId(rs.getString("id"));
            tbean.setDept_name(rs.getString("dept_name"));
            tbean.setIpaddress(rs.getString("ipaddress"));
            tbean.setChecksum(rs.getString("checksum"));
            tbean.setStatus(rs.getString("active"));
            al.add(tbean);
        }
    } catch (Exception e) {
        System.out.println(e.getMessage());
    } finally {
        if (ps != null)
            ps.close();
        if (rs != null)
            rs.close();
    }
    return al;
}

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

/**
 * returns all systems/*from  w w  w  . jav a2  s.c o  m*/
 *
 * @return system list
 */
public static List<HostSystem> getAllSystems() {

    List<HostSystem> hostSystemList = new ArrayList<>();

    Connection con = null;

    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement("select * from  system");
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            HostSystem hostSystem = new HostSystem();
            hostSystem.setId(rs.getLong("id"));
            hostSystem.setDisplayNm(rs.getString(DISPLAY_NM));
            hostSystem.setUser(rs.getString("user"));
            hostSystem.setHost(rs.getString("host"));
            hostSystem.setPort(rs.getInt("port"));
            hostSystem.setAuthorizedKeys(rs.getString(AUTHORIZED_KEYS));
            hostSystem.setStatusCd(rs.getString(STATUS_CD));
            hostSystemList.add(hostSystem);
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }

    DBUtils.closeConn(con);

    return hostSystemList;

}

From source file:com.novartis.opensource.yada.util.YADAUtils.java

/**
 * One-liner execution of a sql statement, returning an SQL {@link java.sql.ResultSet}.
 * <strong>Note: This method opens a db connection but DOES NOT CLOSE IT. 
 * Use the static method {@link ConnectionFactory#releaseResources(ResultSet)} to close it from 
 * the calling method</strong>//w  w w  .j a  va 2 s  . c  o m
 * @param sql the query to execute
 * @param params the data values to map to query columns
 * @return a {@link java.sql.ResultSet} object containing the result of the query
 * @throws YADAConnectionException when the datasource is inaccessible
 * @throws YADASQLException when the JDBC configuration or execution fails
 */
public static ResultSet executePreparedStatement(String sql, Object[] params)
        throws YADAConnectionException, YADASQLException {
    ResultSet rs = null;
    try {
        Connection c = ConnectionFactory.getConnectionFactory().getConnection(ConnectionFactory.YADA_APP);
        PreparedStatement p = c.prepareStatement(sql);
        for (int i = 1; i <= params.length; i++) {
            Object param = params[i - 1];
            if (param instanceof String) {
                p.setString(i, (String) param);
            } else if (param instanceof Date) {
                p.setDate(i, (Date) param);
            } else if (param instanceof Integer) {
                p.setInt(i, ((Integer) param).intValue());
            } else if (param instanceof Float) {
                p.setFloat(i, ((Float) param).floatValue());
            }
        }
        rs = p.executeQuery();
    } catch (SQLException e) {
        throw new YADASQLException(e.getMessage(), e);
    }
    return rs;
}

From source file:com.wso2telco.dep.validator.handler.utils.ValidatorDBUtils.java

/**
 * Method to retrieve the validator class from the database.
 *
 * @param applicationId/*from www.  j  a  va2  s.c o m*/
 * @param apiId
 * @return validator class name
 * @throws ValidatorException
 */
public static String getValidatorClassForSubscription(int applicationId, int apiId) throws ValidatorException {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet results = null;
    String sql = "SELECT class FROM validator, subscription_validator "
            + "WHERE subscription_validator.application_id=? AND subscription_validator.api_id=? AND "
            + "validator.id=subscription_validator.validator_id";
    String validatorClass = null;
    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
        ps = conn.prepareStatement(sql);
        log.debug("getValidatorClassForSubscription for applicationId---> " + applicationId + " apiId--> "
                + apiId);
        ps.setInt(1, applicationId);
        ps.setInt(2, apiId);
        results = ps.executeQuery();
        if (results.isBeforeFirst()) {
            while (results.next()) {
                validatorClass = results.getString("class");
            }
        } else {
            log.error("Result Set is empty");
        }
    } catch (Exception e) {
        handleException("Error occured while getting Validator Class for App: " + applicationId + " API: "
                + apiId + " from the database", e);
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, conn, results);
    }
    return validatorClass;
}