zclysearchlistaction.java

功能是公安和学校的管理系统,用struts+hibernate+spring等匡架组成,在eclepse就能编译

package com.comingnet.action;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.comingnet.bean.UserInfor;
import com.comingnet.intercepter.UserSessionAware;
import com.db.ProxoolAction;
import com.db.page;
import com.opensymphony.xwork2.ActionSupport;

public class ZclySearchListAction extends ActionSupport implements UserSessionAware{
	private UserInfor userSession=null;
	private String auid_test="12";
	private String zclyr;                     //领用人
	private String zcmc;                      //资产名称
	private String beginDate;                 //开始时间
	private String lastDate;                  //截止时间
	List zclySearchlist=null;
	private String pageStr;                           //分页字符串
	private String pageno;                            //当前页
	private int pagecount=0;                          //总记录数
	private int rownum=10;                            //每页记录数
	private int pagerow=10;                           //显示多少页
	
	public int getPagecount() {
		return pagecount;
	}
	public void setPagecount(int pagecount) {
		this.pagecount = pagecount;
	}
	public String getPageno() {
		return pageno;
	}
	public void setPageno(String pageno) {
		this.pageno = pageno;
	}
	public int getPagerow() {
		return pagerow;
	}
	public void setPagerow(int pagerow) {
		this.pagerow = pagerow;
	}
	public String getPageStr() {
		return pageStr;
	}
	public void setPageStr(String pageStr) {
		this.pageStr = pageStr;
	}
	public int getRownum() {
		return rownum;
	}
	public void setRownum(int rownum) {
		this.rownum = rownum;
	}

	public List getZclySearchlist() {
		return zclySearchlist;
	}
	public void setZclySearchlist(List zclySearchlist) {
		this.zclySearchlist = zclySearchlist;
	}
	public String getBeginDate() {
		return beginDate;
	}
	public void setBeginDate(String beginDate) {
		this.beginDate = beginDate;
	}
	public String getLastDate() {
		return lastDate;
	}
	public void setLastDate(String lastDate) {
		this.lastDate = lastDate;
	}
	public String getZclyr() {
		return zclyr;
	}
	public void setZclyr(String zclyr) {
		this.zclyr = zclyr;
	}
	public String getZcmc() {
		return zcmc;
	}
	public void setZcmc(String zcmc) {
		this.zcmc = zcmc;
	}
	public String getAuid_test() {
		return auid_test;
	}
	public void setAuid_test(String auid_test) {
		this.auid_test = auid_test;
	}
	public UserInfor getUserSession() {
		return userSession;
	}
	public void setUserSession(UserInfor userSession) {
		this.userSession = userSession;
	}
	public String execute()
	{
		System.out.println("------------------->"+getZcmc()+"      "+getZclyr()+"        "+getBeginDate()+"        "+getLastDate());
		String sql0="";
		if(getZcmc()!=null&&!getZcmc().equals(""))
			sql0=sql0+" and asset_mc like '%"+getZcmc()+"%'";
		if(getZclyr()!=null&&!getZclyr().equals(""))
			sql0=sql0+" and ass_ly_name like '%"+getZclyr()+"%'";
		if(getBeginDate()!=null&&!getBeginDate().equals(""))
			sql0=sql0+" and ass_ly_sj>='"+getBeginDate()+"'";
		if(getLastDate()!=null&&!getLastDate().equals(""))
			sql0=sql0+" and ass_ly_sj<='"+getLastDate()+"'";
		
		String pageno1=getPageno(); //得到当前页
		if(pageno1==null)
			 pageno1="1";
		
		String flag;
		List newlist=new ArrayList();
		Connection conn=null;
		ResultSet rs=null;
		PreparedStatement stmt=null;
		
		try
		  {
			conn=ProxoolAction.getConnection();
			stmt=conn.prepareStatement("select count(*) from assets_ly a,assets_dz b where a.ass_id=b.asset_id"+sql0);
			rs=stmt.executeQuery();
			if(rs.next())
			{
				pagecount=rs.getInt(1);
			}
			setPagecount(pagecount);                    //向前台传回总记录数
			ProxoolAction.closeResultSet(rs);
			ProxoolAction.closeStatement(stmt);
			
			page page1=new page();
			page1.setPageString(pagecount,rownum,Integer.parseInt(pageno1),pagerow,"");
			String pageStr=page1.getPageString();
		    setPageStr(pageStr);                         //设置分页的字符串	
			
		    int cupage=Integer.parseInt(pageno1)-1;
		    String sqlStr="select top  "+rownum+" ass_ly_id,ass_ly_name,ass_ly_shr,ass_ly_lrr,ass_ly_sl,ass_cu_sl,Convert(varchar(20),ass_ly_sj,120)as ass_ly_sj,asset_mc,asset_jldw   from assets_ly a,assets_dz b  where  a.ass_id=b.asset_id "+sql0+" and (ass_ly_id not in (select top "+cupage*rownum+" ass_ly_id  from  assets_ly a,assets_dz b   where a.ass_id=b.asset_id "+sql0+" order by  ass_ly_id DESC))  order by  ass_ly_id  DESC";
			System.out.println("assets_ly:"+sqlStr);
		    stmt=conn.prepareStatement(sqlStr);
			rs=stmt.executeQuery();
			String name="";
			String nameStr="user_xh";
			int j=0;
			
			HashMap row=null;
			ResultSetMetaData rsmd=rs.getMetaData();
	        int columnCount=rsmd.getColumnCount();	
	        while(rs.next())
	   	     {
	              j++;
	        	  row=new HashMap();
	          	  for(int i=1;i<=columnCount;i++)
	          	   {
	                   name=rsmd.getColumnName(i);
	                   row.put(nameStr,""+j);
	                   row.put(name.toLowerCase(),rs.getString(i));
	                }
	          	newlist.add(row);
	         }
	       
	        setZclySearchlist(newlist);   //此处不能利用getList1(),get方法是获取从页面传过来的值
	        flag=SUCCESS;
		  }catch(Exception e)
		  {
			  flag=ERROR;
	      }finally
		  {
	    	  ProxoolAction.closeResultSet(rs);
	    	  ProxoolAction.closeStatement(stmt);
	    	  ProxoolAction.closeConnection(conn);
		  }
	   return flag;
	 }
}