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
35
36 package fr.paris.lutece.plugins.termofservice.business;
37
38 import fr.paris.lutece.portal.service.plugin.Plugin;
39 import fr.paris.lutece.util.ReferenceList;
40 import fr.paris.lutece.util.sql.DAOUtil;
41 import java.sql.Statement;
42
43 import java.util.ArrayList;
44 import java.util.List;
45 import java.util.Optional;
46
47
48
49
50 public final class EntryDAO implements IEntryDAO
51 {
52
53 private static final String SQL_QUERY_SELECT = "SELECT id_entry, title, text, version, published FROM termofservice_entry WHERE id_entry = ?";
54 private static final String SQL_QUERY_SELECT_LAST_VERSION = "SELECT id_entry, title, text, version, published FROM termofservice_entry WHERE version = (select max( version ) from termofservice_entry ) ";
55 private static final String SQL_QUERY_INSERT = "INSERT INTO termofservice_entry ( title, text, version, published ) VALUES ( ?, ?, ?, ? ) ";
56 private static final String SQL_QUERY_DELETE = "DELETE FROM termofservice_entry WHERE id_entry = ? ";
57 private static final String SQL_QUERY_UPDATE = "UPDATE termofservice_entry SET id_entry = ?, title = ? , text = ?, version = ?, published = ? WHERE id_entry = ?";
58 private static final String SQL_QUERY_SELECTALL = "SELECT id_entry, title, text, version, published FROM termofservice_entry";
59 private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_entry FROM termofservice_entry";
60 private static final String SQL_QUERY_SELECTALL_BY_IDS = "SELECT id_entry, title, text, version, published FROM termofservice_entry WHERE id_entry IN ( ";
61 private static final String SQL_QUERY_PUBLISH = "UPDATE termofservice_entry SET published = 1 WHERE id_entry = ?";
62 private static final String SQL_QUERY_UNPUBLISH_ALL = "UPDATE termofservice_entry SET published = 0";
63 private static final String SQL_QUERY_SELECT_PUBLISHED = "SELECT id_entry, title, text, version, published FROM termofservice_entry WHERE published = 1 ";
64
65
66
67
68 @Override
69 public void insert( Entry entry, Plugin plugin )
70 {
71 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin ) )
72 {
73 int nIndex = 1;
74 daoUtil.setString( nIndex++ , entry.getTitle( ) );
75 daoUtil.setString( nIndex++ , entry.getText( ) );
76 daoUtil.setInt( nIndex++ , entry.getVersion( ) );
77 daoUtil.setBoolean( nIndex++ , entry.isPublished( ) );
78
79 daoUtil.executeUpdate( );
80 if ( daoUtil.nextGeneratedKey( ) )
81 {
82 entry.setId( daoUtil.getGeneratedKeyInt( 1 ) );
83 }
84 }
85
86 }
87
88
89
90
91 @Override
92 public Optional<Entry> load( int nKey, Plugin plugin )
93 {
94 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
95 {
96 daoUtil.setInt( 1 , nKey );
97 daoUtil.executeQuery( );
98 Entry entry = null;
99
100 if ( daoUtil.next( ) )
101 {
102 entry = new Entry();
103 int nIndex = 1;
104
105 entry.setId( daoUtil.getInt( nIndex++ ) );
106 entry.setTitle( daoUtil.getString( nIndex++ ) );
107 entry.setText( daoUtil.getString( nIndex++ ) );
108 entry.setVersion( daoUtil.getInt( nIndex++ ) );
109 entry.setPublished( daoUtil.getBoolean( nIndex ) );
110 }
111
112 return Optional.ofNullable( entry );
113 }
114 }
115
116
117
118
119 @Override
120 public void delete( int nKey, Plugin plugin )
121 {
122 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
123 {
124 daoUtil.setInt( 1 , nKey );
125 daoUtil.executeUpdate( );
126 }
127 }
128
129
130
131
132 @Override
133 public void store( Entry entry, Plugin plugin )
134 {
135 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
136 {
137 int nIndex = 1;
138
139 daoUtil.setInt( nIndex++ , entry.getId( ) );
140 daoUtil.setString( nIndex++ , entry.getTitle( ) );
141 daoUtil.setString( nIndex++ , entry.getText( ) );
142 daoUtil.setInt( nIndex++ , entry.getVersion( ) );
143 daoUtil.setBoolean( nIndex++ , entry.isPublished( ) );
144 daoUtil.setInt( nIndex , entry.getId( ) );
145
146 daoUtil.executeUpdate( );
147 }
148 }
149
150
151
152
153 @Override
154 public List<Entry> selectEntrysList( Plugin plugin )
155 {
156 List<Entry> entryList = new ArrayList<>( );
157 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
158 {
159 daoUtil.executeQuery( );
160
161 while ( daoUtil.next( ) )
162 {
163 Entryins/termofservice/business/Entry.html#Entry">Entry entry = new Entry( );
164 int nIndex = 1;
165
166 entry.setId( daoUtil.getInt( nIndex++ ) );
167 entry.setTitle( daoUtil.getString( nIndex++ ) );
168 entry.setText( daoUtil.getString( nIndex++ ) );
169 entry.setVersion( daoUtil.getInt( nIndex++ ) );
170 entry.setPublished( daoUtil.getBoolean( nIndex ) );
171
172 entryList.add( entry );
173 }
174
175 return entryList;
176 }
177 }
178
179
180
181
182 @Override
183 public List<Integer> selectIdEntrysList( Plugin plugin )
184 {
185 List<Integer> entryList = new ArrayList<>( );
186 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin ) )
187 {
188 daoUtil.executeQuery( );
189
190 while ( daoUtil.next( ) )
191 {
192 entryList.add( daoUtil.getInt( 1 ) );
193 }
194
195 return entryList;
196 }
197 }
198
199
200
201
202 @Override
203 public ReferenceList selectEntrysReferenceList( Plugin plugin )
204 {
205 ReferenceList entryList = new ReferenceList();
206 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
207 {
208 daoUtil.executeQuery( );
209
210 while ( daoUtil.next( ) )
211 {
212 entryList.addItem( daoUtil.getInt( 1 ) , daoUtil.getString( 3 ) );
213 }
214
215 return entryList;
216 }
217 }
218
219
220
221
222 @Override
223 public List<Entry> selectEntrysListByIds( Plugin plugin, List<Integer> listIds ) {
224 List<Entry> entryList = new ArrayList<>( );
225
226 StringBuilder builder = new StringBuilder( );
227
228 if ( !listIds.isEmpty( ) )
229 {
230 for( int i = 0 ; i < listIds.size(); i++ ) {
231 builder.append( "?," );
232 }
233
234 String placeHolders = builder.deleteCharAt( builder.length( ) -1 ).toString( );
235 String stmt = SQL_QUERY_SELECTALL_BY_IDS + placeHolders + ")";
236
237
238 try ( DAOUtil daoUtil = new DAOUtil( stmt, plugin ) )
239 {
240 int index = 1;
241 for( Integer n : listIds ) {
242 daoUtil.setInt( index++, n );
243 }
244
245 daoUtil.executeQuery( );
246 while ( daoUtil.next( ) )
247 {
248 Entry entry = new Entry( );
249 int nIndex = 1;
250
251 entry.setId( daoUtil.getInt( nIndex++ ) );
252 entry.setTitle( daoUtil.getString( nIndex++ ) );
253 entry.setText( daoUtil.getString( nIndex++ ) );
254 entry.setVersion( daoUtil.getInt( nIndex++ ) );
255 entry.setPublished( daoUtil.getBoolean( nIndex ) );
256
257 entryList.add( entry );
258 }
259
260 daoUtil.free( );
261
262 }
263 }
264 return entryList;
265
266 }
267
268 @Override
269 public Optional<Entry> loadLastVersion(Plugin _plugin) {
270 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LAST_VERSION, _plugin ) )
271 {
272 daoUtil.executeQuery( );
273 Entry entry = null;
274
275 if ( daoUtil.next( ) )
276 {
277 entry = new Entry();
278 int nIndex = 1;
279
280 entry.setId( daoUtil.getInt( nIndex++ ) );
281 entry.setTitle( daoUtil.getString( nIndex++ ) );
282 entry.setText( daoUtil.getString( nIndex++ ) );
283 entry.setVersion( daoUtil.getInt( nIndex++ ) );
284 entry.setPublished( daoUtil.getBoolean( nIndex ) );
285 }
286
287 return Optional.ofNullable( entry );
288 }
289 }
290
291 @Override
292 public void publish( int nKey, Plugin plugin )
293 {
294 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_PUBLISH, plugin ) )
295 {
296 daoUtil.setInt( 1, nKey );
297
298 daoUtil.executeUpdate( );
299 }
300 }
301
302 @Override
303 public void unpublishAll( Plugin plugin )
304 {
305 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UNPUBLISH_ALL, plugin ) )
306 {
307 daoUtil.executeUpdate( );
308 }
309 }
310
311 @Override
312 public Optional<Entry> loadPublishedEntry( Plugin plugin )
313 {
314 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PUBLISHED, plugin ) )
315 {
316 daoUtil.executeQuery( );
317 Entry entry = null;
318
319 if ( daoUtil.next( ) )
320 {
321 entry = new Entry();
322 int nIndex = 1;
323
324 entry.setId( daoUtil.getInt( nIndex++ ) );
325 entry.setTitle( daoUtil.getString( nIndex++ ) );
326 entry.setText( daoUtil.getString( nIndex++ ) );
327 entry.setVersion( daoUtil.getInt( nIndex++ ) );
328 entry.setPublished( daoUtil.getBoolean( nIndex ) );
329 }
330
331 return Optional.ofNullable( entry );
332 }
333 }
334 }