package com.xuliugen.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBUtil {
 public static Connection getConn() {
 Connection conn = null;
 try {
 Class.forName("com.mysql.jdbc.Driver");
 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/你的数据库名称", "你的账号", "你的密码");
 } catch (ClassNotFoundException e) {
 e.printStackTrace();
 } catch (SQLException e) {
 e.printStackTrace();
 }
 return conn;
 }

 public static PreparedStatement prepareStmt(Connection conn, String sql) {
 PreparedStatement pstmt = null;
 try {
 pstmt = conn.prepareStatement(sql);
 } catch (SQLException e) {
 e.printStackTrace();
 }
 return pstmt;
 }

 public static ResultSet executeQuery(Statement stmt, String sql) {
 ResultSet rs = null;
 try {
 rs = stmt.executeQuery(sql);
 } catch (SQLException e) {
 e.printStackTrace();
 }
 return rs;
 }


 public static void close(Connection conn, Statement stmt,
 PreparedStatement preStatement, ResultSet rs) {
 if (conn != null) {
 try {
 conn.close();
 } catch (SQLException e) {
 e.printStackTrace();
 }
 conn = null;
 }
 if (stmt != null) {
 try {
 stmt.close();
 } catch (SQLException e) {
 e.printStackTrace();
 }
 stmt = null;
 }
 if (preStatement != null) {
 try {
 preStatement.close();
 } catch (SQLException e) {
 e.printStackTrace();
 }
 preStatement = null;
 }

 if (rs != null) {
 try {
 rs.close();
 } catch (SQLException e) {
 e.printStackTrace();
 }
 rs = null;
 }
 }
}

public boolean saveComment(Comment comment) {

 Connection connection = DBUtil.getConn();
 String sql = "insert into comment values (null,?,?,?,?)";
 PreparedStatement preparedStatement = null;
 boolean flag = false;
 try {
 preparedStatement = connection.prepareStatement(sql);
 preparedStatement.setString(1, comment.getCommenttext() + "");
 preparedStatement.setString(2, comment.getCommenttime() + "");
 preparedStatement.setString(3, comment.getUserid() + "");
 preparedStatement.setString(4, comment.getArticleid() + "");
 int isOk = preparedStatement.executeUpdate();
 if (isOk > 0) {
 return !flag;
 } else {
 return flag;
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 DBUtil.close(connection, null, preparedStatement, null);
 return flag;
 }

public List getCommentDetail(int userid, int articleid) {
 Connection connection = DBUtil.getConn();
 String sql = "select * from comment c where c.userid=? and c.articleid=?";// 编写sql语句，第一个字段不需要插入，是自动增加的
 PreparedStatement preparedStatement = null;
 List commentList = new ArrayList();
 try {
 preparedStatement = connection.prepareStatement(sql);
 preparedStatement.setInt(1, userid);
 preparedStatement.setInt(2, articleid);
 //这里的产讯不需要传入sql语句
 ResultSet rs = preparedStatement.executeQuery();

 // 判断是否为空
 if (rs.next()) {
 while (rs.next()) {// 将信息迭代到list中
 Comment comment = new Comment();
 comment.setCommentid(rs.getInt("commentid"));
 comment.setCommenttext(rs.getString("commenttext"));
 comment.setCommenttime(rs.getString("commenttime"));
 comment.setUserid(rs.getInt("userid"));
 comment.setArticleid(rs.getInt("articleid"));

 commentList.add(comment);
 }
 } else {
 return null;
 }

 } catch (SQLException e) {
 e.printStackTrace();
 }
 DBUtil.close(connection, null, preparedStatement, null);
 return commentList;
 }

public List getArticleMessage() {
 Connection connection = DBUtil.getConn();
 String sql = "select * from article";// 编写sql语句，第一个字段不需要插入，是自动增加的
 Statement statement = null;
 List articleList = new ArrayList();
 try {
 statement = connection.createStatement();
 ResultSet rs = statement.executeQuery(sql);
 //判断是否为空
 if (rs.next()) {
 while (rs.next()) {//将信息迭代到list中
 Article article = new Article();
 article.setTitle(rs.getString("title"));
 article.setContext(rs.getString("context"));
 article.setArticleTime(rs.getString("articletime"));
 article.setUserid(rs.getInt("userid"));
 article.setArticleid(rs.getInt("articleid"));
 articleList.add(article);
 }
 } else {
 return null;
 }

 } catch (SQLException e) {
 e.printStackTrace();
 }
 DBUtil.close(connection, statement, null, null);
 return articleList;
 }