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.adminwall.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
46 public final class PostDAO implements IPostDAO
47 {
48
49 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_post ) FROM adminwall_post";
50 private static final String SQL_QUERY_SELECT = "SELECT id_post, contenu, date, id_auteur, auteur FROM adminwall_post WHERE id_post = ?";
51 private static final String SQL_QUERY_INSERT = "INSERT INTO adminwall_post ( id_post, contenu, date, id_auteur, auteur ) VALUES ( ?, ?, ?, ? ,?) ";
52 private static final String SQL_QUERY_DELETE = "DELETE FROM adminwall_post WHERE id_post = ? ";
53 private static final String SQL_QUERY_UPDATE = "UPDATE adminwall_post SET id_post = ?, contenu = ?, date = ?, id_auteur= ?, auteur = ? WHERE id_post = ?";
54 private static final String SQL_QUERY_SELECTALL = "SELECT id_post, contenu, date, id_auteur, auteur FROM adminwall_post ORDER BY id_post DESC";
55 private static final String SQL_QUERY_SELECT_ID_AUTEUR = "SELECT id_post, contenu, date, id_auteur, auteur FROM adminwall_post WHERE id_auteur = ? ORDER BY id_post DESC";
56
57
58
59
60
61
62 public int newPrimaryKey( Plugin plugin )
63 {
64 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
65 daoUtil.executeQuery( );
66
67 int nKey = 1;
68
69 if ( daoUtil.next( ) )
70 {
71 nKey = daoUtil.getInt( 1 ) + 1;
72 }
73
74 daoUtil.free( );
75
76 return nKey;
77 }
78
79
80
81
82 @Override
83 public void insert( Post post, Plugin plugin )
84 {
85 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
86
87 post.setIdPost( newPrimaryKey( plugin ) );
88
89 daoUtil.setInt( 1, post.getIdPost( ) );
90 daoUtil.setString( 2, post.getContenu( ) );
91 daoUtil.setTimestamp( 3, post.getTimestamp( ) );
92 daoUtil.setInt( 4, post.getIdAuteur( ) );
93 daoUtil.setString( 5, post.getAuteur( ) );
94
95 daoUtil.executeUpdate( );
96 daoUtil.free( );
97 }
98
99
100
101
102 @Override
103 public Post load( int nKey, Plugin plugin )
104 {
105 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
106 daoUtil.setInt( 1, nKey );
107 daoUtil.executeQuery( );
108
109 Post post = null;
110
111 if ( daoUtil.next( ) )
112 {
113 post = new Post( );
114 post.setIdPost( daoUtil.getInt( 1 ) );
115 post.setContenu( daoUtil.getString( 2 ) );
116 post.setTimestamp( daoUtil.getTimestamp( 3 ) );
117 post.setIdAuteur( daoUtil.getInt( 4 ) );
118 post.setAuteur( daoUtil.getString( 5 ) );
119 }
120
121 daoUtil.free( );
122
123 return post;
124 }
125
126
127
128
129 @Override
130 public void delete( int nPostId, Plugin plugin )
131 {
132 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
133 daoUtil.setInt( 1, nPostId );
134 daoUtil.executeUpdate( );
135 daoUtil.free( );
136 }
137
138
139
140
141 @Override
142 public void store( Post post, Plugin plugin )
143 {
144 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
145
146 daoUtil.setInt( 1, post.getIdPost( ) );
147 daoUtil.setString( 2, post.getContenu( ) );
148 daoUtil.setTimestamp( 3, post.getTimestamp( ) );
149 daoUtil.setString( 4, post.getAuteur( ) );
150 daoUtil.setInt( 5, post.getIdAuteur( ) );
151 daoUtil.setInt( 6, post.getIdPost( ) );
152
153 daoUtil.executeUpdate( );
154 daoUtil.free( );
155 }
156
157
158
159
160 @Override
161 public Collection<Post> selectPostsList( Plugin plugin )
162 {
163 Collection<Post> postList = new ArrayList<Post>( );
164 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
165 daoUtil.executeQuery( );
166
167 while ( daoUtil.next( ) )
168 {
169 Post post = new Post( );
170
171 post.setIdPost( daoUtil.getInt( 1 ) );
172 post.setContenu( daoUtil.getString( 2 ) );
173 post.setTimestamp( daoUtil.getTimestamp( 3 ) );
174 post.setIdAuteur( daoUtil.getInt( 4 ) );
175 post.setAuteur( daoUtil.getString( 5 ) );
176
177 postList.add( post );
178 }
179
180 daoUtil.free( );
181
182 return postList;
183 }
184
185
186
187
188 @Override
189 public Collection<Post> selectPostsListIdAuteur( int nIdAuteur, Plugin plugin )
190 {
191 Collection<Post> postList = new ArrayList<Post>( );
192 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ID_AUTEUR, plugin );
193 daoUtil.setInt( 1, nIdAuteur );
194
195 daoUtil.executeQuery( );
196
197 while ( daoUtil.next( ) )
198 {
199 Post post = new Post( );
200
201 post.setIdPost( daoUtil.getInt( 1 ) );
202 post.setContenu( daoUtil.getString( 2 ) );
203 post.setTimestamp( daoUtil.getTimestamp( 3 ) );
204 post.setIdAuteur( daoUtil.getInt( 4 ) );
205 post.setAuteur( daoUtil.getString( 5 ) );
206
207 postList.add( post );
208 }
209
210 daoUtil.free( );
211
212 return postList;
213 }
214 }