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.ldapdatabase.authentication.business;
35
36 import fr.paris.lutece.plugins.mylutece.modules.ldapdatabase.authentication.LDAPDatabaseUser;
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 LdapDatabaseDAO implements ILdapDatabaseDAO
51 {
52 public static final String SQL_QUERY_FIND_USER_BY_GUID = "SELECT mylutece_ldapdatabase_user_id, ldap_guid, name_family, name_given, email" +
53 " FROM mylutece_ldapdatabase_user WHERE ldap_guid like ? ";
54 public static final String SQL_QUERY_FIND_ALL_USERS = "SELECT mylutece_ldapdatabase_user_id, ldap_guid, name_family, name_given, email" +
55 " FROM mylutece_ldapdatabase_user ";
56 public static final String SQL_QUERY_FIND_ROLES_FROM_GUID = "SELECT b.role_key FROM mylutece_ldapdatabase_user a, mylutece_ldapdatabase_user_role b" +
57 " WHERE a.mylutece_ldapdatabase_user_id = b.mylutece_ldapdatabase_user_id AND a.ldap_guid like ? ";
58 public static final String SQL_QUERY_FIND_GUID_FROM_ROLE = "SELECT a.ldap_guid FROM mylutece_ldapdatabase_user a, mylutece_ldapdatabase_user_role b" +
59 " WHERE a.mylutece_ldapdatabase_user_id = b.mylutece_ldapdatabase_user_id AND b.role_key = ? ";
60 private static final String SQL_QUERY_DELETE_ROLES_FOR_USER = "DELETE FROM mylutece_ldapdatabase_user_role WHERE mylutece_ldapdatabase_user_id = ?";
61 private static final String SQL_QUERY_INSERT_ROLE_FOR_USER = "INSERT INTO mylutece_ldapdatabase_user_role ( mylutece_ldapdatabase_user_id, role_key ) VALUES ( ?, ? ) ";
62
63
64 private static LdapDatabaseDAO _dao = new LdapDatabaseDAO( );
65
66
67
68
69
70
71 static LdapDatabaseDAO getInstance( )
72 {
73 return _dao;
74 }
75
76
77
78
79
80
81
82
83
84 public LDAPDatabaseUser selectLuteceUserByGuid( String strLdapGuid, Plugin plugin,
85 LuteceAuthentication authenticationService )
86 {
87 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_USER_BY_GUID, plugin );
88 daoUtil.setString( 1, strLdapGuid );
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
102 LDAPDatabaseUser user = new LDAPDatabaseUser( strLdapGuid, authenticationService );
103 user.setUserInfo( LuteceUser.NAME_FAMILY, strLastName );
104 user.setUserInfo( LuteceUser.NAME_GIVEN, strFirstName );
105 user.setUserInfo( LuteceUser.BUSINESS_INFO_ONLINE_EMAIL, strEmail );
106 daoUtil.free( );
107
108 return user;
109 }
110
111
112
113
114
115
116
117
118 public ArrayList<String> selectUserRolesFromGuid( String strGuid, Plugin plugin )
119 {
120 ArrayList<String> arrayRoles = new ArrayList<String>( );
121 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_ROLES_FROM_GUID, plugin );
122 daoUtil.setString( 1, strGuid );
123 daoUtil.executeQuery( );
124
125 while ( daoUtil.next( ) )
126 {
127 arrayRoles.add( daoUtil.getString( 1 ) );
128 }
129
130 daoUtil.free( );
131
132 return arrayRoles;
133 }
134
135
136
137
138
139
140
141
142 public ArrayList<String> selectUserGuidFromRole( String strRoleKey, Plugin plugin )
143 {
144 ArrayList<String> arrayGuid = new ArrayList<String>( );
145 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_GUID_FROM_ROLE, plugin );
146 daoUtil.setString( 1, strRoleKey );
147 daoUtil.executeQuery( );
148
149 while ( daoUtil.next( ) )
150 {
151 arrayGuid.add( daoUtil.getString( 1 ) );
152 }
153
154 daoUtil.free( );
155
156 return arrayGuid;
157 }
158
159
160
161
162
163
164 public void deleteRolesForUser( int nIdUser, Plugin plugin )
165 {
166 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ROLES_FOR_USER, plugin );
167 daoUtil.setInt( 1, nIdUser );
168
169 daoUtil.executeUpdate( );
170 daoUtil.free( );
171 }
172
173
174
175
176
177
178
179 public void createRoleForUser( int nIdUser, String strRoleKey, Plugin plugin )
180 {
181 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_ROLE_FOR_USER, plugin );
182 daoUtil.setInt( 1, nIdUser );
183 daoUtil.setString( 2, strRoleKey );
184
185 daoUtil.executeUpdate( );
186 daoUtil.free( );
187 }
188
189
190
191
192
193
194
195 public Collection<LDAPDatabaseUser> selectUserList( Plugin plugin, LuteceAuthentication authenticationService )
196 {
197 Collection<LDAPDatabaseUser> listLdapDatabaseUsers = new ArrayList<LDAPDatabaseUser>( );
198 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_ALL_USERS, plugin );
199 daoUtil.executeQuery( );
200
201 while ( daoUtil.next( ) )
202 {
203 String strLastName = daoUtil.getString( 3 );
204 String strFirstName = daoUtil.getString( 4 );
205 String strEmail = daoUtil.getString( 5 );
206
207 LDAPDatabaseUser user = new LDAPDatabaseUser( daoUtil.getString( 2 ), authenticationService );
208 user.setUserInfo( LuteceUser.NAME_FAMILY, strLastName );
209 user.setUserInfo( LuteceUser.NAME_GIVEN, strFirstName );
210 user.setUserInfo( LuteceUser.BUSINESS_INFO_ONLINE_EMAIL, strEmail );
211
212 listLdapDatabaseUsers.add( user );
213 }
214
215 daoUtil.free( );
216
217 return listLdapDatabaseUsers;
218 }
219 }