Blog Archives

EJB downloading an excel file from JSP

The Calling Page

Let’s say you have a button on a JSP page that calls it. One of the problems is stopping the page opening as blank. So here’s the calling button

<form method="post" action="#">
 <input type="button" value="Download" onClick="return downloadButton();"/>
        </form>
        

The iFRame is used to trigger the excel call, and here’s the download button javascript.

function downloadButton() {
 
    document.getElementById("excel").src="exportxl.jsp?sql=select * from mytable";
    return false;
    
}

The Responding Page

Now the Calling JSP Page that goes off and gets the Excel Spreadsheet from the EJB, I have an EJB called server and the corresponding remote interface “server remote” note that these must be registered in the xml files in the web app so the web app can see the remote interface in the EJB

<%-- 
    Document   : exportxl
    Created on : Aug 1, 2011, 8:36:23 AM
    Author     : paulc
--%>
<%@ page import = "java.sql.*" %>
<%@ page import = "javax.naming.Context" %>
<%@ page import = "javax.naming.InitialContext; "%>
<%@ page import = "PHJ.*" %>
<%@page contentType="application/vnd.ms-excel" pageEncoding="UTF-8"%>
<%

    PHJ.ServerRemote jobManagerBean = null;
    try {
        Context c = new InitialContext();
        jobManagerBean = (PHJ.ServerRemote) c.lookup("java:comp/env/Server");
    } catch (Exception ne) {
        System.out.println("Could not find Job Manager Bean " + ne);
        out.println("Could not find Job Manager Bean");
        return;
    }

    response.setHeader("Content-disposition", "attachment; filename=excelexport.csv");

    String sql = "";
    if (request.getParameter("sql") != null) {
        sql = request.getParameter("sql");

        out.print(jobManagerBean.prepareCSVForExcel(sql));

    } else {
        out.println("Error, No SQL statement specified");

    }
    
    

%>

The EJB

And here’s the routine in the EJB.

 
    private Connection conn;
    private String dbName = "myJNDIRESOURCE"; // make sure you have JNDI resource setup 
    /* This routine gets a connection from the above DBName, however, if it can't find it it lists the available JNDI resourcees
     * to the console or log
     */ 
    private Connection getConnection() {
        if (conn != null) {
            try {
                if (!conn.isClosed()) {
                    return conn;
                }
            } catch (SQLException ex) {
                // Do nothing just carry on.
            }

        }
        InitialContext ic = null;

        Query q = null;

        try {
            // initialize JNDI lookup parameters
            ic = new InitialContext();
            DataSource ds = (DataSource) ic.lookup(dbName);
            conn = ds.getConnection();
        } catch (Exception e) {
            try {
                System.out.println("JNDI Resource " + dbName + " not found " + e);
                NamingEnumeration children = ic.list("");
                while (children.hasMore()) {
                    NameClassPair ncPair = (NameClassPair) children.next();
                    System.out.print(ncPair.getName() + " (type " + ncPair.getClassName() + ")");

                }
                System.out.println("**** CANNOT FIND DATABASE ************************");
                children = ic.list("jdbc");
                while (children.hasMore()) {
                    NameClassPair ncPair = (NameClassPair) children.next();
                    System.out.print(ncPair.getName() + " (type " + ncPair.getClassName() + ")");

                }
                System.out.println("****************************************");
            } catch (Exception f) {
                System.out.println("Error 57:" + f);
            }
            System.out.println("ERROR 52: " + e);
            return null;
        }
        return conn;

    }

    public String prepareCSVForExcel(String sSQL) {

        String sResult = "";
        try {
            conn = getConnection();
            Statement s = conn.createStatement();
            ResultSet r = s.executeQuery(sSQL);
            // feldnames
            for (int i = 1; i != r.getMetaData().getColumnCount(); ++i) {
                sResult = sResult + r.getMetaData().getColumnName(i) + ",";


            }
            sResult = sResult + "\n";

            while (r.next()) {
                for (int i = 1; i != r.getMetaData().getColumnCount(); ++i) {
                    try {
                    if (r.getObject(i) != null) {
                        sResult = sResult + r.getString(i) + ",";
                    } else {
                        sResult = sResult + ",";

                    }
                    } catch (Exception e) {
                        sResult = sResult + ","; // Happens when there is a conversion error to a tiemstamp
                    }


                }
                sResult=sResult+"\n";



            }




        } catch (Exception e) {
            return e + "";
        }
        return sResult;
    }
Advertisements

Autocomplete with Netbeans 6.1!

Ok – for those of you who are stuck like us with Netbeans 6.1 until Jasper Reports catches up with 6.5 this is how to do an autocomplete field with woodstock.

I hate to say this but once I’d worked it out it was pretty easy.

1. Download the latest WOODSTOCK nbm (netbeans plugin file) and install.
2. In your Session bean you need to create a routine to return the data, it must look like this

Public Option[] getDropDownOptions(String sFilter) {

// Put your code in here to create an array of options
// new Option(Key, Display text)
}
2. In your Visual Web project create a text box and turn on the AutoComplete property
3. In the AutoCompleteExpression field bind it to:-
#{SessionBean1.getDropDownOptions}

Thats it now you will get an autocomplete text field.

Don’t bother doing all the stuff in other blogs, you just don’t need it once you’ve updated to the next version of Woodstock.

Incidentally we have had to shelve Icefaces until at least after Christmas and I am looking at Infragistics because they have a VERY interesting Grid control that can dynamically load Grid data, useful when customers want to see a lot of data at once.

Using SQL Server 2000 with Netbeans 6.1 (can’t see any tables)

Right

I’m just starting a HUGE project which has a SQL sever 2000 database at the back. So I configured up the SQL database and couldn’t see any tables…

The problem?

I needed to select the dbo schema not the name of the database. So your settings should be like this:-

Connection String: jdbc:sqlserver://192.168.100.11:1433;databaseName=(database name)
Schema (this is the important bit) : dbo

For some reason I mistook the Schema for the DATABASE NAME which in NB is a completely different thing.

I also tried the jtds driver and decided to use that – it’s open source and provides even faster connection.

Note that ODBC is old hat now and you should not use it as it’s really slow and probably ends up using the same sort of code you would use directly but with extra layers.

 

 

6.1 Netbeans 12 hours later

Well I was going to update the 6.1 Netbeans thing I posted yesterday, but I spent a solid 12 hours developing with it. I made a massive amount of progress with a large project I was working on.

Although I had a few crashes that was largly due to the massive project I have been working on. It’s  a huge web project and is hooked up to the glassfish server. Although relogging into my Solaris Box fixed it.

I am really really ENJOYING writing Netbeans based code under Solaris. I think the Netbeans community have developed something very special and once you get “under the hood” and work out how to do basic things it is soon really great developing large projects with it.

I am going to enter my blog entries into Sun’s competition but this is honestly from the heart and I hope that as a developer (if you’re reading this) you’ll go ahead and try Netbeans out. I’m just looking forward to the full release of it.

Good job! Please catch up with me in Second Life (pnc Blessed) if you fancy a chat.

Free Java Hosting for Developers!

http://www.myjavaserver.com/

Is a free hosting site for developer. but there’s a catch  to get the free hosting you have to do a bit of coding to prove you really ARE a java developer. I cracked it quite quick but I do have to say you need to read the specification CAREFULLY.

Good Job!