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.notificationstore.business;
35
36 import fr.paris.lutece.plugins.grubusiness.business.demand.DemandCategory;
37 import fr.paris.lutece.plugins.grubusiness.business.demand.IDemandCategoryDAO;
38 import fr.paris.lutece.plugins.notificationstore.service.NotificationStorePlugin;
39 import fr.paris.lutece.util.sql.DAOUtil;
40 import java.sql.Statement;
41
42 import java.util.ArrayList;
43 import java.util.List;
44 import java.util.Optional;
45
46
47
48
49 public final class DemandCategoryDAO implements IDemandCategoryDAO
50 {
51
52 private static final String SQL_QUERY_SELECT = "SELECT id_demand_category, code, label, is_default FROM notificationstore_demand_category WHERE id_demand_category = ?";
53 private static final String SQL_QUERY_INSERT = "INSERT INTO notificationstore_demand_category ( code, label, is_default ) VALUES ( ?, ?, ? ) ";
54 private static final String SQL_QUERY_DELETE = "DELETE FROM notificationstore_demand_category WHERE id_demand_category = ? ";
55 private static final String SQL_QUERY_UPDATE = "UPDATE notificationstore_demand_category SET code = ?, label = ?, is_default = ? WHERE id_demand_category = ?";
56 private static final String SQL_QUERY_SELECTALL = "SELECT id_demand_category, code, label, is_default FROM notificationstore_demand_category";
57 private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_demand_category FROM notificationstore_demand_category";
58 private static final String SQL_QUERY_SELECTALL_BY_IDS = "SELECT id_demand_category, code, label, is_default FROM notificationstore_demand_category WHERE id_demand_category IN ( ";
59
60
61
62
63 @Override
64 public void insert( DemandCategory demandCategory )
65 {
66 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, NotificationStorePlugin.getPlugin( ) ) )
67 {
68 int nIndex = 1;
69 daoUtil.setString( nIndex++, demandCategory.getCode( ) );
70 daoUtil.setString( nIndex++, demandCategory.getLabel( ) );
71 daoUtil.setBoolean( nIndex++, demandCategory.isDefault( ) );
72
73 daoUtil.executeUpdate( );
74 if ( daoUtil.nextGeneratedKey( ) )
75 {
76 demandCategory.setId( daoUtil.getGeneratedKeyInt( 1 ) );
77 }
78 }
79
80 }
81
82
83
84
85 @Override
86 public Optional<DemandCategory> load( int nKey )
87 {
88 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, NotificationStorePlugin.getPlugin( ) ) )
89 {
90 daoUtil.setInt( 1, nKey );
91 daoUtil.executeQuery( );
92 DemandCategory demandCategory = null;
93
94 if ( daoUtil.next( ) )
95 {
96 demandCategory = new DemandCategory( );
97 int nIndex = 1;
98
99 demandCategory.setId( daoUtil.getInt( nIndex++ ) );
100 demandCategory.setCode( daoUtil.getString( nIndex++ ) );
101 demandCategory.setLabel( daoUtil.getString( nIndex++ ) );
102 demandCategory.setDefault( daoUtil.getBoolean( nIndex ) );
103 }
104
105 return Optional.ofNullable( demandCategory );
106 }
107 }
108
109
110
111
112 @Override
113 public void delete( int nKey )
114 {
115 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, NotificationStorePlugin.getPlugin( ) ) )
116 {
117 daoUtil.setInt( 1, nKey );
118 daoUtil.executeUpdate( );
119 }
120 }
121
122
123
124
125 @Override
126 public void store( DemandCategory demandCategory )
127 {
128 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, NotificationStorePlugin.getPlugin( ) ) )
129 {
130 int nIndex = 1;
131
132 daoUtil.setString( nIndex++, demandCategory.getCode( ) );
133 daoUtil.setString( nIndex++, demandCategory.getLabel( ) );
134 daoUtil.setBoolean( nIndex++, demandCategory.isDefault( ) );
135 daoUtil.setInt( nIndex++, demandCategory.getId( ) );
136
137 daoUtil.executeUpdate( );
138 }
139 }
140
141
142
143
144 @Override
145 public List<DemandCategory> selectDemandCategoriesList( )
146 {
147 List<DemandCategory> demandCategoryList = new ArrayList<>( );
148 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, NotificationStorePlugin.getPlugin( ) ) )
149 {
150 daoUtil.executeQuery( );
151
152 while ( daoUtil.next( ) )
153 {
154 DemandCategory demandCategory = new DemandCategory( );
155 int nIndex = 1;
156
157 demandCategory.setId( daoUtil.getInt( nIndex++ ) );
158 demandCategory.setCode( daoUtil.getString( nIndex++ ) );
159 demandCategory.setLabel( daoUtil.getString( nIndex++ ) );
160 demandCategory.setDefault( daoUtil.getBoolean( nIndex ) );
161
162 demandCategoryList.add( demandCategory );
163 }
164
165 return demandCategoryList;
166 }
167 }
168
169
170
171
172 @Override
173 public List<Integer> selectIdDemandCategoriesList( )
174 {
175 List<Integer> demandCategoryList = new ArrayList<>( );
176 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, NotificationStorePlugin.getPlugin( ) ) )
177 {
178 daoUtil.executeQuery( );
179
180 while ( daoUtil.next( ) )
181 {
182 demandCategoryList.add( daoUtil.getInt( 1 ) );
183 }
184
185 return demandCategoryList;
186 }
187 }
188
189
190
191
192 @Override
193 public List<DemandCategory> selectDemandCategoriesListByIds( List<Integer> listIds )
194 {
195 List<DemandCategory> demandCategoryList = new ArrayList<>( );
196
197 StringBuilder builder = new StringBuilder( );
198
199 if ( !listIds.isEmpty( ) )
200 {
201 for ( int i = 0; i < listIds.size( ); i++ )
202 {
203 builder.append( "?," );
204 }
205
206 String placeHolders = builder.deleteCharAt( builder.length( ) - 1 ).toString( );
207 String stmt = SQL_QUERY_SELECTALL_BY_IDS + placeHolders + ")";
208
209 try ( DAOUtil daoUtil = new DAOUtil( stmt, NotificationStorePlugin.getPlugin( ) ) )
210 {
211 int index = 1;
212 for ( Integer n : listIds )
213 {
214 daoUtil.setInt( index++, n );
215 }
216
217 daoUtil.executeQuery( );
218 while ( daoUtil.next( ) )
219 {
220 DemandCategory demandCategory = new DemandCategory( );
221 int nIndex = 1;
222
223 demandCategory.setId( daoUtil.getInt( nIndex++ ) );
224 demandCategory.setCode( daoUtil.getString( nIndex++ ) );
225 demandCategory.setLabel( daoUtil.getString( nIndex++ ) );
226 demandCategory.setDefault( daoUtil.getBoolean( nIndex ) );
227
228 demandCategoryList.add( demandCategory );
229 }
230
231 daoUtil.free( );
232
233 }
234 }
235 return demandCategoryList;
236
237 }
238 }