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.avatarserver.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 public final class AvatarDAO implements IAvatarDAO
46 {
47
48 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_avatar ) FROM avatarserver_avatar";
49 private static final String SQL_QUERY_SELECT = "SELECT id_avatar, email, mime_type, file_value, hash_email FROM avatarserver_avatar WHERE id_avatar = ?";
50 private static final String SQL_QUERY_SELECT_BY_HASH = "SELECT id_avatar, email, mime_type, file_value, hash_email FROM avatarserver_avatar WHERE hash_email = ?";
51 private static final String SQL_QUERY_INSERT = "INSERT INTO avatarserver_avatar ( id_avatar, email,mime_type,file_value, hash_email ) VALUES ( ?, ?, ?, ?, ? ) ";
52 private static final String SQL_QUERY_DELETE = "DELETE FROM avatarserver_avatar WHERE id_avatar = ? ";
53 private static final String SQL_QUERY_UPDATE = "UPDATE avatarserver_avatar SET id_avatar = ?, email = ?, mime_type = ?, file_value = ?, hash_email = ? WHERE id_avatar = ?";
54 private static final String SQL_QUERY_SELECTALL = "SELECT id_avatar, email, mime_type, file_value, hash_email FROM avatarserver_avatar";
55
56
57
58
59
60
61
62
63 public int newPrimaryKey( Plugin plugin )
64 {
65 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
66 daoUtil.executeQuery( );
67
68 int nKey = 1;
69
70 if ( daoUtil.next( ) )
71 {
72 nKey = daoUtil.getInt( 1 ) + 1;
73 }
74
75 daoUtil.free( );
76
77 return nKey;
78 }
79
80
81
82
83 @Override
84 public void insert( Avatar avatar, Plugin plugin )
85 {
86 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
87 avatar.setId( newPrimaryKey( plugin ) );
88 daoUtil.setInt( 1, avatar.getId( ) );
89 daoUtil.setString( 2, avatar.getEmail( ) );
90 daoUtil.setString( 3, avatar.getMimeType( ) );
91 daoUtil.setBytes( 4, avatar.getValue( ) );
92 daoUtil.setString( 5, avatar.getHash( ) );
93 daoUtil.executeUpdate( );
94 daoUtil.free( );
95 }
96
97
98
99
100 @Override
101 public Avatar load( int nKey, Plugin plugin )
102 {
103 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
104 daoUtil.setInt( 1, nKey );
105 daoUtil.executeQuery( );
106
107 Avatar avatar = null;
108
109 if ( daoUtil.next( ) )
110 {
111 avatar = new Avatar( );
112 avatar.setId( daoUtil.getInt( 1 ) );
113 avatar.setEmail( daoUtil.getString( 2 ) );
114 avatar.setMimeType( daoUtil.getString( 3 ) );
115 avatar.setValue( daoUtil.getBytes( 4 ) );
116 avatar.setHash( daoUtil.getString( 5 ) );
117 }
118
119 daoUtil.free( );
120
121 return avatar;
122 }
123
124
125
126
127 @Override
128 public void delete( int nAvatarId, Plugin plugin )
129 {
130 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
131 daoUtil.setInt( 1, nAvatarId );
132 daoUtil.executeUpdate( );
133 daoUtil.free( );
134 }
135
136
137
138
139 @Override
140 public void store( Avatar avatar, Plugin plugin )
141 {
142 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
143 daoUtil.setInt( 1, avatar.getId( ) );
144 daoUtil.setString( 2, avatar.getEmail( ) );
145 daoUtil.setString( 3, avatar.getMimeType( ) );
146 daoUtil.setBytes( 4, avatar.getValue( ) );
147 daoUtil.setString( 5, avatar.getHash( ) );
148 daoUtil.setInt( 6, avatar.getId( ) );
149 daoUtil.executeUpdate( );
150 daoUtil.free( );
151 }
152
153
154
155
156 @Override
157 public Collection<Avatar> selectAvatarsList( Plugin plugin )
158 {
159 Collection<Avatar> avatarList = new ArrayList<Avatar>( );
160 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
161 daoUtil.executeQuery( );
162
163 while ( daoUtil.next( ) )
164 {
165 Avatar avatar = new Avatar( );
166
167 avatar.setId( daoUtil.getInt( 1 ) );
168 avatar.setEmail( daoUtil.getString( 2 ) );
169 avatar.setMimeType( daoUtil.getString( 3 ) );
170 avatar.setValue( daoUtil.getBytes( 4 ) );
171 avatar.setHash( daoUtil.getString( 5 ) );
172
173 avatarList.add( avatar );
174 }
175
176 daoUtil.free( );
177
178 return avatarList;
179 }
180
181
182
183
184 @Override
185 public Avatar selectByHash( String strHash, Plugin plugin )
186 {
187 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_HASH, plugin );
188 daoUtil.setString( 1, strHash );
189 daoUtil.executeQuery( );
190
191 Avatar avatar = null;
192
193 if ( daoUtil.next( ) )
194 {
195 avatar = new Avatar( );
196 avatar.setId( daoUtil.getInt( 1 ) );
197 avatar.setEmail( daoUtil.getString( 2 ) );
198 avatar.setMimeType( daoUtil.getString( 3 ) );
199 avatar.setValue( daoUtil.getBytes( 4 ) );
200 avatar.setHash( daoUtil.getString( 5 ) );
201 }
202
203 daoUtil.free( );
204
205 return avatar;
206 }
207 }