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.mydashboard.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
40 import java.util.ArrayList;
41 import java.util.List;
42
43
44
45
46
47 public final class PanelDAO implements IPanelDAO
48 {
49
50 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_panel ) FROM mydashboard_panel";
51 private static final String SQL_QUERY_SELECTALL = "SELECT id_panel, code, title, description,is_default FROM mydashboard_panel";
52 private static final String SQL_QUERY_ORDER_BY_TITLE = " ORDER BY title";
53 private static final String SQL_QUERY_SELECT = SQL_QUERY_SELECTALL + " WHERE id_panel = ?";
54 private static final String SQL_QUERY_SELECT_BY_CODE = SQL_QUERY_SELECTALL + " WHERE code = ?";
55 private static final String SQL_QUERY_SELECT_DEFAULT_PANEL = SQL_QUERY_SELECTALL + " WHERE is_default = 1";
56 private static final String SQL_QUERY_INSERT = "INSERT INTO mydashboard_panel ( id_panel, code, title, description,is_default ) VALUES ( ?, ?, ?, ?, ? ) ";
57 private static final String SQL_QUERY_DELETE = "DELETE FROM mydashboard_panel WHERE id_panel = ? ";
58 private static final String SQL_QUERY_UPDATE = "UPDATE mydashboard_panel SET id_panel = ?, code = ?, title = ?, description = ?, is_default = ? WHERE id_panel = ?";
59 private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_panel FROM mydashboard_panel";
60
61
62
63
64
65
66 public int newPrimaryKey( Plugin plugin )
67 {
68 int nKey = 1;
69 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin ) )
70 {
71 daoUtil.executeQuery( );
72
73 if ( daoUtil.next( ) )
74 {
75 nKey = daoUtil.getInt( 1 ) + 1;
76 }
77 }
78
79 return nKey;
80 }
81
82
83
84
85 @Override
86 public void insert( Panel panel, Plugin plugin )
87 {
88 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin ) )
89 {
90 panel.setId( newPrimaryKey( plugin ) );
91
92 int nIndex = 1;
93
94 daoUtil.setInt( nIndex++, panel.getId( ) );
95 daoUtil.setString( nIndex++, panel.getCode( ) );
96 daoUtil.setString( nIndex++, panel.getTitle( ) );
97 daoUtil.setString( nIndex++, panel.getDescription( ) );
98 daoUtil.setBoolean( nIndex++, panel.isDefault( ) );
99
100 daoUtil.executeUpdate( );
101 }
102 }
103
104
105
106
107 @Override
108 public Panel load( int nKey, Plugin plugin )
109 {
110 Panel panel = null;
111 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
112 {
113 daoUtil.setInt( 1, nKey );
114 daoUtil.executeQuery( );
115
116 if ( daoUtil.next( ) )
117 {
118 panel = new Panel( );
119
120 int nIndex = 1;
121
122 panel.setId( daoUtil.getInt( nIndex++ ) );
123 panel.setCode( daoUtil.getString( nIndex++ ) );
124 panel.setTitle( daoUtil.getString( nIndex++ ) );
125 panel.setDescription( daoUtil.getString( nIndex++ ) );
126 panel.setDefault( daoUtil.getBoolean( nIndex++ ) );
127 }
128 }
129
130 return panel;
131 }
132
133
134
135
136 @Override
137 public Panel loadByCode( String strCode, Plugin plugin )
138 {
139 Panel panel = null;
140 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_CODE, plugin ) )
141 {
142 daoUtil.setString( 1, strCode );
143 daoUtil.executeQuery( );
144
145 if ( daoUtil.next( ) )
146 {
147 panel = new Panel( );
148
149 int nIndex = 1;
150
151 panel.setId( daoUtil.getInt( nIndex++ ) );
152 panel.setCode( daoUtil.getString( nIndex++ ) );
153 panel.setTitle( daoUtil.getString( nIndex++ ) );
154 panel.setDescription( daoUtil.getString( nIndex++ ) );
155 panel.setDefault( daoUtil.getBoolean( nIndex++ ) );
156 }
157 }
158
159 return panel;
160 }
161
162
163
164
165 @Override
166 public void delete( int nKey, Plugin plugin )
167 {
168 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
169 {
170 daoUtil.setInt( 1, nKey );
171 daoUtil.executeUpdate( );
172 }
173 }
174
175
176
177
178 @Override
179 public void store( Panel panel, Plugin plugin )
180 {
181 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
182 {
183 int nIndex = 1;
184
185 daoUtil.setInt( nIndex++, panel.getId( ) );
186 daoUtil.setString( nIndex++, panel.getCode( ) );
187 daoUtil.setString( nIndex++, panel.getTitle( ) );
188 daoUtil.setString( nIndex++, panel.getDescription( ) );
189 daoUtil.setBoolean( nIndex++, panel.isDefault( ) );
190 daoUtil.setInt( nIndex, panel.getId( ) );
191
192 daoUtil.executeUpdate( );
193 }
194 }
195
196
197
198
199 @Override
200 public List<Panel> selectPanelsList( Plugin plugin )
201 {
202 List<Panel> panelList = new ArrayList<>( );
203 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL + SQL_QUERY_ORDER_BY_TITLE, plugin ) )
204 {
205 daoUtil.executeQuery( );
206
207 while ( daoUtil.next( ) )
208 {
209 Panelins/mydashboard/business/Panel.html#Panel">Panel panel = new Panel( );
210 int nIndex = 1;
211
212 panel.setId( daoUtil.getInt( nIndex++ ) );
213 panel.setCode( daoUtil.getString( nIndex++ ) );
214 panel.setTitle( daoUtil.getString( nIndex++ ) );
215 panel.setDescription( daoUtil.getString( nIndex++ ) );
216 panel.setDefault( daoUtil.getBoolean( nIndex ) );
217 panelList.add( panel );
218 }
219 }
220
221 return panelList;
222 }
223
224
225
226
227 @Override
228 public List<Integer> selectIdPanelsList( Plugin plugin )
229 {
230 List<Integer> panelList = new ArrayList<>( );
231 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin ) )
232 {
233 daoUtil.executeQuery( );
234
235 while ( daoUtil.next( ) )
236 {
237 panelList.add( daoUtil.getInt( 1 ) );
238 }
239 }
240
241 return panelList;
242 }
243
244
245
246
247 @Override
248 public ReferenceList selectPanelsReferenceList( Plugin plugin )
249 {
250 ReferenceList panelList = new ReferenceList( );
251 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
252 {
253 daoUtil.executeQuery( );
254
255 while ( daoUtil.next( ) )
256 {
257 panelList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
258 }
259 }
260
261 return panelList;
262 }
263
264
265
266
267 @Override
268 public Panel loadDefaultPanel( Plugin plugin )
269 {
270 Panel panel = null;
271 try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_DEFAULT_PANEL, plugin ) )
272 {
273 daoUtil.executeQuery( );
274
275 if ( daoUtil.next( ) )
276 {
277 panel = new Panel( );
278
279 int nIndex = 1;
280
281 panel.setId( daoUtil.getInt( nIndex++ ) );
282 panel.setCode( daoUtil.getString( nIndex++ ) );
283 panel.setTitle( daoUtil.getString( nIndex++ ) );
284 panel.setDescription( daoUtil.getString( nIndex++ ) );
285 panel.setDefault( daoUtil.getBoolean( nIndex++ ) );
286 }
287 }
288
289 return panel;
290 }
291 }