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.dila.business.fichelocale.dao.impl;
35
36 import fr.paris.lutece.plugins.dila.business.enums.ResourceTypeEnum;
37 import fr.paris.lutece.plugins.dila.business.fichelocale.dao.IXmlDAO;
38 import fr.paris.lutece.plugins.dila.business.fichelocale.dto.XmlDTO;
39 import fr.paris.lutece.plugins.dila.service.DilaPlugin;
40 import fr.paris.lutece.portal.service.plugin.PluginService;
41 import fr.paris.lutece.util.sql.DAOUtil;
42
43 import java.io.Serializable;
44 import java.sql.Date;
45 import java.util.ArrayList;
46 import java.util.List;
47
48
49
50
51
52 public class XmlDAO implements IXmlDAO, Serializable
53 {
54
55 private static final long serialVersionUID = -907799297326123041L;
56 private static final String SQL_QUERY_NEW_PK = "SELECT max(id) FROM dila_xml";
57 private static final String SQL_QUERY_FIND_TITLE_BY_ID = "SELECT title FROM dila_xml WHERE id_xml = ? ";
58 private static final String SQL_QUERY_FIND_FOLDER_BY_ID = "SELECT title, breadcrumb FROM dila_xml WHERE id_xml = ? ";
59 private static final String SQL_QUERY_FIND_TITLE_BY_ID_AND_TYPES_AND_AUDIENCE = "SELECT title FROM dila_xml "
60 + "WHERE id_xml = ? AND audience_id = ? ";
61 private static final String SQL_QUERY_FIND_FODLER_BY_ID_AND_TYPES_AND_AUDIENCE = "SELECT id, title, breadcrumb FROM dila_xml "
62 + "WHERE id_xml = ? AND audience_id = ? ";
63 private static final String SQL_QUERY_AND_RESOURCE_TYPE = "AND type_resource IN ";
64 private static final String SQL_QUERY_FIND_RESOURCE_TYPE_BY_IDXML_AND_AUDIENCE = "SELECT type_resource FROM dila_xml"
65 + " WHERE id_xml = ? AND audience_id = ?";
66 private static final String SQL_QUERY_FIND_ALL = "SELECT id, id_xml, title, breadcrumb, audience_id, type_resource, date_creation, date_modification FROM dila_xml ";
67 private static final String SQL_QUERY_FIND_HOW_TO_BY_AUDIENCE = "SELECT id_xml, title FROM dila_xml "
68 + "WHERE audience_id = ? AND type_resource = ? ORDER BY date_modification DESC";
69 private static final String SQL_QUERY_FIND_ID_BY_XML_AND_AUDIENCE = "SELECT id FROM dila_xml "
70 + "WHERE id_xml = ? AND audience_id = ?";
71 private static final String SQL_QUERY_INSERT = " INSERT INTO dila_xml ( id, id_xml , title , type_resource, breadcrumb, audience_id, date_creation, date_modification ) "
72 + " VALUES ( ?, ? ,?, ?, ?, ?, ?, ? )";
73 private static final String SQL_QUERY_UPDATE = " UPDATE dila_xml SET title = ?, type_resource = ?, breadcrumb = ?, date_modification = ? WHERE id_xml = ? AND audience_id = ? ";
74 private static final String SQL_QUERY_DELETE = " DELETE FROM dila_xml WHERE date_modification < ?";
75
76
77
78
79
80 private Long newPrimaryKey( )
81 {
82 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
83 daoUtil.executeQuery( );
84
85 Long nKey = 1L;
86
87 if ( daoUtil.next( ) )
88 {
89 nKey = daoUtil.getLong( 1 ) + 1L;
90 }
91
92 daoUtil.free( );
93
94 return nKey;
95 }
96
97 @Override
98 public String findTitleById( String id )
99 {
100 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_TITLE_BY_ID, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
101 daoUtil.setString( 1, id );
102
103 daoUtil.executeQuery( );
104
105 String title = null;
106
107 if ( daoUtil.next( ) )
108 {
109 title = daoUtil.getString( 1 );
110 }
111
112 daoUtil.free( );
113
114 return title;
115 }
116
117 @Override
118 public XmlDTO findFolderById( String id )
119 {
120 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FOLDER_BY_ID, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
121 daoUtil.setString( 1, id );
122
123 daoUtil.executeQuery( );
124
125 XmlDTO xml = null;
126
127 if ( daoUtil.next( ) )
128 {
129 xml = new XmlDTO( );
130
131 xml.setIdXml( id );
132 xml.setTitle( daoUtil.getString( 1 ) );
133 xml.setBreadCrumb( daoUtil.getString( 2 ) );
134 }
135
136 daoUtil.free( );
137
138 return xml;
139 }
140
141 @Override
142 public String findTitleByIdAndTypesAndAudience( String idDossierFrere, List<String> availableTypes, Long idAudience )
143 {
144 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_FIND_TITLE_BY_ID_AND_TYPES_AND_AUDIENCE );
145
146 sbSQL.append( " " );
147 sbSQL.append( SQL_QUERY_AND_RESOURCE_TYPE );
148 sbSQL.append( "(" );
149
150 int count = 0;
151
152 for ( String type : availableTypes )
153 {
154 count++;
155
156 if ( count < availableTypes.size( ) )
157 {
158 sbSQL.append( "'" + type + "'," );
159 }
160 else
161 {
162 sbSQL.append( "'" + type + "'" );
163 }
164 }
165
166 sbSQL.append( ")" );
167
168 DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
169 daoUtil.setString( 1, idDossierFrere );
170 daoUtil.setLong( 2, idAudience );
171
172 daoUtil.executeQuery( );
173
174 String title = null;
175
176 if ( daoUtil.next( ) )
177 {
178 title = daoUtil.getString( 1 );
179 }
180
181 daoUtil.free( );
182
183 return title;
184 }
185
186 @Override
187 public XmlDTO findByIdAndTypesAndAudience( String strId, List<String> availableTypes, Long lIdAudience )
188 {
189 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_FIND_FODLER_BY_ID_AND_TYPES_AND_AUDIENCE );
190
191 sbSQL.append( " " );
192 sbSQL.append( SQL_QUERY_AND_RESOURCE_TYPE );
193 sbSQL.append( "(" );
194
195 int count = 0;
196
197 for ( String type : availableTypes )
198 {
199 count++;
200
201 if ( count < availableTypes.size( ) )
202 {
203 sbSQL.append( "'" + type + "'," );
204 }
205 else
206 {
207 sbSQL.append( "'" + type + "'" );
208 }
209 }
210
211 sbSQL.append( ")" );
212
213 DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
214 daoUtil.setString( 1, strId );
215 daoUtil.setLong( 2, lIdAudience );
216
217 daoUtil.executeQuery( );
218
219 XmlDTO xml = null;
220
221 if ( daoUtil.next( ) )
222 {
223 xml = new XmlDTO( );
224
225 xml.setId( daoUtil.getLong( 1 ) );
226 xml.setIdXml( strId );
227 xml.setTitle( daoUtil.getString( 2 ) );
228
229 if ( daoUtil.getString( 3 ) != null )
230 {
231 xml.setBreadCrumb( daoUtil.getString( 3 ) );
232 }
233 else
234 {
235 xml.setBreadCrumb( "" );
236 }
237 }
238
239 daoUtil.free( );
240
241 return xml;
242 }
243
244 @Override
245 public String findResourceTypeByIdXMLAndAudience( String strIdXml, Long lIdAudience )
246 {
247 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_RESOURCE_TYPE_BY_IDXML_AND_AUDIENCE,
248 PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
249 daoUtil.setString( 1, strIdXml );
250 daoUtil.setLong( 2, lIdAudience );
251
252 daoUtil.executeQuery( );
253
254 String typeResource = null;
255
256 if ( daoUtil.next( ) )
257 {
258 typeResource = daoUtil.getString( 1 );
259 }
260
261 daoUtil.free( );
262
263 return typeResource;
264 }
265
266 @Override
267 public List<XmlDTO> findAll( )
268 {
269 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_ALL, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
270
271 daoUtil.executeQuery( );
272
273 List<XmlDTO> result = new ArrayList<XmlDTO>( );
274
275 while ( daoUtil.next( ) )
276 {
277 XmlDTO xml = new XmlDTO( );
278 xml.setId( daoUtil.getLong( 1 ) );
279 xml.setIdXml( daoUtil.getString( 2 ) );
280 xml.setTitle( daoUtil.getString( 3 ) );
281 xml.setBreadCrumb( daoUtil.getString( 4 ) );
282 xml.setIdAudience( daoUtil.getLong( 5 ) );
283 xml.setResourceType( daoUtil.getString( 6 ) );
284 xml.setCreationDate( daoUtil.getDate( 7 ) );
285 xml.setModificationDate( daoUtil.getDate( 8 ) );
286
287 result.add( xml );
288 }
289
290 daoUtil.free( );
291
292 return result;
293 }
294
295 @Override
296 public List<XmlDTO> findHowToByAudience( Long audienceId )
297 {
298 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_HOW_TO_BY_AUDIENCE,
299 PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
300 daoUtil.setLong( 1, audienceId );
301 daoUtil.setString( 2, ResourceTypeEnum.HOW_TO.getLabel( ) );
302
303 List<XmlDTO> result = new ArrayList<XmlDTO>( );
304
305 daoUtil.executeQuery( );
306
307 while ( daoUtil.next( ) )
308 {
309 XmlDTO dto = new XmlDTO( );
310
311 dto.setIdXml( daoUtil.getString( 1 ) );
312 dto.setTitle( daoUtil.getString( 2 ) );
313
314 result.add( dto );
315 }
316
317 daoUtil.free( );
318
319 return result;
320 }
321
322 @Override
323 public Long findIdByXmlAndAudience( String xmlName, Long audienceId )
324 {
325 Long id = null;
326 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_ID_BY_XML_AND_AUDIENCE,
327 PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
328 daoUtil.setString( 1, xmlName );
329 daoUtil.setLong( 2, audienceId );
330 daoUtil.executeQuery( );
331
332 if ( daoUtil.next( ) )
333 {
334 id = daoUtil.getLong( 1 );
335 }
336
337 daoUtil.free( );
338
339 return id;
340 }
341
342 @Override
343 public XmlDTO findHomeHowTo( Long audienceId )
344 {
345 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_HOW_TO_BY_AUDIENCE,
346 PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
347 daoUtil.setLong( 1, audienceId );
348 daoUtil.setString( 2, ResourceTypeEnum.HOW_TO_HOME.getLabel( ) );
349
350 daoUtil.executeQuery( );
351
352 XmlDTO homeHowTo = null;
353
354 if ( daoUtil.next( ) )
355 {
356 homeHowTo = new XmlDTO( );
357 homeHowTo.setIdXml( daoUtil.getString( 1 ) );
358 homeHowTo.setTitle( daoUtil.getString( 2 ) );
359 }
360
361 daoUtil.free( );
362
363 return homeHowTo;
364 }
365
366 @Override
367 public void create( XmlDTO dilaXml )
368 {
369 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
370
371 dilaXml.setId( newPrimaryKey( ) );
372
373 java.util.Date current = new java.util.Date( );
374
375 daoUtil.setLong( 1, dilaXml.getId( ) );
376 daoUtil.setString( 2, dilaXml.getIdXml( ) );
377 daoUtil.setString( 3, dilaXml.getTitle( ) );
378 daoUtil.setString( 4, dilaXml.getResourceType( ) );
379 daoUtil.setString( 5, dilaXml.getBreadCrumb( ) );
380 daoUtil.setLong( 6, dilaXml.getIdAudience( ) );
381 daoUtil.setDate( 7, new Date( current.getTime( ) ) );
382 daoUtil.setDate( 8, new Date( current.getTime( ) ) );
383
384 daoUtil.executeUpdate( );
385 daoUtil.free( );
386 }
387
388 @Override
389 public void store( XmlDTO dilaXml )
390 {
391 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
392
393 java.util.Date current = new java.util.Date( );
394
395 daoUtil.setString( 1, dilaXml.getTitle( ) );
396 daoUtil.setString( 2, dilaXml.getResourceType( ) );
397 daoUtil.setString( 3, dilaXml.getBreadCrumb( ) );
398 daoUtil.setDate( 4, new Date( current.getTime( ) ) );
399 daoUtil.setString( 5, dilaXml.getIdXml( ) );
400 daoUtil.setLong( 6, dilaXml.getIdAudience( ) );
401
402 daoUtil.executeUpdate( );
403 daoUtil.free( );
404 }
405
406 @Override
407 public void delete( )
408 {
409 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
410
411 java.util.Date current = new java.util.Date( );
412 daoUtil.setDate( 1, new Date( current.getTime( ) ) );
413
414 daoUtil.executeUpdate( );
415 daoUtil.free( );
416 }
417 }