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, text, version FROM termofservice_entry WHERE id_entry = ?";
54 private static final String SQL_QUERY_SELECT_LAST_VERSION = "SELECT id_entry, text, version FROM termofservice_entry WHERE version = (select max( version ) from termofservice_entry ) ";
55 private static final String SQL_QUERY_INSERT = "INSERT INTO termofservice_entry ( text, version ) 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 = ?, text = ?, version = ? WHERE id_entry = ?";
58 private static final String SQL_QUERY_SELECTALL = "SELECT id_entry, text, version 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, text, version FROM termofservice_entry WHERE id_entry IN ( ";
61
62
63
64
65 @Override
66 public void insert( Entry entry, Plugin plugin )
67 {
68 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin ) )
69 {
70 int nIndex = 1;
71 daoUtil.setString( nIndex++ , entry.getText( ) );
72 daoUtil.setInt( nIndex++ , entry.getVersion( ) );
73
74 daoUtil.executeUpdate( );
75 if ( daoUtil.nextGeneratedKey( ) )
76 {
77 entry.setId( daoUtil.getGeneratedKeyInt( 1 ) );
78 }
79 }
80
81 }
82
83
84
85
86 @Override
87 public Optional<Entry> load( int nKey, Plugin plugin )
88 {
89 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
90 {
91 daoUtil.setInt( 1 , nKey );
92 daoUtil.executeQuery( );
93 Entry entry = null;
94
95 if ( daoUtil.next( ) )
96 {
97 entry = new Entry();
98 int nIndex = 1;
99
100 entry.setId( daoUtil.getInt( nIndex++ ) );
101 entry.setText( daoUtil.getString( nIndex++ ) );
102 entry.setVersion( daoUtil.getInt( nIndex ) );
103 }
104
105 return Optional.ofNullable( entry );
106 }
107 }
108
109
110
111
112 @Override
113 public void delete( int nKey, Plugin plugin )
114 {
115 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
116 {
117 daoUtil.setInt( 1 , nKey );
118 daoUtil.executeUpdate( );
119 }
120 }
121
122
123
124
125 @Override
126 public void store( Entry entry, Plugin plugin )
127 {
128 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
129 {
130 int nIndex = 1;
131
132 daoUtil.setInt( nIndex++ , entry.getId( ) );
133 daoUtil.setString( nIndex++ , entry.getText( ) );
134 daoUtil.setInt( nIndex++ , entry.getVersion( ) );
135 daoUtil.setInt( nIndex , entry.getId( ) );
136
137 daoUtil.executeUpdate( );
138 }
139 }
140
141
142
143
144 @Override
145 public List<Entry> selectEntrysList( Plugin plugin )
146 {
147 List<Entry> entryList = new ArrayList<>( );
148 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
149 {
150 daoUtil.executeQuery( );
151
152 while ( daoUtil.next( ) )
153 {
154 Entryins/termofservice/business/Entry.html#Entry">Entry entry = new Entry( );
155 int nIndex = 1;
156
157 entry.setId( daoUtil.getInt( nIndex++ ) );
158 entry.setText( daoUtil.getString( nIndex++ ) );
159 entry.setVersion( daoUtil.getInt( nIndex ) );
160
161 entryList.add( entry );
162 }
163
164 return entryList;
165 }
166 }
167
168
169
170
171 @Override
172 public List<Integer> selectIdEntrysList( Plugin plugin )
173 {
174 List<Integer> entryList = new ArrayList<>( );
175 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin ) )
176 {
177 daoUtil.executeQuery( );
178
179 while ( daoUtil.next( ) )
180 {
181 entryList.add( daoUtil.getInt( 1 ) );
182 }
183
184 return entryList;
185 }
186 }
187
188
189
190
191 @Override
192 public ReferenceList selectEntrysReferenceList( Plugin plugin )
193 {
194 ReferenceList entryList = new ReferenceList();
195 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
196 {
197 daoUtil.executeQuery( );
198
199 while ( daoUtil.next( ) )
200 {
201 entryList.addItem( daoUtil.getInt( 1 ) , daoUtil.getString( 2 ) );
202 }
203
204 return entryList;
205 }
206 }
207
208
209
210
211 @Override
212 public List<Entry> selectEntrysListByIds( Plugin plugin, List<Integer> listIds ) {
213 List<Entry> entryList = new ArrayList<>( );
214
215 StringBuilder builder = new StringBuilder( );
216
217 if ( !listIds.isEmpty( ) )
218 {
219 for( int i = 0 ; i < listIds.size(); i++ ) {
220 builder.append( "?," );
221 }
222
223 String placeHolders = builder.deleteCharAt( builder.length( ) -1 ).toString( );
224 String stmt = SQL_QUERY_SELECTALL_BY_IDS + placeHolders + ")";
225
226
227 try ( DAOUtil daoUtil = new DAOUtil( stmt, plugin ) )
228 {
229 int index = 1;
230 for( Integer n : listIds ) {
231 daoUtil.setInt( index++, n );
232 }
233
234 daoUtil.executeQuery( );
235 while ( daoUtil.next( ) )
236 {
237 Entry entry = new Entry( );
238 int nIndex = 1;
239
240 entry.setId( daoUtil.getInt( nIndex++ ) );
241 entry.setText( daoUtil.getString( nIndex++ ) );
242 entry.setVersion( daoUtil.getInt( nIndex ) );
243
244 entryList.add( entry );
245 }
246
247 daoUtil.free( );
248
249 }
250 }
251 return entryList;
252
253 }
254
255 @Override
256 public Optional<Entry> loadLastVersion(Plugin _plugin) {
257 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LAST_VERSION, _plugin ) )
258 {
259 daoUtil.executeQuery( );
260 Entry entry = null;
261
262 if ( daoUtil.next( ) )
263 {
264 entry = new Entry();
265 int nIndex = 1;
266
267 entry.setId( daoUtil.getInt( nIndex++ ) );
268 entry.setText( daoUtil.getString( nIndex++ ) );
269 entry.setVersion( daoUtil.getInt( nIndex ) );
270 }
271
272 return Optional.ofNullable( entry );
273 }
274 }
275 }