Retrieve EntityManager while using Spring JpaTemplate

In current project, I'm using Spring framework combine with OpenJpa. Life seems becoming much easier while using Spring JpaTemplate to handle all the database queries. However those methods provide by JpaTemplate class are not powerful enough to satisfy all kinds of requirements, so we may need use EntityManager to prepare query at some point.

When I was facing the problem of retrieve only the first n rows of a query, I could not find any method in JpaTemplate would that help me easily achieve this goal. At insult to the injury, there is no such keyword in JPQL like the rownum in Oracle or limit in Mysql that we can use directly into the query -- there may do have a keyword, but I didn't find by google. The only choice I have is to do it using Hibernate way, using setFirstResult() and setMaxResults() methods. In that case we do need an instance of EntityManager.

JpaTemplate class has a public method called getEntityManager(). The interesting fact is this mehod will always return null. Based on the online gossips of some people, though the getEntityManager's indentifier is public, no one should call it explicitedly. It can only be used by Spring itself. After further googled this issue, there are many ways to workaround this. Among all those solutions, using JpaCallback may be the simplest one.

There's some sample code:

   List results = getJpaTemplate().executeFind(
           new JpaCallBack() {
                public Object doInJpa(EntityManager em) throws PersistenceException {
                        Query query = em.createQuery(queryStr);
                        query.setFirstResult(0);
                        query.setMaxResults(1000);
                        List results = query.getResultList();
                        return results;
                }
         }
   );

Export Excel file under JSF

I think maybe it is a good idea to write down how to export Excel file under JSF. I'm using Myfaces 1.2 and Apache POI 3.1

The basic code should look like:

FacesContext context = FacesContext.getCurrentInstance();
HttpServletResponse response =
(HttpServletResponse)context.getExternalContext().getResponse();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment;filename=job.xls");
response.setHeader("Pragma", "no-cache");
try{
OutputStream os = response.getOutputStream();

int rowIndex = 0;

// Create Excel Object
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet 1");

// Print out configuration, optional
HSSFPrintSetup ps = sheet.getPrintSetup();
ps.setFitWidth((short)1);

// Define cell style, optional
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true);

// Create a row
HSSFRow row = sheet.createRow(rowIndex++);
// Create cells
HSSFCell cell = row.createCell((short)0);
cell.setCellValue(new HSSFRichTextString("Id") );
cell.setCellStyle(cellStyle);
cell = row.createCell((short)1);
cell.setCellStyle(cellStyle);
cell.setCellValue(new HSSFRichTextString("Name") );
cell = row.createCell((short)2);
cell.setCellStyle(cellStyle);
cell.setCellValue(new HSSFRichTextString("Email") );
cell.setCellStyle(cellStyle);
cell = row.createCell((short)3);
cell.setCellValue(new HSSFRichTextString("Phone Number") );
cell.setCellStyle(cellStyle);
// Your own code ...

wb.write(ops);
os.flush();
os.close();
}
catch(IOException ioe) {
// Handle exception ...
}

FacesContext.getCurrentInstance().responseComplete();

Tell the truth, the default setting of POI is not very smart. In real project you may need add some extra code to beautify your excel. I list some useful settings here.

Print Configuration
Fit your Excel the width of one page

HSSFPrintSetup ps = sheet.getPrintSetup();
ps.setFitWidth((short)1);

Set default orientation to landscape

HSSFPrintSetup ps = sheet.getPrintSetup();
ps.setLandscape(true);

Auto break

sheet.setAutobreaks(true);

Cell Configuration
Auto wrap text

HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true);

Alignment

HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);

Vertical Alignment

HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);

You can get more detail information of POI by checking out the following link:
http://poi.apache.org/hssf/quick-guide.html

Back slashes needed to escape a quote in Java

During today's work, I faced the problem of escaping the quote, double quote and line break inside a String while sending it into javascript.

The following code was the approach first came into my mind:

String eStr = orginalStr.replaceAll("\"", "\\\"");
eStr = eStr.replaceAll("'", "\\'");
eStr = eStr.replaceAll("\\r\\n", "<br>");

Ironically, none single line of these code works. After some research, I figured out the correct way should be:

String eStr = orginalStr.replaceAll("\"", "\\\\\"");
eStr = eStr.replaceAll("'", "\\\\'");
eStr = eStr.replaceAll("\r\n", "<br>");

It seems my previous code replace a quote by quote itself. The interesting result is the following two lines are quite the same:

String eStr = orginalStr.replaceAll("\"", "\\\"");
String eStr = orginalStr.replaceAll("\"", "\"");

So four more back slashes are required to escape the quote or single quote. However six back slashes are needed to escape back slash itself.