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.flexive.core.Database.java

/**
 * Store a FxString in a translation table that only consists of n translation columns
 *
 * @param string     string to be stored
 * @param con        existing connection
 * @param table      storage table//from w ww  .  j  av a 2s. c o m
 * @param dataColumn names of the data columns
 * @param idColumn   name of the id column
 * @param id         id of the given string
 * @throws SQLException if a database error occured
 */
public static void storeFxString(FxString[] string, Connection con, String table, String[] dataColumn,
        String idColumn, long id) throws SQLException {
    PreparedStatement ps = null;
    if (string.length != dataColumn.length)
        throw new SQLException("string.length != dataColumn.length");
    for (FxString param : string) {
        if (!param.isMultiLanguage()) {
            throw new FxInvalidParameterException("string", LOG, "ex.db.fxString.store.multilang", table)
                    .asRuntimeException();
        }
    }
    try {
        ps = con.prepareStatement("DELETE FROM " + table + ML + " WHERE " + idColumn + "=?");
        ps.setLong(1, id);
        ps.execute();

        //find languages to write
        List<Long> langs = new ArrayList<Long>(5);
        for (FxString curr : string)
            for (long currLang : curr.getTranslatedLanguages())
                if (curr.translationExists(currLang)) {
                    if (!langs.contains(currLang))
                        langs.add(currLang);
                }
        if (langs.size() > 0) {
            StringBuffer sql = new StringBuffer(300);
            sql.append("INSERT INTO ").append(table).append(ML + "(").append(idColumn).append(",LANG");
            for (String dc : dataColumn)
                sql.append(',').append(dc).append(',').append(dc).append("_MLD");
            sql.append(")VALUES(?,?");
            //noinspection UnusedDeclaration
            for (FxString aString : string)
                sql.append(",?,?");
            sql.append(')');
            ps.close();
            ps = con.prepareStatement(sql.toString());
            boolean hasData;
            for (long lang : langs) {
                hasData = false;
                ps.setLong(1, id);
                ps.setInt(2, (int) lang);
                for (int i = 0; i < string.length; i++) {
                    if (FxString.EMPTY.equals(string[i].getTranslation(lang))) {
                        ps.setNull(3 + i * 2, java.sql.Types.VARCHAR);
                        ps.setBoolean(3 + 1 + i * 2, false);
                    } else {
                        ps.setString(3 + i * 2, string[i].getTranslation(lang)); //get translation or empty string
                        ps.setBoolean(3 + 1 + i * 2, string[i].isDefaultLanguage(lang));
                        hasData = true;
                    }
                }
                if (hasData)
                    ps.executeUpdate();
            }
        }
    } finally {
        if (ps != null)
            ps.close();
    }
}

From source file:de.ingrid.importer.udk.strategy.v1.IDCStrategy1_0_2_clean.java

protected void importDefaultCatalogData() throws Exception {

    if (log.isInfoEnabled()) {
        log.info("Creating default catalog...");
    }/*from   ww  w  . j a v  a2 s .co  m*/

    pSqlStr = "INSERT INTO t03_catalogue (id, cat_uuid, cat_name, partner_name , provider_name, country_code,"
            + "workflow_control, expiry_duration, create_time, mod_uuid, mod_time, language_code) VALUES "
            + "( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    PreparedStatement p = jdbc.prepareStatement(pSqlStr);

    sqlStr = "DELETE FROM t03_catalogue";
    jdbc.executeUpdate(sqlStr);

    String currentTime = IDCStrategyHelper.transDateTime(new Date());

    int cnt = 1;
    dataProvider.setId(dataProvider.getId() + 1);
    p.setLong(cnt++, dataProvider.getId()); // id
    p.setString(cnt++, UuidGenerator.getInstance().generateUuid()); // cat_uuid
    p.setString(cnt++, getImportDescriptor().getIdcCatalogueName()); // cat_name
    p.setString(cnt++, getImportDescriptor().getIdcPartnerName()); // partner_name
    p.setString(cnt++, getImportDescriptor().getIdcProviderName()); // provider_name
    p.setString(cnt++, getImportDescriptor().getIdcCatalogueCountry()); // country_code
    p.setString(cnt++, "N"); // workflow_control
    p.setNull(cnt++, Types.INTEGER); // expiry_duration
    p.setString(cnt++, currentTime); // create_time

    String modUuid = null;
    String modTime = null;

    String sql = "SELECT adr_uuid FROM t02_address";
    Statement st = jdbc.createStatement();
    ResultSet rs = jdbc.executeQuery(sql, st);
    if (rs.next()) {
        modUuid = rs.getString("adr_uuid");
        if (modUuid != null) {
            modTime = currentTime;
        }
    }
    rs.close();
    st.close();

    p.setString(cnt++, modUuid); // mod_uuid,
    p.setString(cnt++, modTime); // mod_time
    p.setString(cnt++, getCatalogLanguageFromDescriptor()); // language_code
    try {
        p.executeUpdate();
    } catch (Exception e) {
        log.error("Error executing SQL: " + p.toString(), e);
        throw e;
    }

    if (log.isInfoEnabled()) {
        log.info("Creating default catalog... done.");
    }
}

From source file:com.flexive.core.storage.PostgreSQL.PostgreSQLSequencerStorage.java

/**
 * {@inheritDoc}//from   w ww.  ja  v  a2s  .  c o  m
 */
@Override
public void setSequencerId(String name, long newId) throws FxApplicationException {
    Connection con = null;
    PreparedStatement ps = null;
    try {
        con = Database.getDbConnection();
        ps = con.prepareStatement("SELECT SETVAL('" + PG_SEQ_PREFIX + name + "',?,TRUE)");
        ps.setLong(1, newId);
        ps.execute();
    } catch (SQLException exc) {
        throw new FxDbException(LOG, exc, "ex.db.sqlError", exc.getMessage());
    } finally {
        Database.closeObjects(PostgreSQLSequencerStorage.class, con, ps);
    }
}

From source file:com.l2jfree.gameserver.communitybbs.bb.Topic.java

/**
 *
 *//*w  w  w  . j a va 2s .  c  om*/
public void insertindb() {
    Connection con = null;
    try {
        con = L2DatabaseFactory.getInstance().getConnection(con);
        PreparedStatement statement = con.prepareStatement(
                "INSERT INTO topic (topic_id,topic_forum_id,topic_name,topic_date,topic_ownername,topic_ownerid,topic_type,topic_reply) values (?,?,?,?,?,?,?,?)");
        statement.setInt(1, _id);
        statement.setInt(2, _forumId);
        statement.setString(3, _topicName);
        statement.setLong(4, _date);
        statement.setString(5, _ownerName);
        statement.setInt(6, _ownerId);
        statement.setInt(7, _type);
        statement.setInt(8, _cReply);
        statement.execute();
        statement.close();

    } catch (Exception e) {
        _log.warn("error while saving new Topic to db ", e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

From source file:com.l2jfree.gameserver.model.entity.faction.FactionMember.java

public FactionMember(int playerId, int factionId) {
    _playerId = playerId;//from   w w w  .  jav  a  2s .  co  m
    _factionId = factionId;
    _factionPoints = 0;
    _contributions = 0;
    _joinDate = System.currentTimeMillis();
    Connection con = null;
    try {
        con = L2DatabaseFactory.getInstance().getConnection(con);
        PreparedStatement statement;
        statement = con.prepareStatement(
                "INSERT INTO faction_members (player_id, faction_id, faction_points, contributions, join_date) VALUES (?, ?, 0, 0, ?)");
        statement.setInt(1, _playerId);
        statement.setInt(2, _factionId);
        statement.setLong(3, _joinDate);
        statement.execute();
        statement.close();
    } catch (Exception e) {
        _log.warn("", e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

From source file:com.jagornet.dhcp.db.JdbcIdentityAssocDAO.java

public void create(final IdentityAssoc ia) {
    /**//w ww. j a  v a  2 s  .  c om
     * Note: see https://issues.apache.org/jira/browse/DERBY-3609
     * "Formally, Derby does not support getGeneratedKeys since 
     * DatabaseMetaData.supportsGetGeneratedKeys() returns false. 
     * However, Statement.getGeneratedKeys() is partially implemented,
     * ... since it will only return a meaningful result when an single 
     * row insert is done with INSERT...VALUES"
     * 
     * Spring has thus provided a workaround as described here:
     * http://jira.springframework.org/browse/SPR-5306
     */
    GeneratedKeyHolder newKey = new GeneratedKeyHolder();
    getJdbcTemplate().update(new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
            PreparedStatement ps = conn.prepareStatement(
                    "insert into identityassoc" + " (duid, iatype, iaid, state)" + " values (?, ?, ?, ?)",
                    PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setBytes(1, ia.getDuid());
            ps.setByte(2, ia.getIatype());
            ps.setLong(3, ia.getIaid());
            ps.setByte(4, ia.getState());
            return ps;
        }
    }, newKey);
    Number newId = newKey.getKey();
    if (newId != null) {
        ia.setId(newId.longValue());
    }
}

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

/**
 * Helper method to insert an annotation and allow the other methods in
 * this class to do the work of linking the annotation to the appropriate
 * entity./* ww w .j a  va  2s.  c  o  m*/
 * 
 * @param annotationId a UUID to uniquely identify this annotation
 * @param time the epoch millis at which the annotation was created
 * @param timezone the timezone in which the annotation was created
 * @param client the software client that generated the annotation request
 * @param text the annotation text
 * @return the primary key of the newly created annotation
 * @throws org.springframework.dao.DataAccessException if an error occurs
 */
private long insertAnnotation(final UUID annotationId, final Long time, final DateTimeZone timezone,
        final String client, final String annotationText) throws org.springframework.dao.DataAccessException {

    final KeyHolder annotationIdKeyHolder = new GeneratedKeyHolder();

    getJdbcTemplate().update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(SQL_INSERT_ANNOTATION, new String[] { "id" });
            ps.setString(1, annotationId.toString());
            ps.setLong(2, time);
            ps.setString(3, timezone.getID());
            ps.setString(4, client);
            ps.setString(5, annotationText);
            return ps;
        }
    }, annotationIdKeyHolder);

    return annotationIdKeyHolder.getKey().longValue();
}

From source file:dk.netarkivet.harvester.datamodel.extendedfield.ExtendedFieldDBDAO.java

@Override
public void delete(long aExtendedfieldId) throws IOFailure {
    ArgumentNotValid.checkNotNull(aExtendedfieldId, "aExtendedfieldId");

    Connection c = HarvestDBConnection.get();
    PreparedStatement stm = null;
    try {/*from  w  w  w .  j  a v  a2  s .c o  m*/
        c.setAutoCommit(false);

        stm = c.prepareStatement("DELETE FROM extendedfieldvalue WHERE extendedfield_id = ?");
        stm.setLong(1, aExtendedfieldId);
        stm.executeUpdate();
        stm.close();
        stm = c.prepareStatement("DELETE FROM extendedfield WHERE extendedfield_id = ?");
        stm.setLong(1, aExtendedfieldId);
        stm.executeUpdate();

        c.commit();

    } catch (SQLException e) {
        String message = "SQL error deleting extended fields for ID " + aExtendedfieldId + "\n"
                + ExceptionUtils.getSQLExceptionCause(e);
        log.warn(message, e);
    } finally {
        DBUtils.closeStatementIfOpen(stm);
        DBUtils.rollbackIfNeeded(c, "delete extended field", aExtendedfieldId);
        HarvestDBConnection.release(c);
    }

}

From source file:org.kordamp.griffon.addressbook.spring.SpringContactRepository.java

private void update(@Nonnull final Contact contact) {
    jdbcTemplate.update(connection -> {
        PreparedStatement ps = connection.prepareStatement(
                "UPDATE contacts SET name = ?, lastname = ?, address = ?, company = ?, email = ? WHERE id = ?");
        ps.setString(1, contact.getName());
        ps.setString(2, contact.getLastname());
        ps.setString(3, contact.getAddress());
        ps.setString(4, contact.getCompany());
        ps.setString(5, contact.getEmail());
        ps.setLong(6, contact.getId());
        return ps;
    });//from w  ww.j  av  a2s  . c o m
}

From source file:at.alladin.rmbt.db.dao.QoSTestResultDao.java

/**
 * //  w  ww . j a  va  2 s .co  m
 * @param result
 * @throws SQLException
 */
public int save(QoSTestResult result) throws SQLException {
    String sql;

    PreparedStatement ps = null;

    if (result.getUid() == null) {
        sql = "INSERT INTO qos_test_result (test_uid, result, qos_test_uid, success_count, failure_count) VALUES (?,?::json,?,?,?)";
        ps = conn.prepareStatement(sql);
        ps.setLong(1, result.getTestUid());
        ps.setObject(2, result.getResults());
        ps.setLong(3, result.getQoSTestObjectiveId());
        ps.setInt(4, result.getSuccessCounter());
        ps.setInt(5, result.getFailureCounter());
    } else {
        sql = "UPDATE qos_test_result SET test_uid = ?, result = ?::json, qos_test_uid = ?, success_count = ?, failure_count = ? WHERE uid = ?";
        ps = conn.prepareStatement(sql);
        ps.setLong(1, result.getTestUid());
        ps.setObject(2, result.getResults());
        ps.setLong(3, result.getQoSTestObjectiveId());
        ps.setInt(4, result.getSuccessCounter());
        ps.setInt(5, result.getFailureCounter());
        ps.setLong(6, result.getUid());
    }

    return ps.executeUpdate();
}