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:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java

private int fillEventStatement(PreparedStatement ps, Event ev, AccessToken at, int i) throws SQLException {
    int idx = i;/*from ww w.j  a va 2 s. co  m*/
    ps.setString(idx++, ev.getExtId().getExtId());
    ps.setString(idx++, ev.getTimezoneName());
    ps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VOPACITY, ev.getOpacity().toString()));
    ps.setString(idx++, ev.getTitle());
    ps.setString(idx++, ev.getLocation());
    Integer cat = catIdFromString(ps.getConnection(), ev.getCategory(), at.getDomain().getId());
    if (cat != null) {
        ps.setInt(idx++, cat);
    } else {
        ps.setNull(idx++, Types.INTEGER);
    }
    ps.setInt(idx++, RFC2445.getPriorityOrDefault(ev.getPriority()));
    ps.setInt(idx++, ev.getPrivacy().toInteger());
    if (ev.getStartDate() != null) {
        ps.setTimestamp(idx++, new Timestamp(ev.getStartDate().getTime()));
    } else {
        ps.setNull(idx++, Types.DATE);
    }
    ps.setInt(idx++, ev.getDuration());
    ps.setBoolean(idx++, ev.isAllday());
    EventRecurrence r = ev.getRecurrence();
    ps.setString(idx++, r.getKind().toString());
    ps.setInt(idx++, r.getFrequence());
    ps.setString(idx++, new RecurrenceDaysSerializer().serialize(r.getDays()));
    if (r.getEnd() != null) {
        ps.setTimestamp(idx++, new Timestamp(r.getEnd().getTime()));
    } else {
        ps.setNull(idx++, Types.DATE);
    }
    ps.setNull(idx++, Types.VARCHAR); // color
    ps.setNull(idx++, Types.DATE); // FIXME completed
    ps.setNull(idx++, Types.VARCHAR); // FIXME url
    ps.setString(idx++, ev.getDescription());
    ps.setInt(idx++, at.getDomain().getId());
    ps.setString(idx++, at.getOrigin());
    ps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VCOMPONENT, ev.getType().toString()));
    ps.setInt(idx++, ev.getSequence());
    return idx;
}

From source file:com.pari.nm.utils.db.ReportDBHelper.java

public static final void addUserDefinedReport(UserDefinedReport report) throws Exception {
    String query = "INSERT INTO user_defined_reports (report_id, title, creator_name, created_time, report_definition, service_definition, decorator_definition,customer_id) VALUES (?,?,?,?,?,?,?,?)";

    Connection c = null;//from ww  w  .  j  av  a 2  s .  co m
    PreparedStatement ps = null;
    try {
        c = DBHelper.getConnection();
        ps = c.prepareStatement(query);
        ps.setString(1, report.getIdentifier());
        ps.setString(2, report.getTitle());
        ps.setString(3, report.getCreatedUser());
        ps.setTimestamp(4, (new Timestamp(System.currentTimeMillis())));
        ps.setString(5, report.getReportDef().getXML("GetReportDefinition", ""));
        ps.setString(6, report.getServiceDef());
        ps.setString(7, report.getDecoratorDef());
        ps.setInt(8, report.getCustomerId());
        ps.executeUpdate();
    } catch (Exception ex) {
        String error = "Error while adding user defined report: " + report.getIdentifier();
        logger.warn(error, ex);
        throw new PariException(-1, error);
    } finally {
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (Exception e) {
            logger.debug("Error while closing the prepared staement", e);
        }
        try {
            if (c != null) {
                DBHelper.releaseConnection(c);
            }
        } catch (Exception e) {
            logger.debug("Error while releasing the connection", e);
        }
    }
}

From source file:com.alfaariss.oa.engine.tgt.jdbc.JDBCTGTFactory.java

/**
 * Uses a batch update to persist all supplied tgts.
 * @param tgts The TGTs to persist./*from   w  ww.  j  ava  2  s.  c  o  m*/
 * @throws PersistenceException If persistance fails.
 * 
 * @see IEntityManager#persist(IEntity[])
 * @see PreparedStatement#addBatch()
 */
public void persist(JDBCTGT[] tgts) throws PersistenceException {
    if (tgts == null)
        throw new IllegalArgumentException("Suplied tgt array is empty or invalid");

    List<TGTEventError> listTGTEventErrors = new Vector<TGTEventError>();

    Connection connection = null;
    PreparedStatement psInsert = null;
    PreparedStatement psDelete = null;
    PreparedStatement psDeleteAliasR = null;
    PreparedStatement psDeleteAliasF = null;
    PreparedStatement psUpdate = null;
    try {
        connection = _oDataSource.getConnection(); //Manage connection
        connection.setAutoCommit(false);

        if (_aliasStoreSP != null)
            psDeleteAliasR = connection.prepareStatement(_aliasStoreSP.getQueryAliasRemove());

        if (_aliasStoreIDP != null)
            psDeleteAliasF = connection.prepareStatement(_aliasStoreIDP.getQueryAliasRemove());

        psInsert = connection.prepareStatement(_sInsertQuery);
        psDelete = connection.prepareStatement(_sRemoveQuery);
        psUpdate = connection.prepareStatement(_sUpdateQuery);

        Vector<ITGT> vCreate = new Vector<ITGT>();
        Vector<ITGT> vUpdate = new Vector<ITGT>();
        Vector<ITGT> vRemove = new Vector<ITGT>();

        for (JDBCTGT tgt : tgts) //For all tgts
        {
            String id = tgt.getId();
            if (id == null) //New TGT
            {
                byte[] baId = new byte[ITGT.TGT_LENGTH];
                do {
                    _random.nextBytes(baId);
                    try {
                        id = ModifiedBase64.encode(baId);
                    } catch (UnsupportedEncodingException e) {
                        _logger.error("Could not create tgt id for byte[]: " + baId, e);
                        throw new PersistenceException(SystemErrors.ERROR_INTERNAL);
                    }
                } while (exists(id)); //Key allready exists   

                tgt.setId(id);
                //Update expiration time
                long expiration = System.currentTimeMillis() + _lExpiration;
                tgt.setTgtExpTime(expiration);
                psInsert.setString(1, id);
                psInsert.setTimestamp(2, new Timestamp(expiration));
                psInsert.setBytes(3, Serialize.encode(tgt.getUser()));
                psInsert.setBytes(4, Serialize.encode(tgt.getAuthenticationProfile()));
                psInsert.setBytes(5, Serialize.encode(tgt.getModifiableAuthNProfileIDs()));
                psInsert.setBytes(6, Serialize.encode(tgt.getModifiableRequestorIDs()));
                psInsert.setBytes(7, Serialize.encode(tgt.getAttributes()));
                psInsert.addBatch();

                vCreate.add(tgt);
            } else if (tgt.isExpired()) //Expired
            {
                _logger.debug("TGT Expired: " + id);

                if (psDeleteAliasR != null) {
                    psDeleteAliasR.setString(1, id);
                    psDeleteAliasR.addBatch();
                }

                if (psDeleteAliasF != null) {
                    psDeleteAliasF.setString(1, id);
                    psDeleteAliasF.addBatch();
                }

                vRemove.add(tgt);
            } else //Update
            {
                //Update expiration time
                long expiration = System.currentTimeMillis() + _lExpiration;
                tgt.setTgtExpTime(expiration);
                //Update tgt
                psUpdate.setTimestamp(1, new Timestamp(expiration));
                psUpdate.setBytes(2, Serialize.encode(tgt.getUser()));
                psUpdate.setBytes(3, Serialize.encode(tgt.getAuthenticationProfile()));
                psUpdate.setBytes(4, Serialize.encode(tgt.getModifiableAuthNProfileIDs()));
                psUpdate.setBytes(5, Serialize.encode(tgt.getModifiableRequestorIDs()));
                psUpdate.setBytes(6, Serialize.encode(tgt.getAttributes()));
                psUpdate.setString(7, id);
                psUpdate.addBatch();

                vUpdate.add(tgt);
            }
        }

        try {
            int iTotalAdded = 0;
            for (int i : psInsert.executeBatch()) {
                iTotalAdded += i;
            }
            _logger.debug(iTotalAdded + " new TGT(s) added by batch");

            for (ITGT tgt : vCreate) {
                try {
                    processEvent(TGTListenerEvent.ON_CREATE, tgt);
                } catch (TGTListenerException e) {
                    listTGTEventErrors.addAll(e.getErrors());
                }
            }
        } catch (SQLException e) {
            _logger.error("Could not execute insert batch", e);
            throw new PersistenceException(SystemErrors.ERROR_RESOURCE_INSERT);
        }

        try {
            for (ITGT tgt : vRemove) {
                IUser tgtUser = tgt.getUser();
                _eventLogger.info(new UserEventLogItem(null, tgt.getId(), null, UserEvent.TGT_EXPIRED,
                        tgtUser.getID(), tgtUser.getOrganization(), null, null, this, null));

                try {
                    processEvent(TGTListenerEvent.ON_REMOVE, tgt);
                } catch (TGTListenerException e) {
                    listTGTEventErrors.addAll(e.getErrors());
                }
            }

            int iTotalDeleted = 0;
            for (int i : psDelete.executeBatch()) {
                iTotalDeleted += i;
            }
            _logger.debug(iTotalDeleted + " TGT(s) deleted by batch");
        } catch (SQLException e) {
            _logger.error("Could not execute delete batch", e);
            throw new PersistenceException(SystemErrors.ERROR_RESOURCE_REMOVE);
        }

        if (psDeleteAliasR != null) {
            try {
                int iTotalAliasDeleted = 0;
                for (int i : psDeleteAliasR.executeBatch()) {
                    iTotalAliasDeleted += i;
                }
                _logger.debug(iTotalAliasDeleted + " (requestor based) alias(es) deleted by batch");
            } catch (SQLException e) {
                _logger.error("Could not execute delete (requestor based) alias batch", e);
                throw new PersistenceException(SystemErrors.ERROR_RESOURCE_REMOVE);
            }
        }

        if (psDeleteAliasF != null) {
            try {
                int iTotalAliasDeleted = 0;
                for (int i : psDeleteAliasF.executeBatch()) {
                    iTotalAliasDeleted += i;
                }
                _logger.debug(iTotalAliasDeleted + " (remote enitity based) alias(es) deleted by batch");
            } catch (SQLException e) {
                _logger.error("Could not execute delete (remote enitity based) alias batch", e);
                throw new PersistenceException(SystemErrors.ERROR_RESOURCE_REMOVE);
            }
        }

        try {
            int iTotalUpdated = 0;
            for (int i : psUpdate.executeBatch()) {
                iTotalUpdated += i;
            }
            _logger.debug(iTotalUpdated + " TGT(s) updated by batch");

            for (ITGT tgt : vUpdate) {
                try {
                    processEvent(TGTListenerEvent.ON_UPDATE, tgt);
                } catch (TGTListenerException e) {
                    listTGTEventErrors.addAll(e.getErrors());
                }
            }
        } catch (SQLException e) {
            _logger.error("Could not execute update batch", e);
            throw new PersistenceException(SystemErrors.ERROR_RESOURCE_UPDATE);
        }

        connection.commit();

        if (listTGTEventErrors != null) {//TGT Event processing failed, error has been logged already
            throw new TGTListenerException(listTGTEventErrors);
        }
    } catch (SQLException e) {
        _logger.error("Could not execute Batch", e);
        try {
            if (connection != null)
                connection.rollback();
        } catch (SQLException e1) {
            _logger.warn("Could not rollback batch", e);
        }

        throw new PersistenceException(SystemErrors.ERROR_INTERNAL);
    } catch (PersistenceException e) {
        try {
            if (connection != null)
                connection.rollback();
        } catch (SQLException e1) {
            _logger.warn("Could not rollback batch", e);
        }
        throw e;
    } catch (Exception e) {
        _logger.error("Could not connect to JDBC resource", e);
        throw new PersistenceException(SystemErrors.ERROR_RESOURCE_CONNECT);
    } finally {
        try {
            if (psInsert != null)
                psInsert.close();
        } catch (SQLException e) {
            _logger.debug("Could not close insert statement", e);
        }

        try {
            if (psDelete != null)
                psDelete.close();
        } catch (SQLException e) {
            _logger.debug("Could not close delete statement", e);
        }

        try {
            if (psDeleteAliasR != null)
                psDeleteAliasR.close();
        } catch (SQLException e) {
            _logger.debug("Could not close delete (requestor based) alias statement", e);
        }

        try {
            if (psDeleteAliasF != null)
                psDeleteAliasF.close();
        } catch (SQLException e) {
            _logger.debug("Could not close delete (remote entity based) alias statement", e);
        }

        try {
            if (psUpdate != null)
                psUpdate.close();
        } catch (SQLException e) {
            _logger.debug("Could not close update statement", e);
        }

        try {
            if (connection != null)
                connection.close();
        } catch (SQLException e) {
            _logger.debug("Could not close connection", e);
        }
    }
}

From source file:com.gtwm.pb.model.manageData.DataManagement.java

public void addComment(SessionDataInfo sessionData, BaseField field, int rowId, AppUserInfo user,
        String rawComment)/*from   w  ww. j a  va  2 s.  c  o  m*/
        throws SQLException, ObjectNotFoundException, CantDoThatException, CodingErrorException {
    String SQLCode = "INSERT INTO dbint_comments(created, author, internalfieldname, rowid, text) VALUES (?,?,?,?,?)";
    // Protect against cross-site scripting
    String comment = Naming.makeValidXML(Helpers.smartCharsReplace(rawComment));
    TableInfo table = field.getTableContainingField();
    Connection conn = null;
    try {
        conn = this.dataSource.getConnection();
        conn.setAutoCommit(false);
        PreparedStatement statement = conn.prepareStatement(SQLCode);
        java.sql.Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis());
        statement.setTimestamp(1, timestamp);
        statement.setString(2, user.getForename() + " " + user.getSurname());
        statement.setString(3, field.getInternalFieldName());
        statement.setInt(4, rowId);
        statement.setString(5, comment);
        int rowsAffected = statement.executeUpdate();
        statement.close();
        if (rowsAffected != 1) {
            throw new ObjectNotFoundException(
                    "Error adding comment. " + rowsAffected + " rows inserted. SQL = " + statement);
        }
        // Concatenate all comments into the hidden comments field (for
        // searching)
        BaseField concatenationField = table.getField(HiddenFields.COMMENTS_FEED.getFieldName());
        BaseField lastModifiedField = table.getField(HiddenFields.LAST_MODIFIED.getFieldName());
        SQLCode = "UPDATE " + table.getInternalTableName() + " SET "
                + concatenationField.getInternalFieldName();
        SQLCode += " = (? || coalesce(" + concatenationField.getInternalFieldName() + ", '')), ";
        SQLCode += lastModifiedField.getInternalFieldName() + " = now()";
        SQLCode += " WHERE " + table.getPrimaryKey().getInternalFieldName() + "=?";
        statement = conn.prepareStatement(SQLCode);
        statement.setString(1, comment + "\n---\n");
        statement.setInt(2, rowId);
        rowsAffected = statement.executeUpdate();
        if (rowsAffected != 1) {
            throw new ObjectNotFoundException("Error concatenating new comment with old. " + rowsAffected
                    + " rows updated. SQL = " + statement);
        }
        statement.close();
        conn.commit();
        field.setHasComments(true);
    } finally {
        if (conn != null) {
            conn.close();
        }
    }
    // Email notification
    Authenticator authenticator = (Authenticator) this.authManager.getAuthenticator();
    CompanyInfo company = user.getCompany();
    Set<String> recipients = new HashSet<String>();
    for (AppUserInfo companyUser : company.getUsers()) {
        String email = companyUser.getEmail();
        if (email != null) {
            if (email.contains("@")
                    && authenticator.userAllowedTo(PrivilegeType.VIEW_TABLE_DATA, table, companyUser)) {
                recipients.add(email);
            }
        }
    }
    if (recipients.size() > 0) {
        this.emailComments(recipients, field, rowId, user, comment);
    }
    // HTTP / websocket notification
    // UsageLogger.sendNotification(user, table, sessionData.getReport(),
    // rowId, "comment", comment);
}

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

/**
 * @param pStmt//from w ww  .j  a va2s. c  o m
 * @param type
 * @param data
 * @throws SQLException 
 */
public static void setData(final PreparedStatement pStmt, final int type, final int colInx, final Object data)
        throws SQLException {
    if (data == null) {
        pStmt.setObject(colInx, null);
        return;
    }

    boolean isStr = data instanceof String;
    switch (type) {
    case java.sql.Types.TINYINT:
    case java.sql.Types.SMALLINT:
    case java.sql.Types.INTEGER:
        if (isStr) {
            pStmt.setString(colInx, (String) data);
        } else {
            pStmt.setInt(colInx, (Integer) data);
        }
        break;

    case java.sql.Types.FLOAT:
        if (isStr) {
            pStmt.setString(colInx, (String) data);
        } else {
            pStmt.setFloat(colInx, (Float) data);
        }
        break;

    case java.sql.Types.VARCHAR:
    case java.sql.Types.CHAR:
    case java.sql.Types.LONGVARCHAR:
    case java.sql.Types.LONGNVARCHAR:
    case java.sql.Types.NCHAR:
        if (isStr) {
            pStmt.setString(colInx, (String) data);
        } else {
            pStmt.setString(colInx, (String) data);
        }
        break;

    case java.sql.Types.REAL:
    case java.sql.Types.DOUBLE:
        if (isStr) {
            pStmt.setString(colInx, (String) data);
        } else {
            pStmt.setDouble(colInx, (Double) data);
        }
        break;

    case java.sql.Types.DATE:
        if (isStr) {
            pStmt.setString(colInx, (String) data);
        } else {
            pStmt.setDate(colInx, (java.sql.Date) data);
        }
        break;

    case java.sql.Types.TIMESTAMP:
        if (isStr) {
            pStmt.setString(colInx, (String) data);
        } else {
            pStmt.setTimestamp(colInx, (Timestamp) data);
        }
        break;

    case java.sql.Types.BOOLEAN:
        if (isStr) {
            String val = (String) data;
            pStmt.setBoolean(colInx, !val.equalsIgnoreCase("true"));
        } else {
            pStmt.setBoolean(colInx, (Boolean) data);
        }
        break;

    case java.sql.Types.BIT:
        if (data instanceof Boolean) {
            pStmt.setBoolean(colInx, (Boolean) data);
        } else {
            pStmt.setBoolean(colInx, !(((Integer) data) == 0));
        }
        break;

    default:
        throw new RuntimeException(String.format("Missing case for SQL Type %d for Column: %d Data[%s]", type,
                colInx, data.getClass().getSimpleName()));
    }
}

From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java

@Override
public void insert(final Nummeraanduiding nummeraanduiding) throws DAOException {
    try {//from  w w  w  .  j a v  a  2  s.com
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection
                        .prepareStatement("insert into bag_nummeraanduiding (" + "bag_nummeraanduiding_id,"
                                + "aanduiding_record_inactief," + "aanduiding_record_correctie," + "huisnummer,"
                                + "officieel," + "huisletter," + "huisnummertoevoeging," + "postcode,"
                                + "begindatum_tijdvak_geldigheid," + "einddatum_tijdvak_geldigheid,"
                                + "in_onderzoek," + "type_adresseerbaar_object," + "bron_documentdatum,"
                                + "bron_documentnummer," + "nummeraanduiding_status," + "bag_woonplaats_id,"
                                + "bag_openbare_ruimte_id" + ") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                ps.setLong(1, nummeraanduiding.getIdentificatie());
                ps.setInt(2, nummeraanduiding.getAanduidingRecordInactief().ordinal());
                ps.setLong(3, nummeraanduiding.getAanduidingRecordCorrectie());
                ps.setInt(4, nummeraanduiding.getHuisnummer());
                ps.setInt(5, nummeraanduiding.getOfficieel().ordinal());
                if (nummeraanduiding.getHuisletter() == null)
                    ps.setNull(6, Types.VARCHAR);
                else
                    ps.setString(6, nummeraanduiding.getHuisletter());
                if (nummeraanduiding.getHuisnummertoevoeging() == null)
                    ps.setNull(7, Types.VARCHAR);
                else
                    ps.setString(7, nummeraanduiding.getHuisnummertoevoeging());
                if (nummeraanduiding.getPostcode() == null)
                    ps.setNull(8, Types.VARCHAR);
                else
                    ps.setString(8, nummeraanduiding.getPostcode());
                ps.setTimestamp(9, new Timestamp(nummeraanduiding.getBegindatumTijdvakGeldigheid().getTime()));
                if (nummeraanduiding.getEinddatumTijdvakGeldigheid() == null)
                    ps.setNull(10, Types.TIMESTAMP);
                else
                    ps.setTimestamp(10,
                            new Timestamp(nummeraanduiding.getEinddatumTijdvakGeldigheid().getTime()));
                ps.setInt(11, nummeraanduiding.getInOnderzoek().ordinal());
                ps.setInt(12, nummeraanduiding.getTypeAdresseerbaarObject().ordinal());
                ps.setDate(13, new Date(nummeraanduiding.getDocumentdatum().getTime()));
                ps.setString(14, nummeraanduiding.getDocumentnummer());
                ps.setInt(15, nummeraanduiding.getNummeraanduidingStatus().ordinal());
                if (nummeraanduiding.getGerelateerdeWoonplaats() == null)
                    ps.setNull(16, Types.INTEGER);
                else
                    ps.setLong(16, nummeraanduiding.getGerelateerdeWoonplaats());
                ps.setLong(17, nummeraanduiding.getGerelateerdeOpenbareRuimte());
                return ps;
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException("Error inserting nummeraanduiding: " + nummeraanduiding.getIdentificatie(), e);
    }
}

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

@Override
public List<String> findEventTwinKeys(String calendar, Event event, ObmDomain domain) {

    String squery = "SELECT DISTINCT event_id from Event "
            + "INNER JOIN EventLink ON eventlink_event_id=event_id "
            + "INNER JOIN UserEntity ON userentity_entity_id=eventlink_entity_id "
            + "INNER JOIN UserObm ON userobm_id=userentity_user_id "
            + "WHERE event_title=? AND userobm_login=? ";
    if (event.isAllday()) {
        squery += "AND event_date >= ? AND event_date <= ? AND (event_duration >= 86400 OR event_allday) ";
    } else {//from   ww w .  ja v  a2s  .  co m
        squery += "AND event_date=? AND event_duration=? ";
    }
    squery += "AND userobm_domain_id=?";

    PreparedStatement query = null;
    Connection con = null;
    ResultSet rs = null;
    List<String> ret = new LinkedList<String>();

    try {
        con = obmHelper.getConnection();
        query = con.prepareStatement(squery);
        int index = 1;
        query.setString(index++, event.getTitle());
        query.setString(index++, calendar);
        if (event.isAllday()) {

            java.util.Calendar dayAfter = java.util.Calendar.getInstance();
            dayAfter.setTimeZone(TimeZone.getTimeZone("GMT"));
            dayAfter.setTimeInMillis(event.getStartDate().getTime());
            dayAfter.add(java.util.Calendar.HOUR_OF_DAY, 11);

            java.util.Calendar dayBefore = java.util.Calendar.getInstance();
            dayBefore.setTimeZone(TimeZone.getTimeZone("GMT"));
            dayBefore.setTimeInMillis(event.getStartDate().getTime());
            dayBefore.add(java.util.Calendar.HOUR_OF_DAY, -11);

            query.setTimestamp(index++, new java.sql.Timestamp(dayBefore.getTimeInMillis()));

            query.setTimestamp(index++, new java.sql.Timestamp(dayAfter.getTimeInMillis()));
        } else {
            query.setTimestamp(index++, new java.sql.Timestamp(event.getStartDate().getTime()));
            query.setInt(index++, event.getDuration());
        }

        if (domain != null) {
            query.setInt(index++, domain.getId());
        }
        rs = query.executeQuery();
        while (rs.next()) {
            ret.add(rs.getString(1));
        }
        logger.info("Found " + ret.size() + " results with title " + event.getTitle() + " date: "
                + event.getStartDate() + " duration: " + event.getDuration() + " domain_id: "
                + (domain != null ? domain.getId() : "null"));
    } catch (Exception e) {
        logger.error(e.getMessage(), e);
    } finally {
        obmHelper.cleanup(con, query, rs);
    }
    return ret;
}

From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java

@Override
public void update(final Nummeraanduiding nummeraanduiding) throws DAOException {
    try {//from  w  ww . j av a2  s.  c o m
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("update bag_nummeraanduiding set"
                        + " aanduiding_record_inactief = ?," + " huisnummer = ?," + " officieel = ?,"
                        + " huisletter = ?," + " huisnummertoevoeging = ?," + " postcode = ?,"
                        + " einddatum_tijdvak_geldigheid = ?," + " in_onderzoek = ?,"
                        + " type_adresseerbaar_object = ?," + " bron_documentdatum = ?,"
                        + " bron_documentnummer = ?," + " nummeraanduiding_status = ?,"
                        + " bag_woonplaats_id = ?," + " bag_openbare_ruimte_id = ?"
                        + " where bag_nummeraanduiding_id = ?" + " and aanduiding_record_correctie = ?"
                        + " and begindatum_tijdvak_geldigheid = ?");
                ps.setInt(1, nummeraanduiding.getAanduidingRecordInactief().ordinal());
                ps.setInt(2, nummeraanduiding.getHuisnummer());
                ps.setInt(3, nummeraanduiding.getOfficieel().ordinal());
                if (nummeraanduiding.getHuisletter() == null)
                    ps.setNull(4, Types.INTEGER);
                else
                    ps.setString(4, nummeraanduiding.getHuisletter());
                if (nummeraanduiding.getHuisnummertoevoeging() == null)
                    ps.setNull(5, Types.VARCHAR);
                else
                    ps.setString(5, nummeraanduiding.getHuisnummertoevoeging());
                if (nummeraanduiding.getPostcode() == null)
                    ps.setNull(6, Types.VARCHAR);
                else
                    ps.setString(6, nummeraanduiding.getPostcode());
                if (nummeraanduiding.getEinddatumTijdvakGeldigheid() == null)
                    ps.setNull(7, Types.TIMESTAMP);
                else
                    ps.setTimestamp(7,
                            new Timestamp(nummeraanduiding.getEinddatumTijdvakGeldigheid().getTime()));
                ps.setInt(8, nummeraanduiding.getInOnderzoek().ordinal());
                ps.setInt(9, nummeraanduiding.getTypeAdresseerbaarObject().ordinal());
                ps.setDate(10, new Date(nummeraanduiding.getDocumentdatum().getTime()));
                ps.setString(11, nummeraanduiding.getDocumentnummer());
                ps.setInt(12, nummeraanduiding.getNummeraanduidingStatus().ordinal());
                if (nummeraanduiding.getGerelateerdeWoonplaats() == null)
                    ps.setNull(13, Types.INTEGER);
                else
                    ps.setLong(13, nummeraanduiding.getGerelateerdeWoonplaats());
                ps.setLong(14, nummeraanduiding.getGerelateerdeOpenbareRuimte());
                ps.setLong(15, nummeraanduiding.getIdentificatie());
                ps.setLong(16, nummeraanduiding.getAanduidingRecordCorrectie());
                ps.setTimestamp(17, new Timestamp(nummeraanduiding.getBegindatumTijdvakGeldigheid().getTime()));
                return ps;
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException("Error updating nummeraanduiding: " + nummeraanduiding.getIdentificatie(), e);
    }
}

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

private boolean changeParticipation(Connection con, AccessToken token, EventExtId extId,
        RecurrenceId recurrenceId, ObmUser calendarOwner, Participation participation)
        throws SQLException, ParseException {

    PreparedStatement ps = null;

    String q = "UPDATE EventLink " + "SET eventlink_state = ?, eventlink_userupdate = ?, eventlink_comment = ? "
            + "WHERE eventlink_event_id IN " + "(" + "SELECT event_id " + "FROM Event e "
            + "LEFT JOIN EventException eexp ON e.event_id = eventexception_child_id "
            + "WHERE event_ext_id = ? " + "AND eexp.eventexception_date = ?" + ") AND "
            + "eventlink_entity_id IN "
            + "( SELECT userentity_entity_id FROM UserEntity WHERE userentity_user_id = ? )";

    Integer loggedUserId = token.getObmId();

    try {//  w  ww. j  a  va2 s.com
        ps = con.prepareStatement(q);

        int idx = 1;
        ps.setObject(idx++,
                obmHelper.getDBCP().getJdbcObject(ObmHelper.VPARTSTAT, participation.getState().toString()));
        ps.setInt(idx++, loggedUserId);
        ps.setString(idx++, participation.getSerializedCommentToString());
        ps.setString(idx++, extId.getExtId());
        Date recId = new DateTime(recurrenceId.getRecurrenceId());
        ps.setTimestamp(idx++, new Timestamp(recId.getTime()));
        ps.setInt(idx++, calendarOwner.getUid());
        ps.execute();
        if (ps.getUpdateCount() > 0) {
            return true;
        }
    } catch (SQLException e) {
        logger.error(e.getMessage(), e);
        throw e;
    } catch (ParseException e) {
        logger.error(e.getMessage(), e);
        throw e;
    } finally {
        obmHelper.cleanup(null, ps, null);
    }
    return false;
}

From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java

@Override
public void update(final Nummeraanduiding origineel, final Nummeraanduiding mutation) throws DAOException {
    try {//from www .  j ava  2s . co  m
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("update bag_nummeraanduiding set"
                        + " aanduiding_record_inactief = ?," + " aanduiding_record_correctie = ?,"
                        + " huisnummer = ?," + " officieel = ?," + " huisletter = ?,"
                        + " huisnummertoevoeging = ?," + " postcode = ?," + " einddatum_tijdvak_geldigheid = ?,"
                        + " in_onderzoek = ?," + " type_adresseerbaar_object = ?," + " bron_documentdatum = ?,"
                        + " bron_documentnummer = ?," + " nummeraanduiding_status = ?,"
                        + " bag_woonplaats_id = ?," + " bag_openbare_ruimte_id = ?"
                        + " where bag_nummeraanduiding_id = ?" + " and aanduiding_record_correctie = ?"
                        + " and begindatum_tijdvak_geldigheid = ?");
                ps.setInt(1, mutation.getAanduidingRecordInactief().ordinal());
                ps.setLong(2, mutation.getAanduidingRecordCorrectie());
                ps.setInt(3, mutation.getHuisnummer());
                ps.setInt(4, mutation.getOfficieel().ordinal());
                if (mutation.getHuisletter() == null)
                    ps.setNull(5, Types.INTEGER);
                else
                    ps.setString(5, mutation.getHuisletter());
                if (mutation.getHuisnummertoevoeging() == null)
                    ps.setNull(6, Types.VARCHAR);
                else
                    ps.setString(6, mutation.getHuisnummertoevoeging());
                if (mutation.getPostcode() == null)
                    ps.setNull(7, Types.VARCHAR);
                else
                    ps.setString(7, mutation.getPostcode());
                if (mutation.getEinddatumTijdvakGeldigheid() == null)
                    ps.setNull(8, Types.TIMESTAMP);
                else
                    ps.setTimestamp(8, new Timestamp(mutation.getEinddatumTijdvakGeldigheid().getTime()));
                ps.setInt(9, mutation.getInOnderzoek().ordinal());
                ps.setInt(10, mutation.getTypeAdresseerbaarObject().ordinal());
                ps.setDate(11, new Date(mutation.getDocumentdatum().getTime()));
                ps.setString(12, mutation.getDocumentnummer());
                ps.setInt(13, mutation.getNummeraanduidingStatus().ordinal());
                if (mutation.getGerelateerdeWoonplaats() == null)
                    ps.setNull(14, Types.INTEGER);
                else
                    ps.setLong(14, mutation.getGerelateerdeWoonplaats());
                ps.setLong(15, mutation.getGerelateerdeOpenbareRuimte());
                ps.setLong(16, origineel.getIdentificatie());
                ps.setLong(17, origineel.getAanduidingRecordCorrectie());
                ps.setTimestamp(18, new Timestamp(origineel.getBegindatumTijdvakGeldigheid().getTime()));
                return ps;
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException("Error updating nummeraanduiding: " + origineel.getIdentificatie(), e);
    }
}