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.ILocalCardDAO;
37 import fr.paris.lutece.plugins.dila.business.fichelocale.dto.LocalCardDTO;
38 import fr.paris.lutece.plugins.dila.business.fichelocale.dto.LocalDTO;
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 LocalCardDAO implements ILocalCardDAO, Serializable
52 {
53
54 private static final long serialVersionUID = 1687246174844997810L;
55
56
57 private static final String SQL_QUERY_NEW_PK = "SELECT max(id) FROM dila_local_card";
58 private static final String SQL_QUERY_INSERT = " INSERT INTO dila_local_card "
59 + "( id, parent_folder_id, sibling_card_id, position, local_id ) " + " VALUES ( ?, ? ,?, ?, ?)";
60 private static final String SQL_QUERY_SELECT_CARD_ID_BY_LOCAL_ID = "SELECT "
61 + "id FROM dila_local_card WHERE local_id = ?";
62 private static final String SQL_QUERY_SELECT_CARD_BY_LOCAL_ID = "SELECT "
63 + "fiche.id, fiche.parent_folder_id, fiche.sibling_card_id, fiche.position, local.title, local.author, local.audience_id FROM dila_local_card fiche, dila_local local "
64 + "WHERE fiche.local_id = ? AND fiche.local_id = local.id";
65 private static final String SQL_QUERY_DELETE = "DELETE FROM dila_local_card WHERE id = ?";
66 private static final String SQL_QUERY_UPDATE = " UPDATE dila_local_card "
67 + "set parent_folder_id = ?, sibling_card_id = ?, position = ? WHERE id = ?";
68 private static final String SQL_QUERY_SELECT_CARD_ID_BY_PARENT_ID = "SELECT "
69 + "id FROM dila_local_card WHERE parent_folder_id = ?";
70 private static final String SQL_QUERY_FIND_CARDS_BY_PARENT_ID = "SELECT l.id, l.title, f.sibling_card_id, f.position"
71 + " FROM dila_local_card f JOIN dila_local l ON f.local_id = l.id" + " WHERE f.parent_folder_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 insert( LocalCardDTO card )
97 {
98 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
99
100 card.setId( newPrimaryKey( ) );
101
102 daoUtil.setLong( 1, card.getId( ) );
103 daoUtil.setString( 2, card.getParentFolderId( ) );
104 daoUtil.setString( 3, card.getSiblingCardId( ) );
105
106 if ( card.getPosition( ) != null )
107 {
108 daoUtil.setInt( 4, card.getPosition( ) );
109 }
110 else
111 {
112 daoUtil.setIntNull( 4 );
113 }
114
115 daoUtil.setLong( 5, card.getLocalDTO( ).getId( ) );
116
117 daoUtil.executeUpdate( );
118 daoUtil.free( );
119
120 return card.getId( );
121 }
122
123 @Override
124 public Long findCardIdByLocalId( String localId )
125 {
126 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_CARD_ID_BY_LOCAL_ID,
127 PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
128 daoUtil.setString( 1, localId );
129 daoUtil.executeQuery( );
130
131 Long result = null;
132
133 if ( daoUtil.next( ) )
134 {
135 result = daoUtil.getLong( 1 );
136 }
137
138 daoUtil.free( );
139
140 return result;
141 }
142
143 @Override
144 public void delete( Long ficheLocalId )
145 {
146 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
147 daoUtil.setLong( 1, ficheLocalId );
148 daoUtil.executeUpdate( );
149 daoUtil.free( );
150 }
151
152 @Override
153 public LocalCardDTO findCardByLocalId( Long idLocal )
154 {
155 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_CARD_BY_LOCAL_ID,
156 PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
157 daoUtil.setLong( 1, idLocal );
158 daoUtil.executeQuery( );
159
160 LocalCardDTO card = null;
161
162 if ( daoUtil.next( ) )
163 {
164 card = new LocalCardDTO( );
165 card.getLocalDTO( ).setId( idLocal );
166 card.setId( daoUtil.getLong( 1 ) );
167 card.setParentFolderId( daoUtil.getString( 2 ) );
168
169 if ( daoUtil.getString( 3 ) != null )
170 {
171 card.setSiblingCardId( daoUtil.getString( 3 ) );
172 card.setPosition( daoUtil.getInt( 4 ) );
173 }
174
175 card.getLocalDTO( ).setTitle( daoUtil.getString( 5 ) );
176 card.getLocalDTO( ).setAuthor( daoUtil.getString( 6 ) );
177 card.getLocalDTO( ).setIdAudience( daoUtil.getLong( 7 ) );
178 }
179
180 daoUtil.free( );
181
182 return card;
183 }
184
185 @Override
186 public void store( LocalCardDTO card )
187 {
188 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
189 daoUtil.setString( 1, card.getParentFolderId( ) );
190 daoUtil.setString( 2, card.getSiblingCardId( ) );
191
192 if ( card.getPosition( ) != null )
193 {
194 daoUtil.setInt( 3, card.getPosition( ) );
195 }
196 else
197 {
198 daoUtil.setIntNull( 3 );
199 }
200
201 daoUtil.setLong( 4, card.getId( ) );
202
203 daoUtil.executeUpdate( );
204 daoUtil.free( );
205 }
206
207 @Override
208 public boolean isCardWithParentId( String localId )
209 {
210 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_CARD_ID_BY_PARENT_ID,
211 PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
212 daoUtil.setString( 1, localId );
213 daoUtil.executeQuery( );
214
215 boolean result = false;
216
217 if ( daoUtil.next( ) )
218 {
219 result = true;
220 }
221
222 daoUtil.free( );
223
224 return result;
225 }
226
227 @Override
228 public List<LocalCardDTO> findLocalCardsByParentFolder( String parentId )
229 {
230 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_CARDS_BY_PARENT_ID,
231 PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
232 daoUtil.setString( 1, parentId );
233 daoUtil.executeQuery( );
234
235 List<LocalCardDTO> resultList = new ArrayList<LocalCardDTO>( );
236
237 while ( daoUtil.next( ) )
238 {
239 LocalCardDTO localCard = new LocalCardDTO( );
240 LocalDTO local = new LocalDTO( );
241 local.setId( daoUtil.getLong( 1 ) );
242 local.setTitle( daoUtil.getString( 2 ) );
243 localCard.setLocalDTO( local );
244 localCard.setSiblingCardId( daoUtil.getString( 3 ) );
245 localCard.setPosition( daoUtil.getInt( 4 ) );
246 resultList.add( localCard );
247 }
248
249 daoUtil.free( );
250
251 return resultList;
252 }
253 }