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 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 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT );
100 daoUtil.setInt( 1, link.getId( ) );
101 daoUtil.setString( 2, link.getName( ) );
102 daoUtil.setString( 3, link.getDescription( ) );
103 daoUtil.setDate( 4, link.getDate( ) );
104 daoUtil.setString( 5, link.getUrl( ) );
105 daoUtil.setString( 7, link.getWorkgroupKey( ) );
106
107 if ( ( link.getImageContent( ) == null ) )
108 {
109 daoUtil.setBytes( 6, null );
110 daoUtil.setString( 8, "" );
111 }
112 else
113 {
114 daoUtil.setBytes( 6, link.getImageContent( ) );
115 daoUtil.setString( 8, link.getMimeType( ) );
116 }
117
118 daoUtil.executeUpdate( );
119
120 insertUrlsList( link );
121 daoUtil.free( );
122 }
123
124
125
126
127
128
129 public void delete( int nLinkId )
130 {
131 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE );
132 daoUtil.setInt( 1, nLinkId );
133
134 daoUtil.executeUpdate( );
135
136 daoUtil.free( );
137 daoUtil = new DAOUtil( DELETE_URLS_SQL );
138 daoUtil.setInt( 1, nLinkId );
139
140 daoUtil.executeUpdate( );
141 daoUtil.free( );
142 }
143
144
145
146
147
148
149
150 public Link load( int nLinkId )
151 {
152 Link link = new Link( );
153 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT );
154
155 daoUtil.setInt( 1, nLinkId );
156
157 daoUtil.executeQuery( );
158
159 if ( daoUtil.next( ) )
160 {
161 link.setId( nLinkId );
162 link.setName( daoUtil.getString( 1 ) );
163 link.setDescription( daoUtil.getString( 2 ) );
164 link.setDate( daoUtil.getDate( 3 ) );
165 link.setUrl( daoUtil.getString( 4 ) );
166 link.setId( daoUtil.getInt( 5 ) );
167 link.setImageContent( daoUtil.getBytes( 6 ) );
168 link.setWorkgroupKey( daoUtil.getString( 7 ) );
169 link.setMimeType( daoUtil.getString( 8 ) );
170 link.setOptionalUrls( this.selectUrlsList( nLinkId ) );
171 }
172
173 daoUtil.free( );
174
175 return link;
176 }
177
178
179
180
181
182
183 public void store( Link link )
184 {
185 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE );
186 daoUtil.setString( 1, link.getName( ) );
187 daoUtil.setString( 2, link.getDescription( ) );
188 daoUtil.setDate( 3, link.getDate( ) );
189 daoUtil.setString( 4, link.getUrl( ) );
190 daoUtil.setBytes( 5, link.getImageContent( ) );
191 daoUtil.setString( 6, link.getWorkgroupKey( ) );
192 daoUtil.setString( 7, link.getMimeType( ) );
193
194 daoUtil.setInt( 8, link.getId( ) );
195
196 daoUtil.executeUpdate( );
197
198 daoUtil.free( );
199
200 daoUtil = new DAOUtil( DELETE_URLS_SQL );
201 daoUtil.setInt( 1, link.getId( ) );
202 daoUtil.executeUpdate( );
203
204 insertUrlsList( link );
205
206 daoUtil.free( );
207 }
208
209
210
211
212
213
214 public int newPrimaryKey( )
215 {
216 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PRIMARY_KEY );
217 int nKey;
218
219 daoUtil.executeQuery( );
220
221 if ( !daoUtil.next( ) )
222 {
223
224 nKey = 1;
225 }
226
227 nKey = daoUtil.getInt( 1 ) + 1;
228
229 daoUtil.free( );
230
231 return nKey;
232 }
233
234
235
236
237
238
239 public Collection<Link> selectList( )
240 {
241 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL );
242
243 daoUtil.executeQuery( );
244
245 ArrayList<Link> list = new ArrayList<Link>( );
246
247 while ( daoUtil.next( ) )
248 {
249 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 daoUtil.free( );
264
265 return list;
266 }
267
268
269
270
271
272
273
274 public Collection<Link> selectByPortlet( int nIdPortlet )
275 {
276 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_PORTLET );
277 daoUtil.setInt( 1, nIdPortlet );
278
279 daoUtil.executeQuery( );
280
281 ArrayList<Link> list = new ArrayList<Link>( );
282
283 while ( daoUtil.next( ) )
284 {
285 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 daoUtil.free( );
298
299 return list;
300 }
301
302
303
304
305
306
307
308 private ReferenceList selectUrlsList( int idLink )
309 {
310 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_URLS_LIST );
311
312
313 daoUtil.setInt( 1, idLink );
314
315 daoUtil.executeQuery( );
316
317 ReferenceList list = new ReferenceList( );
318
319 while ( daoUtil.next( ) )
320 {
321 list.addItem( daoUtil.getString( 1 ), daoUtil.getString( 2 ) );
322 }
323
324 daoUtil.free( );
325
326 return list;
327 }
328
329
330
331
332
333
334 private void insertUrlsList( Link link )
335 {
336
337 DAOUtil daoUtil = new DAOUtil( INSERT_URLS_SQL );
338
339 for ( ReferenceItem item : link.getOptionalUrls( ) )
340 {
341 daoUtil.setInt( 1, link.getId( ) );
342 daoUtil.setString( 2, item.getCode( ) );
343 daoUtil.setString( 3, item.getName( ) );
344
345 daoUtil.executeUpdate( );
346 }
347
348 daoUtil.free( );
349 }
350
351
352
353
354 public ImageResource loadImageResource( int nIdLink )
355 {
356 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_RESOURCE_IMAGE );
357 daoUtil.setInt( 1, nIdLink );
358 daoUtil.executeQuery( );
359
360 ImageResource image = null;
361
362 if ( daoUtil.next( ) )
363 {
364 image = new ImageResource( );
365 image.setImage( daoUtil.getBytes( 1 ) );
366 image.setMimeType( daoUtil.getString( 2 ) );
367 }
368
369 daoUtil.free( );
370
371 return image;
372 }
373 }