MVC以及JDBC的理解和应用

使用自己的话表述MVC

C即controller控制器主要负责人机交互，调用业务逻辑。
M即model模型进行业务逻辑判断，对数据库进行增删查改。
V即View视图负责将处理结果直观的显示给用户。
三者间的相互关系如下：

掌握JDBC的基本操作

对book表中的数据进行增删改查；
book表：id int，bookName varchar,price int,description varchar
Book类：id int，bookName String ，price int，description String
提示：创建数据库连接工具类DBUtil类。
book表

Book类

public class Book {
 public int id;
 public String bookName;
 public int price;
 public 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;
 }
 @Override
 public String toString() {
  return "Book [id=" + id + ", bookName=" + bookName + ", price=" + price + ", description=" + description + "]";
 }
}

DBUtil类

public class DBUtil {
 static{
  try {
   //加载数据库驱动程序
   Class.forName("com.mysql.jdbc.Driver");
  } catch (ClassNotFoundException e) {
   
   e.printStackTrace();
  }
 }
 public static Connection getConnection(){
  //创建数据库连接
  String sql="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8";
  Connection conn;
  try {
   conn = DriverManager.getConnection(sql,"root","123456");
   return conn;
  } catch (SQLException e) {
   
   e.printStackTrace();
   return null;
  }
  
 }
 //关闭数据库连接
 public static void close(Connection conn){
  try {
   conn.close();
   
  } catch (SQLException e) {
   
   e.printStackTrace();
  }
 }
 //关闭sql执行语句
 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类

public class Test {
 public static void main(String[] args) {
  Book book=new Book();
  book.setId(5);
  book.setBookName("阿弥陀佛么么哒");
  book.setPrice(30);
  book.setDescription("大冰");
//  addBook(book);
//  deleteBookById(5);
//  updateBook(book);
//  searchBookById(5);
//  searchBookByBookName("小");
//  searchBookByPrice(20,40);
//  searchBookByDescription("b");
  
 }
 //添加图书的方法
 public static void addBook(Book book){
  Connection connection=DBUtil.getConnection();
  PreparedStatement pStatement;
  try {
   pStatement=connection.prepareStatement("insert into book(id,bookName,price,description) values(?,?,?,?)");
   pStatement.setInt(1,book.getId());
   pStatement.setString(2,book.getBookName());
   pStatement.setInt(3,book.getPrice());
   pStatement.setString(4,book.getDescription());
   int rs=pStatement.executeUpdate();
   System.out.println(rs);
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
 //删除图书的方法（根据id）
 public static void deleteBookById(int id){
  Connection connection=DBUtil.getConnection();
  PreparedStatement pStatement;
  try {
   pStatement=connection.prepareStatement("delete from book where id=?");
   pStatement.setInt(1,id);
   int rs=pStatement.executeUpdate();
   System.out.println(rs);
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
 //修改图书的方法（根据id）
 public static void updateBook(Book book){
  Connection connection=DBUtil.getConnection();
  PreparedStatement pStatement;
  try {
   pStatement=connection.prepareStatement("update book set bookName=?,price=?,description=? where id=?");
   pStatement.setString(1,book.getBookName());
   pStatement.setInt(2,book.getPrice());
   pStatement.setString(3,book.getDescription());
   pStatement.setInt(4,book.getId());
   int rs=pStatement.executeUpdate();
   System.out.println(rs);
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
 //根据id查询图书
 public static void searchBookById(int id){
  Connection connection=DBUtil.getConnection();
  PreparedStatement pStatement;
  Book book=new Book();
  try {
   pStatement=connection.prepareStatement("select * from book where id=?");
   pStatement.setInt(1,id);
   ResultSet rs=pStatement.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();
  }
 }
 //根据书名查询图书(模糊搜索)
 public static void searchBookByBookName(String bookName){
  Connection connection=DBUtil.getConnection();
  PreparedStatement pStatement;
  Book book=new Book();
  try {
   pStatement=connection.prepareStatement("select * from book where bookName like ?");
   pStatement.setString(1,"%"+bookName+"%");
   ResultSet rs=pStatement.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();
  }
 }
 //根据价格查询图书(价格区间）
 public static void searchBookByPrice(int minPrice,int maxPrice){
  Connection connection=DBUtil.getConnection();
  PreparedStatement pStatement;
  Book book=new Book();
  try {
   pStatement=connection.prepareStatement("select * from book where price between ? and ?");
   pStatement.setInt(1,minPrice);
   pStatement.setInt(2,maxPrice);
   ResultSet rs=pStatement.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();
  }
 }
 //根据描述查询图书（模糊搜索）
 public static void searchBookByDescription(String description){
  Connection connection=DBUtil.getConnection();
  PreparedStatement pStatement;
  Book book=new Book();
  try {
   pStatement=connection.prepareStatement("select * from book where description like ?");
   pStatement.setString(1,"%"+description+"%");
   ResultSet rs=pStatement.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();
  }
 }
}