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.plugins.mylutece.modules.openiddatabase.authentication.BaseUser;
37 import fr.paris.lutece.portal.service.plugin.Plugin;
38 import fr.paris.lutece.portal.service.security.LuteceAuthentication;
39 import fr.paris.lutece.portal.service.security.LuteceUser;
40 import fr.paris.lutece.util.sql.DAOUtil;
41
42 import java.util.ArrayList;
43 import java.util.Collection;
44
45
46
47
48
49
50 public class OpenIdDatabaseDAO implements IManageUsers
51 {
52 private static final String SQL_QUERY_FIND_USER_BY_LOGIN = "SELECT mylutece_database_openid_user_id, login, name_family, name_given, email, authen_type" +
53 " FROM mylutece_database_openid_user WHERE login like ? ";
54 private static final String SQL_QUERY_FIND_ROLES_FROM_LOGIN = "SELECT b.role_key FROM mylutece_database_openid_user a, mylutece_database_openid_user_role b" +
55 " WHERE a.mylutece_database_openid_user_id = b.mylutece_database_openid_user_id AND a.login like ? ";
56 private static final String SQL_QUERY_DELETE_ROLES_FOR_USER = "DELETE FROM mylutece_database_openid_user_role WHERE mylutece_database_openid_user_id = ?";
57 private static final String SQL_QUERY_INSERT_ROLE_FOR_USER = "INSERT INTO mylutece_database_openid_user_role ( mylutece_database_openid_user_id, role_key ) VALUES ( ?, ? ) ";
58 private static final String SQL_QUERY_FIND_GROUPS_FROM_LOGIN = "SELECT b.group_key FROM mylutece_database_openid_user a, mylutece_database_openid_user_group b" +
59 " WHERE a.mylutece_database_openid_user_id = b.mylutece_database_openid_user_id AND a.login like ? ";
60 private static final String SQL_QUERY_DELETE_GROUPS_FOR_USER = "DELETE FROM mylutece_database_openid_user_group WHERE mylutece_database_openid_user_id = ?";
61 private static final String SQL_QUERY_INSERT_GROUP_FOR_USER = "INSERT INTO mylutece_database_openid_user_group ( mylutece_database_openid_user_id, group_key ) VALUES ( ?, ? ) ";
62 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 ";
63
64
65 private static OpenIdDatabaseDAO _dao = new OpenIdDatabaseDAO( );
66
67
68
69
70
71
72 static OpenIdDatabaseDAO getInstance( )
73 {
74 return _dao;
75 }
76
77
78
79
80
81
82
83
84
85 public BaseUser selectLuteceUserByLogin( String strLogin, Plugin plugin, LuteceAuthentication authenticationService )
86 {
87 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_USER_BY_LOGIN, plugin );
88 daoUtil.setString( 1, strLogin );
89 daoUtil.executeQuery( );
90
91 if ( !daoUtil.next( ) )
92 {
93 daoUtil.free( );
94
95 return null;
96 }
97
98 String strLastName = daoUtil.getString( 3 );
99 String strFirstName = daoUtil.getString( 4 );
100 String strEmail = daoUtil.getString( 5 );
101 String strAuthentificationType = daoUtil.getString( 6 );
102
103 BaseUser user = new BaseUser( strLogin, authenticationService );
104
105 user.setUserInfo( LuteceUser.NAME_FAMILY, strLastName );
106 user.setUserInfo( LuteceUser.NAME_GIVEN, strFirstName );
107 user.setUserInfo( LuteceUser.BUSINESS_INFO_ONLINE_EMAIL, strEmail );
108 user.setUserInfo( BaseUser.AUTHENTICATION_TYPE, strAuthentificationType );
109 daoUtil.free( );
110
111 return user;
112 }
113
114
115
116
117
118
119 public Collection<BaseUser> selectLuteceUserList( Plugin plugin, LuteceAuthentication authenticationService )
120 {
121 Collection<BaseUser> listBaseUsers = new ArrayList<BaseUser>( );
122 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
123 daoUtil.executeQuery( );
124
125 while ( daoUtil.next( ) )
126 {
127 BaseUser user = new BaseUser( daoUtil.getString( 2 ), authenticationService );
128 user.setUserInfo( LuteceUser.NAME_FAMILY, daoUtil.getString( 3 ) );
129 user.setUserInfo( LuteceUser.NAME_GIVEN, daoUtil.getString( 4 ) );
130 user.setUserInfo( LuteceUser.BUSINESS_INFO_ONLINE_EMAIL, daoUtil.getString( 5 ) );
131 user.setUserInfo( BaseUser.AUTHENTICATION_TYPE, daoUtil.getString( 6 ) );
132 listBaseUsers.add( user );
133 }
134
135 daoUtil.free( );
136
137 return listBaseUsers;
138 }
139
140
141
142
143
144
145
146
147 public ArrayList<String> selectUserRolesFromLogin( String strLogin, Plugin plugin )
148 {
149 ArrayList<String> arrayRoles = new ArrayList<String>( );
150 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_ROLES_FROM_LOGIN, plugin );
151 daoUtil.setString( 1, strLogin );
152 daoUtil.executeQuery( );
153
154 while ( daoUtil.next( ) )
155 {
156 arrayRoles.add( daoUtil.getString( 1 ) );
157 }
158
159 daoUtil.free( );
160
161 return arrayRoles;
162 }
163
164
165
166
167
168
169 public void deleteRolesForUser( int nIdUser, Plugin plugin )
170 {
171 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ROLES_FOR_USER, plugin );
172 daoUtil.setInt( 1, nIdUser );
173
174 daoUtil.executeUpdate( );
175 daoUtil.free( );
176 }
177
178
179
180
181
182
183
184 public void createRoleForUser( int nIdUser, String strRoleKey, Plugin plugin )
185 {
186 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_ROLE_FOR_USER, plugin );
187 daoUtil.setInt( 1, nIdUser );
188 daoUtil.setString( 2, strRoleKey );
189
190 daoUtil.executeUpdate( );
191 daoUtil.free( );
192 }
193
194
195
196
197
198
199
200
201 public ArrayList<String> selectUserGroupsFromLogin( String strLogin, Plugin plugin )
202 {
203 ArrayList<String> arrayGroups = new ArrayList<String>( );
204 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_GROUPS_FROM_LOGIN, plugin );
205 daoUtil.setString( 1, strLogin );
206 daoUtil.executeQuery( );
207
208 while ( daoUtil.next( ) )
209 {
210 arrayGroups.add( daoUtil.getString( 1 ) );
211 }
212
213 daoUtil.free( );
214
215 return arrayGroups;
216 }
217
218
219
220
221
222
223 public void deleteGroupsForUser( int nIdUser, Plugin plugin )
224 {
225 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_GROUPS_FOR_USER, plugin );
226 daoUtil.setInt( 1, nIdUser );
227
228 daoUtil.executeUpdate( );
229 daoUtil.free( );
230 }
231
232
233
234
235
236
237
238 public void createGroupForUser( int nIdUser, String strGroupKey, Plugin plugin )
239 {
240 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_GROUP_FOR_USER, plugin );
241 daoUtil.setInt( 1, nIdUser );
242 daoUtil.setString( 2, strGroupKey );
243
244 daoUtil.executeUpdate( );
245 daoUtil.free( );
246 }
247 }