对book表中的数据进行增删查改

数据库表结构为：

Book.java

package com.lye.entity;

public class Book {
	private int id;
	private String bookName;
	private int price;
	private String description;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getBookName() {
		return bookName;
	}
	public void setBookName(String bookName) {
		this.bookName = bookName;
	}
	public int getPrice() {
		return price;
	}
	public void setPrice(int price) {
		this.price = price;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	
	public String toString(){
		return "Book [id=" + id + ", bookName=" + bookName + ", price=" + price + ", description=" + description +"]";
	}
}

JdbcConnection.java

package com.lye.jdbc;

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

import com.lye.entity.Book;

public class JdbcConnection {
	public static void main(String[] args){
		//创建数据库连接
		try{
			Class.forName("com.mysql.jdbc.Driver");
			String url="jdbc:mysql://localhost:3306/onest?useUnicode=true&characterEncoding=UTF-8";
			Connection connection=DriverManager.getConnection(url,"root","123456");
			PreparedStatement ps=connection.prepareStatement("select * from book");
			ResultSet rs=ps.executeQuery();
			Book book=new Book();
			while(rs.next()){
				book.setId(rs.getInt("id"));
				book.setBookName(rs.getString("bookName"));
				book.setPrice(rs.getInt("price"));
				book.setDescription(rs.getString("description"));
				System.out.println(book);
			}
		}catch(ClassNotFoundException e){
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

DBUtil.java

package com.lye.util;

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

/**
 * 
* @ClassName: DBUtil  
* @Description:数据库连接工具类
* @author lye
* @date 2019年10月10日  
*
 */
public class DBUtil {
	//类加载只执行一次
		static{
			try {
				Class.forName("com.mysql.jdbc.Driver");
			} catch (ClassNotFoundException e) {
				
				e.printStackTrace();
			}
		}
		public static Connection getConnection(){
			String sql="jdbc:mysql://localhost:3306/onest?useUnicode=true&characterEncoding=UTF-8";
			Connection connection;
			try {
				connection = DriverManager.getConnection(sql,"root","123456");
				return connection;
			} catch (SQLException e) {
				
				e.printStackTrace();
				return null;
			}
			
		}
		
		public static void close(Connection connectionn){
			try {
				connectionn.close();
				
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}
		
		public static void close(PreparedStatement ps){
			try {
				ps.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
		}
		public static void close(ResultSet rs){
			try {
				rs.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}
}

Test.java

package com.lye.ui;

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

import com.lye.entity.Book;
import com.lye.util.DBUtil;
/**
 * 
* @ClassName: Test  
* @Description:测试类
* @author lye
* @date 2019年10月10日  
*
 */
public class Test {

	public static void main(String[] args) {
		Book book=new Book();
		book.setId(3);
		book.setBookName("JAVASE");
		book.setPrice(44);
		book.setDescription("我爱JavaSE");
		
		addBook(book);
//		updateBook(book);
//		findBookById(1);
//		deleteBookById(1);
		
	}



	/*
	 * 实现添加商品的方法
	 */
	public static void addBook(Book book) {
		Connection connection =DBUtil.getConnection();
		PreparedStatement pstmt;
		try{
			pstmt=connection.prepareStatement("insert into book(id,bookName,price,description) values(?,?,?,?)");
			pstmt.setInt(1, book.getId());
			pstmt.setString(2, book.getBookName());
			pstmt.setInt(3, book.getPrice());
			pstmt.setString(4, book.getDescription());
			int rs=pstmt.executeUpdate();
			System.out.println(rs);//值大于0说明发生了变化，证明修改成功了
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	/*
	 * 实现修改商品的方法
	 */
	public static void updateBook(Book book) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement pstmt;
		try{
			pstmt=connection.prepareStatement("update book set name=?,price=? where id=?");
			pstmt.setInt(1, book.getId());
			pstmt.setString(2, book.getBookName());
			pstmt.setInt(3, book.getPrice());
			pstmt.setString(4, book.getDescription());
			int rs=pstmt.executeUpdate();
			System.out.println(rs);
		}catch(SQLException e){
			e.printStackTrace();
		}
		
	}
	
	/*
	 * 实现查找商品的方法
	 */
	public static Book findBookById(int id) {
		Connection connection=DBUtil.getConnection();
		PreparedStatement pstmt;
		Book book=new Book();
		try{
			pstmt=connection.prepareStatement("select * from book where id = ?");
			pstmt.setInt(1, id);
			ResultSet rs=pstmt.executeQuery();
			while(rs.next()){
				book.setId(rs.getInt("id"));
				book.setBookName(rs.getString("bookName"));
				book.setPrice(rs.getInt("price"));
				book.setDescription(rs.getString("description"));
				System.out.println(book);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		return book;
	}
	
	/*
	 * 实现删除商品的方法
	 */
	public static void deleteBookById(int id) {
		Connection connection=DBUtil.getConnection();
		PreparedStatement pstmt;
		try{
			pstmt=connection.prepareStatement("delete from product where id=?");
			pstmt.setInt(1, id);
			int rs=pstmt.executeUpdate();
			System.out.println(rs);
		}catch(SQLException e){
			e.printStackTrace();
		}
	}


}