【MVC设计模式】赵雅智_购物车(1)_查询所有书籍
使用jsp的model2模型开发购物车(bookstore)
create database bookstoreuse bookstorecreate table book(id int not null primary key auto_increment,name varchar(20) unique,author varchar(20),price double,desscription varchar(40));set character_set_client=gb2312;set character_set_results=gb2312;insert into book values(1,'java高级','李丹','32.90','很好');insert into book values(2,'mysql数据库','张三','29.70','很好');insert into book values(3,'c#技术详解','李四','62.40','一般般');insert into book values(4,'Xml技术详解','Jack','45.90','很好');insert into book values(5,'JSP详解','Tom','77.90','很好');insert into book values(6,'Android开发应用','Daney','56.30','很好');
package www.hbsi.domain;public class Book {private int id;private String name;private String author;private double price;private String desscription;public Book() {super();}public Book(int id, String name, String author, double price,String desscription) {super();this.id = id;this.name = name;this.author = author;this.price = price;this.desscription = desscription;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getAuthor() {return author;}public void setAuthor(String author) {this.author = author;}public double getPrice() {return price;}public void setPrice(double price) {this.price = price;}public String getDesscription() {return desscription;}public void setDesscription(String desscription) {this.desscription = desscription;}@Overridepublic String toString() {return "Book [author=" + author + ", desscription=" + desscription+ ", id=" + id + ", name=" + name + ", price=" + price + "]";}}
db.properties
driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/bookstoreusername=rootpassword=123456连接数据库
package www.hbsi.util;import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Properties;public class DBConn {static String driver;static String url;static String username;static String password;static{InputStream in = DBConn.class.getClassLoader().getResourceAsStream("db.properties");Properties pro = new Properties();try {pro.load(in);} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}driver = pro.getProperty("driver");url = pro.getProperty("url");username = pro.getProperty("username");password = pro.getProperty("password");}public static Connection getConnection(){Connection conn = null;try {Class.forName(driver);conn = DriverManager.getConnection(url,username,password); } catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return conn;}public static void close(ResultSet rs, PreparedStatement ps, Connection conn){if(rs != null){try {rs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(ps != null){try {ps.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(conn != null){try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}测试连接
package www.hbsi.test;import org.junit.Test;import www.hbsi.util.DBConn;import junit.framework.TestCase;public class DBConnTest extends TestCase {@Testpublic void testGetConn(){System.out.print(DBConn.getConnection());}}
package www.hbsi.dao;import java.util.List;import www.hbsi.domain.Book;public interface BookDao {//查询所有书籍public List<Book> getAll();//根据ID查找书籍public Book findById(String id);}实现dao接口
package www.hbsi.dao;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 www.hbsi.util.DBConn;import www.hbsi.domain.Book;public class BookDaoImpl implements BookDao {Connection conn = null;PreparedStatement pstmt = null;ResultSet rs = null;Book book;public Book findById(String id) {return book;}public List<Book> getAll() {try {conn = DBConn.getConnection();String sql = "select id,name,author,price,desscription from book";pstmt = conn.prepareStatement(sql);rs = pstmt.executeQuery();List<Book> list = new ArrayList<Book>();while(rs.next()){book = new Book();book.setId(rs.getInt("id"));book.setName(rs.getString("name"));book.setAuthor(rs.getString("author"));book.setPrice(rs.getDouble("price"));book.setDesscription(rs.getString("desscription"));list.add(book);}return list;} catch (Exception e) {throw new RuntimeException(e);} finally {DBConn.close(rs, pstmt, conn);}}}测试
package www.hbsi.test;import java.util.List;import org.junit.Test;import www.hbsi.dao.BookDao;import www.hbsi.dao.BookDaoImpl;import www.hbsi.domain.Book;public class DaoTest {BookDao bd = new BookDaoImpl();@Testpublic void testQueryAll() {List<Book> list = bd.getAll();System.out.println("查询内容是:");for (Book book : list) {System.out.println("id:" + book.getId());System.out.println("书名:" + book.getName());System.out.println("作者:" + book.getAuthor());System.out.println("价格:" + book.getPrice());System.out.println("评价:" + book.getDesscription());System.out.println(" ");}}}
package www.hbsi.service;import java.util.List;import www.hbsi.domain.Book;public interface BookService {//查询所有书籍public List<Book> getAll();//根据ID查找书籍public Book findById(String id);}
package www.hbsi.service;import java.util.List;import www.hbsi.dao.BookDao;import www.hbsi.dao.BookDaoImpl;import www.hbsi.domain.Book;public class BookServiceImpl implements BookService {BookDao bd = new BookDaoImpl();public Book findById(String id) {// TODO Auto-generated method stubreturn bd.findById(id);}public List<Book> getAll() {// TODO Auto-generated method stubreturn bd.getAll();}}
package www.hbsi.servlet;import java.io.IOException;import java.io.PrintWriter;import java.util.List;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import www.hbsi.domain.Book;import www.hbsi.service.BookService;import www.hbsi.service.BookServiceImpl;public class GetAllServlet extends HttpServlet {public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException { BookService service = new BookServiceImpl(); List<Book> list = service.getAll(); request.setAttribute("books",list); request.getRequestDispatcher("/WEB-INF/jsp/listbook.jsp").forward(request, response); }public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {doGet(request, response);}}
在WEB-INF下新建文件夹jsp
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html> <head> <title>显示所有商品</title> </head> <body style="text-align:center"> <h2>小书店</h2> <table border="1" width="80%"> <tr> <td>编号</td> <td>书名</td> <td>作者</td> <td>价格</td> <td>描述</td> <td>操作</td> </tr> <c:forEach var="book" items="${books}"> <tr> <td>${book.id}</td> <td>${book.name}</td> <td>${book.author}</td> <td>${book.price}</td> <td>${book.desscription}</td> <td> <a href="${pageContext.request.contextPath}/getAll.do?id=${book.id}">购买</a> </td> </tr> </c:forEach> </table> </body></html>
程序运行如下:
在地址栏输入http://localhost:8080/bookstore/getAll.do