Example usage for java.sql PreparedStatement setInt

List of usage examples for java.sql PreparedStatement setInt

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. setInt

Introduction

In this page you can find the example usage for java.sql PreparedStatement setInt.

Prototype

void setInt(int parameterIndex, int x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java int value.

Usage

From source file:com.concursive.connect.web.modules.profile.utils.ProjectUtils.java

public static int retrieveWebcastIdFromProjectId(Connection db, int projectId) throws SQLException {
    int webcastId = -1;
    PreparedStatement pst = db
            .prepareStatement("SELECT webcast_id " + "FROM project_webcast " + "WHERE project_id = ? ");
    pst.setInt(1, projectId);
    ResultSet rs = pst.executeQuery();
    if (rs.next()) {
        webcastId = rs.getInt("webcast_id");
    }//from www. j  ava 2  s.  c o m
    rs.close();
    pst.close();

    return webcastId;
}

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

/**
 * updates host system record//from   www .j a  v a  2 s  .com
 *
 * @param hostSystem host system object
 */
public static void updateSystem(HostSystem hostSystem) {

    Connection con = null;

    try {
        con = DBUtils.getConn();

        PreparedStatement stmt = con.prepareStatement(
                "update system set display_nm=?, user=?, host=?, port=?, authorized_keys=?, status_cd=?  where id=?");
        stmt.setString(1, hostSystem.getDisplayNm());
        stmt.setString(2, hostSystem.getUser());
        stmt.setString(3, hostSystem.getHost());
        stmt.setInt(4, hostSystem.getPort());
        stmt.setString(5, hostSystem.getAuthorizedKeys());
        stmt.setString(6, hostSystem.getStatusCd());
        stmt.setLong(7, hostSystem.getId());
        stmt.execute();
        DBUtils.closeStmt(stmt);

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

}

From source file:com.concursive.connect.web.modules.search.utils.SearchUtils.java

/**
 * Generates a list of projects that the user has access to
 *
 * @param db//from  ww w  .j a  va2s.c  o  m
 * @param userId
 * @param specificProjectId
 * @param specificCategoryId
 * @return
 * @throws SQLException
 */
public static String generateValidProjects(Connection db, int userId, int specificProjectId,
        int specificCategoryId) throws SQLException {
    if (userId < 1) {
        return "";
    }
    // @todo get ids from user cache
    // @update cache everytime a user is added or removed from a project team
    // get the projects for the user
    // get the project permissions for each project
    // if user has access to the data, then add to query
    StringBuffer projectList = new StringBuffer();
    PreparedStatement pst = db
            .prepareStatement("SELECT project_id " + "FROM project_team " + "WHERE user_id = ? "
                    + "AND status IS NULL " + (specificProjectId > -1 ? "AND project_id = ? " : "")
                    + (specificCategoryId > -1
                            ? "AND project_id IN (SELECT project_id FROM projects WHERE category_id = ?) "
                            : ""));
    int i = 0;
    pst.setInt(++i, userId);
    if (specificProjectId > -1) {
        pst.setInt(++i, specificProjectId);
    }
    if (specificCategoryId > -1) {
        pst.setInt(++i, specificCategoryId);
    }
    ResultSet rs = pst.executeQuery();
    while (rs.next()) {
        int projectId = rs.getInt("project_id");
        // these projects override the lower access projects
        if (projectList.length() > 0) {
            projectList.append(" OR ");
        }
        projectList.append(projectId);
    }
    rs.close();
    pst.close();
    return projectList.toString();
}

From source file:com.chaosinmotion.securechat.server.commands.DropMessages.java

public static void processRequest(UserInfo userinfo, JSONObject requestParams)
        throws ClassNotFoundException, SQLException, IOException {
    ArrayList<Message> messages = new ArrayList<Message>();

    JSONArray a = requestParams.getJSONArray("messages");
    int i, len = a.length();
    for (i = 0; i < len; ++i) {
        JSONObject item = a.getJSONObject(i);
        Message msg = new Message();
        msg.message = item.getInt("messageid");
        msg.checksum = item.getString("checksum");
        messages.add(msg);/*  w  w  w .j  a va  2s  .c o  m*/
    }

    /*
     * Iterate through the messages, deleting each. We only delete a
     * message if message belongs to the user and the checksum matches.
     * This assumes it's our message and it was read with someone who
     * can read the message.
     * 
     * (Thus, the weird query)
     */

    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        int count = 0;
        c = Database.get();
        ps = c.prepareStatement("DELETE FROM Messages " + "WHERE messageid IN "
                + "    (SELECT Messages.messageid " + "     FROM Messages, Devices "
                + "     WHERE Messages.messageid = ? " + "     AND Messages.checksum = ? "
                + "     AND Devices.deviceid = Messages.deviceid " + "     AND Devices.userid = ?)");

        for (Message msg : messages) {
            /*
             * Get the device ID for this device. Verify it belongs to the
             * user specified
             */

            ps.setInt(1, msg.message);
            ps.setString(2, msg.checksum);
            ps.setInt(3, userinfo.getUserID());
            ps.addBatch();
            ++count;
            if (count > 10240) {
                ps.executeBatch();
            }
        }
        if (count > 0) {
            ps.executeBatch();
        }
    } catch (BatchUpdateException batch) {
        throw batch.getNextException();
    } finally {
        if (rs != null)
            rs.close();
        if (ps != null)
            ps.close();
        if (c != null)
            c.close();
    }
}

From source file:com.concursive.connect.web.modules.profile.utils.ProjectUtils.java

/**
 * Rejects a project for the given user//from   www .  j  a  v  a  2s .c o m
 *
 * @param db        Description of the Parameter
 * @param projectId Description of the Parameter
 * @param userId    Description of the Parameter
 * @throws SQLException Description of the Exception
 */
public static void reject(Connection db, int projectId, int userId) throws SQLException {
    // Remove the user...
    PreparedStatement pst = db.prepareStatement(
            "DELETE FROM project_team " + "WHERE project_id = ? " + "AND user_id = ? " + "AND status = ? ");
    pst.setInt(1, projectId);
    pst.setInt(2, userId);
    pst.setInt(3, TeamMember.STATUS_PENDING);
    pst.execute();
    pst.close();
    CacheUtils.invalidateValue(Constants.SYSTEM_PROJECT_CACHE, projectId);
}

From source file:com.concursive.connect.web.modules.profile.utils.ProjectUtils.java

public static synchronized String updateUniqueId(Connection db, int projectId, String title)
        throws SQLException {
    if (projectId == -1) {
        throw new SQLException("ID was not specified");
    }// w  w  w  .  j  ava 2  s  . c  o m
    if (title == null) {
        throw new SQLException("Title was not specified");
    }
    // reserve a unique text id for the project
    String uniqueId = ProjectUtils.generateUniqueId(title, projectId, db);
    PreparedStatement pst = db
            .prepareStatement("UPDATE projects " + "SET projecttextid = ? " + "WHERE project_id = ?");
    pst.setString(1, uniqueId);
    pst.setInt(2, projectId);
    pst.execute();
    pst.close();
    CacheUtils.invalidateValue(Constants.SYSTEM_PROJECT_CACHE, projectId);
    CacheUtils.invalidateValue(Constants.SYSTEM_PROJECT_UNIQUE_ID_CACHE, uniqueId);
    return uniqueId;
}

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;//from   w w w.  j  a v  a 2  s. c  o m
    Connection cono = null;
    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.predic8.membrane.core.interceptor.statistics.util.JDBCUtil.java

public static void setData(AbstractExchange exc, PreparedStatement prepSt, boolean idGenerated, int flag,
        String tId, String[] gatewayHCIDs) throws SQLException {
    int startIndex = 0;
    if (!idGenerated) {
        UUID id = UUID.randomUUID();
        prepSt.setLong(++startIndex, id.getLeastSignificantBits());
    }//from w  ww .  j  av  a  2  s  . c o  m
    boolean isReq = (exc.getResponse() == null);

    log.info("Handling interceptor id is: " + tId);
    log.info((isReq) ? "logging for request" : "logging for response");

    prepSt.setInt(++startIndex, (isReq) ? 200 : exc.getResponse().getStatusCode());
    prepSt.setString(++startIndex, (flag == 0) ? "REQUEST" : "RESPONSE");
    prepSt.setTimestamp(++startIndex, new Timestamp(ExchangesUtil.getDate(exc).getTime()));//skb
    prepSt.setString(++startIndex, exc.getRule().toString());
    prepSt.setString(++startIndex, exc.getRequest().getMethod());
    prepSt.setString(++startIndex, exc.getRequest().getUri());
    prepSt.setString(++startIndex,
            (gatewayHCIDs != null && !"".equals(gatewayHCIDs[2])) ? gatewayHCIDs[2] : exc.getSourceHostname());
    prepSt.setString(++startIndex,
            (gatewayHCIDs != null && !"".equals(gatewayHCIDs[3])) ? gatewayHCIDs[3] : exc.getServer());

    if (gatewayHCIDs != null) {
        prepSt.setString(++startIndex, gatewayHCIDs[0]);
        prepSt.setString(++startIndex, gatewayHCIDs[1]);
    } else {
        prepSt.setString(++startIndex, exc.getSourceHostname());
        prepSt.setString(++startIndex, exc.getServer());
    }

    prepSt.setString(++startIndex, (isReq) ? exc.getRequestContentType() : exc.getResponseContentType());
    prepSt.setInt(++startIndex, (isReq) ? exc.getRequestContentLength() : exc.getResponseContentLength());
    /*
    prepSt.setString(++ startIndex, (isReq)?null:exc.getResponseContentType());
    prepSt.setInt(++ startIndex, (isReq)?null:exc.getResponseContentLength());
    */
    prepSt.setLong(++startIndex, (isReq) ? 0 : (exc.getTimeResReceived() - exc.getTimeReqSent()));

    prepSt.setString(++startIndex, (String) getExProperty(exc, FileExchangeStore.MESSAGE_FILE_PATH));

    /* skb */
    String[] colList = { JDBCUtil.MSG_HEADER, JDBCUtil.MSG };

    if (isReq) {
        for (String col : colList) {
            log.info("processing col:" + col);

            ++startIndex;
            try {
                byte[] os = (byte[]) getExProperty(exc, col);
                if (os != null) {
                    prepSt.setBytes(startIndex, os);
                } else
                    prepSt.setBytes(startIndex, null);

            } catch (Exception ex) {
                prepSt.setBytes(startIndex, null);
            }
        }
    } else {

        for (String col : colList) {
            log.info("processing col:" + col);

            ++startIndex;
            try {
                byte[] os = null;
                if (col.equals(JDBCUtil.MSG)) {
                    try {
                        os = IOUtils.toByteArray((exc.getResponse().getBodyAsStream()));
                    } catch (Exception ex) {
                        log.info(ex.toString());
                    }
                } else if (col.equals(JDBCUtil.MSG_HEADER)) {
                    Message msg2 = exc.getResponse();

                    ByteArrayOutputStream header2 = new ByteArrayOutputStream();

                    msg2.writeStartLine(header2);
                    msg2.getHeader().write(header2);
                    header2.write((Constants.CRLF).getBytes());

                    os = header2.toByteArray();
                }
                if (os != null) {
                    prepSt.setBytes(startIndex, os);
                } else
                    prepSt.setBytes(startIndex, null);

            } catch (Exception ex) {
                prepSt.setBytes(startIndex, null);
            }
        }

    }

}

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>//from w w w  . j  av a 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.keybox.manage.db.SystemDB.java

/**
 * inserts host system into DB//from  w  ww . j a v a 2  s. c o  m
 *
 * @param hostSystem host system object
 * @return user id
 */
public static Long insertSystem(HostSystem hostSystem) {

    Connection con = null;

    Long userId = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(
                "insert into system (display_nm, user, host, port, authorized_keys, status_cd) values (?,?,?,?,?,?)",
                PreparedStatement.RETURN_GENERATED_KEYS);
        stmt.setString(1, hostSystem.getDisplayNm());
        stmt.setString(2, hostSystem.getUser());
        stmt.setString(3, hostSystem.getHost());
        stmt.setInt(4, hostSystem.getPort());
        stmt.setString(5, hostSystem.getAuthorizedKeys());
        stmt.setString(6, hostSystem.getStatusCd());
        stmt.execute();

        ResultSet rs = stmt.getGeneratedKeys();
        if (rs.next()) {
            userId = rs.getLong(1);
        }
        DBUtils.closeStmt(stmt);

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

}