1   package ch.ige.edossier.web.server.dao;
2   
3   import java.sql.*;
4   import java.util.ArrayList;
5   import java.util.List;
6   import ch.ige.edossier.util.DBHelper;
7   import ch.ige.edossier.web.vo.DossierVO;
8   import ch.ige.edossier.web.vo.ImageVO;
9   import oracle.jdbc.OracleResultSet;
10  import oracle.sql.BLOB;
11  import oracle.sql.CLOB;
12  
13  /**
14   * B32.03 - eDossier-Interceptions - Diplomarbeit an der Software-Schule Schweiz<br>
15   * Data Access Objekt für die Tabelle DOSSIER
16   * <p>
17   * Copyright (c) 2004, Eidgenössisches Institut für Geistiges Eigentum
18   * @author    Anita Rueegsegger, Marc Bouquet
19   * @version   $Id: DossierDAO.java,v 1.18 2004/11/10 19:51:57 ruegsegger Exp $
20   */
21  public class DossierDAO
22  {
23    /**
24     * Fügt einen Datensatz in die Tabelle DOSSIER ein.
25     * @param dossierVO Value-Object DossierVO
26     * @exception Exception Unerwarteter SQL Fehler oder Konvertierungsfehler
27     */
28    public void insert( DossierVO dossierVO ) throws Exception
29    {
30      Connection con = null;
31      PreparedStatement ps = null;
32      PreparedStatement ps1 = null;
33      PreparedStatement ps2 = null;
34      ResultSet rs = null;
35      BLOB blob = null;
36  
37      try
38      {
39        con = DBHelper.getInstance().getEsolutionConnection();
40        // Auto commit must be turned off.
41        con.setAutoCommit( false );
42        // Insert Values and preparing blob-column with empty-blob
43        ps = con.prepareStatement(
44            "INSERT INTO edossier.dossier(" +
45            "dossier_id, " +
46            "account_id, " +
47            "tmtype_id, " +
48            "language_cd, " +
49            "request_nr, " +
50            "guard_nr, " +
51            "description, " +
52            "begin_dat, " +
53            "reference, " +
54            "colorclaim, " +
55            "image) " +
56            "VALUES(?,?,?,?,?,?,?,?,?,?,EMPTY_BLOB())"
57            );
58  
59        ps.setInt( 1, dossierVO.getDossierId() );
60        ps.setInt( 2, dossierVO.getAccountId() );
61        ps.setInt( 3, dossierVO.getTmtypeId() );
62        ps.setInt( 4, dossierVO.getLanguageCd() );
63        ps.setString( 5, dossierVO.getRequestNr() );
64        ps.setString( 6, dossierVO.getGuardNr() );
65        ps.setString( 7, dossierVO.getDescription() );
66        ps.setDate( 8, dossierVO.getBeginDat() );
67        ps.setString( 9, dossierVO.getReference() );
68        ps.setString( 10, dossierVO.getColorclaim() );
69  
70        if( ps.executeUpdate() != 1 )
71        {
72          throw new SQLException();
73        }
74  
75        // Select the blob column for update.
76        // An OracleResultSet will be used.
77        ps2 = con.prepareStatement( "SELECT dossier_id, image FROM dossier WHERE dossier_id = ? FOR UPDATE" );
78        ps2.setInt( 1, dossierVO.getDossierId() );
79  
80        rs = ( OracleResultSet )ps2.executeQuery();
81        // Throw an exception if the blob reference was not retrieved.
82        if( !rs.next() )
83        {
84          throw new SQLException( "Unable to retrieve BLOB reference." );
85        }
86        blob = ( oracle.sql.BLOB )rs.getBlob( "image" );
87  
88        //update blob
89        ps1 = con.prepareStatement( "UPDATE dossier set image = ? WHERE dossier_id = ? " );
90        ps1.setInt( 2, dossierVO.getDossierId() );
91        if( dossierVO.getImageVO() == null || dossierVO.getImageVO().getImage() == null )
92        {
93          ps1.setNull( 1, java.sql.Types.BLOB );
94        }
95        else
96        {
97          blob.putBytes( 1, dossierVO.getImageVO().getImage() );
98          ps1.setBlob( 1, blob );
99        }
100       con.commit();
101       ps1.executeUpdate();
102     }
103     finally
104     {
105       if( rs != null )
106       {
107         rs.close();
108       }
109       if( ps1 != null )
110       {
111         ps1.close();
112       }
113       if( ps2 != null )
114       {
115         ps2.close();
116       }
117       if( con != null && ps != null )
118       {
119         DBHelper.getInstance().close( con, ps );
120       }
121     }
122   }
123 
124   /**
125    * Selektiert einen Datensatz der Tabelle DOSSIER.
126    * @param dossierId int Nummer zum selektieren des Datensatzes.
127    * @return DossierVO Value-Object
128    * @throws Exception Unerwarteter Fehler
129    */
130   public DossierVO select( int dossierId ) throws Exception
131   {
132     Connection con = null;
133     PreparedStatement ps = null;
134     ResultSet rs = null;
135     DossierVO vo = null;
136 
137     try
138     {
139       con = DBHelper.getInstance().getEsolutionConnection();
140       ps = con.prepareStatement( getSelectStatement() + "where dossier_id = ?" );
141 
142       ps.setInt( 1, dossierId );
143 
144       rs = ps.executeQuery();
145       if( rs.next() )
146       {
147         vo = fillVO( rs );
148       }
149       return vo;
150     }
151     finally
152     {
153       DBHelper.getInstance().close( con, ps );
154     }
155   }
156 
157   /**
158    * Diese Methode gibt ein Image-Value-Object zurück, anhand der Dossier_Id
159    * @param dossierId int Nummer zum selektieren des Datensatzes
160    * @throws Exception Unerwarteter Fehler
161    * @return ImageVO Value-Object
162    */
163   public ImageVO selectImage( int dossierId ) throws Exception
164   {
165     Connection con = null;
166     PreparedStatement ps = null;
167     ResultSet rs = null;
168     String query = null;
169     ImageVO imageVO = null;
170     Blob imageBlob = null;
171 
172     try
173     {
174       query = "SELECT image FROM edossier.dossier WHERE dossier_id = ?";
175       con = DBHelper.getInstance().getEsolutionConnection();
176       ps = con.prepareStatement( query );
177       ps.setInt( 1, dossierId );
178 
179       rs = ps.executeQuery();
180       if( rs.next() )
181       {
182         imageVO = new ImageVO();
183 
184         Object obj = rs.getObject( "image" );
185         if( obj == null )
186         {
187           imageVO.setImage( null );
188         }
189         else
190         {
191           // Blob lesen
192           imageBlob = ( java.sql.Blob )rs.getBlob( "image" );
193           imageVO.setImage( imageBlob.getBytes( new Long( "1" ).longValue(), new Long( imageBlob.length() ).intValue() ) );
194         }
195       }
196       return imageVO;
197     }
198     finally
199     {
200       DBHelper.getInstance().close( con, ps );
201     }
202   }
203 
204   /**
205    * Selektierte alle Einträge eines Accounts der Tablle DOSSIER.
206    * @param accountId int Nummer zum selektieren der Datensätze
207    * @return List mit Value Objects DossierVO
208    * @throws Exception Unerwarteter SQL-Fehler oder I/O-Fehler(Stream)
209    */
210   public List selectByAccount( int accountId ) throws Exception
211   {
212     Connection con = null;
213     PreparedStatement ps = null;
214     ResultSet rs = null;
215     List list = new ArrayList();
216     DossierVO vo = null;
217 
218     try
219     {
220       con = DBHelper.getInstance().getEsolutionConnection();
221 
222       ps = con.prepareStatement( "select a.dossier_id, a.tmtype_id, a.account_id, a.language_cd, " +
223                                  "a.request_nr, a.guard_nr, a.description, a.begin_dat, a.reference, a.created_dat, a.changed_dat, " +
224                                  "(select min(state_id) from interception where dossier_id = a.dossier_id) as state_id, " +
225                                  "(select min(expire_dat) from interception where dossier_id = a.dossier_id) as expire_dat " +
226                                  "from edossier.dossier a " +
227                                  "where account_id = ? order by expire_dat" );
228 
229       ps.setInt( 1, accountId );
230       rs = ps.executeQuery();
231 
232       while( rs.next() )
233       {
234         vo = new DossierVO();
235 
236         vo.setDossierId( rs.getInt( "dossier_id" ) );
237         vo.setAccountId( rs.getInt( "account_id" ) );
238         vo.setTmtypeId( rs.getInt( "tmtype_id" ) );
239         vo.setLanguageCd( rs.getInt( "language_cd" ) );
240         vo.setRequestNr( rs.getString( "request_nr" ) );
241         vo.setGuardNr( rs.getString( "guard_nr" ) );
242         vo.setDescription( rs.getString( "description" ) );
243         vo.setBeginDat( rs.getDate( "begin_dat" ) );
244         vo.setReference( rs.getString( "reference" ) );
245         vo.setStateId( rs.getInt( "state_id" ) );
246         vo.setCreatedDat( rs.getDate( "created_dat" ) );
247         vo.setChangedDat( rs.getDate( "changed_dat" ) );
248 
249         list.add( vo );
250       }
251     }
252     finally
253     {
254       DBHelper.getInstance().close( con, ps );
255     }
256     return list;
257   }
258 
259   /**
260    * Löscht einen Datensatz aus der Tabelle DOSSIER.
261    * @param accountId int PK FehlerId
262    * @return int Anzahl gelöschter Datensätze
263    * @exception SQLException Unerwarteter SQL Fehler
264    */
265   public int delete( int accountId ) throws SQLException
266   {
267     Connection con = null;
268     PreparedStatement ps = null;
269 
270     try
271     {
272       con = DBHelper.getInstance().getEsolutionConnection();
273       ps = con.prepareStatement( "DELETE FROM edossier.dossier where dossier_id = ? " );
274       ps.setInt( 1, accountId );
275 
276       return ps.executeUpdate();
277     }
278     finally
279     {
280       DBHelper.getInstance().close( con, ps );
281     }
282   }
283 
284   //---------------------------------------------------------------------------
285   // private methods
286   //---------------------------------------------------------------------------
287 
288   /**
289    * Gibt das SQL-Statement für den select aller Attribute ohne where-Bedingngung zurück.
290    * @return String Select-Statement
291    */
292   private String getSelectStatement()
293   {
294     return
295         ( "select dossier_id, tmtype_id, account_id, language_cd, request_nr, guard_nr, " +
296           "description, begin_dat, reference, colorclaim, created_dat, changed_dat " +
297           "from edossier.dossier " );
298   }
299 
300   /**
301    * Füllt einen Datensatz anhand des ResultSet.
302    * @param rs ResultSet Gültiges resultSet
303    * @return DossierVO Value-Object
304    * @exception Exception Unerwarteter SQL Fehler oder Konvertierungsfehler
305    */
306   private DossierVO fillVO( ResultSet rs ) throws Exception
307   {
308     DossierVO dossierVO = new DossierVO();
309     CLOB clob = null;
310     char[] buf = null;
311 
312     dossierVO.setDossierId( rs.getInt( "dossier_id" ) );
313     dossierVO.setAccountId( rs.getInt( "account_id" ) );
314     dossierVO.setTmtypeId( rs.getInt( "tmtype_id" ) );
315     dossierVO.setLanguageCd( rs.getInt( "language_cd" ) );
316     dossierVO.setRequestNr( rs.getString( "request_nr" ) );
317     dossierVO.setGuardNr( rs.getString( "guard_nr" ) );
318     dossierVO.setDescription( rs.getString( "description" ) );
319     dossierVO.setBeginDat( rs.getDate( "begin_dat" ) );
320     dossierVO.setReference( rs.getString( "reference" ) );
321 
322     clob = ( CLOB ) ( ( OracleResultSet )rs ).getClob( "colorclaim" );
323     if( clob != null )
324     {
325       buf = new char[ ( int )clob.length() ];
326       clob.getCharacterStream().read( buf );
327       dossierVO.setColorclaim( new String( buf ).trim() );
328     }
329 
330     dossierVO.setCreatedDat( rs.getDate( "created_dat" ) );
331     dossierVO.setChangedDat( rs.getDate( "changed_dat" ) );
332 
333     return dossierVO;
334   }
335 
336   /**
337    * Diese Methode fügt nachträglich einen Blob in die Tabelle DOSSIER
338    * @param dossierVO DossierVO Value-Object
339    * @throws SQLException Unerwartete SQL-Exception
340    */
341   public void insertBlob( DossierVO dossierVO ) throws SQLException
342   {
343     Connection con = null;
344     PreparedStatement ps = null;
345     ResultSet rs;
346     String queryBlobSelect = null;
347     String queryBlobUpdate = null;
348 
349     try
350     {
351       con = DBHelper.getInstance().getEsolutionConnection();
352       // Auto commit must be turned off.
353       con.setAutoCommit( false );
354       // Insert Values and preparing blob-column with empty-blob
355       ps = con.prepareStatement( "UPDATE edossier.dossier SET image = EMPTY_BLOB(), changed_dat = sysdate WHERE dossier_id = ?" );
356 
357       ps.setInt( 1, dossierVO.getDossierId() );
358       rs = ps.executeQuery();
359       rs.next();
360       ps.close(); // Statement schliessen, da mehrere ausgeführt werden.
361 
362       queryBlobSelect = "SELECT image " +
363           "FROM edossier.dossier " +
364           "WHERE dossier_id = ? " +
365           "FOR UPDATE";
366 
367       con = DBHelper.getInstance().getEsolutionConnection();
368       ps = con.prepareStatement( queryBlobSelect );
369       ps.setInt( 1, dossierVO.getDossierId() );
370       rs = ps.executeQuery();
371       rs.next();
372 
373       oracle.sql.BLOB dbBlob = ( oracle.sql.BLOB )rs.getBlob( 1 );
374       // update blob
375 
376       ps.close(); // Statement schliessen, da mehrere ausgeführt werden.
377 
378       queryBlobUpdate = "UPDATE edossier.dossier " +
379           "SET image = ? " +
380           "WHERE dossier_id = ?";
381 
382       ps = con.prepareStatement( queryBlobUpdate );
383 
384       dbBlob.putBytes( 1, dossierVO.getImageVO().getImage() );
385       ps.setInt( 2, dossierVO.getDossierId() );
386 
387       ps.setBlob( 1, dbBlob );
388       con.commit();
389       ps.executeUpdate();
390       ps.close(); // Statement schliessen, da mehrere ausgeführt werden.
391     }
392     finally
393     {
394       DBHelper.getInstance().close( con, ps );
395     }
396   }
397 }
398