SELECT COUNT(*) AS cnt FROM invoice

SELECT invoiceno, password FROM invoice LIMIT ?, ?

invoice.jsp?pageno=1&numofrec=800000

package com.esurfer.util; public class PaginationUtil {
 /*
 function:  Pagination
 author:  esurfer
 date:   2005-08-28
 parameter:
  1. numOfRec– total number of records
  2. recPerPage– number of rows per page
  3. currPage–current page number
  4. url–the requested url
 return: pagination links with First, Prev, Next, Last, Go to
 */
 public static String Pagination(int numOfRec, int recPerPage, int currPage, String url) {
  StringBuffer ressurl = new StringBuffer();
  
  String spChar = “?”;
  if (url.indexOf(spChar) != -1) spChar = “&”;

  if (numOfRec > recPerPage) {
   int pages = (int)java.lang.Math.ceil((double)numOfRec / (double)recPerPage);  // Get total number of pages
   
   ressurl.append(”<div nowrap>n”);
   ressurl.append(”<font face=arial size=-1>”);
   ressurl.append(”Results Page: “).append(currPage).append(” / “).append(pages);
   
   // Display link to first page
   ressurl.append(”&nbsp;<b><a href=”").append(url).append(spChar).append(”pageno=1&numofrec=”)
       .append(numOfRec).append(””>First</a></b>n”);
   
   // Link to previous page
   if (currPage > 1) {
       ressurl.append(”|&nbsp;<b><a href=”").append(url).append(spChar).append(”pageno=”)
          .append(currPage - 1).append(”&numofrec=”).append(numOfRec).append(””>Prev</a></b>n”);
   }
   
   // Display link to next page
   if (currPage < pages) {
       ressurl.append(”|&nbsp;<b><a href=”").append(url).append(spChar).append(”pageno=”)
          .append(currPage + 1).append(”&numofrec=”).append(numOfRec).append(””>Next</a></b>n”);
   }
   
   // Link to last page
   ressurl.append(”|&nbsp;<b><a href=”").append(url).append(spChar).append(”pageno=”)
       .append(pages).append(”&numofrec=”).append(numOfRec).append(””>Last</a></b>n”);
   
   // Drop down navigation 
   ressurl.append(”&nbsp;&nbsp;Go to?&nbsp;<select id=”pageno” name=”pageno” onchange=”pagination()”>n”);
   
   // If number of pages > 200 
   //    display only 20 pagination links in the drop down list
   // else
   //    display all pagination links in the drop down list
   int iBeginPageNo = 1, iEndPageNo = pages;
   if (pages >= 200) { 
   
       // Calculate the beginning index
       int temp = currPage - 20;    
       if (temp <= 0) {
           iBeginPageNo = 1;
       } else {
           iBeginPageNo = temp;
       }
                
       // Calculate the ending index
       temp = currPage + 20;
       if (temp >= pages) {
           iEndPageNo = pages;
       } else {
           iEndPageNo = temp;
       }
       
   }
   for (int i = iBeginPageNo; i <= iEndPageNo; i++) {
       if (i == currPage) {
           ressurl.append(”<option value=”").append(i).append(”” selected>”).append(i).append(”</option>n”);
       } else {
           ressurl.append(”<option value=”").append(i).append(””>”).append(i).append(”</option>n”);
       }
   }
   ressurl.append(”</select></font>n”);
   ressurl.append(”</div>n”);
   ressurl.append(”<script type”=text/javascript”>n”);
   ressurl.append(”function pagination() {n”);
   ressurl.append(”location.href=’”).append(url).append(spChar).append(”pageno=’ + document.getElementById(’pageno’).value”)
     .append(” + ‘&numofrec=”).append(numOfRec).append(”‘;n”);
   ressurl.append(”}n”);
   ressurl.append(”</script>n”);
  }
  return ressurl.toString();
 }
}

package com.esurfer.report;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Map;
import com.esurfer.db.DBConn;
import com.esurfer.util.PaginationUtil;
public class Invoice {
    static String sqlcnt = “SELECT COUNT(*) AS cnt FROM invoice”;;
    static String url = “invoice.jsp”;
    static int recPerPage = 15;

    public static String getInvoiceList(int numOfRec, int currPage) {
        int iStartLimit;
        if (currPage == 1) {
            numOfRec = DBConn.getNumOfRec(sqlcnt); // get total number of records only the first time
            iStartLimit = 0;
         currPage = 1;
        } else {
         iStartLimit = (currPage -1) * recPerPage;
        }
        StringBuffer result = new StringBuffer();
        StringBuffer sql = new StringBuffer(”SELECT invoiceno, password FROM invoice”).append(” LIMIT “).append(”?, ?”);
        ArrayList list = DBConn.searchToMapList(sql.toString(), new Object[]{new Integer(iStartLimit), new Integer(recPerPage)});
        Iterator iterator = list.iterator();
        Map map = null;
  while (iterator.hasNext()) {
      map = (Map)iterator.next();
      result.append(”“).append(map.get(”invoiceno”)).append(”“).append(map.get(”password”)).append(”n”);
  }
  result.append(”“).append(PaginationUtil.Pagination(numOfRec, recPerPage, currPage, url)).append(”“);
  iterator = null;
  list = null;
  map = null;
        return result.toString();
    }
}

<%@page pageEncoding=”UTF-8″ contentType=”text/html; charset=UTF-8″ %>
<%@page import=”com.esurfer.report.*”%>
<%
String numofrec = request.getParameter(”numofrec”);
if (numofrec == null) numofrec = “0″;
String pageno = request.getParameter(”pageno”);
if (pageno == null) pageno = “1″;
%>
<html>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=UTF-8″/>
<title>Pagination Test</title>
</head>
<body>
<table border=”1″ cellpadding=”0″ cellspacing=”0″ width=”100%”>
<thead>
  <tr><th>Invoice No</th><th>Password</th></tr>
</thead>
<tbody>
<%=Invoice.getInvoiceList(Integer.parseInt(numofrec), Integer.parseInt(pageno))%>
</tbody>
</table>
</body>
</html>

Invalid project description.

D:/eclipse/workspace/report and D:/eclipse/workspace overlap

<?xml version=”1.0″ encoding=”UTF-8″?>
<projectDescription>
 <name>test</name>
 <comment></comment>
 <projects>
 </projects>
 <buildSpec>
  <buildCommand>
   <name>org.eclipse.jdt.core.javabuilder</name>
   <arguments>
   </arguments>
  </buildCommand>
 </buildSpec>
 <natures>
  <nature>org.eclipse.jdt.core.javanature</nature>
  <nature>com.sysdeo.eclipse.tomcat.tomcatnature</nature>
 </natures>
</projectDescription>

 <meta name=‘ICBM’ content=“38.3045, 116.8532″ />
 <meta name=‘DC.title’ content=“E-Surfer’s Weblog” />
 <meta name=‘geo.position’ content=“38.3045;116.8532″ />

package com.esurfer.dbutil;

import java.io.File;
import java.io.OutputStream;
import java.net.URL;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class DataExport {
    /** excel template file */
    private String templatefile = null;
   
    /** data to export */
    private ArrayList  datasource = null;
   
    /**
     * column names, excel columns should be in
     * the same order (and number) of table fields.
    */
    private String[] columnnames = null;
   
    /** response to write to */
    private HttpServletResponse response = null;
   
    /** output file name */
    private String outputfile;
   
    /** row number begin to write data */
    private int rowno;
   
    public void setColumnnames(String[] columnnames) {
        this.columnnames = columnnames;
    }
   
    public void setOutputfile(String outputfile) {
        this.outputfile = outputfile;
    }
   
    public void setDatasource(ArrayList datasource) {
        this.datasource = datasource;
    }
   
    public void setTemplatefile(String templatefile) {
        this.templatefile = templatefile;
    }
   
    public void setResponse(HttpServletResponse response) {
        this.response = response;
    }
   
 public void setRowno(int rowno) {
  this.rowno = rowno;
 }
 
    /**
     * get file saved in WEB-INF/classes directory.
     *
     * @param fileName file name to be retrieved
     * @return File or null if the file is not exist
     */
    public File getResource(String fileName) {
        URL url = this.getClass().getResource(”/” + fileName);
        return url != null ? new File(url.getFile()): null;
    }
   
    /**
     * Export data to excel file
     */
    public void export() {
        Workbook workbook;
        WritableWorkbook w;
        Label label;
        int currentRow = rowno;
        try {
         OutputStream out = null;
            response.reset();
            response.setContentType(”application/vnd.ms-excel”);
           response.setHeader(”Content-Disposition”, “attachment; filename=” + outputfile);
          
           // creates a readable spreadsheet using the excel template
           workbook = Workbook.getWorkbook(getResource(templatefile));
          
           // obtain a writable version of this spreadsheet
           out = response.getOutputStream();
            w = Workbook.createWorkbook(out, workbook);
            WritableSheet sheet = w.getSheet(0);
           
            WritableFont font = new WritableFont(WritableFont.createFont(”Arial”), 10);
            WritableCellFormat format = new WritableCellFormat(font);
            format.setAlignment(Alignment.RIGHT);
            format.setVerticalAlignment(VerticalAlignment.JUSTIFY);
            format.setBorder(Border.ALL, BorderLineStyle.THIN);
           
            // write data to excel
            Iterator iterator = datasource.iterator();
            Map map = null;
            int cols = columnnames.length;
            while (iterator.hasNext()) {
                sheet.insertRow(currentRow);
                map = (Map)iterator.next();
               
                for (int i = 0; i < cols; i++) {
                    label = new Label(i, currentRow, “” + map.get(columnnames[i]).toString());
                    label.setCellFormat(format);
                    sheet.addCell(label);
                }
                currentRow++;
            }
           w.write();
           w.close();
           if (out != null) out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

<%@ page pageEncoding=”UTF-8″ contentType=”text/html; charset=UTF-8″
%><%@ page import=”com.esurfer.product.*”
%><jsp:useBean id=”exp” class=”com.esurfer.dbutil.DataExport”/><%
String sExport = request.getParameter(”export”);
if (sExport != null) {
   exp.setTemplatefile(”producttpl.xls”);
   exp.setOutputfile(”ProductRep.xls”);
   exp.setColumnnames(new String[]{”pid”, “pname”, “amount”, “price”});
   exp.setRowno(2);
   exp.setDatasource(Product.getProductList());
   exp.setResponse(response);
   exp.export();
   return;
}
%>
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01//EN” “http://www.w3.org/TR/html4/strict.dtd“>
<html>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=UTF-8″/>
<title>Export data from MySQL to excel</title>
</head>
<body>
<form name=”expform” method=”post”>
<input type=”hidden” name=”export” value=”true” />
<input type=”submit” value=”Export” />
</form>
</body>
</html>

package com.esurfer.dbutil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;

import javax.servlet.ServletInputStream;
import javax.servlet.http.HttpServletRequest;

import com.esurfer.db.DBConn;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;

public class DataImport {
    private HttpServletRequest request = null;
   
    // column names of the MySQL table
    // which should be in the same order as the excel columns
    private String[] columnNames = null;
   
    // table name to import to
    private String tableName = null;
   
    static Connection conn = null;
   
    static PreparedStatement pst = null;
   
    public void setColumnNames(String[] columnNames) {
        this.columnNames = columnNames;
    }
   
    public void setRequest(HttpServletRequest request) {
        this.request = request;
    }
   
    public void setTableName(String tableName) {
        this.tableName = tableName;
    }
   
    // import data from excel to mysql
    public boolean importdata() {
        boolean result = false;
        try {
            ServletInputStream is = request.getInputStream();
            byte[] junk = new byte[1024];
            int bytesRead = 0;
           
            // strip off the HTTP information from input stream
            // the first four lines are request junk
            bytesRead = is.readLine(junk, 0, junk.length);
            bytesRead = is.readLine(junk, 0, junk.length);
            bytesRead = is.readLine(junk, 0, junk.length);
            bytesRead = is.readLine(junk, 0, junk.length);
           
            // create the workbook object from the ServletInputStream
            Workbook workbook = Workbook.getWorkbook(is);
            Sheet sheet = workbook.getSheet(0);
            Cell cell = null;
           
            // prepare the insert sql statement
            StringBuffer sql = new StringBuffer(”INSERT INTO “).append(tableName).append(”(”);
            StringBuffer params = new StringBuffer(”VALUES(”);
            int cols = columnNames.length;
            for (int i = 0; i < cols; i++) {
                sql.append(columnNames[i]).append(”,”);
                params.append(”?,”);
            }         
            sql = sql.deleteCharAt(sql.length() - 1).append(”)”).append(params.deleteCharAt(params.length() - 1)).append(”)”);
           
            // get database connection
            conn = DBConn.getConnection();
            conn.setAutoCommit(false);
   pst = conn.prepareStatement(sql.toString()); 
   SimpleDateFormat df = new SimpleDateFormat(”yyyy-MM-dd”);
   // read data from the excel spreadsheet
   // the code here assumes that the data begin in row 2 [A2]
   int rows = sheet.getRows();
   for (int i = 1; i < rows; i++) {
       for (int j = 0; j < cols - 1; j++) {
           cell = sheet.getCell(j, i);
           if (cell.getType() == CellType.DATE) {
                    DateCell datecell = (DateCell)cell;
                    pst.setString(j + 1, df.format(datecell.getDate()));
           } else {
                    pst.setString(j + 1, cell.getContents());
           }      
       }
       pst.executeUpdate();
   }
   conn.commit();
   conn.setAutoCommit(true);
   result = true;
   // close the workbook and free up memory
            workbook.close();
        } catch (Exception e) {
             try {
                conn.rollback();
            } catch (SQLException ex) {
            }
            result = false;
        } finally {
            closeDB();
        }
        return result;
     }
   
    private static void closeDB() {
     try {
         if (pst != null) {
             pst.close();
             pst = null;
         }
         if (conn != null) {
          conn.close();
          conn = null;
         }
     } catch (Exception e) {}
 }
}

<%@ page contentType=”text/html; charset=utf-8″ language=”java” errorPage=”" %>
<jsp:useBean id=”imp” class=”com.esurfer.dbutil.DataImport” />
<%
String operate = request.getParameter(”imp”);
if (”true”.equals(operate)) {
    imp.setRequest(request);
    imp.setTableName(”products”);
    imp.setColumnNames(new String[]{”pid”, “pname”, “amount”, “price”});
    imp.importdata();
} 
%>
<html>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″>
<title>Import data from excel to MySQL</title>
</head>
<body>
<form name=”impform” method=”post” action=”?imp=true” enctype=”multipart/form-data”>
    Select the Excel file to import?<br />
    <input name=”filename” type=”file” size=”80″ maxlength=”40″>
    <impur type=”submit” value=”Import” />
</form> 
</body>
</html>

	A	B	C	D
1	pid	pname	amount	price
2	1	Computer	100	4000
3	2	Software	200	3000