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.zimbra.cs.db.DbMailItem.java

public static void addToCalendarItemTable(CalendarItem calItem) throws ServiceException {
    Mailbox mbox = calItem.getMailbox();

    long start = calItem.getStartTime();
    long end = calItem.getEndTime();
    Timestamp startTs = new Timestamp(start);
    Timestamp endTs = getEnd(start, end);

    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    try {/*from www  . ja  v a2s  .  com*/
        String mailbox_id = DebugConfig.disableMailboxGroups ? "" : "mailbox_id, ";
        stmt = conn.prepareStatement("INSERT INTO " + getCalendarItemTableName(mbox) + " (" + mailbox_id
                + "uid, item_id, start_time, end_time)" + " VALUES ("
                + (DebugConfig.disableMailboxGroups ? "" : "?, ") + "?, ?, ?, ?)");
        int pos = 1;
        pos = setMailboxId(stmt, mbox, pos);
        stmt.setString(pos++, calItem.getUid());
        stmt.setInt(pos++, calItem.getId());
        stmt.setTimestamp(pos++, startTs);
        stmt.setTimestamp(pos++, endTs);
        stmt.executeUpdate();
    } catch (SQLException e) {
        throw ServiceException.FAILURE("writing invite to calendar item table: UID=" + calItem.getUid(), e);
    } finally {
        DbPool.closeStatement(stmt);
    }
}

From source file:com.zimbra.cs.db.DbMailItem.java

/**
 * @param start     start time of range, in milliseconds. {@code -1} means to leave the start time unconstrained.
 * @param end       end time of range, in milliseconds. {@code -1} means to leave the end time unconstrained.
 *//*w ww  . ja  v a  2  s. co  m*/
private static PreparedStatement calendarItemStatement(DbConnection conn, String fields, Mailbox mbox,
        MailItem.Type type, long start, long end, int folderId, int[] excludeFolderIds) throws SQLException {
    boolean folderSpecified = folderId != Mailbox.ID_AUTO_INCREMENT;

    // Note - Negative times are valid.  However, treat -1 as meaning "unconstrained"
    // Want appointments that start before the end time
    String endConstraint = (end != -1) ? " AND ci.start_time < ?" : "";
    // Want appointments that end after the start time
    String startConstraint = (start != -1) ? " AND ci.end_time > ?" : "";
    String typeConstraint = type == MailItem.Type.UNKNOWN ? "type IN " + CALENDAR_TYPES : typeIn(type);

    String excludeFolderPart = "";
    if (excludeFolderIds != null && excludeFolderIds.length > 0) {
        excludeFolderPart = " AND " + DbUtil.whereNotIn("folder_id", excludeFolderIds.length);
    }

    PreparedStatement stmt = conn.prepareStatement("SELECT " + fields + " FROM "
            + getCalendarItemTableName(mbox, "ci") + ", " + getMailItemTableName(mbox, "mi")
            + " WHERE mi.id = ci.item_id" + endConstraint + startConstraint + " AND mi." + typeConstraint
            + (DebugConfig.disableMailboxGroups ? ""
                    : " AND ci.mailbox_id = ? AND mi.mailbox_id = ci.mailbox_id")
            + (folderSpecified ? " AND folder_id = ?" : "") + excludeFolderPart);

    int pos = 1;
    if (!endConstraint.isEmpty()) {
        stmt.setTimestamp(pos++, new Timestamp(end));
    }
    if (!startConstraint.isEmpty()) {
        stmt.setTimestamp(pos++, new Timestamp(start));
    }
    pos = setMailboxId(stmt, mbox, pos);
    if (folderSpecified) {
        stmt.setInt(pos++, folderId);
    }
    if (excludeFolderIds != null) {
        for (int id : excludeFolderIds) {
            stmt.setInt(pos++, id);
        }
    }

    return stmt;
}

From source file:com.zimbra.cs.db.DbMailItem.java

public static void updateInCalendarItemTable(CalendarItem calItem) throws ServiceException {
    Mailbox mbox = calItem.getMailbox();
    long start = calItem.getStartTime();
    long end = calItem.getEndTime();
    Timestamp startTs = new Timestamp(start);
    Timestamp endTs = getEnd(start, end);

    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    try {//from w  w  w.  j  ava2  s .c om
        String command = Db.supports(Db.Capability.REPLACE_INTO) ? "REPLACE" : "INSERT";
        String mailbox_id = DebugConfig.disableMailboxGroups ? "" : "mailbox_id, ";
        stmt = conn.prepareStatement(command + " INTO " + getCalendarItemTableName(mbox) + " (" + mailbox_id
                + "uid, item_id, start_time, end_time)" + " VALUES (" + MAILBOX_ID_VALUE + "?, ?, ?, ?)");
        int pos = 1;
        pos = setMailboxId(stmt, mbox, pos);
        stmt.setString(pos++, calItem.getUid());
        stmt.setInt(pos++, calItem.getId());
        stmt.setTimestamp(pos++, startTs);
        stmt.setTimestamp(pos++, endTs);
        stmt.executeUpdate();
    } catch (SQLException e) {
        if (Db.errorMatches(e, Db.Error.DUPLICATE_ROW)) {
            try {
                DbPool.closeStatement(stmt);

                stmt = conn.prepareStatement("UPDATE " + getCalendarItemTableName(mbox)
                        + " SET item_id = ?, start_time = ?, end_time = ? WHERE " + IN_THIS_MAILBOX_AND
                        + "uid = ?");
                int pos = 1;
                stmt.setInt(pos++, calItem.getId());
                stmt.setTimestamp(pos++, startTs);
                stmt.setTimestamp(pos++, endTs);
                pos = setMailboxId(stmt, mbox, pos);
                stmt.setString(pos++, calItem.getUid());
                stmt.executeUpdate();
            } catch (SQLException nested) {
                throw ServiceException.FAILURE("updating data in calendar item table " + calItem.getUid(),
                        nested);
            }
        } else {
            throw ServiceException.FAILURE("writing invite to calendar item table " + calItem.getUid(), e);
        }
    } finally {
        DbPool.closeStatement(stmt);
    }
}

From source file:com.mirth.connect.donkey.server.data.jdbc.JdbcDao.java

@Override
public void insertMetaData(ConnectorMessage connectorMessage, List<MetaDataColumn> metaDataColumns) {
    logger.debug(connectorMessage.getChannelId() + "/" + connectorMessage.getMessageId() + "/"
            + connectorMessage.getMetaDataId() + ": inserting custom meta data");
    PreparedStatement statement = null;

    try {/*from  ww  w .  j  a v a 2  s .c o m*/
        List<String> metaDataColumnNames = new ArrayList<String>();
        Map<String, Object> metaDataMap = connectorMessage.getMetaDataMap();

        for (MetaDataColumn metaDataColumn : metaDataColumns) {
            Object value = metaDataMap.get(metaDataColumn.getName());

            if (value != null) {
                metaDataColumnNames.add(metaDataColumn.getName());
            }
        }

        // Don't do anything if all values were null
        if (!metaDataColumnNames.isEmpty()) {
            Map<String, Object> values = new HashMap<String, Object>();
            values.put("localChannelId", getLocalChannelId(connectorMessage.getChannelId()));
            values.put("metaDataColumnNames",
                    quoteChar + StringUtils.join(metaDataColumnNames, quoteChar + "," + quoteChar) + quoteChar);
            values.put("metaDataColumnPlaceholders",
                    "?" + StringUtils.repeat(", ?", metaDataColumnNames.size() - 1));

            statement = connection.prepareStatement(querySource.getQuery("insertMetaData", values));
            statement.setInt(1, connectorMessage.getMetaDataId());
            statement.setLong(2, connectorMessage.getMessageId());
            int n = 3;

            for (MetaDataColumn metaDataColumn : metaDataColumns) {
                Object value = metaDataMap.get(metaDataColumn.getName());

                if (value != null) {
                    // @formatter:off
                    switch (metaDataColumn.getType()) {
                    case STRING:
                        statement.setString(n, (String) value);
                        break;
                    case NUMBER:
                        statement.setBigDecimal(n, (BigDecimal) value);
                        break;
                    case BOOLEAN:
                        statement.setBoolean(n, (Boolean) value);
                        break;
                    case TIMESTAMP:
                        statement.setTimestamp(n, new Timestamp(((Calendar) value).getTimeInMillis()));
                        break;
                    }
                    // @formatter:on

                    n++;
                }
            }

            statement.executeUpdate();
        }
    } catch (Exception e) {
        throw new DonkeyDaoException("Failed to insert connector message meta data", e);
    } finally {
        close(statement);
    }
}

From source file:edu.ku.brc.specify.conversion.SpecifyDBConverter.java

/**
 * @param oldDBConn//from w  ww  . j a  va 2  s .  c o  m
 * @param newDBConn
 */
protected void fixGiftPreps(final Connection oldDBConn, final Connection newDBConn) {
    // Category == 0 -> Is a Loan, 1 is a Gift

    System.out.println("------------------------ Gifts ----------------------------");

    int fixCnt = 0;
    int totalCnt = 0;
    int skippedCnt = 0;
    int notFndCnt = 0;

    IdMapperMgr.getInstance().setDBs(oldDBConn, newDBConn);
    IdTableMapper colObjMapper = IdMapperMgr.getInstance().addTableMapper("collectionobject",
            "CollectionObjectID", false);
    IdTableMapper giftMapper = new IdTableMapper("gift", "GiftID", false, false);

    Statement stmt = null;
    PreparedStatement newStmt = null;
    PreparedStatement pStmt = null;
    try {
        pStmt = newDBConn.prepareStatement(
                "UPDATE giftpreparation SET Quantity=?, TimestampModified=?, TimestampCreated=?, "
                        + "GiftID=?, DescriptionOfMaterial=?, OutComments=?, InComments=?, PreparationID=?, Version=? "
                        + "WHERE GiftPreparationID = ?");

        newStmt = newDBConn.prepareStatement(
                "SELECT GiftPreparationID FROM giftpreparation WHERE GiftID = ? AND PreparationID = ?");

        String sql = "SELECT lp.LoanPhysicalObjectID, lp.PhysicalObjectID, lp.LoanID, lp.Quantity, lp.DescriptionOfMaterial, lp.OutComments, lp.InComments, "
                + "lp.QuantityResolved, lp.QuantityReturned, lp.TimestampCreated, lp.TimestampModified, lp.LastEditedBy, l.Closed "
                + "FROM loanphysicalobject lp INNER JOIN loan l ON l.LoanID = lp.LoanID WHERE l.Category = 1";

        stmt = oldDBConn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            int loanId = rs.getInt(5);
            int oldPrepId = rs.getInt(6);
            Integer newPrepId = colObjMapper.get(oldPrepId);

            //System.out.println(id);
            Integer giftID = giftMapper.get(loanId);
            if (giftID != null) {
                String s = String.format(
                        "SELECT COUNT(*) FROM giftpreparation WHERE GiftID = %d AND PreparationID = %d", giftID,
                        newPrepId);
                //System.out.println(s);
                int cnt = BasicSQLUtils.getCountAsInt(s);
                if (cnt == 1) {
                    newStmt.setInt(1, giftID);
                    newStmt.setInt(2, newPrepId);
                } else {
                    log.error(String.format("*** Error get unique GiftPrep GiftID = %d AND PrepId = %d  %s",
                            giftID, newPrepId, s) + "  " + rs.getTimestamp(2));
                }

                ResultSet rs2 = newStmt.executeQuery();
                if (rs2.next()) {
                    pStmt.setInt(1, rs.getInt(4));
                    pStmt.setTimestamp(2, rs.getTimestamp(2));
                    pStmt.setInt(3, rs2.getInt(1));

                    if (pStmt.executeUpdate() != 1) {
                        log.error(String.format("*** Error updating OldID %d  newID %d", rs.getInt(1), giftID));
                    } else {
                        fixCnt++;
                    }

                } else {
                    notFndCnt++;
                }
                rs2.close();
            } else {
                //log.error(String.format("*** Error not new Id for OldID %d", rs.getInt(1)));
                skippedCnt++;
            }
            totalCnt++;
        }
        rs.close();

    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        try {
            if (stmt != null)
                stmt.close();
            if (newStmt != null)
                newStmt.close();
            if (pStmt != null)
                pStmt.close();

        } catch (Exception ex) {
        }
    }

    System.out.println(String.format("Total: %d  Fixed: %d  Skipped: %d  NotFnd: %d", totalCnt, fixCnt,
            skippedCnt, notFndCnt));
}

From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java

@Override
public EventChanges getSync(AccessToken token, ObmUser calendarUser, Date lastSync, SyncRange syncRange,
        EventType typeFilter, boolean onEventDate) {

    PreparedStatement evps = null;
    ResultSet evrs = null;//from  w w w.j av  a2 s.c  om
    Connection con = null;
    Calendar cal = getGMTCalendar();
    StringBuilder fetchIds = new StringBuilder();
    fetchIds.append("SELECT e.event_id, att.eventlink_state, e.event_ext_id, ex.eventexception_parent_id ");
    fetchIds.append(" FROM Event e ");
    fetchIds.append("INNER JOIN EventLink att ON att.eventlink_event_id=e.event_id ");
    fetchIds.append("INNER JOIN UserEntity ue ON att.eventlink_entity_id=ue.userentity_entity_id ");
    fetchIds.append("INNER JOIN EventLink attupd ON attupd.eventlink_event_id=e.event_id ");
    fetchIds.append("LEFT JOIN EventException ex ON ex.eventexception_child_id = e.event_id ");
    fetchIds.append("WHERE e.event_type=? AND ue.userentity_user_id=? ");

    // dirty hack to disable need-action to opush & tbird
    if (token.getOrigin().contains("push")) {
        fetchIds.append(" AND att.eventlink_state != 'NEEDS-ACTION' ");
    }

    if (lastSync != null) {
        fetchIds.append(
                " AND (e.event_timecreate >= ? OR e.event_timeupdate >= ? OR attupd.eventlink_timeupdate >= ?");
        if (onEventDate) {
            fetchIds.append(" OR e.event_date >= ? OR e.event_repeatkind != 'none'");
        }
        fetchIds.append(")");
    }

    if (syncRange != null) {
        fetchIds.append("AND (");
        fetchIds.append(
                "(e.event_repeatkind != 'none' AND (e.event_endrepeat IS NULL OR e.event_endrepeat >= ?)) OR ");

        fetchIds.append("(e.event_date >= ? ");
        if (syncRange.getBefore() != null) {
            fetchIds.append("AND e.event_date <= ? ");
        }
        fetchIds.append(") )");
        logger.info(token.getUserLogin() + " will use the sync range [ " + syncRange.getAfter() + " - "
                + syncRange.getBefore() + " ]");
    }

    fetchIds.append(" GROUP BY e.event_id, att.eventlink_state, e.event_ext_id, ex.eventexception_parent_id");

    List<DeletedEvent> declined = new LinkedList<DeletedEvent>();
    Set<Event> parentOfDeclinedRecurrentEvent = Sets.newHashSet();

    StringBuilder fetched = new StringBuilder();
    fetched.append("(0");
    boolean fetchedData = false;
    try {
        con = obmHelper.getConnection();
        evps = con.prepareStatement(fetchIds.toString());
        int idx = 1;
        evps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VCOMPONENT, typeFilter.toString()));
        evps.setObject(idx++, calendarUser.getUid());
        if (lastSync != null) {
            evps.setTimestamp(idx++, new Timestamp(lastSync.getTime()));
            evps.setTimestamp(idx++, new Timestamp(lastSync.getTime()));
            evps.setTimestamp(idx++, new Timestamp(lastSync.getTime()));
            if (onEventDate) {
                evps.setTimestamp(idx++, new Timestamp(lastSync.getTime()));
            }
        }
        if (syncRange != null) {
            // Recurrent events
            evps.setTimestamp(idx++, new Timestamp(syncRange.getAfter().getTime()));
            // Non-recurrent events
            evps.setTimestamp(idx++, new Timestamp(syncRange.getAfter().getTime()));
            if (syncRange.getBefore() != null) {
                evps.setTimestamp(idx++, new Timestamp(syncRange.getBefore().getTime()));
            }
        }

        evrs = evps.executeQuery();
        while (evrs.next()) {
            int recurentParentId = evrs.getInt(4);
            State state = State.getValueOf(evrs.getString(2));
            Integer eventId = evrs.getInt(1);
            if (state == State.DECLINED) {
                if (recurentParentId == 0) {
                    declined.add(
                            DeletedEvent.builder().eventObmId(eventId).eventExtId(evrs.getString(3)).build());
                } else {
                    Event e = findEventById(token, new EventObmId(recurentParentId));
                    parentOfDeclinedRecurrentEvent.add(e);
                }
            } else {
                fetchedData = true;
                if (recurentParentId > 0) {
                    fetched.append(",");
                    fetched.append(recurentParentId);
                }
                fetched.append(",");
                fetched.append(eventId);
            }
        }
    } catch (Throwable t) {
        logger.error(t.getMessage(), t);
    } finally {
        obmHelper.cleanup(con, evps, evrs);
    }
    fetched.append(")");

    List<Event> changedEvent = new LinkedList<Event>();
    Map<EventObmId, Event> eventById = new HashMap<EventObmId, Event>();

    evps = null;
    evrs = null;
    con = null;

    Date lastSyncToBuild = null;
    if (fetchedData) {
        String ev = "SELECT " + EVENT_SELECT_FIELDS + ", eventexception_date as recurrence_id "
                + " FROM Event e " + "INNER JOIN EventEntity ON e.event_id=evententity_event_id "
                + "INNER JOIN UserObm o ON e.event_owner=o.userobm_id "
                + "INNER JOIN UserObm c ON e.event_usercreate=c.userobm_id "
                + "INNER JOIN Domain ON e.event_domain_id=domain_id "
                + "LEFT JOIN EventCategory1 ON e.event_category1_id=eventcategory1_id "
                + "LEFT JOIN EventException ON e.event_id = eventexception_child_id " + "WHERE e.event_id IN "
                + fetched.toString();

        try {
            con = obmHelper.getConnection();
            evps = con.prepareStatement(ev);
            evrs = evps.executeQuery();
            boolean lastSyncSet = false;
            while (evrs.next()) {
                if (!lastSyncSet) {
                    lastSyncToBuild = JDBCUtils.getDate(evrs, "last_sync");
                    lastSyncSet = true;
                }

                Event e = eventFromCursor(cal, evrs);
                eventById.put(e.getObmId(), e);
                changedEvent.add(e);
            }
        } catch (SQLException e) {
            logger.error("error getting events", e);
        } finally {
            obmHelper.cleanup(con, evps, evrs);
        }
    }

    if (lastSyncToBuild == null) {
        if (lastSync != null) {
            Connection conDate = null;
            try {
                conDate = obmHelper.getConnection();
                Date newLastSync = obmHelper.selectNow(conDate);
                if (newLastSync != null) {
                    lastSyncToBuild = newLastSync;
                } else {
                    lastSyncToBuild = lastSync;
                }
            } catch (SQLException e) {
                logger.error("error updating lastsync field", e);
            } finally {
                obmHelper.cleanup(conDate, null, null);
            }

        } else {
            Calendar ls = Calendar.getInstance();
            ls.set(Calendar.YEAR, 1970);
            lastSyncToBuild = ls.getTime();
        }
    }

    Connection conComp = null;
    Date touchDateForFakeExDates;
    try {
        conComp = obmHelper.getConnection();
        touchDateForFakeExDates = obmHelper.selectNow(conComp);
        if (!changedEvent.isEmpty()) {
            IntegerIndexedSQLCollectionHelper changedIds = new IntegerIndexedSQLCollectionHelper(changedEvent);
            loadAttendees(conComp, eventById, calendarUser.getDomain().getName());
            loadAlerts(conComp, token, eventById, changedIds);
            loadExceptions(conComp, cal, eventById, changedIds);
            loadEventExceptions(conComp, token, eventById, changedIds);
        }

        touchParentOfDeclinedRecurrentEvents(parentOfDeclinedRecurrentEvent, changedEvent,
                touchDateForFakeExDates);

        if (!changedEvent.isEmpty()) {
            replaceDeclinedEventExceptionByException(calendarUser, changedEvent);
        }
    } catch (SQLException e) {
        logger.error("error loading attendees, alerts, exceptions, eventException", e);
    } finally {
        obmHelper.cleanup(conComp, null, null);
    }

    Iterable<DeletedEvent> deletedEvents = Iterables.concat(
            findDeletedEvents(calendarUser, lastSync, typeFilter, declined),
            findDeletedEventsLinks(calendarUser, lastSync));

    EventChanges syncEventChanges = EventChanges.builder().lastSync(lastSyncToBuild).updates(changedEvent)
            .deletes(Sets.newHashSet(deletedEvents)).build();

    return syncEventChanges;
}

From source file:com.zimbra.cs.db.DbMailItem.java

/**
 * @param start start time of range, in milliseconds. {@code -1} means to leave the start time unconstrained.
 * @param end end time of range, in milliseconds. {@code -1} means to leave the end time unconstrained.
 *//*w ww  .  ja va 2  s. c  o  m*/
public static List<CalendarItem.CalendarMetadata> getCalendarItemMetadata(Folder folder, long start, long end)
        throws ServiceException {
    Mailbox mbox = folder.getMailbox();

    ArrayList<CalendarItem.CalendarMetadata> result = new ArrayList<CalendarItem.CalendarMetadata>();

    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        // Note - Negative times are valid.  However, treat -1 as meaning "unconstrained"
        // Want appointments that end after the start time
        String startConstraint = (start != -1) ? " AND ci.end_time > ?" : "";
        // Want appointments that start before the end time
        String endConstraint = (end != -1) ? " AND ci.start_time < ?" : "";
        String folderConstraint = " AND mi.folder_id = ?";
        stmt = conn.prepareStatement(
                "SELECT mi.mailbox_id, mi.id, ci.uid, mi.mod_metadata, mi.mod_content, ci.start_time, ci.end_time"
                        + " FROM " + getMailItemTableName(mbox, "mi") + ", "
                        + getCalendarItemTableName(mbox, "ci")
                        + " WHERE mi.mailbox_id = ci.mailbox_id AND mi.id = ci.item_id"
                        + (DebugConfig.disableMailboxGroups ? "" : " AND mi.mailbox_id = ? ") + startConstraint
                        + endConstraint + folderConstraint);
        int pos = 1;
        pos = setMailboxId(stmt, mbox, pos);
        if (!startConstraint.isEmpty()) {
            stmt.setTimestamp(pos++, new Timestamp(start));
        }
        if (!endConstraint.isEmpty()) {
            stmt.setTimestamp(pos++, new Timestamp(end));
        }
        stmt.setInt(pos++, folder.getId());
        rs = stmt.executeQuery();
        while (rs.next()) {
            result.add(new CalendarItem.CalendarMetadata(rs.getInt(1), rs.getInt(2), rs.getString(3),
                    rs.getInt(4), rs.getInt(5), rs.getTimestamp(6).getTime(), rs.getTimestamp(7).getTime()));
        }
    } catch (SQLException e) {
        throw ServiceException.FAILURE("fetching CalendarItem Metadata for mbox " + mbox.getId(), e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
    }
    return result;
}

From source file:i5.las2peer.services.mobsos.SurveyService.java

@POST
@Consumes(MediaType.APPLICATION_JSON)/* w  w  w  .  j a va2s .c om*/
@Path("surveys/{id}/responses")
@Summary("submit response data to given survey.")
@ApiResponses(value = { @ApiResponse(code = 200, message = "Survey response submitted successfully."),
        @ApiResponse(code = 400, message = "Survey response invalid -or- questionnaire form invalid. Cause: ..."),
        @ApiResponse(code = 404, message = "Survey does not exist -or- No questionnaire defined for survey."),
        @ApiResponse(code = 400, message = "Survey response already submitted."), })
public HttpResponse submitSurveyResponseJSON(@PathParam("id") int id, @ContentParam String answerJSON) {
    Date now = new Date();
    String onAction = "submitting response to survey " + id;
    try {

        // retrieve survey by id;
        HttpResponse rs = getSurvey(id);
        if (rs.getStatus() != 200) {
            return rs;
        }

        JSONObject s = (JSONObject) JSONValue.parse(rs.getResult());

        // check if survey expired/not started
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
        df.setTimeZone(TimeZone.getTimeZone("GMT"));

        Date start = df.parse((String) s.get("start"));
        Date end = df.parse((String) s.get("end"));

        if (now.getTime() > end.getTime()) {
            HttpResponse resp = new HttpResponse("Cannot submit response. Survey expired.");
            resp.setStatus(403);
            return resp;
        } else if (now.getTime() < start.getTime()) {
            HttpResponse resp = new HttpResponse("Cannot submit response. Survey has not begun, yet.");
            resp.setStatus(403);
            return resp;
        }

        // check for questionnaire form
        int qid = Integer.parseInt(s.get("qid") + "");

        if (qid == -1) {
            HttpResponse result = new HttpResponse("No questionnaire defined for survey " + id + "!");
            result.setStatus(404);
            return result;
        }

        // retrieve questionnaire form for survey to do answer validation
        HttpResponse r = downloadQuestionnaireForm(qid);

        if (200 != r.getStatus()) {
            // if questionnaire form does not exist, pass on response containing error status
            return r;
        }

        Document form;
        JSONObject answer;

        // parse form to XML document incl. validation
        try {
            form = validateQuestionnaireData(r.getResult());
        } catch (SAXException e) {
            HttpResponse result = new HttpResponse("Questionnaire form is invalid! Cause: " + e.getMessage());
            result.setStatus(400);
            return result;
        }

        try {
            //System.out.println(answerJSON);

            answer = (JSONObject) JSONValue.parseWithException(answerJSON);
        } catch (ParseException e) {
            HttpResponse result = new HttpResponse(
                    "Survey response is not valid JSON! Cause: " + e.getMessage());
            result.setStatus(400);
            return result;
        }

        JSONObject answerFieldTable;

        // validate if answer matches form.
        try {
            answerFieldTable = validateResponse(form, answer);
        } catch (IllegalArgumentException e) {
            HttpResponse result = new HttpResponse("Survey response is invalid! Cause: " + e.getMessage());
            result.setStatus(400);
            return result;
        }

        // after all validation finally persist survey response in database
        int surveyId = id;

        String sub = (String) getActiveUserInfo().get("sub");

        if (getActiveAgent().getId() == getActiveNode().getAnonymous().getId()) {
            sub += now.getTime();
        }

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rset = null;

        try {
            conn = dataSource.getConnection();
            stmt = conn.prepareStatement(
                    "insert into " + jdbcSchema + ".response(uid,sid,qkey,qval,time) values (?,?,?,?,?)");

            Iterator<String> it = answerFieldTable.keySet().iterator();
            while (it.hasNext()) {

                String qkey = it.next();
                String qval = "" + answerFieldTable.get(qkey);

                stmt.setString(1, sub);
                stmt.setInt(2, surveyId);
                stmt.setString(3, qkey);
                stmt.setString(4, qval);
                stmt.setTimestamp(5, new Timestamp(now.getTime()));
                stmt.addBatch();

            }
            stmt.executeBatch();

            HttpResponse result = new HttpResponse("Response to survey " + id + " submitted successfully.");
            result.setStatus(200);
            return result;

        } catch (SQLException | UnsupportedOperationException e) {
            if (0 <= e.getMessage().indexOf("Duplicate")) {
                HttpResponse result = new HttpResponse("Survey response already submitted!");
                result.setStatus(409);
                return result;
            } else {
                e.printStackTrace();
                return internalError(onAction);
            }
        } finally {
            try {
                if (rset != null)
                    rset.close();
            } catch (Exception e) {
                e.printStackTrace();
                return internalError(onAction);
            }
            try {
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
                e.printStackTrace();
                return internalError(onAction);
            }
            try {
                if (conn != null)
                    conn.close();
            } catch (Exception e) {
                e.printStackTrace();
                return internalError(onAction);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
        return internalError(onAction);
    }
}

From source file:edu.ku.brc.specify.conversion.StratToGTP.java

/**
 * @param oldDBConn/*from  ww w.j a v a 2  s. c o  m*/
 * @return
 */
private boolean convStratGTPToStratKUINVP() {
    PreparedStatement pStmt1 = null;
    try {
        String sqlCreate = "CREATE TABLE `stratigraphy2` (  `StratigraphyID` int(10) NOT NULL,  `GeologicTimePeriodID` int(10) DEFAULT NULL,  `SuperGroup` varchar(50) CHARACTER SET utf8 DEFAULT NULL,  `Group` varchar(50) CHARACTER SET utf8 DEFAULT NULL,  `Formation` varchar(50) CHARACTER SET utf8 DEFAULT NULL, "
                + "`Member` varchar(50) CHARACTER SET utf8 DEFAULT NULL,  `Bed` varchar(50) CHARACTER SET utf8 DEFAULT NULL,  `Remarks` longtext,  `Text1` varchar(300) CHARACTER SET utf8 DEFAULT NULL,  `Text2` varchar(300) CHARACTER SET utf8 DEFAULT NULL,  `Number1` double DEFAULT NULL, "
                + "`Number2` double DEFAULT NULL,  `TimestampCreated` datetime DEFAULT NULL,  `TimestampModified` datetime DEFAULT NULL,  `LastEditedBy` varchar(50) CHARACTER SET utf8 DEFAULT NULL,  `YesNo1` smallint(5) DEFAULT NULL,  `YesNo2` smallint(5) DEFAULT NULL,  PRIMARY KEY (`StratigraphyID`) "
                + ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        DBMSUserMgr dbMgr = DBMSUserMgr.getInstance();
        dbMgr.setConnection(oldDBConn);
        if (dbMgr.doesDBHaveTable("stratigraphy2")) {
            try {
                BasicSQLUtils.update(oldDBConn, "DROP TABLE stratigraphy2");
            } catch (Exception ex) {
            }
        }
        dbMgr.setConnection(null);

        BasicSQLUtils.update(oldDBConn, sqlCreate);

        String postfix = " FROM collectingevent AS ce "
                + "Inner Join stratigraphy AS s ON ce.CollectingEventID = s.StratigraphyID "
                + "Left Join geologictimeperiod AS g ON s.GeologicTimePeriodID = g.GeologicTimePeriodID "
                + "WHERE s.`Group` IS NOT NULL OR g.Name IS NOT NULL OR s.Member IS NOT NULL OR s.Bed IS NOT NULL OR g.GeologicTimePeriodID IS NOT NULL";

        String sql = "SELECT ce.CollectingEventID, s.`Group`, g.Name, s.Member, s.Bed, s.TimestampCreated, s.TimestampModified "
                + postfix;
        log.debug(sql);

        String cntSQL = "SELECT COUNT(*) " + postfix;
        int habCnt = BasicSQLUtils.getCountAsInt(oldDBConn, cntSQL);

        log.debug("****** Startigraphy Count: " + habCnt);

        //Timestamp now = new Timestamp(System .currentTimeMillis());
        //              
        String placeholder = "Placeholder";

        pStmt1 = oldDBConn.prepareStatement(
                "INSERT INTO stratigraphy2 (StratigraphyID, SuperGroup, `Group`, Formation, Member, Bed, TimestampCreated, TimestampModified) VALUES(?,?,?,?,?,?,?,?)");

        int cnt = 0;
        Vector<Object[]> rows = BasicSQLUtils.query(oldDBConn, sql);
        for (Object[] row : rows) {
            Integer ceID = (Integer) row[0];
            String superGrp = placeholder;
            String group = (String) row[1];
            String formation = (String) row[2];
            String member = (String) row[3];
            String bed = (String) row[4];
            Timestamp crTS = (Timestamp) row[5];
            Timestamp mdTS = (Timestamp) row[6];

            /*if (StringUtils.isNotEmpty(formation))
            {
            if (StringUtils.isEmpty(group)) group = placeholder;
            }
                    
            if (StringUtils.isNotEmpty(member))
            {
            if (StringUtils.isEmpty(group)) group = placeholder;
            if (StringUtils.isEmpty(formation)) formation = placeholder;
            }*/

            if (StringUtils.isNotEmpty(bed)) {
                //if (StringUtils.isEmpty(group)) group = placeholder;
                //if (StringUtils.isEmpty(formation)) formation = placeholder;
                //if (StringUtils.isEmpty(member)) member = placeholder;
                if (bed.length() > 50) {
                    bed = bed.substring(0, 50);
                }
            } else {
                //bed = placeholder;
            }

            if (ceID != null) {
                pStmt1.setInt(1, ceID);
                pStmt1.setString(2, superGrp);
                pStmt1.setString(3, group);
                pStmt1.setString(4, formation);
                pStmt1.setString(5, member);
                pStmt1.setString(6, bed);
                pStmt1.setTimestamp(7, crTS);
                pStmt1.setTimestamp(8, mdTS);
                pStmt1.execute();
                cnt++;
                if (cnt % 100 == 0) {
                    log.debug(cnt + " / " + habCnt);
                }
            }
        }
        return true;

    } catch (Exception ex) {
        ex.printStackTrace();

    } finally {
        try {
            if (pStmt1 != null)
                pStmt1.close();

        } catch (Exception ex) {
        }
    }

    return false;
}

From source file:i5.las2peer.services.mobsos.SurveyService.java

/**
 * Stores a new survey described with JSON into the MobSOS database.
 * The MobSOS database thereby generates a new id returned by this method.
 * @throws ParseException /*from   www  . j a va2 s .com*/
 */
private int storeNewSurvey(JSONObject survey) throws IllegalArgumentException, SQLException, ParseException {

    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rset = null;

    String sub = (String) getActiveUserInfo().get("sub");

    try {
        conn = dataSource.getConnection();
        stmt = conn.prepareStatement("insert into " + jdbcSchema
                + ".survey(owner, organization, logo, name, description, resource, start, end, lang ) values (?,?,?,?,?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);

        stmt.clearParameters();
        stmt.setString(1, sub); // active agent becomes owner automatically
        stmt.setString(2, (String) survey.get("organization"));
        stmt.setString(3, (String) survey.get("logo"));
        stmt.setString(4, (String) survey.get("name"));
        stmt.setString(5, (String) survey.get("description"));
        stmt.setString(6, (String) survey.get("resource"));
        stmt.setTimestamp(7,
                new Timestamp(DatatypeConverter.parseDateTime((String) survey.get("start")).getTimeInMillis()));
        stmt.setTimestamp(8,
                new Timestamp(DatatypeConverter.parseDateTime((String) survey.get("end")).getTimeInMillis()));
        stmt.setString(9, (String) survey.get("lang"));

        stmt.executeUpdate();
        ResultSet rs = stmt.getGeneratedKeys();

        if (rs.next()) {
            return rs.getInt(1);
        } else {
            throw new NoSuchElementException("No new survey was created!");
        }

    } catch (UnsupportedOperationException e) {
        e.printStackTrace();
    } finally {
        try {
            if (rset != null)
                rset.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (stmt != null)
                stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    throw new NoSuchElementException("No new survey was created!");
}