Example usage for java.sql PreparedStatement setLong

List of usage examples for java.sql PreparedStatement setLong

Introduction

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

Prototype

void setLong(int parameterIndex, long x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java long value.

Usage

From source file:com.microsoftopentechnologies.azchat.web.dao.UserDAOImpl.java

/**
 * This method generates the prepare statement from userEntity object.
 * /*from   w  ww. j  av  a 2s . com*/
 * @param preparedStatement
 * @param user
 * @return
 * @throws SQLException
 */
public PreparedStatement generatePreparedStatement(PreparedStatement preparedStatement, UserEntity user)
        throws SQLException {
    preparedStatement.setString(1, user.getNameId());
    preparedStatement.setString(2, user.getIdentityProvider());
    preparedStatement.setString(3, user.getFirstName());
    preparedStatement.setString(4, user.getLastName());
    preparedStatement.setString(5, user.getPhotoBlobUrl());
    preparedStatement.setString(6, user.getEmailAddress());
    preparedStatement.setInt(7, user.getPhoneCountryCode());
    preparedStatement.setLong(8, user.getPhoneNumber());
    preparedStatement.setDate(9, new java.sql.Date(user.getDateCreated().getTime()));
    preparedStatement.setDate(10, new java.sql.Date(user.getCreatedBy().getTime()));
    preparedStatement.setDate(11, new java.sql.Date(user.getDateModified().getTime()));
    preparedStatement.setDate(12, new java.sql.Date(user.getModifiedBy().getTime()));
    return preparedStatement;
}

From source file:at.alladin.rmbt.controlServer.NewsResource.java

@Post("json")
public String request(final String entity) {
    addAllowOrigin();//from  w  w  w .  j  av a  2  s .c  om

    JSONObject request = null;

    final ErrorList errorList = new ErrorList();
    final JSONObject answer = new JSONObject();
    String answerString;

    System.out.println(MessageFormat.format(labels.getString("NEW_NEWS"), getIP()));

    if (entity != null && !entity.isEmpty())
        // try parse the string to a JSON object
        try {
            request = new JSONObject(entity);

            String lang = request.optString("language");

            // Load Language Files for Client

            final List<String> langs = Arrays
                    .asList(settings.getString("RMBT_SUPPORTED_LANGUAGES").split(",\\s*"));

            if (langs.contains(lang)) {
                errorList.setLanguage(lang);
                labels = ResourceManager.getSysMsgBundle(new Locale(lang));
            } else
                lang = settings.getString("RMBT_DEFAULT_LANGUAGE");

            String sqlLang = lang;
            if (!sqlLang.equals("de"))
                sqlLang = "en";

            if (conn != null) {
                final long lastNewsUid = request.optLong("lastNewsUid");
                final String plattform = request.optString("plattform");
                final int softwareVersionCode = request.optInt("softwareVersionCode", -1);
                String uuid = request.optString("uuid");

                final JSONArray newsList = new JSONArray();

                try {

                    final PreparedStatement st = conn.prepareStatement("SELECT uid,title_" + sqlLang
                            + " AS title, text_" + sqlLang + " AS text FROM news " + " WHERE"
                            + " (uid > ? OR force = true)" + " AND active = true"
                            + " AND (plattform IS NULL OR plattform = ?)"
                            + " AND (max_software_version_code IS NULL OR ? <= max_software_version_code)"
                            + " AND (min_software_version_code IS NULL OR ? >= min_software_version_code)"
                            + " AND (uuid IS NULL OR uuid::TEXT = ?)" + //convert to text so that empty uuid-strings are tolerated
                            " ORDER BY time ASC");
                    st.setLong(1, lastNewsUid);
                    st.setString(2, plattform);
                    st.setInt(3, softwareVersionCode);
                    st.setInt(4, softwareVersionCode);
                    st.setString(5, uuid);

                    final ResultSet rs = st.executeQuery();

                    while (rs.next()) {
                        final JSONObject jsonItem = new JSONObject();

                        jsonItem.put("uid", rs.getInt("uid"));
                        jsonItem.put("title", rs.getString("title"));
                        jsonItem.put("text", rs.getString("text"));

                        newsList.put(jsonItem);
                    }

                    rs.close();
                    st.close();
                } catch (final SQLException e) {
                    e.printStackTrace();
                    errorList.addError("ERROR_DB_GET_NEWS_SQL");
                }
                //                    }

                answer.put("news", newsList);

            } else
                errorList.addError("ERROR_DB_CONNECTION");

        } catch (final JSONException e) {
            errorList.addError("ERROR_REQUEST_JSON");
            System.out.println("Error parsing JSON Data " + e.toString());
        }
    else
        errorList.addErrorString("Expected request is missing.");

    try {
        answer.putOpt("error", errorList.getList());
    } catch (final JSONException e) {
        System.out.println("Error saving ErrorList: " + e.toString());
    }

    answerString = answer.toString();

    return answerString;
}

From source file:com.oic.event.SetProfile.java

@Override
public void ActionEvent(JSONObject json, WebSocketListener webSocket) {
    JSONObject responseJSON = new JSONObject();
    responseJSON.put("method", "setprofile");
    if (!validation(json, webSocket)) {
        return;//from w w w. j  a v  a  2s  . c o m
    }
    Connection con = DatabaseConnection.getConnection();
    PreparedStatement ps;
    try {
        con = DatabaseConnection.getConnection();
        con.setAutoCommit(false);
        String sql = "UPDATE user SET studentnumber = ?, name = ?, avatarid = ?, grade = ?, sex = ?, birth = ?, comment = ? "
                + "WHERE userid = ?";
        ps = con.prepareStatement(sql);
        ps.setString(1, json.get("studentid").toString());
        ps.setString(2, json.get("username").toString());
        ps.setInt(3, Integer.parseInt(json.get("avatarid").toString()));
        ps.setInt(4, Integer.parseInt(json.get("grade").toString()));
        ps.setInt(5, Integer.parseInt(json.get("gender").toString()));
        ps.setDate(6, toDate(json.get("birthday").toString()));
        ps.setString(7, json.get("comment").toString());
        ps.setLong(8, webSocket.getCharacter().getUserId());
        ps.executeUpdate();
        ps.close();

        sql = "UPDATE setting SET privategrade = ?, privatesex = ?, privatebirth =? WHERE userid = ?";
        ps = con.prepareStatement(sql);
        ps.setInt(1, Integer.parseInt(json.get("vgrade").toString()));
        ps.setInt(2, Integer.parseInt(json.get("vgender").toString()));
        ps.setInt(3, Integer.parseInt(json.get("vbirthday").toString()));
        ps.setLong(4, webSocket.getCharacter().getUserId());

        ps.executeUpdate();
        ps.close();

        con.commit();

        //TODO 
        responseJSON.put("status", 0);
    } catch (Exception e) {
        try {
            con.rollback();
        } catch (SQLException sq) {
            LOG.warning("[setProfile]Error Rolling back.");
        }
        e.printStackTrace();
        responseJSON.put("status", 1);
    } finally {
        try {
            con.setAutoCommit(true);
        } catch (SQLException ex) {
            Logger.getLogger(SetProfile.class.getName()).log(Level.WARNING,
                    "Error going back to AutoCommit mode", ex);
        }
    }

    webSocket.sendJson(responseJSON);
}

From source file:dk.netarkivet.harvester.datamodel.ScheduleDBDAO.java

/** Sets the first twelve parameters of a Schedule in the order.
 * name, comments, startdate, enddate, maxrepeats,
 * timeunit, numtimeunits, anytime, onminute, onhour,
 * ondayofweek, ondayofmonth/*from  www  .j  av  a 2  s .  c o  m*/
 * @param s a prepared SQL statement
 * @param schedule a given schedule.
 * @throws SQLException If the operation fails.
 */
private void setScheduleParameters(PreparedStatement s, Schedule schedule) throws SQLException {
    DBUtils.setName(s, 1, schedule, Constants.MAX_NAME_SIZE);
    DBUtils.setComments(s, 2, schedule, Constants.MAX_COMMENT_SIZE);
    final Date startDate = schedule.getStartDate();
    final int fieldNum = 3;
    DBUtils.setDateMaybeNull(s, fieldNum, startDate);
    if (schedule instanceof TimedSchedule) {
        TimedSchedule ts = (TimedSchedule) schedule;
        DBUtils.setDateMaybeNull(s, 4, ts.getEndDate());
        s.setNull(5, Types.BIGINT);
    } else {
        s.setNull(4, Types.DATE);
        RepeatingSchedule rs = (RepeatingSchedule) schedule;
        s.setLong(5, rs.getRepeats());
    }
    Frequency freq = schedule.getFrequency();
    s.setInt(6, freq.ordinal());
    s.setInt(7, freq.getNumUnits());
    s.setBoolean(8, freq.isAnytime());
    DBUtils.setIntegerMaybeNull(s, 9, freq.getOnMinute());
    DBUtils.setIntegerMaybeNull(s, 10, freq.getOnHour());
    DBUtils.setIntegerMaybeNull(s, 11, freq.getOnDayOfWeek());
    DBUtils.setIntegerMaybeNull(s, 12, freq.getOnDayOfMonth());
}

From source file:com.globalsight.everest.permission.Permission.java

/**
 * Adds the given permission to the database PERMISSION table if it does not
 * already exist in the map. This does update one by one, but this method
 * should almost never be called except on startup and if new permissions
 * were actually added, so there is no reason to batch.
 * /*from w w w .j av  a2 s. co m*/
 * @param p_added
 *            set to true if anything was added
 * @param p_perm
 *            permission name (should be above defined constant)
 */
static private boolean addPermission(long id, String p_perm) {
    boolean added = false;

    Connection c = null;
    PreparedStatement stmt = null;

    try {
        c = ConnectionPool.getConnection();
        c.setAutoCommit(false);

        if (s_idMap.isEmpty()) {
            stmt = c.prepareStatement(SQL_INSERT_FIRST_PERM);
            stmt.executeUpdate();
            s_idMap.put(p_perm, new Long(1));

            if (logger.isDebugEnabled()) {
                logger.debug("Added " + p_perm + " to the table.");
            }
        } else if (!s_idMap.containsKey(p_perm)) {
            stmt = c.prepareStatement(SQL_INSERT_PERM);
            stmt.setLong(1, id);
            stmt.setString(2, p_perm);
            stmt.executeUpdate();

            added = true;

            if (logger.isDebugEnabled()) {
                logger.debug("Added " + p_perm + " to the table.");
            }
        }

        c.commit();
    } catch (Exception ex) {
        logger.error("Failed to add permission" + p_perm + " to the database.", ex);
        added = false;
    } finally {
        ConnectionPool.silentClose(stmt);
        ConnectionPool.silentReturnConnection(c);
    }

    return added;
}

From source file:gsn.storage.StorageManager.java

/**
 * Executes the query of the database. Returns the specified colIndex of the
 * first row. Useful for image recovery of the web interface.
 *
 * @param query The query to be executed.
 * @return A resultset with only one row and one column. The user of the
 *         method should first call next on the result set to make sure that
 *         the row is there and then retrieve the value for the row.
 * @throws SQLException/*  ww w  .ja v a  2  s. c o m*/
 */

public ResultSet getBinaryFieldByQuery(StringBuilder query, String colName, long pk, Connection connection)
        throws SQLException {
    PreparedStatement ps = connection.prepareStatement(query.toString());
    ps.setLong(1, pk);
    return ps.executeQuery();
}

From source file:com.mirth.connect.server.controllers.tests.TestUtils.java

public static void createTestMessagesFast(String channelId, Message templateMessage, int power)
        throws Exception {
    long localChannelId = ChannelController.getInstance().getLocalChannelId(channelId);
    deleteAllMessages(channelId);//w w  w  .j  av  a  2s . c o  m
    createTestMessages(channelId, templateMessage, 1);

    Connection connection = null;
    PreparedStatement messageStatement = null;
    PreparedStatement metaDataStatement = null;
    PreparedStatement contentStatement = null;
    long idOffset = templateMessage.getMessageId();

    logger.debug("Replicating messages in channel \"" + channelId + "\"");

    try {
        connection = getConnection();
        messageStatement = connection.prepareStatement("INSERT INTO d_m" + localChannelId
                + " (id, server_id, received_date, processed) SELECT id + ?, server_id, received_date, processed FROM d_m"
                + localChannelId);
        metaDataStatement = connection.prepareStatement("INSERT INTO d_mm" + localChannelId
                + " (id, server_id, message_id, chain_id, received_date, status, order_id) SELECT id, server_id, message_id + ?, chain_id, received_date, status, order_id FROM d_mm"
                + localChannelId);
        contentStatement = connection.prepareStatement("INSERT INTO d_mc" + localChannelId
                + " (metadata_id, message_id, content_type, content, is_encrypted, data_type) SELECT metadata_id, message_id + ?, content_type, content, is_encrypted, data_type FROM d_mc"
                + localChannelId);

        for (int i = 0; i < power; i++) {
            messageStatement.setLong(1, idOffset);
            metaDataStatement.setLong(1, idOffset);
            contentStatement.setLong(1, idOffset);

            messageStatement.executeUpdate();
            metaDataStatement.executeUpdate();
            contentStatement.executeUpdate();

            idOffset *= 2;

            connection.commit();
            logger.debug("# of messages in channel \"" + channelId + "\" is now " + getNumMessages(channelId));
        }
    } finally {
        close(messageStatement);
        close(metaDataStatement);
        close(contentStatement);
        close(connection);
    }

    fixMessageIdSequence(channelId);
    logger.debug("Finished replicating messages in channel \"" + channelId + "\"");
}

From source file:org.ohmage.query.impl.AuditQueries.java

@Override
public void createAudit(final RequestServlet.RequestType requestType, final String uri, final String client,
        final String requestId, final String deviceId, final Map<String, String[]> parameters,
        final Map<String, String[]> extras, final String response, final long receivedMillis,
        final long respondMillis) throws DataAccessException {

    if (requestType == null) {
        throw new IllegalArgumentException("The request type is required and cannot be null.");
    } else if (uri == null) {
        throw new IllegalArgumentException("The request URI is required and cannot be null.");
    } else if (response == null) {
        throw new IllegalArgumentException("The response is required and cannot be null.");
    }//from  w ww  .j  a  v  a 2s.  co m

    // Create the transaction.
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("Creating a request audit.");

    try {
        // Begin the transaction.
        PlatformTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource());
        TransactionStatus status = transactionManager.getTransaction(def);

        // Create a key holder that will be responsible for referencing 
        // which row was just inserted.
        KeyHolder keyHolder = new GeneratedKeyHolder();

        // Insert the audit entry.
        try {
            getJdbcTemplate().update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(SQL_INSERT_AUDIT, new String[] { "id" });

                    ps.setString(1, requestType.name().toLowerCase());
                    ps.setString(2, uri);
                    ps.setString(3, client);
                    ps.setString(4, requestId);
                    ps.setString(5, deviceId);
                    ps.setString(6, response);
                    ps.setLong(7, receivedMillis);
                    ps.setLong(8, respondMillis);

                    return ps;
                }
            }, keyHolder);
        } catch (org.springframework.dao.DataAccessException e) {
            transactionManager.rollback(status);
            throw new DataAccessException("Error while executing SQL '" + SQL_INSERT_AUDIT
                    + "' with parameters: " + requestType.name().toLowerCase() + ", " + uri + ", " + client
                    + ", " + deviceId + ", " + response + ", " + receivedMillis + ", " + respondMillis, e);
        }

        // Add all of the parameters.
        if (parameters != null) {
            for (String key : parameters.keySet()) {
                for (String value : parameters.get(key)) {
                    try {
                        getJdbcTemplate().update(SQL_INSERT_PARAMETER, keyHolder.getKey().longValue(), key,
                                value);
                    } catch (org.springframework.dao.DataAccessException e) {
                        transactionManager.rollback(status);
                        throw new DataAccessException(
                                "Error while executing SQL '" + SQL_INSERT_PARAMETER + "' with parameters: "
                                        + keyHolder.getKey().longValue() + ", " + key + ", " + value,
                                e);
                    }
                }
            }
        }

        // Add all of the extras.
        if (extras != null) {
            for (String key : extras.keySet()) {
                for (String value : extras.get(key)) {
                    try {
                        getJdbcTemplate().update(SQL_INSERT_EXTRA, keyHolder.getKey().longValue(), key, value);
                    } catch (org.springframework.dao.DataAccessException e) {
                        transactionManager.rollback(status);
                        throw new DataAccessException(
                                "Error while executing SQL '" + SQL_INSERT_EXTRA + "' with parameters: "
                                        + keyHolder.getKey().longValue() + ", " + key + ", " + value,
                                e);
                    }
                }
            }
        }

        // Commit the transaction.
        try {
            transactionManager.commit(status);
        } catch (TransactionException e) {
            transactionManager.rollback(status);
            throw new DataAccessException("Error while committing the transaction.", e);
        }
    } catch (TransactionException e) {
        throw new DataAccessException("Error while attempting to rollback the transaction.", e);
    }
}

From source file:com.flexive.ejb.beans.MandatorEngineBean.java

/**
 * {@inheritDoc}/*from  w  w w.j  a  va 2  s  .  com*/
 */
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void remove(long mandatorId) throws FxApplicationException {
    final UserTicket ticket = FxContext.getUserTicket();
    final FxEnvironment environment = CacheAdmin.getEnvironment();
    // Security
    FxPermissionUtils.checkRole(ticket, Role.GlobalSupervisor);
    //exist check
    Mandator mand = environment.getMandator(mandatorId);
    Connection con = null;
    PreparedStatement ps = null;
    String sql;

    try {
        try {
            FxContext.get().runAsSystem();
            grp.remove(grp.loadMandatorGroup(mandatorId).getId());
        } finally {
            FxContext.get().stopRunAsSystem();
        }
        con = Database.getDbConnection();
        //                                                  1
        sql = "DELETE FROM " + TBL_USERGROUPS + " WHERE MANDATOR=? AND AUTOMANDATOR=1";
        ps = con.prepareStatement(sql);
        ps.setLong(1, mandatorId);
        ps.executeUpdate();
        ps.close();
        //                                                1
        sql = "DELETE FROM " + TBL_MANDATORS + " WHERE ID=?";
        ps = con.prepareStatement(sql);
        ps.setLong(1, mandatorId);
        ps.executeUpdate();
        StructureLoader.removeMandator(FxContext.get().getDivisionId(), mandatorId);
        StructureLoader.updateUserGroups(FxContext.get().getDivisionId(), grp.loadAll(-1));
    } catch (SQLException exc) {
        final boolean keyViolation = StorageManager.isForeignKeyViolation(exc);
        EJBUtils.rollback(ctx);
        if (keyViolation)
            throw new FxEntryInUseException(exc, "ex.mandator.removeFailed.inUse", mand.getName());
        throw new FxRemoveException(LOG, exc, "ex.mandator.removeFailed", mand.getName(), exc.getMessage());
    } finally {
        Database.closeObjects(MandatorEngineBean.class, con, ps);
    }
}

From source file:com.flexive.ejb.beans.MandatorEngineBean.java

/**
 * {@inheritDoc}/*from   w  w  w  .j  a va2 s.c o  m*/
 */
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void changeName(long mandatorId, String name) throws FxApplicationException {
    FxSharedUtils.checkParameterEmpty(name, "name");
    final UserTicket ticket = FxContext.getUserTicket();
    final FxEnvironment environment = CacheAdmin.getEnvironment();
    // Security
    FxPermissionUtils.checkRole(ticket, Role.GlobalSupervisor);
    //exist check
    Mandator mand = environment.getMandator(mandatorId);
    Connection con = null;
    PreparedStatement ps = null;
    String sql;

    try {
        name = name.trim();
        // Obtain a database connection
        con = Database.getDbConnection();
        //                                           1              2              3          4
        sql = "UPDATE " + TBL_MANDATORS + " SET NAME=?, MODIFIED_BY=?, MODIFIED_AT=? WHERE ID=?";
        final long NOW = System.currentTimeMillis();
        ps = con.prepareStatement(sql);
        ps.setString(1, name.trim());
        ps.setLong(2, ticket.getUserId());
        ps.setLong(3, NOW);
        ps.setLong(4, mandatorId);
        ps.executeUpdate();
        ps.close();
        sql = "UPDATE " + TBL_USERGROUPS + " SET NAME=? WHERE AUTOMANDATOR=?";
        ps = con.prepareStatement(sql);
        ps.setString(1, "Everyone (" + name + ")");
        ps.setLong(2, mandatorId);
        ps.executeUpdate();
        StructureLoader.updateMandator(FxContext.get().getDivisionId(),
                new Mandator(mand.getId(), name, mand.getMetadataId(), mand.isActive(),
                        new LifeCycleInfoImpl(mand.getLifeCycleInfo().getCreatorId(),
                                mand.getLifeCycleInfo().getCreationTime(), ticket.getUserId(), NOW)));
        StructureLoader.updateUserGroups(FxContext.get().getDivisionId(), grp.loadAll(-1));
    } catch (SQLException exc) {
        // check before rollback, because it might need an active transaciton
        final boolean uniqueConstraintViolation = StorageManager.isUniqueConstraintViolation(exc);
        EJBUtils.rollback(ctx);
        if (uniqueConstraintViolation) {
            throw new FxUpdateException(LOG, "ex.mandator.update.name.unique", name);
        } else {
            throw new FxUpdateException(LOG, exc, "ex.mandator.updateFailed", mand.getName(), exc.getMessage());
        }
    } finally {
        Database.closeObjects(MandatorEngineBean.class, con, ps);
    }
}