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.myapps.modules.database.business;
35
36 import fr.paris.lutece.plugins.myapps.business.MyApps;
37 import fr.paris.lutece.portal.service.image.ImageResource;
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
42 import org.apache.commons.lang.StringUtils;
43 import org.apache.commons.lang3.math.NumberUtils;
44
45 import java.util.ArrayList;
46 import java.util.List;
47
48
49
50
51
52
53 public final class MyAppsDatabaseDAO implements IMyAppsDatabaseDAO
54 {
55
56 private static final String SQL_QUERY_NEW_PK = " SELECT max( id_application ) FROM myapps_database_application ";
57 private static final String SQL_QUERY_SELECT = " SELECT id_application, name, description, url, code, password, data, code_heading, data_heading, icon_mime_type,code_category FROM myapps_database_application WHERE id_application = ? ";
58 private static final String SQL_QUERY_INSERT = " INSERT INTO myapps_database_application ( id_application, name, description, url, code, password, data, code_heading, data_heading, icon_content, icon_mime_type,code_category) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?,?, ? , ?, ? ) ";
59 private static final String SQL_QUERY_DELETE = " DELETE FROM myapps_database_application WHERE id_application = ? ";
60 private static final String SQL_QUERY_UPDATE = " UPDATE myapps_database_application SET name = ?, description = ?, url = ?, code = ?, password = ?, data = ?, code_heading = ?, data_heading = ?, icon_content = ?, icon_mime_type = ?,code_category = ? WHERE id_application = ? ";
61 private static final String SQL_QUERY_UPDATE_WITHOUT_ICON = " UPDATE myapps_database_application SET name = ?, description = ?, url = ?, code = ?, password = ?, data = ?, code_heading = ?, data_heading = ?,code_category = ? WHERE id_application = ? ";
62 private static final String SQL_QUERY_SELECTALL = " SELECT a.id_application, a.name, a.description, a.url, a.code, a.password, a.data, a.code_heading, a.data_heading, a.icon_mime_type ,code_category FROM myapps_database_application as a ";
63 private static final String SQL_QUERY_SELECT_MYAPPS = " SELECT a.id_application, a.name FROM myapps_database_application as a ";
64
65 private static final String SQL_QUERY_SELECT_ICON_MIME_TYPE = " SELECT icon_mime_type FROM myapps_database_application WHERE id_application = ? ";
66 private
67 static final String SQL_QUERY_WHERE = "WHERE";
68 static final String SQL_QUERY_AND = "AND";
69
70 private static final String SQL_FILTER_USER_NAME = " INNER JOIN myapps_database_user as u ON (a.id_application = u.id_application and u.name= ? )";
71 private static final String SQL_FILTER_CATEGORY = " a.code_category= ? ";
72
73 private static final String SQL_QUERY_SELECT_RESOURCE_IMAGE = " SELECT icon_content , icon_mime_type FROM myapps_database_application WHERE id_application= ? ";
74 private static final String SQL_ORDER_BY_NAME = " ORDER BY a.name ";
75 private static final String SQL_ORDER_BY_APPLICATION_ORDER = " ORDER BY u.application_order";
76 private static final String SQL_ASC = " ASC ";
77 private static final String SQL_DESC = " DESC ";
78
79
80
81
82 public int newPrimaryKey( Plugin plugin )
83 {
84 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
85 daoUtil.executeQuery( );
86
87 int nKey;
88
89 if ( !daoUtil.next( ) )
90 {
91
92 nKey = 1;
93 }
94
95 nKey = daoUtil.getInt( 1 ) + 1;
96 daoUtil.free( );
97
98 return nKey;
99 }
100
101
102
103
104 public void insert( MyAppsDatabase myApps, Plugin plugin )
105 {
106 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
107
108 int nIndex = 1;
109 myApps.setIdApplication( newPrimaryKey( plugin ) );
110
111 daoUtil.setInt( nIndex++, myApps.getIdApplication( ) );
112 daoUtil.setString( nIndex++, myApps.getName( ) );
113 daoUtil.setString( nIndex++, myApps.getDescription( ) );
114 daoUtil.setString( nIndex++, myApps.getUrl( ) );
115 daoUtil.setString( nIndex++, myApps.getCode( ) );
116 daoUtil.setString( nIndex++, myApps.getPassword( ) );
117 daoUtil.setString( nIndex++, myApps.getData( ) );
118 daoUtil.setString( nIndex++, myApps.getCodeHeading( ) );
119 daoUtil.setString( nIndex++, myApps.getDataHeading( ) );
120
121 if ( ( myApps.getIconContent( ) == null ) )
122 {
123 daoUtil.setBytes( nIndex++, null );
124 daoUtil.setString( nIndex++, StringUtils.EMPTY );
125 }
126 else
127 {
128 daoUtil.setBytes( nIndex++, myApps.getIconContent( ) );
129 daoUtil.setString( nIndex++, myApps.getIconMimeType( ) );
130 }
131 daoUtil.setString( nIndex++, myApps.getCodeCategory( ) );
132
133 daoUtil.executeUpdate( );
134 daoUtil.free( );
135 }
136
137
138
139
140 public MyApps load( int nId, Plugin plugin )
141 {
142 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
143 daoUtil.setInt( 1, nId );
144 daoUtil.executeQuery( );
145
146 MyAppsDatabase myApps = null;
147
148 if ( daoUtil.next( ) )
149 {
150 int nIndex = 1;
151 myApps = new MyAppsDatabase( );
152
153 myApps.setIdApplication( daoUtil.getInt( nIndex++ ) );
154 myApps.setName( daoUtil.getString( nIndex++ ) );
155 myApps.setDescription( daoUtil.getString( nIndex++ ) );
156 myApps.setUrl( daoUtil.getString( nIndex++ ) );
157 myApps.setCode( daoUtil.getString( nIndex++ ) );
158 myApps.setPassword( daoUtil.getString( nIndex++ ) );
159 myApps.setData( daoUtil.getString( nIndex++ ) );
160 myApps.setCodeHeading( daoUtil.getString( nIndex++ ) );
161 myApps.setDataHeading( daoUtil.getString( nIndex++ ) );
162 myApps.setIconMimeType( daoUtil.getString( nIndex++ ) );
163 myApps.setCodeCategory( daoUtil.getString( nIndex++ ) );
164 }
165
166 daoUtil.free( );
167
168 return myApps;
169 }
170
171
172
173
174 public void delete( int nMyAppsId, Plugin plugin )
175 {
176 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
177 daoUtil.setInt( 1, nMyAppsId );
178 daoUtil.executeUpdate( );
179 daoUtil.free( );
180 }
181
182
183
184
185 public void store( MyAppsDatabase myApps, boolean bUpdateIcon, Plugin plugin )
186 {
187 String strSQL = bUpdateIcon ? SQL_QUERY_UPDATE : SQL_QUERY_UPDATE_WITHOUT_ICON;
188 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
189
190 int nIndex = 1;
191 daoUtil.setString( nIndex++, myApps.getName( ) );
192 daoUtil.setString( nIndex++, myApps.getDescription( ) );
193 daoUtil.setString( nIndex++, myApps.getUrl( ) );
194 daoUtil.setString( nIndex++, myApps.getCode( ) );
195 daoUtil.setString( nIndex++, myApps.getPassword( ) );
196 daoUtil.setString( nIndex++, myApps.getData( ) );
197 daoUtil.setString( nIndex++, myApps.getCodeHeading( ) );
198 daoUtil.setString( nIndex++, myApps.getDataHeading( ) );
199
200 if ( bUpdateIcon )
201 {
202 if ( myApps.getIconContent( ) == null )
203 {
204 daoUtil.setBytes( nIndex++, null );
205 daoUtil.setString( nIndex++, StringUtils.EMPTY );
206 }
207 else
208 {
209 daoUtil.setBytes( nIndex++, myApps.getIconContent( ) );
210 daoUtil.setString( nIndex++, myApps.getIconMimeType( ) );
211 }
212 }
213 daoUtil.setString( nIndex++, myApps.getCodeCategory( ) );
214 daoUtil.setInt( nIndex++, myApps.getIdApplication( ) );
215
216 daoUtil.executeUpdate( );
217 daoUtil.free( );
218 }
219
220
221
222
223 public List<MyApps> selectMyAppsList( MyAppsDatabaseFilter filter, Plugin plugin )
224 {
225 List<MyApps> myAppsList = new ArrayList<MyApps>( );
226
227 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECTALL );
228 if ( filter != null && filter.containsCategory( ) )
229 {
230 sbSQL.append( SQL_QUERY_WHERE );
231 sbSQL.append( SQL_FILTER_CATEGORY );
232 }
233
234 DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin );
235
236 int ncpt = 0;
237 if ( filter != null && filter.containsCategory( ) )
238 {
239
240 daoUtil.setString( ++ncpt, filter.getCategory( ) );
241
242 }
243 daoUtil.executeQuery( );
244
245 while ( daoUtil.next( ) )
246 {
247 int nIndex = 1;
248 MyAppsDatabase myApps = new MyAppsDatabase( );
249
250 myApps.setIdApplication( daoUtil.getInt( nIndex++ ) );
251 myApps.setName( daoUtil.getString( nIndex++ ) );
252 myApps.setDescription( daoUtil.getString( nIndex++ ) );
253 myApps.setUrl( daoUtil.getString( nIndex++ ) );
254 myApps.setCode( daoUtil.getString( nIndex++ ) );
255 myApps.setPassword( daoUtil.getString( nIndex++ ) );
256 myApps.setData( daoUtil.getString( nIndex++ ) );
257 myApps.setCodeHeading( daoUtil.getString( nIndex++ ) );
258 myApps.setDataHeading( daoUtil.getString( nIndex++ ) );
259 myApps.setIconMimeType( daoUtil.getString( nIndex++ ) );
260 myApps.setCodeCategory( daoUtil.getString( nIndex++ ) );
261
262 myAppsList.add( myApps );
263 }
264
265 daoUtil.free( );
266
267 return myAppsList;
268 }
269
270
271
272
273 public List<MyApps> selectMyAppsListByFilter( MyAppsDatabaseFilter filter, boolean bIsAscSort, Plugin plugin )
274 {
275 List<MyApps> myAppsList = new ArrayList<MyApps>( );
276 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECTALL );
277
278 if ( filter.containsUserName( ) )
279 {
280 sbSQL.append( SQL_FILTER_USER_NAME );
281 }
282
283 if ( filter.containsCategory( ) )
284 {
285 sbSQL.append( SQL_QUERY_WHERE );
286 sbSQL.append( SQL_FILTER_CATEGORY );
287 }
288
289 sbSQL.append( SQL_ORDER_BY_NAME );
290 sbSQL.append( bIsAscSort ? SQL_ASC : SQL_DESC );
291
292 DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin );
293 int ncpt = 0;
294
295 if ( filter.containsUserName( ) )
296 {
297
298 daoUtil.setString( ++ncpt, filter.getUserName( ) );
299 }
300 if ( filter.containsCategory( ) )
301 {
302
303 daoUtil.setString( ++ncpt, filter.getCategory( ) );
304
305 }
306
307 daoUtil.executeQuery( );
308
309 while ( daoUtil.next( ) )
310 {
311 int nIndex = 1;
312 MyAppsDatabase myApps = new MyAppsDatabase( );
313
314 myApps.setIdApplication( daoUtil.getInt( nIndex++ ) );
315 myApps.setName( daoUtil.getString( nIndex++ ) );
316 myApps.setDescription( daoUtil.getString( nIndex++ ) );
317 myApps.setUrl( daoUtil.getString( nIndex++ ) );
318 myApps.setCode( daoUtil.getString( nIndex++ ) );
319 myApps.setPassword( daoUtil.getString( nIndex++ ) );
320 myApps.setData( daoUtil.getString( nIndex++ ) );
321 myApps.setCodeHeading( daoUtil.getString( nIndex++ ) );
322 myApps.setDataHeading( daoUtil.getString( nIndex++ ) );
323 myApps.setIconMimeType( daoUtil.getString( nIndex++ ) );
324 myApps.setCodeCategory( daoUtil.getString( nIndex++ ) );
325
326 myAppsList.add( myApps );
327 }
328
329 daoUtil.free( );
330
331 return myAppsList;
332 }
333
334
335
336
337 public List<MyApps> selectMyAppsListByUser( String strUserName, Plugin plugin )
338 {
339 List<MyApps> myAppsList = new ArrayList<MyApps>( );
340 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECTALL );
341 sbSQL.append( SQL_FILTER_USER_NAME );
342 sbSQL.append( SQL_ORDER_BY_APPLICATION_ORDER );
343
344 DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin );
345 daoUtil.setString( NumberUtils.INTEGER_ONE, strUserName );
346 daoUtil.executeQuery( );
347
348 while ( daoUtil.next( ) )
349 {
350 int nIndex = 1;
351 MyAppsDatabase myApps = new MyAppsDatabase( );
352
353 myApps.setIdApplication( daoUtil.getInt( nIndex++ ) );
354 myApps.setName( daoUtil.getString( nIndex++ ) );
355 myApps.setDescription( daoUtil.getString( nIndex++ ) );
356 myApps.setUrl( daoUtil.getString( nIndex++ ) );
357 myApps.setCode( daoUtil.getString( nIndex++ ) );
358 myApps.setPassword( daoUtil.getString( nIndex++ ) );
359 myApps.setData( daoUtil.getString( nIndex++ ) );
360 myApps.setCodeHeading( daoUtil.getString( nIndex++ ) );
361 myApps.setDataHeading( daoUtil.getString( nIndex++ ) );
362 myApps.setIconMimeType( daoUtil.getString( nIndex++ ) );
363 myApps.setCodeCategory( daoUtil.getString( nIndex++ ) );
364
365 myAppsList.add( myApps );
366 }
367
368 daoUtil.free( );
369
370 return myAppsList;
371 }
372
373
374
375
376 public ImageResource getIconResource( int nIdMyApps, Plugin plugin )
377 {
378 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_RESOURCE_IMAGE, plugin );
379 daoUtil.setInt( 1, nIdMyApps );
380 daoUtil.executeQuery( );
381
382 ImageResource image = null;
383
384 if ( daoUtil.next( ) )
385 {
386 int nIndex = 1;
387 image = new ImageResource( );
388 image.setImage( daoUtil.getBytes( nIndex++ ) );
389 image.setMimeType( daoUtil.getString( nIndex++ ) );
390 }
391
392 daoUtil.free( );
393
394 return image;
395 }
396
397
398
399
400 public ReferenceList getMyAppsList( Plugin plugin )
401 {
402 ReferenceList myAppsList = new ReferenceList( );
403 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_MYAPPS, plugin );
404 daoUtil.executeQuery( );
405
406 while ( daoUtil.next( ) )
407 {
408 int nIndex = 1;
409 myAppsList.addItem( daoUtil.getString( nIndex++ ), daoUtil.getString( nIndex++ ) );
410 }
411
412 daoUtil.free( );
413
414 return myAppsList;
415 }
416
417
418
419
420 public boolean hasIcon( int nIdApps, Plugin plugin )
421 {
422 boolean bHasIcon = false;
423 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ICON_MIME_TYPE, plugin );
424 daoUtil.setInt( 1, nIdApps );
425 daoUtil.executeQuery( );
426
427 if ( daoUtil.next( ) )
428 {
429 bHasIcon = true;
430 }
431
432 daoUtil.free( );
433
434 return bHasIcon;
435 }
436
437 }