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.resource.business.database;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.portal.service.util.AppLogService;
38 import fr.paris.lutece.util.sql.DAOUtil;
39
40 import java.util.ArrayList;
41 import java.util.List;
42
43
44
45
46 public class DatabaseResourceDAO implements IDatabaseResourceDAO
47 {
48
49 private static final String SQL_QUERY_NEW_PRIMARY_KEY = " SELECT MAX(id_resource) FROM resource_resource ";
50 private static final String SQL_QUERY_SELECT_RESOURCE = " SELECT id_resource, resource_type, resource_name FROM resource_resource ";
51 private static final String SQL_QUERY_SELECT_RESOURCE_BY_ID = SQL_QUERY_SELECT_RESOURCE + " WHERE id_resource = ? ";
52 private static final String SQL_QUERY_SELECT_RESOURCE_ID = " SELECT id_resource FROM resource_resource ";
53 private static final String SQL_QUERY_SELECT_RESOURCE_LIST_BY_ID = SQL_QUERY_SELECT_RESOURCE + " WHERE id_resource IN ( ";
54 private static final String SQL_QUERY_SELECT_RESOURCE_BY_RESOURCE_TYPE = SQL_QUERY_SELECT_RESOURCE + " WHERE resource_type = ? ";
55
56
57 private static final String SQL_QUERY_INSERT_RESOURCE = " INSERT INTO resource_resource( id_resource, resource_type, resource_name ) VALUES (?,?,?) ";
58 private static final String SQL_QUERY_UPDATE_RESOURCE = " UPDATE resource_resource SET resource_type = ?, resource_name = ? WHERE id_resource = ? ";
59 private static final String SQL_QUERY_DELETE_RESOURCE = " DELETE FROM resource_resource WHERE id_resource = ? ";
60
61
62 private static final String ORDER_BY = " ORDER BY ";
63 private static final String ORDER_BY_ASCENDING = " ASC ";
64 private static final String ORDER_BY_DESCENDING = " DESC ";
65 private static final String CONSTANT_QUESTION_MARK = "?";
66 private static final String CONSTANT_COMMA = ",";
67 private static final String CONSTANT_CLOSE_PARENTHESIS = ")";
68
69
70
71
72
73
74
75
76 private int newPrimaryKey( Plugin plugin )
77 {
78 int nRes;
79 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PRIMARY_KEY, plugin ) )
80 {
81 daoUtil.executeQuery( );
82
83 if ( daoUtil.next( ) )
84 {
85 nRes = daoUtil.getInt( 1 ) + 1;
86 }
87 else
88 {
89 nRes = 1;
90 }
91 }
92
93 return nRes;
94 }
95
96
97
98
99 @Override
100 public synchronized void insert( DatabaseResource resource, Plugin plugin )
101 {
102 int nId = newPrimaryKey( plugin );
103 resource.setIdResource( nId );
104
105 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_RESOURCE, plugin ) )
106 {
107 int nIndex = 1;
108 daoUtil.setInt( nIndex++, nId );
109 daoUtil.setString( nIndex++, resource.getResourceType( ) );
110 daoUtil.setString( nIndex, resource.getResourceName( ) );
111 daoUtil.executeUpdate( );
112 }
113 }
114
115
116
117
118 @Override
119 public void update( DatabaseResource resource, Plugin plugin )
120 {
121 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_RESOURCE, plugin ) )
122 {
123 int nIndex = 1;
124
125 try
126 {
127 daoUtil.setString( nIndex++, resource.getResourceType( ) );
128 daoUtil.setString( nIndex++, resource.getResourceName( ) );
129 daoUtil.setInt( nIndex, Integer.parseInt( resource.getIdResource( ) ) );
130 daoUtil.executeUpdate( );
131 }
132 catch( NumberFormatException e )
133 {
134 AppLogService.error( e.getMessage( ), e );
135 }
136 }
137 }
138
139
140
141
142 @Override
143 public void delete( int nIdResource, Plugin plugin )
144 {
145 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_RESOURCE, plugin ) )
146 {
147 daoUtil.setInt( 1, nIdResource );
148 daoUtil.executeUpdate( );
149 }
150 }
151
152
153
154
155 @Override
156 public DatabaseResource findByPrimaryKey( int nIdResource, Plugin plugin )
157 {
158 DatabaseResource resource = null;
159 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_RESOURCE_BY_ID, plugin ) )
160 {
161 daoUtil.setInt( 1, nIdResource );
162 daoUtil.executeQuery( );
163
164 if ( daoUtil.next( ) )
165 {
166 resource = getResourceFromDAO( daoUtil );
167 }
168 }
169
170 return resource;
171 }
172
173
174
175
176 @Override
177 public List<DatabaseResource> findAll( Plugin plugin )
178 {
179 List<DatabaseResource> listResources = new ArrayList<>( );
180 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_RESOURCE, plugin ) )
181 {
182 daoUtil.executeQuery( );
183
184 while ( daoUtil.next( ) )
185 {
186 listResources.add( getResourceFromDAO( daoUtil ) );
187 }
188 }
189
190 return listResources;
191 }
192
193
194
195
196 @Override
197 public List<Integer> findAllId( DatabaseResourceSort resourceSort, Plugin plugin )
198 {
199 List<Integer> listResourcesId = new ArrayList<>( );
200 StringBuilder sbSql = new StringBuilder( SQL_QUERY_SELECT_RESOURCE_ID );
201 sbSql.append( ORDER_BY );
202 sbSql.append( resourceSort.getSort( ) );
203 sbSql.append( resourceSort.getSortAsc( ) ? ORDER_BY_ASCENDING : ORDER_BY_DESCENDING );
204
205 try ( DAOUtil daoUtil = new DAOUtil( sbSql.toString( ), plugin ) )
206 {
207 daoUtil.executeQuery( );
208
209 while ( daoUtil.next( ) )
210 {
211 listResourcesId.add( daoUtil.getInt( 1 ) );
212 }
213 }
214
215 return listResourcesId;
216 }
217
218
219
220
221 @Override
222 public List<DatabaseResource> findByListId( List<Integer> listId, DatabaseResourceSort resourceSort, Plugin plugin )
223 {
224 if ( ( listId == null ) || ( listId.isEmpty( ) ) )
225 {
226 return new ArrayList<>( );
227 }
228
229 StringBuilder sbSql = new StringBuilder( SQL_QUERY_SELECT_RESOURCE_LIST_BY_ID );
230
231 for ( int i = 0; i < listId.size( ); i++ )
232 {
233 if ( i > 0 )
234 {
235 sbSql.append( CONSTANT_COMMA );
236 }
237
238 sbSql.append( CONSTANT_QUESTION_MARK );
239 }
240
241 sbSql.append( CONSTANT_CLOSE_PARENTHESIS );
242
243 sbSql.append( ORDER_BY );
244 sbSql.append( resourceSort.getSort( ) );
245 sbSql.append( resourceSort.getSortAsc( ) ? ORDER_BY_ASCENDING : ORDER_BY_DESCENDING );
246
247 List<DatabaseResource> listResources = new ArrayList<>( );
248 try ( DAOUtil daoUtil = new DAOUtil( sbSql.toString( ), plugin ) )
249 {
250 int nIndex = 1;
251
252 for ( int nId : listId )
253 {
254 daoUtil.setInt( nIndex++, nId );
255 }
256
257 daoUtil.executeQuery( );
258
259 while ( daoUtil.next( ) )
260 {
261 listResources.add( getResourceFromDAO( daoUtil ) );
262 }
263 }
264
265 return listResources;
266 }
267
268
269
270
271 @Override
272 public List<DatabaseResource> findByResourceType( String strResourceType, Plugin plugin )
273 {
274 List<DatabaseResource> listResources = new ArrayList<>( );
275 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_RESOURCE_BY_RESOURCE_TYPE, plugin ) )
276 {
277 daoUtil.setString( 1, strResourceType );
278 daoUtil.executeQuery( );
279
280 while ( daoUtil.next( ) )
281 {
282 listResources.add( getResourceFromDAO( daoUtil ) );
283 }
284 }
285
286 return listResources;
287 }
288
289
290
291
292
293
294
295
296
297
298 private DatabaseResource getResourceFromDAO( DAOUtil daoUtil )
299 {
300 DatabaseResourcee/business/database/DatabaseResource.html#DatabaseResource">DatabaseResource resource = new DatabaseResource( );
301 int nIndex = 1;
302
303 resource.setIdResource( daoUtil.getInt( nIndex++ ) );
304 resource.setResourceType( daoUtil.getString( nIndex++ ) );
305 resource.setResourceName( daoUtil.getString( nIndex ) );
306
307 return resource;
308 }
309 }