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.releaser.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 SiteDAO implements ISiteDAO
47 {
48
49 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_site ) FROM releaser_site";
50 private static final String SQL_QUERY_SELECT = "SELECT a.id_site, a.name, a.description, a.artifact_id, a.id_cluster, b.name, a.scm_url, a.jira_key,a.is_theme "
51 + " FROM releaser_site a , releaser_cluster b WHERE a.id_site = ? AND a.id_cluster = b.id_cluster";
52 private static final String SQL_QUERY_INSERT = "INSERT INTO releaser_site ( id_site, artifact_id, id_cluster, scm_url, name, description, jira_key,is_theme ) VALUES ( ?, ?, ?, ?, ?, ?, ? , ?) ";
53 private static final String SQL_QUERY_DELETE = "DELETE FROM releaser_site WHERE id_site = ? ";
54 private static final String SQL_QUERY_UPDATE = "UPDATE releaser_site SET id_site = ?, artifact_id = ?, id_cluster = ?, scm_url = ?, name = ?, description = ?, jira_key = ?,is_theme= ? WHERE id_site = ?";
55 private static final String SQL_QUERY_SELECTALL = "SELECT a.id_site, a.name, a.description, a.artifact_id, a.id_cluster, b.name, a.scm_url, a.jira_key,a.is_theme "
56 + " FROM releaser_site a , releaser_cluster b WHERE a.id_cluster = b.id_cluster";
57 private static final String SQL_QUERY_SELECT_BY_CLUSTER = "SELECT a.id_site, a.name, a.description, a.artifact_id, a.id_cluster, b.name, a.scm_url, a.jira_key,a.is_theme "
58 + " FROM releaser_site a , releaser_cluster b WHERE a.id_cluster = b.id_cluster AND a.id_cluster = ?";
59 private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_site FROM releaser_site";
60 private static final String SQL_QUERY_SELECT_SEARCH_DUPLICATE_SITE = "SELECT b.name, a.id_site FROM releaser_site a LEFT OUTER JOIN releaser_cluster b"
61 + " ON a.id_cluster = b.id_cluster WHERE a.artifact_id = ? OR a.name = ? OR a.scm_url = ?";
62
63
64
65
66
67
68
69
70 public int newPrimaryKey( Plugin plugin )
71 {
72 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
73 daoUtil.executeQuery( );
74 int nKey = 1;
75
76 if ( daoUtil.next( ) )
77 {
78 nKey = daoUtil.getInt( 1 ) + 1;
79 }
80
81 daoUtil.free( );
82 return nKey;
83 }
84
85
86
87
88 @Override
89 public void insert( Site site, Plugin plugin )
90 {
91 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
92 site.setId( newPrimaryKey( plugin ) );
93 int nIndex = 1;
94
95 daoUtil.setInt( nIndex++, site.getId( ) );
96 daoUtil.setString( nIndex++, site.getArtifactId( ) );
97 daoUtil.setInt( nIndex++, site.getIdCluster( ) );
98 daoUtil.setString( nIndex++, site.getScmUrl( ) );
99 daoUtil.setString( nIndex++, site.getName( ) );
100 daoUtil.setString( nIndex++, site.getDescription( ) );
101 daoUtil.setString( nIndex++, site.getJiraKey( ) );
102 daoUtil.setBoolean( nIndex++, site.isTheme( ) );
103
104 daoUtil.executeUpdate( );
105 daoUtil.free( );
106 }
107
108
109
110
111 @Override
112 public Site load( int nKey, Plugin plugin )
113 {
114 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
115 daoUtil.setInt( 1, nKey );
116 daoUtil.executeQuery( );
117 Site site = null;
118
119 if ( daoUtil.next( ) )
120 {
121 site = new Site( );
122 int nIndex = 1;
123
124 site.setId( daoUtil.getInt( nIndex++ ) );
125 site.setName( daoUtil.getString( nIndex++ ) );
126 site.setDescription( daoUtil.getString( nIndex++ ) );
127 site.setArtifactId( daoUtil.getString( nIndex++ ) );
128 site.setIdCluster( daoUtil.getInt( nIndex++ ) );
129 site.setCluster( daoUtil.getString( nIndex++ ) );
130 site.setScmUrl( daoUtil.getString( nIndex++ ) );
131 site.setJiraKey( daoUtil.getString( nIndex++ ) );
132 site.setTheme( daoUtil.getBoolean( nIndex++ ) );
133 }
134
135 daoUtil.free( );
136 return site;
137
138 }
139
140
141
142
143 @Override
144 public void delete( int nKey, Plugin plugin )
145 {
146 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
147 daoUtil.setInt( 1, nKey );
148 daoUtil.executeUpdate( );
149 daoUtil.free( );
150 }
151
152
153
154
155 @Override
156 public void store( Site site, Plugin plugin )
157 {
158 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
159 int nIndex = 1;
160
161 daoUtil.setInt( nIndex++, site.getId( ) );
162 daoUtil.setString( nIndex++, site.getArtifactId( ) );
163 daoUtil.setInt( nIndex++, site.getIdCluster( ) );
164 daoUtil.setString( nIndex++, site.getScmUrl( ) );
165 daoUtil.setString( nIndex++, site.getName( ) );
166 daoUtil.setString( nIndex++, site.getDescription( ) );
167 daoUtil.setString( nIndex++, site.getJiraKey( ) );
168 daoUtil.setBoolean( nIndex++, site.isTheme( ) );
169
170 daoUtil.setInt( nIndex, site.getId( ) );
171
172 daoUtil.executeUpdate( );
173 daoUtil.free( );
174 }
175
176
177
178
179 @Override
180 public List<Site> selectSitesList( Plugin plugin )
181 {
182 List<Site> siteList = new ArrayList<Site>( );
183 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
184 daoUtil.executeQuery( );
185
186 while ( daoUtil.next( ) )
187 {
188 Siteugins/releaser/business/Site.html#Site">Site site = new Site( );
189 int nIndex = 1;
190
191 site.setId( daoUtil.getInt( nIndex++ ) );
192 site.setName( daoUtil.getString( nIndex++ ) );
193 site.setDescription( daoUtil.getString( nIndex++ ) );
194 site.setArtifactId( daoUtil.getString( nIndex++ ) );
195 site.setIdCluster( daoUtil.getInt( nIndex++ ) );
196 site.setCluster( daoUtil.getString( nIndex++ ) );
197 site.setScmUrl( daoUtil.getString( nIndex++ ) );
198 site.setJiraKey( daoUtil.getString( nIndex++ ) );
199 site.setTheme( daoUtil.getBoolean( nIndex++ ) );
200
201 siteList.add( site );
202 }
203
204 daoUtil.free( );
205 return siteList;
206 }
207
208
209
210
211 @Override
212 public List<Integer> selectIdSitesList( Plugin plugin )
213 {
214 List<Integer> siteList = new ArrayList<Integer>( );
215 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin );
216 daoUtil.executeQuery( );
217
218 while ( daoUtil.next( ) )
219 {
220 siteList.add( daoUtil.getInt( 1 ) );
221 }
222
223 daoUtil.free( );
224 return siteList;
225 }
226
227
228
229
230 @Override
231 public ReferenceList selectSitesReferenceList( Plugin plugin )
232 {
233 ReferenceList siteList = new ReferenceList( );
234 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
235 daoUtil.executeQuery( );
236
237 while ( daoUtil.next( ) )
238 {
239 siteList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 5 ) );
240 }
241
242 daoUtil.free( );
243 return siteList;
244 }
245
246
247
248
249 @Override
250 public List<Site> selectByCluster( int nClusterId, Plugin plugin )
251 {
252 List<Site> siteList = new ArrayList<Site>( );
253 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_CLUSTER, plugin );
254 daoUtil.setInt( 1, nClusterId );
255 daoUtil.executeQuery( );
256
257 while ( daoUtil.next( ) )
258 {
259 Siteugins/releaser/business/Site.html#Site">Site site = new Site( );
260 int nIndex = 1;
261
262 site.setId( daoUtil.getInt( nIndex++ ) );
263 site.setName( daoUtil.getString( nIndex++ ) );
264 site.setDescription( daoUtil.getString( nIndex++ ) );
265 site.setArtifactId( daoUtil.getString( nIndex++ ) );
266 site.setIdCluster( daoUtil.getInt( nIndex++ ) );
267 site.setCluster( daoUtil.getString( nIndex++ ) );
268 site.setScmUrl( daoUtil.getString( nIndex++ ) );
269 site.setJiraKey( daoUtil.getString( nIndex++ ) );
270 site.setTheme( daoUtil.getBoolean( nIndex++ ) );
271
272 siteList.add( site );
273 }
274
275 daoUtil.free( );
276 return siteList;
277 }
278
279
280
281
282 @Override
283 public String searchDuplicateSite( String siteName, String artifactId, String scmUrl, Plugin plugin )
284 {
285 String clusterName = null;
286 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_SEARCH_DUPLICATE_SITE, plugin );
287 daoUtil.setNString( 1, artifactId );
288 daoUtil.setNString( 2, siteName );
289 daoUtil.setNString( 3, scmUrl );
290 daoUtil.executeQuery( );
291
292 while ( daoUtil.next( ) )
293 {
294 int nIndex = 1;
295 if ( daoUtil != null && daoUtil.getString( 1 ) != null )
296 clusterName = daoUtil.getString( 1 );
297 }
298
299 daoUtil.free( );
300 return clusterName;
301 }
302 }