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.subscribe.business;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.sql.DAOUtil;
38
39 import java.util.ArrayList;
40 import java.util.Collection;
41 import java.util.List;
42
43 import org.apache.commons.lang3.StringUtils;
44
45
46
47
48
49 public final class SubscriptionDAO implements ISubscriptionDAO
50 {
51
52
53 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_subscription ) FROM subscribe_subscription";
54 private static final String SQL_QUERY_SELECT = " SELECT id_subscription, id_user, subscription_provider, subscription_key, id_subscribed_resource, subscription_order FROM subscribe_subscription ";
55 private static final String SQL_QUERY_SELECT_FROM_SUBSCRIPTION_ID = SQL_QUERY_SELECT + " WHERE id_subscription = ? ";
56 private static final String SQL_QUERY_INSERT = "INSERT INTO subscribe_subscription ( id_subscription, id_user, subscription_provider, subscription_key, id_subscribed_resource, subscription_order ) VALUES ( ?, ?, ?, ?, ?, ? ) ";
57 private static final String SQL_QUERY_DELETE = "DELETE FROM subscribe_subscription WHERE id_subscription = ? ";
58 private static final String SQL_QUERY_UPDATE = "UPDATE subscribe_subscription SET id_user = ?, subscription_provider = ?, subscription_key = ?, id_subscribed_resource = ?, subscription_order = ? WHERE id_subscription = ?";
59 private static final String SQL_QUERY_SELECTALL = "SELECT id_subscription, id_user, subscription_provider, subscription_key, id_subscribed_resource, subscription_order FROM subscribe_subscription";
60 private static final String SQL_QUERY_MAX_ORDER = "SELECT max( subscription_order ) FROM subscribe_subscription";
61
62 private static final String SQL_FILTER_ID_USER = " id_user = ? ";
63 private static final String SQL_FILTER_PROVIDER = " subscription_provider = ? ";
64 private static final String SQL_FILTER_SUBSCRIPTION_KEY = " subscription_key = ? ";
65 private static final String SQL_FILTER_ID_SUBSCRIBED_RESOURCE = " id_subscribed_resource = ? ";
66 private static final String CONSTANT_WHERE = " WHERE ";
67 private static final String CONSTANT_AND = " AND ";
68
69
70
71
72
73
74
75
76 private int newPrimaryKey( Plugin plugin )
77 {
78 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin ) )
79 {
80 daoUtil.executeQuery( );
81
82 int nKey = 1;
83 if ( daoUtil.next( ) )
84 {
85 nKey = daoUtil.getInt( 1 ) + 1;
86 }
87
88 daoUtil.free( );
89 return nKey;
90 }
91 }
92
93
94
95
96 @Override
97 public synchronized void insert( Subscription subscription, Plugin plugin )
98 {
99 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin ) )
100 {
101
102 subscription.setIdSubscription( newPrimaryKey( plugin ) );
103
104 daoUtil.setInt( 1, subscription.getIdSubscription( ) );
105 daoUtil.setString( 2, subscription.getUserId( ) );
106 daoUtil.setString( 3, subscription.getSubscriptionProvider( ) );
107 daoUtil.setString( 4, subscription.getSubscriptionKey( ) );
108 daoUtil.setString( 5, subscription.getIdSubscribedResource( ) );
109 daoUtil.setInt( 6, getMaxOrder( plugin ) + 1 );
110
111 daoUtil.executeUpdate( );
112 daoUtil.free( );
113 }
114 }
115
116
117
118
119 @Override
120 public Subscription load( int nId, Plugin plugin )
121 {
122 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_FROM_SUBSCRIPTION_ID, plugin ) )
123 {
124 daoUtil.setInt( 1, nId );
125 daoUtil.executeQuery( );
126
127 Subscription subscription = null;
128
129 if ( daoUtil.next( ) )
130 {
131 subscription = new Subscription( );
132 subscription.setIdSubscription( daoUtil.getInt( 1 ) );
133 subscription.setUserId( daoUtil.getString( 2 ) );
134 subscription.setSubscriptionProvider( daoUtil.getString( 3 ) );
135 subscription.setSubscriptionKey( daoUtil.getString( 4 ) );
136 subscription.setIdSubscribedResource( daoUtil.getString( 5 ) );
137 subscription.setOrder( daoUtil.getInt( 6 ) );
138 }
139
140 daoUtil.free( );
141 return subscription;
142 }
143 }
144
145
146
147
148 @Override
149 public void delete( int nSubscriptionId, Plugin plugin )
150 {
151 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
152 {
153 daoUtil.setInt( 1, nSubscriptionId );
154 daoUtil.executeUpdate( );
155 daoUtil.free( );
156 }
157 }
158
159
160
161
162 @Override
163 public void store( Subscription subscription, Plugin plugin )
164 {
165 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
166 {
167
168 daoUtil.setString( 1, subscription.getUserId( ) );
169 daoUtil.setString( 2, subscription.getSubscriptionProvider( ) );
170 daoUtil.setString( 3, subscription.getSubscriptionKey( ) );
171 daoUtil.setString( 4, subscription.getIdSubscribedResource( ) );
172 daoUtil.setInt( 5, subscription.getOrder( ) );
173
174 daoUtil.setInt( 6, subscription.getIdSubscription( ) );
175
176 daoUtil.executeUpdate( );
177 daoUtil.free( );
178 }
179 }
180
181
182
183
184 @Override
185 public Collection<Subscription> selectSubscriptionsList( Plugin plugin )
186 {
187 Collection<Subscription> subscriptionList = new ArrayList<>( );
188 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
189 {
190 daoUtil.executeQuery( );
191
192 while ( daoUtil.next( ) )
193 {
194 Subscriptionbusiness/Subscription.html#Subscription">Subscription subscription = new Subscription( );
195
196 subscription.setIdSubscription( daoUtil.getInt( 1 ) );
197 subscription.setUserId( daoUtil.getString( 2 ) );
198 subscription.setSubscriptionProvider( daoUtil.getString( 3 ) );
199 subscription.setSubscriptionKey( daoUtil.getString( 4 ) );
200 subscription.setIdSubscribedResource( daoUtil.getString( 5 ) );
201 subscription.setOrder( daoUtil.getInt( 6 ) );
202
203 subscriptionList.add( subscription );
204 }
205
206 daoUtil.free( );
207 }
208 return subscriptionList;
209 }
210
211
212
213
214 @Override
215 public List<Subscription> findByFilter( SubscriptionFilter filter )
216 {
217 List<Subscription> listSubscription = new ArrayList<>( );
218 boolean bHasFilter = false;
219 StringBuilder sbSql = new StringBuilder( SQL_QUERY_SELECT );
220 if ( StringUtils.isNotEmpty( filter.getUserId( ) ) )
221 {
222 sbSql.append( CONSTANT_WHERE );
223 sbSql.append( SQL_FILTER_ID_USER );
224 bHasFilter = true;
225 }
226 if ( filter.getSubscriptionProvider( ) != null )
227 {
228 if ( bHasFilter )
229 {
230 sbSql.append( CONSTANT_AND );
231 }
232 else
233 {
234 sbSql.append( CONSTANT_WHERE );
235 bHasFilter = true;
236 }
237 sbSql.append( SQL_FILTER_PROVIDER );
238 }
239 if ( filter.getSubscriptionKey( ) != null )
240 {
241 if ( bHasFilter )
242 {
243 sbSql.append( CONSTANT_AND );
244 }
245 else
246 {
247 sbSql.append( CONSTANT_WHERE );
248 bHasFilter = true;
249 }
250 sbSql.append( SQL_FILTER_SUBSCRIPTION_KEY );
251 }
252 if ( filter.getIdSubscribedResource( ) != null )
253 {
254 if ( bHasFilter )
255 {
256 sbSql.append( CONSTANT_AND );
257 }
258 else
259 {
260 sbSql.append( CONSTANT_WHERE );
261 bHasFilter = true;
262 }
263 sbSql.append( SQL_FILTER_ID_SUBSCRIBED_RESOURCE );
264 }
265
266 int nIndex = 1;
267 try ( DAOUtil daoUtil = new DAOUtil( sbSql.toString( ) ) )
268 {
269 if ( StringUtils.isNotEmpty( filter.getUserId( ) ) )
270 {
271 daoUtil.setString( nIndex++, filter.getUserId( ) );
272 }
273 if ( filter.getSubscriptionProvider( ) != null )
274 {
275 daoUtil.setString( nIndex++, filter.getSubscriptionProvider( ) );
276 }
277 if ( filter.getSubscriptionKey( ) != null )
278 {
279 daoUtil.setString( nIndex++, filter.getSubscriptionKey( ) );
280 }
281 if ( filter.getIdSubscribedResource( ) != null )
282 {
283
284 daoUtil.setString( nIndex, filter.getIdSubscribedResource( ) );
285 }
286 daoUtil.executeQuery( );
287
288 while ( daoUtil.next( ) )
289 {
290 Subscriptionbusiness/Subscription.html#Subscription">Subscription subscription = new Subscription( );
291 subscription.setIdSubscription( daoUtil.getInt( 1 ) );
292 subscription.setUserId( daoUtil.getString( 2 ) );
293 subscription.setSubscriptionProvider( daoUtil.getString( 3 ) );
294 subscription.setSubscriptionKey( daoUtil.getString( 4 ) );
295 subscription.setIdSubscribedResource( daoUtil.getString( 5 ) );
296 subscription.setOrder( daoUtil.getInt( 6 ) );
297 listSubscription.add( subscription );
298 }
299
300 daoUtil.free( );
301 }
302
303 return listSubscription;
304 }
305
306 @Override
307 public int getMaxOrder( Plugin plugin )
308 {
309 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MAX_ORDER ) )
310 {
311 daoUtil.executeQuery( );
312
313 while ( daoUtil.next( ) )
314 {
315 return daoUtil.getInt( 1 );
316 }
317 }
318 return 0;
319 }
320 }