pageuserinfodao.java

一个简单的汽车租赁系统 jsp+struts+oracle

package dao;

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

import page.Page;
import page.PageCustomer;

import domian.user.Car;
import domian.user.Check;
import domian.user.Customer;
import domian.user.RentTable;
import domian.user.Role;
import domian.user.UserInfo;

public class PageUserInfoDao {
	public String genSQLUerinfo(UserInfo userinfo) {
		StringBuffer sb = new StringBuffer("select * from users,role where 1=1 ");
		if (userinfo.getUsername() != null && userinfo.getUsername().length() > 0)
			sb.append("and USERNAME='").append(userinfo.getUsername()).append("'");
		if (userinfo.getIdentity() != null && userinfo.getIdentity().length() > 0)
			sb.append("and IDENTITY='").append(userinfo.getIdentity()).append("'");
		if (userinfo.getAddRess() != null && userinfo.getAddRess().length() > 0)
			sb.append("and ADDRESS='").append(userinfo.getAddRess()).append("'");
		if (userinfo.getPosition() != null && userinfo.getPosition().length() > 0)
			sb.append("and POSITION='").append(userinfo.getPosition()).append("'");
		if (userinfo.getPhone() != null && userinfo.getPhone().length() > 0)
			sb.append("and PHONE='").append(userinfo.getPhone()).append("'");		
		if (userinfo.getUserpwd() != null && userinfo.getUserpwd().length() > 0)
			sb.append("and USERPWD='").append(userinfo.getUserpwd()).append("'");
		if (userinfo.getFullname() != null && userinfo.getFullname().length() > 0)
			sb.append("and FULLNAME='").append(userinfo.getFullname()).append("'");		
		if (userinfo.getMale() != null && userinfo.getMale().equals("true")){
			sb.append("and SEX=").append(1);	
		}
		if(userinfo.getMale() != null && userinfo.getMale().equals("false")){
			sb.append("and SEX=").append(0);
		}
		if(userinfo != null){
			sb.append(" And users.roleid=role.roleid ");
		}
		return sb.toString();

	}
	public String genSQLCustomer(Customer customer) {
		StringBuffer sb = new StringBuffer("select * from customers where 1=1 ");
		if (customer.getIdentity() != null && customer.getIdentity().length() > 0)
			sb.append(" and IDENTITY='").append(customer.getIdentity()).append("'");
		if (customer.getAddress() != null && customer.getAddress().length() > 0)
			sb.append("and ADDRESS='").append(customer.getAddress()).append("'");
		if (customer.getCareer() != null && customer.getCareer().length() > 0)
			sb.append("and CAREER='").append(customer.getCareer()).append("'");
		if (customer.getPhone() != null && customer.getPhone().length() > 0)
			sb.append("and PHONE='").append(customer.getPhone()).append("'");		
		if (customer.getCustpwd() != null && customer.getCustpwd() .length() > 0)
			sb.append("and CUSTPWD='").append(customer.getCustpwd() ).append("'");
		if (customer.getCustname() != null && customer.getCustname().length() > 0)
			sb.append("and CUSTNAME='").append(customer.getCustname()).append("'");		
		if (customer.getSex() != null && customer.getSex().equals("true")){
			sb.append("and SEX=").append(1);	
		}
		if(customer.getSex() != null && customer.getSex().equals("false")){
			sb.append("and SEX=").append(0);
		}
		return sb.toString();

	}
	public String genSQLCheckTable(Check check) {
		StringBuffer sb = new StringBuffer("select * from checktable where 1=1 ");
		if (check.getCheckid() != 0 )
			sb.append(" and CHECKID=").append(check.getCheckid());
		
		if (check.getRentid() != null && check.getRentid().length() > 0)
			sb.append(" and TABLEID='").append(check.getRentid()).append("'");
		
		if (check.getCheckuserid() != null && check.getCheckuserid().length() > 0)
			sb.append(" and USERNAME='").append(check.getCheckuserid()).append("'");
		return sb.toString();	

	}
	public String genSQLRentTable(RentTable renttable) {
		StringBuffer sb = new StringBuffer("select * from renttable r,customers c where r.identity = c.identity and RENTFLAG='"+renttable.getRentflag()+"'");
		if (renttable.getTableid() != null && renttable.getTableid().length() > 0)
			sb.append(" and TABLEID='").append(renttable.getTableid()).append("'");
		if (renttable.getCarid() != null && renttable.getCarid().length() > 0)
			sb.append("and CARID='").append(renttable.getCarid()).append("'");
		if (renttable.getCustid() != null && renttable.getCustid() .length() > 0)
			sb.append("and CUSTID='").append(renttable.getCustid()).append("'");		
		if (renttable.getShouldreturn() != null )
			sb.append("and SHOULDRETURNDATE='").append(new java.sql.Date(renttable.getShouldreturn().getTime()) ).append("'");
		if (renttable.getBegindate() != null )
			sb.append("and BEGINDATE='").append(new java.sql.Date(renttable.getBegindate().getTime())).append("'");		
		return sb.toString();

	}
	public String genSQLCar(Car car) {
		StringBuffer sb = new StringBuffer("select * from cars where 1=1 ");
		if (car.getCarlor() != null && car.getCarlor().length() > 0)
			sb.append(" and COLOR='").append(car.getCarlor()).append("'");
		if (car.getCartype() != null && car.getCartype().length() > 0)
			sb.append(" and CARTYPE='").append(car.getCartype()).append("'");
		if (car.getDeposit() != 0 ){
			sb.append(" and DEPOSIT=").append(car.getDeposit()); 
			}	
		if (car.getDescription() != null && car.getDescription() .length() > 0)
			sb.append(" and DESCRIPTION='").append(car.getDescription() ).append("'");
		if (car.getIsrenting() != null && car.getIsrenting().length() > 0)
			sb.append(" and ISRENTING='").append(car.getIsrenting()).append("'");
		if (car.getCarid() != null && car.getCarid().length() > 0)
			sb.append(" and CARID='").append(car.getCarid()).append("'");
		if (car.getPrice() != 0){
			sb.append(" and PRICE=").append(car.getPrice());	
		}	
		if(car.getRentprice() != 0 ){
			sb.append(" and RENTPRICE=").append(car.getRentprice() );
		}	
		return sb.toString();

	}
	public Page browseRentTable(int pageNo,int pageSize,RentTable renttable) throws Exception{
		if(pageNo<1){
			pageNo = 1;
		}
		List<Object> carlist = new ArrayList<Object>();
		int recordSize = 0;
		int startIndex = (pageNo-1) * pageSize;
		int endIndex = startIndex + pageSize;
		Page page = Page.EMPTY_PAGE;
		Connection connection = null;
		connection=JdbcUtil.getInstance().getConn();
		String sql = genSQLRentTable(renttable);		
		PreparedStatement preparedStatement = connection.prepareStatement(
				sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
		
		ResultSet resultSet = preparedStatement.executeQuery();
		
		if(resultSet.last()){
			/**
			 * 得到最后记录的行号, 也就是记录总数
			 */
			recordSize = resultSet.getRow();
			/**
			 * 容错处理, 如果申请分页第一个记录超过记录总数, 使用上一个分页
			 */
			if(startIndex >= recordSize){
				int lastPageNo = (int) Math.ceil((double) recordSize/ pageSize);
				startIndex = (lastPageNo - 1)*pageSize;
				endIndex = startIndex + pageSize;
				pageNo = lastPageNo;
			}
			/**
			 * 定位游标到分页的第一条记录之前的记录
			 */
			if(startIndex == 0){
				resultSet.beforeFirst();
			}else{
				resultSet.absolute(startIndex);
			}
			
			while(resultSet.next() && resultSet.getRow() <= endIndex){
				
				RentTable rentable1 = new RentTable();
				
				rentable1.setTableid(resultSet.getString("TABLEID"));
				rentable1.setImprest(resultSet.getInt("IMPREST"));
				rentable1.setShouldpayprice(resultSet.getInt("SHOULDPAYPRICE"));
				rentable1.setPrice(resultSet.getInt("PRICE"));
				rentable1.setBegindate((java.sql.Date)resultSet.getDate("BEGINDATE"));
				rentable1.setShouldreturn((java.sql.Date)resultSet.getDate("SHOULDRETURNDATE"));
				rentable1.setReturndate((java.sql.Date)resultSet.getDate("RETURNDATE"));
				rentable1.setRentflag(resultSet.getString("RENTFLAG"));
				rentable1.setCarid(resultSet.getString("CARID"));
				Customer cust= new Customer();
				cust.setCustname(resultSet.getString("custname"));
				rentable1.setCustomer(cust);
				rentable1.setCustid(resultSet.getString("CUSTID"));
				renttable.setUserid(resultSet.getInt("USERID"));
				carlist.add(rentable1);
			}
			page = new Page(carlist, pageNo, pageSize, recordSize);
		}
		resultSet.close();
		preparedStatement.close();
		connection.close();
		return page;
	}
	public Page browseUserInfo(int pageNo,int pageSize,UserInfo userinfo) throws Exception{
		if(pageNo<1){
			pageNo = 1;
		}
		List<Object> userinfolist = new ArrayList<Object>();
		int recordSize = 0;
		int startIndex = (pageNo-1) * pageSize;
		int endIndex = startIndex + pageSize;
		Page page = Page.EMPTY_PAGE;
		Connection connection = null;
		connection=JdbcUtil.getInstance().getConn();
		String sql = genSQLUerinfo(userinfo);
		PreparedStatement preparedStatement = connection.prepareStatement(
				sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
		
		ResultSet resultSet = preparedStatement.executeQuery();
		
		if(resultSet.last()){
			/**
			 * 得到最后记录的行号, 也就是记录总数
			 */
			recordSize = resultSet.getRow();
			/**
			 * 容错处理, 如果申请分页第一个记录超过记录总数, 使用上一个分页
			 */
			if(startIndex >= recordSize){
				int lastPageNo = (int) Math.ceil((double) recordSize/ pageSize);