Example usage for java.sql Connection 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.wso2telco.dep.reportingservice.dao.OperatorDAO.java

/**
 * Gets the SP list.//from   w ww  .  j a v  a  2  s . c o  m
 *
 * @param operator the operator
 * @return the SP list
 */
public static ArrayList<SPObject> getSPList(String operator) {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    String sql = "Select * from sub_approval_operators WHERE OPERATOR_LIST like '%" + operator + "%'";
    ArrayList<SPObject> spList = new ArrayList<SPObject>();
    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            SPObject spObject = new SPObject();
            spObject.setAppId(rs.getInt("APP_ID"));
            spList.add(spObject);

        }

    } catch (Exception e) {
        log.error("Error occured while getting approved operators of application from the database" + e);
    }
    return spList;
}

From source file:index.IncrementIndex.java

public static ResultSet getResult(String storeId, String sqlPath, String defaultPath) throws Exception {
    // ??//from   www  .ja  v a 2s  .c  o  m
    config.load(new FileInputStream(sqlPath));
    // 
    String jdbcDriverClassName = config.getProperty("jdbcDriverClassName");
    String jdbcUrl = "jdbc:db2://";
    if (jdbcDriverClassName.contains("db2")) {
        jdbcUrl = "jdbc:db2://";
    } else if (jdbcDriverClassName.contains("mysql")) {
        jdbcUrl = "jdbc:mysql://";
    } else if (jdbcDriverClassName.contains("sqlserver")) {
        jdbcUrl = "jdbc:sqlserver://";
    } else if (jdbcDriverClassName.contains("sourceforge")) {
        jdbcUrl = "jdbc:jtds:sqlserver://";
    } else if (jdbcDriverClassName.contains("oracle")) {
        jdbcUrl = "jdbc:oracle:thin:@";
    }
    // url
    jdbcUrl = jdbcUrl + config.getProperty("iampDBIp") + ":" + config.getProperty("iampDBPort") + "/"
            + config.getProperty("iampDBName");
    String jdbcUsername = config.getProperty("iampDBUserName");
    String jdbcPassword = config.getProperty("iampDBPassword");
    // ?
    Class.forName(jdbcDriverClassName).newInstance();
    Connection conn = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword);
    // ???
    config.load(new FileInputStream(defaultPath));
    PreparedStatement stmt = null;
    ResultSet rs = null;
    String sql;
    if ("0".equals(storeId)) {
        sql = config.getProperty("nullSql");
        stmt = conn.prepareStatement(sql);
        rs = stmt.executeQuery();
    } else {
        sql = config.getProperty("entitySql");

        System.out.print("sql=" + sql);
        stmt = conn.prepareStatement(sql);
        stmt.setString(1, storeId);
        // 
        rs = stmt.executeQuery();
    }

    return rs;
}

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

public static void raisePrice(String coffeeName, double maximumPercentage, BigDecimal[] newPrice)
        throws SQLException {

    Connection con = DriverManager.getConnection("jdbc:default:connection");
    PreparedStatement pstmt = null;
    ResultSet rs = null;// w ww.j  a v  a2s  .c  o  m
    BigDecimal oldPrice;

    String queryGetCurrentCoffeePrice = "select COFFEES.PRICE " + "from COFFEES "
            + "where COFFEES.COF_NAME = ?";

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

    if (rs.next()) {
        oldPrice = rs.getBigDecimal(1);
    } else {
        return;
    }

    BigDecimal maximumNewPrice = oldPrice.multiply(new BigDecimal(1 + maximumPercentage));

    // Test if newPrice[0] > maximumNewPrice

    if (newPrice[0].compareTo(maximumNewPrice) == 1) {
        newPrice[0] = maximumNewPrice;
    }

    // Test if newPrice[0] <= oldPrice

    if (newPrice[0].compareTo(oldPrice) < 1) {
        newPrice[0] = oldPrice;
        return;
    }

    String queryUpdatePrice = "update COFFEES " + "set COFFEES.PRICE = ? " + "where COFFEES.COF_NAME = ?";

    pstmt = con.prepareStatement(queryUpdatePrice);
    pstmt.setBigDecimal(1, newPrice[0]);
    pstmt.setString(2, coffeeName);
    pstmt.executeUpdate();
}

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

/**
 * returns all system ids for user//from   w  w w .  j  a  v  a2  s  . c om
 *
 * @param con    DB connection
 * @param userId user id
 * @return system
 */
public static List<Long> getAllSystemIdsForUser(Connection con, Long userId) {

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

    try {
        PreparedStatement stmt = con.prepareStatement(
                "select distinct system_id from  system_map m, user_map um where m.profile_id=um.profile_id and um.user_id=?");
        stmt.setLong(1, userId);
        ResultSet rs = stmt.executeQuery();

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

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

    return systemIdList;

}

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

/**
 * method to do order by based on the sorted set object for systems for user
 *
 * @param sortedSet sorted set object/* www  .j av a2s  . co  m*/
 * @param userId    user id
 * @return sortedSet with list of host systems
 */
public static SortedSet getUserSystemSet(SortedSet sortedSet, Long userId) {
    List<HostSystem> hostSystemList = new ArrayList<>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select * from system where id in (select distinct system_id from  system_map m, user_map um where m.profile_id=um.profile_id and um.user_id=? ";
    //if profile id exists add to statement
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID)) ? " and um.profile_id=? "
            : "";
    sql += ") " + orderBy;

    //get user for auth token
    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        stmt.setLong(1, userId);
        //filter by profile id if exists
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))) {
            stmt.setLong(2, Long.valueOf(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID)));
        }

        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);

    sortedSet.setItemList(hostSystemList);
    return sortedSet;

}