Java编写web系统-房屋租借平台

package org.house.comment; import java.sql.*; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; import org.house.domain.House; public class CommentDao { static final String DBDRIVER="com.mysql.jdbc.Driver"; static final String DBURL="jdbc:mysql://192.168.12.193:3306/house?useUnicode=true&characterEncoding=UTF-8"; static final String DBUSERNAME="root"; static final String DBPASSWORD="111"; static Connection conn = null; ResultSet rs=null; PreparedStatement pstmt = null; static{ try { Class.forName(DBDRIVER); conn=DriverManager.getConnection(DBURL,DBUSERNAME,DBPASSWORD); } catch(Exception e) { e.printStackTrace(); } } public List listByPage(int pageNo){ return listByPage(pageNo,null); } public List listByPage(int pageNo,String houseId) //按页查询每次要显示的记录 { List commentList=new ArrayList(); int pageSize=3; try { String sql="select count(*) from comment"; Statement stmt=conn.createStatement(); rs=stmt.executeQuery(sql); rs.next(); int totalRecords=rs.getInt(1); rs.close(); int totalPages=(totalRecords/pageSize==0?totalRecords/pageSize:totalRecords/pageSize+1); if(pageNo<1) { pageNo=1; } if(totalPages>0) { if(pageNo>totalPages) { pageNo=totalPages; } } int startPos=(pageNo-1)*pageSize; if (houseId==null){ sql="select * from comment order by id desc limit "+startPos+","+pageSize; }else{ sql="select * from comment where houseid = "+houseId+" order by id desc limit "+startPos+","+pageSize; } rs=stmt.executeQuery(sql); while(rs.next()){ Comment comment=new Comment(); comment.setId(rs.getInt("id")); comment.setContent(rs.getString("content")); //Date date=rs.getDate("posttime"); //String dateStr=date.toString(); //System.out.println(dateStr); //SimpleDateFormat matter=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //System.out.println(matter.format(rs.getDate("posttime"))); String dateStr=rs.getTimestamp("posttime").toString(); String dateStr2=dateStr.substring(0, 19); //System.out.println(dateStr2); comment.setPosttime(dateStr2); comment.setUsername(rs.getString("username")); commentList.add(comment); } } catch(Exception e) { e.printStackTrace(); } return commentList; } public boolean addComment(Comment comment) { boolean flag = false; String sql = "insert into comment(content,posttime,username,houseid) values(?,now(),?,?)"; try { //conn = this.getConn(); pstmt = conn.prepareStatement(sql); //pstmt.setInt(1, comment.getId()); pstmt.setString(1, comment.getContent()); pstmt.setString(2, comment.getUsername()); pstmt.setInt(3,comment.getHouseId()); //pstmt.setDate(, comment.getPosttime()); flag=pstmt.execute(); } catch (SQLException e) { e.printStackTrace(); } return flag; } public boolean deletById(int id) { System.out.println("delete"); boolean flag=false; String sql="delete from comment where id="+ id; try { Statement stmt=conn.createStatement(); stmt.executeUpdate(sql); flag=true; stmt.close(); } catch(Exception e) { e.printStackTrace(); } return flag; } public int findPagelast() { int totalPages = 0; int pageSize = 3; try { String sql="select count(*) from comment"; Statement stmt=conn.createStatement(); rs=stmt.executeQuery(sql); rs.next(); int totalRecords=rs.getInt(1); totalPages = totalRecords%pageSize==0?totalRecords/pageSize:totalRecords/pageSize + 1; rs.close(); } catch(Exception e) { e.printStackTrace(); } return totalPages; } public List searchByinput(String style,String housemodel,int priceValue) { List houseList = new ArrayList(); String sql=null; try { switch(priceValue) { case 0: sql = "select * from house where huxing = '"+housemodel+"' and fangxing ='" +style+"'"; break; case 1: sql = "select * from house where huxing = '"+housemodel+"' and fangxing ='" +style+"' and price between " +0+ "and" +299; break; case 2: sql = "select * from house where huxing = '"+housemodel+"' and fangxing ='" +style+"' and price between " +301+ "and" +499; break; case 3: sql = "select * from house where huxing = '"+housemodel+"' and fangxing ='" +style+"' and price between " +500+ "and" +999; break; default :sql = "select * from house where huxing = '"+housemodel+"' and fangxing ='" +style+"' and price > " +999; } Statement stmt = conn.createStatement(); rs = stmt.executeQuery(sql); while(rs.next()) { House house = new House(); house.setId(rs.getInt("id")); house.setTitle(rs.getString("title")); house.setAddress(rs.getString("address")); house.setUserid(rs.getInt("userid")); //SimpleDateFormat formatDate = new SimpleDateFormat("yyyy-MM-dd"); house.setPosttime(rs.getDate("posttime")); house.setDescription(rs.getString("description")); house.setPrice(rs.getDouble("price")); house.setArea(rs.getDouble("area")); house.setHuxing(rs.getString("huxing")); house.setFangxing(rs.getString("fangxing")); houseList.add(house); } rs.close(); } catch(Exception e) { e.printStackTrace(); } return houseList; } }