1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 package fr.paris.lutece.plugins.rss.business;
35
36 import fr.paris.lutece.util.ReferenceList;
37 import fr.paris.lutece.util.sql.DAOUtil;
38
39 import java.util.ArrayList;
40 import java.util.List;
41
42
43
44
45
46 public final class RssFeedDAO implements IRssFeedDAO
47 {
48
49
50
51 private static final String SQL_QUERY_NEW_PK = " SELECT max( id_rss_feed ) FROM rss_feed ";
52 private static final String SQL_QUERY_SELECT = " SELECT id_rss_feed, name, url, last_fetch_date, last_fetch_status, last_fetch_error, workgroup_key, include_style FROM rss_feed WHERE id_rss_feed = ? ";
53 private static final String SQL_QUERY_INSERT = " INSERT INTO rss_feed ( id_rss_feed, name, url, last_fetch_date, last_fetch_status, last_fetch_error, workgroup_key, include_style ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? ) ";
54 private static final String SQL_QUERY_DELETE = " DELETE FROM rss_feed WHERE id_rss_feed = ? ";
55 private static final String SQL_QUERY_UPDATE = " UPDATE rss_feed SET id_rss_feed = ?, name = ?, url = ?, workgroup_key = ?, include_style = ? WHERE id_rss_feed = ? ";
56 private static final String SQL_QUERY_SELECTALL = " SELECT id_rss_feed, name, url, last_fetch_date, last_fetch_status, last_fetch_error, workgroup_key, include_style FROM rss_feed ";
57 private static final String SQL_QUERY_UPDATE_LAST_FETCH_INFOS = " UPDATE rss_feed SET last_fetch_date = ?, last_fetch_status = ?, last_fetch_error = ? WHERE id_rss_feed = ? ";
58
59
60 private static final String SQL_QUERY_NEW_PK_OFF = " SELECT max( id_rss_feed ) FROM rss_feed_inactive ";
61 private static final String SQL_QUERY_SELECT_OFF = " SELECT id_rss_feed, name, url, last_fetch_date, last_fetch_status, last_fetch_error, workgroup_key, include_style FROM rss_feed_inactive WHERE id_rss_feed = ? ";
62 private static final String SQL_QUERY_INSERT_OFF = " INSERT INTO rss_feed_inactive ( id_rss_feed, name, url, last_fetch_date, last_fetch_status, last_fetch_error, workgroup_key, include_style ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? ) ";
63 private static final String SQL_QUERY_DELETE_OFF = " DELETE FROM rss_feed_inactive WHERE id_rss_feed = ? ";
64 private static final String SQL_QUERY_UPDATE_OFF = " UPDATE rss_feed_inactive SET id_rss_feed = ?, name = ?, url = ?, workgroup_key = ?, include_style = ? WHERE id_rss_feed = ? ";
65 private static final String SQL_QUERY_SELECTALL_OFF = " SELECT id_rss_feed, name, url, last_fetch_date, last_fetch_status, last_fetch_error, workgroup_key, include_style FROM rss_feed_inactive ";
66 private static final String SQL_QUERY_UPDATE_LAST_FETCH_INFOS_OFF = " UPDATE rss_feed_inactive SET last_fetch_date = ?, last_fetch_status = ?, last_fetch_error = ? WHERE id_rss_feed = ? ";
67 private static final String SQL_QUERY_SELECT_URL = "SELECT url FROM rss_feed WHERE url = ? UNION SELECT url FROM rss_feed_inactive WHERE url = ? ";
68
69
70
71
72
73
74 public int newPrimaryKey( boolean bActive )
75 {
76 int nKey;
77 try( DAOUtil daoUtil = new DAOUtil( bActive ? SQL_QUERY_NEW_PK : SQL_QUERY_NEW_PK_OFF ) )
78 {
79 daoUtil.executeQuery( );
80
81 if ( !daoUtil.next( ) )
82 {
83
84 nKey = 1;
85 }
86
87 nKey = daoUtil.getInt( 1 ) + 1;
88 }
89
90 return nKey;
91 }
92
93
94
95
96
97 public void insert( RssFeed rssFeed )
98 {
99 boolean bActive = rssFeed.getIsActive( );
100 try( DAOUtil daoUtil = new DAOUtil( bActive ? SQL_QUERY_INSERT : SQL_QUERY_INSERT_OFF ) )
101 {
102 rssFeed.setId( newPrimaryKey( bActive ) );
103 daoUtil.setInt( 1, rssFeed.getId( ) );
104 daoUtil.setString( 2, rssFeed.getName( ) );
105 daoUtil.setString( 3, rssFeed.getUrl( ) );
106 daoUtil.setTimestamp( 4, rssFeed.getLastFetchDate( ) );
107 daoUtil.setInt( 5, rssFeed.getLastFetchStatus( ) );
108 daoUtil.setString( 6, rssFeed.getLastFetchError( ) );
109 daoUtil.setString( 7, rssFeed.getWorkgroup( ) );
110 daoUtil.setInt( 8, rssFeed.getIdIncludeStyle( ) );
111
112 daoUtil.executeUpdate( );
113 }
114 }
115
116
117
118
119
120
121
122 public RssFeed load( int nRssFeedId, boolean bActive )
123 {
124 RssFeed rssFeed = null;
125
126 try( DAOUtil daoUtil = new DAOUtil( bActive ? SQL_QUERY_SELECT : SQL_QUERY_SELECT_OFF ) )
127 {
128 daoUtil.setInt( 1, nRssFeedId );
129 daoUtil.executeQuery( );
130
131 if ( daoUtil.next( ) )
132 {
133 rssFeed = new RssFeed( );
134 rssFeed.setIsActive( bActive );
135 rssFeed.setId( daoUtil.getInt( 1 ) );
136 rssFeed.setName( daoUtil.getString( 2 ) );
137 rssFeed.setUrl( daoUtil.getString( 3 ) );
138 rssFeed.setLastFetchDate( daoUtil.getTimestamp( 4 ) );
139 rssFeed.setLastFetchStatus( daoUtil.getInt( 5 ) );
140 rssFeed.setLastFetchError( daoUtil.getString( 6 ) );
141 rssFeed.setWorkgroup( daoUtil.getString( 7 ) );
142 rssFeed.setIdIncludeStyle( daoUtil.getInt( 8 ) );
143 }
144 }
145
146 return rssFeed;
147 }
148
149
150
151
152
153 public void delete( RssFeed rssFeed )
154 {
155 try( DAOUtil daoUtil = new DAOUtil( rssFeed.getIsActive( ) ? SQL_QUERY_DELETE : SQL_QUERY_DELETE_OFF ) )
156 {
157 daoUtil.setInt( 1, rssFeed.getId( ) );
158
159 daoUtil.executeUpdate( );
160 }
161 }
162
163
164
165
166
167 public void store( RssFeed rssFeed )
168 {
169 try( DAOUtil daoUtil = new DAOUtil( rssFeed.getIsActive( ) ? SQL_QUERY_UPDATE : SQL_QUERY_UPDATE_OFF ) )
170 {
171 daoUtil.setInt( 1, rssFeed.getId( ) );
172 daoUtil.setString( 2, rssFeed.getName( ) );
173 daoUtil.setString( 3, rssFeed.getUrl( ) );
174 daoUtil.setString( 4, rssFeed.getWorkgroup( ) );
175 daoUtil.setInt( 5, rssFeed.getIdIncludeStyle( ) );
176
177 daoUtil.setInt( 6, rssFeed.getId( ) );
178
179 daoUtil.executeUpdate( );
180 }
181 }
182
183
184
185
186
187 public void storeLastFetchInfos( RssFeed rssFeed )
188 {
189 try( DAOUtil daoUtil = new DAOUtil( rssFeed.getIsActive( ) ? SQL_QUERY_UPDATE_LAST_FETCH_INFOS
190 : SQL_QUERY_UPDATE_LAST_FETCH_INFOS_OFF ) )
191 {
192 daoUtil.setTimestamp( 1, rssFeed.getLastFetchDate( ) );
193 daoUtil.setInt( 2, rssFeed.getLastFetchStatus( ) );
194 daoUtil.setString( 3, rssFeed.getLastFetchError( ) );
195 daoUtil.setInt( 4, rssFeed.getId( ) );
196
197 daoUtil.executeUpdate( );
198 }
199 }
200
201
202
203
204
205
206 public List<RssFeed> selectRssFeeds( boolean bActive )
207 {
208 List<RssFeed> list = new ArrayList<>( );
209 try( DAOUtil daoUtil = new DAOUtil( bActive ? SQL_QUERY_SELECTALL : SQL_QUERY_SELECTALL_OFF ) )
210 {
211 daoUtil.executeQuery( );
212
213 while ( daoUtil.next( ) )
214 {
215 RssFeedrss/business/RssFeed.html#RssFeed">RssFeed rssFeed = new RssFeed( );
216
217 rssFeed.setIsActive( bActive );
218 rssFeed.setId( daoUtil.getInt( 1 ) );
219 rssFeed.setName( daoUtil.getString( 2 ) );
220 rssFeed.setUrl( daoUtil.getString( 3 ) );
221 rssFeed.setLastFetchDate( daoUtil.getTimestamp( 4 ) );
222 rssFeed.setLastFetchStatus( daoUtil.getInt( 5 ) );
223 rssFeed.setLastFetchError( daoUtil.getString( 6 ) );
224 rssFeed.setWorkgroup( daoUtil.getString( 7 ) );
225 rssFeed.setIdIncludeStyle( daoUtil.getInt( 8 ) );
226
227 list.add( rssFeed );
228 }
229
230 }
231
232 return list;
233 }
234
235
236
237
238
239
240 public boolean checkUrlNotUsed( String strUrl )
241 {
242 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_URL ) )
243 {
244 daoUtil.setString( 1, strUrl );
245 daoUtil.setString( 2, strUrl );
246 daoUtil.executeQuery( );
247
248 if ( daoUtil.next( ) )
249 {
250 daoUtil.free( );
251
252 return false;
253 }
254 }
255
256 return true;
257 }
258
259
260
261
262
263
264 public ReferenceList selectRssFeedReferenceList( boolean bActive )
265 {
266 ReferenceList listRssFeeds = new ReferenceList( );
267 try( DAOUtil daoUtil = new DAOUtil( bActive ? SQL_QUERY_SELECTALL : SQL_QUERY_SELECTALL_OFF ) )
268 {
269 daoUtil.executeQuery( );
270
271 while ( daoUtil.next( ) )
272 {
273 RssFeedrss/business/RssFeed.html#RssFeed">RssFeed rssFeed = new RssFeed( );
274 rssFeed.setId( daoUtil.getInt( 1 ) );
275 rssFeed.setName( daoUtil.getString( 2 ) );
276
277 listRssFeeds.addItem( rssFeed.getId( ), rssFeed.getName( ) );
278 }
279 }
280
281 return listRssFeeds;
282 }
283 }