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.donneescomplementaires.dao.impl;
35
36 import fr.paris.lutece.plugins.dila.business.donneescomplementaires.dao.IComplementaryDataDAO;
37 import fr.paris.lutece.plugins.dila.business.donneescomplementaires.dto.ComplementaryDataDTO;
38 import fr.paris.lutece.plugins.dila.business.fichelocale.dto.XmlDTO;
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 ComplementaryDataDAO implements IComplementaryDataDAO, Serializable
52 {
53
54 private static final long serialVersionUID = -4819383738721769830L;
55 private static final String SQL_QUERY_NEW_PK = "SELECT max(id) FROM dila_complementary_data";
56 private static final String SQL_QUERY_SELECT_ALL = "SELECT donnee.id, xml.id_xml, xml.title"
57 + " FROM dila_complementary_data donnee, dila_xml xml WHERE donnee.xml_id = xml.id"
58 + " ORDER BY donnee.id ASC";
59 private static final String SQL_QUERY_INSERT = " INSERT INTO dila_complementary_data "
60 + "( id, bottom_block, column_block, xml_id, audience_id ) VALUES ( ?, ?, ?, ?, ? )";
61 private static final String SQL_QUERY_FIND_EXISTING_CARD = " SELECT id FROM dila_complementary_data "
62 + "WHERE xml_id = ?";
63 private static final String SQL_QUERY_FIND_BY_ID = " SELECT donnee.bottom_block, donnee.column_block, xml.id_xml, donnee.audience_id "
64 + " FROM dila_complementary_data donnee, dila_xml xml WHERE donnee.id = ? AND donnee.xml_id = xml.id";
65 private static final String SQL_QUERY_UPDATE = " UPDATE dila_complementary_data "
66 + "SET bottom_block = ?, column_block = ? WHERE id = ?";
67 private static final String SQL_QUERY_DELETE = " DELETE FROM dila_complementary_data WHERE id = ?";
68 private static final String SQL_QUERY_FIND_BY_CARD_AND_AUDIENCE = "SELECT id, bottom_block, column_block "
69 + " FROM dila_complementary_data WHERE xml_id = ? AND audience_id = ?";
70
71
72
73
74
75 private Long newPrimaryKey( )
76 {
77 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
78 daoUtil.executeQuery( );
79
80 Long nKey = 1L;
81
82 if ( daoUtil.next( ) )
83 {
84
85 nKey = daoUtil.getLong( 1 ) + 1L;
86 }
87
88 daoUtil.free( );
89
90 return nKey;
91 }
92
93 @Override
94 public List<ComplementaryDataDTO> findAll( )
95 {
96 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
97
98 daoUtil.executeQuery( );
99
100 List<ComplementaryDataDTO> result = new ArrayList<ComplementaryDataDTO>( );
101
102 while ( daoUtil.next( ) )
103 {
104 ComplementaryDataDTO donnee = new ComplementaryDataDTO( );
105 XmlDTO fiche = new XmlDTO( );
106
107 donnee.setId( daoUtil.getLong( 1 ) );
108 fiche.setIdXml( daoUtil.getString( 2 ) );
109 fiche.setTitle( daoUtil.getString( 3 ) );
110
111 donnee.setCard( fiche );
112
113 result.add( donnee );
114 }
115
116 daoUtil.free( );
117
118 return result;
119 }
120
121 @Override
122 public Long insert( ComplementaryDataDTO dto )
123 {
124 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
125
126 dto.setId( newPrimaryKey( ) );
127
128 daoUtil.setLong( 1, dto.getId( ) );
129 daoUtil.setString( 2, dto.getBottomBlock( ) );
130 daoUtil.setString( 3, dto.getColumnBlock( ) );
131 daoUtil.setLong( 4, dto.getCard( ).getId( ) );
132 daoUtil.setLong( 5, dto.getIdAudience( ) );
133
134 daoUtil.executeUpdate( );
135
136 daoUtil.free( );
137
138 return dto.getId( );
139 }
140
141 @Override
142 public boolean cardHasComplement( String id )
143 {
144 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_EXISTING_CARD, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
145
146 daoUtil.setString( 1, id );
147
148 daoUtil.executeQuery( );
149
150 boolean result = false;
151
152 if ( daoUtil.next( ) )
153 {
154 result = true;
155 }
156
157 daoUtil.free( );
158
159 return result;
160 }
161
162 @Override
163 public ComplementaryDataDTO findById( Long id )
164 {
165 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_ID, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
166 daoUtil.setLong( 1, id );
167
168 ComplementaryDataDTO result = null;
169
170 daoUtil.executeQuery( );
171
172 if ( daoUtil.next( ) )
173 {
174 result = new ComplementaryDataDTO( );
175
176 result.setId( id );
177 result.setBottomBlock( daoUtil.getString( 1 ) );
178 result.setColumnBlock( daoUtil.getString( 2 ) );
179 result.getCard( ).setIdXml( daoUtil.getString( 3 ) );
180 result.setIdAudience( daoUtil.getLong( 4 ) );
181 }
182
183 daoUtil.free( );
184
185 return result;
186 }
187
188 @Override
189 public void store( ComplementaryDataDTO donneesComplementaires )
190 {
191 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
192
193 daoUtil.setString( 1, donneesComplementaires.getBottomBlock( ) );
194 daoUtil.setString( 2, donneesComplementaires.getColumnBlock( ) );
195 daoUtil.setLong( 3, donneesComplementaires.getId( ) );
196
197 daoUtil.executeUpdate( );
198 daoUtil.free( );
199 }
200
201 @Override
202 public void delete( Long id )
203 {
204 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
205
206 daoUtil.setLong( 1, id );
207 daoUtil.executeUpdate( );
208 daoUtil.free( );
209 }
210
211 @Override
212 public ComplementaryDataDTO findByCardAndAudience( Long ficheId, Long audienceId )
213 {
214 ComplementaryDataDTO donneeComplementaireDTO = null;
215 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_CARD_AND_AUDIENCE,
216 PluginService.getPlugin( DilaPlugin.PLUGIN_NAME ) );
217 daoUtil.setLong( 1, ficheId );
218 daoUtil.setLong( 2, audienceId );
219 daoUtil.executeQuery( );
220
221 if ( daoUtil.next( ) )
222 {
223 donneeComplementaireDTO = new ComplementaryDataDTO( );
224 donneeComplementaireDTO.setId( daoUtil.getLong( 1 ) );
225 donneeComplementaireDTO.setBottomBlock( daoUtil.getString( 2 ) );
226 donneeComplementaireDTO.setColumnBlock( daoUtil.getString( 3 ) );
227 }
228
229 daoUtil.free( );
230
231 return donneeComplementaireDTO;
232 }
233 }