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.appointment.business.user;
35
36 import java.sql.Statement;
37 import java.util.ArrayList;
38 import java.util.List;
39
40 import fr.paris.lutece.portal.service.plugin.Plugin;
41 import fr.paris.lutece.util.sql.DAOUtil;
42
43
44
45
46
47
48
49 public final class UserDAO implements IUserDAO
50 {
51
52 private static final String SQL_QUERY_INSERT = "INSERT INTO appointment_user ( guid, first_name, last_name, email, phone_number) VALUES ( ?, ?, ?, ?, ?)";
53 private static final String SQL_QUERY_UPDATE = "UPDATE appointment_user SET guid = ?, first_name = ?, last_name = ?, email = ?, phone_number = ? WHERE id_user = ?";
54 private static final String SQL_QUERY_DELETE = "DELETE FROM appointment_user WHERE id_user = ?";
55 private static final String SQL_QUERY_SELECT_COLUMNS = "SELECT id_user, guid, first_name, last_name, email, phone_number FROM appointment_user";
56 private static final String SQL_QUERY_SELECT = SQL_QUERY_SELECT_COLUMNS + " WHERE id_user = ?";
57 private static final String SQL_QUERY_SELECT_BY_GUID = SQL_QUERY_SELECT_COLUMNS + " WHERE guid = ?";
58 private static final String SQL_QUERY_SELECT_BY_EMAIL = SQL_QUERY_SELECT_COLUMNS + " WHERE email = ?";
59 private static final String SQL_QUERY_SELECT_BY_FIRSTNAME_LASTNAME_AND_EMAIL = SQL_QUERY_SELECT_COLUMNS
60 + " WHERE UPPER(first_name) = ? and UPPER(last_name) = ? and UPPER(email) = ?";
61
62 @Override
63 public void insert( User user, Plugin plugin )
64 {
65 try ( DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_INSERT, user, plugin, true ) )
66 {
67 daoUtil.executeUpdate( );
68 if ( daoUtil.nextGeneratedKey( ) )
69 {
70 user.setIdUser( daoUtil.getGeneratedKeyInt( 1 ) );
71 }
72 }
73 }
74
75 @Override
76 public void update( User user, Plugin plugin )
77 {
78 try ( DAOUtil daoUtil = buildDaoUtil( SQL_QUERY_UPDATE, user, plugin, false ) )
79 {
80 daoUtil.executeUpdate( );
81 }
82 }
83
84 @Override
85 public void delete( int nIdUser, Plugin plugin )
86 {
87 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
88 {
89 daoUtil.setInt( 1, nIdUser );
90 daoUtil.executeUpdate( );
91 }
92 }
93
94 @Override
95 public User select( int nIdUser, Plugin plugin )
96 {
97 User user = null;
98 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
99 {
100 daoUtil.setInt( 1, nIdUser );
101 daoUtil.executeQuery( );
102 if ( daoUtil.next( ) )
103 {
104 user = buildUser( daoUtil );
105 }
106 }
107 return user;
108 }
109
110 @Override
111 public User selectByGuid( String strGuid, Plugin plugin )
112 {
113 User user = null;
114 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_GUID, plugin ) )
115 {
116 daoUtil.setString( 1, strGuid );
117 daoUtil.executeQuery( );
118 if ( daoUtil.next( ) )
119 {
120 user = buildUser( daoUtil );
121 }
122 }
123 return user;
124 }
125
126 @Override
127 public List<User> findByEmail( String strEmail, Plugin plugin )
128 {
129 List<User> listUsers = new ArrayList<>( );
130 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_EMAIL, plugin ) )
131 {
132 daoUtil.setString( 1, strEmail );
133 daoUtil.executeQuery( );
134 while ( daoUtil.next( ) )
135 {
136 listUsers.add( buildUser( daoUtil ) );
137 }
138 }
139 return listUsers;
140 }
141
142 @Override
143 public User findByFirstNameLastNameAndEmail( String strFirstName, String strLastName, String strEmail, Plugin plugin )
144 {
145 User user = null;
146 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_FIRSTNAME_LASTNAME_AND_EMAIL, plugin ) )
147 {
148 daoUtil.setString( 1, strFirstName.toUpperCase( ) );
149 daoUtil.setString( 2, strLastName.toUpperCase( ) );
150 daoUtil.setString( 3, strEmail.toUpperCase( ) );
151 daoUtil.executeQuery( );
152 if ( daoUtil.next( ) )
153 {
154 user = buildUser( daoUtil );
155 }
156 }
157 return user;
158 }
159
160
161
162
163
164
165
166
167 private User buildUser( DAOUtil daoUtil )
168 {
169 int nIndex = 1;
170 User/plugins/appointment/business/user/User.html#User">User user = new User( );
171 user.setIdUser( daoUtil.getInt( nIndex++ ) );
172 user.setGuid( daoUtil.getString( nIndex++ ) );
173 user.setFirstName( daoUtil.getString( nIndex++ ) );
174 user.setLastName( daoUtil.getString( nIndex++ ) );
175 user.setEmail( daoUtil.getString( nIndex++ ) );
176 user.setPhoneNumber( daoUtil.getString( nIndex ) );
177 return user;
178 }
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194 private DAOUtil buildDaoUtil( String query, User user, Plugin plugin, boolean isInsert )
195 {
196 int nIndex = 1;
197 DAOUtil daoUtil = null;
198 if ( isInsert )
199 {
200 daoUtil = new DAOUtil( query, Statement.RETURN_GENERATED_KEYS, plugin );
201 }
202 else
203 {
204 daoUtil = new DAOUtil( query, plugin );
205 }
206 daoUtil.setString( nIndex++, user.getGuid( ) );
207 daoUtil.setString( nIndex++, user.getFirstName( ) );
208 daoUtil.setString( nIndex++, user.getLastName( ) );
209 daoUtil.setString( nIndex++, user.getEmail( ) );
210 daoUtil.setString( nIndex++, user.getPhoneNumber( ) );
211 if ( !isInsert )
212 {
213 daoUtil.setInt( nIndex, user.getIdUser( ) );
214 }
215 return daoUtil;
216 }
217 }