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.mylutece.modules.wssodatabase.authentication.business;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.sql.DAOUtil;
38
39 import java.util.ArrayList;
40 import java.util.Collection;
41
42
43
44
45
46 public final class WssoUserDAO implements IWssoUserDAO
47 {
48
49 private static final String SQL_QUERY_NEW_PK = " SELECT max( mylutece_wsso_user_id ) FROM mylutece_wsso_user ";
50 private static final String SQL_QUERY_SELECT = " SELECT mylutece_wsso_user_id, guid, last_name, first_name, email, date_last_login FROM mylutece_wsso_user WHERE mylutece_wsso_user_id = ? ";
51 private static final String SQL_QUERY_INSERT = " INSERT INTO mylutece_wsso_user ( mylutece_wsso_user_id, guid, last_name, first_name, email ) VALUES ( ?, ?, ?, ?, ? ) ";
52 private static final String SQL_QUERY_DELETE = " DELETE FROM mylutece_wsso_user WHERE mylutece_wsso_user_id = ? ";
53 private static final String SQL_QUERY_UPDATE = " UPDATE mylutece_wsso_user SET mylutece_wsso_user_id = ?, guid = ?, last_name = ?, first_name = ?, email = ? WHERE mylutece_wsso_user_id = ? ";
54 private static final String SQL_QUERY_SELECTALL = " SELECT mylutece_wsso_user_id, guid, last_name, first_name, email, date_last_login FROM mylutece_wsso_user ORDER BY last_name, first_name, email ";
55 private static final String SQL_QUERY_SELECTALL_FOR_ROLE = " SELECT u.mylutece_wsso_user_id, u.guid, u.last_name, u.first_name, u.email, u.date_last_login FROM mylutece_wsso_user u, mylutece_wsso_user_role ur WHERE u.mylutece_wsso_user_id = ur.mylutece_wsso_user_id AND ur.mylutece_wsso_role_id = ? ORDER BY u.last_name, u.first_name, u.email ";
56 private static final String SQL_QUERY_SELECTALL_FOR_GUID = " SELECT mylutece_wsso_user_id, guid, last_name, first_name, email, date_last_login FROM mylutece_wsso_user WHERE guid = ? ORDER BY last_name, first_name, email ";
57
58
59 private static WssoUserDAO _dao = new WssoUserDAO( );
60
61
62
63
64 private WssoUserDAO( )
65 {
66 }
67
68
69
70
71
72
73 static WssoUserDAO getInstance( )
74 {
75 return _dao;
76 }
77
78
79
80
81
82
83 public int newPrimaryKey( Plugin plugin )
84 {
85 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
86 daoUtil.executeQuery( );
87
88 int nKey;
89
90 if ( !daoUtil.next( ) )
91 {
92
93 nKey = 1;
94 }
95
96 nKey = daoUtil.getInt( 1 ) + 1;
97
98 daoUtil.free( );
99
100 return nKey;
101 }
102
103
104
105
106
107
108
109 public void insert( WssoUser wssoUser, Plugin plugin )
110 {
111 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
112 wssoUser.setMyluteceWssoUserId( newPrimaryKey( plugin ) );
113 daoUtil.setInt( 1, wssoUser.getMyluteceWssoUserId( ) );
114 daoUtil.setString( 2, wssoUser.getGuid( ) );
115 daoUtil.setString( 3, wssoUser.getLastName( ) );
116 daoUtil.setString( 4, wssoUser.getFirstName( ) );
117 daoUtil.setString( 5, wssoUser.getEmail( ) );
118
119 daoUtil.executeUpdate( );
120 daoUtil.free( );
121 }
122
123
124
125
126
127
128
129
130 public WssoUser load( int nWssoUserId, Plugin plugin )
131 {
132 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
133 daoUtil.setInt( 1, nWssoUserId );
134 daoUtil.executeQuery( );
135
136 WssoUser wssoUser = null;
137
138 if ( daoUtil.next( ) )
139 {
140 wssoUser = new WssoUser( );
141 wssoUser.setMyluteceWssoUserId( daoUtil.getInt( 1 ) );
142 wssoUser.setGuid( daoUtil.getString( 2 ) );
143 wssoUser.setLastName( daoUtil.getString( 3 ) );
144 wssoUser.setFirstName( daoUtil.getString( 4 ) );
145 wssoUser.setEmail( daoUtil.getString( 5 ) );
146 wssoUser.setDateLastLogin( daoUtil.getDate( 6 ) );
147 }
148
149 daoUtil.free( );
150
151 return wssoUser;
152 }
153
154
155
156
157
158
159 public void delete( WssoUser wssoUser, Plugin plugin )
160 {
161 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
162 daoUtil.setInt( 1, wssoUser.getMyluteceWssoUserId( ) );
163
164 daoUtil.executeUpdate( );
165 daoUtil.free( );
166 }
167
168
169
170
171
172
173 public void store( WssoUser wssoUser, Plugin plugin )
174 {
175 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
176 daoUtil.setInt( 1, wssoUser.getMyluteceWssoUserId( ) );
177 daoUtil.setString( 2, wssoUser.getGuid( ) );
178 daoUtil.setString( 3, wssoUser.getLastName( ) );
179 daoUtil.setString( 4, wssoUser.getFirstName( ) );
180 daoUtil.setString( 5, wssoUser.getEmail( ) );
181 daoUtil.setInt( 6, wssoUser.getMyluteceWssoUserId( ) );
182
183 daoUtil.executeUpdate( );
184 daoUtil.free( );
185 }
186
187
188
189
190
191
192 public Collection<WssoUser> selectWssoUserList( Plugin plugin )
193 {
194 Collection<WssoUser> listWssoUsers = new ArrayList<WssoUser>( );
195 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
196 daoUtil.executeQuery( );
197
198 while ( daoUtil.next( ) )
199 {
200 WssoUser wssoUser = new WssoUser( );
201 wssoUser.setMyluteceWssoUserId( daoUtil.getInt( 1 ) );
202 wssoUser.setGuid( daoUtil.getString( 2 ) );
203 wssoUser.setLastName( daoUtil.getString( 3 ) );
204 wssoUser.setFirstName( daoUtil.getString( 4 ) );
205 wssoUser.setEmail( daoUtil.getString( 5 ) );
206 wssoUser.setDateLastLogin( daoUtil.getDate( 6 ) );
207
208 listWssoUsers.add( wssoUser );
209 }
210
211 daoUtil.free( );
212
213 return listWssoUsers;
214 }
215
216
217
218
219
220
221
222 public Collection<WssoUser> selectWssoUsersListForRole( int nIdRole, Plugin plugin )
223 {
224 Collection<WssoUser> listWssoUsers = new ArrayList<WssoUser>( );
225 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_FOR_ROLE, plugin );
226 daoUtil.setInt( 1, nIdRole );
227 daoUtil.executeQuery( );
228
229 while ( daoUtil.next( ) )
230 {
231 WssoUser wssoUser = new WssoUser( );
232 wssoUser.setMyluteceWssoUserId( daoUtil.getInt( 1 ) );
233 wssoUser.setGuid( daoUtil.getString( 2 ) );
234 wssoUser.setLastName( daoUtil.getString( 3 ) );
235 wssoUser.setFirstName( daoUtil.getString( 4 ) );
236 wssoUser.setEmail( daoUtil.getString( 5 ) );
237 wssoUser.setDateLastLogin( daoUtil.getDate( 6 ) );
238
239 listWssoUsers.add( wssoUser );
240 }
241
242 daoUtil.free( );
243
244 return listWssoUsers;
245 }
246
247
248
249
250
251
252
253 public Collection<WssoUser> selectWssoUserListForGuid( String strGuid, Plugin plugin )
254 {
255 Collection<WssoUser> listWssoUsers = new ArrayList<WssoUser>( );
256 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_FOR_GUID, plugin );
257 daoUtil.setString( 1, strGuid );
258 daoUtil.executeQuery( );
259
260 while ( daoUtil.next( ) )
261 {
262 WssoUser wssoUser = new WssoUser( );
263 wssoUser.setMyluteceWssoUserId( daoUtil.getInt( 1 ) );
264 wssoUser.setGuid( daoUtil.getString( 2 ) );
265 wssoUser.setLastName( daoUtil.getString( 3 ) );
266 wssoUser.setFirstName( daoUtil.getString( 4 ) );
267 wssoUser.setEmail( daoUtil.getString( 5 ) );
268 wssoUser.setDateLastLogin( daoUtil.getDate( 6 ) );
269
270 listWssoUsers.add( wssoUser );
271 }
272
273 daoUtil.free( );
274
275 return listWssoUsers;
276 }
277 }