housedao.java

房屋出租,jsp房屋出租系统,可以注册添加出租/修改/删除信息,用户可搜索房源

package com.svse.dao;

import java.sql.Date;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.svse.model.House;
import com.svse.util.DBHelper;

public class HouseDAO extends BaseDao{
	private List<House> list;
	
	/*********************
	 * 
	 * @author Denis Zhou
	 * @remark 获得所有信息
	 * @return
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public List<House> getAll() throws ClassNotFoundException, SQLException
	{
		list = new ArrayList<House>();
		String sql = "select * from t_house order by h_id desc";
		try {
			conn = DBHelper.getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next())
			{
				House h = new House();
				h.setH_id(rs.getInt("h_id"));
				h.setP_id(rs.getInt("p_id"));
				h.setH_title(rs.getString("h_title"));
				h.setH_qx(rs.getString("h_qx"));
				h.setH_jd(rs.getString("h_jd"));
				h.setH_shi(rs.getInt("h_shi"));
				h.setH_ting(rs.getInt("h_ting"));
				h.setH_type(rs.getString("h_type"));
				h.setH_price(rs.getInt("h_price"));
				h.setH_per(rs.getString("h_per"));
				h.setH_tel(rs.getString("h_tel"));
				h.setH_date(rs.getString("h_date"));
				h.setH_remark(rs.getString("h_remark"));
				list.add(h);
			}
		} catch (ClassNotFoundException e) {
			throw e;
		} catch (SQLException e) {
			throw e;
		}finally
		{
			closeResourse(ps, rs);
			DBHelper.closeConn(conn);
		}
		return list;
	}
	
	/*********************
	 * 
	 * @author Denis Zhou
	 * @remark 高级查询
	 * @param house
	 * @return
	 * @throws ClassNotFoundException 
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 * @throws SQLException 
	 */
	public List<House> select(String strTitle,String strQx,String strJd,String minPrice,String maxPrice,String strShi,String strTing,String[] strTypes,String strDate) throws ClassNotFoundException, SQLException
	{
		list = new ArrayList<House>();
		String strType = "";
		if(strTypes.length == 0)
		{
			strType = "";
		}else{
			strType = "'"+strTypes[0]+"'";
			for (int i = 1; i < strTypes.length; i++) {
				strType += ",'"+strTypes[i]+"'";
			}
		}
		
		String sql = "select * from t_house where 1=1";
		if(!"%%".equals(strTitle))
		{
			sql = sql+ " and h_title like '"+strTitle+"'";
		}
		if(!"%不限%".equals(strQx))
		{
			sql = sql+ " and h_qx like '"+strQx+"'";
		}
		if(!"%不限%".equals(strJd))
		{
			sql = sql+ " and h_jd like '"+strJd+"'";
		}
		if(!"".equals(minPrice) && !"".equals(maxPrice))
		{
			sql = sql+ " and h_price between "+Integer.parseInt(minPrice)+" and "+Integer.parseInt(maxPrice);
		}
		if(!"0".equals(strShi))
		{
			sql = sql+ " and h_shi = "+Integer.parseInt(strShi);
		}
		if(!"0".equals(strTing))
		{
			sql = sql+ " and h_ting = "+Integer.parseInt(strTing);
		}
		if(!"".equals(strType))
		{
			sql = sql+ " and h_type in ('"+strType+"')";
		}
		if(!"0".equals(strDate))
		{
			sql = sql+ " and datediff(d,h_date,getdate()) <= " + Integer.parseInt(strDate);
		}
		
		//System.out.println(sql);
		
		try {
			conn = DBHelper.getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next())
			{
				House h = new House();
				h.setH_id(rs.getInt("h_id"));
				h.setP_id(rs.getInt("p_id"));
				h.setH_title(rs.getString("h_title"));
				h.setH_qx(rs.getString("h_qx"));
				h.setH_jd(rs.getString("h_jd"));
				h.setH_shi(rs.getInt("h_shi"));
				h.setH_ting(rs.getInt("h_ting"));
				h.setH_type(rs.getString("h_type"));
				h.setH_price(rs.getInt("h_price"));
				h.setH_per(rs.getString("h_per"));
				h.setH_tel(rs.getString("h_tel"));
				h.setH_date(rs.getString("h_date"));
				h.setH_remark(rs.getString("h_remark"));
				list.add(h);
			}
		} catch (ClassNotFoundException e) {
			throw e;
		} catch (SQLException e) {
			throw e;
		}finally
		{
			closeResourse(ps, rs);
			DBHelper.closeConn(conn);
		}
		return list;
	}
	
	
	/*********************
	 * 
	 * @author Denis Zhou
	 * @remark 通过p_id获得信息
	 * @param house
	 * @return
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public List<House> get_perid(House house) throws ClassNotFoundException, SQLException
	{
		list = new ArrayList<House>();
		String sql = "select * from t_house where p_id=? order by h_id desc";
		try {
			conn = DBHelper.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, house.getP_id());
			rs = ps.executeQuery();
			while(rs.next())
			{
				House h = new House();
				h.setH_id(rs.getInt("h_id"));
				h.setP_id(rs.getInt("p_id"));
				h.setH_title(rs.getString("h_title"));
				h.setH_qx(rs.getString("h_qx"));
				h.setH_jd(rs.getString("h_jd"));
				h.setH_shi(rs.getInt("h_shi"));
				h.setH_ting(rs.getInt("h_ting"));
				h.setH_type(rs.getString("h_type"));
				h.setH_price(rs.getInt("h_price"));
				h.setH_per(rs.getString("h_per"));
				h.setH_tel(rs.getString("h_tel"));
				h.setH_date(rs.getString("h_date"));
				h.setH_remark(rs.getString("h_remark"));
				list.add(h);
			}
		} catch (ClassNotFoundException e) {
			throw e;
		} catch (SQLException e) {
			throw e;
		}finally
		{
			closeResourse(ps, rs);
			DBHelper.closeConn(conn);
		}
		return list;
	}
	
	/**********************
	 * 
	 * @author Denis Zhou
	 * @remark 通过h_id获得信息
	 * @param house
	 * @return
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public House get_hid(House house) throws ClassNotFoundException, SQLException
	{
		House h = null;
		String sql = "select * from t_house where h_id=?";
		try {
			conn = DBHelper.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, house.getH_id());
			rs = ps.executeQuery();
			while(rs.next())
			{
				h = new House();
				h.setH_id(rs.getInt("h_id"));
				h.setP_id(rs.getInt("p_id"));
				h.setH_title(rs.getString("h_title"));
				h.setH_qx(rs.getString("h_qx"));
				h.setH_jd(rs.getString("h_jd"));
				h.setH_shi(rs.getInt("h_shi"));
				h.setH_ting(rs.getInt("h_ting"));
				h.setH_type(rs.getString("h_type"));
				h.setH_price(rs.getInt("h_price"));
				h.setH_per(rs.getString("h_per"));
				h.setH_tel(rs.getString("h_tel"));
				h.setH_date(rs.getString("h_date"));
				h.setH_remark(rs.getString("h_remark"));
			}
		} catch (ClassNotFoundException e) {
			throw e;
		} catch (SQLException e) {
			throw e;
		}finally
		{
			closeResourse(ps, rs);
			DBHelper.closeConn(conn);
		}
		return h;
	}
	
	/*********************
	 * 
	 * @author Denis Zhou
	 * @remark 添加信息
	 * @param house
	 * @return
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public boolean add(House house) throws ClassNotFoundException, SQLException
	{
		boolean isFlat = false;
		String sql = "insert into t_house values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try{
			count = getCount();
			conn = DBHelper.getConn();	
			ps = conn.prepareStatement(sql);
			ps.setInt(1, count+1);
			ps.setInt(2, house.getP_id());
			ps.setString(3, house.getH_title());
			ps.setString(4, house.getH_qx());
			ps.setString(5, house.getH_jd());
			ps.setInt(6, house.getH_shi());
			ps.setInt(7, house.getH_ting());
			ps.setString(8, house.getH_type());
			ps.setInt(9, house.getH_price());
			ps.setString(10, house.getH_per());
			ps.setString(11, house.getH_tel());
			ps.setString(12, house.getH_date());
			ps.setString(13, house.getH_remark());
			int i = ps.executeUpdate();
			if(i >0)
			{
				isFlat = true;
			}
		} catch (ClassNotFoundException e) {
			throw e;
		} catch (SQLException e) {
			throw e;
		}finally
		{
			closeResourse(ps, rs);
			DBHelper.closeConn(conn);
		}
		return isFlat;
	}
	
	/*********************
	 * 
	 * @author Denis Zhou
	 * @remark 删除信息
	 * @param house
	 * @return
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public boolean del(House house) throws ClassNotFoundException, SQLException
	{
		boolean isFlat = false;
		String sql = "delete from t_house where h_id=?";
		try{
			conn = DBHelper.getConn();	
			ps = conn.prepareStatement(sql);
			ps.setInt(1, house.getH_id());
			int i = ps.executeUpdate();
			if(i >0)
			{
				isFlat = true;
			}
		} catch (ClassNotFoundException e) {
			throw e;
		} catch (SQLException e) {
			throw e;
		}finally
		{
			closeResourse(ps, rs);
			DBHelper.closeConn(conn);
		}
		return isFlat;
	}
	
	/*******************
	 * 
	 * @author Denis Zhou
	 * @remark 修改信息
	 * @param house
	 * @return
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public boolean upd(House house) throws ClassNotFoundException, SQLException
	{
		boolean isFlat = false;
		String sql = "update t_house set h_title=?,h_qx=?,h_jd=?,h_shi=?,h_ting=?,h_type=?,h_price=?,h_per=?,h_tel=?,h_date=?,h_remark=? where h_id=?";
		try{
			conn = DBHelper.getConn();	
			ps = conn.prepareStatement(sql);
			ps.setString(1, house.getH_title());
			ps.setString(2, house.getH_qx());
			ps.setString(3, house.getH_jd());
			ps.setInt(4, house.getH_shi());
			ps.setInt(5, house.getH_ting());
			ps.setString(6, house.getH_type());
			ps.setInt(7, house.getH_price());
			ps.setString(8, house.getH_per());
			ps.setString(9, house.getH_tel());
			ps.setString(10, house.getH_date());
			ps.setString(11, house.getH_remark());
			ps.setInt(12, house.getH_id());
			int i = ps.executeUpdate();
			if(i >0)
			{
				isFlat = true;
			}
		} catch (ClassNotFoundException e) {
			throw e;
		} catch (SQLException e) {
			throw e;
		}finally
		{
			closeResourse(ps, rs);
			DBHelper.closeConn(conn);
		}
		return isFlat;
	}
	
	/*******************
	 * @author Denis Zhou
	 * @remark 返回表中最大的h_id号
	 * @return
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public int getCount() throws ClassNotFoundException, SQLException
	{
		try {
			list = getAll();
			count = list.get(0).getH_id();
		} catch (ClassNotFoundException e) {
			throw e;
		} catch (SQLException e) {
			throw e;
		}finally
		{
			closeResourse(ps, rs);
			DBHelper.closeConn(conn);
		}
		return count;
	}
}