MySQL database introduction learning (4) — use tools to encapsulate JDBC to achieve MySQL update operations

1. Introduction

Int executeUpdate(String SQL) contains insert, update, and delete operations. The other type is query operations (SELECT), which are implemented by ResultSet executeQuery(String SQL). Observe the code for the database update operation:

Public int saveByPrepareStatement(book book) {// Concatenate the SQL statement to be executed String sql = "insert into book(name,price,status,discount,isBorrowed,createTime) values(? ,? ,? ,? ,? ,?) "; int result = 0; Connection Connection = jdbcutil.getConnection (); / / create a preprocessing PreparedStatement object PreparedStatement = connection. PrepareStatement (SQL); / / set the value of name placeholder corresponding preparedStatement setString (1 book), getName ()); / / set price corresponding value preparedStatement placeholder. SetDouble (2, book. GetPrice ()); / / set the status placeholder preparedStatement. The corresponding value setByte (3, book getStatus ()); / / set the discount placeholder preparedStatement. The corresponding value setFloat (4, book getDiscount ()); / / set the value of the corresponding placeholder isBorrowed preparedStatement setBoolean (5, book. IsBorrowed ()); / / set createTime placeholder corresponding value, need to Java. Util. The Date into Java. SQL. The Date can be a preparedStatement. SetDate (6, new Date(book.getCreateTime().getTime())); / / start executing, and return the number of rows result = preparedStatement. ExecuteUpdate (); Jdbcutil. close(Connection, preparedStatement, null); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return result; } / update book * * * * @ param newBook * @ return returns the number of rows affected * / public int updateBookByPreparedStatement (book newBook) { String SQL = "Update book set name=? ,price = ? ,status = ? ,discount = ? ,isBorrowed = ? ,createTime = ? where id = ?" ; int result = 0; Connection Connection = jdbcutil.getConnection (); / / create a preprocessing PreparedStatement object PreparedStatement = connection. PrepareStatement (SQL); / / set the value of name placeholder corresponding preparedStatement setString (1, newBook getName ()); / / set price corresponding value preparedStatement placeholder. SetDouble (2, newBook. GetPrice ()); / / set the status placeholder preparedStatement. The corresponding value setByte (3, newBook getStatus ()); / / set the discount placeholder preparedStatement. The corresponding value setFloat (4, newBook getDiscount ()); / / set the value of the corresponding placeholder isBorrowed preparedStatement setBoolean (5, newBook. IsBorrowed ()); / / set createTime placeholder corresponding value, need to Java. Util. The Date into Java. SQL. The Date can be a preparedStatement. SetDate (6, new Date(newBook.getCreateTime().getTime())); Preparedstatement.setint (7, newbook.getid ()); preparedStatement.setint (7, newbook.getid ()); / / update, and return the number of rows result = preparedStatement. ExecuteUpdate (); Jdbcutil. close(Connection, preparedStatement, null); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return result; } /** * delete book * @param ID Id of the book to delete * @return Returns the number of rows affected */ public int deleteBookById(int ID) {// Concatenate the SQL statement to be deleted with String sql = "delete from book where id = ?" ; int result = 0; Connection Connection = jdbcutil.getConnection (); / / create a preprocessing PreparedStatement object PreparedStatement = connection. PrepareStatement (SQL); Preparedstatement.setint (1, id); preparedStatement.setint (1, id); / / update, and return the number of rows result = preparedStatement. ExecuteUpdate (); Jdbcutil. close(Connection, preparedStatement, null); } catch (SQLException e) { e.printStackTrace(); } return result; }Copy the code

Insert, update,delete; insert, update,delete; (2) Create a prepared object PreparedStatement based on the SQL statement. (3) Replace the placeholder “? “in PreparedStatement with the actual parameter. . SetXXX (int parameterIndex, XXX x); preparedStatement.setXXX(int parameterIndex, XXX x); XXX, the actual parameter type of the fill, is indeterminate. At that time we can use preparedStatement. SetObject (int parameterIndex, Object x) to set parameters. Because Object is the common parent of all classes, using this parameter allows code generality to set the placeholder “?” . Int upDate(String SQL,Object… Params). Where SQL is the parameter to be executed including the placeholder “?” SQL statements such as delete from book where id =? Params is an Object mutable array that stores the placeholder “? “in preparedStatement. As many real parameters as there are placeholders, and the order of each placeholder must be the same as the order of its corresponding real parameters.

2. Update the encapsulated implementation of the operation method in the utility class jdbcutil.java

/** * Execute SQL update statement. * @param SQL The SQL statement to execute * @param params preprocessing the actual parameter array in the placeholder * @return returns the number of rows affected */ public static int upDate(String) sql,Object... params){ if (params==null||params.length==0) return 0; Connection Connection = jdbcutil.getConnection (); PreparedStatement preparedStatement=null; int result = 0; Try {/ / create a preprocessing preparedStatement object = connection. The prepareStatement (SQL); // Replace the placeholder for (int I = 0; i < params.length; I++) {/ / parameterIndex starting from 1 preparedStatement. SetObject (I + 1, params [I]); } / / executing SQL statements result = preparedStatement. ExecuteUpdate (); } catch (SQLException e) { e.printStackTrace(); } / / closing resources JDBCUtil. Close (connection, a preparedStatement, null); return result; }Copy the code

Insert,delete,update (String SQL,Object, etc.); params)

package dao; import java.sql.Connection; import java.sql.Date; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import entity.Book; import util.JDBCUtil; /** * @author TODO: BookDao *2020 10月30日 9:04:08 PM */ public class BookDao {/** * @param book to save the object * @return save success returns 1 otherwise returns 0 */ public int SaveByPrepareStatement (Book Book) {// SQL statement to be executed String SQL = "INSERT into book(name,price,status,discount,isBorrowed,createTime) values(? ,? ,? ,? ,? ,?) "; Int result = jdbcutil. upDate(SQL, book.getName(), book.getprice (), book.getStatus(),book.getDiscount(), book.isBorrowed(),book.getCreateTime() ); return result; } / update book * * * * @ param newBook * @ return returns the number of rows affected * / public int updateBookByPreparedStatement (book newBook) { String SQL = "Update book set name=? ,price = ? ,status = ? ,discount = ? ,isBorrowed = ? ,createTime = ? where id = ?" ; Int result = jdbcutil. upDate(SQL, newbook.getName (), newbook.getprice (), newBook.getStatus(),newBook.getDiscount(), newBook.isBorrowed(),newBook.getCreateTime() ,newBook.getId()); return result; } /** * delete book * @param ID Id of the book to be deleted * @return Returns the number of rows affected */ public int deleteBookById(int ID) {// The SQL statement to be deleted is String sql = "delete from book where id = ?" ; Int result = jdbcutil. upDate(SQL, id); int result = jdbcutil. upDate(SQL, id); return result; } public book findBookById(int ID) {public book findBookById(int ID) {public book findBookById(int ID) {public book findBookById(int ID) String sql = "select * from book where id = ?" ; // ResultSet ResultSet = null; Book book = null; Connection Connection = jdbcutil.getConnection (); / / create a preprocessing PreparedStatement object PreparedStatement = connection. PrepareStatement (SQL); Preparedstatement.setint (1, id); preparedStatement.setint (1, id); / / start executing the query, and returns the query result set the resultSet = preparedStatement. ExecuteQuery (); If (resultSet.next()) {// Obtain the name of book. String name = resultSet.getString("name"); Byte status = resultSet.getByte("status"); // Get book's price double price = resultSet.getDouble("price"); // Get book's discount float discount = resultSet.getFloat("discount"); IsBorrowed = resultSet.getBoolean("isBorrowed"); Date = resultSet. GetDate ("createTime"); book = new Book(); book.setBorrowed(isBorrowed); book.setDiscount(discount); book.setId(id); book.setPrice(price); book.setName(name); book.setStatus(status); book.setCreateTime(date); } // Release the resource. Jdbcutil. close(Connection, preparedStatement, resultSet); } catch (SQLException e) { e.printStackTrace(); book = null; } return book; } @return return List<book> */ public List<book> findAllBook() {String SQL = "select * from"  book "; // ResultSet ResultSet = null; List<Book> bookList = null; Connection Connection = jdbcutil.getConnection (); / / create a preprocessing PreparedStatement object PreparedStatement = connection. PrepareStatement (SQL); / / start executing the query, and returns the query result set the resultSet = preparedStatement. ExecuteQuery (); bookList = new ArrayList<>(); While (resultSet.next()) {// Obtain the ID of book int ID = resultSet.getint (" ID "); // Obtain the ID of book int ID = resultSet.getint (" ID "); String name = resultSet.getString("name"); Byte status = resultSet.getByte("status"); // Get book's price double price = resultSet.getDouble("price"); // Get book's discount float discount = resultSet.getFloat("discount"); IsBorrowed = resultSet.getBoolean("isBorrowed"); Date = resultSet. GetDate ("createTime"); Book book = new Book(); book.setBorrowed(isBorrowed); book.setDiscount(discount); book.setId(id); book.setPrice(price); book.setName(name); book.setStatus(status); book.setCreateTime(date); bookList.add(book); } // Release the resource. Jdbcutil. close(Connection, preparedStatement, resultSet); } catch (SQLException e) { e.printStackTrace(); bookList = null; } return bookList; }}Copy the code

4. Use JUnit unit tests

package test; import java.util.Date; import java.util.List; import org.junit.jupiter.api.Test; import dao.BookDao; import entity.Book; class BookDaoTest { @Test void testUpdateBook() { Book newBook = new Book(); newBook.setId(1); Newbook.setname (" Dream of Red Mansions "); newBook.setCreateTime(new Date()); newBook.setBorrowed(Book.isBorrowed_FALSE); NewBook. SetPrice (60.5); newBook.setStatus(Book.STATUS_NORMAL); BookDao bookDao = new BookDao(); int result = bookDao.updateBookByPreparedStatement(newBook); If (result == 1) {system.out.println (" Update book successfully! ); }else {system.out.println (" Update book failed! ") ); } } @Test void testdeleteBookById() { BookDao bookDao = new BookDao(); int result = bookDao.deleteBookById(2); If (result == 1) {system.out.println (" delete book successfully! ); }else {system.out.println (" Delete book failed! ") ); } } @Test void testFindBookById() { BookDao bookDao = new BookDao(); Book book = bookDao.findBookById(3); if(book ! = null) {system.out.println (" find book :"+ book); }else {system.out.println (" This book does not exist in database! ") ); } } @Test void testFindAllBook() { BookDao bookDao = new BookDao(); List<Book> bookList = bookDao.findAllBook(); If (bookList = = null | | bookList. The size () = = 0) {System. Out. Println (" books is empty!" ); }else { for(Book book : bookList) { System.out.println(book); }}}}Copy the code

After the test, the database can be updated normally.

5. The experiment environment

Eclipse219 jdk1.8, mysql8.0

6.. This blog and synchronize to personal blog, and the test source code has been uploaded to the personal server, if necessary (there is a download address at the back of personal blog), please go to:

Moyisuiying.com/index.php/j…