【实例】赵雅智_购物车（1）查询所有书籍

使用jsp的model2模型开发购物车（bookstore）

1. 搭建开发环境
   1. 导入开发包：jdbc驱动  （jstl的jar）
   2. 创建程序所在的在mysql下创建库和表 book : id ,name, author price description
2. 编码：
   1. 开发实体类bean ：Book
   2. 开发dao   （创建工具类）
   3. 开发service
   4. web层 

新建WEB项目

考驱动

 

 

创建包

 

 

创建表

创建数据库

创建表

添加数据

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','很好');

 

domin封装实体对象

 

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 + "]";}} 

util连接数据库

配置文件

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());}}

 

Dao包操作

创建dao接口

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(" ");}}}

service业务逻辑处理

创建service接口

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();}}

servlet控制器

 

 

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);}}

jsp显示界面

 在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