http://stackoverflow.com/questions/508028/number-of-sockets-available-for-a-jdbc-connection-at-windows-2003
"When a TCP connection is closed, they hang around for a little while in the TIME_WAIT state. On Windows, the default time they exist is 240 seconds. It sounds like you might have quite a few tcp connections in the TIME_WAIT state.

You can check this by running netstat. If you have a huge number of tcp connections to the database server in the TIME_WAIT state, a connection pool will fix your issue.

You can try to raise the socket limit, and/or lower the time a connection will stay in the TIME_WAIT state. But this will alter the behavior of all tcp connections. So use a connection pool :) We use dbcp as our connection pool solution in Java."

Address Already in Use Error

There are many solutions to read or write Excel spreadsheets from Java. This HowTo is only about OpenSource (and free) solutions.

JDBC-ODBC Excel driver

This solution lets you access your Excel worksheet with SQL SELECT statement. The required ODBC driver is included in a regular Windows installation and the JDBC-ODBC bridge is used to access the Excel DSN.

See this HowTo for an example.

JExcel

Java Excel API is a java API enabling developers to read, write, and modify Excel spreadsheets dynamically. Any operating system which can run a Java virtual machine can both process and deliver Excel spreadsheets. One nice thing about JExcelApi is that it has no dependencies on any third party libraries.

Example : output an Excel file from a Servlet

import java.io.IOException;
import java.io.OutputStream;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class Sample extends HttpServlet {
 public void doGet
   (HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
  OutputStream out = null;
  try {
   response.setContentType("application/vnd.ms-excel");
   response.setHeader
     ("Content-Disposition", "attachment; filename=sampleName.xls");
   WritableWorkbook w = 
     Workbook.createWorkbook(response.getOutputStream());
   WritableSheet s = w.createSheet("Demo", 0);
   s.addCell(new Label(0, 0, "Hello World"));
   w.write();
   w.close();
  } 
  catch (Exception e){
   throw new ServletException("Exception in Excel Sample Servlet", e);
  } 
  finally{
   if (out != null)
    out.close();
  }
 }
}

See http://jexcelapi.sourceforge.net/

POI

The POI project consists of APIs for manipulating various file formats based upon Microsoft's OLE 2 Compound Document format using pure Java. POI is your Java Excel solution as well as your Java Word solution.

HSSF is the POI Project's pure Java implementation of the Excel '97(-2002) file format and it provides a way to read spreadsheets create, modify, read and write XLS spreadsheets.

Since it's Jakarta project, POI has a dependencies with other JARs (commons,log4j,etc...).

Example : create an Excel file

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");

HSSFRow row = sheet.createRow((short)0);
row.createCell((short)0).setCellValue("HelloWorld");

FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

The name was originally an acronym for "Poor Obfuscation Implementation" (ref: Wikipedia).

See http://jakarta.apache.org/poi/

JXLS

jXLS is a project that allows creation of extremely complex Excel reports just in several lines of code. It is based on Jakarta POI.

With jXLS, all you need is to create XLS template file with all required formatting, formulas etc using specific notation to indicate placement of data and then write a couple lines of code to invoke jXLS engine passing XLS template and the exported data as parameters.

Example :
The XLS Template

Employees               
Name                Age             Payment             Bonus
${employee.name}    ${employee.age} ${employee.payment} ${employee.bonus}
                                    $[SUM(@employee.payment@)]  

with the code

Collection staff = new HashSet();
staff.add(new Employee("Derek", 35, 3000, 0.30));
staff.add(new Employee("Elsa", 28, 1500, 0.15));
Map beans = new HashMap();
beans.put("employee", staff);
XLSTransformer transformer = new XLSTransformer();
transformer.transformXLS(templateFileName, beans, destFileName);

gives the result

Employees               
Name    Age     Payment Bonus
Derek   35      3000    30,00%
Else    28      1500    15,00%
                4500   

See http://jxls.sourceforge.net/

xlSQL

xlSQL is a JDBC Driver for Excel and CSV data sources. Documents can be read and written with SQL as if they were tables in a database.

You can export XLS to XML or SQL INSERT statements. xlSQL includes its own "zero-admin" mySQL database. The documentation is minimal at this time.

See http://xlsql.sourceforge.net/

JCOM

JCOM is a Java to COM bridge library. With JCOM you can call a COM object from Java as if it were a Java object without having to deal with the internals of JNI. The documentation is minimal (in Japanese!).

Example :

import jp.ne.so_net.ga2.no_ji.jcom.excel8.*;
import jp.ne.so_net.ga2.no_ji.jcom.*;
import java.io.File;
import java.util.Date;

class TestExcel {
 public static void main(String[] args) throws Exception {
   ReleaseManager rm = new ReleaseManager();
   try {
    System.out.println("EXCEL startup...");
    // if already started, open new window
    ExcelApplication excel = new ExcelApplication(rm);
    excel.Visible(true);
    // display any information
    System.out.println("Version="+excel.Version());
    System.out.println("UserName="+excel.UserName());
    System.out.println("Caption="+excel.Caption());
    System.out.println("Value="+excel.Value());

    ExcelWorkbooks xlBooks = excel.Workbooks();
    ExcelWorkbook xlBook = xlBooks.Add();   // create new book

    // enumurate all files
    System.out.println
      ("set infomation of files in current directory to cell ...");
    ExcelWorksheets xlSheets = xlBook.Worksheets();
    ExcelWorksheet xlSheet = xlSheets.Item(1);
    ExcelRange xlRange = xlSheet.Cells();

    xlRange.Item(1,1).Value("filename" );
    xlRange.Item(2,1).Value("size" );
    xlRange.Item(3,1).Value("last modified time");
    xlRange.Item(4,1).Value("is directory");
    xlRange.Item(5,1).Value("is file");
    xlRange.Item(6,1).Value("can read");
    xlRange.Item(7,1).Value("can write");

    File path = new File("./");
    String[] filenames = path.list();
    for(int i=0; i<filenames.length; i++) {
        File file = new File(filenames[i]);
        System.out.println(file);
        xlRange.Item(1,i+2).Value( file.getName() );
        xlRange.Item(2,i+2).Value( (int)file.length() );
        xlRange.Item(3,i+2).Value( new Date(file.lastModified()) ); 
        xlRange.Item(4,i+2).Value( file.isDirectory()?"Yes":"No" );
        xlRange.Item(5,i+2).Value( file.isFile()?"Yes":"No" );   
        xlRange.Item(6,i+2).Value( file.canRead()?"Yes":"No" );  
        xlRange.Item(7,i+2).Value( file.canWrite()?"Yes":"No" ); 
    }
    String expression = "=Sum(B2:B"+(filenames.length+1)+")";
    System.out.println
       ("embed equation, calculate sum of filesize: "+expression);
    xlRange.Item(1,filenames.length+2).Value("sum");
    xlRange.Item(2,filenames.length+2).Formula(expression);
    xlRange.Columns().AutoFit();    // fit columns

    // comment out, if print out.
    // output default printer.
    //   System.out.println("print out...");
    //   xlSheet.PrintOut();

    // comment out, if book save to file.
    // if no path, save to(My Documents)
    // System.out.println
    //   ("save to file... (My Documents)\\testExcel.xls");
    // xlBook.SaveAs("testExcel.xls");

    xlBook.Close(false,null,false);
    excel.Quit();

    System.out.println("thank you .");
   }
   catch(Exception e) { e.printStackTrace(); }
   finally { rm.release(); }
  }
}

See http://sourceforge.net/projects/jcom

See also this HowTo for an alternative package to access a COM package from Java.

OpenXLS Java Spreadsheet SDK

OpenXLS claims that it has the best compatibility with complex Excel files and able to handle any kind of Excel file out there without corrupting it. This open source effort is the result of over 6 years of development into it.

See http://www.extentech.com/estore/product_detail.jsp?product_group_id=228

Example (extract 3 images from a workbook, create a new workbook with them) :

doit("testImages.xls","Sheet1");

...

void doit(String finpath, String sheetname){    
  System.out.println("Begin parsing: " + workingdir + finpath);
  WorkBookHandle tbo = new WorkBookHandle(workingdir + finpath);

  try{
    sheet = tbo.getWorkSheet(sheetname);
    // read images from sheet 1 -- .gif, .png, .jpg
    ImageHandle[] extracted = sheet.getImages();
    // extract and output images
    for(int t=0;t<extracted.length;t++) {
      System.out.println("Successfully extracted: " 
         + workingdir + "testImageOut_" 
         + extracted[t].getName()+"."
         +extracted[t].getType());
      FileOutputStream outimg = new FileOutputStream
         (workingdir + extracted[t].getName()+"."
         +extracted[t].getType());
      extracted[t].write(outimg);
      outimg.flush();
      outimg.close();
    }

    tbo = new WorkBookHandle();
    sheet = tbo.getWorkSheet("Sheet1");
    CellHandle a1 = sheet.add
       ("New workbook with 3 images: a gif, a jpg, and a png", "A1");

    // get gif image input stream
    FileInputStream fin = new FileInputStream
       (workingdir + "testImages.gif");

    // add to sheet
    ImageHandle giffy = new ImageHandle(fin, sheet);

    // set picture size and location in sheet
    giffy.setBounds(100, 100, 400, 200);
    giffy.setName("giffy");
    sheet.insertImage(giffy);

    // add to sheet
    for(int x=0;x<100;x++) {
      fin = new FileInputStream(workingdir + "testImages.png");
      ImageHandle jpgy = new ImageHandle(fin, sheet);
      jpgy.setName("heart" + x);
      // set the random x/y coords of picture
      int ix = Math.round((float)((x * (Math.random()*10))));
      jpgy.setX(100 +  ix);
      ix = Math.round((float)((x * (Math.random()*10))));
      jpgy.setY(100 + ix);
      sheet.insertImage(jpgy);
    }
    // get png image input stream
    fin = new FileInputStream(workingdir + "testImages.jpg");
    // add to sheet
    ImageHandle pngy = new ImageHandle(fin, sheet);
    // set just the x/y coords of picture
    pngy.setX(10);
    pngy.setY(200);
    sheet.insertImage(pngy);
  }
  catch(Exception e){
    System.err.println("testImages failed: " + e.toString());
  }
  testWrite(tbo, workingdir + "testImagesOut.xls");
  WorkBookHandle newbook = new WorkBookHandle
     (workingdir + "testImagesOut.xls",0);
  System.out.println("Successfully read: " + newbook);
}

public void testWrite(WorkBookHandle b, String fout){
  try{
    java.io.File f = new java.io.File(fout);
    FileOutputStream fos = new FileOutputStream(f);
    BufferedOutputStream bbout = new BufferedOutputStream(fos);
    bbout.write(b.getBytes());
    bbout.flush();
    fos.close();
  } 
  catch (java.io.IOException e){
    System.err.println("IOException in Tester.  "+e);
  }  
}

See also this HowTo for a way to create a simple XLS without any additional library.

by Gabi Solomon

Preinfo

Before you go right to the comment section and recommend gettext or other similar ways, know that i am talking about content that is manageable from an admin panel or is added by the user

Also this article is based on personal experience and is not necessary the best way to do this.

Building a metalanguage website poses a lot of problems, and one of them is how you store the content in the database for each language.

If you do a google search you will find little resources about it, and most of the are on forums. This seem a bit strange to me, so after i had decided on a database schema for a metalanguage website i decided to post it here in the hope that other people might find it useful and save them some googling time.

As far as i searched there are more or less 4 databases schemas for metalanguage website.

1. Column approach

This approach is very common and basically it duplicates the column of content for each language.

table pages
-- id (int)
-- title_en (varchar)
-- title_es (varchar)
-- content_en (varchar)
-- content_es (varchar)

The way you would query it is by automatically selecting the right columns according to the language chosen:

PLAIN TEXT

SQL:

1. SELECT `id`, `title_en` AS `title`, `content_en` AS `content` FROM `pages`

Or you could select all and do the column selection from php :

PLAIN TEXT

PHP:

1. echo $rowPage['title' . $_SESSION['currentLanguage']];

Advantages

• It doesn't have duplicate content, since there is only one row for each record, and only the language columns are duplicated
• Easy to implement

Disadvantages

• You need to build the watch what column you are working with depending on the language
• Hard to maintain. Although this is a easy way for 2-3 languages its becomes a real drag when you have a lot of columns or a lot of languages
• Hard to add a new language

2. Multirow approach

Another approach that i saw but i have never worked with it. It is simillar to the one above but instead of duplicating the content in columns it does it in rows.

table pages
-- id (int)
-- language_id (int)
-- title (varchar)
-- content (varchar)

So you will basically have 3 rows for the same page if you have 3 languages. The main problem i see with this approach is that it would be a bit tricky to know witch id you will use for the table relations.

Sorry but since i dont really have experience with this i cant show you sql & php examples.

Advantages

• Ease in adding a new language

Disadvantages

• Need to watch the table relations
• A lot of duplicate content. You will have duplicate content for all the columns that are not translated

3. Single Translation table approach

This is an approach that becomes a little more complex then the other 2, but it is more suited for dinamic websites and which have a large number of languages or which intend to add a new language in the future and want to do it with ease.

table languages
-- id (int)
-- name (varchar)

table pages
-- id (int)
-- language_id (int)
-- title (int fk)
-- content (int fk)

table translation
-- id (int)

table translation_entry
-- translation_id (int)
-- language_id (int)
-- content (text)

In this approach you would store the id from the translation table in the title and content columns from the pages table, and then do a join with the translation_entry table based on the language id.

Advantages

• Proper normalization
• Ease in adding a new language

Disadvantages

• Longer joins and query to get the content
• All the translated content goes into one table
• For me it just looks hard to work with and maintain

4. Coupled Translation table approach [my aproach :D]

This is a variation of the above approach that to me seems easier to maintain and work with.
Instead of having just one translation table, you have one for each table. and you move the columns from the pages that need to be translated to the translation table.

table languages
-- id (int)
-- name (varchar)

table pages
-- id (int)

table pages_translation
-- id (int)
-- page_id (int)
-- language_id (int)
-- title (text)
-- content (text)

To get your data you just do a simple join:

PLAIN TEXT

SQL:

1. SELECT * FROM `pages` JOIN `pages_translation` ON `pages`.`id` = `pages_translation`.`page_id` WHERE `map_landmarks_translation`.`language_id`='1'

Advantages

• Proper normalization
• Ease in adding a new language
• Easy to query
• Columns keep there names

Disadvantages

• You have to create translation tables for all your tables that have columns that need to be translated

Conclusion

I am sure that there are other methods of doing a multilingual website, this are just the ones that i thought are most commonly used. My solution is the best, its just the best for me, because it works for my project and its easier for me to work with compared to other approaches.
In the end the best approach is the one that is the best for you. The one that you find the most easier to work with and maintain.

Cheers,
and good coding.

 

What are you preparing for the women’s day?

It’s the same old question for every coming special day every year. The Valentine day, the Women’s Day, the Xmas day, etc. Most of those special day, we celebrate and give the gifts to our loving ones.

It’s very kind of you if you give flowers to them. Flower is the most beautiful thing and it brings a very special idea. Watched in the video, my wife is teaching her brother and another child playing around with flowers to make a very special gift to their mother or sisters or grand mothers or their teachers, of course they are all women :)