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.krawler.common.util.SchedulingUtilities.java

public static String getCmpHolidaydays(Connection conn, String companyId) throws ServiceException {
    String res = "";
    try {//from   ww w  .jav  a  2  s.co m
        String qry = "SELECT holiday, description FROM companyholidays WHERE companyid = ?";
        PreparedStatement pstmt = conn.prepareStatement(qry);
        pstmt.setString(1, companyId);
        ResultSet rs = pstmt.executeQuery();
        KWLJsonConverter j = new KWLJsonConverter();
        res = j.GetJsonForGrid(rs);
    } catch (SQLException e) {
        throw ServiceException.FAILURE("SchedulingUtilities.getCmpHolidaydays : " + e.getMessage(), e);
    }
    return res;
}

From source file:com.wso2telco.historylog.DbTracelog.java

/**
 * Log history.//from ww  w .  ja va  2s . c o m
 *
 * @param Reqtype         the reqtype
 * @param isauthenticated the isauthenticated
 * @param application     the application
 * @param authUser        the auth user
 * @param authenticators  the authenticators
 * @param ipaddress       the ipaddress
 * @throws LogHistoryException the log history exception
 */
public static void LogHistory(String Reqtype, boolean isauthenticated, String application, String authUser,
        String authenticators, String ipaddress) throws LogHistoryException {
    Connection con = null;
    PreparedStatement pst = null;
    try {
        con = DbTracelog.getMobileDBConnection();

        String sql = "INSERT INTO sp_login_history (reqtype, application_id, authenticated_user, isauthenticated,"
                + " authenticators,ipaddress, created, created_date)" + " VALUES " + "(?, ?, ?, ?, ?, ?, ?, ?)";

        pst = con.prepareStatement(sql);

        pst.setString(1, Reqtype);
        pst.setString(2, application);
        pst.setString(3, authUser);
        pst.setInt(4, (isauthenticated ? 1 : 0));
        pst.setString(5, authenticators);
        pst.setString(6, ipaddress);
        pst.setString(7, "authUser");
        pst.setTimestamp(8, new java.sql.Timestamp(new java.util.Date().getTime()));
        pst.executeUpdate();

    } catch (SQLException e) {
        handleException(
                "Error occured while Login SP LogHistory: " + application + " Service Provider: " + authUser,
                e);
    } finally {
        DbUtil.closeAllConnections(pst, con, null);
    }
}

From source file:com.sql.SECExceptions.java

/**
 * Inserts an exception to the database//from   w  ww  . jav  a2  s .c o m
 *
 * @param item SECExceptionsModel
 * @return boolean
 */
public static boolean insertException(SECExceptionsModel item) {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "INSERT INTO SECExceptions (" + "className, " + "methodName, " + "exceptionType, "
                + "exceptionDescrption, " + "timeOccurred " + ") VALUES (" + "?, " + "?, " + "?, " + "?, "
                + "GETDATE())";
        ps = conn.prepareStatement(sql);
        ps.setString(1, item.getClassName());
        ps.setString(2, item.getMethodName());
        ps.setString(3, item.getExceptionType());
        ps.setString(4, item.getExceptionDescription());
        ps.executeUpdate();
    } catch (SQLException ex) {
        System.out.println(ex.toString());
        return true;
    } finally {
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(conn);
    }
    return false;
}

From source file:ca.qc.adinfo.rouge.leaderboard.db.LeaderboardDb.java

public static HashMap<String, Leaderboard> getLeaderboards(DBManager dbManager) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;/*ww  w  .  java2 s .  c o m*/
    HashMap<String, Leaderboard> returnValue = new HashMap<String, Leaderboard>();

    String sql = "SELECT `key` FROM rouge_leaderboards;";

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

        rs = stmt.executeQuery();

        while (rs.next()) {

            String key = rs.getString("key");
            Leaderboard leaderboard = getLeaderboard(dbManager, key);

            if (leaderboard == null) {
                returnValue.put(key, new Leaderboard(key));
            } else {
                returnValue.put(key, leaderboard);
            }
        }

        return returnValue;

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

    } finally {

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

From source file:ca.qc.adinfo.rouge.social.db.SocialDb.java

public static boolean deleteFriend(DBManager dbManager, long userId, long friendId) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;//from  w w  w . j av a 2s. co  m
    String sql = null;

    sql = "DELETE FROM rouge_social_friends " + "WHERE (`user_id` = ? AND `friend_user_id` = ?) "
            + "OR (`user_id` = ? AND `friend_user_id` = ?)";

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

        stmt.setLong(1, userId);
        stmt.setLong(2, friendId);
        stmt.setLong(3, friendId);
        stmt.setLong(4, userId);

        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.firewallid.util.FISQL.java

public static void updateRowInsertIfNotExist(Connection conn, String tableName,
        Map<String, String> updateConditions, Map<String, String> fields) throws SQLException {
    /* Query *//*from w w w .j  a v  a  2 s  .c o  m*/
    String query = "SELECT " + Joiner.on(", ").join(updateConditions.keySet()) + " FROM " + tableName
            + " WHERE " + Joiner.on(" = ? AND ").join(updateConditions.keySet()) + " = ?";

    /* Execute */
    PreparedStatement pst = conn.prepareStatement(query);
    int i = 1;
    for (String value : updateConditions.values()) {
        pst.setString(i, value);
        i++;
    }
    ResultSet executeQuery = pst.executeQuery();
    if (executeQuery.next()) {
        /* Update */
        query = "UPDATE " + tableName + " SET " + Joiner.on(" = ?, ").join(fields.keySet()) + " = ? WHERE "
                + Joiner.on(" = ? AND ").join(updateConditions.keySet()) + " = ?";
        pst = conn.prepareStatement(query);
        i = 1;
        for (String value : fields.values()) {
            pst.setString(i, value);
            i++;
        }
        for (String value : updateConditions.values()) {
            pst.setString(i, value);
            i++;
        }
        pst.executeUpdate();
        return;
    }

    /* Row is not exists. Insert */
    query = "INSERT INTO " + tableName + " (" + Joiner.on(", ").join(fields.keySet()) + ", "
            + Joiner.on(", ").join(updateConditions.keySet()) + ") VALUES ("
            + StringUtils.repeat("?, ", fields.size() + updateConditions.size() - 1) + "?)";
    pst = conn.prepareStatement(query);
    i = 1;
    for (String value : fields.values()) {
        pst.setString(i, value);
        i++;
    }
    for (String value : updateConditions.values()) {
        pst.setString(i, value);
        i++;
    }
    pst.execute();
}

From source file:ca.qc.adinfo.rouge.leaderboard.db.LeaderboardDb.java

public static Leaderboard getLeaderboard(DBManager dbManager, String key) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;//from   www  . j  av a2s  .c  o m

    String sql = "SELECT score, user_id FROM rouge_leaderboard_score "
            + "WHERE `leaderboard_key` = ? ORDER BY `score` DESC LIMIT 5;";

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

        stmt.setString(1, key);

        Leaderboard leaderboard = new Leaderboard(key);

        rs = stmt.executeQuery();

        while (rs.next()) {
            Score score = new Score(rs.getLong("user_id"), rs.getLong("score"));
            leaderboard.addScore(score);
        }

        return leaderboard;

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

    } finally {

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

From source file:org.ulyssis.ipp.snapshot.Event.java

public static List<Event> loadFrom(Connection connection, Instant time) throws SQLException, IOException {
    String statement = "SELECT \"id\",\"data\",\"removed\" FROM \"events\" "
            + "WHERE \"time\" >= ? ORDER BY \"time\" ASC, \"id\" ASC";
    List<Event> events = new ArrayList<>();
    try (PreparedStatement stmt = connection.prepareStatement(statement)) {
        stmt.setTimestamp(1, Timestamp.from(time));
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            String evString = rs.getString("data");
            Event event = Serialization.getJsonMapper().readValue(evString, Event.class);
            event.id = rs.getLong("id");
            event.removed = rs.getBoolean("removed");
            events.add(event);/*w  ww  . j a  va  2 s.  c o  m*/
        }
    }
    return events;
}

From source file:com.aurel.track.dbase.InitReportTemplateBL.java

private static void addReportTemplateToDatabase(Integer oid, String name, String expfmt, String description) {

    String stmt = "INSERT INTO TEXPORTTEMPLATE (OBJECTID,NAME,EXPORTFORMAT,REPOSITORYTYPE,DESCRIPTION,PROJECT,PERSON,REPORTTYPE)"
            + "VALUES (" + oid + ",'" + name + "','" + expfmt + "',2,'" + description
            + "',NULL,1,'Jasper Report')";

    Connection coni = null;
    Connection cono = null;/*ww w.  j a va  2s  .co m*/
    ResultSet rs = null;
    try {
        coni = InitDatabase.getConnection();
        cono = InitDatabase.getConnection();
        PreparedStatement istmt = coni
                .prepareStatement("SELECT MAX(OBJECTID) FROM TEXPORTTEMPLATE WHERE OBJECTID < 100");
        Statement ostmt = cono.createStatement();

        rs = istmt.executeQuery();
        Integer maxInt = 0;
        if (rs != null) {
            rs.next();
            maxInt = rs.getInt(1);
        }
        if (oid.intValue() <= maxInt.intValue()) {
            return;
        }

        istmt = coni.prepareStatement("SELECT * FROM TEXPORTTEMPLATE WHERE OBJECTID = ?");
        istmt.setInt(1, oid);

        rs = istmt.executeQuery();
        if (rs == null || !rs.next()) {
            LOGGER.info("Adding report template with OID " + oid + ": " + name);
            try {
                ostmt.executeUpdate(stmt);
            } catch (Exception exc) {
                LOGGER.error("Problem...: " + exc.getMessage());
            }
        }
    } catch (Exception e) {
        LOGGER.debug(ExceptionUtils.getStackTrace(e));
    } finally {
        try {
            if (rs != null)
                rs.close();
            if (coni != null)
                coni.close();
            if (cono != null)
                cono.close();
        } catch (Exception e) {
            LOGGER.debug(ExceptionUtils.getStackTrace(e));
        }
    }
}

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

/**
 * returns terminal logs for user session for host system
 *
 * @param con       DB connection/*w w w  .ja va  2 s .c o m*/
 * @param sessionId session id
 * @return session output for session
 */
public static List<HostSystem> getHostSystemsForSession(Connection con, Long sessionId) {

    List<HostSystem> hostSystemList = new ArrayList<>();
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select distinct instance_id, system_id from terminal_log where session_id=?");
        stmt.setLong(1, sessionId);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            HostSystem hostSystem = SystemDB.getSystem(con, rs.getLong("system_id"));
            hostSystem.setInstanceId(rs.getInt("instance_id"));
            hostSystemList.add(hostSystem);
        }

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

    } catch (Exception ex) {
        log.error(ex.toString(), ex);
    }
    return hostSystemList;

}