Struts2+Mysql开发的小BBS系统

package com.db; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import com.bbs.Article; import com.bbs.User; public class ArticleManager { Connection conn = null; public boolean addArticle(Article article) { DBConnect dbc = new DBConnect(); String sql = "insert into article (author_id,title,content,time,author_name)values(?,?,?,?,?)"; try { this.conn = new DBConnect().getConnection(); PreparedStatement ps = this.conn.prepareStatement(sql); ps.setInt(1, article.getAuthor_id()); ps.setString(2, article.getTitle()); ps.setString(3, article.getContent()); ps.setDate(4, new java.sql.Date(article.getTime().getTime())); ps.setString(5, article.getAuthor_name()); ps.executeUpdate(); ps.close(); this.conn.close(); return true; } catch (SQLException e) { e.printStackTrace(); }return false; } public ArrayList<Article> getSomeArticle(int start, int length) { String sql = "select * from article order by id DESC LIMIT ?,?"; try { this.conn = new DBConnect().getConnection(); PreparedStatement ps = this.conn.prepareStatement(sql); ps.setInt(1, start); ps.setInt(2, length); ResultSet rs = ps.executeQuery(); ArrayList<Article> list= new ArrayList<Article>(); Article article = null; while (rs.next()) { article = new Article(); article.setAuthor_id(rs.getInt("author_id")); article.setId(rs.getInt("id")); article.setContent(rs.getString("content")); article.setTitle(rs.getString("title")); article.setTime(rs.getDate("time")); article.setAuthor_name(rs.getString("author_name")); list.add(article); } rs.close(); ps.close(); this.conn.close(); return list; } catch (SQLException e) { e.printStackTrace(); }return null; } public ArrayList<Article> getSomeArticle(User user, int start, int length) { String sql = "select * from article where author_id=? order by id DESC LIMIT ?,?"; try{ this.conn = new DBConnect().getConnection(); PreparedStatement ps = this.conn.prepareStatement(sql); ps.setInt(1, user.getId()); ps.setInt(2, start); ps.setInt(3, length); ResultSet rs = ps.executeQuery(); ArrayList<Article> list = new ArrayList<Article>(); Article article = null; while (rs.next()) { article = new Article(); article.setAuthor_id(rs.getInt("author_id")); article.setId(rs.getInt("id")); article.setContent(rs.getString("content")); article.setTitle(rs.getString("title")); article.setTime(rs.getDate("time")); article.setAuthor_name(rs.getString("author_name")); list.add(article); } rs.close(); ps.close(); this.conn.close(); return list; } catch (SQLException e) { e.printStackTrace(); }return null; } public int getAllArticles(User user) { String sql = "select count(*) from article where author_id=?"; try { this.conn = new DBConnect().getConnection(); PreparedStatement ps = this.conn.prepareStatement(sql); ps.setInt(1, user.getId()); ResultSet rs = ps.executeQuery(); rs.next(); int all = rs.getInt(1); rs.close(); ps.close(); this.conn.close(); return all; } catch (SQLException e) { e.printStackTrace(); }return -1; } public int getAllArticles() { String sql = "select count(*) from article"; try { this.conn = new DBConnect().getConnection(); PreparedStatement ps = this.conn.prepareStatement(sql); ResultSet rs = ps.executeQuery(); rs.next(); int all = rs.getInt(1); rs.close(); ps.close(); this.conn.close(); return all; } catch (SQLException e) { e.printStackTrace(); }return -1; } public Article getArticleById(int id) { String sql = "select * from article where id=?"; try { this.conn = new DBConnect().getConnection(); PreparedStatement ps = this.conn.prepareStatement(sql); ps.setInt(1, id); ResultSet rs = ps.executeQuery(); Article article = null; while (rs.next()) { article = new Article(); article.setAuthor_id(rs.getInt("author_id")); article.setId(rs.getInt("id")); article.setContent(rs.getString("content")); article.setTitle(rs.getString("title")); article.setTime(rs.getDate("time")); article.setAuthor_name(rs.getString("author_name")); } rs.close(); ps.close(); this.conn.close(); return article; } catch (SQLException e) { e.printStackTrace(); }return null; } public boolean deleteArticle(int id) { String sql = "DELETE FROM article WHERE id=?"; try { this.conn = new DBConnect().getConnection(); PreparedStatement ps = this.conn.prepareStatement(sql); ps.setInt(1, id); ps.executeUpdate(); ps.close(); this.conn.close(); return true; } catch (SQLException e) { e.printStackTrace(); }return false; } }