else if (method.equals("look")) {
List list = service.getAllCategory();
request.setAttribute("list", list);
request.getRequestDispatcher("/background/lookCategory.jsp").forward(request, response);
}
CREATE TABLE book (
id VARCHAR(40) PRIMARY KEY,
name VARCHAR(10) NOT NULL UNIQUE,
description VARCHAR(255),
author VARCHAR(10),
price FLOAT,
image VARCHAR(100),
category_id VARCHAR(40),
CONSTRAINT category_id_FK FOREIGN KEY (category_id) REFERENCES category (id)
);
編寫DAO
/**
* 圖書模塊
* 1：添加圖書
* 2：查看圖書
* 3：查找圖書的分頁數據【圖書一般來說有很多，所以要分頁】
*/
public class BookDaoImpl {
public void addBook(Book book) {
QueryRunner queryRunner = new QueryRunner(Utils2DB.getDataSource());
String sql = "INSERT INTO book (id,name,description,author,price,image,category_id) VALUES(?,?,?,?,?,?,?)";
try {
queryRunner.update(sql, new Object[]{book.getId(), book.getName(), book.getDescription(), book.getAuthor(), book.getPrice(),book.getImage(), book.getCategory_id()});
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public Book findBook(String id) {
QueryRunner queryRunner = new QueryRunner(Utils2DB.getDataSource());
String sql = "SELECT * FROM book WHERE id=?";
try {
return (Book) queryRunner.query(sql, id, new BeanHandler(Book.class));
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**得到圖書的分頁數據*/
public List getPageData(int start, int end) {
QueryRunner queryRunner = new QueryRunner(Utils2DB.getDataSource());
String sql = "SELECT * FROM book limit ?,?";
try {
return (List) queryRunner.query(sql, new BeanListHandler(Book.class), new Object[]{start, end});
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**得到按照分類圖書的分頁數據*/
public List getPageData(int start, int end,String category_id) {
QueryRunner queryRunner = new QueryRunner(Utils2DB.getDataSource());
//WHERE字句在limit字句的前邊，注意Object[]的參數位置！
String sql = "SELECT * FROM book WHERE category_id=? limit ?,?";
try {
return (List) queryRunner.query(sql, new BeanListHandler(Book.class), new Object[]{ category_id,start, end});
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 得到圖書的總記錄數
*/
public int getTotalRecord() {
QueryRunner queryRunner = new QueryRunner(Utils2DB.getDataSource());
String sql = "SELECT COUNT(*) FROM book";
try {
return (int) queryRunner.query(sql, new ScalarHandler());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 得到分類后圖書的總記錄數
* getCategoryTotalRecord
*/
public long getCategoryTotalRecord(String category_id) {
try {
QueryRunner queryRunner = new QueryRunner(Utils2DB.getDataSource());
String sql = "SELECT COUNT(*) FROM book WHERE category_id=?";
return (long) queryRunner.query(sql, category_id, new ScalarHandler());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
測試DAO
public class BookDemo {
BookDaoImpl bookDao = new BookDaoImpl();
@Test
public void add() {
Book book = new Book();
book.setId("5");
book.setName("SQLServer");
book.setAuthor("我也不知道");
book.setImage("33333332432");
book.setPrice(33.22);
book.setDescription("這是一本好書");
book.setCategory_id("2");
bookDao.addBook(book);
}
@Test
public void look() {
List bookList = bookDao.getPageData(3, 3);
for (Book book : bookList) {
System.out.println(book.getName());
}
List books = bookDao.getPageData(0,2,"2");
for (Book book : books) {
System.out.println(book.getName());
}
}
@Test
public void find() {
String id = "2";
Book book = bookDao.findBook(id);
System.out.println(book.getName());
}
}
抽取成DAO接口
public interface BookDao {
void addBook(Book book);
Book findBook(String id);
List getPageData(int start, int end);
List getPageData(int start, int end, String category_id);
long getTotalRecord();
long getCategoryTotalRecord(String category_id);
}
編寫Service層
/*添加圖書*/
public void addBook(Book book) {
bookDao.addBook(book);
}
/*查找圖書*/
public Book findBook(String id) {
return bookDao.findBook(id);
}
/*查找圖書*/
public Book findBook(String id) {
return bookDao.findBook(id);
}
/*獲取圖書的分頁數據*/
public Page getPageData(String pageNum) {
Page page=null;
if (pageNum == null) {
page = new Page(1, bookDao.getTotalRecord());
} else {
page = new Page(Integer.valueOf(pageNum), bookDao.getTotalRecord());
}
List books = bookDao.getPageData(page.getStartIndex(), page.getLinesize());
page.setList(books);
return page;
}
/*獲取圖書分類后的分頁數據*/
public Page getPageData(String currentPageCount,String category_id) {
Page page=null;
if (currentPageCount == null) {
page = new Page(1, bookDao.getCategoryTotalRecord(category_id));
} else {
page = new Page(Integer.valueOf(currentPageCount), bookDao.getCategoryTotalRecord(category_id));
}
List books = bookDao.getPageData(page.getStartIndex(), page.getLinesize(), category_id);
page.setList(books);
return page;
}
String method = request.getParameter("method");
BussinessServiceImpl service = new BussinessServiceImpl();
if (method.equals("addUI")) {
List list = service.getAllCategory();
request.setAttribute("list", list);
request.getRequestDispatcher("/background/addBook.jsp").forward(request, response);
}
顯示JSP頁面處理表單數據Servlet
else if (method.equals("add")) {
//上傳文件和普通數據分割開，封裝到Book對象上
Book book = uploadData(request);
book.setId(WebUtils.makeId());
service.addBook(book);
request.setAttribute("message", "添加圖書成功");
request.getRequestDispatcher("/message.jsp").forward(request, response);
}
uploadData()方法代碼
private Book uploadData(HttpServletRequest request) {
Book book = new Book();
try{
//1.得到解析器工廠
DiskFileItemFactory factory = new DiskFileItemFactory();
//2.得到解析器
ServletFileUpload upload = new ServletFileUpload(factory);
//設置編碼
upload.setHeaderEncoding("UTF-8");
//為上傳表單，則調用解析器解析上傳數據
List list = upload.parseRequest(request); //FileItem
//遍歷list，得到用于封裝第一個上傳輸入項數據fileItem對象
for(FileItem item : list){
if(item.isFormField()){
//得到的是普通輸入項
String name = item.getFieldName(); //得到輸入項的名稱
String value = item.getString("UTF-8");
//使用BeanUtils封裝數據
BeanUtils.setProperty(book, name, value);
}else{
//得到上傳輸入項
//得到上傳文件名全路徑
String filename = item.getName();
//截取文件名
filename = filename.substring(filename.lastIndexOf("")+1);
InputStream in = item.getInputStream(); //得到上傳數據
int len = 0;
byte buffer[]= new byte[1024];
//如果沒有這個目錄，就創建它
String savepath = this.getServletContext().getRealPath("/image");
File file = new File(savepath);
if (!file.exists()) {
file.mkdir();
}
FileOutputStream out = new FileOutputStream(savepath + "" + filename);
while((len=in.read(buffer))>0){
out.write(buffer, 0, len);
}
//設置圖片的名字
book.setImage(filename);
in.close();
out.close();
//關閉臨時文件
item.delete();
}
}
}catch (Exception e) {
e.printStackTrace();
}
return book;
}
