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.openiddatabase.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 OpenIdDatabaseUserDAO implements IOpenIdDatabaseUserDAO
47 {
48
49 private static final String SQL_QUERY_NEW_PK = " SELECT max( mylutece_database_openid_user_id ) FROM mylutece_database_openid_user ";
50 private static final String SQL_QUERY_SELECT = " SELECT mylutece_database_openid_user_id, login, name_family, name_given, email, authen_type FROM mylutece_database_openid_user WHERE mylutece_database_openid_user_id = ? ";
51 private static final String SQL_QUERY_SELECT_PASSWORD = " SELECT password FROM mylutece_database_openid_user WHERE mylutece_database_openid_user_id = ? ";
52 private static final String SQL_QUERY_INSERT = " INSERT INTO mylutece_database_openid_user ( mylutece_database_openid_user_id, login, name_family, name_given, email, password, authen_type ) VALUES ( ?, ?, ?, ?, ?, ?,? ) ";
53 private static final String SQL_QUERY_DELETE = " DELETE FROM mylutece_database_openid_user WHERE mylutece_database_openid_user_id = ? ";
54 private static final String SQL_QUERY_UPDATE = " UPDATE mylutece_database_openid_user SET login = ?, name_family = ?, name_given = ?, email = ? WHERE mylutece_database_openid_user_id = ? ";
55 private static final String SQL_QUERY_UPDATE_BY_LOGIN = " UPDATE mylutece_database_openid_user SET name_family = ?, name_given = ?, email = ? WHERE login = ? AND authen_type = 'openid' ";
56 private static final String SQL_QUERY_UPDATE_PASSWORD = " UPDATE mylutece_database_openid_user SET password = ? WHERE mylutece_database_openid_user_id = ? ";
57 private static final String SQL_QUERY_SELECTALL = " SELECT mylutece_database_openid_user_id, login, name_family, name_given, email, authen_type FROM mylutece_database_openid_user ";
58 private static final String SQL_QUERY_SELECTALL_FOR_LOGIN = " SELECT mylutece_database_openid_user_id, login, name_family, name_given, email, authen_type FROM mylutece_database_openid_user WHERE login = ? ";
59 private static final String SQL_QUERY_SELECT_USER_FOR_EMAIL = " SELECT mylutece_database_openid_user_id, login, name_family, name_given, email, authen_type FROM mylutece_database_openid_user WHERE email = ? AND authen_type = 'database' ";
60 private static final String SQL_QUERY_CHECK_PASSWORD_FOR_USER_ID = " SELECT count(*) FROM mylutece_database_openid_user WHERE login = ? AND password = ? AND authen_type = 'database' ";
61 private static final String SQL_QUERY_CHECK_LOGIN_FOR_USER_ID = " SELECT count(*) FROM mylutece_database_openid_user WHERE login = ? ";
62
63
64 private static OpenIdDatabaseUserDAO _dao = new OpenIdDatabaseUserDAO( );
65
66
67
68
69
70
71 static OpenIdDatabaseUserDAO getInstance( )
72 {
73 return _dao;
74 }
75
76
77
78
79
80
81 public int newPrimaryKey( Plugin plugin )
82 {
83 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
84 daoUtil.executeQuery( );
85
86 int nKey;
87
88 if ( !daoUtil.next( ) )
89 {
90
91 nKey = 1;
92 }
93
94 nKey = daoUtil.getInt( 1 ) + 1;
95
96 daoUtil.free( );
97
98 return nKey;
99 }
100
101
102
103
104
105
106
107
108 public void insert( OpenIdDatabaseUser databaseUser, String strPassword, Plugin plugin )
109 {
110 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
111 databaseUser.setUserId( newPrimaryKey( plugin ) );
112 daoUtil.setInt( 1, databaseUser.getUserId( ) );
113 daoUtil.setString( 2, databaseUser.getLogin( ) );
114 daoUtil.setString( 3, databaseUser.getLastName( ) );
115 daoUtil.setString( 4, databaseUser.getFirstName( ) );
116 daoUtil.setString( 5, databaseUser.getEmail( ) );
117 daoUtil.setString( 6, strPassword );
118 daoUtil.setString( 7, databaseUser.getAuthenticationType( ) );
119 daoUtil.executeUpdate( );
120 daoUtil.free( );
121 }
122
123
124
125
126
127
128
129
130 public OpenIdDatabaseUser load( int nUserId, Plugin plugin )
131 {
132 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
133 daoUtil.setInt( 1, nUserId );
134 daoUtil.executeQuery( );
135
136 OpenIdDatabaseUser databaseUser = null;
137
138 if ( daoUtil.next( ) )
139 {
140 databaseUser = new OpenIdDatabaseUser( );
141 databaseUser.setUserId( daoUtil.getInt( 1 ) );
142 databaseUser.setLogin( daoUtil.getString( 2 ) );
143 databaseUser.setLastName( daoUtil.getString( 3 ) );
144 databaseUser.setFirstName( daoUtil.getString( 4 ) );
145 databaseUser.setEmail( daoUtil.getString( 5 ) );
146 databaseUser.setAuthentificationType( daoUtil.getString( 6 ) );
147 }
148
149 daoUtil.free( );
150
151 return databaseUser;
152 }
153
154
155
156
157
158
159 public void delete( OpenIdDatabaseUser databaseUser, Plugin plugin )
160 {
161 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
162 daoUtil.setInt( 1, databaseUser.getUserId( ) );
163
164 daoUtil.executeUpdate( );
165 daoUtil.free( );
166 }
167
168
169
170
171
172
173 public void store( OpenIdDatabaseUser databaseUser, Plugin plugin )
174 {
175 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
176 daoUtil.setString( 1, databaseUser.getLogin( ) );
177 daoUtil.setString( 2, databaseUser.getLastName( ) );
178 daoUtil.setString( 3, databaseUser.getFirstName( ) );
179 daoUtil.setString( 4, databaseUser.getEmail( ) );
180 daoUtil.setInt( 5, databaseUser.getUserId( ) );
181
182 daoUtil.executeUpdate( );
183 daoUtil.free( );
184 }
185
186
187
188
189
190
191 public void storeByLogin( OpenIdDatabaseUser databaseUser, Plugin plugin )
192 {
193 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_BY_LOGIN, plugin );
194
195 daoUtil.setString( 1, databaseUser.getLastName( ) );
196 daoUtil.setString( 2, databaseUser.getFirstName( ) );
197 daoUtil.setString( 3, databaseUser.getEmail( ) );
198 daoUtil.setString( 4, databaseUser.getLogin( ) );
199
200 daoUtil.executeUpdate( );
201 daoUtil.free( );
202 }
203
204
205
206
207
208
209
210 public void updatePassword( OpenIdDatabaseUser databaseUser, String strNewPassword, Plugin plugin )
211 {
212 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_PASSWORD, plugin );
213 daoUtil.setString( 1, strNewPassword );
214 daoUtil.setInt( 2, databaseUser.getUserId( ) );
215
216 daoUtil.executeUpdate( );
217 daoUtil.free( );
218 }
219
220
221
222
223
224
225
226
227 public String selectPasswordByPrimaryKey( int nDatabaseUserId, Plugin plugin )
228 {
229 String strPassword = null;
230 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PASSWORD, plugin );
231 daoUtil.setInt( 1, nDatabaseUserId );
232 daoUtil.executeQuery( );
233
234 if ( daoUtil.next( ) )
235 {
236 strPassword = daoUtil.getString( 1 );
237 }
238
239 daoUtil.free( );
240
241 return strPassword;
242 }
243
244
245
246
247
248
249 public Collection<OpenIdDatabaseUser> selectDatabaseUserList( Plugin plugin )
250 {
251 Collection<OpenIdDatabaseUser> listDatabaseUsers = new ArrayList<OpenIdDatabaseUser>( );
252 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
253 daoUtil.executeQuery( );
254
255 while ( daoUtil.next( ) )
256 {
257 OpenIdDatabaseUser databaseUser = new OpenIdDatabaseUser( );
258 databaseUser.setUserId( daoUtil.getInt( 1 ) );
259 databaseUser.setLogin( daoUtil.getString( 2 ) );
260 databaseUser.setLastName( daoUtil.getString( 3 ) );
261 databaseUser.setFirstName( daoUtil.getString( 4 ) );
262 databaseUser.setEmail( daoUtil.getString( 5 ) );
263 databaseUser.setAuthentificationType( daoUtil.getString( 6 ) );
264
265 listDatabaseUsers.add( databaseUser );
266 }
267
268 daoUtil.free( );
269
270 return listDatabaseUsers;
271 }
272
273
274
275
276
277
278
279 public Collection<OpenIdDatabaseUser> selectDatabaseUserListForLogin( String strLogin, Plugin plugin )
280 {
281 Collection<OpenIdDatabaseUser> listDatabaseUsers = new ArrayList<OpenIdDatabaseUser>( );
282 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_FOR_LOGIN, plugin );
283 daoUtil.setString( 1, strLogin );
284 daoUtil.executeQuery( );
285
286 while ( daoUtil.next( ) )
287 {
288 OpenIdDatabaseUser databaseUser = new OpenIdDatabaseUser( );
289 databaseUser.setUserId( daoUtil.getInt( 1 ) );
290 databaseUser.setLogin( daoUtil.getString( 2 ) );
291 databaseUser.setLastName( daoUtil.getString( 3 ) );
292 databaseUser.setFirstName( daoUtil.getString( 4 ) );
293 databaseUser.setEmail( daoUtil.getString( 5 ) );
294 databaseUser.setAuthentificationType( daoUtil.getString( 6 ) );
295
296 listDatabaseUsers.add( databaseUser );
297 }
298
299 daoUtil.free( );
300
301 return listDatabaseUsers;
302 }
303
304
305
306
307
308
309
310
311
312 public boolean checkPassword( String strLogin, String strPassword, Plugin plugin )
313 {
314 int nCount = 0;
315 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CHECK_PASSWORD_FOR_USER_ID, plugin );
316 daoUtil.setString( 1, strLogin );
317 daoUtil.setString( 2, strPassword );
318 daoUtil.executeQuery( );
319
320 if ( daoUtil.next( ) )
321 {
322 nCount = daoUtil.getInt( 1 );
323 }
324
325 daoUtil.free( );
326
327 return ( nCount == 1 ) ? true : false;
328 }
329
330
331
332
333
334
335
336
337 public boolean checkUserLogin( String strLogin, Plugin plugin )
338 {
339 int nCount = 0;
340 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CHECK_LOGIN_FOR_USER_ID, plugin );
341 daoUtil.setString( 1, strLogin );
342 daoUtil.executeQuery( );
343
344 if ( daoUtil.next( ) )
345 {
346 nCount = daoUtil.getInt( 1 );
347 }
348
349 daoUtil.free( );
350
351 return ( nCount == 1 ) ? true : false;
352 }
353
354 public OpenIdDatabaseUser selectDatabaseUserByEmail( String strEmail, Plugin plugin )
355 {
356 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_USER_FOR_EMAIL, plugin );
357 daoUtil.setString( 1, strEmail );
358 daoUtil.executeQuery( );
359
360 OpenIdDatabaseUser databaseUser = null;
361
362 if ( daoUtil.next( ) )
363 {
364 databaseUser = new OpenIdDatabaseUser( );
365 databaseUser.setUserId( daoUtil.getInt( 1 ) );
366 databaseUser.setLogin( daoUtil.getString( 2 ) );
367 databaseUser.setLastName( daoUtil.getString( 3 ) );
368 databaseUser.setFirstName( daoUtil.getString( 4 ) );
369 databaseUser.setEmail( daoUtil.getString( 5 ) );
370 databaseUser.setAuthentificationType( daoUtil.getString( 6 ) );
371 }
372
373 daoUtil.free( );
374
375 return databaseUser;
376 }
377 }