练习题

 

1.使用自己的话表述MVC

M  （model）模型  是用来收集、存储用户的反馈活动的。
V （view）视图 就是类似于浏览器的版面设计，用来给用户观看的。
C  （controller） 是用来接收用户给出的反馈活动，并将用户的反馈活动进一步反馈，将用户反馈的活动，以一种形式反馈给用户。

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

建立一个Book类

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

}

创建连接池并连接数据库

package com.mmm.entity;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
public class DbcpConnection {
	//声明一个DataSource对象
	private static DataSource ds=null;
	//类加载只执行一次
	static{
		try {
			//加载配置文件并读取
			Properties p=new Properties();
			FileInputStream in=new FileInputStream("dbcp.properties");
			p.load(in);
			ds=BasicDataSourceFactory.createDataSource(p);
		} catch (Exception e) {
			
			e.printStackTrace();
		}
	}
	/*
	 * 获取连接对象方法
	 */
	public static Connection getConnection(){
		try {
			
			return ds.getConnection();
		} catch (SQLException e) {
			
			return null;
		}
		
	}
	
	public static void close(Connection conn){
		try {
			conn.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();
		}
		
	}


}

 创建一个测试类，测试程序

package com.mmm.ui;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mmm.entity.Book;
import com.mmm.entity.DbcpConnection;
public class Test {
public static void main(String[] args){
	 Book book = new Book();
	 book.setId(19);
	 book.setBookName("计算机网络");
	 book.setDescription("beautiful");
	 book.setPrice(22);
	 // saveBook(book);
	 //deleteBookById(7);
	 //updateBook(book);
	// findBookById(4);
	 //findBookBybookName("book3");
	 findBookByPrice(10);
 }
/*
  * 实现添加书本的方法
  */
public static void saveBook(Book book) {
	 Connection conn = DbcpConnection.getConnection();
     PreparedStatement pstmt;
     try {
    	 for(int i=1;i<21;i++){
			 pstmt = conn.prepareStatement("insert into book(id,bookName,price,description) values(?,?,?,?)");
			 pstmt.setInt(1,i);// book.getId()
			 pstmt.setString(2,"book"+Integer.toString(i));
			 pstmt.setInt(3, 2*i);
			 pstmt.setString(4,"ISN-"+Integer.toString(i));
			 pstmt.execute();
    	 }
    	 
     } catch (SQLException e) {
         
         e.printStackTrace();
     }
     
	
}
/*
 * 实现删除书本的方法(根据id)
 */
public static void deleteBookById(int id) {
	Connection conn = DbcpConnection.getConnection();
    PreparedStatement pstmt;
    try {
        pstmt = conn.prepareStatement("delete from book where id=?");
        pstmt.setInt(1, id);
        int rs = pstmt.executeUpdate();
        System.out.println(rs);
    } catch (SQLException e) {
        
        e.printStackTrace();
    }
	
}
/*
 * 实现修改书本的方法(根据id)
 */
public static void updateBook(Book book){
    Connection conn = DbcpConnection.getConnection();
    PreparedStatement pstmt;
    try {
        pstmt = conn.prepareStatement("update book set bookName=?,price=?,description=? where id=?");
        pstmt.setString(1, book.getBookName());
        pstmt.setInt(2, book.getPrice());
        pstmt.setString(3,book.getDescription());
        pstmt.setInt(4, book.getId());

        int rs = pstmt.executeUpdate();
        System.out.println(rs);
    } catch (SQLException e) {
        
        e.printStackTrace();
    }
}
/*
 * 实现根据id查询商品的方法
 */
public static Book findBookById(Integer id){
    Connection conn = DbcpConnection.getConnection();
    PreparedStatement pstmt;
    Book book = new Book();
    try {
        pstmt = conn.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 Book findBookBybookName(String bookName){
    Connection conn = DbcpConnection.getConnection();
    PreparedStatement pstmt;
    Book book = new Book();
    try {
        pstmt = conn.prepareStatement("select * from book where bookName = ?");
        pstmt.setString(1, bookName);
        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 List<Book> findBookByPrice(int price){
    Connection conn = DbcpConnection.getConnection();
    PreparedStatement pstmt;
    List<Book> list = new ArrayList<Book>();
    Book book = new Book();
    try {
        pstmt = conn.prepareStatement("select * from book where price = ?");
        pstmt.setInt(1, price);
        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"));
            list.add(book);
            System.out.println(book);
        }
    } catch (SQLException e) {
        
        e.printStackTrace();
    }

    return list;
}

}