Category Archives: Netbeans

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

Book: Java EE 6 Development with NetBeans 7

Having Junior staff is always a problem. Once every couple  of hours you get  a silly question so I’ve been looking for a guide book to give them a basic knowledge of things like JPA.

I was very pleased to see the book covers installing on all platforms, the author recognising that there are a lot of developers who use Linux, Solaris and Mac as well as Windows .

Installation also includes how to install with JBoss which  I like, not just sticking to the easy option.

I think for the beginner this is an excellent book in terms of setting up NetBeans and covering the basics. I would think a knowledge of Java would ofcourse help, but that is not the scope of this book. You’d need to get another tutorial on Java and there are many good ones out there.

I like the inclusion of PrimeFaces components. Although one of my frustrations with all of this is the lack of a WYSIWYG designer for these components, it makes good design of a lot of components quite hard.

It’s a thorough book and covers the basics achieving what it sets out to do.

Link to the book: http://link.packtpub.com/Kc6Gqg

Netbeans 6.9…. Woodstock is BACK!

Great News,

To my surprise, upon installing Netbeans 6.9, I find the woodstock plugins back inside and working great. This is amazingly good news for us as we are often building complex screens and laying it out in raw, naked jsp is just too much and too time consuming!

Great Job!

Now – perhaps we should get back to supporting it somehow?

My application won’t deploy!

You know one of those problems that makes you kick yourself when you find out the solution!

I’ve got this Solaris server running Glassfish, the application runs fine locally, but when deploying it just hangs.

I THOUGHT it was a problem with a slow network, so just sat there waiting but nothing – had the network checked as well. Then one of my guys was dealing with a different problem (Uthay!) and started talking about JDK versions.

The application I had was configured as JDK 5, so I changed it to JDK 6 and guess what – it deployed straight away.

THere are two morals to this story:-

a. Listen to what is going on around you if you work in a programming team as someone elses solution might be yours as well.
b. Make sure the version of the JDK you are running on the development environment MATCHES the one on the server!

Problems with Webservices in Netbeans and how to solve them

Ok, rather than give a tutorial on web services – because there are some around, I thought I would solve one of the mysterious problems that can occur when you are creating these services.

Netbeans is AMAZING at creating web services, the tools are incredibly rich.

But this error dogged me. I had one or two web services that worked and others that didn’t. To set up my service I had set up some inner classes

public static class dtaData {
private String Name;
private String date;
private String sid;
}

then created a web services that returned a LIST of those objects:-

    @WebMethod(operationName = "Data")
    public List Data() {
        List result=new ArrayList();
       for (int i=0;i<20;++i) {
        dtaData DataObject=new dtaData ();
       DataObject.sid=""+i;
       result.add(Result);
     }

        
        return result;
    }
  

And it falls over with this error when you test the webserver. HOWEVER The problem is caused because there is no web service DIRECTLY returning the dtaData class. The solution is to create a web service that returns null but has dtaData as the returning Object.

e.g.

    @WebMethod(operationName = "testData")
    public dtaData testData() {
           return null;

    }
   

These errors are given when the problem occurs:-

On the glassfish server

Caused by: javax.xml.ws.WebServiceException: javax.xml.bind.MarshalException
 - with linked exception:
[javax.xml.bind.JAXBException: class PHJ.Agenda$ICDB_Agenda_data nor any of its super class is known to this context.]
        at com.sun.xml.ws.message.jaxb.JAXBMessage.writePayloadTo(JAXBMessage.java:322)
        at com.sun.xml.ws.message.AbstractMessageImpl.writeTo(AbstractMessageImpl.java:142)
        at com.sun.xml.ws.encoding.StreamSOAPCodec.encode(StreamSOAPCodec.java:108)
        at com.sun.xml.ws.encoding.SOAPBindingCodec.encode(SOAPBindingCodec.java:258)
        at com.sun.xml.ws.transport.http.HttpAdapter.encodePacket(HttpAdapter.java:320)
        at com.sun.xml.ws.transport.http.HttpAdapter.access$100(HttpAdapter.java:93)
        at com.sun.xml.ws.transport.http.HttpAdapter$HttpToolkit.handle(HttpAdapter.java:454)

On the web service test return:


Exceptions details : java.lang.reflect.InvocationTargetException


javax.servlet.ServletException: java.lang.reflect.InvocationTargetException at com.sun.enterprise.webservice.monitoring.WebServiceTesterServlet.doPost(WebServiceTesterServlet.java:345) at com.sun.enterprise.webservice.monitoring.WebServiceTesterServlet.invoke(WebServiceTesterServlet.java:121) at com.sun.enterprise.webservice.JAXWSServlet.doPost(JAXWSServlet.java:165) at javax.servlet.http.HttpServlet.service(HttpServlet.java:738) at javax.servlet.http.HttpServlet.service(HttpServlet.java:831) at org.apache.catalina.core.ApplicationFilterChain.servletService(ApplicationFilterChain.java:411) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:317) at org.apache.catalina.core.Ap

Java Server Faces, SQL SERVER and CHAR FIELDS

I have modified the excellent JTDS driver to strip the spaces from CHAR FIELDS so that Java Server faces controls do not get confused when fields are of different lengths and also when you go to edit a field you don’t get a pile of spaces at the end of the text.

The problem.

Well I have a project where the people who originally designed the database were obviously monkeys. So they used CHAR fields throughout the database. But not all the same size, for example:-

TABLE 1 (SERVICE CODES) SERVICE CODE CHAR 2 JI

Then in an other table

TABLE 2 SERVICE CODE CHAR 50

This means that if you create a drop down box on a page with the service code list it would be padded and would not match the other table. I initially started modifying the SQL code to CAST service code as a CHAR to match the other one. Since there were 100 odd tables this meant masses of extra work to use Java Server Faces.

The other problem is that editing fields have extra spaces at the end of the data – what a nightmare.

So I pulled down the open source JTDS driver and rewrote it to trim all spaces from CHAR fields, thus making JSF work right and solving my SPACE problem.

The files are here

Source Code (converted into a Netbeans Project)

Note if you download and use this you do so at your own risk, no warranty, implied or otherwise is provided for this software.

Note 2: if you were using an OLD jdts driver you will need to remove the references from the SERVER as well as NETBEANS and use this new one – I might get round to renaming the driver to something else but if you find there are spaces where there should not be its probably because you are running the standard driver not the new one.

Note 3: If you just want the driver its in the “dist” subdirectory, you need the LIB folder as well.

Note 4: If you publish a project using this driver and modify the driver then publish it again, NB won’t change the driver on the glassfish server if it has the same name, so you’ll have to copy it over yourself.

Java Server Faces, Data Class (Parent/Child) (if you use JSF read this)

I’m working on a huge project with some multi tab screens and I noticed two things about the code, firstly my init code had a lot of changes to the default SQL providers, mainly for  things where tables had links to other tables, parent child relationships. The more I added, the slower it got  – plus my code got quite messy.

So – just to tidy up the code, I wrote a class below that has some functions for changing data providers. Guess what, not only was my code neater but the pages speeded up very noticably when loading. – Why not comment if you have a clue why, personally I think its something to do with the way java handles classes that are called a lot.

I declare one change object at the start of the JSF java code.

DataProviderSQLChanger change = new DataProviderSQLChanger();

Then I can use the change object whenever I like in the init code and throughout the page…

change.ChangeSQL(tblsubservicetypesDataProvider, "select * FROM tblsubservicetypes");

Functions

public void ChangeSQL(CachedRowSetDataProvider dataProvider, String sNewSQL)

This routine changes the SQL of a cached data provider – however it does look to see if the SQL your are submitting is what is already on the cached data provider so we don’t waste time reloading schemas etc.

public void ParentChildLink(
            CachedRowSetDataProvider parentDataProvider,
            String sParentFieldName,
            CachedRowSetDataProvider childDataProvider,
            String sChildFieldName,
            boolean bIsText)

 This Parentchildlink is for when you have a parent child relationship for example, when I have a “MAIN SUBFORM” Idea. It’s pretty straight forward (see the code for documentation and parameters).

public void ForceParentChildLink(
            String sKey,
            CachedRowSetDataProvider parentDataProvider,
            String sParentFieldName,
            CachedRowSetDataProvider childDataProvider,
            String sChildFieldName,
            boolean bIsText)

This is the same as parent child link but FORCES the Parent CachedRowSetDataProvider to look only to the record selected.

Anyway – here’s the class, have fun!

import com.sun.data.provider.impl.CachedRowSetDataProvider;
import java.sql.SQLException;
/**
 * This is an easier way to change a Data Providers SQL and manage data providers
 * By Paul Clevett http://www.netbeansboy.com
 * @author paulc
 */
public class DataProviderSQLChanger {
    /**
     * Changes the SQL Statement of a Dataprovider
     * If the sql statement is already set to that, just return, refreshing the data provider
     * @param dataProvider
     * @param sNewSQL
     */
    public void ChangeSQL(CachedRowSetDataProvider dataProvider, String sNewSQL) {
        // Is the dataproviders sql already set?
        if (!dataProvider.getCachedRowSet().getCommand().equals(sNewSQL)) {
            try {
                dataProvider.getCachedRowSet().setCommand(sNewSQL);
            } catch (SQLException ex) {
                System.out.println("SQL Error: " + ex);
            // Logger.getLogger(DataProviderSQLChanger.class.getName()).log(Level.SEVERE, null, ex);
            }

        }
        dataProvider.refresh();
    }
    /**
     * Manage the Parent Child Link between two cached data providers
     * gives much clearer code, note that this routinedoes not change the parents
     * data provider's SQL unlike ForceParentChildLink
     * @param parentDataProvider    - Parent Data Provider
     * @param sParentFieldName      - Parent Field name
     * @param childDataProvider     - Child Data Provider
     * @param sChildFieldName       - Child Field name
     * @param bIsText               - if the link is a text link
     */
    public void ParentChildLink(
            CachedRowSetDataProvider parentDataProvider,
            String sParentFieldName,
            CachedRowSetDataProvider childDataProvider,
            String sChildFieldName,
            boolean bIsText) {
        // Sets up the link between parent and childe data providers
        String sOrderBy="";
        String sSQL = childDataProvider.getCachedRowSet().getCommand();
        if (sSQL.indexOf("ORDER BY")!=-1) sOrderBy = sSQL.substring(sSQL.indexOf("ORDER BY"));
        // Now remove any existing where's
        
        if (sSQL.indexOf("WHERE")!=-1) sSQL = sSQL.substring(0, sSQL.indexOf("WHERE") - 1);
        // Add the Query
        if (bIsText) {
            
            sSQL = sSQL.concat(" WHERE " + sChildFieldName + "='" + parentDataProvider.getValue(sParentFieldName).toString().trim() + "'");
            
        } else {
            
            sSQL = sSQL.concat(" WHERE " + sChildFieldName + "=" + parentDataProvider.getValue(sParentFieldName));
            
        }
        sSQL=sSQL.concat(sOrderBy);
        
        // Execute the query on the child
        ChangeSQL(childDataProvider,sSQL);
    }
    
    /**
     * Syncs up the parent/child relationship between two data providers with
     * a key
     * @param sKey                  - Parents Key Value or blank if the fieldname is the same as the sParentNameField 
     * @param parentDataProvider    - Parents Data Provider
     * @param sParentFieldName      - Parents FieldName
     * @param childDataProvider     - Childs Data Provider
     * @param sChildFieldName       - Childs Field Name
     * @param bIsText               - Is the key Text or Numeric? (TRUE/FALSE)
     */
    public void ForceParentChildLink(
            String sKey,
            CachedRowSetDataProvider parentDataProvider,
            String sParentFieldName,
            CachedRowSetDataProvider childDataProvider,
            String sChildFieldName,
            boolean bIsText) {
        // Sets up the link between parent and childe data providers
        String sOrderBy="";
        String sSQL= parentDataProvider.getCachedRowSet().getCommand();
        if (sKey.equals("")) sKey=parentDataProvider.getValue(sParentFieldName).toString();
        if (sSQL.indexOf("ORDER BY")!=-1) sOrderBy = sSQL.substring(sSQL.indexOf("ORDER BY"));
        // Now remove any existing where's
        
        if (sSQL.indexOf("WHERE")!=-1) sSQL = sSQL.substring(0, sSQL.indexOf("WHERE") - 1);
        // Add the Query
        if (bIsText) {
            
            sSQL = sSQL.concat(" WHERE " + sParentFieldName + "='" + sKey + "'");
            
        } else {
            
            sSQL = sSQL.concat(" WHERE " + sParentFieldName + "=" + sKey);
            
        }
        sSQL=sSQL.concat(sOrderBy);
        // Force the parent over to look at the particular row
        ChangeSQL(parentDataProvider,sSQL);
        
        
        // Now the child
        sSQL = childDataProvider.getCachedRowSet().getCommand();
        if (sSQL.indexOf("ORDER BY")!=-1) sOrderBy = sSQL.substring(sSQL.indexOf("ORDER BY"));
        // Now remove any existing where's
        
        if (sSQL.indexOf("WHERE")!=-1) sSQL = sSQL.substring(0, sSQL.indexOf("WHERE") - 1);
        // Add the Query
        if (bIsText) {
            
            sSQL = sSQL.concat(" WHERE " + sChildFieldName + "='" + parentDataProvider.getValue(sParentFieldName) + "'");
            
        } else {
            
            sSQL = sSQL.concat(" WHERE " + sChildFieldName + "=" + parentDataProvider.getValue(sParentFieldName));
            
        }
        sSQL=sSQL.concat(sOrderBy);
        
        // Execute the query on the child
        ChangeSQL(childDataProvider,sSQL);
    }
    
    
    
}

Java Server Faces and Google Chrome — Goodbye IE!

When Firefox 3 came out I was hoping it would run JSF faster than IE did, instead it actually failed to run at all well so we just forgot about it and went back to sunny IE for our clients.

Today! I install Google Chrome… Actually I expected JSF not to work at all… let alone how it does.

JSF runs VERY VERY VERY Fast in Google Chrome, sorry, actually, the whole internet runs VERY VERY VERY FAST.

Our tests so far show perfect compatibilty with JSF and it turns the web browser into an application browser, apart from the fact we can perform more customisations of the browser and use it as a real tool for accessing the multiple JSF projects we have.

We can now see bottlenecks without running profiler. The time between the pages loading shows where the problems are. IE really struggled with massive amounts of componants on pages. 

GOOD JOB GOOGLE

If you’ve not tried Google Chrome please please do… hitch it up to your JSF application (or any web app) or your favorite website and just fall off your chair at the MARKED DIFFERENCE in speed. 

I would think once word gets out that people will not want to use IE any longer.

Beautiful Coding – The way to life.

I often have to fix other people’s code. You can tell coders who have been coding for years simply by the feel of their code.

One of the problems with java is the simple fact that you can end up with a spaghetti-junction of objects all calling, calling back, implementing, sub classing, overriding, running ejb’s etc etc and it can be a bit of a nightmare to find your way through.

The key here (although you can reverse engineer into a UML Model with Netbeans) is really in the way that the code is written.

Things I do to make code more readable

– Initials of variables

All my strings for instance start with s – sName, sUsername
integers I , iCount, iGold
doubles d

and so on for different types of variables or objects.

Components on forms I use a 3 letter start up.

txtTEXTBOX
lblLABEL
stcSTATICTEXT
cmbCOMBOBOX

Also in the code using the java doc function, within Netbeans if you type /** enter it will auto document the next function and you just “fill in the blanks”, javaDoc is fantastic and well worth looking into. If you have “java doced” your code, not only does Netbeans pick up the documentation when you are typing or using your own functions but also you can generate a website that documents your classes!!! How Call

ALT-SHIFT-F – auto format your code, very useful.

Comment long bracketed loops. I always put // from  next to a bracketed loop  that is too long to see in one screen. e.g.

for (int iCount=0;iCount<10;++iCount) {          
system.out.println("Hello World "+iCount); } // From count to 10 icount

Also comment all calls to EJB’s to say what they do –

// Get this job’s History
JobManagerBean.getHistory(sJobNumber);

Beautiful Code!

Lisp programmers write beautiuful code, why? Because you have to, it becomes unreadable otherwise. In a way I wish all programmers would learn Lisp – because it makes you think in a different way and see software development from an almost etherial height. When I get some further research time I want to work on a lisp implementation for netbeans.

Remember that you will not be around forever, don’t punish the next poor programmer who has to work on your code. I like my code to be really easy to read and understand.

Code Rewriting

You know that sometimes I will rewrite a whole subroutine if I’m not happy with it. Programmers are artists, the difference is that the result of our art is a system for people to use or some form of output. Well written, neat, fast, efficient code means happy users and that is the caling of a programmer, to make the lives of the users easier.

If your software makes it harder. It needs rewriting, or maybe replacing with a pen and paper.

Be passionate about your coding and your users will be passionate about your software.

Jasper Reports — those empty reports.

Jasper reports is a wonderful product .. and it does integrate really nicely with netbeans. But when it goes wrong you can get blank reports with no clue to what the problem is.

Here are some short observations when coding it up with java and JSF that might help you.

1. Watch your field names – I noticed that when you generated reports Jasper Reports writes a pretty odd query especially with SQL server 2000 at the back end (yes I know, some people still use it, although this one WILL be upgraded to MYSQL):-

SELECT
     tblZones.”ZoneDescription” AS tblZones_ZoneDescription
FROM
     “dbo”.”tblZones” tblZones
ORDER BY
  tblZones.”ZoneDescription

Now this is fine – so then the fields in the report are mapped to tblZones_ZoneDescription intead of just leaving it as is – so if you then have a custom query for the report make sure that you are using the right field names or you could get that “blank page thing”

2. Main/Subforms or header/details (you know, Purchase Order Header and lines) – getting sub forms to work is hard graft! – I will post a solution when we do it – but until then it’s easier to have a SINGLE query with a join have the lines in the detail and group by the header id.

3. When it all goes wrong – sometimes I’ve had it refusing to run the jasper reports report I created earlier in good Blue Peter fashion. Normally a restart of NetBeans does fix this – but sometimes I’ve found a CHKDSK.

4. BACK IT UP – EVERYONE who develops should use a repository system such as subversion or CVS it’s saved my life countless times.

Have fun!