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.dila.business.fichelocale.dao.impl;
35
36 import fr.paris.lutece.plugins.dila.business.fichelocale.dao.ILocalFolderDAO;
37 import fr.paris.lutece.plugins.dila.business.fichelocale.dto.LocalDTO;
38 import fr.paris.lutece.plugins.dila.business.fichelocale.dto.LocalFolderDTO;
39 import fr.paris.lutece.plugins.dila.service.DilaPlugin;
40 import fr.paris.lutece.portal.service.plugin.PluginService;
41 import fr.paris.lutece.util.sql.DAOUtil;
42
43 import java.io.Serializable;
44 import java.util.ArrayList;
45 import java.util.List;
46
47
48
49
50
51 public class LocalFolderDAO implements ILocalFolderDAO, Serializable
52 {
53
54 private static final long serialVersionUID = -4915475113278829044L;
55
56
57 private static final String SQL_QUERY_NEW_PK = "SELECT max(id) FROM dila_local_folder";
58 private static final String SQL_QUERY_INSERT = " INSERT INTO dila_local_folder "
59 + "( id, parent_theme_id, sibling_folder_id, position, presentation, local_id ) "
60 + " VALUES ( ?, ? ,?, ?, ?, ? )";
61 private static final String SQL_QUERY_SELECT_FOLDER_ID_BY_LOCAL_ID = "SELECT "
62 + "id FROM dila_local_folder WHERE local_id = ?";
63 private static final String SQL_QUERY_DELETE = "DELETE FROM dila_local_folder WHERE id = ?";
64 private static final String SQL_QUERY_SELECT_FOLDER_BY_LOCAL_ID = "SELECT "
65 + "dossier.id, dossier.parent_theme_id, dossier.sibling_folder_id, dossier.position, dossier.presentation, local.title, local.author, local.audience_id "
66 + "FROM dila_local_folder dossier, dila_local local "
67 + "WHERE dossier.local_id = ? AND dossier.local_id = local.id";
68 private static final String SQL_QUERY_UPDATE = " UPDATE dila_local_folder"
69 + " SET parent_theme_id = ?, sibling_folder_id = ?, position = ?, presentation = ?" + " WHERE id = ?";
70 private static final String SQL_QUERY_FIND_FOLDERS_BY_PARENT_ID = "SELECT l.id, l.title, d.sibling_folder_id, d.position"
71 + " FROM dila_local_folder d JOIN dila_local l ON d.local_id = l.id" + " WHERE d.parent_theme_id = ?";
72
73
74
75
76
77 private Long newPrimaryKey( )
78 {
79 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
80 daoUtil.executeQuery( );
81
82 Long nKey = 1L;
83
84 if ( daoUtil.next( ) )
85 {
86
87 nKey = daoUtil.getLong( 1 ) + 1L;
88 }
89
90 daoUtil.free( );
91
92 return nKey;
93 }
94
95 @Override
96 public Long create( LocalFolderDTO folder )
97 {
98 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
99
100 folder.setId( newPrimaryKey( ) );
101
102 daoUtil.setLong( 1, folder.getId( ) );
103 daoUtil.setString( 2, folder.getParentThemeId( ) );
104 daoUtil.setString( 3, folder.getSiblingFolderId( ) );
105
106 if ( folder.getPosition( ) != null )
107 {
108 daoUtil.setInt( 4, folder.getPosition( ) );
109 }
110 else
111 {
112 daoUtil.setIntNull( 4 );
113 }
114
115 daoUtil.setString( 5, folder.getPresentation( ) );
116 daoUtil.setLong( 6, folder.getLocalDTO( ).getId( ) );
117
118 daoUtil.executeUpdate( );
119 daoUtil.free( );
120
121 return folder.getId( );
122 }
123
124 @Override
125 public Long findFolderIdByLocalId( String localId )
126 {
127 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_FOLDER_ID_BY_LOCAL_ID,
128 PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
129 daoUtil.setString( 1, localId );
130 daoUtil.executeQuery( );
131
132 Long result = null;
133
134 if ( daoUtil.next( ) )
135 {
136 result = daoUtil.getLong( 1 );
137 }
138
139 daoUtil.free( );
140
141 return result;
142 }
143
144 @Override
145 public void delete( Long dossierLocalId )
146 {
147 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
148 daoUtil.setLong( 1, dossierLocalId );
149 daoUtil.executeUpdate( );
150
151 daoUtil.free( );
152 }
153
154 @Override
155 public LocalFolderDTO findFolderByLocalId( Long localId )
156 {
157 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_FOLDER_BY_LOCAL_ID,
158 PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
159 daoUtil.setLong( 1, localId );
160 daoUtil.executeQuery( );
161
162 LocalFolderDTO folder = null;
163
164 if ( daoUtil.next( ) )
165 {
166 folder = new LocalFolderDTO( );
167 folder.getLocalDTO( ).setId( localId );
168
169 folder.setId( daoUtil.getLong( 1 ) );
170 folder.setParentThemeId( daoUtil.getString( 2 ) );
171
172 if ( daoUtil.getString( 3 ) != null )
173 {
174 folder.setSiblingFolderId( daoUtil.getString( 3 ) );
175 folder.setPosition( daoUtil.getInt( 4 ) );
176 }
177
178 folder.setPresentation( daoUtil.getString( 5 ) );
179
180 folder.getLocalDTO( ).setTitle( daoUtil.getString( 6 ) );
181 folder.getLocalDTO( ).setAuthor( daoUtil.getString( 7 ) );
182 folder.getLocalDTO( ).setIdAudience( daoUtil.getLong( 8 ) );
183 }
184
185 daoUtil.free( );
186
187 return folder;
188 }
189
190 @Override
191 public void store( LocalFolderDTO folder )
192 {
193 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
194
195 daoUtil.setString( 1, folder.getParentThemeId( ) );
196 daoUtil.setString( 2, folder.getSiblingFolderId( ) );
197
198 if ( folder.getPosition( ) != null )
199 {
200 daoUtil.setInt( 3, folder.getPosition( ) );
201 }
202 else
203 {
204 daoUtil.setIntNull( 3 );
205 }
206
207 daoUtil.setString( 4, folder.getPresentation( ) );
208 daoUtil.setLong( 5, folder.getId( ) );
209
210 daoUtil.executeUpdate( );
211 daoUtil.free( );
212 }
213
214 @Override
215 public List<LocalFolderDTO> findLocalFoldersByParentTheme( String parentId )
216 {
217 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FOLDERS_BY_PARENT_ID,
218 PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
219 daoUtil.setString( 1, parentId );
220 daoUtil.executeQuery( );
221
222 List<LocalFolderDTO> resultList = new ArrayList<LocalFolderDTO>( );
223
224 while ( daoUtil.next( ) )
225 {
226 LocalFolderDTO localFolder = new LocalFolderDTO( );
227 LocalDTO local = new LocalDTO( );
228 local.setId( daoUtil.getLong( 1 ) );
229 local.setTitle( daoUtil.getString( 2 ) );
230 localFolder.setLocalDTO( local );
231 localFolder.setSiblingFolderId( daoUtil.getString( 3 ) );
232 localFolder.setPosition( daoUtil.getInt( 4 ) );
233 localFolder.setParentThemeId( parentId );
234 resultList.add( localFolder );
235 }
236
237 daoUtil.free( );
238
239 return resultList;
240 }
241 }