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.directory.modules.gismap.business.portlet;
35
36 import java.util.ArrayList;
37 import java.util.List;
38
39 import fr.paris.lutece.plugins.directory.modules.gismap.business.DirectoryGismapSourceQuery;
40 import fr.paris.lutece.portal.business.portlet.Portlet;
41 import fr.paris.lutece.portal.service.plugin.Plugin;
42 import fr.paris.lutece.util.sql.DAOUtil;
43
44
45
46
47 public final class GismapDirectoryPortletDAO implements IGismapDirectoryPortletDAO
48 {
49 private static final String SQL_QUERY_INSERT = "INSERT INTO gismap_portlet ( id_portlet , id_view ) VALUES ( ? , ? )";
50 private static final String SQL_QUERY_SELECT = "SELECT id_portlet , id_view FROM gismap_portlet WHERE id_portlet = ? ";
51 private static final String SQL_QUERY_UPDATE = "UPDATE gismap_portlet SET id_view = ? WHERE id_portlet = ? ";
52 private static final String SQL_QUERY_DELETE = "DELETE FROM gismap_portlet WHERE id_portlet= ? ";
53 private static final String SQL_QUERY_SELECT_DIRECTORY_GISMAP_SOURCES_BY_PORTLET_ID = "SELECT s.id_directory, d.title, s.id_entry_geolocation, e.title, s.geojson_index"
54 + " FROM directory_gismap_source s INNER JOIN directory_directory d ON s.id_directory = d.id_directory "
55 + " INNER JOIN directory_entry e ON s.id_entry_geolocation = e.id_entry"
56 + " WHERE s.id_portlet= ? ORDER BY s.geojson_index";
57 private static final String SQL_QUERY_DELETE_DIRECTORY_GISMAP_SOURCES_BY_PORTLET_ID = "DELETE from directory_gismap_source WHERE id_portlet= ?";
58 private static final String SQL_QUERY_INSERT_DIRECTORY_GISMAP_SOURCES_BY_PORTLET_ID = "INSERT INTO directory_gismap_source ( id_directory_gismap_source, id_portlet, id_directory, id_entry_geolocation, geojson_index) "
59 + " VALUES ( ?, ?, ?, ?, ? ) ";
60 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_directory_gismap_source ) FROM directory_gismap_source";
61
62
63
64
65
66
67
68
69
70
71
72
73 @Override
74 public void insert( Portlet portlet )
75 {
76 GismapDirectoryPortlet p = ( GismapDirectoryPortlet ) portlet;
77
78 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT );
79 daoUtil.setInt( 1, p.getId( ) );
80 daoUtil.setInt( 2, p.getView( ) );
81
82 daoUtil.executeUpdate( );
83 daoUtil.free( );
84
85 removeDirectoryGismapSourcesByPortletId(p.getId( ) );
86 addDirectoryGismapSourcesToPortlet(p.getId( ), p.getListMapSource( ) );
87 }
88
89
90
91
92
93
94
95 @Override
96 public void delete( int nPortletId )
97 {
98 removeDirectoryGismapSourcesByPortletId( nPortletId );
99
100 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE );
101 daoUtil.setInt( 1, nPortletId );
102 daoUtil.executeUpdate( );
103 daoUtil.free( );
104 }
105
106
107
108
109
110
111
112
113 @Override
114 public Portlet load( int nPortletId )
115 {
116 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT );
117 daoUtil.setInt( 1, nPortletId );
118 daoUtil.executeQuery( );
119
120 GismapDirectoryPortlet portlet = new GismapDirectoryPortlet( );
121
122 if ( daoUtil.next( ) )
123 {
124 portlet.setId( daoUtil.getInt( 1 ) );
125 portlet.setView( daoUtil.getInt( 2 ) );
126 }
127
128 daoUtil.free( );
129
130 List<DirectoryGismapSourceQuery> listMapSources = loadMapSourcesByPortletId( nPortletId );
131 portlet.setListMapSource( listMapSources );
132
133 return portlet;
134 }
135
136
137
138
139
140
141
142
143 @Override
144 public void store( Portlet portlet )
145 {
146 GismapDirectoryPortlet p = ( GismapDirectoryPortlet ) portlet;
147 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE );
148 daoUtil.setInt( 1, p.getView( ) );
149 daoUtil.setInt( 2, p.getId( ) );
150 daoUtil.executeUpdate( );
151 daoUtil.free( );
152
153 removeDirectoryGismapSourcesByPortletId(p.getId( ) );
154 addDirectoryGismapSourcesToPortlet(p.getId( ), p.getListMapSource( ) );
155
156 }
157
158
159
160
161
162
163
164 public List<DirectoryGismapSourceQuery> loadMapSourcesByPortletId( int portletId )
165 {
166 List<DirectoryGismapSourceQuery> listDirectoryGismapSourceQuery = new ArrayList<>( );
167 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_DIRECTORY_GISMAP_SOURCES_BY_PORTLET_ID );
168 daoUtil.setInt( 1, portletId );
169 daoUtil.executeQuery( );
170
171 while ( daoUtil.next( ) )
172 {
173 DirectoryGismapSourceQuery directoryGismapSourceQuery = new DirectoryGismapSourceQuery( );
174 directoryGismapSourceQuery.setIdDirectory( daoUtil.getInt( 1 ) );
175 directoryGismapSourceQuery.setDirectoryTitle( daoUtil.getString( 2 ) );
176 directoryGismapSourceQuery.setIdGeolocationEntry( daoUtil.getInt( 3) );
177 directoryGismapSourceQuery.setGeolocationEntryTitle( daoUtil.getString( 4 ) );
178 directoryGismapSourceQuery.setGeoJsonIndex( daoUtil.getInt( 5 ) );
179
180 listDirectoryGismapSourceQuery.add( directoryGismapSourceQuery );
181 }
182
183 daoUtil.free();
184
185 return listDirectoryGismapSourceQuery;
186 }
187
188
189
190
191
192
193
194 public void removeDirectoryGismapSourcesByPortletId(int portletId) {
195 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_DIRECTORY_GISMAP_SOURCES_BY_PORTLET_ID );
196 daoUtil.setInt( 1, portletId );
197 daoUtil.executeUpdate( );
198 daoUtil.free();
199 }
200
201
202
203
204
205
206
207 public void addDirectoryGismapSourcesToPortlet(int idPortlet, List<DirectoryGismapSourceQuery> listDirectoryGismapSource) {
208
209 if ( idPortlet != 0)
210 {
211 for (DirectoryGismapSourceQuery directoryGismapSource : listDirectoryGismapSource)
212 {
213 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_DIRECTORY_GISMAP_SOURCES_BY_PORTLET_ID );
214 int nIndex = 1;
215 daoUtil.setInt( nIndex++, newPrimaryKey( ));
216 daoUtil.setInt( nIndex++, idPortlet );
217 daoUtil.setInt( nIndex++, directoryGismapSource.getIdDirectory( ) );
218 daoUtil.setInt( nIndex++, directoryGismapSource.getIdGeolocationEntry( ) );
219 daoUtil.setInt( nIndex++, directoryGismapSource.getGeoJsonIndex( ) );
220 daoUtil.executeUpdate( );
221 daoUtil.free();
222 }
223 }
224 }
225
226
227
228
229
230
231
232
233
234 public int newPrimaryKey( )
235 {
236 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK );
237 daoUtil.executeQuery( );
238
239 int nKey = 1;
240
241 if ( daoUtil.next( ) )
242 {
243 nKey = daoUtil.getInt( 1 ) + 1;
244 }
245
246 daoUtil.free( );
247
248 return nKey;
249 }
250
251
252 }