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