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