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.library.business;
35
36 import fr.paris.lutece.plugins.library.business.LibraryMapping.AttributeAssociation;
37 import fr.paris.lutece.portal.service.plugin.Plugin;
38 import fr.paris.lutece.util.sql.DAOUtil;
39
40 import java.util.ArrayList;
41 import java.util.Collection;
42
43
44 public class LibraryMappingDAO implements ILibraryMappingDAO
45 {
46 private static final String SQL_QUERY_NEW_PK = " SELECT max( id_mapping ) FROM library_mapping ";
47 private static final String SQL_QUERY_REMOVE_MAPPING = "DELETE FROM library_mapping WHERE id_mapping = ?";
48 private static final String SQL_QUERY_REMOVE_MAPPING_ATTRIBUTES = "DELETE FROM library_mapping_attribute WHERE id_mapping = ?";
49 private static final String SQL_QUERY_INSERT_MAPPING = "INSERT INTO library_mapping (id_mapping ,id_media ,code_document_type) VALUES ( ? , ? , ? )";
50 private static final String SQL_QUERY_INSERT_MAPPING_ATTRIBUTE = "INSERT INTO library_mapping_attribute (id_mapping ,id_media_attribute ,id_document_attribute) VALUES ( ? , ? , ? )";
51 private static final String SQL_QUERY_SELECT_MAPPING = "SELECT a.id_mapping ,a.id_media ,a.code_document_type, b.id_media_attribute ,b.id_document_attribute FROM library_mapping a LEFT JOIN library_mapping_attribute b ON a.id_mapping= b.id_mapping WHERE a.id_mapping = ? ";
52 private static final String SQL_QUERY_SELECT_MAPPING_BY_MEDIA = "SELECT * FROM library_mapping WHERE id_media = ? ";
53 private static final String SQL_QUERY_SELECT_MAPPING_ATTRIBUTES_BY_MAPPING_ID = "SELECT * FROM library_mapping_attribute WHERE id_mapping = ? ";
54 private static final String SQL_QUERY_UPDATE_MAPPING = "UPDATE library_mapping SET id_media=?, code_document_type=? WHERE id_mapping=?";
55 private static final String SQL_QUERY_REMOVE_MAPPING_ATTRIBUTE_FROM_ID = "DELETE FROM library_mapping_attribute WHERE id_media_attribute = ?";
56
57 public void delete( int nMappingId, Plugin plugin )
58 {
59
60 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_REMOVE_MAPPING_ATTRIBUTES, plugin );
61 daoUtil.setInt( 1, nMappingId );
62 daoUtil.executeUpdate( );
63 daoUtil.free( );
64
65
66 daoUtil = new DAOUtil( SQL_QUERY_REMOVE_MAPPING, plugin );
67 daoUtil.setInt( 1, nMappingId );
68 daoUtil.executeUpdate( );
69 daoUtil.free( );
70 }
71
72 public void insert( LibraryMapping mapping, Plugin plugin )
73 {
74 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_MAPPING, plugin );
75 int nMappingId = newPrimaryKey( plugin );
76
77 daoUtil.setInt( 1, nMappingId );
78 daoUtil.setInt( 2, mapping.getIdMedia( ) );
79 daoUtil.setString( 3, mapping.getCodeDocumentType( ) );
80 daoUtil.executeUpdate( );
81 daoUtil.free( );
82
83 if ( mapping.getAttributeAssociationList( ) == null )
84 {
85 return;
86 }
87
88
89 for ( AttributeAssociation association : mapping.getAttributeAssociationList( ) )
90 {
91 daoUtil = new DAOUtil( SQL_QUERY_INSERT_MAPPING_ATTRIBUTE, plugin );
92 daoUtil.setInt( 1, nMappingId );
93 daoUtil.setInt( 2, association.getIdMediaAttribute( ) );
94 daoUtil.setInt( 3, association.getIdDocumentAttribute( ) );
95 daoUtil.executeUpdate( );
96 daoUtil.free( );
97 }
98 }
99
100 public LibraryMapping load( int nMappingId, Plugin plugin )
101 {
102 LibraryMapping mapping = null;
103 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_MAPPING, plugin );
104 daoUtil.setInt( 1, nMappingId );
105 daoUtil.executeQuery( );
106
107 if ( daoUtil.next( ) )
108 {
109 mapping = new LibraryMapping( );
110 mapping.setIdMapping( daoUtil.getInt( 1 ) );
111 mapping.setIdMedia( daoUtil.getInt( 2 ) );
112 mapping.setCodeDocumentType( daoUtil.getString( 3 ) );
113
114
115 mapping.addAttributeAssociation( daoUtil.getInt( 4 ), daoUtil.getInt( 5 ) );
116 }
117
118
119 while ( daoUtil.next( ) )
120 {
121 mapping.addAttributeAssociation( daoUtil.getInt( 4 ), daoUtil.getInt( 5 ) );
122 }
123
124 daoUtil.free( );
125
126 return mapping;
127 }
128
129 public Collection<LibraryMapping> selectByMedia( int nMediaId, Plugin plugin )
130 {
131 Collection<LibraryMapping> colMapping = new ArrayList<LibraryMapping>( );
132
133 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_MAPPING_BY_MEDIA, plugin );
134 daoUtil.setInt( 1, nMediaId );
135 daoUtil.executeQuery( );
136
137 while ( daoUtil.next( ) )
138 {
139 LibraryMapping/business/LibraryMapping.html#LibraryMapping">LibraryMapping mapping = new LibraryMapping( );
140 mapping.setIdMapping( daoUtil.getInt( 1 ) );
141 mapping.setIdMedia( daoUtil.getInt( 2 ) );
142 mapping.setCodeDocumentType( daoUtil.getString( 3 ) );
143
144
145 DAOUtil daoUtilAssociation = new DAOUtil( SQL_QUERY_SELECT_MAPPING_ATTRIBUTES_BY_MAPPING_ID, plugin );
146 daoUtilAssociation.setInt( 1, mapping.getIdMapping( ) );
147 daoUtilAssociation.executeQuery( );
148
149 while ( daoUtilAssociation.next( ) )
150 {
151 mapping.addAttributeAssociation( daoUtilAssociation.getInt( 2 ), daoUtilAssociation.getInt( 3 ) );
152 }
153
154 daoUtilAssociation.free( );
155
156 colMapping.add( mapping );
157 }
158
159 daoUtil.free( );
160
161 return colMapping;
162 }
163
164 public void store( LibraryMapping mapping, Plugin plugin )
165 {
166
167 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_REMOVE_MAPPING_ATTRIBUTES, plugin );
168 daoUtil.setInt( 1, mapping.getIdMapping( ) );
169 daoUtil.executeUpdate( );
170 daoUtil.free( );
171
172
173 daoUtil = new DAOUtil( SQL_QUERY_UPDATE_MAPPING, plugin );
174 daoUtil.setInt( 1, mapping.getIdMedia( ) );
175 daoUtil.setString( 2, mapping.getCodeDocumentType( ) );
176 daoUtil.setInt( 3, mapping.getIdMapping( ) );
177 daoUtil.executeUpdate( );
178 daoUtil.free( );
179
180 if ( mapping.getAttributeAssociationList( ) == null )
181 {
182 return;
183 }
184
185
186
187 for ( AttributeAssociation association : mapping.getAttributeAssociationList( ) )
188 {
189 daoUtil = new DAOUtil( SQL_QUERY_INSERT_MAPPING_ATTRIBUTE, plugin );
190 daoUtil.setInt( 1, mapping.getIdMapping( ) );
191 daoUtil.setInt( 2, association.getIdMediaAttribute( ) );
192 daoUtil.setInt( 3, association.getIdDocumentAttribute( ) );
193 daoUtil.executeUpdate( );
194 daoUtil.free( );
195 }
196 }
197
198
199
200
201
202 public int newPrimaryKey( Plugin plugin )
203 {
204 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
205 daoUtil.executeQuery( );
206
207 int nKey;
208
209 if ( !daoUtil.next( ) )
210 {
211
212 nKey = 1;
213 }
214
215 nKey = daoUtil.getInt( 1 ) + 1;
216 daoUtil.free( );
217
218 return nKey;
219 }
220
221 public void deleteAttributeAssociation( int nAttributeId, Plugin plugin )
222 {
223
224 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_REMOVE_MAPPING_ATTRIBUTE_FROM_ID, plugin );
225 daoUtil.setInt( 1, nAttributeId );
226 daoUtil.executeUpdate( );
227 daoUtil.free( );
228 }
229 }