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.genericattributes.business;
35
36 import java.sql.Date;
37 import java.sql.Statement;
38 import java.util.ArrayList;
39 import java.util.List;
40 import java.util.stream.Collectors;
41
42 import org.apache.commons.collections.CollectionUtils;
43
44 import fr.paris.lutece.portal.business.file.File;
45 import fr.paris.lutece.portal.service.image.ImageResourceManager;
46 import fr.paris.lutece.portal.service.plugin.Plugin;
47 import fr.paris.lutece.util.sql.DAOUtil;
48
49
50
51
52 public final class FieldDAO implements IFieldDAO
53 {
54
55 private static final String SQL_QUERY_SELECT_ALL = "SELECT id_field,id_entry,code,title,value,default_value,pos,value_type_date,no_display_title,comment,id_file_key"
56 + " FROM genatt_field ";
57 private static final String SQL_QUERY_FIND_BY_PRIMARY_KEY = SQL_QUERY_SELECT_ALL + " WHERE id_field = ? ORDER BY pos";
58 private static final String SQL_QUERY_FIND_BY_CODE = SQL_QUERY_SELECT_ALL + " WHERE code = ? ORDER BY pos";
59 private static final String SQL_QUERY_INSERT = "INSERT INTO genatt_field(id_entry,code,title,value,default_value,pos,value_type_date,no_display_title,comment,id_file_key)"
60 + " VALUES(?,?,?,?,?,?,?,?,?,?)";
61 private static final String SQL_QUERY_DELETE = "DELETE FROM genatt_field WHERE id_field = ? ";
62 private static final String SQL_QUERY_INSERT_VERIF_BY = "INSERT INTO genatt_verify_by(id_field,id_expression) VALUES(?,?) ";
63 private static final String SQL_QUERY_DELETE_VERIF_BY = "DELETE FROM genatt_verify_by WHERE id_field = ? and id_expression= ?";
64 private static final String SQL_QUERY_UPDATE = "UPDATE genatt_field SET "
65 + "id_field=?,id_entry=?,code=?,title=?,value=?,default_value=?,pos=?,value_type_date=?,no_display_title=?,comment=?,id_file_key=? WHERE id_field = ?";
66 private static final String SQL_QUERY_SELECT_FIELD_BY_ID_ENTRY = SQL_QUERY_SELECT_ALL + " WHERE id_entry = ? ORDER BY pos";
67 private static final String SQL_QUERY_NEW_POSITION = "SELECT MAX(pos)" + " FROM genatt_field ";
68 private static final String SQL_QUERY_SELECT_REGULAR_EXPRESSION_BY_ID_FIELD = "SELECT id_expression " + " FROM genatt_verify_by where id_field=?";
69 private static final String SQL_QUERY_COUNT_FIELD_BY_ID_REGULAR_EXPRESSION = "SELECT COUNT(id_field) " + " FROM genatt_verify_by where id_expression = ?";
70 private static final String SQL_QUERY_SELECT_FIELD_BY_LIST_ID_ENTRY = SQL_QUERY_SELECT_ALL + " WHERE id_entry IN ";
71
72 private static final String PUBLIC_IMAGE_RESOURCE = "public_image_resource";
73
74
75
76
77
78
79
80 private int newPosition( Plugin plugin )
81 {
82 int nPos;
83 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_POSITION, plugin ) )
84 {
85 daoUtil.executeQuery( );
86
87 if ( !daoUtil.next( ) )
88 {
89
90 nPos = 1;
91 }
92
93 nPos = daoUtil.getInt( 1 ) + 1;
94 }
95
96 return nPos;
97 }
98
99
100
101
102 @Override
103 public synchronized int insert( Field field, Plugin plugin )
104 {
105 field.setPosition( newPosition( plugin ) );
106
107 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin ) )
108 {
109 int nIndex = 1;
110 daoUtil.setInt( nIndex++, field.getParentEntry( ).getIdEntry( ) );
111 daoUtil.setString( nIndex++, field.getCode( ) );
112 daoUtil.setString( nIndex++, field.getTitle( ) );
113 daoUtil.setString( nIndex++, field.getValue( ) );
114 daoUtil.setBoolean( nIndex++, field.isDefaultValue( ) );
115 daoUtil.setInt( nIndex++, field.getPosition( ) );
116 daoUtil.setDate( nIndex++, ( field.getValueTypeDate( ) == null ) ? null : new Date( field.getValueTypeDate( ).getTime( ) ) );
117 daoUtil.setBoolean( nIndex++, field.isNoDisplayTitle( ) );
118 daoUtil.setString( nIndex++, field.getComment( ) );
119 daoUtil.setString( nIndex++, ( field.getFileImage( ) == null ) ? null : field.getFileImage( ).getFileKey( ) );
120
121
122 daoUtil.executeUpdate( );
123 if ( daoUtil.nextGeneratedKey( ) )
124 {
125 field.setIdField( daoUtil.getGeneratedKeyInt( 1 ) );
126 }
127 }
128 return field.getIdField( );
129 }
130
131
132
133
134 @Override
135 public Field load( int nId, Plugin plugin )
136 {
137 Field field = null;
138 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_PRIMARY_KEY, plugin ) )
139 {
140 daoUtil.setInt( 1, nId );
141 daoUtil.executeQuery( );
142
143 if ( daoUtil.next( ) )
144 {
145 field = dataToObject( daoUtil );
146 }
147
148 }
149 return field;
150 }
151
152
153
154
155 @Override
156 public void delete( int nIdField, Plugin plugin )
157 {
158 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
159 {
160 daoUtil.setInt( 1, nIdField );
161 daoUtil.executeUpdate( );
162 }
163 }
164
165
166
167
168 @Override
169 public void store( Field field, Plugin plugin )
170 {
171 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
172 {
173 int nIndex = 1;
174 daoUtil.setInt( nIndex++, field.getIdField( ) );
175 daoUtil.setInt( nIndex++, field.getParentEntry( ).getIdEntry( ) );
176 daoUtil.setString( nIndex++, field.getCode( ) );
177 daoUtil.setString( nIndex++, field.getTitle( ) );
178 daoUtil.setString( nIndex++, field.getValue( ) );
179 daoUtil.setBoolean( nIndex++, field.isDefaultValue( ) );
180 daoUtil.setInt( nIndex++, field.getPosition( ) );
181 daoUtil.setDate( nIndex++, ( field.getValueTypeDate( ) == null ) ? null : new Date( field.getValueTypeDate( ).getTime( ) ) );
182 daoUtil.setBoolean( nIndex++, field.isNoDisplayTitle( ) );
183 daoUtil.setString( nIndex++, field.getComment( ) );
184 daoUtil.setString( nIndex++, ( field.getFileImage( ) == null ) ? null : field.getFileImage( ).getFileKey( ) );
185
186 daoUtil.setInt( nIndex++, field.getIdField( ) );
187 daoUtil.executeUpdate( );
188 }
189 }
190
191
192
193
194 @Override
195 public List<Field> selectFieldListByIdEntry( int nIdEntry, Plugin plugin )
196 {
197 List<Field> fieldList = new ArrayList<>( );
198
199 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_FIELD_BY_ID_ENTRY, plugin ) )
200 {
201 daoUtil.setInt( 1, nIdEntry );
202 daoUtil.executeQuery( );
203
204 while ( daoUtil.next( ) )
205 {
206 Field field = dataToObject( daoUtil );
207 fieldList.add( field );
208 }
209 }
210 return fieldList;
211 }
212
213
214
215
216 @Override
217 public void deleteVerifyBy( int nIdField, int nIdExpression, Plugin plugin )
218 {
219 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_VERIF_BY, plugin ) )
220 {
221 daoUtil.setInt( 1, nIdField );
222 daoUtil.setInt( 2, nIdExpression );
223 daoUtil.executeUpdate( );
224 }
225 }
226
227
228
229
230 @Override
231 public void insertVerifyBy( int nIdField, int nIdExpression, Plugin plugin )
232 {
233 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_VERIF_BY, plugin ) )
234 {
235 daoUtil.setInt( 1, nIdField );
236 daoUtil.setInt( 2, nIdExpression );
237 daoUtil.executeUpdate( );
238 }
239 }
240
241
242
243
244 @Override
245 public List<Integer> selectListRegularExpressionKeyByIdField( int nIdField, Plugin plugin )
246 {
247 List<Integer> regularExpressionList = new ArrayList<>( );
248 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_REGULAR_EXPRESSION_BY_ID_FIELD, plugin ) )
249 {
250 daoUtil.setInt( 1, nIdField );
251 daoUtil.executeQuery( );
252
253 while ( daoUtil.next( ) )
254 {
255 regularExpressionList.add( daoUtil.getInt( 1 ) );
256 }
257
258 }
259
260 return regularExpressionList;
261 }
262
263
264
265
266 @Override
267 public boolean isRegularExpressionIsUse( int nIdExpression, Plugin plugin )
268 {
269 int nNumberEntry = 0;
270
271 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_COUNT_FIELD_BY_ID_REGULAR_EXPRESSION, plugin ) )
272 {
273 daoUtil.setInt( 1, nIdExpression );
274 daoUtil.executeQuery( );
275
276 if ( daoUtil.next( ) )
277 {
278 nNumberEntry = daoUtil.getInt( 1 );
279 }
280
281 }
282
283 return nNumberEntry != 0;
284 }
285
286 @Override
287 public List<Field> loadMultipleByEntryIdList( List<Integer> idList, Plugin plugin )
288 {
289 List<Field> list = new ArrayList<>( );
290 if ( CollectionUtils.isEmpty( idList ) )
291 {
292 return list;
293 }
294 String query = SQL_QUERY_SELECT_FIELD_BY_LIST_ID_ENTRY + " ( " + idList.stream( ).distinct( ).map( i -> "?" ).collect( Collectors.joining( "," ) )
295 + " )";
296
297 try ( DAOUtil daoUtil = new DAOUtil( query, plugin ) )
298 {
299 for ( int i = 0; i < idList.size( ); i++ )
300 {
301 daoUtil.setInt( i + 1, idList.get( i ) );
302 }
303 daoUtil.executeQuery( );
304
305 while ( daoUtil.next( ) )
306 {
307 list.add( dataToObject( daoUtil ) );
308 }
309 }
310 return list;
311 }
312
313 private Field dataToObject( DAOUtil daoUtil )
314 {
315 int nIndex = 1;
316 Fieldins/genericattributes/business/Field.html#Field">Field field = new Field( );
317 field.setIdField( daoUtil.getInt( nIndex++ ) );
318
319 Entryins/genericattributes/business/Entry.html#Entry">Entry entry = new Entry( );
320 entry.setIdEntry( daoUtil.getInt( nIndex++ ) );
321 field.setParentEntry( entry );
322 field.setCode( daoUtil.getString( nIndex++ ) );
323 field.setTitle( daoUtil.getString( nIndex++ ) );
324 field.setValue( daoUtil.getString( nIndex++ ) );
325 field.setDefaultValue( daoUtil.getBoolean( nIndex++ ) );
326 field.setPosition( daoUtil.getInt( nIndex++ ) );
327 field.setValueTypeDate( daoUtil.getDate( nIndex++ ) );
328 field.setNoDisplayTitle( daoUtil.getBoolean( nIndex++ ) );
329 field.setComment( daoUtil.getString( nIndex++ ) );
330 File fileImage = new File( );
331 if ( daoUtil.getString( nIndex ) != null )
332 {
333 fileImage.setFileKey( daoUtil.getString( nIndex ) );
334 fileImage.setUrl( ImageResourceManager.getImageUrl( PUBLIC_IMAGE_RESOURCE, Integer.parseInt( fileImage.getFileKey( ) ) ) );
335 }
336 field.setFileImage( fileImage );
337
338 return field;
339 }
340
341 @Override
342 public List<Field> loadByCode( String code, Plugin plugin )
343 {
344 List<Field> result = new ArrayList<>( );
345 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_CODE, plugin ) )
346 {
347 daoUtil.setString( 1, code );
348 daoUtil.executeQuery( );
349
350 while ( daoUtil.next( ) )
351 {
352 result.add( dataToObject( daoUtil ) );
353 }
354
355 }
356 return result;
357 }
358 }