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