Example usage for java.sql PreparedStatement setTimestamp

List of usage examples for java.sql PreparedStatement setTimestamp

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

Introduction

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

Prototype

void setTimestamp(int parameterIndex, java.sql.Timestamp x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given java.sql.Timestamp value.

Usage

From source file:com.linuxbox.enkive.audit.sqldb.SqlDbAuditService.java

@Override
public void addEvent(final int eventCode, final String userIdentifier, final String description,
        final boolean truncateDescription) throws AuditServiceException {
    AuditOperation<Integer> op = new AuditOperation<Integer>() {
        @Override/*from w ww . j  a va2s.  c  o  m*/
        public Integer execute(Connection connection) throws SQLException {
            PreparedStatement statement = connection.prepareStatement(INSERT_STATEMENT);
            setStatement(statement);

            Date nowDate = new Date();
            Timestamp nowTimestamp = new Timestamp(nowDate.getTime());

            // truncate description if allowed and requested and the
            // description exists
            String descriptionAlternate = description;
            if (truncateDescription && descriptionColumnSize > 0 && description != null
                    && description.length() > descriptionColumnSize) {
                descriptionAlternate = description.substring(0, descriptionColumnSize);
            }

            // JDBC is 1-based, not 0-based
            statement.setTimestamp(1, nowTimestamp);
            statement.setInt(2, eventCode);
            statement.setString(3, userIdentifier);
            statement.setString(4, descriptionAlternate);
            Integer result = statement.executeUpdate();
            return result;
        }
    };

    op.executeAuditOperation(); // return value is ignored
}

From source file:net.pms.dlna.DLNAMediaDatabase.java

public ArrayList<DLNAMediaInfo> getData(String name, long modified) {
    ArrayList<DLNAMediaInfo> list = new ArrayList<DLNAMediaInfo>();
    Connection conn = null;/*from w w w  . ja v  a2 s .c om*/
    ResultSet rs = null;
    PreparedStatement stmt = null;
    try {
        conn = getConnection();
        stmt = conn.prepareStatement("SELECT * FROM FILES WHERE FILENAME = ? AND MODIFIED = ?");
        stmt.setString(1, name);
        stmt.setTimestamp(2, new Timestamp(modified));
        rs = stmt.executeQuery();
        while (rs.next()) {
            DLNAMediaInfo media = new DLNAMediaInfo();
            int id = rs.getInt("ID");
            media.setDuration(toDouble(rs, "DURATION"));
            media.setBitrate(rs.getInt("BITRATE"));
            media.setWidth(rs.getInt("WIDTH"));
            media.setHeight(rs.getInt("HEIGHT"));
            media.setSize(rs.getLong("SIZE"));
            media.setCodecV(rs.getString("CODECV"));
            media.setFrameRate(rs.getString("FRAMERATE"));
            media.setAspect(rs.getString("ASPECT"));
            media.setAspectRatioContainer(rs.getString("ASPECTRATIOCONTAINER"));
            media.setAspectRatioVideoTrack(rs.getString("ASPECTRATIOVIDEOTRACK"));
            media.setReferenceFrameCount(rs.getByte("REFRAMES"));
            media.setAvcLevel(rs.getString("AVCLEVEL"));
            media.setBitsPerPixel(rs.getInt("BITSPERPIXEL"));
            media.setThumb(rs.getBytes("THUMB"));
            media.setContainer(rs.getString("CONTAINER"));
            media.setModel(rs.getString("MODEL"));
            if (media.getModel() != null && !FormatConfiguration.JPG.equals(media.getContainer())) {
                media.setExtrasAsString(media.getModel());
            }
            media.setExposure(rs.getInt("EXPOSURE"));
            media.setOrientation(rs.getInt("ORIENTATION"));
            media.setIso(rs.getInt("ISO"));
            media.setMuxingMode(rs.getString("MUXINGMODE"));
            media.setFrameRateMode(rs.getString("FRAMERATEMODE"));
            media.setMediaparsed(true);
            PreparedStatement audios = conn.prepareStatement("SELECT * FROM AUDIOTRACKS WHERE FILEID = ?");
            audios.setInt(1, id);
            ResultSet subrs = audios.executeQuery();
            while (subrs.next()) {
                DLNAMediaAudio audio = new DLNAMediaAudio();
                audio.setId(subrs.getInt("ID"));
                audio.setLang(subrs.getString("LANG"));
                audio.setFlavor(subrs.getString("FLAVOR"));
                audio.getAudioProperties().setNumberOfChannels(subrs.getInt("NRAUDIOCHANNELS"));
                audio.setSampleFrequency(subrs.getString("SAMPLEFREQ"));
                audio.setCodecA(subrs.getString("CODECA"));
                audio.setBitsperSample(subrs.getInt("BITSPERSAMPLE"));
                audio.setAlbum(subrs.getString("ALBUM"));
                audio.setArtist(subrs.getString("ARTIST"));
                audio.setSongname(subrs.getString("SONGNAME"));
                audio.setGenre(subrs.getString("GENRE"));
                audio.setYear(subrs.getInt("YEAR"));
                audio.setTrack(subrs.getInt("TRACK"));
                audio.getAudioProperties().setAudioDelay(subrs.getInt("DELAY"));
                audio.setMuxingModeAudio(subrs.getString("MUXINGMODE"));
                audio.setBitRate(subrs.getInt("BITRATE"));
                media.getAudioTracksList().add(audio);
            }
            subrs.close();
            audios.close();

            PreparedStatement subs = conn.prepareStatement("SELECT * FROM SUBTRACKS WHERE FILEID = ?");
            subs.setInt(1, id);
            subrs = subs.executeQuery();
            while (subrs.next()) {
                DLNAMediaSubtitle sub = new DLNAMediaSubtitle();
                sub.setId(subrs.getInt("ID"));
                sub.setLang(subrs.getString("LANG"));
                sub.setFlavor(subrs.getString("FLAVOR"));
                sub.setType(SubtitleType.valueOfStableIndex(subrs.getInt("TYPE")));
                media.getSubtitleTracksList().add(sub);
            }
            subrs.close();
            subs.close();

            list.add(media);
        }
    } catch (SQLException se) {
        logger.error(null, se);
        return null;
    } finally {
        close(rs);
        close(stmt);
        close(conn);
    }
    return list;
}

From source file:at.alladin.rmbt.statisticServer.UsageJSONResource.java

/**
 * Returns the statistics for used versions for a specific timespan [begin, end)
 * @param begin select all tests with time >= begin
 * @param end select all tests with time < end
 * @return the structurized JSON object//from   w ww .jav a 2  s  .co m
 * @throws SQLException
 * @throws JSONException
 */
private JSONObject getVersions(String platform, Timestamp begin, Timestamp end)
        throws SQLException, JSONException {
    JSONObject returnObj = new JSONObject();
    JSONArray sums = new JSONArray();
    JSONArray values = new JSONArray();
    returnObj.put("sums", sums);
    returnObj.put("values", values);

    HashMap<String, Long> fieldSums = new HashMap<>();

    PreparedStatement ps;
    ResultSet rs;

    final String sql = "SELECT date_trunc('day', time) _day, COALESCE(client_software_version,'null') \"version\", count(client_software_version) count_version"
            + " FROM test"
            + " WHERE status='FINISHED' AND deleted=false AND time >= ? AND time < ? AND plattform = ?"
            + " GROUP BY _day, client_software_version " + " HAVING count(client_software_version) > 0 "
            + " ORDER BY _day ASC";

    ps = conn.prepareStatement(sql);
    ps.setTimestamp(1, begin);
    ps.setTimestamp(2, end);
    ps.setString(3, platform);
    rs = ps.executeQuery();

    //one array-item for each day
    long currentTime = -1;
    JSONObject currentEntry = null;
    JSONArray currentEntryValues = null;
    while (rs.next()) {

        //new item, of a new day is reached
        long newTime = rs.getDate("_day").getTime();
        if (currentTime != newTime) {
            currentTime = newTime;
            currentEntry = new JSONObject();
            currentEntryValues = new JSONArray();
            currentEntry.put("day", rs.getDate("_day").getTime());
            currentEntry.put("values", currentEntryValues);
            values.put(currentEntry);
        }

        //disable null-values
        String version = rs.getString("version");
        long count = rs.getLong("count_version");
        if (version.isEmpty()) {
            version = "empty";
        }

        //add value to sum
        if (!fieldSums.containsKey(version)) {
            fieldSums.put(version, new Long(0));
        }
        fieldSums.put(version, fieldSums.get(version) + count);

        JSONObject current = new JSONObject();
        current.put("field", version);
        current.put("value", count);
        currentEntryValues.put(current);
    }

    rs.close();
    ps.close();

    //add field sums
    for (String field : fieldSums.keySet()) {
        JSONObject obj = new JSONObject();
        obj.put("field", field);
        obj.put("sum", fieldSums.get(field));
        sums.put(obj);
    }

    return returnObj;
}

From source file:cz.lbenda.dataman.db.RowDesc.java

@SuppressWarnings("ConstantConditions")
private <T> void putToPS(ColumnDesc columnDesc, T value, PreparedStatement ps, int position)
        throws SQLException {
    if (value == null) {
        ps.setObject(position, null);/*from   w  ww.j  av a  2 s. c o m*/
        return;
    }
    BinaryData bd = value instanceof BinaryData ? (BinaryData) value : null;
    switch (columnDesc.getDataType()) {
    case STRING:
        ps.setString(position, (String) value);
        break;
    case BOOLEAN:
        ps.setBoolean(position, (Boolean) value);
        break;
    case TIMESTAMP:
        ps.setTimestamp(position, (Timestamp) value);
        break;
    case DATE:
        ps.setDate(position, (Date) value);
        break;
    case TIME:
        ps.setTime(position, (Time) value);
        break;
    case BYTE:
        ps.setByte(position, (Byte) value);
        break;
    case SHORT:
        ps.setShort(position, (Short) value);
        break;
    case INTEGER:
        ps.setInt(position, (Integer) value);
        break;
    case LONG:
        ps.setLong(position, (Long) value);
        break;
    case FLOAT:
        ps.setFloat(position, (Float) value);
        break;
    case DOUBLE:
        ps.setDouble(position, (Double) value);
        break;
    case DECIMAL:
        ps.setBigDecimal(position, (BigDecimal) value);
        break;
    case UUID:
        ps.setBytes(position, AbstractHelper.uuidToByteArray((UUID) value));
        break;
    case ARRAY:
        throw new UnsupportedOperationException("The saving changes in ARRAY isn't supported.");
        // ps.setArray(position, (Array) value); break; // FIXME the value isn't in type java.sql.Array
    case BYTE_ARRAY:
        if (bd == null || bd.isNull()) {
            ps.setBytes(position, null);
        } else {
            try {
                ps.setBytes(position, IOUtils.toByteArray(bd.getInputStream()));
            } catch (IOException e) {
                throw new SQLException(e);
            }
        }
        break;
    case CLOB:
        if (bd == null || bd.isNull()) {
            ps.setNull(position, Types.CLOB);
        } else {
            ps.setClob(position, bd.getReader());
        }
        break;
    case BLOB:
        if (bd == null || bd.isNull()) {
            ps.setNull(position, Types.BLOB);
        } else {
            ps.setBlob(position, bd.getInputStream());
        }
        break;
    case OBJECT:
        ps.setObject(position, value);
    }
}

From source file:com.ushahidi.swiftriver.core.api.dao.impl.JpaDropDao.java

/**
 * Populates the buckets_droplets table// w ww. j  av  a 2s.c o  m
 * 
 * @param drops
 */
private void insertBucketDrops(final List<Drop> drops) {
    // Stores the drop id against the destination bucket ids
    Map<Long, Set<Long>> dropBucketsMap = new HashMap<Long, Set<Long>>();

    // Stores the drop id against its index in the drops list
    final Map<Long, Integer> dropsIndex = new HashMap<Long, Integer>();
    int i = 0;
    for (Drop drop : drops) {
        if (drop.getBucketIds() == null)
            continue;

        Set<Long> bucketSet = new HashSet<Long>();
        bucketSet.addAll(drop.getBucketIds());
        dropBucketsMap.put(drop.getId(), bucketSet);
        dropsIndex.put(drop.getId(), i);
        i++;
    }

    if (dropsIndex.isEmpty())
        return;

    // Exclude existing drops
    String existsSQL = "SELECT `bucket_id`, `droplet_id` "
            + "FROM `buckets_droplets` WHERE `droplet_id` IN (:ids)";

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("ids", dropsIndex.keySet());

    for (Map<String, Object> row : namedJdbcTemplate.queryForList(existsSQL, params)) {
        Long dropId = ((Number) row.get("droplet_id")).longValue();
        Long bucketId = ((Number) row.get("bucket_id")).longValue();

        if (dropBucketsMap.containsKey(dropId)) {
            Set<Long> bucketIdSet = dropBucketsMap.get(dropId);
            bucketIdSet.remove(bucketId);
        }
    }

    // List of arrays comprised of the drop id and bucket id
    final List<Long[]> bucketDropList = new ArrayList<Long[]>();
    for (Map.Entry<Long, Set<Long>> entry : dropBucketsMap.entrySet()) {
        for (Long bucketId : entry.getValue()) {
            Long[] bucketDrop = { bucketId, entry.getKey() };
            bucketDropList.add(bucketDrop);
        }
    }

    if (bucketDropList.isEmpty())
        return;

    // Store for the no. of drops inserted per bucket
    final Map<Long, Integer> bucketDropCount = new HashMap<Long, Integer>();

    // Query for populating TABLE buckets_droplets
    String insertSQL = "INSERT INTO `buckets_droplets` (`bucket_id`, `droplet_id`, `droplet_date_added`) "
            + "VALUES (?, ?, ?)";

    jdbcTemplate.batchUpdate(insertSQL, new BatchPreparedStatementSetter() {
        public void setValues(PreparedStatement ps, int index) throws SQLException {
            Long[] bucketDrop = bucketDropList.get(index);
            Long bucketId = bucketDrop[0];

            ps.setLong(1, bucketId);
            ps.setLong(2, bucketDrop[1]);
            ps.setTimestamp(3, new java.sql.Timestamp(new Date().getTime()));

            Integer count = bucketDropCount.remove(bucketId);
            count = (count == null) ? 1 : new Integer(count.intValue() + 1);
            bucketDropCount.put(bucketId, count);
        }

        @Override
        public int getBatchSize() {
            return bucketDropList.size();
        }
    });

    // Update the drop count for the updated buckets
    final List<Entry<Long, Integer>> bucketDropCountList = new ArrayList<Map.Entry<Long, Integer>>();
    bucketDropCountList.addAll(bucketDropCount.entrySet());

    String updateSQL = "UPDATE `buckets` SET `drop_count` = `drop_count` + ? WHERE `id` = ?";
    jdbcTemplate.batchUpdate(updateSQL, new BatchPreparedStatementSetter() {

        public void setValues(PreparedStatement ps, int i) throws SQLException {
            Entry<Long, Integer> entry = bucketDropCountList.get(i);
            ps.setLong(1, entry.getValue());
            ps.setLong(2, entry.getKey());
        }

        public int getBatchSize() {
            return bucketDropCountList.size();
        }
    });

}

From source file:com.agiletec.plugins.jpcrowdsourcing.aps.system.services.idea.IdeaDAO.java

@Override
public void insertIdea(IIdea idea) {
    Connection conn = null;/*from www .  ja va 2 s .c  o  m*/
    PreparedStatement stat = null;
    try {
        conn = this.getConnection();
        conn.setAutoCommit(false);
        stat = conn.prepareStatement(INSERT_IDEA);
        int index = 1;
        stat.setString(index++, idea.getId());
        stat.setString(index++, idea.getTitle());
        stat.setString(index++, idea.getDescr());
        stat.setTimestamp(index++, new Timestamp(idea.getPubDate().getTime()));
        stat.setString(index++, idea.getUsername());
        stat.setInt(index++, idea.getStatus());
        stat.setInt(index++, idea.getVotePositive());
        stat.setInt(index++, idea.getVoteNegative());
        stat.setString(index++, idea.getInstanceCode());
        stat.executeUpdate();

        this.updateTags(idea, conn);

        conn.commit();
    } catch (Throwable t) {
        this.executeRollback(conn);
        _logger.error("Error adding Idea", t);
        throw new RuntimeException("Error adding Idea", t);
    } finally {
        closeDaoResources(null, stat, conn);
    }
}

From source file:com.concursive.connect.web.modules.documents.dao.FileFolder.java

/**
 * Inserts a new folder/*  www  .  j av  a  2 s. co m*/
 *
 * @param db Description of the Parameter
 * @return Description of the Return Value
 * @throws SQLException Description of the Exception
 */
public boolean insert(Connection db) throws SQLException {
    if (!isValid()) {
        return false;
    }
    StringBuffer sql = new StringBuffer();
    sql.append("INSERT INTO project_folders "
            + "(link_module_id, link_item_id, subject, description, parent_id, ");
    if (entered != null) {
        sql.append("entered, ");
    }
    if (modified != null) {
        sql.append("modified, ");
    }
    sql.append("enteredby, modifiedby, display) " + "VALUES (?, ?, ?, ?, ?, ");
    if (entered != null) {
        sql.append("?, ");
    }
    if (modified != null) {
        sql.append("?, ");
    }
    sql.append("?, ?, ?) ");
    int i = 0;
    PreparedStatement pst = db.prepareStatement(sql.toString());
    pst.setInt(++i, linkModuleId);
    pst.setInt(++i, linkItemId);
    pst.setString(++i, subject);
    pst.setString(++i, description);
    DatabaseUtils.setInt(pst, ++i, parentId);
    if (entered != null) {
        pst.setTimestamp(++i, entered);
    }
    if (modified != null) {
        pst.setTimestamp(++i, modified);
    }
    pst.setInt(++i, enteredBy);
    pst.setInt(++i, modifiedBy);
    DatabaseUtils.setInt(pst, ++i, display);
    pst.execute();
    pst.close();
    id = DatabaseUtils.getCurrVal(db, "project_folders_folder_id_seq", -1);
    if (subFolders != null) {
        Iterator subI = subFolders.iterator();
        while (subI.hasNext()) {
            FileFolder thisFolder = (FileFolder) subI.next();
            thisFolder.setLinkItemId(linkItemId);
            thisFolder.setParentId(id);
            thisFolder.setId(-1);
        }
    }
    return true;
}

From source file:com.krminc.phr.security.PHRRealm.java

private void doFailedUpdate(String username, java.sql.Timestamp windowStart, int failedAttempts,
        boolean setLock) {
    String query = "UPDATE user_users SET failed_password_attempts = ? , failed_password_window_start = ? , is_locked_out = ?, lockout_begin = ? WHERE username = ?";
    PreparedStatement st = null;
    java.sql.Timestamp lockoutBegin = null;

    if (setLock) {
        GregorianCalendar tempCal = new GregorianCalendar(java.util.TimeZone.getTimeZone("GMT"));
        lockoutBegin = new java.sql.Timestamp(tempCal.getTimeInMillis());
    }//from www. ja  v  a2  s .co  m

    try {
        createDS();
        //TX for UPDATE
        conn = ds.getConnection();
        st = conn.prepareStatement(query);
        st.setInt(1, failedAttempts);
        st.setTimestamp(2, windowStart);
        st.setBoolean(3, setLock);
        st.setTimestamp(4, lockoutBegin);
        st.setString(5, username);
        st.executeUpdate();
    } catch (Exception e) {
        log("Error updating failed password values");
        log(e.getMessage());
    } finally {
        try {
            st.close();
            conn.close();
        } catch (Exception e) {
            log(e.getMessage());
        }
        conn = null;
    }
}

From source file:net.duckling.ddl.service.devent.dao.NoticeDAOImpl.java

public void batchWriteNotices(final List<Notice> data) {
    getJdbcTemplate().batchUpdate(SAVE, new BatchPreparedStatementSetter() {
        public int getBatchSize() {
            return data.size();
        }/*from www.ja v a 2  s  .  c o m*/

        public void setValues(PreparedStatement pst, int index) throws SQLException {
            int i = 0;
            Notice e = data.get(index);
            pst.setString(++i, e.getNoticeType());
            pst.setInt(++i, e.getTid());
            pst.setInt(++i, e.getEventId());
            pst.setString(++i, e.getRecipient());
            pst.setString(++i, e.getActor().getId());
            pst.setString(++i, e.getActor().getName());
            pst.setString(++i, e.getActor().getUrl());
            pst.setString(++i, e.getOperation().getName());
            pst.setString(++i, e.getTarget().getId());
            pst.setString(++i, e.getTarget().getType());
            pst.setString(++i, e.getTarget().getName());
            pst.setString(++i, e.getTarget().getUrl());
            pst.setInt(++i, e.getTargetVersion());
            pst.setString(++i, e.getReason());
            pst.setString(++i, e.getMessage());
            pst.setTimestamp(++i, new Timestamp(e.getOccurTime().getTime()));
            pst.setString(++i, e.getAddition());
            if (e.getRelative() != null) {
                pst.setString(++i, e.getRelative().getId());
                pst.setString(++i, e.getRelative().getName());
                pst.setString(++i, e.getRelative().getUrl());
            } else {
                pst.setString(++i, null);
                pst.setString(++i, null);
                pst.setString(++i, null);
            }
        }
    });
}