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.fichelocale.dao.ILocalDAO;
37 import fr.paris.lutece.plugins.dila.business.fichelocale.dto.LocalDTO;
38 import fr.paris.lutece.plugins.dila.business.fichelocale.dto.LocalTypeDTO;
39 import fr.paris.lutece.plugins.dila.service.DilaLocalTypeEnum;
40 import fr.paris.lutece.plugins.dila.service.DilaPlugin;
41 import fr.paris.lutece.plugins.dila.utils.DilaUtils;
42 import fr.paris.lutece.portal.service.plugin.PluginService;
43 import fr.paris.lutece.util.sql.DAOUtil;
44
45 import java.io.Serializable;
46 import java.util.ArrayList;
47 import java.util.List;
48
49
50
51
52
53 public class LocalDAO implements ILocalDAO, Serializable
54 {
55
56 private static final long serialVersionUID = -6005693978102619298L;
57 private static final String SQL_QUERY_NEW_PK = "SELECT max(id) FROM dila_local";
58 private static final String SQL_QUERY_FIND_LOCAL_BY_ID_AND_AUDIENCE_AND_TYPE = "SELECT title , author, path, type_id FROM dila_local "
59 + "WHERE id = ? AND audience_id = ? AND type_id = ?";
60 private static final String SQL_QUERY_FIND_BY_ID_AND_TYPE_AND_AUDIENCE = "SELECT title FROM dila_local "
61 + "WHERE id = ? AND type_id = ? AND audience_id = ?";
62 private static final String SQL_QUERY_SELECT_ALL = " SELECT id, title, author, path, xml, audience_id, type_id, date_creation "
63 + "FROM dila_local ORDER by id";
64 private static final String SQL_QUERY_SELECT_ALL_BY_AUDIENCE_ID = " SELECT id, title, author, path, xml, audience_id, type_id, date_creation "
65 + "FROM dila_local WHERE audience_id = ? ORDER by id";
66 private static final String SQL_QUERY_INSERT = " INSERT INTO dila_local ( id , title , author, path, xml, audience_id, type_id ) "
67 + " VALUES ( ?, ? ,?, ?, ?, ?, ? )";
68 private static final String SQL_QUERY_DELETE = " DELETE FROM dila_local WHERE id = ?";
69 private static final String SQL_QUERY_UPDATE = " UPDATE dila_local "
70 + "SET title = ?, author = ?, path = ?, xml = ?, audience_id = ? WHERE id = ?";
71 private static final String SQL_QUERY_FIND_XML_BY_ID = "SELECT xml FROM dila_local WHERE id = ?";
72 private static final String SQL_QUERY_FIND_LAST_CARDS_BY_AUDIENCE = "SELECT id, title FROM dila_local "
73 + "WHERE audience_id = ? AND type_id = '1' ORDER BY date_creation DESC";
74
75
76
77
78
79 private Long newPrimaryKey( )
80 {
81 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
82 daoUtil.executeQuery( );
83
84 Long nKey = 1L;
85
86 if ( daoUtil.next( ) )
87 {
88 nKey = daoUtil.getLong( 1 ) + 1L;
89 }
90
91 daoUtil.free( );
92
93 return nKey;
94 }
95
96 @Override
97 public LocalDTO findLocalByIdAndTypeAndAudience( Long id, Long type, Long idAudience )
98 {
99 StringBuilder sbQuery = new StringBuilder( SQL_QUERY_FIND_LOCAL_BY_ID_AND_AUDIENCE_AND_TYPE );
100
101 DAOUtil daoUtil = new DAOUtil( sbQuery.toString( ), PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
102 daoUtil.setLong( 1, id );
103 daoUtil.setLong( 2, idAudience );
104 daoUtil.setLong( 3, type );
105
106 daoUtil.executeQuery( );
107
108 LocalDTO local = null;
109
110 if ( daoUtil.next( ) )
111 {
112 local = new LocalDTO( );
113 local.setId( id );
114 local.setTitle( daoUtil.getString( 1 ) );
115 local.setAuthor( daoUtil.getString( 2 ) );
116 local.setBreadCrumb( daoUtil.getString( 3 ) );
117
118 DilaLocalTypeEnum typeEnum = DilaLocalTypeEnum.fromId( daoUtil.getLong( 4 ) );
119
120 LocalTypeDTO localType = new LocalTypeDTO( );
121 localType.setId( typeEnum.getId( ) );
122 localType.setLabel( typeEnum.getLabel( ) );
123 local.setType( localType );
124 }
125
126 daoUtil.free( );
127
128 return local;
129 }
130
131 @Override
132 public String findTitleByIdAndTypeAndAudience( Long id, Long type, Long idAudience )
133 {
134 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_ID_AND_TYPE_AND_AUDIENCE,
135 PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
136 daoUtil.setLong( 1, id );
137 daoUtil.setLong( 2, type );
138 daoUtil.setLong( 3, idAudience );
139
140 daoUtil.executeQuery( );
141
142 String title = null;
143
144 if ( daoUtil.next( ) )
145 {
146 title = daoUtil.getString( 1 );
147 }
148
149 daoUtil.free( );
150
151 return title;
152 }
153
154 @Override
155 public List<LocalDTO> findAll( )
156 {
157 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
158 daoUtil.executeQuery( );
159
160 List<LocalDTO> results = new ArrayList<LocalDTO>( );
161
162 while ( daoUtil.next( ) )
163 {
164 LocalDTO local = new LocalDTO( );
165
166 local.setId( daoUtil.getLong( 1 ) );
167 local.setTitle( daoUtil.getString( 2 ) );
168 local.setAuthor( daoUtil.getString( 3 ) );
169 local.setBreadCrumb( daoUtil.getString( 4 ) );
170 local.setXml( daoUtil.getString( 5 ) );
171 local.setIdAudience( daoUtil.getLong( 6 ) );
172 local.setDisplayPath( DilaUtils.convertBreadcrumbIntoDisplay( local.getBreadCrumb( ), local.getIdAudience( ) ) );
173
174 DilaLocalTypeEnum typeEnum = DilaLocalTypeEnum.fromId( daoUtil.getLong( 7 ) );
175
176 LocalTypeDTO type = new LocalTypeDTO( );
177 type.setId( typeEnum.getId( ) );
178 type.setLabel( typeEnum.getLabel( ) );
179 local.setType( type );
180
181 local.setCreationDate( daoUtil.getDate( 8 ) );
182
183 results.add( local );
184 }
185
186 daoUtil.free( );
187
188 return results;
189 }
190
191 @Override
192 public Long insert( LocalDTO local, boolean addIdToBreadcrumb )
193 {
194 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
195
196 local.setId( newPrimaryKey( ) );
197
198 daoUtil.setLong( 1, local.getId( ) );
199 daoUtil.setString( 2, local.getTitle( ) );
200 daoUtil.setString( 3, local.getAuthor( ) );
201
202 if ( addIdToBreadcrumb )
203 {
204 daoUtil.setString( 4, local.getBreadCrumb( ) + ";" + local.getId( ) );
205 }
206 else
207 {
208 daoUtil.setString( 4, local.getBreadCrumb( ) );
209 }
210
211 daoUtil.setString(
212 5,
213 local.getXml( ).replaceAll( "<%ID%>", "" + local.getId( ) )
214 .replaceAll( "<%NIVEAU_ID%>", "" + local.getId( ) ) );
215 daoUtil.setLong( 6, local.getIdAudience( ) );
216 daoUtil.setLong( 7, local.getType( ).getId( ) );
217
218 daoUtil.executeUpdate( );
219 daoUtil.free( );
220
221 return local.getId( );
222 }
223
224 @Override
225 public void delete( String idLocal )
226 {
227 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
228
229 daoUtil.setString( 1, idLocal );
230
231 daoUtil.executeUpdate( );
232 daoUtil.free( );
233 }
234
235 @Override
236 public void store( LocalDTO localDTO, boolean addIdToBreadcrumb )
237 {
238 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
239
240 daoUtil.setString( 1, localDTO.getTitle( ) );
241 daoUtil.setString( 2, localDTO.getAuthor( ) );
242
243 if ( addIdToBreadcrumb )
244 {
245 daoUtil.setString( 3, localDTO.getBreadCrumb( ) + ";" + localDTO.getId( ) );
246 }
247 else
248 {
249 daoUtil.setString( 3, localDTO.getBreadCrumb( ) );
250 }
251
252 daoUtil.setString(
253 4,
254 localDTO.getXml( ).replaceAll( "<%ID%>", "" + localDTO.getId( ) )
255 .replaceAll( "<%NIVEAU_ID%>", "" + localDTO.getId( ) ) );
256 daoUtil.setLong( 5, localDTO.getIdAudience( ) );
257 daoUtil.setLong( 6, localDTO.getId( ) );
258
259 daoUtil.executeUpdate( );
260 daoUtil.free( );
261 }
262
263 @Override
264 public String findXmlById( Long id )
265 {
266 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_XML_BY_ID, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
267 daoUtil.setLong( 1, id );
268
269 daoUtil.executeQuery( );
270
271 String xml = null;
272
273 if ( daoUtil.next( ) )
274 {
275 xml = daoUtil.getString( 1 );
276 }
277
278 daoUtil.free( );
279
280 return xml;
281 }
282
283 @Override
284 public List<LocalDTO> findLastCardsByAudience( Long audienceId )
285 {
286 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_LAST_CARDS_BY_AUDIENCE,
287 PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
288 daoUtil.setLong( 1, audienceId );
289
290 List<LocalDTO> result = new ArrayList<LocalDTO>( );
291
292 daoUtil.executeQuery( );
293
294 while ( daoUtil.next( ) )
295 {
296 LocalDTO dto = new LocalDTO( );
297
298 dto.setId( daoUtil.getLong( 1 ) );
299 dto.setTitle( daoUtil.getString( 2 ) );
300
301 result.add( dto );
302 }
303
304 daoUtil.free( );
305
306 return result;
307 }
308
309 @Override
310 public List<LocalDTO> findAllByAudienceId( Long audienceId )
311 {
312 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_BY_AUDIENCE_ID,
313 PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
314 daoUtil.setLong( 1, audienceId );
315 daoUtil.executeQuery( );
316
317 List<LocalDTO> results = new ArrayList<LocalDTO>( );
318
319 while ( daoUtil.next( ) )
320 {
321 LocalDTO local = new LocalDTO( );
322
323 local.setId( daoUtil.getLong( 1 ) );
324 local.setTitle( daoUtil.getString( 2 ) );
325 local.setAuthor( daoUtil.getString( 3 ) );
326 local.setBreadCrumb( daoUtil.getString( 4 ) );
327 local.setXml( daoUtil.getString( 5 ) );
328 local.setIdAudience( daoUtil.getLong( 6 ) );
329 local.setDisplayPath( DilaUtils.convertBreadcrumbIntoDisplay( local.getBreadCrumb( ), local.getIdAudience( ) ) );
330
331 DilaLocalTypeEnum typeEnum = DilaLocalTypeEnum.fromId( daoUtil.getLong( 7 ) );
332
333 LocalTypeDTO type = new LocalTypeDTO( );
334 type.setId( typeEnum.getId( ) );
335 type.setLabel( typeEnum.getLabel( ) );
336 local.setType( type );
337
338 local.setCreationDate( daoUtil.getDate( 8 ) );
339
340 results.add( local );
341 }
342
343 daoUtil.free( );
344
345 return results;
346 }
347 }