Example usage for java.sql PreparedStatement setLong

List of usage examples for java.sql PreparedStatement setLong

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. 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);
    }
}