HashNews.zip

package controller; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.Iterator; import Bean.New; import Bean.Reply; import Bean.User; public class DBUtil { private Connection conn=null; //用户信息和URL private String url="jdbc:mysql://localhost:3306/hashnews?serverTimezone=UTC"; private String userName="root"; private String password="root"; public DBUtil() { try { getConn(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public Connection getConn() throws ClassNotFoundException, SQLException { //加载驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //连接 conn=DriverManager.getConnection(url,userName,password); //返回用户连接 return conn; } //登录 public User login(String username,String password) { String sql="select * from `hashnews`.`user` where `username`= \'"+username+"\' and `password`=\'"+password+"\';"; User user=null; Statement st=null; ResultSet rs=null; try { st=conn.createStatement(); rs=st.executeQuery(sql); while(rs.next()) { user=new User(); user.setGrade(rs.getInt("Grade")); user.setPassWord(rs.getString("password")); user.setPost(rs.getString("post")); user.setUserName(rs.getString("username")); } if(st!=null) { st.close(); } if(rs!=null) { rs.close(); } }catch(SQLException a) { a.printStackTrace(); } return user; } //注册 public User regist(String username,String password,String post) { String sql="INSERT INTO `hashnews`.`user` (`username`, `password`, `post`) VALUES (\'"+username+"\', \'"+password+"\', \'"+post+"\');"; User user=null; Statement st=null; try { st=conn.createStatement(); st.execute(sql); user=new User(); user.setGrade(0); user.setPassWord(password); user.setPost(post); user.setUserName(username); if(st!=null) { st.close(); } }catch(SQLException a) { a.printStackTrace(); } return user; } //获取新闻 public ArrayList<New>[] getNews(String name) { String getNew="select `title`,`content`,u.username as `name`,`date`,`viewCount`,`replyCount` from `hashnews`.`news` n inner join `hashnews`.`user` u where n.userId = u.id;"; String getReply="select b.title as `title`, c.username as `name`,a.content as `content`, a.date as `date` from `hashnews`.`reply` a inner join `hashnews`.`news` b on b.id=a.newId inner join `hashnews`.`user` c on c.id=a.userid;"; Statement st=null; ResultSet rs=null; //临时变量 New news=null; Reply reply=null; //创建存存储 ArrayList l[]=new ArrayList[2]; ArrayList<New> all=new ArrayList<New>(); ArrayList<New> self=new ArrayList<New>(); ArrayList<Reply> r=new ArrayList<Reply>(); //创建序列 l[0]=all; l[1]=self; try { //查询回复 st=conn.createStatement(); rs=st.executeQuery(getReply); while(rs.next()) { reply=new Reply(); reply.setContent(rs.getString("content")); reply.setDate(rs.getDate("date").toString()); reply.setNews(rs.getString("title")); reply.setReplyUser(rs.getString("name")); r.add(reply); } rs.close(); st.close(); //查询新闻 st=conn.createStatement(); rs=st.executeQuery(getNew); while(rs.next()) { news=new New(); news.setAuther(rs.getString("name")); news.setContent(rs.getString("content")); news.setDate(rs.getDate("date").toString()); news.setReplyCount(rs.getInt("replyCount")); news.setTitle(rs.getString("title")); news.setViewCount(rs.getInt("viewCount")); Iterator<Reply> iterator = r.iterator(); while (iterator.hasNext()) { Reply re = iterator.next(); if(news.getTitle().equals(re.getNews())) { news.addReply(re); } } if(news.getAuther().equals(name)){ self.add(news); }else { all.add(news); } } rs.close(); st.close(); }catch(SQLException a) { a.printStackTrace(); } return l; } //存入数据库 public boolean insertContent(String content,String username,String title,Date date) { String getuser="select id from `hashnews`.`user` where `username`=\'"+username+"\';"; String getTitle="select id from `hashnews`.`news` where `title` like \'"+title+"\'"; String sql="INSERT INTO `hashnews`.`reply` (`content`, `userId`, `newId`, `date`) VALUES (?, ?, ?, ?);"; //暂存数据 int userid=0; int newsid=0; //标志 boolean flag=true; // Statement st=null; ResultSet rs=null; PreparedStatement ps=null; try { st=conn.createStatement(); rs=st.executeQuery(getuser); while(rs.next()) { userid=rs.getInt("id"); } st.close(); rs.close(); st=conn.createStatement(); rs=st.executeQuery(getTitle); while(rs.next()) { newsid=rs.getInt("id"); } st.close(); rs.close(); ps=conn.prepareStatement(sql); ps.setString(1, content); ps.setInt(2, userid); ps.setInt(3, newsid); ps.setDate(4, new java.sql.Date(date.getTime())); flag=ps.execute(); ps.close(); }catch(SQLException a) { a.printStackTrace(); } return flag; } //获取当前用户所有评论 public ArrayList getSelfReply(String name) { String getReply="select a.id as `id`,a.content as `content`,b.title as `new`,a.date as `date` from `hashnews`.`reply` a inner join `hashnews`.`news` b on a.newId=b.id inner join `hashnews`.`user` c on a.userId=c.id where c.username=\'"+name+"\';"; Statement st=null; ResultSet rs=null; //创建存储 ArrayList<Reply> replys=new ArrayList<Reply>(); Reply r=null; try { st=conn.createStatement(); rs=st.executeQuery(getReply); while(rs.next()) { r=new Reply(); r.setId(rs.getInt("id")); r.setContent(rs.getString("content")); r.setDate(rs.getDate("date").toString()); r.setReplyUser(name); r.setNews(rs.getString("new")); replys.add(r); } st.close(); rs.close(); }catch(SQLException a) { a.printStackTrace(); } return replys; } //删除所选评论 public boolean deleteReply(int id) { String sql="DELETE FROM `hashnews`.`reply` WHERE (`id` = \'"+id+"\');"; Statement st=null; boolean flag=true; try { st=conn.createStatement(); flag=st.execute(sql); }catch(SQLException a) { a.printStackTrace(); } return flag; } //修改用户信息 public User alterUser(String u,String p,String au,String ap,String po) { User user=login(u,p); if(user==null) return null; String alter="UPDATE `hashnews`.`user` SET `username` = \'"+au+"\', `password` = \'"+ap+"\', `post` = \'"+po+"\' WHERE (`username` = \'"+u+"\');"; Statement st=null; ResultSet rs=null; System.out.println(alter); try { st=conn.createStatement(); st.execute(alter); user.setPassWord(ap); user.setUserName(au); user.setPost(po); st.close(); }catch(SQLException a) { a.printStackTrace(); } return user; } //发表文章 public New publish(String username,String title,String content,Date date) { String getId="select id from `hashnews`.`user` where `username` = \'"+username+"\';"; String publish="INSERT INTO `hashnews`.`news` (`title`, `content`, `userId`, `date`) VALUES (?, ?, ?, ?);"; New news=null; int id=0; Statement st=null; PreparedStatement ps=null; ResultSet rs=null; tr