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.digglike.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.List;
41
42
43
44
45
46
47
48 public class CategoryDAO implements ICategoryDAO
49 {
50 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_category) FROM digglike_category";
51 private static final String SQL_QUERY_FIND_BY_PRIMARY_KEY = "SELECT id_category,title,color FROM digglike_category WHERE id_category=?";
52 private static final String SQL_QUERY_SELECT = "SELECT id_category,title,color FROM digglike_category ORDER BY title";
53 private static final String SQL_QUERY_INSERT = "INSERT INTO digglike_category (id_category,title,color )VALUES(?,?,?)";
54 private static final String SQL_QUERY_UPDATE = "UPDATE digglike_category SET id_category=?,title=?,color=? WHERE id_category=?";
55 private static final String SQL_QUERY_DELETE = "DELETE FROM digglike_category WHERE id_category = ? ";
56 private static final String SQL_QUERY_COUNT_NUMBER_OF_DIGG_ASSOCIATE_TO_THE_CATEGORY = "select COUNT(id_category) " +
57 " FROM digglike_digg_category WHERE id_category=? ";
58 private static final String SQL_QUERY_SELECT_BY_ID_DIGG = "SELECT c.id_category,c.title,c.color FROM digglike_category c,digglike_digg_category dc " +
59 "WHERE c.id_category=dc.id_category AND dc.id_digg=? ORDER BY title";
60 private static final String SQL_QUERY_DELETE_ASSOCIATED_CATEGORIE = "DELETE FROM digglike_digg_category WHERE id_digg = ? and id_category= ? ";
61 private static final String SQL_QUERY_INSERT_ASSOCIATED_CATEGORY = "INSERT INTO digglike_digg_category(id_digg,id_category) VALUES(?,?) ";
62
63
64
65
66
67
68
69 public int newPrimaryKey( Plugin plugin )
70 {
71 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
72 daoUtil.executeQuery( );
73
74 int nKey;
75
76 if ( !daoUtil.next( ) )
77 {
78
79 nKey = 1;
80 }
81
82 nKey = daoUtil.getInt( 1 ) + 1;
83 daoUtil.free( );
84
85 return nKey;
86 }
87
88
89
90
91
92
93
94 public void insert( Category category, Plugin plugin )
95 {
96 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
97 category.setIdCategory( newPrimaryKey( plugin ) );
98 daoUtil.setInt( 1, category.getIdCategory( ) );
99 daoUtil.setString( 2, category.getTitle( ) );
100 daoUtil.setString( 3, category.getColor( ) );
101 daoUtil.executeUpdate( );
102 daoUtil.free( );
103 }
104
105
106
107
108
109
110
111 public void store( Category category, Plugin plugin )
112 {
113 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
114
115 daoUtil.setInt( 1, category.getIdCategory( ) );
116 daoUtil.setString( 2, category.getTitle( ) );
117 daoUtil.setString( 3, category.getColor( ) );
118 daoUtil.setInt( 4, category.getIdCategory( ) );
119 daoUtil.executeUpdate( );
120 daoUtil.free( );
121 }
122
123
124
125
126
127
128
129
130 public Category load( int idKey, Plugin plugin )
131 {
132 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_PRIMARY_KEY, plugin );
133 daoUtil.setInt( 1, idKey );
134 daoUtil.executeQuery( );
135
136 Category category = null;
137
138 if ( daoUtil.next( ) )
139 {
140 category = new Category( );
141 category.setIdCategory( daoUtil.getInt( 1 ) );
142 category.setTitle( daoUtil.getString( 2 ) );
143 category.setColor( daoUtil.getString( 3 ) );
144 }
145
146 daoUtil.free( );
147
148 return category;
149 }
150
151
152
153
154
155
156 public List<Category> select( Plugin plugin )
157 {
158 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
159 daoUtil.executeQuery( );
160
161 Category category = null;
162 List<Category> listCategory = new ArrayList<Category>( );
163
164 while ( daoUtil.next( ) )
165 {
166 category = new Category( );
167 category.setIdCategory( daoUtil.getInt( 1 ) );
168 category.setTitle( daoUtil.getString( 2 ) );
169 category.setColor( daoUtil.getString( 3 ) );
170 listCategory.add( category );
171 }
172
173 daoUtil.free( );
174
175 return listCategory;
176 }
177
178
179
180
181
182
183
184 public void delete( int nIdCategory, Plugin plugin )
185 {
186 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
187 daoUtil.setInt( 1, nIdCategory );
188 daoUtil.executeUpdate( );
189 daoUtil.free( );
190 }
191
192
193
194
195
196
197
198 public boolean isAssociateToDigg( int nIdCategory, Plugin plugin )
199 {
200 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_COUNT_NUMBER_OF_DIGG_ASSOCIATE_TO_THE_CATEGORY, plugin );
201 daoUtil.setInt( 1, nIdCategory );
202 daoUtil.executeQuery( );
203
204 if ( daoUtil.next( ) )
205 {
206 if ( daoUtil.getInt( 1 ) != 0 )
207 {
208 daoUtil.free( );
209
210 return true;
211 }
212 }
213
214 daoUtil.free( );
215
216 return false;
217 }
218
219 @Override
220 public List<Category> select( int nIdDigg, Plugin plugin )
221 {
222 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_ID_DIGG, plugin );
223 daoUtil.setInt( 1, nIdDigg );
224 daoUtil.executeQuery( );
225
226 Category category = null;
227 List<Category> listCategory = new ArrayList<Category>( );
228
229 while ( daoUtil.next( ) )
230 {
231 category = new Category( );
232 category.setIdCategory( daoUtil.getInt( 1 ) );
233 category.setTitle( daoUtil.getString( 2 ) );
234 category.setColor( daoUtil.getString( 3 ) );
235 listCategory.add( category );
236 }
237
238 daoUtil.free( );
239
240 return listCategory;
241 }
242
243
244
245
246
247
248
249
250 public void deleteDiggAssociation( int nIdDigg, int nIdCategory, Plugin plugin )
251 {
252 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ASSOCIATED_CATEGORIE, plugin );
253 daoUtil.setInt( 1, nIdDigg );
254 daoUtil.setInt( 2, nIdCategory );
255
256 daoUtil.executeUpdate( );
257 daoUtil.free( );
258 }
259
260
261
262
263
264
265
266
267 public void insertDiggAssociation( int nIdDigg, int nIdCategory, Plugin plugin )
268 {
269 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_ASSOCIATED_CATEGORY, plugin );
270 daoUtil.setInt( 1, nIdDigg );
271 daoUtil.setInt( 2, nIdCategory );
272 daoUtil.executeUpdate( );
273 daoUtil.free( );
274 }
275 }