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.portal.business.mail;
35
36 import fr.paris.lutece.portal.service.mail.MailItem;
37 import fr.paris.lutece.portal.service.util.AppLogService;
38 import fr.paris.lutece.util.mail.FileAttachment;
39 import fr.paris.lutece.util.mail.UrlAttachment;
40 import fr.paris.lutece.util.sql.DAOUtil;
41 import fr.paris.lutece.util.sql.Transaction;
42 import fr.paris.lutece.util.sql.TransactionManager;
43
44 import java.io.ByteArrayOutputStream;
45 import java.io.IOException;
46 import java.io.InputStream;
47 import java.io.ObjectOutputStream;
48 import java.net.URL;
49 import java.sql.Statement;
50 import java.util.ArrayList;
51
52 import org.apache.commons.io.serialization.ValidatingObjectInputStream;
53
54
55
56
57 public class MailItemQueueDAO implements IMailItemQueueDAO
58 {
59 private static final String SQL_QUERY_SELECT_NEXT_MAIL_ITEM_QUEUE_ID = "SELECT min(id_mail_queue) FROM core_mail_queue WHERE is_locked=0";
60 private static final String SQL_QUERY_SELECT_COUNT = "SELECT COUNT(id_mail_queue) FROM core_mail_queue";
61 private static final String SQL_QUERY_LOAD_MAIL_ITEM = "SELECT id_mail_queue,mail_item FROM core_mail_item WHERE id_mail_queue=? ";
62 private static final String SQL_QUERY_INSERT = " INSERT INTO core_mail_queue( id_mail_queue ) VALUES( DEFAULT ) ";
63 private static final String SQL_QUERY_INSERT_MAIL_ITEM = " INSERT INTO core_mail_item(id_mail_queue,mail_item) VALUES(?,?) ";
64 private static final String SQL_QUERY_LOCK_MAIL_ITEM = " UPDATE core_mail_queue SET is_locked=1 WHERE id_mail_queue= ? ";
65 private static final String SQL_QUERY_DELETE = " DELETE FROM core_mail_queue WHERE id_mail_queue = ?";
66 private static final String SQL_QUERY_DELETE_MAIL_ITEM = " DELETE FROM core_mail_item WHERE id_mail_queue = ?";
67
68
69
70
71
72
73 @Override
74 public int nextMailItemQueueId( )
75 {
76 int nIdMailItemQueue = -1;
77 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_NEXT_MAIL_ITEM_QUEUE_ID ) )
78 {
79
80 daoUtil.executeQuery( );
81
82 if ( daoUtil.next( ) )
83 {
84 nIdMailItemQueue = daoUtil.getInt( 1 );
85 }
86
87 }
88
89 return nIdMailItemQueue;
90 }
91
92
93
94
95
96
97
98 @Override
99 public void lockMailItemQueue( int nIdMailItemQueue )
100 {
101 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_LOCK_MAIL_ITEM ) )
102 {
103 daoUtil.setInt( 1, nIdMailItemQueue );
104 daoUtil.executeUpdate( );
105 }
106 }
107
108
109
110
111
112
113
114 @Override
115 public synchronized void insert( MailItemQueue mailItemQueue )
116 {
117 try
118 {
119 ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream( );
120 ObjectOutputStream objectOutputStream;
121 objectOutputStream = new ObjectOutputStream( byteArrayOutputStream );
122 objectOutputStream.writeObject( mailItemQueue.getMailItem( ) );
123 objectOutputStream.close( );
124 byteArrayOutputStream.close( );
125
126 doInsertMail( mailItemQueue, byteArrayOutputStream );
127 }
128 catch( Exception e )
129 {
130 AppLogService.error( e );
131 }
132 }
133
134 private void doInsertMail( MailItemQueue mailItemQueue, ByteArrayOutputStream byteArrayOutputStream )
135 {
136 TransactionManager.beginTransaction( null );
137 try ( DAOUtilOUtil.html#DAOUtil">DAOUtil daoUtilKey = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) ;
138 DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_MAIL_ITEM ) )
139 {
140 daoUtilKey.executeUpdate( );
141 daoUtilKey.nextGeneratedKey( );
142 int nNewPrimaryKey = daoUtilKey.getGeneratedKeyInt( 1 );
143 mailItemQueue.setIdMailItemQueue( nNewPrimaryKey );
144 daoUtil.setInt( 1, nNewPrimaryKey );
145 daoUtil.setBytes( 2, byteArrayOutputStream.toByteArray( ) );
146 daoUtil.executeUpdate( );
147 TransactionManager.commitTransaction( null );
148 }
149 catch( Exception e )
150 {
151 TransactionManager.rollBack( null );
152 AppLogService.error( e );
153 }
154 }
155
156
157
158
159
160
161
162
163 @Override
164 public MailItemQueue load( int nIdMailItemQueue )
165 {
166 MailItemQueue mailItemQueue = null;
167 MailItem mailItem = null;
168 InputStream inputStream;
169 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_LOAD_MAIL_ITEM ) )
170 {
171 daoUtil.setInt( 1, nIdMailItemQueue );
172 daoUtil.executeQuery( );
173
174 if ( daoUtil.next( ) )
175 {
176 mailItemQueue = new MailItemQueue( );
177 mailItemQueue.setIdMailItemQueue( daoUtil.getInt( 1 ) );
178 inputStream = daoUtil.getBinaryStream( 2 );
179
180 try ( ValidatingObjectInputStream objectInputStream = new ValidatingObjectInputStream( inputStream ) )
181 {
182 objectInputStream.accept( MailItem.class, ArrayList.class, byte [ ].class, FileAttachment.class, UrlAttachment.class,
183 FileAttachment [ ].class, UrlAttachment [ ].class, URL.class );
184 mailItem = (MailItem) objectInputStream.readObject( );
185 }
186 catch( ClassNotFoundException | IOException e )
187 {
188 AppLogService.error( e.getMessage( ), e );
189 }
190 finally
191 {
192 try
193 {
194 inputStream.close( );
195 }
196 catch( IOException e )
197 {
198 AppLogService.error( e.getMessage( ), e );
199 }
200 }
201
202 mailItemQueue.setMailItem( mailItem );
203 }
204
205 }
206
207 return mailItemQueue;
208 }
209
210
211
212
213
214
215
216 @Override
217 public void delete( int nIdMailItemQueue )
218 {
219 Transactiontion.html#Transaction">Transaction transaction = new Transaction( );
220
221 try
222 {
223 transaction.prepareStatement( SQL_QUERY_DELETE_MAIL_ITEM );
224 transaction.getStatement( ).setInt( 1, nIdMailItemQueue );
225 transaction.executeStatement( );
226 transaction.prepareStatement( SQL_QUERY_DELETE );
227 transaction.getStatement( ).setInt( 1, nIdMailItemQueue );
228 transaction.executeStatement( );
229 transaction.commit( );
230 }
231 catch( Exception e )
232 {
233 transaction.rollback( e );
234 AppLogService.error( e );
235 }
236 }
237
238
239
240
241 @Override
242 public int getCountMailItem( )
243 {
244 int nCount = 0;
245 try ( DAOUtil/DAOUtil.html#DAOUtil">DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_COUNT ) )
246 {
247 daoUtil.executeQuery( );
248
249 if ( daoUtil.next( ) )
250 {
251 nCount = daoUtil.getInt( 1 );
252 }
253
254 }
255
256 return nCount;
257 }
258 }