MySQL的簡單操作方法：Statement、PreparedStatement

MySQL的簡單操作方法：Statement、PreparedStatement

（1）連接mysql的工具類：DBUtil.java

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<Comment> 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<Comment> commentList = new ArrayList<Comment>();
        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<Article> getArticleMessage() {
        Connection connection = DBUtil.getConn();
        String sql = "select * from article";// 編寫sql語句，第一個字段不需要插入，是自動增加的
        Statement statement = null;
        List<Article> articleList = new ArrayList<Article>();
        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;
    }