import javax.servlet.http.HttpServletRequest;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.SQLException;
import park.jsp.main.ConnectDB;
import park.jsp.common.TableNewId;
import javax.servlet.http.HttpSession;
import java.util.Date;
import park.jsp.common.DateHandle;

/**
 * <p>Title: </p>
 * <p>Description:修改和存储登记卡相关信息 </p>
 * <p>Copyright: Copyright (c) 2005</p>
 * <p>Company: </p>
 * @author 熊炼
 * @version 1.0
 */

public class Register_Refer
{
    /**
     * 此程序需要在scope中额外获取的参数有：type(对象类型)、message(操作类型)、f_emp_name(用户名)
     * 特别:1.操作类型为modify,需要旧的t_bas_*表中所有可更改参数   2.对象类型为动植物,则需要f_k,f_s,f_classno,f_py
     * @param request
     * @return String(操作信息字段)
     */
    public static String main(HttpServletRequest request,HttpSession session)
    {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try
        {
            //连接数据库
            conn=ConnectDB.connect();
            stmt=conn.createStatement();

//            String f_name, f_k_classname,f_s_classname,f_s_ldm,f_j_classname,f_xx,f_cd,f_layer,
//            f_ldm,f_bhc,f_fzcs,f_szxz ,f_ms,f_yt,f_area,f_yy,v_message,v_type,v_tablename;
            String f_name, f_k,f_s,f_classno,f_xx,f_cd,f_layer,f_py,
            f_ldm,f_bhc,f_fzcs,f_szxz ,f_ms,f_yt,f_area,f_yy,v_message,v_type,v_tablename;
            v_message = request.getParameter("message"); //判断消息字段,modify修改,create创建,delete删除
            v_type=(String)request.getAttribute("type");//判断类型(plant植物,animal是动物...)

            //植物不同字段
            f_ms=request.getParameter("t_ms");
            f_yt=request.getParameter("t_yt");
            f_area=request.getParameter("t_area");
            f_yy=request.getParameter("t_yy");

            //执行数据库操作
            String rrSql="";
            int v_flag=0;//v_flag为数据操作是否成功的标志,1为成功,0为失败
            StringBuffer sb=null;
            //判断对象类型
            if("animal".equals(v_type))
            {
              //获取t_bas_animal表中的所有字段(除f_id)
              //获得不能更改的字段
              f_name=request.getParameter("t_name");
              f_k=(String)request.getAttribute("f_k");
              f_s=(String)request.getAttribute("f_s");
              f_classno=(String) request.getAttribute("f_j");
              f_py=(String)request.getAttribute("f_py");
              f_ldm = request.getParameter("t_ldm");
              f_xx = request.getParameter("t_xx");
              f_cd = request.getParameter("t_cd");
              f_layer = request.getParameter("t_layer");

              //从form中获取可以更改的字段,为字段新内容
              f_bhc = request.getParameter("t_bch");
              f_fzcs = request.getParameter("t_fzcs");
              f_szxz = request.getParameter("t_szxz");
              float animal_f_id=0;

              //判断操作类型
              if("create".equals(v_message))
              {
                //获取t_rpt_modify报表操作表中的相关字段
                String f_rpt_no,f_rpt_pid,f_emp_name,f_type,f_new_value,f_ref_date;
                //获取表中f_pid字段
                float rpt_f_id=0;
                try
                {
                    rpt_f_id = TableNewId.getTableNewId(stmt,1,"t_rpt_modi",
                            "f_pid");
                    if(rpt_f_id==0)
                    {
                       return "<script language=javascript>window.alert('系统错误,创建失败!');window.close();</script>";
                    }
                }
                catch(SQLException ex)
                {
                    ex.printStackTrace();
                }
                //其余相关字段
                f_emp_name=(String) session.getAttribute("f_emp_name");
                f_type="0";
                //以逗号分割连接所有属性值
                f_new_value=sb.append(f_name+","+f_k+","+f_s+","+f_classno+","+f_xx+","+f_cd+","+f_layer+","+f_py+","+f_ldm+","+f_bhc+","+f_fzcs+","+f_szxz).toString();
                f_ref_date=new DateHandle().getDate();
                //*************************************还有一些字段未获取***************
                rrSql="";//插入t_rpt_modi表中的sql语句
                v_flag=stmt.executeUpdate(rrSql);
              }
              else if("modify".equals(v_message))
              {
                //获取t_bas_animal中的f_id
                animal_f_id=Integer.parseInt((String)request.getAttribute("animal_f_id"));
                //获取t_rpt_modify报表操作表中的相关字段
                String f_rpt_no,f_rpt_pid,f_emp_name,f_type,f_old_value,f_new_value,f_ref_date,f_old_bhc,f_old_fzcs,f_old_szxz;
                float rpt_f_id=0;
                try
                {
                  rpt_f_id = park.jsp.common.TableNewId.getTableNewId(stmt, 1, "t_rpt_modi",
                                                      "f_pid");
                  if (rpt_f_id == 0) {
                    return "<script language=javascript>window.alert('系统错误,创建失败!');window.close();</script>";
                  }
                }
                catch (SQLException ex) {
                  ex.printStackTrace();
                }
                f_emp_name=(String) session.getAttribute("f_emp_name");
                f_type="1";
                //以逗号分割连接所有属性值
                f_new_value=sb.append(animal_f_id+","+f_name+","+f_k+","+f_s+","+f_classno+","+f_xx+","+f_cd+","+f_layer+","+f_py+","+f_ldm+","+f_bhc+","+f_fzcs+","+f_szxz).toString();
                f_ref_date=new DateHandle().getDate();
                //获取旧动物对应的t_bas_animal表中对应的所有值,并以逗号分割连接,作为f_old_value字段
                f_old_bhc=(String) request.getAttribute("f_bhc");
                f_old_fzcs=(String)request.getAttribute("f_fzcs");
                f_old_szxz=(String) request.getAttribute("f_szxz");
                f_old_value=sb.append(animal_f_id+","+f_name+","+f_k+","+f_s+","+f_classno+","+f_xx+","+f_cd+","+f_layer+","+f_py+","+f_ldm+","+f_old_bhc+","+f_old_fzcs+","+f_old_szxz).toString();
                //*************************************还有一些字段未获取***************
                rrSql = ""; //插入t_rpt_modi表中的sql语句
                v_flag = stmt.executeUpdate(rrSql);
              }
            }
            else if("plant".equals(v_type))
            {

            }

            v_flag=stmt.executeUpdate(rrSql);
             conn.commit();
             if(v_flag==1)
             {
                 return "<script language=javascript>window.alert('保存操作成功!');window.close();</script>";
             }
             else
             {
                 return
                         "<script language=javascript>window.alert('保存操作改失败!');window.close();</script>";
             }
         }
         catch(SQLException ex)
         {
             try
             {
                 conn.rollback();
             }
             catch(SQLException ex1)
             {}
              return "<script language=javascript>window.alert('系统错误，不能修改!');</script>";
         }
         finally
         {
             try
             {
                 if(rs != null)
                 {
                     rs.close();
                     rs = null;
                 }
             }
             catch(Exception ignore)
             {}
             try
             {
                 if(stmt != null)
                 {
                     stmt.close();
                     stmt = null;
                 }
             }
             catch(Exception ignore)
             {}
             try
             {
                 if(conn != null)
                 {
                     conn.rollback();
                     conn.close();
                     conn = null;
                 }
             }
             catch(Exception ignore)
             {}
         }

        }
    }

在这里就会淹没null pointer Exception,编程时一定要注意

import java.io.File;
import java.net.URL;
import java.io.InputStream;
import java.io.IOException;
import java.io.BufferedWriter;
import java.io.FileWriter;

/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2000</p>
 * <p>Company: </p>
 * @author 熊炼
 * @version 1.0
 */

public class FileHandle
{

  public FileHandle()
  {
  }

  /**
   * filename path和URL之间的转换
   */
  public void _File_URL()
  {
    String path = File.separator + "a";
    System.out.println(path);
    //coverting Between a filename and a URL
    //create a file object
    File file = new File("filename");

    //covert a file object to a URL
    URL url = null;
    try
    {
      url=file.toURL();
      System.out.println(url);
    }
    catch(Exception  e)
    {
      e.printStackTrace();
    }

    //convert a URL to a file object
    file=new File(url.getFile());

    //read the file using the URL
    try
    {
      InputStream is=url.openStream();
      //read from the InputStream
    }
    catch(IOException e)
    {
      e.printStackTrace();
    }
  }

  /**
   * 从relative path到absolute path之间的转换
   */
  public void rel_to_abspath()
  {
    File file=new File("filename.txt");//filename.txt doesn't need exist
    file=file.getAbsoluteFile();//C:\Documents and Settings\xl\桌面\First\filename

    file=new File("test"+File.separator+"filename.txt");
    file=file.getAbsoluteFile();//C:\Documents and Settings\xl\桌面\First\test\filename.txt
  }

  /**
   * 判断两个filename path是否指向同一file
   * @return boolean
   */
  public boolean isSameFile()
  {
    File file1=new File("filename");
    File file2=new File("/filename");
    //file1.equals(file2)=false;
    try
    {
      //利用File.getCanonicalFile()消除多余的symbol links或者"."和".."
      file1.getCanonicalFile();
      file2.getCanonicalFile();
    }
    catch(Exception e){}
    return file1.equals(file2);//true
  }

  /**
   * 创建一个临时temporary文件
   * @param args
   */
  public void createTempFile()
  {
    try
      {
        //create temporary file
        File temp = new File("temp", ".txt");
        //推出程序时删除temp文件
        temp.deleteOnExit();

        //向temp文件中写content
        BufferedWriter out = new BufferedWriter(new FileWriter(temp));
        out.write("熊炼");
        out.close();
      }
      catch(IOException e)
      {
        e.printStackTrace();
      }
  }

  /**
   * File、directory更名或File移动到另一个位置
   * @param args
   */
  public void rename_move_File()
  {
    //rename a File or directory
    File file1=new File("oldname");
    File file2=new File("newname");
    file1.renameTo(file2);

    //move File
    File file=new File("filename");
    File dir=new File("directoryname");
    file.renameTo(new File(dir,file.getName()));
  }

  public static void main(String[] args)
  {

  }

  }

JAVA EXCEL API简介

Java Excel是一开放源码项目，通过它Java开发人员可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件。使用该API非Windows操作系统也可以通过纯Java应用来处理Excel数据表。因为是使用Java编写的，所以我们在Web应用中可以通过JSP、Servlet来调用API实现对Excel数据表的访问。

提供以下功能：

• 从Excel 95、97、2000等格式的文件中读取数据；
• 读取Excel公式（可以读取Excel 97以后的公式）；
• 生成Excel数据表（格式为Excel 97）；
• 支持字体、数字、日期的格式化；
• 支持单元格的阴影操作，以及颜色操作；
• 修改已经存在的数据表；
• 能够读取图表信息

1.应用示例：
包括从Excel读取数据,生成新的Excel，以及修改Excel
package common.util;

import jxl.*;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import jxl.write.Number;
import jxl.write.Boolean;

import java.io.*;

/**
 * Created by IntelliJ IDEA.
 * User: xl
 * Date: 2005-7-17
 * Time: 9:33:22
 * To change this template use File | Settings | File Templates.
 */
public class ExcelHandle
{
    public ExcelHandle()
    {
    }

    /**
     * 读取Excel
     *
     * @param filePath
     */
    public static void readExcel(String filePath)
    {
        try
        {
            InputStream is = new FileInputStream(filePath);
            Workbook rwb = Workbook.getWorkbook(is);
            //Sheet st = rwb.getSheet("0")这里有两种方法获取sheet表,1为名字，而为下标，从0开始
            Sheet st = rwb.getSheet("original");
            Cell c00 = st.getCell(0,0);
            //通用的获取cell值的方式,返回字符串
            String strc00 = c00.getContents();
            //获得cell具体类型值的方式
            if(c00.getType() == CellType.LABEL)
            {
                LabelCell labelc00 = (LabelCell)c00;
                strc00 = labelc00.getString();
            }
            //输出
            System.out.println(strc00);
            //关闭
            rwb.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    /**
     * 输出Excel
     *
     * @param os
     */
    public static void writeExcel(OutputStream os)
    {
        try
        {
            /**
             * 只能通过API提供的工厂方法来创建Workbook，而不能使用WritableWorkbook的构造函数，
             * 因为类WritableWorkbook的构造函数为protected类型
             * method(1)直接从目标文件中读取WritableWorkbook wwb = Workbook.createWorkbook(new File(targetfile));
             * method(2)如下实例所示 将WritableWorkbook直接写入到输出流

             */
            WritableWorkbook wwb = Workbook.createWorkbook(os);
            //创建Excel工作表 指定名称和位置
            WritableSheet ws = wwb.createSheet("Test Sheet 1",0);

            //**************往工作表中添加数据*****************

            //1.添加Label对象
            Label label = new Label(0,0,"this is a label test");
            ws.addCell(label);

            //添加带有字型Formatting对象
            WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
            WritableCellFormat wcf = new WritableCellFormat(wf);
            Label labelcf = new Label(1,0,"this is a label test",wcf);
            ws.addCell(labelcf);

            //添加带有字体颜色的Formatting对象
            WritableFont wfc = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,
                    UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);
            WritableCellFormat wcfFC = new WritableCellFormat(wfc);
            Label labelCF = new Label(1,0,"This is a Label Cell",wcfFC);
            ws.addCell(labelCF);

            //2.添加Number对象
            Number labelN = new Number(0,1,3.1415926);
            ws.addCell(labelN);

            //添加带有formatting的Number对象
            NumberFormat nf = new NumberFormat("#.##");
            WritableCellFormat wcfN = new WritableCellFormat(nf);
            Number labelNF = new jxl.write.Number(1,1,3.1415926,wcfN);
            ws.addCell(labelNF);

            //3.添加Boolean对象
            Boolean labelB = new jxl.write.Boolean(0,2,false);
            ws.addCell(labelB);

            //4.添加DateTime对象
            jxl.write.DateTime labelDT = new jxl.write.DateTime(0,3,new java.util.Date());
            ws.addCell(labelDT);

            //添加带有formatting的DateFormat对象
            DateFormat df = new DateFormat("dd MM yyyy hh:mm:ss");
            WritableCellFormat wcfDF = new WritableCellFormat(df);
            DateTime labelDTF = new DateTime(1,3,new java.util.Date(),wcfDF);
            ws.addCell(labelDTF);

            //添加图片对象,jxl只支持png格式图片
            File image = new File("f:\\2.png");
            WritableImage wimage = new WritableImage(0,1,2,2,image);//0,1分别代表x,y.2,2代表宽和高占的单元格数
            ws.addImage(wimage);
            //写入工作表
            wwb.write();
            wwb.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    /**
     * 拷贝后,进行修改,其中file1为被copy对象，file2为修改后创建的对象
     * 尽单元格原有的格式化修饰是不能去掉的，我们还是可以将新的单元格修饰加上去，
     * 以使单元格的内容以不同的形式表现
     * @param file1
     * @param file2
     */
    public static void modifyExcel(File file1,File file2)
    {
        try
        {
            Workbook rwb = Workbook.getWorkbook(file1);
            WritableWorkbook wwb = Workbook.createWorkbook(file2,rwb);//copy
            WritableSheet ws = wwb.getSheet(0);
            WritableCell wc = ws.getWritableCell(0,0);
            //判断单元格的类型,做出相应的转换
            if(wc.getType == CellType.LABEL)
            {
                Label label = (Label)wc;
                label.setString("The value has been modified");
            }
            wwb.write();
            wwb.close();
            rwb.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    //测试
    public static void main(String[] args)
    {
        try
        {
            //读Excel
            ExcelHandle.readExcel("f:/testRead.xls");
            //输出Excel
            File fileWrite = new File("f:/testWrite.xls");
            fileWrite.createNewFile();
            OutputStream os = new FileOutputStream(fileWrite);
            ExcelHandle.writeExcel(os);
            //修改Excel
            ExcelHandle.modifyExcel(new file(""),new File(""));
        }
        catch(Exception e)
        {
           e.printStackTrace();
        }
    }
}

2.在jsp中做相关测试，创建一个writeExcel.jsp
<%
response.reset();//清除Buffer
response.setContentType("application/vnd.ms-excel");
File fileWrite = new File("f:/testWrite.xls");
fileWrite.createNewFile();
new FileOutputStream(fileWrite);
ExcelHandle.writeExcel(new FileOutputStream(fileWrite));
%>
在IE中浏览writeExcel.jsp就可以动态生成Excel文档了，其中response.setContentType("application/vnd.ms-excel");语句必须要，才能确保不乱码，在jsp中输入<%@page contentType="application/vnd.ms-excel;charset=GBK"%>不行。

Excel操作系列：

jxl <1>
POI<1>
POI<2>

IDEA
IDEA的使用技巧

其它：
JSP+XML构架网站的实例
jspSmartUpload上传下载全攻略

*********************************
调试      部署    数据库SQL操作（旧项目中的SQL借鉴）
**********************************

1.先建一个layout.jsp布局页面

******

    <tr>
     <td height="113" colspan="2" scope="col">
     <tiles:insert attribute="top"/>
     </td>
   </tr>
    <tr>
     <td width="21%" height="409" scope="row">
     <tiles:insert attribute="left"/>
     </td>
      <td width="79%" height="409" scope="row">
     <tiles:insert attribute="main"/>
   </td>
   </tr>

*******

2.新建一个 tiles-defs.xml文件，并在其中做相应的配置。

*********

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE tiles-definitions PUBLIC "-//Apache Software Foundation//DTD Tiles Configuration 1.1//EN" "http://jakarta.apache.org/struts/dtds/tiles-config_1_1.dtd">
<tiles-definitions>
 <definition name="member-definition" path="/Jsp/layout.jsp">
  <put name="top" value="/mTop.do"/>
  <put name="left" value="/mLeft.do"/>
  <put name="main" value="/defaultMmain.do"/>
 </definition>
</tiles-definitions>

*********

这样就相当于通过xml文件定义了一个member.jsp.

3.在web.xml和struts-config.xml和web.xml中做相应配置.

在struts-config.xml中配置如下：

***********

  <plug-in className="org.apache.struts.tiles.TilesPlugin">
   <set-property property="definitions-config" value="/WEB-INF/tiles-defs.xml"/>
   <set-property property="definitions-parser-validate" value="true"/>
   </plug-in>

***********

在web.xml中配置如下：

************

   <init-param>
    <param-name>definitions-config</param-name>
    <param-value>/WEB-INF/tiles-defs.xml</param-value>
   </init-param>

************

4.调用Tiles组件

Tiles组件是通过Struts Action来调用的，在struts-config.xml中做如下action配置：

*************

      <action
        path="/member"
        parameter="member-definition"
        type="org.apache.struts.actions.ForwardAction"/>

**************

然后可以通过访问/member.do访问相当于member.jsp的Tiles组件.

II ---------------------tiles的高级使用--------------------------

1 .tiles组件的嵌套

然后可以通过访问/member.do访问相当于member.jsp的Tiles组件.

*************

<tiles-definitions>
 <definition name="member-definition" path="/Jsp/layout.jsp">
  <put name="top" value="menu-definition" type="definition"/>
  <put name="left" value="/mLeft.do"/>

  <put name="main" value="/defaultMmain.do"/>  

</definition>

************

其中menu-definiton作为一个子tiles组件而单独配置，这样就可以把一个个简单的tiles组件组合成一个复杂的tiles组件。 

2．Tiles组件的扩展

Tiles组件的扩展，在有很多tiles组件使用相同的模板页面layout.jsp的时候，特别有用处，我们先定义一个通用的tiles组件如base-definition，其中组件中相应的地方value设置为空。

*************

<definition name="base-definition" path="/Jsp/layout.jsp">
  <put name="top" value="/mTop.do"/>
  <put name="left" value="/mLeft.do"/>
  <put name="main" value=" "/>
 </definition>
*************

其它tiles组件可以通过集成它，并把name="main"中的value设置成自己特有的，

如：

**************

<definition name="member-definition" extends="base.definition">

 <put name="main" value="/defaultMmain.do "/>

 </definition>

******************