package Dao;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//import java.sql.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Pattern;
import Entities.ImportMessage;
import Utils.ExcelUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import Entities.HouseEntity;
import Entities.PageEntity;
import Utils.DBUtil;
import Utils.UUIDUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
/**
* @time: 2018年02月21日
* @author: wentao
* @copyright: Wuxi Yazuo ,Ltd.copyright 2015-2025
*/
public class HouseSecurityDao implements Serializable {
Logger log= Logger.getLogger("houseCaoZuo-log");
/**
* 小区名称正则
*/
private static final String CODE_HOUSE_NAME = "^[\\u4e00-\\u9fa5]{2,33}$";
/**
* 栋号正则
*/
private static final String CODE_BUILDNUMBER = "^[0-9]*\\d{1,11}$";
/**
* 房号正则
*/
private static final String CODE_ROOMNUMBER = "^[0-9]*\\d{1,11}$";
/**
* 户型正则
*/
private static final String CODE_HOUSETYPE = "^[a-zA-Z0-9_\\u4e00-\\u9fa5]+$";
/**
* 面积正则
*/
private static final String CODE_AREA = "^[0-9]+(.[0-9]{1,3})?$";
/**
* 价格正则
*/
private static final String CODE_PRICE = "^[0-9]+(.[0-9]{1,3})?$";
/**
* 建成年限正则
*/
private static final String CODE_COMPLETEYEAR = "([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8])))";
/**
* 手机号正则
*/
private static final String CODE_PHONE = "^1[34578]\\d{9}$";
/**
* 备注正则
*/
private static final String CODE_REMARKS = "^[\\u4e00-\\u9fa5]{2,33}$";
/**去重查询小区名称，用于筛选
* @author wentao
* @return
*/
public List<String> queryHouseName(){
List<String> house_names= new ArrayList<String>();
Connection conn=null;
try {
conn=DBUtil.getConnection();
conn.setAutoCommit(false);
String sql="SELECT DISTINCT HOUSE_NAME FROM HOUSE_SECURITY WHERE DELETE_FLAG='0' ORDER BY HOUSE_ID";
PreparedStatement smt=conn.prepareStatement(sql);
ResultSet rs=smt.executeQuery();
conn.commit();
while(rs.next()) {
house_names.add(rs.getString("house_name"));
}
return house_names;
} catch (SQLException e) {
log.error("去重查询私有房源小区名称信息失败",e);
DBUtil.rollBack(conn);
e.printStackTrace();
return house_names;
//throw new RuntimeException("分页查询房源信息失败",e);
}finally {
DBUtil.closeConnection(conn);
}
}
/**去重查询房源栋号信息，用于筛选
* @author wentao
* @return
*/
public List<String> queryBuildNumber(){
List<String> buildNumbers= new ArrayList<String>();
Connection conn=null;
try {
conn=DBUtil.getConnection();
conn.setAutoCommit(false);
String sql="SELECT DISTINCT BUILD_NUMBER FROM HOUSE_SECURITY WHERE DELETE_FLAG='0' ORDER BY HOUSE_ID";
PreparedStatement smt=conn.prepareStatement(sql);
ResultSet rs=smt.executeQuery();
conn.commit();
while(rs.next()) {
buildNumbers.add(rs.getString("build_number"));
}
return buildNumbers;
} catch (SQLException e) {
log.error("去重查询私有房源栋号信息失败",e);
DBUtil.rollBack(conn);
e.printStackTrace();
return buildNumbers;
//throw new RuntimeException("分页查询房源信息失败",e);
}finally {
DBUtil.closeConnection(conn);
}
}
/**去重查询房源户型信息，用于筛选
* @author wentao
* @return
*/
public List<String> queryHouseType(){
List<String> houseTypes= new ArrayList<String>();
Connection conn=null;
try {
conn=DBUtil.getConnection();
conn.setAutoCommit(false);
String sql="SELECT DISTINCT HOUSE_TYPE FROM HOUSE_SECURITY WHERE DELETE_FLAG='0' ORDER BY HOUSE_ID";
PreparedStatement smt=conn.prepareStatement(sql);
ResultSet rs=smt.executeQuery();
conn.commit();
while(rs.next()) {
houseTypes.add(rs.getString("house_type"));
}
return houseTypes;
} catch (SQLException e) {
log.error("去重查询私有房源户型信息失败",e);
DBUtil.rollBack(conn);
e.printStackTrace();
return houseTypes;
//throw new RuntimeException("分页查询房源信息失败",e);
}finally {
DBUtil.closeConnection(conn);
}
}
/**去重查询房源装修程度信息，用于筛选
* @author wentao
* @return
*/
public List<String> queryRoomStatus(){
List<String> roomStatus= new ArrayList<String>();
Connection conn=null;
try {
conn=DBUtil.getConnection();
conn.setAutoCommit(false);
String sql="SELECT DISTINCT ROOM_STATUS FROM HOUSE_SECURITY WHERE DELETE_FLAG='0' ORDER BY HOUSE_ID";
PreparedStatement smt=conn.prepareStatement(sql);
ResultSet rs=smt.executeQuery();
conn.commit();
while(rs.next()) {
roomStatus.add(rs.getString("room_status"));
}
return roomStatus;
} catch (SQLException e) {
log.error("去重查询私有房源装修程度信息失败",e);
DBUtil.rollBack(conn);
e.printStackTrace();
return roomStatus;
//throw new RuntimeException("分页查询房源信息失败",e);
}finally {
DBUtil.closeConnection(conn);
}
}
/**分页查询房源信息
* @author wentao
* @param currentPage
* @param pageSize
* @return
*/
public List<HouseEntity> queryCurrentData(Integer currentPage,Integer pageSize,String queryCurrentDataSql){
//计算查询的起始行
int startNo = (currentPage-1)*pageSize;
List<HouseEntity> houses= new ArrayList<HouseEntity>();
Connection conn=null;
try {
conn=DBUtil.getConnection();
conn.setAutoCommit(false);
PreparedStatement smt=conn.prepareStatement(queryCurrentDataSql);
smt.setInt(1,startNo);
smt.setInt(2, pageSize);
ResultSet rs=smt.executeQuery();
conn.commit();
while(rs.next()) {
HouseEntity house=new HouseEntity();
house.setHouseID(rs.getObject("house_id"));
house.setHouseName(rs.getString("house_name"));
house.setBuildNumber(rs.getString("build_number"));
house.setRoomNumber(rs.getString("room_number"));
house.setHouseType(rs.getString("house_type"));
house.setArea(rs.getString("area"));
house.setPrice(rs.getFloat("price"));
house.setRoomStatus(rs.getInt("room_status"));
house.setCompleteYear(rs.getDate("complete_year"));
house.setPhone(rs.getString("phone"));
house.setOrderName(rs.getString("order_name"));
house.setRemarks(rs.getString("remarks"));
houses.add(house);
}
return houses;
} catch (SQLException e) {
log.error("分页查询私有房源信息失败",e);
DBUtil.rollBack(conn);
e.printStackTrace();
return houses;
//throw new RuntimeException("分页查询房源信息失败",e);
}finally {
DBUtil.closeConnection(conn);
}
}
/**提供查询总记录数的方法
* @author wentao
* @return
*/
public Integer queryTotalCount(String queryTotalCountSql){
Integer count=0;
Connection conn=null;
try {
conn=DBUtil.getConnection();
conn.setAutoCommit(false);
PreparedStatement smt=conn.prepareStatement(queryTotalCountSql);
ResultSet rs=smt.executeQuery();
conn.commit();
while(rs.next()) {
count=rs.getInt(1);
}
//System.out.println(count);
return count;
} catch (SQLException e) {
log.error("查询私有房源全部信息失败",e);
DBUtil.rollBack(conn);
e.printStackTrace();
return count;
//throw new RuntimeException("查询房源全部信息失败",e);
}finally {
DBUtil.closeConnection(conn);
}
}
/**向页面返回查询的结果实体
* @author wentao
* @param
* @throws
* @param currentPage
* @param pageSize
* @return
*/
public PageEntity queryPageEntity(Integer currentPage,Integer pageSize,String queryTotalCountSql,String queryCurrentDataSql){
//封装PageBean分页对象数据
PageEntity pageEntity = new PageEntity();
//设置当前页
pageEntity.setCurrentPage(currentPage);
//设置每页显示的记录数
pageEntity.setPageSize(pageSize);
HouseDao houseDao = new HouseDao();
/**
* 从数据库中查询出总记录数
*/
int totalCount = houseDao.queryTotalCount(queryTotalCountSql);
//设置总记录数
pageEntity.setTotalCount(totalCount);
//设置当前页的数据
/**
* 从数据库中查询出当前页的房源数据
*/
List<HouseEntity> list = houseDao.queryCurrentData(pageEntity.getCurrentPage(), pageEntity.getPageSize(),queryCurrentDataSql);
pageEntity.setData(list);
return pageEntity;
}
/**根据houseid查询房源信息
* @author wentao
* @param house_id
* @return
*/
public HouseEntity queryHouseByID(String house_id){
HouseEntity house=new HouseEntity();
Connection conn=null;
try {
conn=DBUtil.getConnection();
conn.setAutoCommit(false);
//delete_flag:删除标记 0：未删除 1：已删除
String sql="SELECT * FROM HOUSE_SECURITY WHERE HOUSE_ID=?";
PreparedStatement smt=conn.prepareStatement(sql);
smt.setString(1,house_id);
ResultSet rs=smt.executeQuery();
conn.commit();
while(rs.next()) {
house.setHouseID(rs.getObject("house_id"));
house.setHouseName(rs.getString("house_name"));
house.setBuildNumber(rs.getString("build_number"));
house.setRoomNumber(rs.getString("room_number"));
house.setHouseType(rs.getString("house_type"));
house.setArea(rs.getString("area"));
house.setPrice(rs.getFloat("price"));
house.setRoomStatus(rs.getInt("room_status"));
house.setCompleteYear(rs.getDate("complete_year"));
house.setPhone(rs.getString("phone"));
house.setOrderName(rs.getString("order_name"));
house.setRemarks(rs.getString("remarks"));
}
return house;
} catch (SQLException e) {
log.error("根据UUID查询私有房源信息失败",e);
DBUtil.rollBack(conn);
e.printStackTrace();
return house;
//throw new RuntimeException("分页查询房源信息失败",e);
}finally {
DBUtil.closeConnection(conn);
}
}
/**
*查询房源信息是否存在
*
*
* @param houseName
* @param buildNumber
* @param roomNumber
* @return
* @author wentao
* @time 2018/2/20
*/
public List<HouseEntity> checkHouse(String houseName,String buildNumber,String roomNumber){
List<HouseEntity> houseList=new ArrayList<HouseEntity>();
//List<List<HouseEntity>> resultList=new ArrayList<List<HouseEntity>>();
Connection conn=null;
try {
conn=DBUtil.getConnection();
conn.setAutoCommit(false);
//delete_flag:删除标记 0：未删除 1：已删除
String sql="SELECT * FROM HOUSE_SECURITY WHERE HOUSE_NAME=? AND BUILD_NUMBER=? AND ROOM_NUMBER=? AND " +
"DELETE_FLAG=0";
PreparedStatement smt=conn.prepareStatement(sql);
smt.setString(1,houseName);
smt.setString(2,buildNumber);
smt.setString(3,roomNumber);
ResultSet rs=smt.executeQuery();
conn.commit();
while(rs.next()) {
HouseEntity house=new HouseEntity();
house.setHouseID(rs.getObject("house_id"));
house.setHouseName(rs.getString("house_name"));
house.setBuildNumber(rs.getString("build_number"));
house.setRoomNumber(rs.getString("room_number"));
houseList.add(house);
}
return houseList;
} catch (SQLException e) {
log.error("查询私有房源信息是否存在失败",e);
DBUtil.rollBack(conn);
e.printStackTrace();
HouseEntity house=new HouseEntity();
house.setHouseName("温涛");
houseList.add(house);
return houseList;
}finally {
DBUtil.closeConnection(conn);
}
}
/**删除房源信息
* @author wentao
* @param houseID
* @param userID
* @return
*/
public boolean delHouse(String houseID,String userID){
Date deletime=new Date();
SimpleDateFormat time = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss");
Connection conn=null;
try {
conn=DBUtil.getConnection();
conn.setAutoCommit(false);
String sql="update HOUSE_SECURITY set delete_flag='1',deleter_id=?,deletime=? where house_id=?";
PreparedStatement smt=conn.prepareStatement(sql);
smt.setString(1,userID);
smt.setString(2, time.format(deletime));
smt.setString(3, houseID);
int delResult=smt.executeUpdate();
conn.commit();
if(delResult==1){
return true;
}else{
return false;
}
} catch (SQLException e) {
log.error("删除私有房源信息失败",e);
DBUtil.rollBack(conn);
e.printStackTrace();
return false;
}finally {
DBUtil.closeConnection(conn);
}
}
/**编辑房源信息
* @author wentao
* @param
* @throws
* @param edit_house
* @param user_id
* @return
*/
public boolean editHouse(HouseEntity edit_house,String user_id) {
Date updatetime=new Date();
SimpleDateFormat time = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss");
String houseID=UUIDUtils.addSepc(edit_house.getHouseID());
Connection editHouseConn=null;
try {
editHouseConn=DBUtil.getConnection();
editHouseConn.setAutoCommit(false);
String editHouse="UPDATE HOUSE_SECURITY SET HOUSE_NAME=?,BUILD_NUMBER=?,ROOM_NUMBER=?,HOUSE_TYPE=?,AREA=?,PRICE=?,"
+ "ROOM_STATUS=?,COMPLETE_YEAR=?,PHONE=?,REMARKS=?,UPDATER_ID=?,UPDATE_TIME=?"
+ "WHERE HOUSE_ID=?";
PreparedStatement smt=editHouseConn.prepareStatement(editHouse);
smt.setString(1, edit_house.getHouseName());
smt.setString(2, edit_house.getBuildNumber());
smt.setString(3, edit_house.getRoomNumber());
smt.setString(4, edit_house.getHouseType());
smt.setString(5, edit_house.getArea());
smt.setDouble(6, edit_house.getPrice());
smt.setInt(7, edit_house.getRoomStatus());
smt.setDate(8, edit_house.getCompleteYear());
smt.setString(9, edit_house.getPhone());
smt.setString(10, edit_house.getRemarks());
smt.setString(11, user_id);
smt.setString(12, time.format(updatetime));
smt.setString(13, houseID);
int editResult=smt.executeUpdate();
editHouseConn.commit();
if(editResult==1){
return true;
}else{
return false;
}
} catch (SQLException e) {
DBUtil.rollBack(editHouseConn);
log.error("修改私有房源信息失败",e);
e.printStackTrace();
return false;
}finally{
DBUtil.closeConnection(editHouseConn);
}
}
/**添加房源信息实体方法
* @author wentao
* @param add_house
* @return
*/
public boolean addHouse(HouseEntity add_house,String userID) {
Connection addHouseConn=null;
try {
addHouseConn=DBUtil.getConnection();
addHouseConn.setAutoCommit(false);
String addHouse="INSERT INTO HOUSE_SECURITY (HOUSE_ID,HOUSE_NAME,BUILD_NUMBER,ROOM_NUMBER,HOUSE_TYPE,AREA,PRICE,"
+ "ROOM_STATUS,COMPLETE_YEAR,PHONE,REMARKS,ORDER_NAME,CREATER_ID) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement smt=addHouseConn.prepareStatement(addHouse,new String[]{"house_id"});
smt.setString(1, UUIDUtils.addSepc(add_house.getHouseID()));
//System.out.println(add_house.getHouseID());
smt.setString(2, add_house.getHouseName());
smt.setString(3, add_house.getBuildNumber());
smt.setString(4, add_house.getRoomNumber());
smt.setString(5, add_house.getHouseType());
smt.setString(6, add_house.getArea());
smt.setFloat(7, add_house.getPrice());
smt.setInt(8, add_house.getRoomStatus());
smt.setDate(9, add_house.getCompleteYear());
smt.setString(10, add_house.getPhone());
smt.setString(11, add_house.getRemarks());
smt.setString(12, add_house.getOrderName());
smt.setString(13,userID);
smt.executeUpdate();
addHouseConn.commit();
//从smt中获取生成的主键
//结果集中包含1行1列
ResultSet rs = smt.getGeneratedKeys();
if(rs.next()){
//这种场景下的结果集,只能通过字段的序号获取值.
String addSuccessID = rs.getString(1);
log.info("新增的私有房源主键id是"+addSuccessID);
return true;
}else{
return false;
}
} catch (SQLException e) {
DBUtil.rollBack(addHouseConn);
log.error("新增私有房源信息失败",e);
e.printStackTrace();
return false;
}finally{
DBUtil.closeConnection(addHouseConn);
}
}
}
最近下载更多
最近浏览