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
35
36 package fr.paris.lutece.plugins.plugin.modules.sample.business;
37
38 import fr.paris.lutece.portal.service.plugin.Plugin;
39 import fr.paris.lutece.util.ReferenceList;
40 import fr.paris.lutece.util.sql.DAOUtil;
41 import java.sql.Statement;
42
43 import java.util.ArrayList;
44 import java.util.List;
45 import java.util.Optional;
46
47
48
49
50 public final class ProjectDAO implements IProjectDAO
51 {
52
53 private static final String SQL_QUERY_SELECT = "SELECT id_project, title, description, range_of_publication_date, date, choice FROM plugin_sample_project_ WHERE id_project = ?";
54 private static final String SQL_QUERY_INSERT = "INSERT INTO plugin_sample_project_ ( title, description, range_of_publication_date, date, choice ) VALUES ( ?, ?, ?, ?, ? ) ";
55 private static final String SQL_QUERY_DELETE = "DELETE FROM plugin_sample_project_ WHERE id_project = ? ";
56 private static final String SQL_QUERY_UPDATE = "UPDATE plugin_sample_project_ SET id_project = ?, title = ?, description = ?, range_of_publication_date = ?, date = ?, choice = ? WHERE id_project = ?";
57 private static final String SQL_QUERY_SELECTALL = "SELECT id_project, title, description, range_of_publication_date, date, choice FROM plugin_sample_project_";
58 private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_project FROM plugin_sample_project_";
59 private static final String SQL_QUERY_SELECTALL_BY_IDS = "SELECT id_project, title, description, range_of_publication_date, date, choice FROM plugin_sample_project_ WHERE id_project IN ( ";
60
61
62
63
64 @Override
65 public void insert( Project project, Plugin plugin )
66 {
67 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin ) )
68 {
69 int nIndex = 1;
70 daoUtil.setString( nIndex++ , project.getTitle( ) );
71 daoUtil.setString( nIndex++ , project.getDescription( ) );
72 daoUtil.setDate( nIndex++ , project.getRangeOfPublicationDate( ) );
73 daoUtil.setString( nIndex++ , project.getDate( ) );
74 daoUtil.setString( nIndex++ , project.getChoice( ) );
75
76 daoUtil.executeUpdate( );
77 if ( daoUtil.nextGeneratedKey( ) )
78 {
79 project.setId( daoUtil.getGeneratedKeyInt( 1 ) );
80 }
81 }
82
83 }
84
85
86
87
88 @Override
89 public Optional<Project> load( int nKey, Plugin plugin )
90 {
91 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
92 {
93 daoUtil.setInt( 1 , nKey );
94 daoUtil.executeQuery( );
95 Project project = null;
96
97 if ( daoUtil.next( ) )
98 {
99 project = new Project();
100 int nIndex = 1;
101
102 project.setId( daoUtil.getInt( nIndex++ ) );
103 project.setTitle( daoUtil.getString( nIndex++ ) );
104 project.setDescription( daoUtil.getString( nIndex++ ) );
105 project.setRangeOfPublicationDate( daoUtil.getDate( nIndex++ ) );
106 project.setDate( daoUtil.getString( nIndex++ ) );
107 project.setChoice( daoUtil.getString( nIndex ) );
108 }
109
110 return Optional.ofNullable( project );
111 }
112 }
113
114
115
116
117 @Override
118 public void delete( int nKey, Plugin plugin )
119 {
120 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
121 {
122 daoUtil.setInt( 1 , nKey );
123 daoUtil.executeUpdate( );
124 }
125 }
126
127
128
129
130 @Override
131 public void store( Project project, Plugin plugin )
132 {
133 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
134 {
135 int nIndex = 1;
136
137 daoUtil.setInt( nIndex++ , project.getId( ) );
138 daoUtil.setString( nIndex++ , project.getTitle( ) );
139 daoUtil.setString( nIndex++ , project.getDescription( ) );
140 daoUtil.setDate( nIndex++ , project.getRangeOfPublicationDate( ) );
141 daoUtil.setString( nIndex++ , project.getDate( ) );
142 daoUtil.setString( nIndex++ , project.getChoice( ) );
143 daoUtil.setInt( nIndex , project.getId( ) );
144
145 daoUtil.executeUpdate( );
146 }
147 }
148
149
150
151
152 @Override
153 public List<Project> selectProjectsList( Plugin plugin )
154 {
155 List<Project> projectList = new ArrayList<>( );
156 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
157 {
158 daoUtil.executeQuery( );
159
160 while ( daoUtil.next( ) )
161 {
162 Projectugins/plugin/modules/sample/business/Project.html#Project">Project project = new Project( );
163 int nIndex = 1;
164
165 project.setId( daoUtil.getInt( nIndex++ ) );
166 project.setTitle( daoUtil.getString( nIndex++ ) );
167 project.setDescription( daoUtil.getString( nIndex++ ) );
168 project.setRangeOfPublicationDate( daoUtil.getDate( nIndex++ ) );
169 project.setDate( daoUtil.getString( nIndex++ ) );
170 project.setChoice( daoUtil.getString( nIndex ) );
171
172 projectList.add( project );
173 }
174
175 return projectList;
176 }
177 }
178
179
180
181
182 @Override
183 public List<Integer> selectIdProjectsList( Plugin plugin )
184 {
185 List<Integer> projectList = new ArrayList<>( );
186 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin ) )
187 {
188 daoUtil.executeQuery( );
189
190 while ( daoUtil.next( ) )
191 {
192 projectList.add( daoUtil.getInt( 1 ) );
193 }
194
195 return projectList;
196 }
197 }
198
199
200
201
202 @Override
203 public ReferenceList selectProjectsReferenceList( Plugin plugin )
204 {
205 ReferenceList projectList = new ReferenceList();
206 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
207 {
208 daoUtil.executeQuery( );
209
210 while ( daoUtil.next( ) )
211 {
212 projectList.addItem( daoUtil.getInt( 1 ) , daoUtil.getString( 2 ) );
213 }
214
215 return projectList;
216 }
217 }
218
219
220
221
222 @Override
223 public List<Project> selectProjectsListByIds( Plugin plugin, List<Integer> listIds ) {
224 List<Project> projectList = new ArrayList<>( );
225
226 StringBuilder builder = new StringBuilder( );
227
228 if ( !listIds.isEmpty( ) )
229 {
230 for( int i = 0 ; i < listIds.size(); i++ ) {
231 builder.append( "?," );
232 }
233
234 String placeHolders = builder.deleteCharAt( builder.length( ) -1 ).toString( );
235 String stmt = SQL_QUERY_SELECTALL_BY_IDS + placeHolders + ")";
236
237
238 try ( DAOUtil daoUtil = new DAOUtil( stmt, plugin ) )
239 {
240 int index = 1;
241 for( Integer n : listIds ) {
242 daoUtil.setInt( index++, n );
243 }
244
245 daoUtil.executeQuery( );
246 while ( daoUtil.next( ) )
247 {
248 Project project = new Project( );
249 int nIndex = 1;
250
251 project.setId( daoUtil.getInt( nIndex++ ) );
252 project.setTitle( daoUtil.getString( nIndex++ ) );
253 project.setDescription( daoUtil.getString( nIndex++ ) );
254 project.setRangeOfPublicationDate( daoUtil.getDate( nIndex++ ) );
255 project.setDate( daoUtil.getString( nIndex++ ) );
256 project.setChoice( daoUtil.getString( nIndex ) );
257
258 projectList.add( project );
259 }
260
261 daoUtil.free( );
262
263 }
264 }
265 return projectList;
266
267 }
268 }