/** * Reads active registry entries assigned to this server. * * @return the list of the active registry entries associated to this server * @throws com.funambol.pushlistener.service.registry.dao.DataAccessException if an error occurs */ public List<RegistryEntry> getActiveEntries() throws DataAccessException { Connection con = null; PreparedStatement ps = null; ResultSet rs = null; List<RegistryEntry> entries = new ArrayList<RegistryEntry>(); try { if (log.isTraceEnabled()) { log.trace("Executing '" + queryDesc.getReadActiveEntriesQuery() + "'"); } con = getConnection(); con.setReadOnly(true); ps = con.prepareStatement(queryDesc.getReadActiveEntriesQuery()); ps.setString(1, "Y"); rs = ps.executeQuery(); while (rs.next()) { entries.add(resultSetToRegistryEntry(rs)); } } catch (Exception e) { throw new DataAccessException(e); } finally { DBTools.close(con, ps, rs); } return entries; }
/** * Reads registry entries assigned to this server. If <code>lastUpdate</code> is different than 0, * just the changed entries with last_update bigger than the given one and the given status are * returned. * * @param lastUpdate the lastUpdate. 0 if all entries must be returned * @param status the wanted status * @throws com.funambol.pushlistener.service.registry.dao.DataAccessException if an error occurs * @return the read entries */ public List<RegistryEntry> getEntries(long lastUpdate, String status) throws DataAccessException { Connection con = null; PreparedStatement ps = null; ResultSet rs = null; List<RegistryEntry> entries = new ArrayList<RegistryEntry>(); try { con = getConnection(); con.setReadOnly(true); if (lastUpdate != 0) { // // Read only changed registry entries // if (log.isTraceEnabled()) { log.trace("Executing '" + queryDesc.getReadChangedEntriesQuery() + "'"); } ps = con.prepareStatement(queryDesc.getReadChangedEntriesQuery()); ps.setLong(1, lastUpdate); ps.setString(2, status); } else { // // Read all registry entries // if (log.isTraceEnabled()) { log.trace("Executing '" + queryDesc.getReadActiveEntriesQuery() + "'"); } ps = con.prepareStatement(queryDesc.getReadActiveEntriesQuery()); ps.setString(1, "Y"); } rs = ps.executeQuery(); while (rs.next()) { entries.add(resultSetToRegistryEntry(rs)); } } catch (Exception e) { throw new DataAccessException(e); } finally { DBTools.close(con, ps, rs); } return entries; }
/** * Marks as deleted the entry with the given id * * @param id the entry id * @throws DataAccessException if an error occurs * @return the number of updated entries */ public int markAsDeleted(long id) throws DataAccessException { Connection con = null; PreparedStatement ps = null; int rowsUpdated = 0; try { con = getConnection(); ps = con.prepareStatement(queryDesc.getUpdateStatusQuery()); ps.setLong(1, System.currentTimeMillis()); ps.setString(2, RegistryEntryStatus.DELETED); ps.setLong(3, id); rowsUpdated = ps.executeUpdate(); } catch (Exception ex) { throw new DataAccessException("Error marking entry with id '" + id + "' as deleted", ex); } finally { DBTools.close(con, ps, null); } return rowsUpdated; }
/** * Returns the registry entry with the given id * * @return the read entry * @param id the id of the pim registry entry * @throws DataAccessException if an error occurs */ public RegistryEntry getEntryById(long id) throws DataAccessException { Connection con = null; PreparedStatement ps = null; ResultSet rs = null; RegistryEntry entry = null; try { con = getConnection(); con.setReadOnly(true); ps = con.prepareStatement(queryDesc.getReadEntryQuery()); ps.setLong(1, id); rs = ps.executeQuery(); while (rs.next()) { entry = resultSetToRegistryEntry(rs); } } catch (Exception e) { throw new DataAccessException("Error reading entry with id: " + id, e); } finally { DBTools.close(con, ps, rs); } return entry; }
/** * Inserts the given entry. If no error occurs, the entry id is returned and set as id in the * given entry object. * * @param entry the entry to updated * @return the entry id * @throws DataAccessException if an error occurs */ public long insertRegistryEntry(RegistryEntry entry) throws DataAccessException { Connection con = null; PreparedStatement ps = null; long id = getNextEntryId(); try { con = getConnection(); ps = con.prepareStatement(queryDesc.getInsertEntryQuery()); ps.setLong(1, id); ps.setLong(2, entry.getPeriod()); ps.setString(3, entry.getActive() ? "Y" : "N"); ps.setString(4, entry.getTaskBeanFile()); ps.setLong(5, entry.getLastUpdate()); ps.setString(6, RegistryEntryStatus.NEW); short numberOfExtraProperties = queryDesc.getNumberOfExtraColumns(); String[] extraProperties = queryDesc.getExtraProperties(); if (numberOfExtraProperties > 0) { for (short j = 0; j < numberOfExtraProperties; j++) { Object value = null; if (entry.hasProperty(extraProperties[j])) { value = entry.getProperty(extraProperties[j]); } ps.setObject(7 + j, value); } } ps.executeUpdate(); entry.setId(id); } catch (Exception e) { throw new DataAccessException("Error inserting a registry entry", e); } finally { DBTools.close(con, ps, null); } return id; }
/** * Updates the given entry. If no error occurs, the number of updated rows is returned. * * @param entry the entry to updated * @return the number of updated rows * @throws DataAccessException if an error occurs */ public int updateRegistryEntry(RegistryEntry entry) throws DataAccessException { Connection con = null; PreparedStatement ps = null; int rowsUpdated = 0; try { con = getConnection(); ps = con.prepareStatement(queryDesc.getUpdateEntryQuery()); ps.setLong(1, entry.getPeriod()); ps.setString(2, entry.getActive() ? "Y" : "N"); ps.setString(3, entry.getTaskBeanFile()); ps.setLong(4, entry.getLastUpdate()); ps.setString(5, RegistryEntryStatus.UPDATED); short numberOfExtraProperties = queryDesc.getNumberOfExtraColumns(); String[] extraProperties = queryDesc.getExtraProperties(); if (numberOfExtraProperties > 0) { short j = 0; for (; j < numberOfExtraProperties; j++) { Object value = null; if (entry.hasProperty(extraProperties[j])) { value = entry.getProperty(extraProperties[j]); } ps.setObject(6 + j, value); } ps.setLong(6 + j, entry.getId()); } else { ps.setLong(6, entry.getId()); } rowsUpdated = ps.executeUpdate(); } catch (Exception e) { throw new DataAccessException("Error updating registry entry '" + entry.getId() + "'", e); } finally { DBTools.close(con, ps, null); } return rowsUpdated; }
/** * Creates a RegistryEntry reading the given ResultSet * * @return the RegistryEntry * @param rs the resultSet * @throws java.sql.SQLException if an error occurs */ private RegistryEntry resultSetToRegistryEntry(ResultSet rs) throws SQLException { RegistryEntry entry = new RegistryEntry(); entry.setId(rs.getLong(1)); entry.setPeriod(rs.getLong(2)); entry.setActive("Y".equalsIgnoreCase(rs.getString(3))); entry.setTaskBeanFile(rs.getString(4)); entry.setLastUpdate(rs.getLong(5)); String s = rs.getString(6); if (s == null || s.length() == 0) { entry.setStatus(RegistryEntryStatus.UNKNOWN); } if ("N".equals(s)) { entry.setStatus(RegistryEntryStatus.NEW); } else if ("U".equals(s)) { entry.setStatus(RegistryEntryStatus.UPDATED); } else if ("D".equals(s)) { entry.setStatus(RegistryEntryStatus.DELETED); } else { entry.setStatus(RegistryEntryStatus.UNKNOWN); } short numberOfExtraProperties = queryDesc.getNumberOfExtraColumns(); String[] extraProperties = queryDesc.getExtraProperties(); if (numberOfExtraProperties > 0) { Map<String, Object> extraPropertiesMap = new LinkedHashMap<String, Object>(); for (short j = 0; j < numberOfExtraProperties; j++) { extraPropertiesMap.put(extraProperties[j], rs.getObject(7 + j)); } entry.setProperties(extraPropertiesMap); } return entry; }
/** * This method allows to establish how many extra property columns are specified in the given * ResultSet. Besides, it builds the two strings used to complete the query. * * @param rs it's the ResultSet to analyze. * @param basicColumnsSet it's the set of labels that must be considered as basic. * @throws java.sql.SQLException if any error occurs analyzing the ResultSet object. */ protected void updateExtraPropertyInfo(ResultSet rs) throws SQLException { short numberOfExtraColumns = 0; String[] extraProperties = new String[0]; columnSuffix = new StringBuilder(); updateSuffix = new StringBuilder(); placeholderSuffix = new StringBuilder(); if (rs != null) { ResultSetMetaData metaData = rs.getMetaData(); int numberOfColumns = metaData.getColumnCount(); numberOfExtraColumns = (short) (numberOfColumns - basicColumns.size()); extraProperties = new String[numberOfExtraColumns]; int j = 0; if (numberOfExtraColumns > 0) { for (int i = 1; i <= numberOfColumns; i++) { String columnName = metaData.getColumnName(i); if (columnName != null) { String columnKey = columnName.toLowerCase(); if (!basicColumns.contains(columnKey)) { extraProperties[j] = columnKey; columnSuffix.append(", "); columnSuffix.append(columnName); // create the suffix used to insert the extra properties placeholderSuffix.append(", "); placeholderSuffix.append("? "); // create the suffix used to update the extra properties updateSuffix.append(", "); updateSuffix.append(columnName); updateSuffix.append(" =? "); j++; } } } } } queryDesc.setNumberOfExtraColumns(numberOfExtraColumns); queryDesc.setExtraProperties(extraProperties); }
/** * This method allows to create all queries used by the push listener framework * * @param registryTableName the name of the table storing information about the push listener * entries * @param clusterSize number of the members in the cluster * @param serverIndex unique index of the instance in the cluster */ private void createQueries(String registryTableName, int clusterSize, int serverIndex) { String segmentationClause = null; String queryReadEntries = MessageFormat.format(SQL_READ_ENTRIES, new Object[] {columnSuffix, registryTableName}); StringBuffer queryReadChangedEntries = new StringBuffer(queryReadEntries); StringBuffer queryReadActiveEntries = new StringBuffer(queryReadEntries); StringBuffer queryDeleteEntries = new StringBuffer( MessageFormat.format(SQL_DELETE_ENTRIES, new Object[] {registryTableName})); if (clusterSize > 1) { segmentationClause = MessageFormat.format( SQL_SEGMENTATION_CLAUSE, new Object[] {String.valueOf(clusterSize), String.valueOf(serverIndex)}); queryReadChangedEntries.append(segmentationClause).append(" and "); queryReadActiveEntries.append(segmentationClause).append(" and "); queryDeleteEntries.append(" and ").append(segmentationClause); } queryReadChangedEntries.append(SQL_WHERECLAUSE_STATUS); queryDesc.setReadChangedEntriesQuery(queryReadChangedEntries.toString()); queryReadActiveEntries.append(SQL_WHERECLAUSE_ACTIVE_ENTRIES); queryDesc.setReadActiveEntriesQuery(queryReadActiveEntries.toString()); queryDesc.setReadEntryQuery( MessageFormat.format(SQL_READ_ENTRY, new Object[] {columnSuffix, registryTableName})); queryDesc.setDeleteEntryQuery( MessageFormat.format(SQL_DELETE_ENTRY, new Object[] {registryTableName})); queryDesc.setDeleteEntriesQuery(queryDeleteEntries.toString()); queryDesc.setUpdateEntryQuery( MessageFormat.format(SQL_UPDATE_ENTRY, new Object[] {registryTableName, updateSuffix})); queryDesc.setUpdateStatusQuery( MessageFormat.format(SQL_UPDATE_STATUS, new Object[] {registryTableName})); queryDesc.setInsertEntryQuery( MessageFormat.format( SQL_INSERT_ENTRY, new Object[] {registryTableName, columnSuffix, placeholderSuffix})); }
/** * Removes the entry with status 'D' associated to this server * * @return the number of the removed entries * @throws com.funambol.pushlistener.service.registry.dao.DataAccessException if an error occurs */ public int removeAllDeletedRegistryEntry() throws DataAccessException { Connection con = null; PreparedStatement ps = null; int rowsDeleted = 0; try { con = getConnection(); ps = con.prepareStatement(queryDesc.getDeleteEntriesQuery()); rowsDeleted = ps.executeUpdate(); return rowsDeleted; } catch (Exception e) { throw new DataAccessException(e); } finally { DBTools.close(con, ps, null); } }
/** * Deletes the entry with the given id * * @param id the id of the entry to delete * @return true if the entry is deleted, false otherwise * @throws com.funambol.pushlistener.service.registry.dao.DataAccessException if an error occurs */ public boolean deleteRegistryEntry(long id) throws DataAccessException { Connection con = null; PreparedStatement ps = null; int rowsDeleted = 0; try { con = getConnection(); ps = con.prepareStatement(queryDesc.getDeleteEntryQuery()); ps.setLong(1, id); rowsDeleted = ps.executeUpdate(); return (rowsDeleted > 0); } catch (Exception e) { throw new DataAccessException(e); } finally { DBTools.close(con, ps, null); } }