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
35
36 package fr.paris.lutece.plugins.broadcastproxy.business;
37
38 import fr.paris.lutece.portal.service.plugin.Plugin;
39 import fr.paris.lutece.util.ReferenceList;
40 import fr.paris.lutece.util.sql.DAOUtil;
41 import java.sql.Statement;
42
43 import java.util.ArrayList;
44 import java.util.List;
45 import java.util.Optional;
46
47
48
49
50 public final class SubscriptionLinkDAO implements ISubscriptionLinkDAO
51 {
52
53 private static final String SQL_QUERY_SELECT = "SELECT id_subscription_link, label, pictogramme, description, frequency, subscription_group, group_id, subscription_id, interest_id, enabled FROM broadcastproxy_subscription_link WHERE id_subscription_link = ?";
54 private static final String SQL_QUERY_SELECT_SUBSCRIPTION_ID = "SELECT id_subscription_link, label, pictogramme, description, frequency, subscription_group, group_id, subscription_id, interest_id, enabled FROM broadcastproxy_subscription_link WHERE subscription_id = ?";
55 private static final String SQL_QUERY_INSERT = "INSERT INTO broadcastproxy_subscription_link ( label, pictogramme, description, frequency, subscription_group, group_id, subscription_id, interest_id, enabled ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
56 private static final String SQL_QUERY_DELETE = "DELETE FROM broadcastproxy_subscription_link WHERE id_subscription_link = ? ";
57 private static final String SQL_QUERY_UPDATE = "UPDATE broadcastproxy_subscription_link SET label = ?, pictogramme = ?, description = ?, frequency = ?, subscription_group = ?, group_id = ?, subscription_id = ?, interest_id = ?, enabled = ? WHERE id_subscription_link = ?";
58 private static final String SQL_QUERY_SELECTALL = "SELECT id_subscription_link, label, pictogramme, description, frequency, subscription_group, group_id, subscription_id, interest_id, enabled FROM broadcastproxy_subscription_link ORDER BY label asc";
59 private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_subscription_link FROM broadcastproxy_subscription_link";
60 private static final String SQL_QUERY_SELECTALL_BY_IDS = "SELECT id_subscription_link, label, pictogramme, description, frequency, subscription_group, group_id, subscription_id, interest_id, enabled FROM broadcastproxy_subscription_link WHERE id_subscription_link IN ( ";
61 private static final String SQL_QUERY_DISABLE_NEWSLETTER = "SELECT id_subscription_link FROM broadcastproxy_subscription_link WHERE enabled = 0";
62
63
64
65
66 @Override
67 public void insert( SubscriptionLink subscriptionLink, Plugin plugin )
68 {
69 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin ) )
70 {
71 int nIndex = 1;
72 daoUtil.setString( nIndex++ , subscriptionLink.getLabel( ) );
73 daoUtil.setString( nIndex++ , subscriptionLink.getPictogramme( ) );
74 daoUtil.setString( nIndex++ , subscriptionLink.getDescription( ) );
75 daoUtil.setString( nIndex++ , subscriptionLink.getFrequency( ) );
76 daoUtil.setString( nIndex++ , subscriptionLink.getGroup( ) );
77 daoUtil.setInt( nIndex++ , subscriptionLink.getGroupId( ) );
78 daoUtil.setInt( nIndex++ , subscriptionLink.getSubscriptionId( ) );
79 daoUtil.setInt( nIndex++ , subscriptionLink.getInterestId( ) );
80 daoUtil.setBoolean( nIndex++, subscriptionLink.isEnabled( ) );
81
82 daoUtil.executeUpdate( );
83 if ( daoUtil.nextGeneratedKey( ) )
84 {
85 subscriptionLink.setId( daoUtil.getGeneratedKeyInt( 1 ) );
86 }
87 }
88
89 }
90
91
92
93
94 @Override
95 public Optional<SubscriptionLink> load( int nKey, Plugin plugin )
96 {
97 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
98 {
99 daoUtil.setInt( 1 , nKey );
100 daoUtil.executeQuery( );
101 SubscriptionLink subscriptionLink = null;
102
103 if ( daoUtil.next( ) )
104 {
105 subscriptionLink = new SubscriptionLink();
106 int nIndex = 1;
107
108 subscriptionLink.setId( daoUtil.getInt( nIndex++ ) );
109 subscriptionLink.setLabel( daoUtil.getString( nIndex++ ) );
110 subscriptionLink.setPictogramme( daoUtil.getString( nIndex++ ) );
111 subscriptionLink.setDescription( daoUtil.getString( nIndex++ ) );
112 subscriptionLink.setFrequency( daoUtil.getString( nIndex++ ) );
113 subscriptionLink.setGroup( daoUtil.getString( nIndex++ ) );
114 subscriptionLink.setGroupId( daoUtil.getInt( nIndex++ ) );
115 subscriptionLink.setSubscriptionId( daoUtil.getInt( nIndex++ ) );
116 subscriptionLink.setInterestId( daoUtil.getInt( nIndex++ ) );
117 subscriptionLink.setEnabled( daoUtil.getBoolean( nIndex ) );
118 }
119
120 return Optional.ofNullable( subscriptionLink );
121 }
122 }
123
124
125
126
127 @Override
128 public void delete( int nKey, Plugin plugin )
129 {
130 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
131 {
132 daoUtil.setInt( 1 , nKey );
133 daoUtil.executeUpdate( );
134 }
135 }
136
137
138
139
140 @Override
141 public void store( SubscriptionLink subscriptionLink, Plugin plugin )
142 {
143 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
144 {
145 int nIndex = 1;
146
147 daoUtil.setString( nIndex++ , subscriptionLink.getLabel( ) );
148 daoUtil.setString( nIndex++ , subscriptionLink.getPictogramme( ) );
149 daoUtil.setString( nIndex++ , subscriptionLink.getDescription( ) );
150 daoUtil.setString( nIndex++ , subscriptionLink.getFrequency( ) );
151 daoUtil.setString( nIndex++ , subscriptionLink.getGroup( ) );
152 daoUtil.setInt( nIndex++ , subscriptionLink.getGroupId( ) );
153 daoUtil.setInt( nIndex++ , subscriptionLink.getSubscriptionId( ) );
154 daoUtil.setInt( nIndex++ , subscriptionLink.getInterestId( ) );
155 daoUtil.setBoolean( nIndex++, subscriptionLink.isEnabled( ) );
156 daoUtil.setInt( nIndex++ , subscriptionLink.getId( ) );
157
158 daoUtil.executeUpdate( );
159 }
160 }
161
162
163
164
165 @Override
166 public List<SubscriptionLink> selectSubscriptionLinksList( Plugin plugin )
167 {
168 List<SubscriptionLink> subscriptionLinkList = new ArrayList<>( );
169 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
170 {
171 daoUtil.executeQuery( );
172
173 while ( daoUtil.next( ) )
174 {
175 SubscriptionLinkiness/SubscriptionLink.html#SubscriptionLink">SubscriptionLink subscriptionLink = new SubscriptionLink( );
176 int nIndex = 1;
177
178 subscriptionLink.setId( daoUtil.getInt( nIndex++ ) );
179 subscriptionLink.setLabel( daoUtil.getString( nIndex++ ) );
180 subscriptionLink.setPictogramme( daoUtil.getString( nIndex++ ) );
181 subscriptionLink.setDescription( daoUtil.getString( nIndex++ ) );
182 subscriptionLink.setFrequency( daoUtil.getString( nIndex++ ) );
183 subscriptionLink.setGroup( daoUtil.getString( nIndex++ ) );
184 subscriptionLink.setGroupId( daoUtil.getInt( nIndex++ ) );
185 subscriptionLink.setSubscriptionId( daoUtil.getInt( nIndex++ ) );
186 subscriptionLink.setInterestId( daoUtil.getInt( nIndex++ ) );
187 subscriptionLink.setEnabled( daoUtil.getBoolean( nIndex ) );
188
189 subscriptionLinkList.add( subscriptionLink );
190 }
191
192 return subscriptionLinkList;
193 }
194 }
195
196
197
198
199 @Override
200 public List<Integer> selectIdSubscriptionLinksList( Plugin plugin )
201 {
202 List<Integer> subscriptionLinkList = new ArrayList<>( );
203 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin ) )
204 {
205 daoUtil.executeQuery( );
206
207 while ( daoUtil.next( ) )
208 {
209 subscriptionLinkList.add( daoUtil.getInt( 1 ) );
210 }
211
212 return subscriptionLinkList;
213 }
214 }
215
216
217
218
219 @Override
220 public ReferenceList selectSubscriptionLinksReferenceList( Plugin plugin )
221 {
222 ReferenceList subscriptionLinkList = new ReferenceList();
223 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
224 {
225 daoUtil.executeQuery( );
226
227 while ( daoUtil.next( ) )
228 {
229 subscriptionLinkList.addItem( daoUtil.getInt( 1 ) , daoUtil.getString( 2 ) );
230 }
231
232 return subscriptionLinkList;
233 }
234 }
235
236
237
238
239 @Override
240 public List<SubscriptionLink> selectSubscriptionLinksListByIds( Plugin plugin, List<Integer> listIds ) {
241 List<SubscriptionLink> subscriptionLinkList = new ArrayList<>( );
242
243 StringBuilder builder = new StringBuilder( );
244
245 if ( !listIds.isEmpty( ) )
246 {
247 for( int i = 0 ; i < listIds.size(); i++ ) {
248 builder.append( "?," );
249 }
250
251 String placeHolders = builder.deleteCharAt( builder.length( ) -1 ).toString( );
252 String stmt = SQL_QUERY_SELECTALL_BY_IDS + placeHolders + ")";
253
254
255 try ( DAOUtil daoUtil = new DAOUtil( stmt, plugin ) )
256 {
257 int index = 1;
258 for( Integer n : listIds ) {
259 daoUtil.setInt( index++, n );
260 }
261
262 daoUtil.executeQuery( );
263 while ( daoUtil.next( ) )
264 {
265 SubscriptionLink subscriptionLink = new SubscriptionLink( );
266 int nIndex = 1;
267
268 subscriptionLink.setId( daoUtil.getInt( nIndex++ ) );
269 subscriptionLink.setLabel( daoUtil.getString( nIndex++ ) );
270 subscriptionLink.setPictogramme( daoUtil.getString( nIndex++ ) );
271 subscriptionLink.setDescription( daoUtil.getString( nIndex++ ) );
272 subscriptionLink.setFrequency( daoUtil.getString( nIndex++ ) );
273 subscriptionLink.setGroup( daoUtil.getString( nIndex++ ) );
274 subscriptionLink.setGroupId( daoUtil.getInt( nIndex++ ) );
275 subscriptionLink.setSubscriptionId( daoUtil.getInt( nIndex++ ) );
276 subscriptionLink.setInterestId( daoUtil.getInt( nIndex++ ) );
277 subscriptionLink.setEnabled( daoUtil.getBoolean( nIndex ) );
278
279 subscriptionLinkList.add( subscriptionLink );
280 }
281
282 daoUtil.free( );
283
284 }
285 }
286 return subscriptionLinkList;
287
288 }
289
290 @Override
291 public Optional<SubscriptionLink> loadBySubscriptionId( int nSubscriptionId, Plugin plugin )
292 {
293 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_SUBSCRIPTION_ID, plugin ) )
294 {
295 daoUtil.setInt( 1 , nSubscriptionId );
296 daoUtil.executeQuery( );
297 SubscriptionLink subscriptionLink = null;
298
299 if ( daoUtil.next( ) )
300 {
301 subscriptionLink = new SubscriptionLink();
302 int nIndex = 1;
303
304 subscriptionLink.setId( daoUtil.getInt( nIndex++ ) );
305 subscriptionLink.setLabel( daoUtil.getString( nIndex++ ) );
306 subscriptionLink.setPictogramme( daoUtil.getString( nIndex++ ) );
307 subscriptionLink.setDescription( daoUtil.getString( nIndex++ ) );
308 subscriptionLink.setFrequency( daoUtil.getString( nIndex++ ) );
309 subscriptionLink.setGroup( daoUtil.getString( nIndex++ ) );
310 subscriptionLink.setGroupId( daoUtil.getInt( nIndex++ ) );
311 subscriptionLink.setSubscriptionId( daoUtil.getInt( nIndex++ ) );
312 subscriptionLink.setInterestId( daoUtil.getInt( nIndex++ ) );
313 subscriptionLink.setEnabled( daoUtil.getBoolean( nIndex ) );
314
315 }
316
317 return Optional.ofNullable( subscriptionLink );
318 }
319 }
320
321 @Override
322 public boolean existDisabledNewsletter( Plugin plugin )
323 {
324 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DISABLE_NEWSLETTER, plugin ) )
325 {
326 daoUtil.executeQuery( );
327 return daoUtil.next( );
328 }
329 }
330 }