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.ProfileSystemsDB.java

/**
 * returns a list of system ids for a given profile
 *
 * @param con       DB con//from  ww  w.j  ava2  s.  co  m
 * @param profileId profile id
 * @param userId user id
 * @return list of host systems
 */
public static List<Long> getSystemIdsByProfile(Connection con, Long profileId, Long userId) {

    List<Long> systemIdList = new ArrayList<Long>();
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select sm.system_id from  system_map sm, user_map um where um.profile_id=sm.profile_id and sm.profile_id=? and um.user_id=?");
        stmt.setLong(1, profileId);
        stmt.setLong(2, userId);
        ResultSet rs = stmt.executeQuery();

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

    } catch (Exception e) {
        e.printStackTrace();
    }
    return systemIdList;
}

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

/**
 * returns user base on id/*from   ww w . ja  v  a  2 s  .c om*/
 * @param con DB connection
 * @param userId user id
 * @return user object
 */
public static User getUser(Connection con, Long userId) {

    User user = null;
    try {
        PreparedStatement stmt = con.prepareStatement("select * from  users where id=?");
        stmt.setLong(1, userId);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            user = new User();
            user.setId(rs.getLong("id"));
            user.setFirstNm(rs.getString(FIRST_NM));
            user.setLastNm(rs.getString(LAST_NM));
            user.setEmail(rs.getString(EMAIL));
            user.setUsername(rs.getString(USERNAME));
            user.setPassword(rs.getString(PASSWORD));
            user.setAuthType(rs.getString(AUTH_TYPE));
            user.setUserType(rs.getString(USER_TYPE));
            user.setSalt(rs.getString("salt"));
            user.setProfileList(UserProfileDB.getProfilesByUser(con, userId));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    return user;
}

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

/**
 * checks to see if username is unique while ignoring current user
 *
 * @param userId user id/*from  w w w  . j  av a2 s. com*/
 * @param username username
 * @return true false indicator
 */
public static boolean isUnique(Long userId, String username) {

    boolean isUnique = true;
    if (userId == null) {
        userId = -99L;
    }

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(
                "select * from users where enabled=true and lower(username) like lower(?) and id != ?");
        stmt.setString(1, username);
        stmt.setLong(2, userId);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            isUnique = false;
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);
    } catch (Exception ex) {
        log.error(ex.toString(), ex);
    }
    DBUtils.closeConn(con);

    return isUnique;

}

From source file:com.sql.SystemError.java

/**
 * Gathers a list of errors based on type and count total of them
 *
 * @return//  w w w  .java  2 s .c o m
 */
public static List<SystemErrorModel> getErrorCounts() {
    List<SystemErrorModel> list = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT exceptionType, COUNT(*) AS 'num' " + "FROM SystemError "
                + "WHERE timeOccurred >= CAST(CURRENT_TIMESTAMP AS DATE) " + "AND username != 'andrew.schmidt' "
                + "AND username != 'anthony.perk' " + "GROUP BY exceptionType";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            SystemErrorModel item = new SystemErrorModel();
            item.setExceptionType(rs.getString("exceptionType") == null ? "" : rs.getString("exceptionType"));
            item.setNumber(rs.getInt("num"));
            list.add(item);
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}

From source file:ca.qc.adinfo.rouge.mail.db.MailDb.java

public static boolean sendMail(DBManager dbManager, long fromId, long toId, RougeObject content) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;//from  w  w w  .  java2  s.  co  m
    String sql = null;

    sql = "INSERT INTO rouge_mail (`from`, `to`, `content`, `status`, `time_sent`) " + "VALUES (?, ?, ?, ?, ?)";

    try {
        connection = dbManager.getConnection();
        stmt = connection.prepareStatement(sql);

        stmt.setLong(1, fromId);
        stmt.setLong(2, toId);
        stmt.setString(3, content.toJSON().toString());
        stmt.setString(4, "unr");
        stmt.setTimestamp(5, new Timestamp(System.currentTimeMillis()));

        int ret = stmt.executeUpdate();

        return (ret > 0);

    } catch (SQLException e) {
        log.error(stmt);
        log.error(e);
        return false;

    } finally {

        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(connection);
    }
}

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

/**
 * deletes audit history for users if after time set in properties file
 *
 * @param con DB connection//w w w .j  a  va  2s.  co  m
 */
public static void deleteAuditHistory(Connection con) {

    try {

        //delete logs with no terminal entries
        PreparedStatement stmt = con.prepareStatement(
                "delete from session_log where id not in (select session_id from terminal_log)");
        stmt.execute();

        //take today's date and subtract how many days to keep history
        Calendar cal = Calendar.getInstance();
        cal.add(Calendar.DATE, (-1 * Integer.parseInt(AppConfig.getProperty("deleteAuditLogAfter")))); //subtract
        java.sql.Date date = new java.sql.Date(cal.getTimeInMillis());

        stmt = con.prepareStatement("delete from session_log where session_tm < ?");
        stmt.setDate(1, date);
        stmt.execute();

        DBUtils.closeStmt(stmt);

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

}

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

/**
 * returns a list of systems for a given profile
 *
 * @param con       DB connection//from   w w w .  j a v a2  s . c  om
 * @param profileId profile id
 * @return list of host systems
 */
public static List<HostSystem> getSystemsByProfile(Connection con, Long profileId) {

    List<HostSystem> hostSystemList = new ArrayList<HostSystem>();
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select * from  system s, system_map m where s.id=m.system_id and m.profile_id=? order by display_nm asc");
        stmt.setLong(1, profileId);
        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.setEnabled(rs.getBoolean("enabled"));
            hostSystemList.add(hostSystem);
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);
    } catch (Exception e) {
        e.printStackTrace();
    }
    return hostSystemList;
}

From source file:ca.qc.adinfo.rouge.achievement.db.AchievementDb.java

public static boolean createAchievement(DBManager dbManager, String key, String name, int pointValue,
        double total) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;/*from   w  w  w .  j av  a2  s .  c  om*/
    String sql = null;

    sql = "INSERT INTO rouge_achievements (`key`, `name`, `point_value`, `total`) " + " VALUES (?, ?, ?, ?);";

    try {
        connection = dbManager.getConnection();
        stmt = connection.prepareStatement(sql);

        stmt.setString(1, key);
        stmt.setString(2, name);
        stmt.setInt(3, pointValue);
        stmt.setDouble(4, total);

        int ret = stmt.executeUpdate();

        return (ret > 0);

    } catch (SQLException e) {
        log.error(stmt);
        log.error(e);
        return false;

    } finally {

        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(connection);
    }

}

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

/**
 * returns users based on sort order defined
 * @param sortedSet object that defines sort order
 * @return sorted user list//from  w ww  .j  a  va 2 s. c o m
 */
public static SortedSet getUserSet(SortedSet sortedSet) {

    ArrayList<User> userList = new ArrayList<>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select * from  users where enabled=true " + orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            User user = new User();
            user.setId(rs.getLong("id"));
            user.setFirstNm(rs.getString(FIRST_NM));
            user.setLastNm(rs.getString(LAST_NM));
            user.setEmail(rs.getString(EMAIL));
            user.setUsername(rs.getString(USERNAME));
            user.setPassword(rs.getString(PASSWORD));
            user.setAuthType(rs.getString(AUTH_TYPE));
            user.setUserType(rs.getString(USER_TYPE));
            userList.add(user);

        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    sortedSet.setItemList(userList);
    return sortedSet;
}

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

/**
 * returns all admin users based on sort order defined
 * @param sortedSet object that defines sort order
 * @return sorted user list// ww w  .  j  a v  a2s .  c om
 */
public static SortedSet getAdminUserSet(SortedSet sortedSet) {

    ArrayList<User> userList = new ArrayList<>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select * from  users where enabled=true and user_type like '" + User.ADMINISTRATOR + "' "
            + orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            User user = new User();
            user.setId(rs.getLong("id"));
            user.setFirstNm(rs.getString(FIRST_NM));
            user.setLastNm(rs.getString(LAST_NM));
            user.setEmail(rs.getString(EMAIL));
            user.setUsername(rs.getString(USERNAME));
            user.setPassword(rs.getString(PASSWORD));
            user.setAuthType(rs.getString(AUTH_TYPE));
            user.setUserType(rs.getString(USER_TYPE));
            userList.add(user);

        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    sortedSet.setItemList(userList);
    return sortedSet;
}