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.document.business.attributes;
35
36 import fr.paris.lutece.plugins.document.business.DocumentType;
37 import fr.paris.lutece.util.sql.DAOUtil;
38
39 import java.util.ArrayList;
40 import java.util.Collection;
41 import java.util.List;
42
43
44
45
46 public final class DocumentAttributeDAO implements IDocumentAttributeDAO
47 {
48
49 private static final String SQL_QUERY_NEW_PK = " SELECT max( id_document_attr ) FROM document_type_attr ";
50 private static final String SQL_QUERY_INSERT = " INSERT INTO document_type_attr ( id_document_attr, code_document_type, code_attr_type, code, document_type_attr_name, description, attr_order, required, searchable ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
51 private static final String SQL_QUERY_DELETE = " DELETE FROM document_type_attr WHERE id_document_attr = ? ";
52 private static final String SQL_QUERY_UPDATE = " UPDATE document_type_attr SET id_document_attr = ?, code_document_type = ?, code_attr_type = ?, code = ?, document_type_attr_name = ?, description = ?, attr_order = ?, required = ?, searchable = ? WHERE id_document_attr = ? ";
53 private static final String SQL_QUERY_SELECTALL_ATTRIBUTES = " SELECT a.id_document_attr, a.code_document_type," + " a.code_attr_type, a.code, "
54 + " a.document_type_attr_name, a.description, a.attr_order, a.required, a.searchable " + " FROM document_type_attr a, document_attr_type b"
55 + " WHERE a.code_attr_type = b.code_attr_type" + " AND a.code_document_type = ? ORDER BY a.attr_order";
56 private static final String SQL_QUERY_SELECT_ATTRIBUTE = " SELECT a.id_document_attr, a.code_document_type," + " a.code_attr_type, a.code, "
57 + " a.document_type_attr_name, a.description, a.attr_order, a.required, a.searchable " + " FROM document_type_attr a, document_attr_type b"
58 + " WHERE a.code_attr_type = b.code_attr_type" + " AND a.id_document_attr = ? ";
59 private static final String SQL_QUERY_SELECTALL_ATTRIBUTES_OF_DOCUMENT_TYPE = " SELECT DISTINCT a.id_document_attr, "
60 + " a.code_document_type, a.code_attr_type, a.code, " + " a.document_type_attr_name, a.description, a.attr_order, a.required, a.searchable "
61 + " FROM document_type_attr a" + " WHERE a.code_document_type = ?" + " ORDER BY a.attr_order";
62 private static final String SQL_QUERY_INSERT_PARAMETER_VALUES = "INSERT INTO document_type_attr_parameters ( id_document_attr, parameter_name, id_list_parameter, parameter_value )"
63 + "VALUES ( ?, ?, ?, ? ) ";
64 private static final String SQL_QUERY_SELECT_PARAMETERS = "SELECT DISTINCT parameter_name FROM document_type_attr_parameters WHERE id_document_attr = ? ";
65 private static final String SQL_QUERY_SELECT_PARAMETER_VALUES = "SELECT parameter_value FROM document_type_attr_parameters "
66 + "WHERE id_document_attr = ? AND parameter_name = ? ";
67 private static final String SQL_QUERY_DELETE_PARAMETER_VALUES = "DELETE FROM document_type_attr_parameters WHERE id_document_attr = ? AND parameter_name = ? ";
68 private static final String SQL_QUERY_DELETE_PARAMETERS_VALUES = "DELETE FROM document_type_attr_parameters WHERE id_document_attr = ? ";
69 private static final String SQL_QUERY_INSERT_REGULAR_EXPRESSION = "INSERT INTO document_type_attr_verify_by(id_document_attr,id_expression) VALUES(?,?)";
70 private static final String SQL_QUERY_DELETE_REGULAR_EXPRESSION = "DELETE FROM document_type_attr_verify_by WHERE id_document_attr=? AND id_expression=?";
71 private static final String SQL_QUERY_DELETE_REGULAR_EXPRESSIONS = "DELETE FROM document_type_attr_verify_by WHERE id_document_attr=?";
72 private static final String SQL_QUERY_SELECT_REGULAR_EXPRESSION_BY_ID_ATTRIBUTE = "SELECT id_expression FROM document_type_attr_verify_by WHERE id_document_attr=?";
73
74
75
76
77
78
79 private int newPrimaryKey( )
80 {
81 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK );
82 daoUtil.executeQuery( );
83
84 int nKey;
85
86 if ( !daoUtil.next( ) )
87 {
88
89 nKey = 1;
90 }
91
92 nKey = daoUtil.getInt( 1 ) + 1;
93
94 daoUtil.free( );
95
96 return nKey;
97 }
98
99
100
101
102
103
104
105 public synchronized void insert( DocumentAttribute documentAttribute )
106 {
107 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT );
108 documentAttribute.setId( newPrimaryKey( ) );
109 daoUtil.setInt( 1, documentAttribute.getId( ) );
110 daoUtil.setString( 2, documentAttribute.getCodeDocumentType( ) );
111 daoUtil.setString( 3, documentAttribute.getCodeAttributeType( ) );
112 daoUtil.setString( 4, documentAttribute.getCode( ) );
113 daoUtil.setString( 5, documentAttribute.getName( ) );
114 daoUtil.setString( 6, documentAttribute.getDescription( ) );
115 daoUtil.setInt( 7, documentAttribute.getAttributeOrder( ) );
116 daoUtil.setInt( 8, documentAttribute.isRequired( ) ? 1 : 0 );
117 daoUtil.setInt( 9, documentAttribute.isSearchable( ) ? 1 : 0 );
118
119 daoUtil.executeUpdate( );
120 daoUtil.free( );
121
122 insertAttributeParameters( documentAttribute );
123 }
124
125
126
127
128
129
130
131
132 public DocumentAttribute load( int nAttributeId )
133 {
134 DocumentAttribute documentAttribute = null;
135 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ATTRIBUTE );
136 daoUtil.setInt( 1, nAttributeId );
137 daoUtil.executeQuery( );
138
139 if ( daoUtil.next( ) )
140 {
141 documentAttribute = new DocumentAttribute( );
142 documentAttribute.setId( daoUtil.getInt( 1 ) );
143 documentAttribute.setCodeDocumentType( daoUtil.getString( 2 ) );
144 documentAttribute.setCodeAttributeType( daoUtil.getString( 3 ) );
145 documentAttribute.setCode( daoUtil.getString( 4 ) );
146 documentAttribute.setName( daoUtil.getString( 5 ) );
147 documentAttribute.setDescription( daoUtil.getString( 6 ) );
148 documentAttribute.setAttributeOrder( daoUtil.getInt( 7 ) );
149 documentAttribute.setRequired( daoUtil.getInt( 8 ) != 0 );
150 documentAttribute.setSearchable( daoUtil.getInt( 9 ) != 0 );
151 }
152
153 daoUtil.free( );
154
155 return documentAttribute;
156 }
157
158
159
160
161
162
163
164 public void delete( int nAttributeId )
165 {
166 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE );
167 daoUtil.setInt( 1, nAttributeId );
168
169 daoUtil.executeUpdate( );
170 daoUtil.free( );
171 deleteParameters( nAttributeId );
172 deleteRegularExpressions( nAttributeId );
173 }
174
175
176
177
178
179
180
181 private void deleteParameters( int nAttributeId )
182 {
183 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_PARAMETERS_VALUES );
184 daoUtil.setInt( 1, nAttributeId );
185
186 daoUtil.executeUpdate( );
187 daoUtil.free( );
188 }
189
190
191
192
193
194
195
196
197
198 private void deleteParameter( int nAttributeId, String strParameterName )
199 {
200 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_PARAMETER_VALUES );
201 daoUtil.setInt( 1, nAttributeId );
202 daoUtil.setString( 2, strParameterName );
203
204 daoUtil.executeUpdate( );
205 daoUtil.free( );
206 }
207
208
209
210
211
212
213
214 public void store( DocumentAttribute documentAttribute )
215 {
216 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE );
217 daoUtil.setInt( 1, documentAttribute.getId( ) );
218 daoUtil.setString( 2, documentAttribute.getCodeDocumentType( ) );
219 daoUtil.setString( 3, documentAttribute.getCodeAttributeType( ) );
220 daoUtil.setString( 4, documentAttribute.getCode( ) );
221 daoUtil.setString( 5, documentAttribute.getName( ) );
222 daoUtil.setString( 6, documentAttribute.getDescription( ) );
223 daoUtil.setInt( 7, documentAttribute.getAttributeOrder( ) );
224 daoUtil.setInt( 8, documentAttribute.isRequired( ) ? 1 : 0 );
225 daoUtil.setInt( 9, documentAttribute.isSearchable( ) ? 1 : 0 );
226 daoUtil.setInt( 10, documentAttribute.getId( ) );
227
228 daoUtil.executeUpdate( );
229 daoUtil.free( );
230
231
232 deleteParameters( documentAttribute.getId( ) );
233 insertAttributeParameters( documentAttribute );
234 }
235
236
237
238
239
240
241
242 public void selectAttributesByDocumentType( DocumentType documentType )
243 {
244 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ATTRIBUTES );
245 daoUtil.setString( 1, documentType.getCode( ) );
246 daoUtil.executeQuery( );
247
248 int nOrder = 1;
249
250 while ( daoUtil.next( ) )
251 {
252 DocumentAttribute/attributes/DocumentAttribute.html#DocumentAttribute">DocumentAttribute documentAttribute = new DocumentAttribute( );
253 documentAttribute.setId( daoUtil.getInt( 1 ) );
254 documentAttribute.setCodeDocumentType( daoUtil.getString( 2 ) );
255 documentAttribute.setCodeAttributeType( daoUtil.getString( 3 ) );
256 documentAttribute.setCode( daoUtil.getString( 4 ) );
257 documentAttribute.setName( daoUtil.getString( 5 ) );
258 documentAttribute.setDescription( daoUtil.getString( 6 ) );
259 documentAttribute.setAttributeOrder( nOrder );
260 documentAttribute.setRequired( daoUtil.getInt( 8 ) != 0 );
261 documentAttribute.setSearchable( daoUtil.getInt( 9 ) != 0 );
262
263 documentType.addAttribute( documentAttribute );
264 nOrder++;
265 }
266
267 daoUtil.free( );
268 }
269
270
271
272
273
274
275
276
277 public List<DocumentAttribute> selectAllAttributesOfDocumentType( String codeDocumentType )
278 {
279 List<DocumentAttribute> listDocumentAttributes = new ArrayList<DocumentAttribute>( );
280 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ATTRIBUTES_OF_DOCUMENT_TYPE );
281 daoUtil.setString( 1, codeDocumentType );
282 daoUtil.executeQuery( );
283
284 while ( daoUtil.next( ) )
285 {
286 DocumentAttribute/attributes/DocumentAttribute.html#DocumentAttribute">DocumentAttribute documentAttribute = new DocumentAttribute( );
287 documentAttribute.setId( daoUtil.getInt( 1 ) );
288 documentAttribute.setCodeDocumentType( daoUtil.getString( 2 ) );
289 documentAttribute.setCodeAttributeType( daoUtil.getString( 3 ) );
290 documentAttribute.setCode( daoUtil.getString( 4 ) );
291 documentAttribute.setName( daoUtil.getString( 5 ) );
292 documentAttribute.setDescription( daoUtil.getString( 6 ) );
293 documentAttribute.setAttributeOrder( daoUtil.getInt( 7 ) );
294 documentAttribute.setRequired( daoUtil.getInt( 8 ) != 0 );
295 documentAttribute.setSearchable( daoUtil.getInt( 9 ) != 0 );
296 listDocumentAttributes.add( documentAttribute );
297 }
298
299 daoUtil.free( );
300
301 return listDocumentAttributes;
302 }
303
304
305 private void insertAttributeParameters( DocumentAttribute documentAttribute )
306 {
307 for ( AttributeTypeParameter parameter : documentAttribute.getParameters( ) )
308 {
309 deleteParameter( documentAttribute.getId( ), parameter.getName( ) );
310
311 int i = 0;
312
313 for ( String value : parameter.getValueList( ) )
314 {
315 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_PARAMETER_VALUES );
316 daoUtil.setInt( 1, documentAttribute.getId( ) );
317 daoUtil.setString( 2, parameter.getName( ) );
318 daoUtil.setInt( 3, i++ );
319 daoUtil.setString( 4, value );
320
321 daoUtil.executeUpdate( );
322 daoUtil.free( );
323 }
324 }
325 }
326
327
328
329
330
331
332
333
334 public List<AttributeTypeParameter> selectAttributeParametersValues( int nAttributeId )
335 {
336 ArrayList<AttributeTypeParameter> listParameters = new ArrayList<AttributeTypeParameter>( );
337 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PARAMETERS );
338 daoUtil.setInt( 1, nAttributeId );
339 daoUtil.executeQuery( );
340
341 while ( daoUtil.next( ) )
342 {
343 AttributeTypeParameteress/attributes/AttributeTypeParameter.html#AttributeTypeParameter">AttributeTypeParameter parameter = new AttributeTypeParameter( );
344 parameter.setName( daoUtil.getString( 1 ) );
345 parameter.setValueList( getAttributeParameterValues( nAttributeId, parameter.getName( ) ) );
346 listParameters.add( parameter );
347 }
348
349 daoUtil.free( );
350
351 return listParameters;
352 }
353
354
355
356
357
358
359
360
361
362
363 public List<String> getAttributeParameterValues( int nAttributeId, String strParameterName )
364 {
365 List<String> listValues = new ArrayList<String>( );
366 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PARAMETER_VALUES );
367 daoUtil.setInt( 1, nAttributeId );
368 daoUtil.setString( 2, strParameterName );
369 daoUtil.executeQuery( );
370
371 while ( daoUtil.next( ) )
372 {
373 listValues.add( daoUtil.getString( 1 ) );
374 }
375
376 daoUtil.free( );
377
378 return listValues;
379 }
380
381
382
383
384
385
386
387
388
389 public void insertRegularExpression( int nIdAttribute, int nIdExpression )
390 {
391 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_REGULAR_EXPRESSION );
392 daoUtil.setInt( 1, nIdAttribute );
393 daoUtil.setInt( 2, nIdExpression );
394 daoUtil.executeUpdate( );
395 daoUtil.free( );
396 }
397
398
399
400
401
402
403
404
405
406 public void deleteRegularExpression( int nIdAttribute, int nIdExpression )
407 {
408 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_REGULAR_EXPRESSION );
409 daoUtil.setInt( 1, nIdAttribute );
410 daoUtil.setInt( 2, nIdExpression );
411 daoUtil.executeUpdate( );
412 daoUtil.free( );
413 }
414
415
416
417
418
419
420
421 private void deleteRegularExpressions( int nIdAttribute )
422 {
423 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_REGULAR_EXPRESSIONS );
424 daoUtil.setInt( 1, nIdAttribute );
425 daoUtil.executeUpdate( );
426 daoUtil.free( );
427 }
428
429
430
431
432
433
434
435
436 public Collection<Integer> selectListRegularExpressionKeyByIdAttribute( int nIdAttribute )
437 {
438 Collection<Integer> colRegularExpression = new ArrayList<Integer>( );
439
440 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_REGULAR_EXPRESSION_BY_ID_ATTRIBUTE );
441 daoUtil.setInt( 1, nIdAttribute );
442 daoUtil.executeQuery( );
443
444 while ( daoUtil.next( ) )
445 {
446 colRegularExpression.add( daoUtil.getInt( 1 ) );
447 }
448
449 daoUtil.free( );
450
451 return colRegularExpression;
452 }
453 }