fileDownload and oracle bfile

[maxqua72]

Hello,
I'm trying to use fileDownload in a dataTable fetching records from a table in an oracle database table without great success.
I'm using glassfish 3.1.1 and primefaces 2.1.
the table has few fields:
matricola (INTEGER) | anno (INTEGER)| mese (INTEGER)| filename (BFILE)

here is the .xhtml excerpt

Code: Select all

<h:dataTable id="ced2" 
                                     value="#{cedolinoMBean.listaCedolini}" 
                                     var="row" >
                            <p:column sortBy="#{msgs.anno}" filterBy="#{msgs.anno}">  
                                <f:facet name="header">  
                                    #{msgs.anno}  
                                </f:facet>  
                                <h:outputText value="#{row.anno}"/>  
                            </p:column>  

                            <p:column sortBy="#{msgs.mese}" filterBy="#{msgs.mese}">  
                                <f:facet name="header">  
                                    #{msgs.mese}  
                                </f:facet>  
                                <h:outputText value="#{row.mese}"/>  
                            </p:column>  

                            <p:column>  
                                <f:facet name="header">  
                                    #{msgs.scarica}  
                                </f:facet>  
                                <h:commandLink >
                                    <p:fileDownload value="#{row.file}"/>
                                    <h:graphicImage library="img" name="pdf_download.png" style="border:0"/>
                                
                                </h:commandLink>  
                            </p:column>  

                        </h:dataTable>

ListaCedolini is a list of Elem type:

Code: Select all

public class Elem {

    private String anno;
    private String mese;
    private StreamedContent file;

    public Elem(String anno, String mese, StreamedContent file) {
        this.anno = anno;
        this.mese = mese;
        this.file = file;
    }
....

In order to read the file pointed by BFILE, In the backing bean I populate the list in the following way:

Code: Select all

public List getListaCedolini() {

        if (listaCedolini == null) {

            try {
                Connection conn = null;
                conn = DriverManager.getConnection("jdbc:oracle:thin:@machine:port:schema", "username", "pwd");
               
                if (conn == null) {
                    Logger.getLogger(AuthEBean.class.getName()).log(Level.INFO, "connessione nulla");
                    return listaCedolini;
                } else {
                    Logger.getLogger(AuthEBean.class.getName()).log(Level.INFO, "connessione ok");
                    java.sql.Statement stmt = conn.createStatement();
                    ResultSet rs = stmt.executeQuery("SELECT * FROM gf_cedolini where matricola = " + impiegato.getMatricola());

                    listaCedolini = new ArrayList();
                    if (rs.next()) {
                        BigDecimal matr = rs.getBigDecimal("matricola");
                        int anno = rs.getInt("anno");
                        int mese = rs.getInt("mese");
                        oracle.sql.BFILE bfile = ((OracleResultSet) rs).getBFILE("filename");
                        System.out.println("mese " + anno + mese + " bfile=" + bfile.getName() + " length: "
                                + bfile.getLength() + " esiste? " + bfile.fileExists());
                        InputStream ins = null;
                        bfile.openFile();
                        ins = bfile.getBinaryStream();
                        StreamedContent file = null;
                        if (ins != null) {
                            file = new DefaultStreamedContent(ins, "application/pdf",
                                    "prime.pdf");
                            listaCedolini.add(new Elem("" + anno, "" + mese, file));
                        } else {
                            System.out.println("bfile non trovato");
                        }
                        
                        //bfile.close();
                        rs.close();
                        stmt.close();
                        //conn.close();
                        return listaCedolini;
                    } else {
                        Logger.getLogger(AuthEBean.class.getName()).log(Level.INFO, "file non trovato");
                        rs.close();
                        stmt.close();
                        conn.close();
                        return listaCedolini;
                    }
                }
            } catch (SQLException ex) {

                Logger.getLogger(AuthEBean.class.getName()).log(Level.INFO, "connessione non riuscita");
                return listaCedolini;
            }
        }
        return listaCedolini;

    }

Now, the problem is that the jdbc connection needs to be open when the actionListener fires and starts reading bytes from the inputStream (also the bfile needs to be open), but I have no way to intercept the primefaces actionListener and open the bfile there and keeping the connection open during the byte reading.
Is there any way to solve this issue?
The method getListaCedolini works the first time and it is not the code I would like to write to allow the file download from a datasource.
In my opinion the solution is to write a dedicated actionListener but how I can do it? Any other hints?

Thanks,