基于JSP的小型bbs

package bbs_services; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import bbs_domain.Page; import bbs_domain.Topic; import bbs_domain.User; import bbs_utils.DBUtils; public class BbsServiceImpl implements IBbsService{ @Override public boolean createPage(Page p) { String sql = "insert into bbs_page values (id,'"+p.getTitle()+"','" +p.getDesct()+"','"+p.getAdmin()+"',now())"; int rs = DBUtils.update(sql); return rs>0; } @Override public boolean createReaplyTopic(Topic t, int tid) { String sql1 = "update bbs_topic set rcount = rcount+1 where id = "+tid; String sql2 = "insert into bbs_topic values(id,"+t.getBid()+","+tid+",'" + t.getTitle()+"','"+t.getDesct()+"','"+t.getCuser()+"',now()," +t.getRcount()+")"; int rs2 = DBUtils.update(sql2); int rs1 = DBUtils.update(sql1); return rs2>0&&rs1>0; } @Override public boolean createTopic(int bid, Topic t) { String sql = "insert into bbs_topic values(id,"+bid+","+t.getPid()+",'" + t.getTitle()+"','"+t.getDesct()+"','"+t.getCuser()+"',now()," +t.getRcount()+")"; return DBUtils.update(sql)>0; } @Override public List<Page> findPages() { String sql = "select id , title ,desct ,admin ,ctime from bbs_page"; Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; List<Page> pages = new ArrayList<Page>(); try { conn = DBUtils.getConn(); stmt = conn.prepareStatement(sql); rs = stmt.executeQuery(); while (rs.next()){ Page page = new Page(); page.setId(rs.getInt("id")); page.setTitle(rs.getString("title")); page.setDesct(rs.getString("desct")); page.setAdmin(rs.getString("admin")); page.setDatetime(new Date(rs.getTimestamp("ctime").getTime())); pages.add(page); } } catch (SQLException e) { e.printStackTrace(); }finally{ DBUtils.free(rs, stmt, conn); } return pages; } @Override public List<Page> findPagesByPageNo(int pageNo, int pageSize) { pageNo = (pageNo-1)*pageSize; String sql = "select id , title ,desct ,admin ,ctime from bbs_page limit ?,?"; Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; List<Page> pages = new ArrayList<Page>(); try { conn = DBUtils.getConn(); stmt = conn.prepareStatement(sql); stmt.setInt(1, pageNo); stmt.setInt(2, pageSize); rs = stmt.executeQuery(); while (rs.next()){ Page page = new Page(); page.setId(rs.getInt("id")); page.setTitle(rs.getString("title")); page.setDesct(rs.getString("desct")); page.setAdmin(rs.getString("admin")); page.setDatetime(new Date(rs.getTimestamp("ctime").getTime())); pages.add(page); } } catch (SQLException e) { e.printStackTrace(); }finally{ DBUtils.free(rs, stmt, conn); } return pages; } @Override public List<Topic> findTopicDetailsById(int tid) { String sql = "select id,bid,pid,title,desct,cuser,ctime,rcount from bbs_topic" + " where id ="+tid; List<Topic> topics = new ArrayList<Topic>(); Connection conn = null; PreparedStatement stmt =null; ResultSet rs = null; try { conn = DBUtils.getConn(); stmt = conn.prepareStatement(sql); rs = stmt.executeQuery(); while (rs.next()){ Topic topic = new Topic(); topic.setId(rs.getInt("id")); topic.setBid(rs.getInt("bid")); topic.setPid(rs.getInt("pid")); topic.setTitle(rs.getString("title")); topic.setDesct(rs.getString("desct")); topic.setCuser(rs.getString("cuser")); topic.setDatetime(new Date(rs.getTimestamp("ctime").getTime())); topic.setRcount(rs.getInt("rcount")); topics.add(topic); } } catch (SQLException e) { e.printStackTrace(); }finally{ DBUtils.free(rs, stmt, conn); } return topics; } @Override public List<Topic> findTopicsByPageId(int bid,int pid) { String sql = "select id,bid,pid,title,desct,cuser,ctime,rcount from bbs_topic" + " where bid ="+bid+" and pid ="+pid; List<Topic> topics = new ArrayList<Topic>(); Connection conn = null; PreparedStatement stmt =null; ResultSet rs = null; try { conn = DBUtils.getConn(); stmt = conn.prepareStatement(sql); rs = stmt.executeQuery(); while (rs.next()){ Topic topic = new Topic(); topic.setId(rs.getInt("id")); topic.setBid(rs.getInt("bid")); topic.setPid(rs.getInt("pid")); topic.setTitle(rs.getString("title")); topic.setDesct(rs.getString("desct")); topic.setCuser(rs.getString("cuser")); topic.setDatetime(new Date(rs.getTimestamp("ctime").getTime())); topic.setRcount(rs.getInt("rcount")); topics.add(topic); } } catch (SQLException e) { e.printStackTrace(); }finally{ DBUtils.free(rs, stmt, conn); } return topics; } @Override public int getTotalPage() { String sql = "select count(id) from bbs_page"; Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; int total = 0; try { conn = DBUtils.getConn(); stmt = conn.prepareStatement(sql); rs = stmt.executeQuery(); if (rs.next()){ total = rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); }finally{ DBUtils.free(rs, stmt, conn); } if(total%10==0) return total/10; else return total/10+1; } @Override public User login(String name, String pwd) { int id = 0; User user = new User(); String sql = "select pwd,id from bbs_user where name =? "; Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { conn = DBUtils.getConn(); stmt = conn.prepareStatement(sql); stmt.setString(1, name); rs = stmt.executeQuery(); if(rs.next()){ String pass = rs.getString("pwd"); if(pass.equals(pwd)) id = rs.getInt("id"); } user = findUserById(id); } catch (SQLException e) { e.printStackTrace(); }finally{ DBUtils.free(rs, stmt, conn); } return user; } @Override public boolean register(User u) { String sql = "insert into bbs_user values (id,'"+u.getName()+"','"+u.getPwd()+"','" + u.getEmail()+"','"+u.getLastip()+"',"+u.getLogincount()+",'"+u.getStat()+"',now())"; return DBUtils.update(sql)>0; } @Override public User findUserById(int id) { User user = new User(); String sql = "select id,name,pwd,email,lastip,logincount,stat,ctime from bbs_user" +" where id = "+id; Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { conn = DBUtils.getConn(); stmt = conn.prepareStatement(sql); rs = stmt.executeQuery(); if(rs.next()){ user.setId(rs.getInt("id")); user.setName(rs.getString("name")); user.setPwd(rs.getString("pwd")); user.setEmail(rs.getString("email")); user.setLastip(rs.getString("lastip")); user.setLogincount(rs.getInt("logincount")); user.setStat(rs.getString("stat")); user.setDatetime(new Date(rs.getTimestamp("ctime").getTime())); } } catch (SQLException e) { e.printStackTrace(); }finally{ DBUtils.free(rs, stmt, conn); } return user; } @Override public boolean loginUpdate(User user) { String sql = "update bbs_user set lastip='"+user.getLastip()+ "',logincount=logincount+1 where id ="+user.getId(); return DBUtils.update(sql)>0; } @Override public String showTime(Date date) { SimpleDateFormat sdf = new SimpleDateFormat("yy-MM-dd HH:mm:ss"); String d = sdf.format(date); return d; } }