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.grafana.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 public final class DashboardDAO implements IDashboardDAO
47 {
48
49 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_dashboard ) FROM grafana_dashboard";
50 private static final String SQL_QUERY_SELECT = "SELECT id_dashboard, idGrafanaDashboard, title, uri FROM grafana_dashboard WHERE id_dashboard = ?";
51 private static final String SQL_QUERY_SELECT_BY_GRAFANA_ID = "SELECT id_dashboard, idGrafanaDashboard, title, uri FROM grafana_dashboard WHERE idGrafanaDashboard = ?";
52 private static final String SQL_QUERY_INSERT = "INSERT INTO grafana_dashboard ( id_dashboard, idGrafanaDashboard, title, uri ) VALUES ( ?, ?, ?, ? ) ";
53 private static final String SQL_QUERY_SELECTALL = "SELECT id_dashboard, idGrafanaDashboard, title, uri FROM grafana_dashboard";
54 private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_dashboard FROM grafana_dashboard";
55 private static final String SQL_QUERY_DELETE = "DELETE FROM grafana_dashboard WHERE id_dashboard = ?";
56 private static final String SQL_QUERY_DELETE_ALL = "DELETE FROM grafana_dashboard";
57 private static final String SQL_QUERY_COUNT_DASHBOARD_GRAFANA_ID = "SELECT count(*) FROM grafana_dashboard WHERE idGrafanaDashboard = ?";
58 private static final String SQL_QUERY_UPDATE = "UPDATE grafana_dashboard SET id_dashboard = ?, idGrafanaDashboard = ?, title = ? WHERE id_dashboard = ?";
59
60
61
62
63
64
65
66
67 public int newPrimaryKey( Plugin plugin )
68 {
69 int nKey = 1;
70
71 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin ) )
72 {
73 daoUtil.executeQuery( );
74 if ( daoUtil.next( ) )
75 {
76 nKey = daoUtil.getInt( 1 ) + 1;
77 }
78
79 }
80 return nKey;
81 }
82
83
84
85
86 @Override
87 public void insert( Dashboard dashboard, Plugin plugin )
88 {
89 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin ) )
90 {
91 dashboard.setId( newPrimaryKey( plugin ) );
92 int nIndex = 1;
93
94 daoUtil.setInt( nIndex++, dashboard.getId( ) );
95 daoUtil.setString( nIndex++, dashboard.getIdGrafanaDashboard( ) );
96 daoUtil.setString( nIndex++, dashboard.getTitle( ) );
97 daoUtil.setString( nIndex++, dashboard.getURI( ) );
98
99 daoUtil.executeUpdate( );
100 }
101 }
102
103
104
105
106 @Override
107 public Dashboard load( int nKey, Plugin plugin )
108 {
109 Dashboard dashboard = null;
110
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 dashboard = new Dashboard( );
119 int nIndex = 1;
120
121 dashboard.setId( daoUtil.getInt( nIndex++ ) );
122 dashboard.setIdGrafanaDashboard( daoUtil.getString( nIndex++ ) );
123 dashboard.setTitle( daoUtil.getString( nIndex++ ) );
124 dashboard.setURI( daoUtil.getString( nIndex++ ) );
125
126 }
127
128 }
129 return dashboard;
130 }
131
132
133
134
135 @Override
136 public Dashboard loadByGrafanaId( String strIdGrafana, Plugin plugin )
137 {
138 Dashboard dashboard = null;
139
140 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_GRAFANA_ID, plugin ) )
141 {
142 daoUtil.setString( 1, strIdGrafana );
143 daoUtil.executeQuery( );
144
145 if ( daoUtil.next( ) )
146 {
147 dashboard = new Dashboard( );
148 int nIndex = 1;
149
150 dashboard.setId( daoUtil.getInt( nIndex++ ) );
151 dashboard.setIdGrafanaDashboard( daoUtil.getString( nIndex++ ) );
152 dashboard.setTitle( daoUtil.getString( nIndex++ ) );
153 dashboard.setURI( daoUtil.getString( nIndex++ ) );
154
155 }
156
157 }
158 return dashboard;
159 }
160
161
162
163
164 @Override
165 public List<Dashboard> selectDashboardsList( Plugin plugin )
166 {
167 List<Dashboard> dashboardList = new ArrayList<Dashboard>( );
168 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
169 {
170 daoUtil.executeQuery( );
171
172 while ( daoUtil.next( ) )
173 {
174 Dashboardana/business/Dashboard.html#Dashboard">Dashboard dashboard = new Dashboard( );
175 int nIndex = 1;
176
177 dashboard.setId( daoUtil.getInt( nIndex++ ) );
178 dashboard.setIdGrafanaDashboard( daoUtil.getString( nIndex++ ) );
179 dashboard.setTitle( daoUtil.getString( nIndex++ ) );
180 dashboard.setURI( daoUtil.getString( nIndex++ ) );
181
182 dashboardList.add( dashboard );
183 }
184
185 }
186 return dashboardList;
187 }
188
189
190
191
192 @Override
193 public List<Integer> selectIdDashboardsList( Plugin plugin )
194 {
195 List<Integer> dashboardList = new ArrayList<Integer>( );
196 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin ) )
197 {
198 daoUtil.executeQuery( );
199
200 while ( daoUtil.next( ) )
201 {
202 dashboardList.add( daoUtil.getInt( 1 ) );
203 }
204
205 }
206 return dashboardList;
207 }
208
209
210
211
212 @Override
213 public ReferenceList selectDashboardsReferenceList( Plugin plugin )
214 {
215 ReferenceList dashboardList = new ReferenceList( );
216 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
217 {
218 daoUtil.executeQuery( );
219
220 while ( daoUtil.next( ) )
221 {
222 dashboardList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
223 }
224
225 }
226 return dashboardList;
227 }
228
229
230
231
232 @Override
233 public void delete( int nIdDashboard, Plugin plugin )
234 {
235 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
236 {
237 daoUtil.setInt( 1, nIdDashboard );
238 daoUtil.executeUpdate( );
239 }
240 }
241
242
243
244
245 @Override
246 public void deleteAll( Plugin plugin )
247 {
248 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ALL, plugin ) )
249 {
250
251 daoUtil.executeUpdate( );
252 }
253 }
254
255
256
257
258 @Override
259 public boolean isDashboardExists( Dashboard dashboard, Plugin plugin )
260 {
261 boolean bExist = false;
262
263 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_COUNT_DASHBOARD_GRAFANA_ID, plugin ) )
264 {
265 daoUtil.setString( 1, dashboard.getIdGrafanaDashboard( ) );
266 daoUtil.executeQuery( );
267 while ( daoUtil.next( ) )
268 {
269 if ( daoUtil.getInt( 1 ) >= 1 )
270 {
271 bExist = true;
272 }
273 }
274 }
275 return bExist;
276 }
277
278
279
280
281 @Override
282 public void store( Dashboard dashboard, Plugin plugin )
283 {
284 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
285 {
286 int nIndex = 1;
287
288 daoUtil.setInt( nIndex++, dashboard.getId( ) );
289 daoUtil.setString( nIndex++, dashboard.getIdGrafanaDashboard( ) );
290 daoUtil.setString( nIndex++, dashboard.getTitle( ) );
291 daoUtil.setInt( nIndex, dashboard.getId( ) );
292
293 daoUtil.executeUpdate( );
294 }
295 }
296 }