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 package fr.paris.lutece.plugins.transparency.business;
36
37 import fr.paris.lutece.portal.service.plugin.Plugin;
38 import fr.paris.lutece.util.ReferenceList;
39 import fr.paris.lutece.util.sql.DAOUtil;
40 import java.sql.Statement;
41
42 import java.util.ArrayList;
43 import java.util.List;
44
45
46
47
48 public final class LobbyDAO implements ILobbyDAO
49 {
50
51 private static final String SQL_QUERY_SELECT = "SELECT transparency_lobby.id_lobby, name, national_id, national_id_type, url, json_data, version_date FROM transparency_lobby ";
52 private static final String SQL_QUERY_INSERT = "INSERT INTO transparency_lobby ( name, national_id, national_id_type, url, json_data, version_date ) VALUES ( ?, ?, ?, ?, ?, ? ) ";
53 private static final String SQL_QUERY_DELETE = "DELETE FROM transparency_lobby WHERE id_lobby = ? ";
54 private static final String SQL_QUERY_UPDATE = "UPDATE transparency_lobby SET id_lobby = ?, name = ?, national_id = ?, national_id_type = ?, url = ?, json_data = ?, version_date = ? WHERE id_lobby = ?";
55 private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_lobby FROM transparency_lobby";
56
57 private static final String SQL_WHERE_NAME_LIKE = " WHERE name like ? ";
58 private static final String SQL_WHERECLAUSE_BY_APPOINTMENT = " LEFT JOIN transparency_lobby_appointment on transparency_lobby_appointment.id_lobby = transparency_lobby.id_lobby WHERE id_appointment = ? ";
59 private static final String SQL_WHERECLAUSE_BY_ID = " WHERE id_lobby = ? ";
60 private static final String SQL_WHERECLAUSE_BY_NATIONAL_ID = " WHERE national_id = ? ";
61
62 private static final String SQL_ORDER_BY = " ORDER BY name ";
63
64
65
66
67 @Override
68 public void insert( Lobby lobby, Plugin plugin )
69 {
70 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin );
71 try
72 {
73 int nIndex = 1;
74 daoUtil.setString( nIndex++, lobby.getName( ) );
75 daoUtil.setString( nIndex++, lobby.getNationalId( ) );
76 daoUtil.setString( nIndex++, lobby.getNationalIdType( ) );
77 daoUtil.setString( nIndex++, lobby.getUrl( ) );
78 daoUtil.setString( nIndex++, lobby.getJsonData( ) );
79 daoUtil.setDate( nIndex++, lobby.getVersionDate( ) );
80
81 daoUtil.executeUpdate( );
82 if ( daoUtil.nextGeneratedKey( ) )
83 {
84 lobby.setId( daoUtil.getGeneratedKeyInt( 1 ) );
85 }
86 }
87 finally
88 {
89 daoUtil.free( );
90 }
91 }
92
93
94
95
96 @Override
97 public Lobby load( int nKey, Plugin plugin )
98 {
99 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT + SQL_WHERECLAUSE_BY_ID, plugin );
100 daoUtil.setInt( 1, nKey );
101 daoUtil.executeQuery( );
102 Lobby lobby = null;
103
104 if ( daoUtil.next( ) )
105 {
106 lobby = new Lobby( );
107 int nIndex = 1;
108
109 lobby.setId( daoUtil.getInt( nIndex++ ) );
110 lobby.setName( daoUtil.getString( nIndex++ ) );
111 lobby.setNationalId( daoUtil.getString( nIndex++ ) );
112 lobby.setNationalIdType( daoUtil.getString( nIndex++ ) );
113 lobby.setUrl( daoUtil.getString( nIndex++ ) );
114 lobby.setJsonData( daoUtil.getString( nIndex++ ) );
115 lobby.setVersionDate( daoUtil.getDate( nIndex++ ) );
116 }
117
118 daoUtil.free( );
119 return lobby;
120 }
121
122
123
124
125 @Override
126 public Lobby loadByNationalId( String strNationalId, Plugin plugin )
127 {
128 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT + SQL_WHERECLAUSE_BY_NATIONAL_ID, plugin );
129 daoUtil.setString( 1, strNationalId );
130 daoUtil.executeQuery( );
131 Lobby lobby = null;
132
133 if ( daoUtil.next( ) )
134 {
135 lobby = new Lobby( );
136 int nIndex = 1;
137
138 lobby.setId( daoUtil.getInt( nIndex++ ) );
139 lobby.setName( daoUtil.getString( nIndex++ ) );
140 lobby.setNationalId( daoUtil.getString( nIndex++ ) );
141 lobby.setNationalIdType( daoUtil.getString( nIndex++ ) );
142 lobby.setUrl( daoUtil.getString( nIndex++ ) );
143 lobby.setJsonData( daoUtil.getString( nIndex++ ) );
144 lobby.setVersionDate( daoUtil.getDate( nIndex++ ) );
145 }
146
147 daoUtil.free( );
148 return lobby;
149 }
150
151
152
153
154 @Override
155 public void delete( int nKey, Plugin plugin )
156 {
157 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
158 daoUtil.setInt( 1, nKey );
159 daoUtil.executeUpdate( );
160 daoUtil.free( );
161 }
162
163
164
165
166 @Override
167 public void store( Lobby lobby, Plugin plugin )
168 {
169 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
170 int nIndex = 1;
171
172 daoUtil.setInt( nIndex++, lobby.getId( ) );
173 daoUtil.setString( nIndex++, lobby.getName( ) );
174 daoUtil.setString( nIndex++, lobby.getNationalId( ) );
175 daoUtil.setString( nIndex++, lobby.getNationalIdType( ) );
176 daoUtil.setString( nIndex++, lobby.getUrl( ) );
177 daoUtil.setString( nIndex++, lobby.getJsonData( ) );
178 daoUtil.setDate( nIndex++, lobby.getVersionDate( ) );
179 daoUtil.setInt( nIndex, lobby.getId( ) );
180
181 daoUtil.executeUpdate( );
182 daoUtil.free( );
183 }
184
185
186
187
188 @Override
189 public List<Lobby> selectLobbiesList( String strLikeText, Plugin plugin )
190 {
191 List<Lobby> lobbyList = new ArrayList<Lobby>( );
192 String strSQL = SQL_QUERY_SELECT;
193 if ( strLikeText != null )
194 strSQL += SQL_WHERE_NAME_LIKE;
195 strSQL += SQL_ORDER_BY;
196
197 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
198 if ( strLikeText != null )
199 daoUtil.setString( 1, strLikeText );
200
201 daoUtil.executeQuery( );
202
203 while ( daoUtil.next( ) )
204 {
205 Lobby lobby = new Lobby( );
206 int nIndex = 1;
207
208 lobby.setId( daoUtil.getInt( nIndex++ ) );
209 lobby.setName( daoUtil.getString( nIndex++ ) );
210 lobby.setNationalId( daoUtil.getString( nIndex++ ) );
211 lobby.setNationalIdType( daoUtil.getString( nIndex++ ) );
212 lobby.setUrl( daoUtil.getString( nIndex++ ) );
213 lobby.setJsonData( daoUtil.getString( nIndex++ ) );
214 lobby.setVersionDate( daoUtil.getDate( nIndex++ ) );
215
216 lobbyList.add( lobby );
217 }
218
219 daoUtil.free( );
220 return lobbyList;
221 }
222
223
224
225
226 @Override
227 public List<Lobby> selectLobbiesListByAppointment( int idAppointment, Plugin plugin )
228 {
229 List<Lobby> lobbyList = new ArrayList<Lobby>( );
230 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT + SQL_WHERECLAUSE_BY_APPOINTMENT + SQL_ORDER_BY, plugin );
231 daoUtil.setInt( 1, idAppointment );
232 daoUtil.executeQuery( );
233
234 while ( daoUtil.next( ) )
235 {
236 Lobby lobby = new Lobby( );
237 int nIndex = 1;
238
239 lobby.setId( daoUtil.getInt( nIndex++ ) );
240 lobby.setName( daoUtil.getString( nIndex++ ) );
241 lobby.setNationalId( daoUtil.getString( nIndex++ ) );
242 lobby.setNationalIdType( daoUtil.getString( nIndex++ ) );
243 lobby.setUrl( daoUtil.getString( nIndex++ ) );
244 lobby.setJsonData( daoUtil.getString( nIndex++ ) );
245 lobby.setVersionDate( daoUtil.getDate( nIndex++ ) );
246
247 lobbyList.add( lobby );
248 }
249
250 daoUtil.free( );
251 return lobbyList;
252 }
253
254
255
256
257 @Override
258 public List<Integer> selectIdLobbiesList( Plugin plugin )
259 {
260 List<Integer> lobbyList = new ArrayList<Integer>( );
261 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin );
262 daoUtil.executeQuery( );
263
264 while ( daoUtil.next( ) )
265 {
266 lobbyList.add( daoUtil.getInt( 1 ) );
267 }
268
269 daoUtil.free( );
270 return lobbyList;
271 }
272
273
274
275
276 @Override
277 public ReferenceList selectLobbiesReferenceList( Plugin plugin )
278 {
279 ReferenceList lobbyList = new ReferenceList( );
280 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT + SQL_ORDER_BY, plugin );
281 daoUtil.executeQuery( );
282
283 while ( daoUtil.next( ) )
284 {
285 lobbyList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
286 }
287
288 daoUtil.free( );
289 return lobbyList;
290 }
291 }