spring mvc+jsp+jdbc开发java web房源网站后台管理系统的代码详情-代码-最代码

首页>代码>spring mvc+jsp+jdbc开发java web房源网站后台管理系统>/fangchan/src/main/java/Dao/HouseDao.java
001package Dao;
002 
003import java.io.Serializable;
004import java.sql.Connection;
005import java.sql.PreparedStatement;
006import java.sql.ResultSet;
007import java.sql.SQLException;
008//import java.sql.Date;
009import java.text.DateFormat;
010import java.text.ParseException;
011import java.text.SimpleDateFormat;
012import java.util.ArrayList;
013import java.util.Date;
014import java.util.List;
015import java.util.regex.Pattern;
016 
017import Entities.ImportMessage;
018import Utils.ExcelUtil;
019import org.apache.commons.lang3.StringUtils;
020import org.apache.log4j.Logger;
021 
022import Entities.HouseEntity;
023import Entities.PageEntity;
024import Utils.DBUtil;
025import Utils.UUIDUtils;
026import org.apache.poi.hssf.usermodel.HSSFDateUtil;
027import org.apache.poi.ss.usermodel.Cell;
028import org.apache.poi.ss.usermodel.Row;
029import org.apache.poi.ss.usermodel.Sheet;
030import org.apache.poi.ss.usermodel.Workbook;
031import org.springframework.web.multipart.MultipartFile;
032 
033//import org.springframework.web.multipart.MultipartFile;
034 
035/**对房源信息进行操作的方法类
036 * 
037 * @author wentao
038 */
039public class HouseDao implements Serializable {
040    Logger log= Logger.getLogger("houseCaoZuo-log");
041    /**
042     * 小区名称正则
043     */
044    private static final String CODE_HOUSE_NAME = "^[\\u4e00-\\u9fa5]{2,33}$";
045    /**
046     * 栋号正则
047     */
048    private static final String CODE_BUILDNUMBER = "^.{0,10}$";
049    /**
050     * 房号正则
051     */
052    private static final String CODE_ROOMNUMBER = "^.{0,10}$";
053    /**
054     * 户型正则
055     */
056    private static final String CODE_HOUSETYPE = "^[a-zA-Z0-9_\\u4e00-\\u9fa5]+$";
057    /**
058     * 面积正则
059     */
060    private static final String CODE_AREA = "^.{0,20}$";
061    /**
062     * 价格正则
063     */
064    private static final String CODE_PRICE = "^[0-9]+(.[0-9]{1,3})?$";
065    /**
066     * 建成年限正则
067     */
068    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])))";
069    /**
070     * 手机号正则
071     */
072    private static final String CODE_PHONE = "^.{0,50}$";
073    /**
074     * 备注正则
075     */
076    private static final String CODE_REMARKS = "^.{0,50}$";
077 
078 
079     
080     
081    /**去重查询小区名称，用于筛选
082     * @author wentao
083     * @return
084     */
085    public List<String> queryHouseName(){
086         List<String> house_names= new ArrayList<String>();
087            Connection conn=null;
088            try {
089                 conn=DBUtil.getConnection();
090                 conn.setAutoCommit(false);
091                String sql="SELECT DISTINCT HOUSE_NAME FROM HOUSE WHERE DELETE_FLAG='0' ORDER BY HOUSE_ID";
092                PreparedStatement smt=conn.prepareStatement(sql);
093                ResultSet rs=smt.executeQuery();
094                conn.commit();
095                while(rs.next()) {      
096                    house_names.add(rs.getString("house_name"));
097                }
098                return house_names;
099            } catch (SQLException e) {
100                log.error("去重查询房源小区名称信息失败",e);
101                DBUtil.rollBack(conn);
102                e.printStackTrace();
103                return house_names;
104                //throw new RuntimeException("分页查询房源信息失败",e);
105            }finally {
106                DBUtil.closeConnection(conn);
107            }
108    }
109     
110    /**去重查询房源栋号信息，用于筛选
111     * @author wentao
112     * @return
113     */
114    public List<String> queryBuildNumber(){
115         List<String> buildNumbers= new ArrayList<String>();
116            Connection conn=null;
117            try {
118                 conn=DBUtil.getConnection();
119                 conn.setAutoCommit(false);
120                String sql="SELECT DISTINCT BUILD_NUMBER FROM HOUSE WHERE DELETE_FLAG='0' ORDER BY HOUSE_ID";
121                PreparedStatement smt=conn.prepareStatement(sql);
122                ResultSet rs=smt.executeQuery();
123                conn.commit();
124                while(rs.next()) {      
125                    buildNumbers.add(rs.getString("build_number"));
126                }
127                return buildNumbers;
128            } catch (SQLException e) {
129                log.error("去重查询房源栋号信息失败",e);
130                DBUtil.rollBack(conn);
131                e.printStackTrace();
132                return buildNumbers;
133                //throw new RuntimeException("分页查询房源信息失败",e);
134            }finally {
135                DBUtil.closeConnection(conn);
136            }
137    }
138     
139    /**去重查询房源户型信息，用于筛选
140     * @author wentao
141     * @return
142     */
143    public List<String> queryHouseType(){
144         List<String> houseTypes= new ArrayList<String>();
145            Connection conn=null;
146            try {
147                 conn=DBUtil.getConnection();
148                 conn.setAutoCommit(false);
149                String sql="SELECT DISTINCT HOUSE_TYPE FROM HOUSE WHERE DELETE_FLAG='0' ORDER BY HOUSE_ID";
150                PreparedStatement smt=conn.prepareStatement(sql);
151                ResultSet rs=smt.executeQuery();
152                conn.commit();
153                while(rs.next()) {      
154                    houseTypes.add(rs.getString("house_type"));
155                }
156                return houseTypes;
157            } catch (SQLException e) {
158                log.error("去重查询房源户型信息失败",e);
159                DBUtil.rollBack(conn);
160                e.printStackTrace();
161                return houseTypes;
162                //throw new RuntimeException("分页查询房源信息失败",e);
163            }finally {
164                DBUtil.closeConnection(conn);
165            }
166    }
167     
168    /**去重查询房源装修程度信息，用于筛选
169     * @author wentao
170     * @return
171     */
172    public List<String> queryRoomStatus(){
173         List<String> roomStatus= new ArrayList<String>();
174            Connection conn=null;
175            try {
176                 conn=DBUtil.getConnection();
177                 conn.setAutoCommit(false);
178                String sql="SELECT DISTINCT ROOM_STATUS FROM HOUSE WHERE DELETE_FLAG='0' ORDER BY HOUSE_ID";
179                PreparedStatement smt=conn.prepareStatement(sql);
180                ResultSet rs=smt.executeQuery();
181                conn.commit();
182                while(rs.next()) {      
183                    roomStatus.add(rs.getString("room_status"));
184                }
185                return roomStatus;
186            } catch (SQLException e) {
187                log.error("去重查询房源装修程度信息失败",e);
188                DBUtil.rollBack(conn);
189                e.printStackTrace();
190                return roomStatus;
191                //throw new RuntimeException("分页查询房源信息失败",e);
192            }finally {
193                DBUtil.closeConnection(conn);
194            }
195    }
196     
197     
198    /**分页查询房源信息
199     * @author wentao
200     * @param currentPage
201     * @param pageSize
202     * @return
203     */
204    public List<HouseEntity> queryCurrentData(Integer currentPage,Integer pageSize,String queryCurrentDataSql){
205        //计算查询的起始行  
206        int startNo = (currentPage-1)*pageSize; 
207        List<HouseEntity> houses= new ArrayList<HouseEntity>();
208        Connection conn=null;
209        try {
210             conn=DBUtil.getConnection();
211             conn.setAutoCommit(false);
212            PreparedStatement smt=conn.prepareStatement(queryCurrentDataSql);
213            smt.setInt(1,startNo);
214            smt.setInt(2, pageSize);
215            ResultSet rs=smt.executeQuery();
216            conn.commit();
217            while(rs.next()) {
218                HouseEntity house=new HouseEntity();
219                house.setHouseID(rs.getObject("house_id"));
220                house.setHouseName(rs.getString("house_name"));
221                house.setBuildNumber(rs.getString("build_number"));
222                house.setRoomNumber(rs.getString("room_number"));
223                house.setHouseType(rs.getString("house_type"));
224                house.setArea(rs.getString("area"));
225                house.setPrice(rs.getFloat("price"));
226                house.setRoomStatus(rs.getInt("room_status"));
227                house.setCompleteYear(rs.getDate("complete_year"));
228                house.setPhone(rs.getString("phone"));
229                house.setOrderName(rs.getString("order_name"));
230                house.setRemarks(rs.getString("remarks"));
231                houses.add(house);
232            }
233            return houses;
234        } catch (SQLException e) {
235            log.error("分页查询房源信息失败",e);
236            DBUtil.rollBack(conn);
237            e.printStackTrace();
238            return houses;
239            //throw new RuntimeException("分页查询房源信息失败",e);
240        }finally {
241            DBUtil.closeConnection(conn);
242        }
243    }
244     
245    /**提供查询总记录数的方法 
246     * @author wentao
247     * @return
248     */
249    public Integer queryTotalCount(String queryTotalCountSql){ 
250        Integer count=0;
251        Connection conn=null;
252        try {
253             conn=DBUtil.getConnection();
254             conn.setAutoCommit(false);
255            PreparedStatement smt=conn.prepareStatement(queryTotalCountSql);
256            ResultSet rs=smt.executeQuery();
257            conn.commit();
258            while(rs.next()) {
259                count=rs.getInt(1);
260            }
261            //System.out.println(count);
262            return count;
263        } catch (SQLException e) {
264            log.error("查询房源全部信息失败",e);
265            DBUtil.rollBack(conn);
266            e.printStackTrace();
267            return count;
268            //throw new RuntimeException("查询房源全部信息失败",e);
269        }finally {
270            DBUtil.closeConnection(conn);
271        }
272    }
273     
274     /**向页面返回查询的结果实体
275     * @author wentao
276     * @param 
277     * @throws 
278     * @param currentPage
279     * @param pageSize
280     * @return
281     */
282    public PageEntity queryPageEntity(Integer currentPage,Integer pageSize,String queryTotalCountSql,String queryCurrentDataSql){  
283            //封装PageBean分页对象数据  
284         PageEntity pageEntity = new PageEntity();     
285            //设置当前页  
286         pageEntity.setCurrentPage(currentPage);  
287            //设置每页显示的记录数  
288         pageEntity.setPageSize(pageSize);   
289            HouseDao houseDao = new HouseDao();  
290            /** 
291             * 从数据库中查询出总记录数 
292             */ 
293            int totalCount = houseDao.queryTotalCount(queryTotalCountSql);  
294            //设置总记录数  
295            pageEntity.setTotalCount(totalCount);      
296            //设置当前页的数据  
297            /** 
298             * 从数据库中查询出当前页的房源数据 
299             */ 
300            List<HouseEntity> list = houseDao.queryCurrentData(pageEntity.getCurrentPage(), pageEntity.getPageSize(),queryCurrentDataSql);  
301            pageEntity.setData(list);      
302            return pageEntity;  
303        } 
304     
305    /**根据houseid查询房源信息
306     * @author wentao
307     * @param house_id
308     * @return
309     */
310    public HouseEntity queryHouseByID(String house_id){
311        HouseEntity house=new HouseEntity();
312        Connection conn=null;
313        try {
314             conn=DBUtil.getConnection();
315             conn.setAutoCommit(false);
316             //delete_flag:删除标记 0：未删除 1：已删除
317            String sql="SELECT * FROM HOUSE WHERE HOUSE_ID=?";
318            PreparedStatement smt=conn.prepareStatement(sql);
319            smt.setString(1,house_id);
320            ResultSet rs=smt.executeQuery();
321            conn.commit();
322            while(rs.next()) {
323                house.setHouseID(rs.getObject("house_id"));
324                house.setHouseName(rs.getString("house_name"));
325                house.setBuildNumber(rs.getString("build_number"));
326                house.setRoomNumber(rs.getString("room_number"));
327                house.setHouseType(rs.getString("house_type"));
328                house.setArea(rs.getString("area"));
329                house.setPrice(rs.getFloat("price"));
330                house.setRoomStatus(rs.getInt("room_status"));
331                house.setCompleteYear(rs.getDate("complete_year"));
332                house.setPhone(rs.getString("phone"));
333                house.setOrderName(rs.getString("order_name"));
334                house.setRemarks(rs.getString("remarks"));
335            }
336            return house;
337        } catch (SQLException e) {
338            log.error("根据UUID查询房源信息失败",e);
339            DBUtil.rollBack(conn);
340            e.printStackTrace();
341            return house;
342            //throw new RuntimeException("分页查询房源信息失败",e);
343        }finally {
344            DBUtil.closeConnection(conn);
345        }
346    }
347 
348    /**
349     *查询房源信息是否存在
350     *
351     *
352     * @param houseName
353     * @param buildNumber
354     * @param roomNumber
355     * @return
356     * @author wentao
357     * @time 2018/2/20
358     */
359    public List<HouseEntity> checkHouse(String houseName,String buildNumber,String roomNumber){
360 
361        List<HouseEntity> houseList=new ArrayList<HouseEntity>();
362        //List<List<HouseEntity>> resultList=new ArrayList<List<HouseEntity>>();
363        Connection conn=null;
364        try {
365            conn=DBUtil.getConnection();
366            conn.setAutoCommit(false);
367            //delete_flag:删除标记 0：未删除 1：已删除
368            String sql="SELECT * FROM HOUSE WHERE HOUSE_NAME=? AND BUILD_NUMBER=? AND ROOM_NUMBER=? AND " +
369                    "DELETE_FLAG=0";
370            PreparedStatement smt=conn.prepareStatement(sql);
371            smt.setString(1,houseName);
372            smt.setString(2,buildNumber);
373            smt.setString(3,roomNumber);
374            ResultSet rs=smt.executeQuery();
375            conn.commit();
376            while(rs.next()) {
377                HouseEntity house=new HouseEntity();
378                house.setHouseID(rs.getObject("house_id"));
379                house.setHouseName(rs.getString("house_name"));
380                house.setBuildNumber(rs.getString("build_number"));
381                house.setRoomNumber(rs.getString("room_number"));
382                houseList.add(house);
383            }
384            return houseList;
385        } catch (SQLException e) {
386            log.error("查询房源信息是否存在失败",e);
387            DBUtil.rollBack(conn);
388            e.printStackTrace();
389            HouseEntity house=new HouseEntity();
390            house.setHouseName("温涛");
391            houseList.add(house);
392            return houseList;
393        }finally {
394            DBUtil.closeConnection(conn);
395        }
396 
397    }
398    /**编辑时查询房源信息是否存在
399     *
400     * @param houseName
401     * @param buildNumber
402     * @param roomNumber
403     * @param houseID
404     * @return
405     * @author wentao
406     * @time 2018/2/22 0022
407     */
408 
409    public List<HouseEntity> checkHouse(String houseName,String buildNumber,String roomNumber,String houseID){
410        List<HouseEntity> houseList=new ArrayList<HouseEntity>();
411        String house_ID=UUIDUtils.addSepc(houseID);
412        Connection conn=null;
413        try {
414            conn=DBUtil.getConnection();
415            conn.setAutoCommit(false);
416            //delete_flag:删除标记 0：未删除 1：已删除
417            String sql="SELECT * FROM HOUSE WHERE HOUSE_NAME=? AND BUILD_NUMBER=? AND ROOM_NUMBER=? AND " +
418                    "DELETE_FLAG=0 AND HOUSE_ID !=?";
419            PreparedStatement smt=conn.prepareStatement(sql);
420            smt.setString(1,houseName);
421            smt.setString(2,buildNumber);
422            smt.setString(3,roomNumber);
423            smt.setString(4,house_ID);
424            ResultSet rs=smt.executeQuery();
425            conn.commit();
426            while(rs.next()) {
427                HouseEntity house=new HouseEntity();
428                house.setHouseID(rs.getObject("house_id"));
429                house.setHouseName(rs.getString("house_name"));
430                house.setBuildNumber(rs.getString("build_number"));
431                house.setRoomNumber(rs.getString("room_number"));
432                houseList.add(house);
433            }
434            return houseList;
435        } catch (SQLException e) {
436            log.error("查询房源信息是否存在失败",e);
437            DBUtil.rollBack(conn);
438            e.printStackTrace();
439            HouseEntity house=new HouseEntity();
440            house.setHouseName("温涛");
441            houseList.add(house);
442            return houseList;
443        }finally {
444            DBUtil.closeConnection(conn);
445        }
446 
447    }
448    /**删除房源信息
449     * @author wentao
450     * @param houseID
451     * @param userID
452     * @return
453     */
454    public boolean delHouse(String houseID,String userID){
455        Date deletime=new Date();
456        SimpleDateFormat time = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss");
457        Connection conn=null;
458        try {
459            conn=DBUtil.getConnection();
460            conn.setAutoCommit(false);
461            String sql="update house set delete_flag='1',deleter_id=?,deletime=? where house_id=?";
462            PreparedStatement smt=conn.prepareStatement(sql);
463            smt.setString(1,userID);
464            smt.setString(2, time.format(deletime));
465            smt.setString(3, houseID);
466            int delResult=smt.executeUpdate();
467            conn.commit();
468            if(delResult==1){
469                return true;
470            }else{
471                return false;
472            }
473        } catch (SQLException e) {
474            log.error("删除房源信息失败",e);
475            DBUtil.rollBack(conn);
476            e.printStackTrace();
477            return false;
478        }finally {
479            DBUtil.closeConnection(conn);
480        }
481    }
482     
483    /**编辑房源信息
484     * @author wentao
485     * @param 
486     * @throws 
487     * @param edit_house
488     * @param user_id
489     * @return
490     */
491    public boolean editHouse(HouseEntity edit_house,String user_id) {
492        Date updatetime=new Date();
493        SimpleDateFormat time = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss");
494         String houseID=UUIDUtils.addSepc(edit_house.getHouseID());
495        Connection editHouseConn=null;
496        try {
497            editHouseConn=DBUtil.getConnection();
498            editHouseConn.setAutoCommit(false);
499            String editHouse="UPDATE HOUSE SET HOUSE_NAME=?,BUILD_NUMBER=?,ROOM_NUMBER=?,HOUSE_TYPE=?,AREA=?,PRICE=?,"
500                    + "ROOM_STATUS=?,COMPLETE_YEAR=?,PHONE=?,REMARKS=?,UPDATER_ID=?,UPDATE_TIME=?"
501                    + "WHERE HOUSE_ID=?";
502            PreparedStatement smt=editHouseConn.prepareStatement(editHouse);
503            smt.setString(1, edit_house.getHouseName());
504            smt.setString(2, edit_house.getBuildNumber());
505            smt.setString(3, edit_house.getRoomNumber());
506            smt.setString(4, edit_house.getHouseType());
507            smt.setString(5, edit_house.getArea());
508            smt.setDouble(6, edit_house.getPrice());
509            smt.setInt(7, edit_house.getRoomStatus());
510            smt.setDate(8, edit_house.getCompleteYear());
511            smt.setString(9, edit_house.getPhone());
512            smt.setString(10, edit_house.getRemarks());
513            smt.setString(11, user_id);
514            smt.setString(12, time.format(updatetime));
515            smt.setString(13, houseID);
516            int editResult=smt.executeUpdate();
517            editHouseConn.commit();
518            if(editResult==1){
519                return true;
520            }else{
521                return false;
522            }   
523        } catch (SQLException e) {
524            DBUtil.rollBack(editHouseConn);
525            log.error("修改房源信息失败",e);
526            e.printStackTrace();
527            return false;
528        }finally{
529            DBUtil.closeConnection(editHouseConn);
530        }   
531    }
532 
533 
534/**添加房源信息实体方法
535 * @author wentao
536 * @param add_house
537 * @return
538 */
539public boolean addHouse(HouseEntity add_house,String userID) {
540    Connection addHouseConn=null;
541    try {
542        addHouseConn=DBUtil.getConnection();
543        addHouseConn.setAutoCommit(false);
544        String addHouse="INSERT INTO HOUSE (HOUSE_ID,HOUSE_NAME,BUILD_NUMBER,ROOM_NUMBER,HOUSE_TYPE,AREA,PRICE,"
545                + "ROOM_STATUS,COMPLETE_YEAR,PHONE,REMARKS,ORDER_NAME,CREATER_ID) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
546        PreparedStatement smt=addHouseConn.prepareStatement(addHouse,new String[]{"house_id"});
547        smt.setString(1, UUIDUtils.addSepc(add_house.getHouseID()));
548        //System.out.println(add_house.getHouseID());
549        smt.setString(2, add_house.getHouseName());
550        smt.setString(3, add_house.getBuildNumber());
551        smt.setString(4, add_house.getRoomNumber());
552        smt.setString(5, add_house.getHouseType());
553        smt.setString(6, add_house.getArea());
554        smt.setFloat(7, add_house.getPrice());
555        smt.setInt(8, add_house.getRoomStatus());
556        smt.setDate(9, add_house.getCompleteYear());
557        smt.setString(10, add_house.getPhone());
558        smt.setString(11, add_house.getRemarks());
559        smt.setString(12, add_house.getOrderName());
560        smt.setString(13,userID);
561        smt.executeUpdate();
562        addHouseConn.commit();
563        //从smt中获取生成的主键
564        //结果集中包含1行1列
565        ResultSet rs = smt.getGeneratedKeys();
566        if(rs.next()){
567            //这种场景下的结果集,只能通过字段的序号获取值.
568            String addSuccessID = rs.getString(1);  
569            log.info("新增的房源主键id是"+addSuccessID);
570            return true;
571        }else{
572            return false;
573        }   
574    } catch (SQLException e) {
575        DBUtil.rollBack(addHouseConn);
576        log.error("新增房源信息失败",e);
577        e.printStackTrace();
578        return false;
579    }finally{
580        DBUtil.closeConnection(addHouseConn);
581    }   
582}
583 
584    /**批量添加房源信息实体方法
585     * @author wentao
586     * @param
587     * @return
588     */
589    public List<String>  addUploadFile(MultipartFile uploadFile,String userID) {
590        Workbook wookbook = ExcelUtil.createWorkbook(uploadFile);
591        //ImportMessage listMessage=new ImportMessage();
592        List<String> errorList = new ArrayList<String>();
593        int sheetNumber = wookbook.getNumberOfSheets();
594        if (sheetNumber != 1) {
595            errorList.add("该Excel【sheet】数量不正确,请下载【正确模板】进行上传操作");
596            return errorList;
597        }
598        Sheet sheet = wookbook.getSheetAt(0);// 第一个sheet
599        int rows = sheet.getPhysicalNumberOfRows();
600        if (rows <= 3) {//数据条数校验，第一行为表头，第二行开始为数据
601            errorList.add("您上传的表格数据为空!");
602            return errorList;
603        }
604        Row cellsTitle = sheet.getRow(0);
605        int cellNumber = cellsTitle.getLastCellNum();
606        if (cellNumber != 8) {
607            errorList.add("该Excel列数量不正确,请下载【正确模板】进行上传操作");
608            return errorList;
609        }
610        String[] titles = {"小区名称", "栋号", "房号", "户型", "面积", "价格", "装修程度", "建成年限",
611                "房主手机号", "备注"};
612        List<HouseEntity> houseList = new ArrayList<HouseEntity>();
613        //List<HouseEntity> houseList = new ArrayList<HouseEntity>();
614        for (int i = 3; i < rows; i++) {//数据校验
615            Row row = sheet.getRow(i);
616            if (row == null){
617                continue;}
618            for (int c = 0; c < row.getLastCellNum(); c++) {//设置excel内容为String
619                if (row.getCell(c) != null)
620                    row.getCell(c).setCellType(Cell.CELL_TYPE_STRING);
621            }
622            HouseEntity house = new HouseEntity();
623            int cellColumnIndex = 0;//从第一列开始读取数据
624 
625            // 小区名称列
626            String houseName = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
627            if (StringUtils.isEmpty(houseName)) {
628                errorList.add("第" + (i + 1) + "行小区名称是空的！");
629            } else {
630                house.setHouseName(houseName);
631                if (!house.getHouseName().matches(CODE_HOUSE_NAME)) {
632                    errorList.add("第" + (i + 1) + "行小区名称不符合规则！");
633                }
634            }
635            // 栋号列
636            String buildNumber = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
637            if (StringUtils.isEmpty(buildNumber)) {
638                errorList.add("第" + (i + 1) + "行栋号是空的！");
639            } else {
640                if (!buildNumber.matches(CODE_BUILDNUMBER)) {
641                    errorList.add("第" + (i + 1) + "行栋号超出字数限制！");
642                }else{
643                try{
644                        house.setBuildNumber(buildNumber);
645 
646                }catch (NumberFormatException e){
647                    errorList.add("第" + (i + 1) + "行栋号不符合规则！");
648                }}
649 
650            }
651            // 房号列
652            String roomNumber = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
653            if (StringUtils.isEmpty(roomNumber)) {
654                errorList.add("第" + (i + 1) + "行房号是空的！");
655            } else {
656                if (!roomNumber.matches(CODE_ROOMNUMBER)) {
657                    errorList.add("第" + (i + 1) + "行房号超出字数限制！");
658                }else{
659                    try{
660                        house.setRoomNumber(roomNumber);
661                    }catch (NumberFormatException e){
662                    errorList.add("第" + (i + 1) + "行房号不符合规则！");
663                    }}
664            }
665            // 户型列
666            String houseType = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
667            if (StringUtils.isEmpty(houseType)) {
668                errorList.add("第" + (i + 1) + "行户型是空的！");
669            } else {
670                house.setHouseType(houseType);
671                if (!house.getHouseType().matches(CODE_HOUSETYPE)) {
672                    errorList.add("第" + (i + 1) + "行户型不符合规则！");
673                }
674            }
675            // 面积列
676            String area = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
677            if (StringUtils.isEmpty(area)) {
678                errorList.add("第" + (i + 1) + "行面积是空的！");
679            } else {
680                try{
681                    if (!area.matches(CODE_AREA)) {
682                        errorList.add("第" + (i + 1) + "行面积不符合规则！");
683                    }else{
684                        house.setArea(area);
685                    }
686                }catch (NumberFormatException e){
687                    errorList.add("第" + (i + 1) + "行面积不符合规则！");
688                }
689            }
690            // 价格列
691            String price = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
692            if (StringUtils.isEmpty(price)) {
693                errorList.add("第" + (i + 1) + "行价格是空的！");
694            } else {
695                try{
696                    if (!price.matches(CODE_PRICE)) {
697                        errorList.add("第" + (i + 1) + "行价格不符合规则！");
698                    }else{
699                        house.setPrice(Float.valueOf(price));
700                    }
701                }catch (NumberFormatException e){
702                    errorList.add("第" + (i + 1) + "行价格不符合规则！");
703                }
704            }
705            // 装修程度列
706            String roomStatus = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
707            if (StringUtils.isEmpty(roomStatus)) {
708                house.setRoomStatus(5);
709            } else {
710               Integer status;
711               if(roomStatus.equals("毛坯")){
712                   status=0;
713                   house.setRoomStatus(status);
714               }else if(roomStatus.equals("简装")){
715                   status=1;
716                   house.setRoomStatus(status);
717               }else if(roomStatus.equals("中装")){
718                   status=2;
719                   house.setRoomStatus(status);
720               }else if(roomStatus.equals("精装")){
721                   status=3;
722                   house.setRoomStatus(status);
723               }else if(roomStatus.equals("豪华装")){
724                   status=4;
725                   house.setRoomStatus(status);
726               }else{
727                   errorList.add("第" + (i + 1) + "行装修程度不是选择的！");
728               }
729            }
730 
731            // 建成年限列
732 
733 
734            String completeYear = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
735            DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
736            if (StringUtils.isEmpty(completeYear)) {
737                house.setCompleteYear(null);
738            } else {
739                try{
740                    Double year=Double.parseDouble(completeYear);
741                    String time=formater.format(HSSFDateUtil.getJavaDate(year));
742                    if (!time.matches(CODE_COMPLETEYEAR)) {
743                        errorList.add("第" + (i + 1) + "行建成年限不符合规则！");
744                    }else{
745                        house.setCompleteYear(java.sql.Date.valueOf(time));
746                    }
747                }catch (NumberFormatException e){
748                    errorList.add("第" + (i + 1) + "行建成年限不符合规则！");
749                }
750            }
751 
752 
753            // 房主手机号列
754            String phone = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
755            if (StringUtils.isEmpty(phone)) {
756                errorList.add("第" + (i + 1) + "行房主手机号是空的！");
757            } else {
758                if (!phone.matches(CODE_PHONE)) {
759                    errorList.add("第" + (i + 1) + "行手机号超出字数限制！");
760                }else{
761                house.setPhone(phone);}
762 
763            }
764            // 备注列
765            String remarks = ExcelUtil.getCellValue(row.getCell(cellColumnIndex++));
766            if (!StringUtils.isEmpty(remarks)) {
767                house.setRemarks(remarks);
768                if (!house.getRemarks().matches(CODE_REMARKS)) {
769                    errorList.add("第" + (i + 1) + "行备注不符合规则！");
770                }
771            }
772            if(errorList.size()==0) {
773                for (HouseEntity houses : houseList) {
774                    if (houses.getHouseName().equals(house.getHouseName()) &&
775                            houses.getRoomNumber().equals(house.getRoomNumber())
776                            && houses.getBuildNumber().equals(house.getBuildNumber())) {
777                        errorList.add("第" + (i + 1) + "行的信息已经在表格里存在！请检查表格内容。");
778                    }
779                }
780            }
781            houseList.add(house);
782        }
783 
784        if(errorList.size()==0) {
785            int i=4;
786            for (HouseEntity houses : houseList) {
787                List<HouseEntity> check = checkHouse(houses.getHouseName(), houses.getBuildNumber(), houses.getRoomNumber());
788                if (check.size() > 0) {
789                    for (HouseEntity h : check) {
790                        if ("温涛".equals(h.getHouseName())) {
791                            errorList.add("第" + (i) + "行的信息查询是否存在时出错，请把表格重新上传一次。");
792                        } else {
793                            errorList.add("第" + (i) + "行的房源信息已经在系统中存在了！请检查表格内容是否跟系统中的房源信息重复了。");
794                        }
795                    }
796                }
797                i++;
798            }
799        }
800        if (errorList.size() > 0)
801            return errorList;
802        else {
803            Connection addHouseConn=null;
804            try {
805                addHouseConn = DBUtil.getConnection();
806                addHouseConn.setAutoCommit(false);
807                String addHouse = "INSERT INTO HOUSE (HOUSE_ID,HOUSE_NAME,BUILD_NUMBER,ROOM_NUMBER,HOUSE_TYPE,AREA,PRICE,"
808                        + "ROOM_STATUS,COMPLETE_YEAR,PHONE,REMARKS,ORDER_NAME,CREATER_ID) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
809                PreparedStatement smt = addHouseConn.prepareStatement(addHouse);
810                List<String> correctList = new ArrayList<String>();
811                int i=0;//用作控制多少条数据时往数据库里插
812                for (HouseEntity houses : houseList) {
813 
814                    smt.setString(1, UUIDUtils.getUUIDStr());
815                    smt.setString(2, houses.getHouseName());
816                    smt.setString(3, houses.getBuildNumber());
817                    smt.setString(4, houses.getRoomNumber());
818                    smt.setString(5, houses.getHouseType());
819                    smt.setString(6, houses.getArea());
820                    smt.setFloat(7, houses.getPrice());
821                    smt.setInt(8, houses.getRoomStatus());
822                    smt.setDate(9, houses.getCompleteYear());
823                    smt.setString(10, houses.getPhone());
824                    smt.setString(11, houses.getRemarks());
825                    smt.setString(12,"批量上传的房源");
826                    smt.setString(13,userID);
827                    smt.addBatch();
828                    i++;
829                    if(i%10==0){
830                        smt.executeBatch();
831                        //清空暂存的数据，便于下一批
832                        smt.clearBatch();
833                       // i=0;
834                    }
835                }
836                smt.executeBatch();
837                addHouseConn.commit();
838                correctList.add("导入成功！");
839                return correctList;
840 
841            }catch (SQLException e) {
842                DBUtil.rollBack(addHouseConn);
843                log.error("批量插入数据库失败",e);
844                e.printStackTrace();
845                errorList.add("批量插入数据库失败");
846                return errorList;
847            }finally{
848                DBUtil.closeConnection(addHouseConn);
849            }
850 
851        }
852    }
853 
854 
855}