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 int nKey;
82 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK ) )
83 {
84 daoUtil.executeQuery( );
85
86 if ( !daoUtil.next( ) )
87 {
88
89 nKey = 1;
90 }
91
92 nKey = daoUtil.getInt( 1 ) + 1;
93 }
94 return nKey;
95 }
96
97
98
99
100
101
102
103 public synchronized void insert( DocumentAttribute documentAttribute )
104 {
105 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT ) )
106 {
107 documentAttribute.setId( newPrimaryKey( ) );
108 daoUtil.setInt( 1, documentAttribute.getId( ) );
109 daoUtil.setString( 2, documentAttribute.getCodeDocumentType( ) );
110 daoUtil.setString( 3, documentAttribute.getCodeAttributeType( ) );
111 daoUtil.setString( 4, documentAttribute.getCode( ) );
112 daoUtil.setString( 5, documentAttribute.getName( ) );
113 daoUtil.setString( 6, documentAttribute.getDescription( ) );
114 daoUtil.setInt( 7, documentAttribute.getAttributeOrder( ) );
115 daoUtil.setInt( 8, documentAttribute.isRequired( ) ? 1 : 0 );
116 daoUtil.setInt( 9, documentAttribute.isSearchable( ) ? 1 : 0 );
117
118 daoUtil.executeUpdate( );
119 }
120
121 insertAttributeParameters( documentAttribute );
122 }
123
124
125
126
127
128
129
130
131 public DocumentAttribute load( int nAttributeId )
132 {
133 DocumentAttribute documentAttribute = null;
134 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ATTRIBUTE ) )
135 {
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 return documentAttribute;
154 }
155
156
157
158
159
160
161
162 public void delete( int nAttributeId )
163 {
164 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
165 {
166 daoUtil.setInt( 1, nAttributeId );
167
168 daoUtil.executeUpdate( );
169 }
170 deleteParameters( nAttributeId );
171 deleteRegularExpressions( nAttributeId );
172 }
173
174
175
176
177
178
179
180 private void deleteParameters( int nAttributeId )
181 {
182 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_PARAMETERS_VALUES ) )
183 {
184 daoUtil.setInt( 1, nAttributeId );
185
186 daoUtil.executeUpdate( );
187 }
188 }
189
190
191
192
193
194
195
196
197
198 private void deleteParameter( int nAttributeId, String strParameterName )
199 {
200 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_PARAMETER_VALUES ) )
201 {
202 daoUtil.setInt( 1, nAttributeId );
203 daoUtil.setString( 2, strParameterName );
204
205 daoUtil.executeUpdate( );
206 }
207 }
208
209
210
211
212
213
214
215 public void store( DocumentAttribute documentAttribute )
216 {
217 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE ) )
218 {
219 daoUtil.setInt( 1, documentAttribute.getId( ) );
220 daoUtil.setString( 2, documentAttribute.getCodeDocumentType( ) );
221 daoUtil.setString( 3, documentAttribute.getCodeAttributeType( ) );
222 daoUtil.setString( 4, documentAttribute.getCode( ) );
223 daoUtil.setString( 5, documentAttribute.getName( ) );
224 daoUtil.setString( 6, documentAttribute.getDescription( ) );
225 daoUtil.setInt( 7, documentAttribute.getAttributeOrder( ) );
226 daoUtil.setInt( 8, documentAttribute.isRequired( ) ? 1 : 0 );
227 daoUtil.setInt( 9, documentAttribute.isSearchable( ) ? 1 : 0 );
228 daoUtil.setInt( 10, documentAttribute.getId( ) );
229
230 daoUtil.executeUpdate( );
231 }
232
233 deleteParameters( documentAttribute.getId( ) );
234 insertAttributeParameters( documentAttribute );
235 }
236
237
238
239
240
241
242
243 public void selectAttributesByDocumentType( DocumentType documentType )
244 {
245 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ATTRIBUTES ) )
246 {
247 daoUtil.setString( 1, documentType.getCode( ) );
248 daoUtil.executeQuery( );
249
250 int nOrder = 1;
251
252 while ( daoUtil.next( ) )
253 {
254 DocumentAttribute/attributes/DocumentAttribute.html#DocumentAttribute">DocumentAttribute documentAttribute = new DocumentAttribute( );
255 documentAttribute.setId( daoUtil.getInt( 1 ) );
256 documentAttribute.setCodeDocumentType( daoUtil.getString( 2 ) );
257 documentAttribute.setCodeAttributeType( daoUtil.getString( 3 ) );
258 documentAttribute.setCode( daoUtil.getString( 4 ) );
259 documentAttribute.setName( daoUtil.getString( 5 ) );
260 documentAttribute.setDescription( daoUtil.getString( 6 ) );
261 documentAttribute.setAttributeOrder( nOrder );
262 documentAttribute.setRequired( daoUtil.getInt( 8 ) != 0 );
263 documentAttribute.setSearchable( daoUtil.getInt( 9 ) != 0 );
264
265 documentType.addAttribute( documentAttribute );
266 nOrder++;
267 }
268 }
269 }
270
271
272
273
274
275
276
277
278 public List<DocumentAttribute> selectAllAttributesOfDocumentType( String codeDocumentType )
279 {
280 List<DocumentAttribute> listDocumentAttributes = new ArrayList<>( );
281 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ATTRIBUTES_OF_DOCUMENT_TYPE ) )
282 {
283 daoUtil.setString( 1, codeDocumentType );
284 daoUtil.executeQuery( );
285
286 while ( daoUtil.next( ) )
287 {
288 DocumentAttribute/attributes/DocumentAttribute.html#DocumentAttribute">DocumentAttribute documentAttribute = new DocumentAttribute( );
289 documentAttribute.setId( daoUtil.getInt( 1 ) );
290 documentAttribute.setCodeDocumentType( daoUtil.getString( 2 ) );
291 documentAttribute.setCodeAttributeType( daoUtil.getString( 3 ) );
292 documentAttribute.setCode( daoUtil.getString( 4 ) );
293 documentAttribute.setName( daoUtil.getString( 5 ) );
294 documentAttribute.setDescription( daoUtil.getString( 6 ) );
295 documentAttribute.setAttributeOrder( daoUtil.getInt( 7 ) );
296 documentAttribute.setRequired( daoUtil.getInt( 8 ) != 0 );
297 documentAttribute.setSearchable( daoUtil.getInt( 9 ) != 0 );
298 listDocumentAttributes.add( documentAttribute );
299 }
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 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_PARAMETER_VALUES ) )
316 {
317 daoUtil.setInt( 1, documentAttribute.getId( ) );
318 daoUtil.setString( 2, parameter.getName( ) );
319 daoUtil.setInt( 3, i++ );
320 daoUtil.setString( 4, value );
321
322 daoUtil.executeUpdate( );
323 }
324 }
325 }
326 }
327
328
329
330
331
332
333
334
335 public List<AttributeTypeParameter> selectAttributeParametersValues( int nAttributeId )
336 {
337 ArrayList<AttributeTypeParameter> listParameters = new ArrayList<>( );
338 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PARAMETERS ) )
339 {
340 daoUtil.setInt( 1, nAttributeId );
341 daoUtil.executeQuery( );
342
343 while ( daoUtil.next( ) )
344 {
345 AttributeTypeParameteress/attributes/AttributeTypeParameter.html#AttributeTypeParameter">AttributeTypeParameter parameter = new AttributeTypeParameter( );
346 parameter.setName( daoUtil.getString( 1 ) );
347 parameter.setValueList( getAttributeParameterValues( nAttributeId, parameter.getName( ) ) );
348 listParameters.add( parameter );
349 }
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<>( );
366 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PARAMETER_VALUES ) )
367 {
368 daoUtil.setInt( 1, nAttributeId );
369 daoUtil.setString( 2, strParameterName );
370 daoUtil.executeQuery( );
371
372 while ( daoUtil.next( ) )
373 {
374 listValues.add( daoUtil.getString( 1 ) );
375 }
376 }
377 return listValues;
378 }
379
380
381
382
383
384
385
386
387
388 public void insertRegularExpression( int nIdAttribute, int nIdExpression )
389 {
390 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_REGULAR_EXPRESSION ) )
391 {
392 daoUtil.setInt( 1, nIdAttribute );
393 daoUtil.setInt( 2, nIdExpression );
394 daoUtil.executeUpdate( );
395 }
396 }
397
398
399
400
401
402
403
404
405
406 public void deleteRegularExpression( int nIdAttribute, int nIdExpression )
407 {
408 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_REGULAR_EXPRESSION ) )
409 {
410 daoUtil.setInt( 1, nIdAttribute );
411 daoUtil.setInt( 2, nIdExpression );
412 daoUtil.executeUpdate( );
413 }
414 }
415
416
417
418
419
420
421
422 private void deleteRegularExpressions( int nIdAttribute )
423 {
424 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_REGULAR_EXPRESSIONS ) )
425 {
426 daoUtil.setInt( 1, nIdAttribute );
427 daoUtil.executeUpdate( );
428 }
429 }
430
431
432
433
434
435
436
437
438 public Collection<Integer> selectListRegularExpressionKeyByIdAttribute( int nIdAttribute )
439 {
440 Collection<Integer> colRegularExpression = new ArrayList<>( );
441
442 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_REGULAR_EXPRESSION_BY_ID_ATTRIBUTE ) )
443 {
444 daoUtil.setInt( 1, nIdAttribute );
445 daoUtil.executeQuery( );
446
447 while ( daoUtil.next( ) )
448 {
449 colRegularExpression.add( daoUtil.getInt( 1 ) );
450 }
451 }
452 return colRegularExpression;
453 }
454 }