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.keybox.manage.db.SystemDB.java

/**
 * returns system by id//w w  w.j  a v a  2  s .  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.magnet.mmx.server.plugin.mmxmgmt.api.tags.MMXUserTagsResourceTest.java

@AfterClass
public static void cleanupDatabase() {
    final String statementStr1 = "DELETE FROM mmxApp WHERE appName LIKE '%" + "usertagresourcetestapp" + "%'";
    final String statementStr3 = "DELETE FROM ofUser WHERE username LIKE '%" + "MMXUserTagsResourceTestUser"
            + "%'";
    final String statementStr4 = "DELETE FROM mmxTag WHERE tagname LIKE '%" + "tag" + "%'";

    Connection conn = null;
    PreparedStatement pstmt1 = null;
    PreparedStatement pstmt3 = null;
    PreparedStatement pstmt4 = null;

    try {//from w ww.j a va2s.  co m
        conn = UnitTestDSProvider.getDataSource().getConnection();
        pstmt1 = conn.prepareStatement(statementStr1);
        pstmt3 = conn.prepareStatement(statementStr3);
        pstmt4 = conn.prepareStatement(statementStr4);
        pstmt1.execute();
        pstmt3.execute();
        pstmt4.execute();
    } catch (SQLException e) {
        LOGGER.error("cleanupDatabase : {}", e);
    } finally {
        CloseUtil.close(LOGGER, pstmt1, conn);
        CloseUtil.close(LOGGER, pstmt3);
        CloseUtil.close(LOGGER, pstmt4);
    }
}

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

/**
 * method to do order by based on the sorted set object for systems
 *
 * @param sortedSet sorted set object//from  w  w  w. j a  va  2  s .  com
 * @return sortedSet with list of host systems
 */
public static SortedSet getSystemSet(SortedSet sortedSet) {
    List<HostSystem> hostSystemList = new ArrayList<HostSystem>();

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

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))) {
            stmt.setLong(1, 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;

}

From source file:com.krawler.esp.servlets.SuperAdminServlet.java

public static String getCompanyList(Connection conn, String start, String limit) throws ServiceException {
    String result = null;//w w w .j  a v  a  2s .c o m
    PreparedStatement pstmt = null;
    KWLJsonConverter KWL = new KWLJsonConverter();
    ResultSet rs = null;
    String tp = null;
    try {
        pstmt = conn.prepareStatement(
                "SELECT company.companyid, company.image, company.companyname, company.createdon, company.address, company.city, company.modifiedon,"
                        + "company.state, company.country, company.phone, company.fax, company.zip, company.timezone, company.website, company.activated, "
                        + "count(companyusers.companyid) AS members FROM company LEFT JOIN companyusers ON company.companyid=companyusers.companyid "
                        + "GROUP BY company.companyid LIMIT ? OFFSET ?;");
        pstmt.setInt(1, Integer.parseInt(limit));
        pstmt.setInt(2, Integer.parseInt(start));
        rs = pstmt.executeQuery();
        result = KWL.GetJsonForGrid(rs);
        pstmt.close();
        pstmt = conn.prepareStatement("select count(*) as count from company;");
        rs = pstmt.executeQuery();
        rs.next();
        int count1 = rs.getInt("count");
        result = result.substring(1);
        tp = "{\"count\":" + count1 + "," + result;
    } catch (SQLException e) {
        throw ServiceException.FAILURE("SuperAdminHandler.getSUAdminData", e);
    } finally {
        DbPool.closeStatement(pstmt);
    }
    return tp;
}

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

/**
 * method to do order by based on the sorted set object for systems for user
 *
 * @param sortedSet sorted set object//  w  w  w . jav  a2  s .  c om
 * @param userId    user id
 * @return sortedSet with list of host systems
 */
public static SortedSet getUserSystemSet(SortedSet sortedSet, Long userId) {
    List<HostSystem> hostSystemList = new ArrayList<HostSystem>();

    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;

}

From source file:FacultyAdvisement.StudentRepository.java

public static void update(DataSource ds, Student student) throws SQLException {
    String studentSQL = "UPDATE STUDENT SET STUID = ?, FIRSTNAME = ?, LASTNAME = ?, MAJORCODE = ?, PHONE = ? WHERE EMAIL = ?";

    if (ds == null) {
        throw new SQLException("ds is null; Can't get data source");
    }/*  w  ww . j av a 2s.c o m*/

    Connection conn = ds.getConnection();

    if (conn == null) {
        throw new SQLException("conn is null; Can't get db connection");
    }

    try {
        //Student Information
        PreparedStatement sqlStatement = conn.prepareStatement(studentSQL);
        sqlStatement.setString(1, student.getId());
        sqlStatement.setString(2, student.getFirstName());
        sqlStatement.setString(3, student.getLastName());
        sqlStatement.setString(4, student.getMajorCode());
        sqlStatement.setString(5, student.getPhoneNumber());
        sqlStatement.setString(6, student.getUsername());

        sqlStatement.executeUpdate();

    } finally {
        conn.close();
    }
}

From source file:com.asakusafw.bulkloader.testutil.UnitTestUtil.java

/**
 * SQL??/* www. j a  v a  2s .  c o m*/
 * @param sql
 */
public static int executeUpdate(String sql) throws Exception {
    Connection conn = null;
    PreparedStatement stmt = null;
    try {
        printLog("executeUpdate???SQL" + sql, "executeUpdate");
        conn = DBConnection.getConnection();
        stmt = conn.prepareStatement(sql);
        int result = stmt.executeUpdate();
        printLog("executeUpdate?????" + result, "executeUpdate");
        return result;
    } finally {
        DBConnection.closePs(stmt);
        DBConnection.closeConn(conn);
    }
}

From source file:com.wso2telco.dep.subscriptionvalidator.util.ValidatorDBUtils.java

public static List<ValidatorClassDTO> getValidatorClassForSMSSubscription() throws ValidatorException {
    Connection conn = null;
    PreparedStatement ps = null;//from  www . j a va 2 s .  c  o m
    ResultSet results = null;
    String sql = "SELECT v.class as class,s.application_id as app,s.api_id as api FROM validator v, subscription_validator s WHERE v.id=s.validator_id";
    List<ValidatorClassDTO> validatorClass = new ArrayList<ValidatorClassDTO>();
    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
        ps = conn.prepareStatement(sql);
        results = ps.executeQuery();
        while (results.next()) {
            ValidatorClassDTO classDTO = new ValidatorClassDTO();
            classDTO.setClassName(results.getString("class"));
            classDTO.setApp(results.getInt("app"));
            classDTO.setApi(results.getInt("api"));
            validatorClass.add(classDTO);
        }
    } catch (Exception e) {
        handleException("Error occured while getting Validator Class  from the database", e);
    } finally {
        closeAllConnections(ps, conn, results);
    }
    return validatorClass;
}

From source file:com.concursive.connect.web.modules.common.social.rating.dao.Rating.java

/**
 * Deletes references from the specified table when the object is being deleted
 *
 * @param db//from   w w w.  jav a  2 s.c  o  m
 * @param objectId
 * @param table
 * @param uniqueField
 * @throws SQLException
 */
public static void delete(Connection db, int objectId, String table, String uniqueField) throws SQLException {
    PreparedStatement pst = db
            .prepareStatement("DELETE FROM " + table + "_rating " + "WHERE " + uniqueField + " = ? ");
    pst.setInt(1, objectId);
    pst.execute();
    pst.close();
}

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

/**
 * method to do order by based on the sorted set object for systems
 *
 * @param sortedSet sorted set object/*from  w ww.j  av a  2s  .c o m*/
 * @return sortedSet with list of host systems
 */
public static SortedSet getSystemSet(SortedSet sortedSet) {
    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 s ";
    //if profile id exists add to statement
    sql += StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))
            ? ",system_map m where s.id=m.system_id and m.profile_id=?"
            : "";
    sql += orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))) {
            stmt.setLong(1, 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;

}