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.appstore.business;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.sql.DAOUtil;
38
39 import java.util.ArrayList;
40 import java.util.Collection;
41
42
43
44
45 public final class ApplicationDAO implements IApplicationDAO
46 {
47
48 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_application ) FROM appstore_application";
49 private static final String SQL_QUERY_SELECT = "SELECT a.id_application, a.title, a.description, a.id_category, a.id_order, a.id_icon, a.pom_url, a.webapp_url, a.sql_script_url, b.name,"
50 + " a.artifact_id, a.presentation, a.installation, a.version, a.build_status, a.publish_status "
51 + " FROM appstore_application a, appstore_category b WHERE a.id_category = b.id_category AND id_application = ?";
52 private static final String SQL_QUERY_INSERT = "INSERT INTO appstore_application ( id_application, title, description, id_category, id_order, id_icon, pom_url, webapp_url, sql_script_url, artifact_id, presentation, installation, version, build_status, publish_status ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
53 private static final String SQL_QUERY_DELETE = "DELETE FROM appstore_application WHERE id_application = ? ";
54 private static final String SQL_QUERY_UPDATE = "UPDATE appstore_application SET id_application = ?, title = ?, description = ?, id_category = ?, id_order = ?, id_icon = ?, pom_url = ?, webapp_url = ?, sql_script_url = ?, artifact_id = ?, presentation = ?, installation = ?, version = ?, build_status = ?, publish_status = ? WHERE id_application = ?";
55 private static final String SQL_QUERY_SELECTALL = "SELECT a.id_application, a.title, a.description, a.id_category, a.id_order, a.id_icon, a.pom_url, a.webapp_url, a.sql_script_url, b.name, "
56 + " a.artifact_id, a.presentation, a.installation, a.version, a.build_status, a.publish_status "
57 + " FROM appstore_application a, appstore_category b WHERE a.id_category = b.id_category ORDER by a.id_order";
58 private static final String SQL_QUERY_DELETE_COMPONENT = "DELETE FROM appstore_application_component WHERE id_application = ? ";
59 private static final String SQL_QUERY_INSERT_COMPONENT = "INSERT INTO appstore_application_component ( id_application, id_component ) VALUES ( ?, ? ) ";
60
61
62
63
64
65
66
67
68 public int newPrimaryKey( Plugin plugin )
69 {
70 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
71 daoUtil.executeQuery( );
72
73 int nKey;
74
75 daoUtil.next( );
76 nKey = daoUtil.getInt( 1 ) + 1;
77 daoUtil.free( );
78
79 return nKey;
80 }
81
82
83
84
85
86
87
88
89
90 @Override
91 public void insert( Application application, Plugin plugin )
92 {
93 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
94
95 application.setId( newPrimaryKey( plugin ) );
96
97 daoUtil.setInt( 1, application.getId( ) );
98 daoUtil.setString( 2, application.getTitle( ) );
99 daoUtil.setString( 3, application.getDescription( ) );
100 daoUtil.setInt( 4, application.getIdCategory( ) );
101 daoUtil.setInt( 5, application.getOrder( ) );
102 daoUtil.setInt( 6, application.getIdIcon( ) );
103 daoUtil.setString( 7, application.getPomUrl( ) );
104 daoUtil.setString( 8, application.getWebappUrl( ) );
105 daoUtil.setString( 9, application.getSqlScriptUrl( ) );
106 daoUtil.setString( 10, application.getArtifactId( ) );
107 daoUtil.setString( 11, application.getPresentation( ) );
108 daoUtil.setString( 12, application.getInstallation( ) );
109 daoUtil.setString( 13, application.getVersion( ) );
110 daoUtil.setInt( 14, application.getBuildStatus( ) );
111 daoUtil.setInt( 15, application.getPublishStatus( ) );
112
113 daoUtil.executeUpdate( );
114 daoUtil.free( );
115 }
116
117
118
119
120
121
122
123
124
125
126 @Override
127 public Application load( int nId, Plugin plugin )
128 {
129 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
130 daoUtil.setInt( 1, nId );
131 daoUtil.executeQuery( );
132
133 Application application = null;
134
135 if ( daoUtil.next( ) )
136 {
137 application = new Application( );
138
139 application.setId( daoUtil.getInt( 1 ) );
140 application.setTitle( daoUtil.getString( 2 ) );
141 application.setDescription( daoUtil.getString( 3 ) );
142 application.setIdCategory( daoUtil.getInt( 4 ) );
143 application.setOrder( daoUtil.getInt( 5 ) );
144 application.setIdIcon( daoUtil.getInt( 6 ) );
145 application.setPomUrl( daoUtil.getString( 7 ) );
146 application.setWebappUrl( daoUtil.getString( 8 ) );
147 application.setSqlScriptUrl( daoUtil.getString( 9 ) );
148 application.setCategory( daoUtil.getString( 10 ) );
149 application.setArtifactId( daoUtil.getString( 11 ) );
150 application.setPresentation( daoUtil.getString( 12 ) );
151 application.setInstallation( daoUtil.getString( 13 ) );
152 application.setVersion( daoUtil.getString( 14 ) );
153 application.setBuildStatus( daoUtil.getInt( 15 ) );
154 application.setPublishStatus( daoUtil.getInt( 16 ) );
155 }
156
157 daoUtil.free( );
158
159 return application;
160 }
161
162
163
164
165
166
167
168
169
170 @Override
171 public void delete( int nApplicationId, Plugin plugin )
172 {
173 clearComponentsList( nApplicationId, plugin );
174
175 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
176 daoUtil.setInt( 1, nApplicationId );
177 daoUtil.executeUpdate( );
178 daoUtil.free( );
179 }
180
181
182
183
184
185
186
187
188
189 @Override
190 public void store( Application application, Plugin plugin )
191 {
192 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
193
194 daoUtil.setInt( 1, application.getId( ) );
195 daoUtil.setString( 2, application.getTitle( ) );
196 daoUtil.setString( 3, application.getDescription( ) );
197 daoUtil.setInt( 4, application.getIdCategory( ) );
198 daoUtil.setInt( 5, application.getOrder( ) );
199 daoUtil.setInt( 6, application.getIdIcon( ) );
200 daoUtil.setString( 7, application.getPomUrl( ) );
201 daoUtil.setString( 8, application.getWebappUrl( ) );
202 daoUtil.setString( 9, application.getSqlScriptUrl( ) );
203 daoUtil.setString( 10, application.getArtifactId( ) );
204 daoUtil.setString( 11, application.getPresentation( ) );
205 daoUtil.setString( 12, application.getInstallation( ) );
206 daoUtil.setString( 13, application.getVersion( ) );
207 daoUtil.setInt( 14, application.getBuildStatus( ) );
208 daoUtil.setInt( 15, application.getPublishStatus( ) );
209 daoUtil.setInt( 16, application.getId( ) );
210
211 daoUtil.executeUpdate( );
212 daoUtil.free( );
213 }
214
215
216
217
218
219
220
221
222 @Override
223 public Collection<Application> selectApplicationsList( Plugin plugin )
224 {
225 Collection<Application> applicationList = new ArrayList<Application>( );
226 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
227 daoUtil.executeQuery( );
228
229 while ( daoUtil.next( ) )
230 {
231 Application application = new Application( );
232
233 application.setId( daoUtil.getInt( 1 ) );
234 application.setTitle( daoUtil.getString( 2 ) );
235 application.setDescription( daoUtil.getString( 3 ) );
236 application.setIdCategory( daoUtil.getInt( 4 ) );
237 application.setOrder( daoUtil.getInt( 5 ) );
238 application.setIdIcon( daoUtil.getInt( 6 ) );
239 application.setPomUrl( daoUtil.getString( 7 ) );
240 application.setWebappUrl( daoUtil.getString( 8 ) );
241 application.setSqlScriptUrl( daoUtil.getString( 9 ) );
242 application.setCategory( daoUtil.getString( 10 ) );
243 application.setArtifactId( daoUtil.getString( 11 ) );
244 application.setPresentation( daoUtil.getString( 12 ) );
245 application.setInstallation( daoUtil.getString( 13 ) );
246 application.setVersion( daoUtil.getString( 14 ) );
247 application.setBuildStatus( daoUtil.getInt( 15 ) );
248 application.setPublishStatus( daoUtil.getInt( 16 ) );
249
250 applicationList.add( application );
251 }
252
253 daoUtil.free( );
254
255 return applicationList;
256 }
257
258 @Override
259 public void clearComponentsList( int nApplicationId, Plugin plugin )
260 {
261 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_COMPONENT, plugin );
262 daoUtil.setInt( 1, nApplicationId );
263 daoUtil.executeUpdate( );
264 daoUtil.free( );
265 }
266
267 @Override
268 public void addComponent( int nApplicationId, int nComponentId, Plugin plugin )
269 {
270 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_COMPONENT, plugin );
271
272 daoUtil.setInt( 1, nApplicationId );
273 daoUtil.setInt( 2, nComponentId );
274
275 daoUtil.executeUpdate( );
276 daoUtil.free( );
277 }
278 }