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.insertajax.business;
35
36 import java.sql.Timestamp;
37 import java.util.ArrayList;
38 import java.util.Collection;
39
40 import fr.paris.lutece.portal.service.plugin.Plugin;
41 import fr.paris.lutece.util.sql.DAOUtil;
42
43
44
45
46
47 public class InsertAjaxDAO implements IInsertAjaxDAO
48 {
49
50 private static final String SQL_QUERY_NEWPK = "SELECT max( id_insertajax_request ) FROM insertajax_request ";
51 private static final String SQL_QUERY_SELECT = "SELECT id_insertajax_request, name, html, sqlrequest, workgroup_key, role FROM insertajax_request WHERE id_insertajax_request = ? ";
52 private static final String SQL_QUERY_SELECTALL = "SELECT id_insertajax_request, name, sqlrequest, workgroup_key, role FROM insertajax_request";
53 private static final String SQL_QUERY_SELECT_ENABLED_insertAjax_LIST = "SELECT id_insertajax_request, name, html, sqlrequest, workgroup_key FROM insertajax_request";
54 private static final String SQL_QUERY_INSERT = "INSERT INTO insertajax_request ( id_insertajax_request , name, html, sqlrequest, workgroup_key, role, date_update ) VALUES ( ?, ?, ?, ?, ?, ?, ? ) ";
55 private static final String SQL_QUERY_DELETE = "DELETE FROM insertajax_request WHERE id_insertajax_request = ? ";
56 private static final String SQL_QUERY_UPDATE = "UPDATE insertajax_request SET name = ? , html = ?, sqlrequest = ?, workgroup_key = ?, role = ?, date_update = ? WHERE id_insertajax_request = ? ";
57 private static final String SQL_LIST_IMG = "SELECT id_document, summary FROM document WHERE id_space = ?";
58
59
60
61
62
63
64 private int newPrimaryKey( Plugin plugin )
65 {
66 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEWPK, plugin );
67 daoUtil.executeQuery( );
68
69 int nKey;
70
71 if ( !daoUtil.next( ) )
72 {
73
74 nKey = 1;
75 }
76
77 nKey = daoUtil.getInt( 1 ) + 1;
78
79 daoUtil.free( );
80
81 return nKey;
82 }
83
84
85
86
87
88
89
90
91
92
93 public void insert( InsertAjax insertAjax, Plugin plugin )
94 {
95 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
96 int id = newPrimaryKey( plugin );
97 insertAjax.replacePrimaryKey(id);
98 insertAjax.setId( id );
99 daoUtil.setInt( 1, insertAjax.getId( ) );
100 daoUtil.setString( 2, insertAjax.getName( ) );
101 daoUtil.setString( 3, insertAjax.getHtml( ) );
102 daoUtil.setString( 4, insertAjax.getSql( ) );
103 daoUtil.setString( 5, insertAjax.getWorkgroup( ) );
104 daoUtil.setString( 6, insertAjax.getRole( ) );
105 daoUtil.setTimestamp( 7, new Timestamp( new java.util.Date( ).getTime( ) ) );
106
107 daoUtil.executeUpdate( );
108 daoUtil.free( );
109 }
110
111
112
113
114
115
116
117 public InsertAjax load( int nInsertAjaxId, Plugin plugin )
118 {
119 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
120 daoUtil.setInt( 1, nInsertAjaxId );
121 daoUtil.executeQuery( );
122
123 InsertAjax insertAjax = null;
124
125 if ( daoUtil.next( ) )
126 {
127 insertAjax = new InsertAjax( );
128 insertAjax.setId( daoUtil.getInt( 1 ) );
129 insertAjax.setName( daoUtil.getString( 2 ) );
130 insertAjax.setHtml( daoUtil.getString( 3 ) );
131 insertAjax.setSql( daoUtil.getString( 4 ) );
132 insertAjax.setWorkgroup( daoUtil.getString( 5 ) );
133 insertAjax.setRole( daoUtil.getString( 6 ) );
134 }
135
136 daoUtil.free( );
137
138 return insertAjax;
139 }
140
141
142
143
144
145
146 public void delete( InsertAjax insertAjax, Plugin plugin )
147 {
148 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
149 daoUtil.setInt( 1, insertAjax.getId( ) );
150 daoUtil.executeUpdate( );
151 daoUtil.free( );
152 }
153
154
155
156
157
158
159 public void store( InsertAjax insertAjax, Plugin plugin )
160 {
161 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
162 int ninsertAjaxId = insertAjax.getId( );
163
164 daoUtil.setString( 1, insertAjax.getName( ) );
165 daoUtil.setString( 2, insertAjax.getHtml( ) );
166 daoUtil.setString( 3, insertAjax.getSql( ) );
167 daoUtil.setString( 4, insertAjax.getWorkgroup( ) );
168 daoUtil.setString( 5, insertAjax.getRole( ) );
169 daoUtil.setTimestamp( 6, new Timestamp( new java.util.Date( ).getTime( ) ) );
170 daoUtil.setInt( 7, ninsertAjaxId );
171
172 daoUtil.executeUpdate( );
173 daoUtil.free( );
174 }
175
176
177
178
179
180
181
182 public Collection<InsertAjax> selectAll( Plugin plugin )
183 {
184 Collection<InsertAjax> insertAjaxList = new ArrayList<InsertAjax>( );
185 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
186 daoUtil.executeQuery( );
187
188 while ( daoUtil.next( ) )
189 {
190 InsertAjax insertAjax = new InsertAjax( );
191 insertAjax.setId( daoUtil.getInt( 1 ) );
192 insertAjax.setName( daoUtil.getString( 2 ) );
193
194 insertAjax.setSql( daoUtil.getString( 3 ) );
195 insertAjax.setWorkgroup( daoUtil.getString( 4 ) );
196 insertAjax.setRole( daoUtil.getString( 5 ) );
197 insertAjaxList.add( insertAjax );
198 }
199
200 daoUtil.free( );
201
202 return insertAjaxList;
203 }
204
205
206
207
208
209
210
211 public Collection<InsertAjax> selectEnabledInsertAjaxList( Plugin plugin )
212 {
213 Collection<InsertAjax> insertAjaxList = new ArrayList<InsertAjax>( );
214 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ENABLED_insertAjax_LIST, plugin );
215 daoUtil.executeQuery( );
216
217 while ( daoUtil.next( ) )
218 {
219 InsertAjax insertAjax = new InsertAjax( );
220 insertAjax.setId( daoUtil.getInt( 1 ) );
221 insertAjax.setName( daoUtil.getString( 2 ) );
222 insertAjax.setHtml( daoUtil.getString( 3 ) );
223 insertAjax.setSql( daoUtil.getString( 4 ) );
224 insertAjax.setWorkgroup( daoUtil.getString( 5 ) );
225 insertAjaxList.add( insertAjax );
226 }
227
228 daoUtil.free( );
229
230 return insertAjaxList;
231 }
232
233
234
235
236 public String executeSql( String strSql, Plugin plugin )
237 {
238 DAOUtil daoUtil = new DAOUtil( strSql, plugin );
239 daoUtil.executeQuery( );
240
241 String strResult = "";
242
243 while ( daoUtil.next( ) )
244 {
245 strResult = daoUtil.getString( 1 );
246 }
247
248 daoUtil.free( );
249
250 return strResult;
251 }
252
253 public Collection<Image> loadImagesListOfWorkspace(int nIdWorkspace, Plugin _plugin)
254 {
255
256
257
258
259
260 Collection<Image> imagesList = new ArrayList<Image>( );
261
262 DAOUtil daoUtil = new DAOUtil( SQL_LIST_IMG, _plugin );
263 daoUtil.setInt( 1, nIdWorkspace );
264 daoUtil.executeQuery( );
265
266 while ( daoUtil.next( ) )
267 {
268 Image img = new Image();
269 img.setId( daoUtil.getInt( 1 ) );
270 img.setComment( daoUtil.getString( 2 ) );
271 imagesList.add( img );
272 }
273
274 daoUtil.free( );
275 return imagesList;
276 }
277 }