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.pari.nm.utils.db.InventoryDBHelper.java

public static void insertJobCreatorDetails(String jobName, String jobGroup, int creatorId, long createdTime,
        String devices) throws Exception {
    Connection c = null;/*from   w  ww. ja  va2  s . c  om*/
    PreparedStatement ps = null;

    try {

        String loginName = "";
        UserDetails ud = UsersFactory.getUser(creatorId);
        if (ud != null) {
            loginName = ud.getLogin();
        }

        c = DBHelper.getConnection();
        ps = c.prepareStatement(DBHelperConstants.UPDATE_QRTZ_JOB_CREATOR_DETAILS);

        ps.setString(1, jobName);
        ps.setString(2, jobGroup);
        ps.setInt(3, creatorId);
        ps.setString(4, loginName);
        ps.setTimestamp(5, (new Timestamp(createdTime)));
        ps.setString(6, devices);
        ps.executeUpdate();
        insertJobCustomerDetails(jobName, jobGroup, -1);

    } catch (Exception ex) {
        logger.warn("Error while creating a job entry in db.", ex);
    } finally {
        try {
            ps.close();
        } catch (Exception ex) {
            logger.debug("Error while closing prepared statement.", ex);
        }
        DBHelper.releaseConnection(c);

    }

}

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

public static void insertJobCreatorDetails(String jobName, String jobGroup, int creatorId, long createdTime,
        String devices, int cusID) throws Exception {
    Connection c = null;/*from  w  w w . j  a  va  2s.  c o m*/
    PreparedStatement ps = null;

    try {

        String loginName = "";
        UserDetails ud = UsersFactory.getUser(creatorId);
        if (ud != null) {
            loginName = ud.getLogin();
        }

        c = DBHelper.getConnection();
        ps = c.prepareStatement(DBHelperConstants.UPDATE_QRTZ_JOB_CREATOR_DETAILS);

        ps.setString(1, jobName);
        ps.setString(2, jobGroup);
        ps.setInt(3, creatorId);
        ps.setString(4, loginName);
        ps.setTimestamp(5, (new Timestamp(createdTime)));
        ps.setString(6, devices);
        ps.executeUpdate();
        insertJobCustomerDetails(jobName, jobGroup, cusID);

    } catch (Exception ex) {
        logger.warn("Error while creating a job entry in db.", ex);
    } finally {
        try {
            ps.close();
        } catch (Exception ex) {
            logger.debug("Error while closing prepared statement.", ex);
        }
        DBHelper.releaseConnection(c);

    }

}

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

public static void addSyslogMsg(int deviceId, DecodedSyslogMessage msg) {
    Connection c = null;/*ww  w.  ja  va 2s. c  om*/
    PreparedStatement ps = null;

    try {

        c = DBHelper.getConnection();
        ps = c.prepareStatement(DBHelperConstants.INSERT_SYSLOG_MESSAGE);
        ps.setInt(1, deviceId);
        ps.setString(2, msg.getModule());
        ps.setString(3, msg.get_mnemonic().substring(1));
        ps.setInt(4, msg.get_severity());
        ps.setString(5, msg.message());
        ps.setString(6, msg.tag());
        ps.setInt(7, msg.priority());
        ps.setLong(8, msg.getSeqNumber());
        ps.setTimestamp(9, (new Timestamp(msg.getReceivedTime())));
        ps.setString(10, msg.get_mnemonic());
        ps.executeUpdate();
    } catch (Exception ee) {
        logger.warn("Error while storing syslog message for the deviceId=" + deviceId, ee);
    } finally {
        try {
            ps.close();
        } catch (Exception e) {
        }
        try {
            DBHelper.releaseConnection(c);
        } catch (Exception ee) {
        }
    }
}

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

public static void updateDeviceOperationTime(int deviceId, String moduleName, String subModuleName,
        long timestamp) {
    PreparedStatement ps = null;
    Connection c = null;/*  w  w w . ja v a2 s.  c o  m*/

    try {
        String query = "delete from device_operation_time where dev_id=" + deviceId + " and module='"
                + moduleName + "' and sub_module='" + subModuleName + "'";
        DBHelper.executeUpdate(query);

        c = DBHelper.getConnection();
        ps = c.prepareStatement(DBHelperConstants.DEVICE_OPERATION_TIME_INSERT);

        ps.setInt(1, deviceId);
        ps.setString(2, moduleName);
        ps.setString(3, subModuleName);
        ps.setTimestamp(4, new Timestamp(timestamp));

        ps.executeUpdate();
    } catch (Exception ee) {
        logger.warn("Error while updating device operation time for the device=" + deviceId, ee);
    } finally {
        try {
            ps.close();
        } catch (Exception ee) {
        }

        try {
            DBHelper.releaseConnection(c);
        } catch (Exception ee) {
        }
    }
}

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

/**
 * @param profile//w w  w. j  a va 2 s.co m
 *            , creator Id
 */
public static int insertCollectionProfile(CollectionProfile profile, int creatorId) throws Exception {
    Connection c = null;
    PreparedStatement ps = null;
    try {
        c = DBHelper.getConnection();
        ps = c.prepareStatement(DBHelperConstants.INSERT_COLLECTION_PROFILE);
        ps.setString(1, profile.getProfileName());
        ps.setString(2, profile.getProfileDescr());
        ps.setString(3, profile.getDatasetsString());
        ps.setString(4, profile.isAllDevicesSelected() ? "y" : "n");
        ps.setString(5, profile.getGroupsStr());
        ps.setInt(6, -1);
        ps.setTimestamp(7, (new Timestamp(profile.getCreatedAt())));
        ps.setTimestamp(8, null);
        ps.setInt(9, creatorId);
        ps.setInt(10, -1);
        ps.setString(11, profile.isSystemProfile() ? "y" : "n");
        ps.setString(12, profile.getCommandCLI());
        ps.setInt(13, profile.isRunDiscovery() ? 1 : 0);
        ps.setInt(14, profile.isRunDAV() ? 1 : 0);
        ps.executeUpdate();

        int profile_id = -1;

        ResultSet rs = null;
        try {
            rs = DBHelper.executeQuery(
                    "select * from collection_profile where profile_name='" + profile.getProfileName() + "'");
            if (rs.next()) {
                profile_id = rs.getInt("profile_id");
                profile.setProfId(profile_id);
            }
            if (profile.getFtpDetails() != null) {
                ServerDBHelper.saveFtpServerDetails(profile.getFtpDetails(), profile.getProfileName());
            }
        } catch (Exception ee) {
            logger.warn("Exception while inserting collection profile", ee);
            return -1;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception ex) {
                logger.warn("Exception while closing the resultset in inserting collection profile", ex);
            }
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (Exception ex) {
                logger.warn("Exception while closing the statement in inserting collection profile", ex);
            }

        }

        try {
            DBHelper.executeUpdate(
                    "delete from collection_prof_devices where profile_id=" + profile.getProfId());

            int size = profile.getNodesStr().length();

            if (size > 0) {
                int count = size / 4000 + 1;
                String idsStr = profile.getNodesStr();
                for (int i = 0; i < count; i++) {
                    String ids = (idsStr.length() > 4000) ? idsStr.substring(0, 4000) : idsStr;
                    if (ids.length() > 0) {
                        DBHelper.executeUpdate("insert into collection_prof_devices values ("
                                + profile.getProfId() + "," + i + ",'" + ids + "')");

                        idsStr = (idsStr.length() > 4000) ? idsStr.substring(4001) : "";
                    }
                }
            }
        } catch (Exception ee) {
            logger.warn("Exception while removing old device in inserting collection profile", ee);
        }

        if (profile.getScheduleDetails() != null) {
            System.err.println("SCHED:" + profile.getScheduleDetails().toString());
            int jobId = JobMgr.getInstance().scheduleCollectionProfileJob(profile, creatorId);
            profile.setJobId(jobId);
            setCollectionProfileJobId(profile.getProfId(), jobId);
        }
        profile.setProfId(profile_id);
        return profile_id;

    } catch (Exception ee) {
        logger.warn("Error while inserting collection profile", ee);
        throw ee;
    } finally {
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (Exception e) {
            logger.warn("Exception while closing the statement in inserting collection profile", e);
        }

        try {
            if (c != null) {
                DBHelper.releaseConnection(c);
            }
        } catch (Exception e) {
            logger.warn("Exception while closing the connection in inserting collection profile", e);
        }
    }
}

From source file:com.funambol.foundation.items.dao.PIMCalendarDAO.java

/**
 * Retrieves the UID list of the calendars considered to be "twins" of a
 * given calendar./*from www . j a  v a2  s . c om*/
 *
 * @param c the Calendar object representing the calendar whose twins
 *          need be found. In the present implementation, only the following
 *          data matter: 
 *          <BR>for events <UL><LI>date start<LI>date end<LI>subject</UL>
 *          for tasks <UL><LI>date end<LI>subject</UL>
 * @throws DAOException
 * @return a List of UIDs (as String objects) that may be empty but not null
 */
public List getTwinItems(Calendar c) throws DAOException {

    if (log.isTraceEnabled()) {
        log.trace("PIMCalendarDAO getTwinItems begin");
    }

    LinkedList<String> twins = new LinkedList<String>();
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    if (!isTwinSearchAppliableOn(c)) {
        if (log.isTraceEnabled()) {
            log.trace("Item with no dtStart, dtEnd, summary: twin search skipped.");
        }
        return twins;
    }

    try {

        // Looks up the data source when the first connection is created
        con = getUserDataSource().getRoutedConnection(userId);
        con.setReadOnly(true);

        Date dtStart;
        Date dtEnd;
        Date dueTomorrowNoon = null;
        Date dueYesterdayNoon = null;

        dtStart = getDateFromString(c.getCalendarContent().isAllDay(),
                Property.stringFrom(c.getCalendarContent().getDtStart()), "000000");
        dtEnd = getDateFromString(c.getCalendarContent().isAllDay(),
                Property.stringFrom(c.getCalendarContent().getDtEnd()), "235900");

        if ((dtEnd != null) && (c.getCalendarContent() instanceof Task)) {
            java.util.Calendar noon = new GregorianCalendar(TimeZone.getTimeZone("UTC"));
            noon.setTime(dtEnd);
            noon.set(java.util.Calendar.HOUR_OF_DAY, 12);
            noon.set(java.util.Calendar.MINUTE, 0);
            noon.set(java.util.Calendar.MILLISECOND, 0);
            noon.add(java.util.Calendar.DATE, +1);
            dueTomorrowNoon = noon.getTime();
            noon.add(java.util.Calendar.DATE, -2); // go back and another -1
            dueYesterdayNoon = noon.getTime();
        }

        StringBuffer sqlGetCalendarTwinList = new StringBuffer(SQL_GET_FNBL_PIM_CALENDAR_ID_LIST_BY_USER);

        String subject = Property.stringFrom(c.getCalendarContent().getSummary(), true); // Empty implies null;

        if ("null".equals(subject)) {
            subject = null;
        }
        if (subject == null) {
            sqlGetCalendarTwinList.append(SQL_AND_NO_SUBJECT_IS_SET);
        } else {
            sqlGetCalendarTwinList.append(SQL_AND_SUBJECT_EQUALS_QUESTIONMARK);
        }
        if (c.getCalendarContent() instanceof Event) {
            if (dtStart == null) {
                sqlGetCalendarTwinList.append(SQL_AND_NO_DSTART_IS_SET);
            } else {
                sqlGetCalendarTwinList.append(SQL_AND_DSTART_EQUALS_QUESTIONMARK);
            }
        }
        if (dtEnd == null) {
            // In method updateItems() while storing the Event in the db, if
            // the End Date is empty it is filled with the Start Date.
            // Filling the empty EndDate with the StartDate is done only for
            // Events and not for Tasks.
            // See "Fix for Siemens S56 end date issue" in method 
            // updateItems().
            // So in order to find the twins, if the incoming Event has an 
            // empty EndDate we seek into the db for Events with EndDate 
            // equal to the StartDate value.
            if (c.getCalendarContent() instanceof Task) {
                sqlGetCalendarTwinList.append(SQL_AND_NO_DEND_IS_SET);
            } else {
                sqlGetCalendarTwinList.append(SQL_AND_DEND_EQUALS_QUESTIONMARK);
            }
        } else {
            if (c.getCalendarContent() instanceof Task) {
                sqlGetCalendarTwinList.append(SQL_AND_DEND_IN_INTERVAL);
            } else {
                sqlGetCalendarTwinList.append(SQL_AND_DEND_EQUALS_QUESTIONMARK);
            }
        }

        if (c.getCalendarContent() instanceof Event) {
            sqlGetCalendarTwinList.append(SQL_FILTER_BY_TYPE[CALENDAR_EVENT_TYPE]);
        } else {
            sqlGetCalendarTwinList.append(SQL_FILTER_BY_TYPE[CALENDAR_TASK_TYPE]);
        }

        //
        // If funambol is not in the debug mode it is not possible to print 
        // the calendar info because it contains sensitive data.
        //
        if (Configuration.getConfiguration().isDebugMode()) {

            if (log.isTraceEnabled()) {

                StringBuilder sb = new StringBuilder(100);
                sb.append("Looking for items having: ");

                if (subject == null || subject.length() == 0) {
                    sb.append("\n> subject: <N/A>");
                } else {
                    sb.append("\n> subject: '").append(subject).append('\'');
                }
                if (c.getCalendarContent() instanceof Event) {
                    if (dtStart == null) {
                        sb.append("\n> start date: <N/A>");
                    } else {
                        sb.append("\n> start date: ").append(dtStart);
                    }
                    if (dtEnd == null) {
                        sb.append("\n> end date: <N/A>");
                    } else {
                        sb.append("\n> end date: ").append(dtEnd);
                    }
                } else { // It's a task
                    if (dtEnd == null) {
                        sb.append("\n> due date: <N/A>");
                    } else {
                        sb.append("\n> due date: between ").append(dueYesterdayNoon)
                                .append("\n>           and ").append(dueTomorrowNoon)
                                .append(",\n>           possibly ").append(dtEnd);
                    }
                }

                log.trace(sb.toString());
            }
        }

        sqlGetCalendarTwinList.append(SQL_ORDER_BY_ID);

        ps = con.prepareStatement(sqlGetCalendarTwinList.toString());

        int k = 1;
        ps.setString(k++, userId);
        if (subject != null) {
            ps.setString(k++, subject.toLowerCase(Locale.ENGLISH));
        }
        if (dtStart != null) {
            if (c.getCalendarContent() instanceof Event) {
                ps.setTimestamp(k++, new Timestamp(dtStart.getTime()));
            }
        }
        if (dtEnd != null) {
            if (c.getCalendarContent() instanceof Task) {
                ps.setTimestamp(k++, new Timestamp(dueYesterdayNoon.getTime()));
                ps.setTimestamp(k++, new Timestamp(dueTomorrowNoon.getTime()));
            } else {
                ps.setTimestamp(k++, new Timestamp(dtEnd.getTime()));
            }
        } else {
            // In method updateItems() while storing the Event in the db, if
            // the End Date is empty it is filled with the Start Date.
            // Filling the empty EndDate with the StartDate is done only for
            // Events and not for Tasks.
            // See "Fix for Siemens S56 end date issue" in method 
            // updateItems().
            // So in order to find the twins, if the incoming Event has an 
            // empty EndDate we seek into the db for Events with EndDate 
            // equal to the StartDate value.
            if (c.getCalendarContent() instanceof Event) {
                ps.setTimestamp(k++, new Timestamp(dtStart.getTime()));
            }
        }

        rs = ps.executeQuery();
        long twinId;
        Timestamp twinDueDate;
        while (rs.next()) {
            if (c.getCalendarContent() instanceof Event) {
                twinId = rs.getLong(1);
                // dend is not relevant in this case
                if (log.isTraceEnabled()) {
                    log.trace("Twin event found: " + twinId);
                }

                twins.add(Long.toString(twinId));

            } else { // it's a Task
                twinId = rs.getLong(1);
                twinDueDate = rs.getTimestamp(2);
                if (log.isTraceEnabled()) {
                    log.trace("Twin task found: " + twinId);
                }

                if ((dtEnd != null) && (twinDueDate != null) && twinDueDate.getTime() == dtEnd.getTime()) {
                    twins.addFirst(Long.toString(twinId));
                    if (log.isTraceEnabled()) {
                        log.trace("Item " + twinId + " is an exact due-date match.");
                    }
                } else {
                    twins.addLast(Long.toString(twinId));
                }
            }
        }

    } catch (Exception e) {
        throw new DAOException("Error retrieving twin. ", e);
    } finally {
        DBTools.close(con, ps, rs);
    }

    if (log.isTraceEnabled()) {
        log.trace("PIMCalendarDAO getTwinItems end");
    }

    return twins;
}

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

public static void storeSyslogMsg(int nodeId, DecodedSyslogMessage msg) {
    Connection c = null;//from  w ww  . j  ava  2s.co  m
    PreparedStatement ps = null;

    try {
        NetworkNode node = null;
        if (nodeId != -1) {
            node = NetworkNodeCache.getInstance().getNodeByID(nodeId);
        } else {
            node = NetworkNodeCache.getInstance().getLiveNode(msg.getSource().toString());
        }

        if (node == null) {
            return;
        }

        String compare_str = msg.get_module();

        if (node instanceof PixDevice) {
            compare_str = msg.get_mnemonic();
        }

        int id = node.getNodeId();
        c = DBHelper.getConnection();
        ps = c.prepareStatement(DBHelperConstants.INSERT_SYSLOG_MESSAGE);
        ps.setInt(1, id);
        ps.setString(2, msg.getModule());
        ps.setString(3, msg.get_mnemonic());
        ps.setInt(4, msg.get_severity());
        ps.setString(5, msg.message());
        ps.setString(6, msg.tag());
        ps.setInt(7, msg.priority());
        ps.setLong(8, msg.getSeqNumber());
        ps.setTimestamp(9, (new Timestamp(msg.getReceivedTime())));
        ps.setString(10, compare_str);
        ps.executeUpdate();
    } catch (Exception ee) {
        logger.warn("Error while storing syslog message for the deviceId=" + nodeId, ee);
    } finally {
        try {
            ps.close();
        } catch (Exception e) {
        }
        try {
            DBHelper.releaseConnection(c);
        } catch (Exception ee) {
        }
    }
}

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

public static String getShowSyslog(int nodeId, Map variables) {
    ResultSet rs = null;// w  w w  . j a  va  2 s. c  o m
    Connection c = null;
    PreparedStatement ps = null;
    long startTime = 0, endTime = 0;
    int timeGap = 0;
    if (variables.containsKey("syslogTimeInterval")) {
        timeGap = (int) variables.get("syslogTimeInterval");

        if (timeGap != -1) {
            endTime = System.currentTimeMillis();
            startTime = System.currentTimeMillis() - TimeUnit.MINUTES.toMillis(timeGap);
        }

    } else {
        // if syslogTimeInterval is not specified, we are expecting the input names should be endTime and startTime.
        // More enhancements can be done to support different names

        endTime = (int) variables.get("endTime");
        startTime = (int) variables.get("startTime");
    }
    String sql = "select COMPARE_STR|| ': ' || MESSAGE as message_desc from syslog_messages where device_id=?";
    if (timeGap != -1) {
        sql += " and RECEIVED_AT " + "BETWEEN TO_TIMESTAMP(?) AND to_TIMESTAMP(?) ORDER BY RECEIVED_AT";

    }
    try {
        c = DBHelper.getConnection();
        ps = c.prepareStatement(sql);
        ps.setInt(1, nodeId);
        if (timeGap != -1) {
            ps.setTimestamp(2, new Timestamp(startTime));
            ps.setTimestamp(3, new Timestamp(endTime));
        }
        rs = ps.executeQuery();
        byte b[] = null;
        StringBuffer ab = new StringBuffer();

        while (rs != null && rs.next()) {

            ab.append(rs.getString("message_desc"));

            ab.append("\n");

        }
        return ab.toString();

    } catch (Exception ee) {
        logger.warn("Error while getting show cli for the node = " + nodeId, ee);
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception ex) {
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (Exception ee) {
            }
        }
        if (c != null) {
            try {
                DBHelper.releaseConnection(c);
            } catch (Exception ex) {
            }
        }
    }
    return "";
}

From source file:com.flexive.core.storage.genericSQL.GenericHierarchicalStorage.java

/**
 * Set a properties data for inserts or updates
 *
 * @param insert          perform insert or update?
 * @param prop            current property
 * @param allData         all data of the instance (might be needed to buld references, etc.)
 * @param con             an open and valid connection
 * @param data            current property data
 * @param ps              prepared statement for the data table
 * @param ft              fulltext indexer
 * @param upperColumnPos  position of the uppercase column (if present, else <code>-1</code>)
 * @param includeFullText add fulltext entries? Will be skipped for position only changes
 * @throws SQLException        on errors
 * @throws FxUpdateException   on errors
 * @throws FxDbException       on errors
 * @throws FxNoAccessException for FxNoAccess values
 *///from   ww w  . jav a2  s  .  co  m
private void setPropertyData(boolean insert, FxProperty prop, List<FxData> allData, Connection con,
        FxPropertyData data, PreparedStatement ps, FulltextIndexer ft, int upperColumnPos,
        boolean includeFullText) throws SQLException, FxUpdateException, FxDbException, FxNoAccessException {
    FxValue value = data.getValue();
    if (value instanceof FxNoAccess)
        throw new FxNoAccessException("ex.content.value.noaccess");
    if (value.isMultiLanguage() != ((FxPropertyAssignment) data.getAssignment()).isMultiLang()) {
        if (((FxPropertyAssignment) data.getAssignment()).isMultiLang())
            throw new FxUpdateException("ex.content.value.invalid.multilanguage.ass.multi",
                    data.getXPathFull());
        else
            throw new FxUpdateException("ex.content.value.invalid.multilanguage.ass.single",
                    data.getXPathFull());
    }
    int pos_lang = insert ? INSERT_LANG_POS : UPDATE_ID_POS + 2;
    int pos_isdef_lang = insert ? INSERT_ISDEF_LANG_POS : UPDATE_MLDEF_POS;
    final FxEnvironment env = CacheAdmin.getEnvironment();
    if (prop.getDataType().isSingleRowStorage()) {
        //Data types that just use one db row can be handled in a very similar way
        Object translatedValue;
        GregorianCalendar gc = null;
        final long[] translatedLanguages = value.getTranslatedLanguages();
        for (long translatedLanguage : translatedLanguages) {
            translatedValue = value.getTranslation(translatedLanguage);
            if (translatedValue == null) {
                LOG.warn("Translation for " + data.getXPath() + " is null!");
            }
            ps.setLong(pos_lang, translatedLanguage);
            if (!value.isMultiLanguage())
                ps.setBoolean(pos_isdef_lang, true);
            else
                ps.setBoolean(pos_isdef_lang, value.isDefaultLanguage(translatedLanguage));
            if (upperColumnPos != -1) {
                final Locale locale = value.isMultiLanguage() ? env.getLanguage(translatedLanguage).getLocale()
                        : Locale.getDefault();
                ps.setString(upperColumnPos, translatedValue.toString().toUpperCase(locale));
            }
            int[] pos = insert ? getColumnPosInsert(prop) : getColumnPosUpdate(prop);
            switch (prop.getDataType()) {
            case Double:
                checkDataType(FxDouble.class, value, data.getXPathFull());
                ps.setDouble(pos[0], (Double) translatedValue);
                break;
            case Float:
                checkDataType(FxFloat.class, value, data.getXPathFull());
                ps.setFloat(pos[0], (Float) translatedValue);
                break;
            case LargeNumber:
                checkDataType(FxLargeNumber.class, value, data.getXPathFull());
                ps.setLong(pos[0], (Long) translatedValue);
                break;
            case Number:
                checkDataType(FxNumber.class, value, data.getXPathFull());
                ps.setInt(pos[0], (Integer) translatedValue);
                break;
            case HTML:
                checkDataType(FxHTML.class, value, data.getXPathFull());
                boolean useTidy = ((FxHTML) value).isTidyHTML();
                ps.setBoolean(pos[1], useTidy);
                final String extractorInput = doTidy(data.getXPathFull(), (String) translatedValue);
                if (useTidy) {
                    translatedValue = extractorInput;
                }
                final HtmlExtractor result = new HtmlExtractor(extractorInput, true);
                setBigString(ps, pos[2], result.getText());
                setBigString(ps, pos[0], (String) translatedValue);
                break;
            case String1024:
            case Text:
                checkDataType(FxString.class, value, data.getXPathFull());
                setBigString(ps, pos[0], (String) translatedValue);
                break;
            case Boolean:
                checkDataType(FxBoolean.class, value, data.getXPathFull());
                ps.setBoolean(pos[0], (Boolean) translatedValue);
                break;
            case Date:
                checkDataType(FxDate.class, value, data.getXPathFull());
                if (gc == null)
                    gc = new GregorianCalendar();
                gc.setTime((Date) translatedValue);
                //strip all time information, this might not be necessary since ps.setDate() strips them
                //for most databases but won't hurt either ;)
                gc.set(GregorianCalendar.HOUR, 0);
                gc.set(GregorianCalendar.MINUTE, 0);
                gc.set(GregorianCalendar.SECOND, 0);
                gc.set(GregorianCalendar.MILLISECOND, 0);
                ps.setDate(pos[0], new java.sql.Date(gc.getTimeInMillis()));
                break;
            case DateTime:
                checkDataType(FxDateTime.class, value, data.getXPathFull());
                if (gc == null)
                    gc = new GregorianCalendar();
                gc.setTime((Date) translatedValue);
                ps.setTimestamp(pos[0], new Timestamp(gc.getTimeInMillis()));
                break;
            case DateRange:
                checkDataType(FxDateRange.class, value, data.getXPathFull());
                if (gc == null)
                    gc = new GregorianCalendar();
                gc.setTime(((DateRange) translatedValue).getLower());
                gc.set(GregorianCalendar.HOUR, 0);
                gc.set(GregorianCalendar.MINUTE, 0);
                gc.set(GregorianCalendar.SECOND, 0);
                gc.set(GregorianCalendar.MILLISECOND, 0);
                ps.setDate(pos[0], new java.sql.Date(gc.getTimeInMillis()));
                gc.setTime(((DateRange) translatedValue).getUpper());
                gc.set(GregorianCalendar.HOUR, 0);
                gc.set(GregorianCalendar.MINUTE, 0);
                gc.set(GregorianCalendar.SECOND, 0);
                gc.set(GregorianCalendar.MILLISECOND, 0);
                ps.setDate(pos[1], new java.sql.Date(gc.getTimeInMillis()));
                break;
            case DateTimeRange:
                checkDataType(FxDateTimeRange.class, value, data.getXPathFull());
                if (gc == null)
                    gc = new GregorianCalendar();
                gc.setTime(((DateRange) translatedValue).getLower());
                ps.setTimestamp(pos[0], new Timestamp(gc.getTimeInMillis()));
                gc.setTime(((DateRange) translatedValue).getUpper());
                ps.setTimestamp(pos[1], new Timestamp(gc.getTimeInMillis()));
                break;
            case Binary:
                checkDataType(FxBinary.class, value, data.getXPathFull());
                BinaryDescriptor binary = (BinaryDescriptor) translatedValue;
                if (!binary.isNewBinary()) {
                    ps.setLong(pos[0], binary.getId());
                } else {
                    try {
                        //transfer the binary from the transit table to the binary table
                        BinaryDescriptor created = binaryStorage.binaryTransit(con, binary);
                        ps.setLong(pos[0], created.getId());
                        //check all other properties if they contain the same handle
                        //and replace with the data of the new binary
                        for (FxData _curr : allData) {
                            if (_curr instanceof FxPropertyData && !_curr.isEmpty()
                                    && ((FxPropertyData) _curr).getValue() instanceof FxBinary) {
                                FxBinary _val = (FxBinary) ((FxPropertyData) _curr).getValue();
                                _val._replaceHandle(binary.getHandle(), created);
                            }
                        }
                    } catch (FxApplicationException e) {
                        throw new FxDbException(e);
                    }
                }
                break;
            case SelectOne:
                checkDataType(FxSelectOne.class, value, data.getXPathFull());
                ps.setLong(pos[0], ((FxSelectListItem) translatedValue).getId());
                break;
            case SelectMany:
                checkDataType(FxSelectMany.class, value, data.getXPathFull());
                SelectMany sm = (SelectMany) translatedValue;

                for (int i1 = 0; i1 < sm.getSelected().size(); i1++) {
                    FxSelectListItem item = sm.getSelected().get(i1);
                    if (i1 > 0) {
                        if (batchContentDataChanges())
                            ps.addBatch();
                        else
                            ps.executeUpdate();
                    }
                    ps.setLong(pos[0], item.getId());
                    ps.setString(pos[1], sm.getSelectedIdsList());
                    ps.setLong(pos[2], sm.getSelectedIds().size());
                }
                if (sm.getSelected().size() == 0)
                    ps.setLong(pos[0], 0); //write the virtual item as a marker to have a valid row
                break;
            case Reference:
                //reference integrity check is done prior to saving
                ps.setLong(pos[0], ((FxPK) translatedValue).getId());
                break;
            case InlineReference:
            default:
                throw new FxDbException(LOG, "ex.db.notImplemented.store", prop.getDataType().getName());
            }
            int valueDataPos = insert ? getValueDataInsertPos(prop.getDataType())
                    : getValueDataUpdatePos(prop.getDataType());
            if (value.hasValueData(translatedLanguage)) {
                ps.setInt(valueDataPos, value.getValueDataRaw(translatedLanguage));
            } else
                ps.setNull(valueDataPos, Types.NUMERIC);
            if (batchContentDataChanges())
                ps.addBatch();
            else {
                try {
                    ps.executeUpdate();
                } catch (SQLException e) {
                    LOG.error(prop.getName(), e);
                    throw e;
                }
            }
        }
    } else {
        switch (prop.getDataType()) {
        //TODO: implement datatype specific insert
        default:
            throw new FxDbException(LOG, "ex.db.notImplemented.store", prop.getDataType().getName());
        }

    }
    if (ft != null && prop.isFulltextIndexed() && includeFullText)
        ft.index(data);
}

From source file:com.funambol.foundation.items.dao.PIMCalendarDAO.java

/**
 * Adds a calendar. If necessary, a new ID is generated and set in the
 * CalendarWrapper./*  w  ww .j  ava2s  .  c  o m*/
 *
 * @param c as a CalendarWrapper object, usually without an ID set.
 * @throws DAOException
 *
 * @see CalendarWrapper
 */
public void addItem(CalendarWrapper cw) throws DAOException {

    if (log.isTraceEnabled()) {
        log.trace("PIMCalendarDAO addItem begin");
    }

    Connection con = null;
    PreparedStatement ps = null;

    long id = 0;
    String allDay = null;
    String body = null;
    Short busyStatus = null;
    String categories = null;
    String companies = null;
    int duration = 0;
    Date dend = null;
    short importance = 0;
    String location = null;
    Short meetingStatus = null;
    String mileage = null;
    Date replyTime = null;
    short sensitivity = 0;
    Date dstart = null;
    String subject = null;
    int interval = 0;
    short monthOfYear = 0;
    short dayOfMonth = 0;
    String dayOfWeekMask = null;
    short instance = 0;
    String startDatePattern = null;
    String endDatePattern = null;
    Reminder reminder = null;
    RecurrencePattern rp = null;
    short recurrenceType = -1;
    String sId = null;
    int occurrences = -1;
    String folder = null;
    String dstartTimeZone = null;
    String dendTimeZone = null;
    String reminderTimeZone = null;
    Date completed = null;
    short percentComplete = -1;

    Timestamp lastUpdate = cw.getLastUpdate();
    if (lastUpdate == null) {
        lastUpdate = new Timestamp(System.currentTimeMillis());
    }

    try {

        sId = cw.getId();
        if (sId == null || sId.length() == 0) { // ...as it should be
            sId = getNextID();
            cw.setId(sId);
        }
        id = Long.parseLong(sId);

        CalendarContent c = cw.getCalendar().getCalendarContent();

        rp = c.getRecurrencePattern();

        boolean allDayB;
        if (c.getAllDay() != null && c.getAllDay().booleanValue()) {
            allDayB = true;
            allDay = "1";
        } else {
            allDayB = false;
            allDay = "0";
        }

        String sd = null;
        if (c.getDtStart() != null) {
            sd = c.getDtStart().getPropertyValueAsString();
            dstart = getDateFromString(allDayB, sd, "000000");
        }

        String ed = null;
        if ((sd != null && sd.length() > 0) || c.getDtEnd() != null) {
            ed = c.getDtEnd().getPropertyValueAsString();

            //
            // Fix for Siemens S56 end date issue only for event
            // @todo: verify if is really need to do this
            //
            if (c instanceof Event) {
                if (ed == null || ed.length() == 0) {
                    ed = sd;
                }
            }

            dend = getDateFromString(allDayB, ed, "235900");
        }

        body = Property.stringFrom(c.getDescription());

        if (c.getBusyStatus() != null) {
            busyStatus = new Short(c.getBusyStatus().shortValue());
        }

        categories = Property.stringFrom(c.getCategories());
        companies = Property.stringFrom(c.getOrganizer());
        location = Property.stringFrom(c.getLocation());
        folder = Property.stringFrom(c.getFolder());
        dstartTimeZone = timeZoneFrom(c.getDtStart());
        dendTimeZone = timeZoneFrom(c.getDtEnd());
        reminderTimeZone = timeZoneFrom(c.getReminder());
        meetingStatus = c.getMeetingStatus();

        Integer mileageInteger = c.getMileage(); // NB: not an int...
        if (mileageInteger != null) { // ...therefore it may be null
            mileage = String.valueOf(mileageInteger);
        }

        if (c instanceof Event) {
            replyTime = getDateFromString(allDayB, // @todo or false?
                    Property.stringFrom(((Event) c).getReplyTime()), "000000");
        }

        try {
            sensitivity = Short.parseShort(Property.stringFrom(c.getAccessClass()));
        } catch (NumberFormatException nfe) {
            sensitivity = 0;
            // The short must go on
        }

        if ((subject = Property.stringFrom(c.getSummary())) == null && body != null) {
            int endOfSentence = body.indexOf('.');
            if (endOfSentence == -1) {
                endOfSentence = body.length();
            }
            if (endOfSentence > SQL_SUBJECT_DIM) {
                endOfSentence = SQL_SUBJECT_DIM;
            }
            subject = body.substring(0, endOfSentence);
        }

        String isInfinite = "0";
        if (rp != null) {
            interval = rp.getInterval();
            recurrenceType = rp.getTypeId();
            monthOfYear = rp.getMonthOfYear();
            dayOfMonth = rp.getDayOfMonth();
            dayOfWeekMask = String.valueOf(rp.getDayOfWeekMask());
            instance = rp.getInstance();
            startDatePattern = rp.getStartDatePattern();
            endDatePattern = rp.getEndDatePattern();
            if (rp.isNoEndDate()) {
                isInfinite = "1";
            }
            occurrences = rp.getOccurrences();
        }

        if (c instanceof Task) {
            Task t = (Task) c;
            if (t.getDateCompleted() != null) {
                completed = getDateFromString(allDayB, ((Task) c).getDateCompleted().getPropertyValueAsString(),
                        "000000");
            }

            try {
                String complete = Property.stringFrom(t.getComplete());
                if (complete != null && complete.equals("1")) {
                    percentComplete = 100;
                } else {
                    percentComplete = Short.parseShort(Property.stringFrom(t.getPercentComplete()));
                }
                if (percentComplete < 0 || percentComplete > 100) {
                    throw new NumberFormatException("A percentage can't be " + percentComplete);
                }
            } catch (NumberFormatException nfe) {
                percentComplete = -1; // the short must go on
            }

            meetingStatus = getTaskStatus(t);
        }

        con = getUserDataSource().getRoutedConnection(userId);

        ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CALENDAR);

        ps.setLong(1, id);
        ps.setString(2, userId);
        ps.setLong(3, lastUpdate.getTime());
        ps.setString(4, String.valueOf(Def.PIM_STATE_NEW));
        ps.setString(5, allDay);
        ps.setString(6, StringUtils.left(body, SQL_BODY_DIM));
        if (busyStatus != null) {
            ps.setShort(7, busyStatus.shortValue());
        } else {
            ps.setNull(7, Types.SMALLINT);
        }
        ps.setString(8, StringUtils.left(categories, SQL_CATEGORIES_DIM));
        ps.setString(9, StringUtils.left(companies, SQL_COMPANIES_DIM));
        ps.setInt(10, duration);
        if (dend != null) {
            ps.setTimestamp(11, new Timestamp(dend.getTime()));
        } else {
            ps.setNull(11, Types.TIMESTAMP);
        }

        if (c.getPriority() != null) {

            String priority = c.getPriority().getPropertyValueAsString();

            if (priority != null && priority.length() > 0) {
                importance = Short.parseShort(priority);
                ps.setShort(12, importance);
            } else {
                ps.setNull(12, Types.SMALLINT);
            }

        } else {
            ps.setNull(12, Types.SMALLINT);
        }

        ps.setString(13, StringUtils.left(location, SQL_LOCATION_DIM));

        if (meetingStatus != null) {
            ps.setShort(14, meetingStatus.shortValue());
        } else {
            ps.setNull(14, Types.SMALLINT);
        }

        ps.setString(15, mileage);

        reminder = c.getReminder();
        if (reminder != null && reminder.isActive()) {
            Timestamp reminderTime = getReminderTime(dstart, reminder);
            if (reminderTime == null) {
                ps.setNull(16, Types.TIMESTAMP);
            } else {
                ps.setTimestamp(16, reminderTime);
            }
            ps.setInt(17, reminder.getRepeatCount());
            ps.setString(18, (reminder.isActive()) ? "1" : "0");

            String soundFileValue = reminder.getSoundFile();
            ps.setString(19, StringUtils.left(soundFileValue, SQL_SOUNDFILE_DIM));
            ps.setInt(20, reminder.getOptions());
        } else {
            ps.setNull(16, Types.TIMESTAMP);
            ps.setInt(17, 0);
            ps.setString(18, "0");
            ps.setString(19, null);
            ps.setInt(20, 0);
        }

        if (replyTime != null) {
            ps.setTimestamp(21, new Timestamp(replyTime.getTime()));
        } else {
            ps.setNull(21, Types.TIMESTAMP);
        }

        ps.setShort(22, sensitivity);

        if (dstart != null) {
            ps.setTimestamp(23, new Timestamp(dstart.getTime()));
        } else {
            ps.setNull(23, Types.TIMESTAMP);
        }
        ps.setString(24, StringUtils.left(subject, SQL_SUBJECT_DIM));
        ps.setShort(25, recurrenceType);
        ps.setInt(26, interval);
        ps.setShort(27, monthOfYear);
        ps.setShort(28, dayOfMonth);
        ps.setString(29, StringUtils.left(dayOfWeekMask, SQL_DAYOFWEEKMASK_DIM));
        ps.setShort(30, instance);
        ps.setString(31, StringUtils.left(startDatePattern, SQL_STARTDATEPATTERN_DIM));
        ps.setString(32, isInfinite);
        ps.setString(33, StringUtils.left(endDatePattern, SQL_ENDDATEPATTERN_DIM));
        ps.setInt(34, occurrences);

        if (c instanceof Event) {
            ps.setInt(35, CALENDAR_EVENT_TYPE);
            ps.setNull(36, Types.TIMESTAMP); // completed
            ps.setNull(37, Types.SMALLINT); // percent_complete
        } else {
            ps.setInt(35, CALENDAR_TASK_TYPE);

            if (completed != null) {
                ps.setTimestamp(36, new Timestamp(completed.getTime()));
            } else {
                ps.setNull(36, Types.TIMESTAMP);
            }

            if (percentComplete != -1) {
                ps.setShort(37, percentComplete);
            } else {
                ps.setNull(37, Types.SMALLINT);
            }
        }

        ps.setString(38, StringUtils.left(folder, SQL_FOLDER_DIM));
        ps.setString(39, StringUtils.left(dstartTimeZone, SQL_TIME_ZONE_DIM));
        ps.setString(40, StringUtils.left(dendTimeZone, SQL_TIME_ZONE_DIM));
        ps.setString(41, StringUtils.left(reminderTimeZone, SQL_TIME_ZONE_DIM));

        ps.executeUpdate();
        DBTools.close(null, ps, null);

        if (recurrenceType != -1) {
            List<ExceptionToRecurrenceRule> exceptions = rp.getExceptions();
            for (ExceptionToRecurrenceRule etrr : exceptions) {
                ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CALENDAR_EXCEPTION);

                ps.setLong(1, id);
                ps.setString(2, (etrr.isAddition() ? "1" : "0"));
                ps.setTimestamp(3,
                        new Timestamp(getDateFromString(allDayB, etrr.getDate(), "000000").getTime()));

                ps.executeUpdate();
                DBTools.close(null, ps, null);
            }
        }

    } catch (Exception e) {
        throw new DAOException("Error adding a calendar item: " + e.getMessage(), e);
    } finally {
        DBTools.close(con, ps, null);
    }
}