Simple MySQL operations: Statement and PreparedStatement

Simple MySQL operations: Statement and PreparedStatement

(1) tool class for connecting to 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/your database name ", "Your Account", "your password");} 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 (Statemen T stmt, String SQL) {ResultSet rs = null; try {rs = stmt.exe cuteQuery (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) databases that use preparedStatement to insert data:

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) Use preparedStatement to select data and read data:

Public List <Comment> getCommentDetail (int userid, int articleid) {Connection connection = DBUtil. getConn (); String SQL = "select * from comment c where c. userid =? And c. articleid =? "; // Write an SQL statement. The first field does not need to be inserted. It is automatically added with PreparedStatement preparedStatement = null; List <Comment> commentList = new ArrayList <Comment> (); try {preparedStatement = connection. prepareStatement (SQL); preparedStatement. setInt (1, userid); preparedStatement. setInt (2, articleid); // you do not need to input the SQL statement ResultSet rs = preparedStatement.exe cuteQuery () for the message output here; // you can determine whether it is null if (rs. next () {while (rs. next () {// iterate the information into the 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) database operations using Statement:

Public List <Article> getArticleMessage () {Connection connection = DBUtil. getConn (); String SQL = "select * from article"; // write an SQL Statement. The first field does not need to be inserted. It is an automatically added statement Statement = null; list <Article> articleList = new ArrayList <Article> (); try {statement = connection. createStatement (); ResultSet rs = statement.exe cuteQuery (SQL); // determines whether it is null if (rs. next () {while (rs. next () {// iterate the information into the 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 ;}