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.portal.business.prefs;
35
36 import fr.paris.lutece.util.sql.DAOUtil;
37
38 import java.util.ArrayList;
39 import java.util.List;
40
41
42
43
44
45 public abstract class AbstractUserPreferencesDAO implements IPreferencesDAO
46 {
47 private final String _strSqlSelect = "SELECT pref_value FROM " + getPreferencesTable( ) +
48 " WHERE id_user = ? AND pref_key = ?";
49 private final String _strSqlInsert = "INSERT INTO " + getPreferencesTable( ) +
50 " ( pref_value , id_user, pref_key ) VALUES ( ?, ?, ? ) ";
51 private final String _strSqlUpdate = "UPDATE " + getPreferencesTable( ) +
52 " SET pref_value = ? WHERE id_user = ? AND pref_key = ?";
53 private final String _strSqlDelete = "DELETE FROM " + getPreferencesTable( ) + " WHERE id_user = ? ";
54 private final String _strSqlSelectAll = "SELECT pref_key FROM " + getPreferencesTable( ) + " WHERE id_user = ?";
55 private final String _strSqlSelectByValue = "SELECT id_user FROM " + getPreferencesTable( ) +
56 " WHERE pref_key = ? AND pref_value = ? ";
57 private final String _strSqlDeleteKey = "DELETE FROM " + getPreferencesTable( ) +
58 " WHERE id_user = ? AND pref_key = ? ";
59 private final String _strSqlDeleteKeyPrefix = "DELETE FROM " + getPreferencesTable( ) +
60 " WHERE id_user = ? AND pref_key LIKE ? ";
61 private final String _strSqlSelectCount = "SELECT COUNT(*) FROM " + getPreferencesTable( ) +
62 " WHERE id_user = ? AND pref_key = ?";
63 private final String _strSqlSelectCountPrefValue = "SELECT COUNT(*) FROM " + getPreferencesTable( ) +
64 " WHERE pref_key = ? AND pref_value = ?";
65
66
67
68
69
70
71 abstract String getPreferencesTable( );
72
73
74
75
76 @Override
77 public String load( String strUserId, String strKey, String strDefault )
78 {
79 DAOUtil daoUtil = new DAOUtil( _strSqlSelect );
80 daoUtil.setString( 1, strUserId );
81 daoUtil.setString( 2, strKey );
82 daoUtil.executeQuery( );
83
84 String strValue = strDefault;
85
86 if ( daoUtil.next( ) )
87 {
88 strValue = ( daoUtil.getString( 1 ) );
89 }
90
91 daoUtil.free( );
92
93 return strValue;
94 }
95
96
97
98
99 @Override
100 public List<String> getUserId( String strKey, String strValue )
101 {
102 List<String> listUserId = new ArrayList<String>( );
103 DAOUtil daoUtil = new DAOUtil( _strSqlSelectByValue );
104 daoUtil.setString( 1, strKey );
105 daoUtil.setString( 2, strValue );
106 daoUtil.executeQuery( );
107
108 while ( daoUtil.next( ) )
109 {
110 listUserId.add( daoUtil.getString( 1 ) );
111 }
112
113 daoUtil.free( );
114
115 return listUserId;
116 }
117
118
119
120
121 @Override
122 public void store( String strUserId, String strKey, String strValue )
123 {
124 String strSQL = _strSqlInsert;
125
126 if ( existsKey( strUserId, strKey ) )
127 {
128 strSQL = _strSqlUpdate;
129 }
130
131 DAOUtil daoUtil = new DAOUtil( strSQL );
132
133 daoUtil.setString( 1, strValue );
134 daoUtil.setString( 2, strUserId );
135 daoUtil.setString( 3, strKey );
136
137 daoUtil.executeUpdate( );
138 daoUtil.free( );
139 }
140
141
142
143
144 @Override
145 public List<String> keys( String strUserId )
146 {
147 List<String> list = new ArrayList<String>( );
148 DAOUtil daoUtil = new DAOUtil( _strSqlSelectAll );
149 daoUtil.setString( 1, strUserId );
150 daoUtil.executeQuery( );
151
152 while ( daoUtil.next( ) )
153 {
154 list.add( daoUtil.getString( 1 ) );
155 }
156
157 daoUtil.free( );
158
159 return list;
160 }
161
162
163
164
165 @Override
166 public void remove( String strUserId )
167 {
168 DAOUtil daoUtil = new DAOUtil( _strSqlDelete );
169 daoUtil.setString( 1, strUserId );
170 daoUtil.executeUpdate( );
171 daoUtil.free( );
172 }
173
174
175
176
177 @Override
178 public void removeKey( String strUserId, String strKey )
179 {
180 DAOUtil daoUtil = new DAOUtil( _strSqlDeleteKey );
181 daoUtil.setString( 1, strUserId );
182 daoUtil.setString( 2, strKey );
183 daoUtil.executeUpdate( );
184 daoUtil.free( );
185 }
186
187
188
189
190 @Override
191 public void removeKeyPrefix( String strUserId, String strKeyPrefix )
192 {
193 DAOUtil daoUtil = new DAOUtil( _strSqlDeleteKeyPrefix );
194 daoUtil.setString( 1, strUserId );
195 daoUtil.setString( 2, "%" + strKeyPrefix );
196 daoUtil.executeUpdate( );
197 daoUtil.free( );
198 }
199
200
201
202
203 @Override
204 public boolean existsKey( String strUserId, String strKey )
205 {
206 DAOUtil daoUtil = new DAOUtil( _strSqlSelectCount );
207 daoUtil.setString( 1, strUserId );
208 daoUtil.setString( 2, strKey );
209 daoUtil.executeQuery( );
210
211 int nValue = 0;
212
213 if ( daoUtil.next( ) )
214 {
215 nValue = ( daoUtil.getInt( 1 ) );
216 }
217
218 daoUtil.free( );
219
220 return ( nValue != 0 );
221 }
222
223
224
225
226 @Override
227 public boolean existsValueForKey( String strKey,String strValue )
228 {
229 DAOUtil daoUtil = new DAOUtil( _strSqlSelectCountPrefValue );
230 daoUtil.setString( 1, strKey );
231 daoUtil.setString( 2, strValue );
232 daoUtil.executeQuery( );
233
234 int nValue = 0;
235
236 if ( daoUtil.next( ) )
237 {
238 nValue = ( daoUtil.getInt( 1 ) );
239 }
240
241 daoUtil.free( );
242
243 return ( nValue != 0 );
244 }
245 }