网站地图    收藏   

主页 > 后端 > mysql数据库 >

MySQL的简单操作方法:Statement、PreparedStatement -

来源:自学PHP网    时间:2015-04-16 10:50 作者: 阅读:

[导读] (1)连接mysql的工具类:DBUtil javapackage com xuliugen util;import java sql Connection;import java sql DriverManager;import java sql PreparedStatement;import java sql ResultSet;impor...

(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;
        }
    }
}

(2)使用preparedStatement插入数据的数据库:

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;
    }

(3)使用preparedStatement选择数据,读取数据:

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;
    }

(4)使用Statement操作数据库:

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;
    }

自学PHP网专注网站建设学习,PHP程序学习,平面设计学习,以及操作系统学习

京ICP备14009008号-1@版权所有www.zixuephp.com

网站声明:本站所有视频,教程都由网友上传,站长收集和分享给大家学习使用,如由牵扯版权问题请联系站长邮箱904561283@qq.com

添加评论