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.blog.business;
35
36 import java.util.ArrayList;
37 import java.util.List;
38
39 import fr.paris.lutece.portal.service.plugin.Plugin;
40 import fr.paris.lutece.util.ReferenceList;
41 import fr.paris.lutece.util.sql.DAOUtil;
42
43
44
45
46 public final class TagDAO implements ITagDAO
47 {
48
49
50 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_tag ) FROM blog_tag";
51 private static final String SQL_QUERY_INSERT_TAG = "INSERT INTO blog_tag ( id_tag, name ) VALUES ( ? , ? )";
52 private static final String SQL_QUERY_SELECT_TAG = "SELECT id_tag, name FROM blog_tag WHERE id_tag = ? ";
53 private static final String SQL_QUERY_SELECT_ALL_TAG = "SELECT id_tag, name FROM blog_tag ";
54 private static final String SQL_QUERY_SELECT_TAG_BY_NAME = "SELECT id_tag, name FROM blog_tag WHERE name = ? ";
55
56 private static final String SQL_QUERY_DELETE = "DELETE FROM blog_tag WHERE id_tag = ? ";
57 private static final String SQL_QUERY_UPDATE = "UPDATE blog_tag SET id_tag = ?, name = ? WHERE id_tag = ?";
58
59 private static final String SQL_QUERY_INSERT_TAG_DOC = "INSERT INTO blog_tag_document ( id_tag, id_blog, priority ) VALUES ( ? , ?, ? )";
60 private static final String SQL_QUERY_SELECT_TAG_DOC = " SELECT a.id_tag, t.name, a.priority FROM blog_tag_document a Inner join blog_tag t on (t.id_tag= a.id_tag) WHERE a.id_blog = ? ORDER BY priority";
61 private static final String SQL_QUERY_DELETE_BY_TAG = "DELETE FROM blog_tag_document WHERE id_tag = ? AND id_blog = ?";
62 private static final String SQL_QUERY_DELETE_BY_DOC = "DELETE FROM blog_tag_document WHERE id_blog = ? ";
63
64
65
66
67
68
69
70
71 public int newPrimaryKey( Plugin plugin )
72 {
73 int nKey = 1;
74 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin ) )
75 {
76 daoUtil.executeQuery( );
77
78 if ( daoUtil.next( ) )
79 {
80 nKey = daoUtil.getInt( 1 ) + 1;
81 }
82 }
83 return nKey;
84 }
85
86
87
88
89 @Override
90 public void insert( Tag tag, Plugin plugin )
91 {
92 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_TAG, plugin ) )
93 {
94 tag.setIdTag( newPrimaryKey( plugin ) );
95
96 daoUtil.setInt( 1, tag.getIdTag( ) );
97 daoUtil.setString( 2, tag.getName( ) );
98
99 daoUtil.executeUpdate( );
100 }
101 }
102
103
104
105
106 @Override
107 public Tag load( int idTag, Plugin plugin )
108 {
109 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_TAG, plugin ) )
110 {
111 daoUtil.setInt( 1, idTag );
112 daoUtil.executeQuery( );
113
114 if ( daoUtil.next( ) )
115 {
116 Tagplugins/blog/business/Tag.html#Tag">Tag tag = new Tag( );
117
118 tag.setIdTag( daoUtil.getInt( 1 ) );
119 tag.setName( daoUtil.getString( 2 ) );
120
121 daoUtil.free( );
122
123 return tag;
124 }
125 }
126 return null;
127 }
128
129
130
131
132 @Override
133 public List<Tag> loadAllTag( Plugin plugin )
134 {
135 List<Tag> listTag = new ArrayList<>( );
136 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_TAG, plugin ) )
137 {
138 daoUtil.executeQuery( );
139
140 while ( daoUtil.next( ) )
141 {
142 Tagplugins/blog/business/Tag.html#Tag">Tag tag = new Tag( );
143
144 tag.setIdTag( daoUtil.getInt( 1 ) );
145 tag.setName( daoUtil.getString( 2 ) );
146
147 listTag.add( tag );
148
149 }
150 }
151
152 return listTag;
153 }
154
155
156
157
158 @Override
159 public void delete( int idTag, Plugin plugin )
160 {
161 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
162 {
163 daoUtil.setInt( 1, idTag );
164 daoUtil.executeUpdate( );
165 }
166 }
167
168
169
170
171 @Override
172 public void store( Tag tag, Plugin plugin )
173 {
174 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
175 {
176
177 daoUtil.setInt( 1, tag.getIdTag( ) );
178 daoUtil.setString( 2, tag.getName( ) );
179 daoUtil.setInt( 3, tag.getIdTag( ) );
180
181 daoUtil.executeUpdate( );
182 }
183 }
184
185
186
187
188 @Override
189 public void insert( int nIdTag, int nIdDoc, int nPriority, Plugin plugin )
190 {
191 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_TAG_DOC, plugin ) )
192 {
193 daoUtil.setInt( 1, nIdTag );
194 daoUtil.setInt( 2, nIdDoc );
195 daoUtil.setInt( 3, nPriority );
196
197 daoUtil.executeUpdate( );
198 }
199 }
200
201
202
203
204 @Override
205 public void deleteByTAG( int idTag, int idDoc, Plugin plugin )
206 {
207 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_TAG, plugin ) )
208 {
209 daoUtil.setInt( 1, idTag );
210 daoUtil.setInt( 2, idDoc );
211
212 daoUtil.executeUpdate( );
213 }
214
215 }
216
217
218
219
220 @Override
221 public void deleteByDoc( int idDoc, Plugin plugin )
222 {
223 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_DOC, plugin ) )
224 {
225 daoUtil.setInt( 1, idDoc );
226 daoUtil.executeUpdate( );
227 }
228 }
229
230
231
232
233 @Override
234 public List<Tag> loadByDoc( int idDoc, Plugin plugin )
235 {
236 List<Tag> list = new ArrayList<>( );
237 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_TAG_DOC, plugin ) )
238 {
239 daoUtil.setInt( 1, idDoc );
240 daoUtil.executeQuery( );
241
242 while ( daoUtil.next( ) )
243 {
244 Tagplugins/blog/business/Tag.html#Tag">Tag tag = new Tag( );
245 tag.setIdTag( daoUtil.getInt( 1 ) );
246 tag.setName( daoUtil.getString( 2 ) );
247 tag.setPriority( daoUtil.getInt( 3 ) );
248 list.add( tag );
249 }
250 }
251
252 return list;
253 }
254
255
256
257
258 @Override
259 public ReferenceList selectTagsReferenceList( Plugin plugin )
260 {
261 ReferenceList blogList = new ReferenceList( );
262 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_TAG, plugin ) )
263 {
264 daoUtil.executeQuery( );
265
266 while ( daoUtil.next( ) )
267 {
268 blogList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
269 }
270 }
271 return blogList;
272 }
273
274
275
276
277 @Override
278 public List<Tag> loadListTagByIdDoc( int idDoc, Plugin plugin )
279 {
280 List<Tag> listTag = new ArrayList<>( );
281 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_TAG_DOC, plugin ) )
282 {
283 daoUtil.setInt( 1, idDoc );
284 daoUtil.executeQuery( );
285
286 while ( daoUtil.next( ) )
287 {
288 Tagplugins/blog/business/Tag.html#Tag">Tag tag = new Tag( );
289 tag.setIdTag( daoUtil.getInt( 1 ) );
290 tag.setName( daoUtil.getString( 2 ) );
291 tag.setPriority( daoUtil.getInt( 3 ) );
292
293 listTag.add( tag );
294
295 }
296 }
297
298 return listTag;
299 }
300
301
302
303
304 @Override
305 public Tag loadByName( String strName, Plugin plugin )
306 {
307 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_TAG_BY_NAME, plugin ) )
308 {
309 daoUtil.setString( 1, strName );
310 daoUtil.executeQuery( );
311
312 if ( daoUtil.next( ) )
313 {
314 Tagplugins/blog/business/Tag.html#Tag">Tag tag = new Tag( );
315
316 tag.setIdTag( daoUtil.getInt( 1 ) );
317 tag.setName( daoUtil.getString( 2 ) );
318
319 daoUtil.free( );
320
321 return tag;
322 }
323 }
324 return null;
325 }
326 }