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.links.business;
35
36 import fr.paris.lutece.portal.service.image.ImageResource;
37 import fr.paris.lutece.util.ReferenceItem;
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.Collection;
43
44
45
46
47
48 public final class LinkDAO implements ILinkDAO
49 {
50
51 private static LinkDAOns/links/business/LinkDAO.html#LinkDAO">LinkDAO _dao = new LinkDAO( );
52 private static final String INSERT_URLS_SQL = "INSERT INTO link_virtual_host (id_link, virtual_host_key, url) " +
53 " VALUES ( ?, ?, ?)";
54 private static final String DELETE_URLS_SQL = "DELETE FROM link_virtual_host WHERE id_link = ?";
55 private static final String SQL_QUERY_INSERT = "INSERT INTO link ( id_link, name, description, date, url, image_content, workgroup_key, mime_type ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?)";
56 private static final String SQL_QUERY_DELETE = "DELETE FROM link WHERE id_link = ?";
57 private static final String SQL_QUERY_SELECT = "SELECT name, description, date, url, id_link, image_content, workgroup_key, mime_type FROM link WHERE id_link = ?";
58 private static final String SQL_QUERY_SELECT_URLS_LIST = "SELECT virtual_host_key, url FROM link_virtual_host WHERE id_link = ?";
59 private static final String SQL_QUERY_UPDATE = "UPDATE link SET name = ?, description = ?, date = ?, url=?, image_content=?, workgroup_key=?, mime_type=? WHERE id_link = ?";
60 private static final String SQL_QUERY_NEW_PRIMARY_KEY = "SELECT max(id_link) FROM link";
61 private static final String SQL_QUERY_SELECT_ALL = "SELECT id_link , name , description, date, url, image_content, workgroup_key, mime_type" +
62 " FROM link ORDER BY name";
63 private static final String SQL_QUERY_SELECT_BY_PORTLET = " SELECT a.id_link , a.name, a.url, a.description, a.image_content, a.workgroup_key, a.mime_type" +
64 " FROM link a , link_list_portlet b" + " WHERE a.id_link = b.id_link " + " AND b.id_portlet = ? " +
65 " ORDER BY b.link_order ";
66 private static final String SQL_QUERY_SELECT_RESOURCE_IMAGE = " SELECT image_content , mime_type FROM link " +
67 " WHERE id_link = ? ";
68
69
70
71
72 private LinkDAO( )
73 {
74 }
75
76
77
78
79
80
81 static LinkDAO getInstance( )
82 {
83 return _dao;
84 }
85
86
87
88
89
90
91
92
93
94 public void insert( Link link )
95 {
96 int nNewPrimaryKey = newPrimaryKey( );
97 link.setId( nNewPrimaryKey );
98
99 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT ) )
100 {
101 daoUtil.setInt( 1, link.getId( ) );
102 daoUtil.setString( 2, link.getName( ) );
103 daoUtil.setString( 3, link.getDescription( ) );
104 daoUtil.setDate( 4, link.getDate( ) );
105 daoUtil.setString( 5, link.getUrl( ) );
106 daoUtil.setString( 7, link.getWorkgroupKey( ) );
107
108 if ( ( link.getImageContent( ) == null ) )
109 {
110 daoUtil.setBytes( 6, null );
111 daoUtil.setString( 8, "" );
112 }
113 else
114 {
115 daoUtil.setBytes( 6, link.getImageContent( ) );
116 daoUtil.setString( 8, link.getMimeType( ) );
117 }
118
119 daoUtil.executeUpdate( );
120
121 insertUrlsList( link );
122 }
123 }
124
125
126
127
128
129
130 public void delete( int nLinkId )
131 {
132 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
133 {
134 daoUtil.setInt( 1, nLinkId );
135 daoUtil.executeUpdate( );
136 }
137
138 try( DAOUtil daoUtil = new DAOUtil( DELETE_URLS_SQL ) )
139 {
140 daoUtil.setInt( 1, nLinkId );
141 daoUtil.executeUpdate( );
142 }
143 }
144
145
146
147
148
149
150
151 public Link load( int nLinkId )
152 {
153 Linkugins/links/business/Link.html#Link">Link link = new Link( );
154 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT ) )
155 {
156 daoUtil.setInt( 1, nLinkId );
157
158 daoUtil.executeQuery( );
159
160 if ( daoUtil.next( ) )
161 {
162 link.setId( nLinkId );
163 link.setName( daoUtil.getString( 1 ) );
164 link.setDescription( daoUtil.getString( 2 ) );
165 link.setDate( daoUtil.getDate( 3 ) );
166 link.setUrl( daoUtil.getString( 4 ) );
167 link.setId( daoUtil.getInt( 5 ) );
168 link.setImageContent( daoUtil.getBytes( 6 ) );
169 link.setWorkgroupKey( daoUtil.getString( 7 ) );
170 link.setMimeType( daoUtil.getString( 8 ) );
171 link.setOptionalUrls( this.selectUrlsList( nLinkId ) );
172 }
173 }
174
175 return link;
176 }
177
178
179
180
181
182
183 public void store( Link link )
184 {
185 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
186 {
187 daoUtil.setString( 1, link.getName( ) );
188 daoUtil.setString( 2, link.getDescription( ) );
189 daoUtil.setDate( 3, link.getDate( ) );
190 daoUtil.setString( 4, link.getUrl( ) );
191 daoUtil.setBytes( 5, link.getImageContent( ) );
192 daoUtil.setString( 6, link.getWorkgroupKey( ) );
193 daoUtil.setString( 7, link.getMimeType( ) );
194
195 daoUtil.setInt( 8, link.getId( ) );
196
197 daoUtil.executeUpdate( );
198 }
199
200 try( DAOUtil daoUtil = new DAOUtil( DELETE_URLS_SQL ) )
201 {
202 daoUtil.setInt( 1, link.getId( ) );
203 daoUtil.executeUpdate( );
204
205 insertUrlsList( link );
206 }
207 }
208
209
210
211
212
213
214 public int newPrimaryKey( )
215 {
216 int nKey;
217
218 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PRIMARY_KEY ) )
219 {
220 daoUtil.executeQuery( );
221
222 if ( !daoUtil.next( ) )
223 {
224
225 nKey = 1;
226 }
227
228 nKey = daoUtil.getInt( 1 ) + 1;
229 }
230
231 return nKey;
232 }
233
234
235
236
237
238
239 public Collection<Link> selectList( )
240 {
241 ArrayList<Link> list = new ArrayList<>( );
242
243 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL ) )
244 {
245 daoUtil.executeQuery( );
246
247 while ( daoUtil.next( ) )
248 {
249 Linkugins/links/business/Link.html#Link">Link link = new Link( );
250 link.setId( daoUtil.getInt( 1 ) );
251 link.setName( daoUtil.getString( 2 ) );
252 link.setDescription( daoUtil.getString( 3 ) );
253 link.setDate( daoUtil.getDate( 4 ) );
254 link.setUrl( daoUtil.getString( 5 ) );
255 link.setImageContent( daoUtil.getBytes( 6 ) );
256 link.setWorkgroupKey( daoUtil.getString( 7 ) );
257 link.setMimeType( daoUtil.getString( 8 ) );
258 link.setOptionalUrls( this.selectUrlsList( daoUtil.getInt( 1 ) ) );
259
260 list.add( link );
261 }
262 }
263
264 return list;
265 }
266
267
268
269
270
271
272
273 public Collection<Link> selectByPortlet( int nIdPortlet )
274 {
275 ArrayList<Link> list = new ArrayList<>( );
276
277 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_PORTLET ) )
278 {
279 daoUtil.setInt( 1, nIdPortlet );
280
281 daoUtil.executeQuery( );
282
283 while ( daoUtil.next( ) )
284 {
285 Linkugins/links/business/Link.html#Link">Link link = new Link( );
286 link.setId( daoUtil.getInt( 1 ) );
287 link.setName( daoUtil.getString( 2 ) );
288 link.setUrl( daoUtil.getString( 3 ) );
289 link.setDescription( daoUtil.getString( 4 ) );
290 link.setImageContent( daoUtil.getBytes( 5 ) );
291 link.setWorkgroupKey( daoUtil.getString( 6 ) );
292 link.setMimeType( daoUtil.getString( 7 ) );
293 link.setOptionalUrls( this.selectUrlsList( daoUtil.getInt( 1 ) ) );
294 list.add( link );
295 }
296
297 }
298
299 return list;
300 }
301
302
303
304
305
306
307
308 private ReferenceList selectUrlsList( int idLink )
309 {
310 ReferenceList list = new ReferenceList( );
311
312 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_URLS_LIST ) )
313 {
314
315 daoUtil.setInt( 1, idLink );
316 daoUtil.executeQuery( );
317
318 while ( daoUtil.next( ) )
319 {
320 list.addItem( daoUtil.getString( 1 ), daoUtil.getString( 2 ) );
321 }
322 }
323
324 return list;
325 }
326
327
328
329
330
331
332 private void insertUrlsList( Link link )
333 {
334
335 try( DAOUtil daoUtil = new DAOUtil( INSERT_URLS_SQL ) )
336 {
337 for ( ReferenceItem item : link.getOptionalUrls( ) )
338 {
339 daoUtil.setInt( 1, link.getId( ) );
340 daoUtil.setString( 2, item.getCode( ) );
341 daoUtil.setString( 3, item.getName( ) );
342
343 daoUtil.executeUpdate( );
344 }
345 }
346 }
347
348
349
350
351 public ImageResource loadImageResource( int nIdLink )
352 {
353 ImageResource image = null;
354
355 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_RESOURCE_IMAGE ) )
356 {
357 daoUtil.setInt( 1, nIdLink );
358 daoUtil.executeQuery( );
359
360 if ( daoUtil.next( ) )
361 {
362 image = new ImageResource( );
363 image.setImage( daoUtil.getBytes( 1 ) );
364 image.setMimeType( daoUtil.getString( 2 ) );
365 }
366 }
367
368 return image;
369 }
370 }