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.forms.business;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.ReferenceList;
38 import fr.paris.lutece.util.sql.DAOUtil;
39 import java.sql.Statement;
40
41 import java.util.ArrayList;
42 import java.util.List;
43 import java.util.stream.Collectors;
44
45 import org.apache.commons.collections.CollectionUtils;
46
47
48
49
50 public final class GroupDAO implements IGroupDAO
51 {
52
53 private static final String SQL_QUERY_SELECTALL = "SELECT id_group, title, description, id_step, iteration_min, iteration_max, iteration_add_label, iteration_remove_label FROM forms_group";
54 private static final String SQL_QUERY_SELECT = SQL_QUERY_SELECTALL + " WHERE id_group = ?";
55 private static final String SQL_QUERY_INSERT = "INSERT INTO forms_group ( title, description, id_step, iteration_min, iteration_max, iteration_add_label, iteration_remove_label ) VALUES ( ?, ?, ?, ?, ?, ?, ? ) ";
56 private static final String SQL_QUERY_DELETE = "DELETE FROM forms_group WHERE id_group = ? ";
57 private static final String SQL_QUERY_UPDATE = "UPDATE forms_group SET id_group = ?, title = ?, description = ?, id_step = ?, iteration_min = ?, iteration_max = ?, iteration_add_label = ?, iteration_remove_label = ? WHERE id_group = ?";
58 private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_group FROM forms_group";
59
60
61
62
63 @Override
64 public void insert( Group group, Plugin plugin )
65 {
66
67 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin ) )
68 {
69 int nIndex = 1;
70 daoUtil.setString( nIndex++, group.getTitle( ) );
71 daoUtil.setString( nIndex++, group.getDescription( ) );
72 daoUtil.setInt( nIndex++, group.getIdStep( ) );
73 daoUtil.setInt( nIndex++, group.getIterationMin( ) );
74 daoUtil.setInt( nIndex++, group.getIterationMax( ) );
75 daoUtil.setString( nIndex++, group.getIterationAddLabel( ) );
76 daoUtil.setString( nIndex++, group.getIterationRemoveLabel( ) );
77
78 daoUtil.executeUpdate( );
79 if ( daoUtil.nextGeneratedKey( ) )
80 {
81 group.setId( daoUtil.getGeneratedKeyInt( 1 ) );
82 }
83 }
84
85 }
86
87
88
89
90 @Override
91 public Group load( int nKey, Plugin plugin )
92 {
93
94 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
95 {
96 daoUtil.setInt( 1, nKey );
97 daoUtil.executeQuery( );
98 Group group = null;
99
100 if ( daoUtil.next( ) )
101 {
102 group = dataToObject( daoUtil );
103 }
104
105 return group;
106 }
107 }
108
109
110
111
112 @Override
113 public void delete( int nKey, Plugin plugin )
114 {
115 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
116 {
117
118 daoUtil.setInt( 1, nKey );
119 daoUtil.executeUpdate( );
120 }
121
122 }
123
124
125
126
127 @Override
128 public void store( Group group, Plugin plugin )
129 {
130 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
131 {
132
133 int nIndex = 1;
134
135 daoUtil.setInt( nIndex++, group.getId( ) );
136 daoUtil.setString( nIndex++, group.getTitle( ) );
137 daoUtil.setString( nIndex++, group.getDescription( ) );
138 daoUtil.setInt( nIndex++, group.getIdStep( ) );
139 daoUtil.setInt( nIndex++, group.getIterationMin( ) );
140 daoUtil.setInt( nIndex++, group.getIterationMax( ) );
141 daoUtil.setString( nIndex++, group.getIterationAddLabel( ) );
142 daoUtil.setString( nIndex++, group.getIterationRemoveLabel( ) );
143 daoUtil.setInt( nIndex, group.getId( ) );
144
145 daoUtil.executeUpdate( );
146 }
147 }
148
149
150
151
152 @Override
153 public List<Group> selectGroupsList( Plugin plugin )
154 {
155 List<Group> groupList = new ArrayList<>( );
156 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
157 {
158
159 daoUtil.executeQuery( );
160
161 while ( daoUtil.next( ) )
162 {
163 groupList.add( dataToObject( daoUtil ) );
164 }
165
166 }
167 return groupList;
168 }
169
170
171
172
173 @Override
174 public List<Integer> selectIdGroupsList( Plugin plugin )
175 {
176 List<Integer> groupList = new ArrayList<>( );
177
178 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin ) )
179 {
180 daoUtil.executeQuery( );
181
182 while ( daoUtil.next( ) )
183 {
184 groupList.add( daoUtil.getInt( 1 ) );
185 }
186
187 }
188 return groupList;
189 }
190
191
192
193
194 @Override
195 public ReferenceList selectGroupsReferenceList( Plugin plugin )
196 {
197 ReferenceList groupList = new ReferenceList( );
198
199 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
200 {
201 daoUtil.executeQuery( );
202
203 while ( daoUtil.next( ) )
204 {
205 groupList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
206 }
207
208 }
209 return groupList;
210 }
211
212 @Override
213 public List<Group> selectGroupsListByListIdStep( List<Integer> idSteplist, Plugin plugin )
214 {
215 List<Group> groupList = new ArrayList<>( );
216 if ( CollectionUtils.isEmpty( idSteplist ) )
217 {
218 return groupList;
219 }
220
221 String query = SQL_QUERY_SELECTALL + " WHERE id_step IN ( ";
222 query += idSteplist.stream( ).map( i -> "?" ).collect( Collectors.joining( "," ) );
223 query += " )";
224
225 try ( DAOUtil daoUtil = new DAOUtil( query, plugin ) )
226 {
227 int index = 0;
228 for ( Integer id : idSteplist )
229 {
230 daoUtil.setInt( ++index, id );
231 }
232 daoUtil.executeQuery( );
233
234 while ( daoUtil.next( ) )
235 {
236 groupList.add( dataToObject( daoUtil ) );
237 }
238 }
239 return groupList;
240 }
241
242
243
244
245
246
247
248 private Group dataToObject( DAOUtil daoUtil )
249 {
250 Groupins/forms/business/Group.html#Group">Group group = new Group( );
251
252 group.setId( daoUtil.getInt( "id_group" ) );
253 group.setTitle( daoUtil.getString( "title" ) );
254 group.setDescription( daoUtil.getString( "description" ) );
255 group.setIdStep( daoUtil.getInt( "id_step" ) );
256 group.setIterationMin( daoUtil.getInt( "iteration_min" ) );
257 group.setIterationMax( daoUtil.getInt( "iteration_max" ) );
258 group.setIterationAddLabel( daoUtil.getString( "iteration_add_label" ) );
259 group.setIterationRemoveLabel( daoUtil.getString( "iteration_remove_label" ) );
260
261 return group;
262 }
263 }