目的:利用该项目的开发,采用MVC模式整合JSP + Servlet + DB(DAO),了解Web开发的一般流程。
一、创建simonshop数据库
/*
Navicat MySQL Data Transfer
Source Server : hwsql
Source Server Version : 50518
Source Host : localhost:3306
Source Database : simonshop
Target Server Type : MYSQL
Target Server Version : 50518
File Encoding : 65001
Date: 2017-02-16 09:21:41
*/
SET FOREIGN_KEY_CHECKS=0;
– Table structure for t_category
DROP TABLE IF EXISTS t_category
;
CREATE TABLE t_category
(
id
int(11) NOT NULL AUTO_INCREMENT COMMENT ‘商品类别标识符’,
name
varchar(100) NOT NULL COMMENT ‘商品类别名称’,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
– Records of t_category
INSERT INTO t_category
VALUES (‘1’, ‘家用电器’);
INSERT INTO t_category
VALUES (‘2’, ‘床上用品’);
INSERT INTO t_category
VALUES (‘3’, ‘文具用品’);
INSERT INTO t_category
VALUES (‘4’, ‘休闲食品’);
– Table structure for t_order
DROP TABLE IF EXISTS t_order
;
CREATE TABLE t_order
(
id
int(11) NOT NULL AUTO_INCREMENT COMMENT ‘订单标识符’,
username
varchar(20) DEFAULT NULL COMMENT ‘用户名’,
telephone
varchar(11) DEFAULT NULL COMMENT ‘电话号码’,
total_price
double DEFAULT NULL COMMENT ‘总金额’,
delivery_address
varchar(50) DEFAULT NULL COMMENT ‘送货地址’,
order_time
timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ‘下单时间’,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
– Records of t_order
INSERT INTO t_order
VALUES (‘1’, ‘郑晓红’, ‘13956567889’, ‘2000’, ‘泸职院信息工程系’, ‘2016-12-25 17:12:36’);
INSERT INTO t_order
VALUES (‘2’, ‘温志军’, ‘13956678907’, ‘1000’, ‘泸职院机械工程系’, ‘2016-12-02 17:12:17’);
– Table structure for t_product
DROP TABLE IF EXISTS t_product
;
CREATE TABLE t_product
(
id
int(11) NOT NULL AUTO_INCREMENT COMMENT ‘商品标识符’,
name
varchar(200) NOT NULL COMMENT ‘商品名称’,
price
double DEFAULT NULL COMMENT ‘商品单价’,
add_time
timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
category_id
int(11) DEFAULT NULL COMMENT ‘商品类别标识符’,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
– Records of t_product
INSERT INTO t_product
VALUES (‘1’, ‘容声电冰箱’, ‘2000’, ‘2016-12-20 09:54:41’, ‘1’);
INSERT INTO t_product
VALUES (‘2’, ‘松下电视’, ‘5000’, ‘2016-12-20 09:54:35’, ‘1’);
INSERT INTO t_product
VALUES (‘3’, ‘红岩墨水’, ‘3’, ‘2016-12-20 09:56:05’, ‘3’);
INSERT INTO t_product
VALUES (‘4’, ‘海尔洗衣机’, ‘1000’, ‘2016-11-30 08:58:09’, ‘1’);
INSERT INTO t_product
VALUES (‘5’, ‘新宇电饭煲’, ‘1200’, ‘2016-12-20 09:55:11’, ‘1’);
INSERT INTO t_product
VALUES (‘6’, ‘英雄微波炉’, ‘600’, ‘2016-12-20 09:55:39’, ‘1’);
INSERT INTO t_product
VALUES (‘7’, ‘红双喜席梦思’, ‘700’, ‘2016-11-28 08:59:38’, ‘2’);
INSERT INTO t_product
VALUES (‘8’, ‘旺仔牛奶糖’, ‘24.4’, ‘2016-12-20 10:00:11’, ‘4’);
INSERT INTO t_product
VALUES (‘9’, ‘西蒙枕头’, ‘100’, ‘2016-12-20 09:56:57’, ‘2’);
INSERT INTO t_product
VALUES (‘10’, ‘甜甜毛毯’, ‘400’, ‘2016-12-20 09:57:26’, ‘2’);
INSERT INTO t_product
VALUES (‘11’, ‘永久钢笔’, ‘50’, ‘2016-12-20 09:57:30’, ‘3’);
INSERT INTO t_product
VALUES (‘12’, ‘硬面抄笔记本’, ‘5’, ‘2016-12-20 09:57:53’, ‘3’);
INSERT INTO t_product
VALUES (‘13’, ‘晨光橡皮擦’, ‘0.5’, ‘2016-11-30 09:02:40’, ‘3’);
INSERT INTO t_product
VALUES (‘14’, ‘美的空调’, ‘3000’, ‘2016-11-03 09:03:02’, ‘1’);
INSERT INTO t_product
VALUES (‘15’, ‘迷你深海鱼肠’, ‘14.4’, ‘2016-12-02 10:01:14’, ‘4’);
– Table structure for t_user
DROP TABLE IF EXISTS t_user
;
CREATE TABLE t_user
(
id
int(11) NOT NULL AUTO_INCREMENT,
username
varchar(20) NOT NULL,
password
varchar(20) DEFAULT NULL,
telephone
varchar(11) DEFAULT NULL,
register_time
timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
popedom
int(11) DEFAULT NULL COMMENT ‘0:管理员;1:普通用户’,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
– Records of t_user
INSERT INTO t_user
VALUES (‘1’, ‘admin’, ‘12345’, ‘15734345678’, ‘2016-12-02 08:40:35’, ‘0’);
INSERT INTO t_user
VALUES (‘2’, ‘郑晓红’, ‘11111’, ‘13956567889’, ‘2016-12-20 09:51:43’, ‘1’);
INSERT INTO t_user
VALUES (‘3’, ‘温志军’, ‘22222’, ‘13956678907’, ‘2016-12-20 09:52:36’, ‘1’);
INSERT INTO t_user
VALUES (‘4’, ‘涂文艳’, ‘33333’, ‘15890905678’, ‘2016-12-05 09:52:56’, ‘1’);
二、创建实体类(User、Category、Product与Order)、建立数据库连接(在web\WEB-INF目录下创建lib子目录,添加MySQL驱动程序的jar包)、数据访问接口(UserDao、CategoryDao、ProductDao与OrderDao)、数据访问接口实现类(UserDaoImpl、CategoryDaoImpl、ProductDaoImpl与OrderDaoImpl)
三、dao测试类
1.TestUserDaoImpl
package net.hw.shop.dao.impl;
import net.hw.shop.bean.User;
import net.hw.shop.dao.UserDao;
import net.hw.shop.dbutil.ConnectionManager;
import org.junit.Test;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.List;
public class TestUserDaoImpl {
/**
* 用户登录
*/
@Test
public void testLogin(){
String username,password;
username="admin";
password="12345";
UserDao userDao=new UserDaoImpl();
User user=userDao.login(username,password);
if (user!=null){
System.out.println("恭喜,登录成功");
}else {
System.out.println("遗憾,登录失败");
}
}
/**
* 更新用户
*/
@Test
public void testUpdate(){
UserDao userDao=new UserDaoImpl();
User user=userDao.findById(4);
user.setPassword("903213");
user.setTelephone("18026351234");
userDao.update(user);
int count=userDao.update(user);
if (count>0){
System.out.println("恭喜,更新成功");
}else {
System.out.println("遗憾,更新失败");
}
}
/**
* 删除用户
*/
@Test
public void testDelete() {
UserDao userDao=new UserDaoImpl();
User user=userDao.findById(2);
user.setId(2);
userDao.deleteById(2);
int count=userDao.deleteById(2);
if (count>0){
System.out.println("恭喜,删除成功");
}else {
System.out.println("遗憾,删除失败");
}
}
/**
* 插入用户
*/
@Test
public void testInsert() {
//创建用户数据访问对象
User user = new User();
user.setUsername("哈哈哈");
user.setId(9);
user.setTelephone("18086902766");
user.setPassword("123123");
user.setRegisterTime(Timestamp.valueOf(LocalDateTime.now()));
user.setPopedom(1);
UserDao dao = new UserDaoImpl();
int count = dao.insert(user);
if (count > 0) {
System.out.println("恭喜,学生记录插入成功!");
} else {
System.out.println("遗憾,学生记录插入失败!");
}
}
/**
* 按姓名查询用户
*/
@Test
public void testFindByUsername() {
UserDao dao = new UserDaoImpl();
String username;
List<User> students = dao.findByUsername("哈哈哈");
for (User student : students) {
System.out.println(student);
}
}
/**
* 查询全部用户
*/
@Test
public void testFindAll() {
UserDao dao = new UserDaoImpl();
String all;
List<User> students = dao.findAll();
for (User student : students) {
System.out.println(student);
}
}
}
2.TestProductDaoImpl
package net.hw.shop.dao.impl;
import net.hw.shop.bean.Product;
import net.hw.shop.bean.User;
import net.hw.shop.dao.CategoryDao;
import net.hw.shop.dao.ProductDao;
import net.hw.shop.dao.UserDao;
import org.junit.Test;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.List;
public class TestProductDaoImpl {
/**
* 插入商品
*/
@Test
public void testInsert() {
Product product = new Product();
product.setId(1);
product.setName("花花公子钢笔");
product.setPrice(78);
product.setAddTime(Timestamp.valueOf(LocalDateTime.now()));
product.setCategoryId(3);
ProductDao dao=new ProductDaoImpl();
int count = dao.insert(product);
if (count > 0) {
System.out.println("恭喜,商品记录插入成功!");
} else {
System.out.println("遗憾,商品记录插入失败!");
}
}
/**
* 按照类别查询
*/
@Test
public void testFindCategoryId(){
ProductDao productDao=new ProductDaoImpl();
int catagoryId=2;
CategoryDao categoryDao=new CategoryDaoImpl();
if (categoryDao.findById(catagoryId)!=null){
String categoryName=categoryDao.findById(catagoryId).getName();
List<Product> products=productDao.findByCategoryId(catagoryId);
if (!products.isEmpty()){
for (Product product:products){
System.out.println(product);
}
}else {
System.out.println("["+categoryName+"]类别没有商品");
}
}else {
System.out.println("类别编号["+catagoryId+"]不存在");
}
}
/**
* 更新商品
*/
@Test
public void testUpdate(){
ProductDao productDao=new ProductDaoImpl();
Product product=productDao.findById(11);
product.setName("英雄钢笔");
product.setPrice(25);
productDao.update(product);
int count=productDao.update(product);
if (count>0){
System.out.println("恭喜,更新成功");
}else {
System.out.println("遗憾,更新失败");
}
}
@Test
/**
* 按标识符删除商品
*/
public void testDeleteById(){
ProductDao productDao = new ProductDaoImpl();
Product product = productDao.findById(3);
product.setId(3);
productDao.deleteById(3);
int count = productDao.deleteById(3);
if (count > 0) {
System.out.println("遗憾,删除失败!");
} else {
System.out.println("恭喜,删除成功!");
}
}
@Test
/**
* 查询全部商品
*/
public void testFindAll() {
ProductDao dao = new ProductDaoImpl();
String all;
List<Product> products = dao.findAll();
for (Product product : products) {
System.out.println(product);
}
}
@Test
/**
* 按标识符查询商品
*/
public void testFindById(){
ProductDao dao = new ProductDaoImpl();
Product product = dao.findById(8);
System.out.println(product);
}
}
3.TestOrderDaoImpl
package net.hw.shop.dao.impl;
import net.hw.shop.bean.Order;
import net.hw.shop.bean.Product;
import net.hw.shop.dao.OrderDao;
import net.hw.shop.dao.ProductDao;
import org.junit.Test;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.List;
public class TestOrderDaoImpl {
/**
* 显示所有订单
*/
@Test
public void testFindAll(){
OrderDao orderDao=new OrderDaoImpl();
List<Order> orders=orderDao.findAll();
if (orders.size()>0){
for (Order order:orders){
System.out.println(order);
}
}else {
System.out.println("没有订单");
}
}
/**
* 按照标识符删除订单
*/
@Test
public void testDeleteById(){
OrderDao orderDao = new OrderDaoImpl();
Order order = orderDao.findById(1);
order.setId(1);
orderDao.deleteById(1);
int count =orderDao.deleteById(1);
if (count > 0) {
System.out.println("遗憾,删除失败!");
} else {
System.out.println("恭喜,删除成功!");
}
}
/**
* 更新订单
*/
@Test
public void testUpdate(){
OrderDao orderDao = new OrderDaoImpl();
Order order = orderDao.findById(1);
order.setUsername("文华");
order.setTelephone("17234823621");
order.setTotalPrice(1000000);
order.setDeliveryAddress("西南医科大护理");
orderDao.update(order);
int count=orderDao.update(order);
if (count>0){
System.out.println("恭喜,更新成功");
}else {
System.out.println("遗憾,更新失败");
}
}
/**
* 按标识符查询订单
*/
@Test
public void testFindById(){
OrderDao dao = new OrderDaoImpl();
Order order = dao.findById(1);
System.out.println(order);
}
@Test
/**
* 插入订单
*/
public void testInsert() {
Order order = new Order();
order.setId(3);
order.setUsername("王欢");
order.setTelephone("18087902756");
order.setTotalPrice(1000);
order.setDeliveryAddress("泸职院信息工程系");
order.setOrderTime(Timestamp.valueOf(LocalDateTime.now()));
OrderDao dao=new OrderDaoImpl();
int count = dao.insert(order);
if (count > 0) {
System.out.println("恭喜,订单记录插入成功!");
} else {
System.out.println("遗憾,订单记录插入失败!");
}
}
@Test
/**
* 查询最后一个订单
*/
public void testFindLast() {
OrderDao orderDao = new OrderDaoImpl();
Order order = orderDao.findLast();
System.out.println(order);
if(order !=null){
System.out.println("恭喜,最后一条查询成功!");
}else {
System.out.println("遗憾,最后一条查询失败!");
}
}
}
4.TestCategoryDaoImpl
package net.hw.shop.dao.impl;
import net.hw.shop.bean.Category;
import net.hw.shop.dao.CategoryDao;
import org.junit.Test;
import java.util.List;
public class TestCategoryDaoImpl {
/**
* 按标识符查询类别
*/
@Test
public void testFindById(){
CategoryDao dao = new CategoryDaoImpl();
Category category = dao.findById(1);
System.out.println(category);
}
/**
* 插入类别
*/
@Test
public void testInsert(){
Category category=new Category();
category.setName("水果海鲜");
CategoryDao dao=new CategoryDaoImpl();
int count = dao.insert(category);
if (count > 0) {
System.out.println("恭喜,商品类别插入成功!");
} else {
System.out.println("遗憾,商品类别插入失败!");
}
}
/**
* 查询全部用户
*/
@Test
public void testFindAll(){
CategoryDao categoryDao=new CategoryDaoImpl();
List<Category> categories=categoryDao.findAll();
if (categories.size()>0){
for (Category category:categories) {
System.out.println(category);
}
}else{
System.out.println("没有商品类别");
}
}
/**
* 更新类别
*/
@Test
public void testUpdate(){
CategoryDao categoryDao=new CategoryDaoImpl();
Category category=categoryDao.findById(1);
category.setName("商用电器");
categoryDao.update(category);
int count=categoryDao.update(category);
if (count>0){
System.out.println("恭喜,更新成功");
}else {
System.out.println("遗憾,更新失败");
}
}
@Test
/**
* 按照标识符删除订单
*/
public void testDeleteById() {
CategoryDao categoryDao = new CategoryDaoImpl();
Category category = categoryDao.findById(1);
category.setId(1);
categoryDao.deleteById(1);
int count = categoryDao.deleteById(1);
if (count > 0) {
System.out.println("遗憾,删除失败!");
} else {
System.out.println("恭喜,删除成功!");
}
}
}
四、数据访问服务类(UserService、CategoryService、ProductService与OrderService)
五、service测试类
1.TestUserService
package net.hw.shop.service;
import net.hw.shop.bean.User;
import org.junit.Test;
import org.omg.PortableServer.SERVANT_RETENTION_POLICY_ID;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.List;
public class TestUserService {
@Test
public void testAddUser(){
User user=new User();
user.setUsername("圈圈");
user.setPassword("172512");
user.setTelephone("18236273821");
user.setPopedom(1);
user.setRegisterTime(Timestamp.valueOf(LocalDateTime.now()));
UserService service=new UserService();
int count=service.addUser(user);
if (count>0){
System.out.println("恭喜,添加信息成功");
}else {
System.out.println("遗憾,添加信息失败");
}
}
@Test
public void testFindUserById(){
UserService service=new UserService();
User user=service.findUserById(1);
System.out.println(user);
}
@Test
public void testDeleteUserById(){
UserService service=new UserService();
int id=1;
int count=service.deleteUserById(id);
if (count>0){
System.out.println("恭喜,删除成功");
}else {
System.out.println("遗憾,删除失败");
}
}
@Test
public void testUpdateUser(){
UserService service=new UserService();
User user=service.findUserById(1);
user.setUsername("AAAAA");
user.setPassword("222222");
int count=service.updateUser(user);
if (count>0){
System.out.println("恭喜,更新记录成功");
user=service.findUserById(1);
System.out.println(user);
}else {
System.out.println("遗憾,记录更新失败");
}
}
@Test
public void testFindUserByName(){
UserService service=new UserService();
String username="涂文艳";
List<User> users=service.findUsersByUsername(username);
for (User user:users){
System.out.println(user);
}
}
@Test
public void testFindAllUsers(){
UserService service=new UserService();
List<User> users=service.findAllUsers();
for (User user:users){
System.out.println(user);
}
}
@Test
public void testLogin(){
UserService service=new UserService();
String username,password;
username="admin";
password="12345";
User user=service.login(username,password);
if (user!=null){
System.out.println("恭喜,登录成功");
}else {
System.out.println("遗憾,登录失败");
}
}
}
2.TestProductService
package net.hw.shop.service;
import net.hw.shop.bean.Product;
import org.junit.Test;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.List;
public class TestProductService {
@Test
/**
* 插入用户信息
*/
public void testAddProduct() {
Product product = new Product();
product.setId(16);
product.setName("围巾");
product.setAddTime(Timestamp.valueOf(LocalDateTime.now()));
product.setCategoryId(3);
ProductService service = new ProductService();
int count = service.addProduct(product);
if (count > 0) {
System.out.println("恭喜,商品记录插入成功!");
} else {
System.out.println("遗憾,商品记录插入失败!");
}
}
@Test
/**
* 通过商品id删除商品信息
*/
public void testDeleteProductById() {
ProductService service = new ProductService();
int count = service.deleteProductById(12);
if (count > 0) {
System.out.println("恭喜,商品记录删除成功!");
} else {
System.out.println("遗憾,商品记录删除失败!");
}
}
@Test
/**
* 更新商品信息
*/
public void testUpdateProductById() {
ProductService service = new ProductService();
Product product = service.findProductById(10);
int count = service.updateProduct(product);
if (count > 0){
System.out.println("恭喜,商品记录更新成功!");
}else{
System.out.println("遗憾,商品记录更新失败!");
}
}
@Test
/**
* 通过商品id查询商品信息
*/
public void testFindProductById() {
ProductService service = new ProductService();
Product product = service.findProductById(10);
System.out.println(product);
}
@Test
public void testFindProductsByCategoryId(){
ProductService service = new ProductService();
List<Product> products = service.findProductsByCategoryId(4);
for (Product product:products){
System.out.println(product);
}
}
@Test
/**
* 查找所有产品信息
*/
public void testFindAllProducts(){
ProductService service = new ProductService();
List<Product> products = service.findAllProducts();
for (Product product:products){
System.out.println(product);
}
}
}
3.TestOrderService
package net.hw.shop.service;
import net.hw.shop.bean.Order;
import org.junit.Test;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.List;
public class TestOrderService {
@Test
public void testAddOrder(){
Order order=new Order();
order.setUsername("汪志荟");
order.setTelephone("19023428901");
order.setDeliveryAddress("泸职院英语教育");
order.setTotalPrice(1200);
order.setOrderTime(Timestamp.valueOf(LocalDateTime.now()));
OrderService service=new OrderService();
int count=service.addOrder(order);
if (count>0){
System.out.println("恭喜,添加信息成功");
}else {
System.out.println("遗憾,添加信息失败");
}
}
@Test
public void testDeleteOrderById(){
OrderService service=new OrderService();
int id=1;
int count=service.deleteOrderById(3);
if (count>0){
System.out.println("恭喜,删除成功");
}else {
System.out.println("遗憾,删除失败");
}
}
@Test
public void testUpdateOrder(){
OrderService service=new OrderService();
Order order=service.findOrderById(1);
order.setUsername("AAAAA");
order.setTelephone("18923472839");
order.setOrderTime(Timestamp.valueOf(LocalDateTime.now()));
order.setDeliveryAddress("泸职院英语教育");
int count=service.updateOrder(order);
if (count>0){
System.out.println("恭喜,更新记录成功");
order=service.findOrderById(1);
System.out.println(order);
}else {
System.out.println("遗憾,记录更新失败");
}
}
@Test
public void testFindAllOrders(){
OrderService service=new OrderService();
List<Order> orders=service.findAllOrders();
for (Order order:orders){
System.out.println(order);
}
}
@Test
public void testFindLastOrders(){
OrderService service=new OrderService();
Order order=service.findLastOrder();
System.out.println(order);
if(order !=null){
System.out.println("恭喜,最后一条查询成功!");
}else {
System.out.println("遗憾,最后一条查询失败!");
}
}
}
4.TestCategoryService
package net.hw.shop.service;
import net.hw.shop.bean.Category;
import org.junit.Test;
import java.util.List;
public class TestCategoryService {
@Test
public void testAddCategory(){
Category category=new Category();
category.setName("水果海鲜");
CategoryService service=new CategoryService();
int count=service.addCategory(category);
if (count>0){
System.out.println("恭喜,添加信息成功");
}else {
System.out.println("遗憾,添加信息失败");
}
}
@Test
public void testDeletCategory(){
CategoryService service=new CategoryService();
int id=5;
int count=service.deleteCategoryById(5);
if (count>0){
System.out.println("恭喜,删除成功");
}else {
System.out.println("遗憾,删除失败");
}
}
@Test
public void testUpdateCategory(){
CategoryService service=new CategoryService();
Category category=service.findCategoryById(1);
category.setName("商用电器");
int count=service.updateCategory(category);
if (count>0){
System.out.println("恭喜,更新记录成功");
category=service.findCategoryById(1);
System.out.println(category);
}else {
System.out.println("遗憾,记录更新失败");
}
}
@Test
public void testFindAllCategories(){
CategoryService service=new CategoryService();
List<Category> categories=service.findAllCategories();
for (Category category:categories){
System.out.println(category);
}
}
}