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