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 package fr.paris.lutece.plugins.myportal.modules.myfavorites.business;
36
37 import fr.paris.lutece.portal.service.plugin.Plugin;
38 import fr.paris.lutece.util.ReferenceList;
39 import fr.paris.lutece.util.sql.DAOUtil;
40
41 import java.util.ArrayList;
42 import java.util.List;
43
44 import org.apache.commons.lang3.math.NumberUtils;
45
46
47
48
49 public final class MyFavoritesDAO implements IMyFavoritesDAO
50 {
51
52 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_my_favorites ) FROM myportal_myfavorites_myfavorites";
53 private static final String SQL_QUERY_SELECT = "SELECT id_my_favorites, url, id_icon, label, user_id, myfavorites_order FROM myportal_myfavorites_myfavorites WHERE id_my_favorites = ?";
54 private static final String SQL_QUERY_INSERT = "INSERT INTO myportal_myfavorites_myfavorites ( id_my_favorites, url, id_icon, label, user_id, myfavorites_order ) VALUES ( ?, ?, ?, ?, ?, ? ) ";
55 private static final String SQL_QUERY_DELETE = "DELETE FROM myportal_myfavorites_myfavorites WHERE id_my_favorites = ? ";
56 private static final String SQL_QUERY_UPDATE = "UPDATE myportal_myfavorites_myfavorites SET id_my_favorites = ?, url = ?, id_icon = ?, label = ?, user_id = ?, myfavorites_order = ? WHERE id_my_favorites = ?";
57 private static final String SQL_QUERY_SELECTALL = "SELECT id_my_favorites, url, id_icon, label, user_id, myfavorites_order FROM myportal_myfavorites_myfavorites";
58 private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_my_favorites FROM myportal_myfavorites_myfavorites";
59 private static final String SQL_QUERY_SELECT_BY_USER = "SELECT id_my_favorites, url, id_icon, label, user_id, myfavorites_order FROM myportal_myfavorites_myfavorites WHERE user_id = ? ORDER BY myfavorites_order";
60 private static final String SQL_QUERY_SELECT_ORDER_BY_USER = "SELECT myfavorites_order FROM myportal_myfavorites_myfavorites WHERE user_id = ? ORDER BY myfavorites_order";
61 private static final String SQL_QUERY_SELECT_BY_ORDER = "SELECT id_my_favorites, url, id_icon, label, user_id, myfavorites_order FROM myportal_myfavorites_myfavorites WHERE user_id = ? AND myfavorites_order = ?";
62 private static final String SQL_QUERY_UPDATE_ORDER = "UPDATE myportal_myfavorites_myfavorites SET myfavorites_order = ? WHERE id_my_favorites = ?";
63
64
65
66
67
68
69
70
71 public int newPrimaryKey( Plugin plugin )
72 {
73 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
74 daoUtil.executeQuery( );
75 int nKey = 1;
76
77 if ( daoUtil.next( ) )
78 {
79 nKey = daoUtil.getInt( 1 ) + 1;
80 }
81
82 daoUtil.free( );
83 return nKey;
84 }
85
86
87
88
89 @Override
90 public void insert( MyFavorites myFavorites, Plugin plugin )
91 {
92 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
93 myFavorites.setId( newPrimaryKey( plugin ) );
94 int nIndex = 1;
95
96 daoUtil.setInt( nIndex++, myFavorites.getId( ) );
97 daoUtil.setString( nIndex++, myFavorites.getUrl( ) );
98 daoUtil.setInt( nIndex++, myFavorites.getIdIcon( ) );
99 daoUtil.setString( nIndex++, myFavorites.getLabel( ) );
100 daoUtil.setString( nIndex++, myFavorites.getIdUser( ) );
101 daoUtil.setInt( nIndex++, myFavorites.getOrder( ) );
102
103 daoUtil.executeUpdate( );
104 daoUtil.free( );
105 }
106
107
108
109
110 @Override
111 public MyFavorites load( int nKey, Plugin plugin )
112 {
113 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
114 daoUtil.setInt( 1, nKey );
115 daoUtil.executeQuery( );
116 MyFavorites myFavorites = null;
117
118 if ( daoUtil.next( ) )
119 {
120 myFavorites = new MyFavorites( );
121 int nIndex = 1;
122
123 myFavorites.setId( daoUtil.getInt( nIndex++ ) );
124 myFavorites.setUrl( daoUtil.getString( nIndex++ ) );
125 myFavorites.setIdIcon( daoUtil.getInt( nIndex++ ) );
126 myFavorites.setLabel( daoUtil.getString( nIndex++ ) );
127 myFavorites.setIdUser( daoUtil.getString( nIndex++ ) );
128 myFavorites.setOrder( daoUtil.getInt( nIndex++ ) );
129 }
130
131 daoUtil.free( );
132 return myFavorites;
133 }
134
135
136
137
138 @Override
139 public void delete( int nKey, Plugin plugin )
140 {
141 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
142 daoUtil.setInt( 1, nKey );
143 daoUtil.executeUpdate( );
144 daoUtil.free( );
145 }
146
147
148
149
150 @Override
151 public void store( MyFavorites myFavorites, Plugin plugin )
152 {
153 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
154 int nIndex = 1;
155
156 daoUtil.setInt( nIndex++, myFavorites.getId( ) );
157 daoUtil.setString( nIndex++, myFavorites.getUrl( ) );
158 daoUtil.setInt( nIndex++, myFavorites.getIdIcon( ) );
159 daoUtil.setString( nIndex++, myFavorites.getLabel( ) );
160 daoUtil.setString( nIndex++, myFavorites.getIdUser( ) );
161 daoUtil.setInt( nIndex++, myFavorites.getOrder( ) );
162
163 daoUtil.setInt( nIndex, myFavorites.getId( ) );
164
165 daoUtil.executeUpdate( );
166 daoUtil.free( );
167 }
168
169
170
171
172 @Override
173 public List<MyFavorites> selectMyFavoritesList( String idUser, Plugin plugin )
174 {
175 List<MyFavorites> myFavoritesList = new ArrayList<MyFavorites>( );
176 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_USER, plugin );
177 daoUtil.setString( 1, idUser );
178
179 daoUtil.executeQuery( );
180
181 while ( daoUtil.next( ) )
182 {
183 MyFavorites myFavorites = new MyFavorites( );
184 int nIndex = 1;
185
186 myFavorites.setId( daoUtil.getInt( nIndex++ ) );
187 myFavorites.setUrl( daoUtil.getString( nIndex++ ) );
188 myFavorites.setIdIcon( daoUtil.getInt( nIndex++ ) );
189 myFavorites.setLabel( daoUtil.getString( nIndex++ ) );
190 myFavorites.setIdUser( daoUtil.getString( nIndex++ ) );
191 myFavorites.setOrder( daoUtil.getInt( nIndex++ ) );
192
193 myFavoritesList.add( myFavorites );
194 }
195
196 daoUtil.free( );
197 return myFavoritesList;
198 }
199
200
201
202
203 @Override
204 public List<MyFavorites> selectMyFavoritessList( Plugin plugin )
205 {
206 List<MyFavorites> myFavoritesList = new ArrayList<MyFavorites>( );
207 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
208 daoUtil.executeQuery( );
209
210 while ( daoUtil.next( ) )
211 {
212 MyFavorites myFavorites = new MyFavorites( );
213 int nIndex = 1;
214
215 myFavorites.setId( daoUtil.getInt( nIndex++ ) );
216 myFavorites.setUrl( daoUtil.getString( nIndex++ ) );
217 myFavorites.setIdIcon( daoUtil.getInt( nIndex++ ) );
218 myFavorites.setLabel( daoUtil.getString( nIndex++ ) );
219 myFavorites.setIdUser( daoUtil.getString( nIndex++ ) );
220 myFavorites.setOrder( daoUtil.getInt( nIndex++ ) );
221
222 myFavoritesList.add( myFavorites );
223 }
224
225 daoUtil.free( );
226 return myFavoritesList;
227 }
228
229
230
231
232 @Override
233 public List<Integer> selectIdMyFavoritessList( Plugin plugin )
234 {
235 List<Integer> myFavoritesList = new ArrayList<Integer>( );
236 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin );
237 daoUtil.executeQuery( );
238
239 while ( daoUtil.next( ) )
240 {
241 myFavoritesList.add( daoUtil.getInt( 1 ) );
242 }
243
244 daoUtil.free( );
245 return myFavoritesList;
246 }
247
248
249
250
251 @Override
252 public ReferenceList selectMyFavoritessReferenceList( Plugin plugin )
253 {
254 ReferenceList myFavoritesList = new ReferenceList( );
255 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
256 daoUtil.executeQuery( );
257
258 while ( daoUtil.next( ) )
259 {
260 myFavoritesList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
261 }
262
263 daoUtil.free( );
264 return myFavoritesList;
265 }
266
267
268
269
270 @Override
271 public List<Integer> selectMyFavoritesOrderList( String strIdUser, Plugin plugin )
272 {
273 List<Integer> myFavoritesOrderList = new ArrayList<Integer>( );
274
275 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ORDER_BY_USER, plugin );
276 daoUtil.setString( 1, strIdUser );
277 daoUtil.executeQuery( );
278
279 while ( daoUtil.next( ) )
280 {
281 myFavoritesOrderList.add( daoUtil.getInt( NumberUtils.INTEGER_ONE ) );
282 }
283 daoUtil.free( );
284
285 return myFavoritesOrderList;
286 }
287
288
289
290
291 @Override
292 public MyFavorites selectUserFavoriteByOrder( String strIdUser, int nOrder, Plugin plugin )
293 {
294 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ORDER, plugin );
295 daoUtil.setString( 1, strIdUser );
296 daoUtil.setInt( 2, nOrder );
297 daoUtil.executeQuery( );
298 MyFavorites myFavorites = null;
299
300 if ( daoUtil.next( ) )
301 {
302 myFavorites = new MyFavorites( );
303 int nIndex = 1;
304
305 myFavorites.setId( daoUtil.getInt( nIndex++ ) );
306 myFavorites.setUrl( daoUtil.getString( nIndex++ ) );
307 myFavorites.setIdIcon( daoUtil.getInt( nIndex++ ) );
308 myFavorites.setLabel( daoUtil.getString( nIndex++ ) );
309 myFavorites.setIdUser( daoUtil.getString( nIndex++ ) );
310 myFavorites.setOrder( daoUtil.getInt( nIndex++ ) );
311 }
312
313 daoUtil.free( );
314
315 return myFavorites;
316 }
317
318
319
320
321 @Override
322 public void updateFavoritesOrder( int nFavoritesId, int nOrder, Plugin plugin )
323 {
324 if ( nFavoritesId > 0 && nOrder > 0 )
325 {
326 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_ORDER, plugin );
327 int nIndex = 1;
328
329 daoUtil.setInt( nIndex++, nOrder );
330 daoUtil.setInt( nIndex, nFavoritesId );
331
332 daoUtil.executeUpdate( );
333 daoUtil.free( );
334 }
335 }
336 }