JavaSE学习总结（十九）—— Java Web 综合应用（JSP、Servlet、IDEA、MySQL、JUnit、AJAX、JSON）

一、使用Servlet+JDBC+MySQL+IDEA实现商品管理

1.1、创建项目

打开IntelliJ IDEA开发工具、点击Create New Project创建一个新的Web项目

 

选择Java Enterprice（Java企业级开发）、选择项目使用的JDK、Java EE版本、如果没有Tomcat则需要配置，请看我的另一篇博客：

《IDEA整合Tomcat与操作技巧》、选择Web应用、点击下一步Next

输入项目名称与项目路径、注意尽量避免中文路径

点击Finish完成后的结果如下图所示：

如果对Tomcat有特别的要求则需要进一步配置，可以参考tomcat配置一文，链接在上面已给出。

1.2、添加依赖

项目中需要依赖许多的包，包含：JSTL、MySQL驱动、JUnit等

Apache Tomcat JSTL 获取：

官方下载地址：http://archive.apache.org/dist/jakarta/taglibs/standard/binaries/

先打开项目结构，ctrl+alt+shift+s是快捷键

点击Libraries(库)、添加、Java

选择包所在的位置

 

将包添加到项目的Lib目录中，否则在部署时不会被引用

点击Fix修正后的结果如下

 依赖包的方法就是这样了，依赖其它包的方法是一样的。

1.3、创建数据库与表

这个综合应用中需要使用到两个表，SQL脚本如下：

脚本：

#创建商品类型表 [] 
CREATE TABLE `category` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(128) NOT NULL COMMENT '类型名称',
  `parentId` int(11) unsigned DEFAULT NULL COMMENT '父节点编号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_category_name` (`name`),
  KEY `fk_category_parentId` (`parentId`),
  CONSTRAINT `fk_category_parentId` FOREIGN KEY (`parentId`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='商品分类';


#创建商品表
drop table if exists goods;  #删除表

create table if not exists goods
(
    `id` int not null primary key auto_increment comment '编号',
    `title` varchar(128) not null unique key comment '商品名称',
    `category_id` int unsigned COMMENT '商品类型编号',
    `add_date` TIMESTAMP default now() comment '上货时间',
    `picture` varchar(64) comment '图片',
    `state` int default 1 comment '状态',
    FOREIGN key (`category_id`) REFERENCES category(`id`)
)COMMENT='商品'

#修改表，增加列
ALTER table goods add details text COMMENT '详细介绍';
ALTER table goods add price DECIMAL(11,2) COMMENT '价格';

#添加记录，单行
insert into goods(title,category_id,picture,price,details,state) 
VALUES('魅族魅蓝note1 ',4,'pic(1).jpg',999,'好手机',default);

#添加记录，多行
insert into goods(title,category_id,picture,price,details) 
select '魅族魅蓝note2',4,'pic(2).jpg',889,'好手机' UNION
select 'iphone X',4,'pic(3).jpg',5889,'好手机' UNION
select '龙虾',1,'pic(4).jpg',9.85,'好吃' 

#查询
select * from goods

#备份

SELECT
goods.id,
goods.title,
goods.category_id,
goods.add_date,
goods.picture,
goods.state,
goods.details,
goods.price
FROM
goods

select SYSDATE();
SELECT now();


#11 mssql不支持limit语句，是非常遗憾的，只能用top 取代limt 0,N，row_number() over()函数取代limit N,M

limit是mysql的语法
select * from table limit m,n
其中m是指记录开始的index，从0开始，表示第一条记录
n是指从第m+1条开始，取n条。
select * from tablename limit 2,4
即取出第3条至第6条，4条记录


select * from goods LIMIT 5,3;

select * from goods LIMIT 0,3;

#相当于 select * from goods limit 0,5
select * from goods limit 5,-1


select * from goods limit 2,(select count(*)-2 from goods)


#index 1  第几页，页号 
#size  3  每页记录数


skip=(index-1)*size, take=size

1 0,3
2 3,3
3 6,3
4 9,3 

select * from goods 


insert into goods(
goods.title,
goods.category_id,
goods.picture,
goods.state,
goods.details,
goods.price)
SELECT
CONCAT(title,'_',id*8),
id%4+1,
CONCAT('pic(',id,'.jpg'),
id%2,
CONCAT('详细:，非常好，给你点',id,'个赞'),
goods.price+id*8
FROM
goods



select CONCAT('a','b','c');


#枚举类型
ENUM是枚举类型，它虽然只能保存一个值，却能够处理多达65535个预定义的值。下面是我写的一个mysql语句


drop table student
CREATE TABLE student(
id INT(11) PRIMARY key auto_increment,
name VARCHAR(10) not null,
sex ENUM('boy','girl','secret') DEFAULT 'secret',
addDate timestamp DEFAULT now(),
)ENGINE=INNODB

表的数据结构是:

如果sex列中插入了除bor,girl,secret之外的其它字符，则视为空字符串


insert into student(name,sex) values('tom','boy');
select * from student;


insert into student(name,sex) values('tom','male');
insert into student(name,sex) values('tom','female');

insert into student(name,sex) values('tom',DEFAULT);

#添加记录
insert into student set name='rose',sex='girl'
insert into student(name,sex) values ('jack','boy'),('lili','girl'),('candy',default)


select now();
select SYSDATE();
select CURRENT_DATE
select CURRENT_TIMESTAMP
select cur_date()

#MicroSoft SQL Server


select DISTINCT details from goods;

BEGIN
declare @cnt INTEGER;
set @cnt:=100;
select @cnt;
end

begin
declare cnts int;
end

begin
 delimeterDECLARE xname VARCHAR(5) DEFAULT 'bob';
 DECLARE newname VARCHAR(5);
 DECLARE xid INT;
end;

begin
  declare @cnts int;
  call ups_getCntByPrice 1000,@cnts out;
end;

    delimiter ;
BEGIN
  declare @cnts int;
end;

declare
declare var1 int default 0;
end;





select `add`(100,100);
select title,`add`(price,100) from goods;

select now();

select Md5(Md5(输入))=='003d712c491c59a86b7ad2207892c704';

select Md5('xx520');


select max(price) into @maxprice from goods;
select @maxprice

set @maxprice:=999;
select @maxprice;

 查询结果：

1.4、创建商品实体（Bean）

获得表的定义信息（DDL）:

根据DDL创建类，先新建一个包：

 创建一个实体类：

 右键Generate（生成）属性与重写toString方法，快捷键是:alt+insert

 

完成的实体类如下：

package com.zhangguo.mall.entities;

import java.math.BigDecimal;
import java.util.Date;

/**
 * 商品实体
 */
public class Goods {

    /**编号*/
    private int id;
    /**商品名称*/
    private String title;
    /**商品类型编号*/
    private int category_id;
    /**上货时间*/
    private Date add_date;
    /**图片*/
    private String picture;
    /**状态*/
    private int state;
    /**详细介绍*/
    private String details;
    /**价格*/
    private BigDecimal price;

    public int getId() {
        return id;
    }

    public Goods setId(int id) {
        this.id = id;
        return this;
    }

    public String getTitle() {
        return title;
    }

    public Goods setTitle(String title) {
        this.title = title;
        return this;
    }

    public int getCategory_id() {
        return category_id;
    }

    public Goods setCategory_id(int category_id) {
        this.category_id = category_id;
        return this;
    }

    public Date getAdd_date() {
        return add_date;
    }

    public Goods setAdd_date(Date add_date) {
        this.add_date = add_date;
        return this;
    }

    public String getPicture() {
        return picture;
    }

    public Goods setPicture(String picture) {
        this.picture = picture;
        return this;
    }

    public int getState() {
        return state;
    }

    public Goods setState(int state) {
        this.state = state;
        return this;
    }

    public String getDetails() {
        return details;
    }

    public Goods setDetails(String details) {
        this.details = details;
        return this;
    }

    public BigDecimal getPrice() {
        return price;
    }

    public Goods setPrice(BigDecimal price) {
        this.price = price;
        return this;
    }

    @Override
    public String toString() {
        return "Goods{" +
                "id=" + id +
                ", title='" + title + '\'' +
                ", category_id=" + category_id +
                ", add_date=" + add_date +
                ", picture='" + picture + '\'' +
                ", state=" + state +
                ", details='" + details + '\'' +
                ", price=" + price +
                '}';
    }
}

1.5、创建工具层（Utils）

封装常用的辅助工具包，如JDBC操作、JSON操作等

JDBCUtils完成数据访问：

package com.zhangguo.mall.utils;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JDBCUtils {

    public static String DRIVER = "com.mysql.jdbc.Driver";
    public static String URL = "jdbc:mysql://localhost:3306/nfmall?useUnicode=true&characterEncoding=UTF-8";
    public static String USER_NAME = "root";
    public static String PASSWORD = "uchr@123";

    static {
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    private JDBCUtils() {

    }

    /**
     * Get connection
     * 获得连接对象
     * @return
     */
    public static Connection getconnnection() {
        Connection con = null;
        try {
            con = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    /**
     * Close connection
     * 关闭连接
     * @param rs
     * @param st
     * @param con
     */
    public static void close(ResultSet rs, Statement st, Connection con) {
        try {
            try {
                if (rs != null) {
                    rs.close();
                }
            } finally {
                try {
                    if (st != null) {
                        st.close();
                    }
                } finally {
                    if (con != null)
                        con.close();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Close connection
     * 
     * @param rs
     */
    public static void close(ResultSet rs) {
        Statement st = null;
        Connection con = null;
        try {
            try {
                if (rs != null) {
                    st = rs.getStatement();
                    rs.close();
                }
            } finally {
                try {
                    if (st != null) {
                        con = st.getConnection();
                        st.close();
                    }
                } finally {
                    if (con != null) {
                        con.close();
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Close connection
     * 
     * @param st
     * @param con
     */
    public static void close(Statement st, Connection con) {
        try {
            try {
                if (st != null) {
                    st.close();
                }
            } finally {
                if (con != null)
                    con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * insert/update/delete
     * 执行增删改
     * @param sql
     * @param args
     * @return
     */
    public static int update(String sql, Object... args) {
        int result = 0;
        Connection con = getconnnection();
        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(sql);
            if (args != null) {
                for (int i = 0; i < args.length; i++) {
                    ps.setObject((i + 1), args[i]);
                }
            }
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(ps, con);
        }

        return result;
    }

    /**
     * query, because need to manually close the resource, so not recommended
     * for use it
     * 查询，根据sql与参数 返回 结果集
     * @param sql
     * @param args
     * @return ResultSet
     */
    public static ResultSet query(String sql, Object... args) {
        ResultSet result = null;
        Connection con = getconnnection();
        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(sql);
            if (args != null) {
                for (int i = 0; i < args.length; i++) {
                    ps.setObject((i + 1), args[i]);
                }
            }
            result = ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * Query a single record
     * 
     * @param sql
     * @param args
     * @return Map<String,Object>
     */
    public static Map<String, Object> queryForMap(String sql, Object... args) {
        Map<String, Object> result = new HashMap<String, Object>();
        List<Map<String, Object>> list = queryForList(sql, args);
        if (list.size() > 0) {
            result = list.get(0);
        }
        return result;
    }

    /**
     * Query a single record
     * 返回强类型的单个对象
     * @param sql
     * @param args
     * @return <T>
     */
    public static <T> T queryForObject(String sql, Class<T> clz, Object... args) {
        T result = null;
        List<T> list = queryForList(sql, clz, args);
        if (list.size() > 0) {
            result = list.get(0);
        }
        return result;
    }

    /**
     * Query a single record
     * 
     * @param sql
     * @param args
     * @return List<Map<String,Object>>
     */
    public static List<Map<String, Object>> queryForList(String sql, Object... args) {
        List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            con = getconnnection();
            ps = con.prepareStatement(sql);
            if (args != null) {
                for (int i = 0; i < args.length; i++) {
                    ps.setObject((i + 1), args[i]);
                }
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    map.put(rsmd.getColumnLabel(i), rs.getObject(i));
                }
                result.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(rs, ps, con);
        }
        return result;
    }

    /**
     * Query a single record
     * 查询，返回 一个强类型的集合
     * @param sql
     * @param args
     * @return List<T>
     */
    public static <T> List<T> queryForList(String sql, Class<T> clz, Object... args) {
        List<T> result = new ArrayList<T>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = getconnnection();
            ps = con.prepareStatement(sql);
            if (args != null) {
                for (int i = 0; i < args.length; i++) {
                    ps.setObject((i + 1), args[i]);
                }
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            while (rs.next()) {
                T obj = clz.newInstance();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = rsmd.getColumnName(i);
                    String methodName = "set" + columnName.substring(0, 1).toUpperCase()
                            + columnName.substring(1, columnName.length());
                    Method method[] = clz.getMethods();
                    for (Method meth : method) {
                        if (methodName.equals(meth.getName())) {
                            meth.invoke(obj, rs.getObject(i));
                        }
                    }
                }
                result.add(obj);
            }
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            close(rs, ps, con);
        }
        return result;
    }
}

1.6、创建商品的数据访问层（DAO） 

初步完成的Dao，代码如下

package com.zhangguo.mall.dao;

import com.zhangguo.mall.entities.Goods;
import com.zhangguo.mall.utils.JDBCUtils;

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

/**商品数据访问*/
public class GoodsDao {
    /**获得所有商品*/
    public List<Goods> getAllGoods(){
        //要执行的sql
        String sql="SELECT\n" +
                "goods.id,\n" +
                "goods.title,\n" +
                "goods.category_id,\n" +
                "goods.add_date,\n" +
                "goods.picture,\n" +
                "goods.state,\n" +
                "goods.details,\n" +
                "goods.price\n" +
                "FROM\n" +
                "goods";
        //结果集
        ResultSet rs=null;
        //将要返回的集合
        List<Goods> entities=new ArrayList<>();
        try{
            //通过工具类的query方法获得结果集，执行查询
            rs=JDBCUtils.query(sql);
            //通过游标获得单行数据
            while (rs.next()){
                //实例化单个商品对象
                Goods entity=new Goods();
                //设置实体的属性值，从当前行中获得数据
                entity.setId(rs.getInt("id"));
                entity.setTitle(rs.getString("title"));
                entity.setCategory_id(rs.getInt("category_id"));
                entity.setAdd_date(rs.getDate("add_date"));
                entity.setPicture(rs.getString("picture"));
                entity.setState(rs.getInt("state"));
                entity.setDetails(rs.getString("details"));
                entity.setPrice(rs.getBigDecimal("price"));
                //将实体添加到集合中
                entities.add(entity);
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            //确保可以关闭对象
            JDBCUtils.close(rs);
        }
        //返回结果
        return entities;
    }

    public static void main(String[] args) {
        GoodsDao dao=new GoodsDao();
        System.out.println(dao.getAllGoods());
    }
}

此时的项目结构与通过main方法测试的结果：

 

1.7、JUnit单元测试

JUnit是一个Java语言的单元测试框架。它由Kent Beck和Erich Gamma建立，逐渐成为源于Kent Beck的sUnit的xUnit家族中最为成功的一个JUnit有它自己的JUnit扩展生态圈。多数Java的开发环境都已经集成了JUnit作为单元测试的工具。

官网：https://junit.org/

Junit 测试也是程序员测试，即所谓的白盒测试，它需要程序员知道被测试的代码如何完成功能，以及完成什么样的功能

我们知道 Junit 是一个单元测试框架，那么使用 Junit 能让我们快速的完成单元测试。

通常我们写完代码想要测试这段代码的正确性，那么必须新建一个类，然后创建一个 main() 方法，然后编写测试代码。如果需要测试的代码很多呢？那么要么就会建很多main() 方法来测试，要么将其全部写在一个 main() 方法里面。这也会大大的增加测试的复杂度，降低程序员的测试积极性。而 Junit 能很好的解决这个问题，简化单元测试，写一点测一点，在编写以后的代码中如果发现问题可以较快的追踪到问题的原因，减小回归错误的纠错难度。

1.7.1、常用注解

1.@Test: 测试方法

　　　　a)(expected=XXException.class)如果程序的异常和XXException.class一样，则测试通过

　　　　b)(timeout=100)如果程序的执行能在100毫秒之内完成，则测试通过

2.@Ignore: 被忽略的测试方法：加上之后，暂时不运行此段代码

3.@Before: 每一个测试方法之前运行

4.@After: 每一个测试方法之后运行

5.@BeforeClass: 方法必须必须要是静态方法（static 声明），所有测试开始之前运行，注意区分before，是所有测试方法

6.@AfterClass: 方法必须要是静态方法（static 声明），所有测试结束之后运行，注意区分 @After

1.7.2、编写测试类的注意事项　

①测试方法上必须使用@Test进行修饰

②测试方法必须使用public void 进行修饰，不能带任何的参数

③新建一个源代码目录来存放我们的测试代码，即将测试代码和项目业务代码分开

④测试类所在的包名应该和被测试类所在的包名保持一致

⑤测试单元中的每个方法必须可以独立测试，测试方法间不能有任何的依赖

⑥测试类使用Test作为类名的后缀（不是必须）

⑦测试方法使用test作为方法名的前缀（不是必须）

 1.7.3、使用Junit

添加junit的依赖包：

 在需要测试的代码中右键->generate->JUnit Test->对应版本

编写测试代码，如下所示：

需要测试的类：

package com.zhangguo.mall.utils;

public class MathUtils {
    public int add(int a, int b) {
        return a + b;
    }

    public int sub(int a, int b) {
        return a - b;
    }

    public int div(int a, int b) {
        if (b == 0) {
            throw new java.lang.ArithmeticException();
        }
        return a / b;
    }

    public int mut(int a, int b) {
        return a * b;
    }
}

测试类：

package test.com.zhangguo.mall.utils;

import com.zhangguo.mall.utils.MathUtils;
import org.junit.Assert;
import org.junit.Test;
import org.junit.Before;
import org.junit.After;

/**
 * MathUtils Tester.
 *
 * @author <Authors name>
 * @version 1.0
 * @since <pre>09/05/2018</pre>
 */
public class MathUtilsTest {

    MathUtils mathUtils=null;
    @Before  //每个测试方法运行前都会执行的方法
    public void before() throws Exception {
        mathUtils=new MathUtils();
    }

    @After  //每个测试方法运行后都会执行的方法
    public void after() throws Exception {

    }

    /**
     * Method: add(int a, int b)
     */
    @Test  //被测试的方法
    public void testAdd() throws Exception {
        int result=mathUtils.add(100,100);
        //断言
        Assert.assertEquals(300,result);
    }

    /**
     * Method: sub(int a, int b)
     */
    @Test
    public void testSub() throws Exception {

    }

    /**
     * Method: div(int a, int b)
     */
    @Test(expected = java.lang.ArithmeticException.class)
    public void testDiv() throws Exception {
        int result=mathUtils.div(100,0);
    }

    /**
     * Method: mut(int a, int b)
     */
    @Test
    public void testMut() throws Exception {
//TODO: Test goes here... 
    }


} 

运行所有测试方法

 运行单个测试方法

 测试dao，没有问题

1.8、控制器

package com.zhangguo.mall.controller;

import com.zhangguo.mall.dao.GoodsDao;
import com.zhangguo.mall.entities.Goods;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.Date;
import java.util.List;

@WebServlet("/GoodsServlet")
public class GoodsServlet extends HttpServlet {
    GoodsDao goodsDao;

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //设置编码
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/json;charset=utf-8");
        request.setCharacterEncoding("utf-8");

        //请求类型
        String action = request.getParameter("action");

        goodsDao=new GoodsDao();
        //如果是列表
        if(action.equals("list")){
            //获得所有商品
            List<Goods> goods=goodsDao.getAllGoods();
            //附加数据，传递给视图
            request.setAttribute("goods",goods);

            //转发到显示页面
            request.getRequestDispatcher("WEB-INF/views/goods/list.jsp").forward(request,response);

        }else{
            response.getWriter().write("action不能为空");
        }

        response.getWriter().write(new Date().toString());

    }
}

结构：

1.9、表示层

商品展示页面：

<%@ page contentType="text/html;charset=UTF-8" language="java" %>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
    <title>商品管理</title>
</head>
<body>
<h2>商品管理</h2>
<div>
    <table width="100%" border="1">
        <tr>
            <th>
                序号
            </th>
            <th>
                编号
            </th>
            <th>
                名称
            </th>
            <th>
                价格
            </th>
            <th>
                日期
            </th>
            <th>
                状态
            </th>
        </tr>
        <c:forEach items="${goods}" var="entity" varStatus="state">
            <tr>
                <td>
                        ${state.index+1}
                </td>
                <td>
                        ${entity.id}
                </td>
                <td>
                        ${entity.title}
                </td>
                <td>
                        ${entity.price}
                </td>
                <td>
                        ${entity.add_date}
                </td>
                <td>
                        ${entity.state}
                </td>
            </tr>
        </c:forEach>
    </table>
</div>
</body>
</html>

 

结构：

 

运行结果：

 

1.10、分页

1.10.1、后台

数据访问：

package com.zhangguo.mall.dao;

import com.zhangguo.mall.entities.Goods;
import com.zhangguo.mall.utils.JDBCUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**商品数据访问*/
public class GoodsDao {
    /**获得所有商品*/
    public List<Goods> getAllGoods(){
        //要执行的sql
        String sql="SELECT\n" +
                "goods.id,\n" +
                "goods.title,\n" +
                "goods.category_id,\n" +
                "goods.add_date,\n" +
                "goods.picture,\n" +
                "goods.state,\n" +
                "goods.details,\n" +
                "goods.price\n" +
                "FROM\n" +
                "goods";
        //结果集
        ResultSet rs=null;
        //将要返回的集合
        List<Goods> entities=new ArrayList<>();
        try{
            //通过工具类的query方法获得结果集，执行查询
            rs=JDBCUtils.query(sql);
            //通过游标获得单行数据
            while (rs.next()){
                //实例化单个商品对象
                Goods entity=new Goods();
                //设置实体的属性值，从当前行中获得数据
                entity.setId(rs.getInt("id"));
                entity.setTitle(rs.getString("title"));
                entity.setCategory_id(rs.getInt("category_id"));
                entity.setAdd_date(rs.getDate("add_date"));
                entity.setPicture(rs.getString("picture"));
                entity.setState(rs.getInt("state"));
                entity.setDetails(rs.getString("details"));
                entity.setPrice(rs.getBigDecimal("price"));
                //将实体添加到集合中
                entities.add(entity);
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            //确保可以关闭对象
            JDBCUtils.close(rs);
        }
        //返回结果
        return entities;
    }


    /**获得总记录数*/
    public int getCount(){
        String sql="select count(*) as count from goods";
        Map<String,Object> result=JDBCUtils.queryForMap(sql);
        return Integer.parseInt(result.get("count")+"");
    }


    /**获得所有商品*/
    public List<Goods> getGoodsPager(int pageNo,int size){

        //要跳过多少记录，从0开始
        int skip=(pageNo)*size;
        //获得多少条记录
        int take=size;

        //要执行的sql
        String sql="SELECT\n" +
                "goods.id,\n" +
                "goods.title,\n" +
                "goods.category_id,\n" +
                "goods.add_date,\n" +
                "goods.picture,\n" +
                "goods.state,\n" +
                "goods.details,\n" +
                "goods.price\n" +
                "FROM\n" +
                "goods limit ?,?";
        //结果集
        ResultSet rs=null;
        //将要返回的集合
        List<Goods> entities=new ArrayList<>();
        try{
            //通过工具类的query方法获得结果集，执行查询
            rs=JDBCUtils.query(sql,skip,take);
            //通过游标获得单行数据
            while (rs.next()){
                //实例化单个商品对象
                Goods entity=new Goods();
                //设置实体的属性值，从当前行中获得数据
                entity.setId(rs.getInt("id"));
                entity.setTitle(rs.getString("title"));
                entity.setCategory_id(rs.getInt("category_id"));
                entity.setAdd_date(rs.getDate("add_date"));
                entity.setPicture(rs.getString("picture"));
                entity.setState(rs.getInt("state"));
                entity.setDetails(rs.getString("details"));
                entity.setPrice(rs.getBigDecimal("price"));
                //将实体添加到集合中
                entities.add(entity);
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            //确保可以关闭对象
            JDBCUtils.close(rs);
        }
        //返回结果
        return entities;
    }

}

控制器：

package com.zhangguo.mall.controller;

import com.zhangguo.mall.dao.GoodsDao;
import com.zhangguo.mall.entities.Goods;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.Date;
import java.util.List;

@WebServlet("/GoodsServlet")
public class GoodsServlet extends HttpServlet {
    GoodsDao goodsDao;

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //设置编码
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/json;charset=utf-8");
        request.setCharacterEncoding("utf-8");

        //请求类型
        String action = request.getParameter("action");

        goodsDao=new GoodsDao();
        //如果是列表
        if(action.equals("list")){

            String pageNoStr=request.getParameter("pageNo");
            String pageSizeStr=request.getParameter("pageSize");

            if(pageNoStr!=null&&pageSizeStr!=null){
                int pageNo =Integer.parseInt(pageNoStr);
                int pageSize =Integer.parseInt(pageSizeStr);
                //获得所有商品
                List<Goods> goods=goodsDao.getGoodsPager(pageNo,pageSize);
                //附加数据，传递给视图
                request.setAttribute("goods",goods);
                //总记录数
                request.setAttribute("count",goodsDao.getCount());
                //当前页号
                request.setAttribute("pageNo",pageNo);
                //需要首次加载
                request.setAttribute("load_first_page","false");
            }else{
                //需要首次加载
                request.setAttribute("load_first_page","true");
            }
            //转发到显示页面
            request.getRequestDispatcher("WEB-INF/views/goods/list.jsp").forward(request,response);



        }else{
            response.getWriter().write("action不能为空");
        }

        response.getWriter().write(new Date().toString());

    }
}


            /*
            //默认为第1页
            int pageNo=1;
            //如果参数中存在页号，则取出
            String pageNoStr=request.getParameter("pageNo");
            if(pageNoStr!=null&&!pageNoStr.equals("")) {
                pageNo = Integer.parseInt(pageNoStr);
            }

            //默认每页条记录
            int pageSize =5;
            String pageSizeStr=request.getParameter("pageSize");
            if(pageSizeStr!=null&&!pageSizeStr.equals("")) {
                pageSize = Integer.parseInt(pageSizeStr);
            }
            */

单元测试：

package test.com.zhangguo.mall.dao;

import com.zhangguo.mall.dao.GoodsDao;
import com.zhangguo.mall.entities.Goods;
import org.junit.Assert;
import org.junit.Test;
import org.junit.Before;
import org.junit.After;

import java.util.List;

/**
 * GoodsDao Tester.
 *
 * @author <Authors name>
 * @version 1.0
 * @since <pre>09/07/2018</pre>
 */
public class GoodsDaoTest {

    GoodsDao dao=null;
    @Before
    public void before() throws Exception {
        dao=new GoodsDao();
    }

    @After
    public void after() throws Exception {
    }

    /**
     * Method: getGetCount()
     */
    @Test
    public void testGetCount() throws Exception {
        Assert.assertEquals(35,dao.getCount());
    }

    /**
     * Method: getAllGoods()
     */
    @Test
    public void testGetAllGoods() throws Exception {

    }

    /**
     * Method: getGoodsPager(int pageNo, int size)
     */
    @Test
    public void testGetGoodsPager() throws Exception {
        List<Goods> list=dao.getGoodsPager(1,10);
        System.out.println(list);
        Assert.assertEquals(10,list.size());
    }


} 

结果：

1.10.2、前台

代码：

<%@ page contentType="text/html;charset=UTF-8" language="java" %>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
    <title>商品管理</title>
</head>
<body>
<h2>商品管理</h2>
<div>
    <table width="100%" border="1">
        <tr>
            <th>
                序号
            </th>
            <th>
                编号
            </th>
            <th>
                名称
            </th>
            <th>
                价格
            </th>
            <th>
                日期
            </th>
            <th>
                状态
            </th>
        </tr>
        <c:forEach items="${goods}" var="entity" varStatus="state">
            <tr>
                <td>
                        ${state.index+1}
                </td>
                <td>
                        ${entity.id}
                </td>
                <td>
                        ${entity.title}
                </td>
                <td>
                        ${entity.price}
                </td>
                <td>
                        ${entity.add_date}
                </td>
                <td>
                        ${entity.state}
                </td>
            </tr>
        </c:forEach>
    </table>
    <div id="pagination"></div>
</div>
<link rel="stylesheet" href="../../../js/pagination22/pagination.css" type="text/css"/>
<script src="../../../js/jquery/jquery-1.11.3.min.js"></script>
<script src="../../../js/pagination22/jquery.pagination2.2.js"></script>

<script>
    var load_first_page=<c:out value="${load_first_page}"></c:out>;
    var pageCount='<c:out value="${count}"></c:out>'||0;
    var pageSize=5;
    var current_page='<c:out value="${pageNo}"></c:out>'||0;
    $("#pagination").pagination(pageCount, {
        items_per_page: pageSize,
        next_text: "下一页",
        next_show_always: true,
        prev_text: "上一页",
        prev_show_always: true,
        current_page:current_page,
        num_edge_entries:2,
        load_first_page:load_first_page,   //是否首次加载，是否首次就执行handlePaginationClick
        callback: handlePaginationClick
    });

    function handlePaginationClick(pageNo, pagination_container) {
        location.href="GoodsServlet?action=list&pageNo="+pageNo+"&pageSize="+pageSize;

    }
</script>
</body>
</html>

结果：

二、使用Servlet+JDBC+MySQL+IDEA+AJAX实现商品管理

2.1、JSON工具类

package com.zhangguo.mall.utils;

import java.text.SimpleDateFormat;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;

public class JsonUtils {
    /**
     * 序列化成json
     * */
    public static String toJson(Object obj) {
        // 对象映射器
        ObjectMapper mapper = new ObjectMapper();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd HH:mm:ss");
        mapper.setDateFormat(sdf);
        
        String result = null;
        // 序列化user对象为json字符串
        try {
            result = mapper.writeValueAsString(obj);
        } catch (JsonProcessingException e) {
            e.printStackTrace();
        }
        return result;
    }
    
    /**
     * 反序列化成对象
     * */
    public static <T> T toObject(String json,Class<T> valueType) {
        //对象映射器
        ObjectMapper mapper=new ObjectMapper();
        T result=null;
        try {
            result=mapper.readValue(json,valueType);

        }catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
}

测试：

package test.com.zhangguo.mall.utils;

import com.zhangguo.mall.utils.JsonUtils;
import org.junit.Test;
import org.junit.Before;
import org.junit.After;

/**
 * JsonUtils Tester.
 *
 * @author <Authors name>
 * @version 1.0
 * @since <pre>09/10/2018</pre>
 */
public class JsonUtilsTest {

    @Before
    public void before() throws Exception {
    }

    @After
    public void after() throws Exception {
    }

    /**
     * Method: toJson(Object obj)
     */
    @Test
    public void testToJson() throws Exception {
        Student tom=new Student(9001,"汤姆");
        System.out.println(JsonUtils.toJson(tom));
    }

    /**
     * Method: toObject(String json, Class<T> valueType)
     */
    @Test
    public void testToObject() throws Exception {

        String json="{\"id\":9002,\"name\":\"马力\"}";
        System.out.println(JsonUtils.toObject(json,Student.class));
    }


} 

结果：

2.2、商品列表服务（提供JSON数据接口）

Servlet:

package com.zhangguo.mall.controller;

import com.zhangguo.mall.dao.GoodsDao;
import com.zhangguo.mall.utils.JsonUtils;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

@WebServlet("/GoodsApi")
public class GoodsApi extends HttpServlet {
    GoodsDao goodsDao;

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //设置编码
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/json;charset=utf-8");
        request.setCharacterEncoding("utf-8");

        //请求类型
        String action = request.getParameter("action");
        
        goodsDao=new GoodsDao();
        //如果是列表
        if(action.equals("list")) {
            //R
            response.getWriter().write(JsonUtils.toJson(goodsDao.getAllGoods()));
        }else{
            response.getWriter().write("action不能为空");
        }
    }
}

结果：

2.3、使用jQuery+AJAX消费服务

index.html页面

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>天狗商城</title>

</head>
<body>
<h2>天狗商城</h2>

<table border="1" width="100%" id="tabGoods">
    <tr>
        <th>序号</th>
        <th>编号</th>
        <th>名称</th>
        <th>价格</th>
        <th>日期</th>
        <th>状态</th>
        <th>操作</th>
    </tr>

</table>

<script src="js/jquery/jquery-1.11.3.js"></script>
<script>

    var app={
        init:function () {
            app.load();
        },
        load:function () {
            $.ajax({
                url:"GoodsApi?action=list",
                type:"get",
                dataType:"json",
                success:function (data) {

                    for(var i=0;i<data.length;i++){
                        var obj=data[i];
                        var tr=$("<tr/>");

                        $("<td/>").text(i+1).appendTo(tr);
                        $("<td/>").text(obj.id).appendTo(tr);
                        $("<td/>").text(obj.title).appendTo(tr);
                        $("<td/>").text(obj.price).appendTo(tr);
                        $("<td/>").text(obj.add_date).appendTo(tr);
                        $("<td/>").text(obj.state>0?"正常":"冻结").appendTo(tr);

                        $("<td/>").text("删除").appendTo(tr);

                        $("#tabGoods").append(tr);
                    }

                },
                error:function (xhr, textStatus, errorThrown) {
                    alert("错误，"+textStatus+","+errorThrown);
                }
            });
        }
    };

    app.init();

</script>
</body>
</html><!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>天狗商城</title>

</head>
<body>
<h2>天狗商城</h2>

<table border="1" width="100%" id="tabGoods">
    <tr>
        <th>序号</th>
        <th>编号</th>
        <th>名称</th>
        <th>价格</th>
        <th>日期</th>
        <th>状态</th>
        <th>操作</th>
    </tr>

</table>

<script src="js/jquery/jquery-1.11.3.js"></script>
<script>

    var app={
        init:function () {
            app.load();
        },
        load:function () {
            $.ajax({
                url:"GoodsApi?action=list",
                type:"get",
                dataType:"json",
                success:function (data) {

                    for(var i=0;i<data.length;i++){
                        var obj=data[i];
                        var tr=$("<tr/>");

                        $("<td/>").text(i+1).appendTo(tr);
                        $("<td/>").text(obj.id).appendTo(tr);
                        $("<td/>").text(obj.title).appendTo(tr);
                        $("<td/>").text(obj.price).appendTo(tr);
                        $("<td/>").text(obj.add_date).appendTo(tr);
                        $("<td/>").text(obj.state>0?"正常":"冻结").appendTo(tr);

                        $("<td/>").text("删除").appendTo(tr);

                        $("#tabGoods").append(tr);
                    }

                },
                error:function (xhr, textStatus, errorThrown) {
                    alert("错误，"+textStatus+","+errorThrown);
                }
            });
        }
    };

    app.init();

</script>
</body>
</html>

运行结果：

2.4、删除商品功能

删除服务：

dao:

package com.zhangguo.mall.dao;

import com.zhangguo.mall.entities.Goods;
import com.zhangguo.mall.utils.JDBCUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**商品数据访问*/
public class GoodsDao {
    /**获得所有商品*/
    public List<Goods> getAllGoods(){
        //要执行的sql
        String sql="SELECT\n" +
                "goods.id,\n" +
                "goods.title,\n" +
                "goods.category_id,\n" +
                "goods.add_date,\n" +
                "goods.picture,\n" +
                "goods.state,\n" +
                "goods.details,\n" +
                "goods.price\n" +
                "FROM\n" +
                "goods";
        //结果集
        ResultSet rs=null;
        //将要返回的集合
        List<Goods> entities=new ArrayList<>();
        try{
            //通过工具类的query方法获得结果集，执行查询
            rs=JDBCUtils.query(sql);
            //通过游标获得单行数据
            while (rs.next()){
                //实例化单个商品对象
                Goods entity=new Goods();
                //设置实体的属性值，从当前行中获得数据
                entity.setId(rs.getInt("id"));
                entity.setTitle(rs.getString("title"));
                entity.setCategory_id(rs.getInt("category_id"));
                entity.setAdd_date(rs.getDate("add_date"));
                entity.setPicture(rs.getString("picture"));
                entity.setState(rs.getInt("state"));
                entity.setDetails(rs.getString("details"));
                entity.setPrice(rs.getBigDecimal("price"));
                //将实体添加到集合中
                entities.add(entity);
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            //确保可以关闭对象
            JDBCUtils.close(rs);
        }
        //返回结果
        return entities;
    }


    /**获得总记录数*/
    public int getCount(){
        String sql="select count(*) as count from goods";
        Map<String,Object> result=JDBCUtils.queryForMap(sql);
        return Integer.parseInt(result.get("count")+"");
    }


    /**获得所有商品*/
    public List<Goods> getGoodsPager(int pageNo,int size){

        //要跳过多少记录，从0开始
        int skip=(pageNo)*size;
        //获得多少条记录
        int take=size;

        //要执行的sql
        String sql="SELECT\n" +
                "goods.id,\n" +
                "goods.title,\n" +
                "goods.category_id,\n" +
                "goods.add_date,\n" +
                "goods.picture,\n" +
                "goods.state,\n" +
                "goods.details,\n" +
                "goods.price\n" +
                "FROM\n" +
                "goods limit ?,?";
        //结果集
        ResultSet rs=null;
        //将要返回的集合
        List<Goods> entities=new ArrayList<>();
        try{
            //通过工具类的query方法获得结果集，执行查询
            rs=JDBCUtils.query(sql,skip,take);
            //通过游标获得单行数据
            while (rs.next()){
                //实例化单个商品对象
                Goods entity=new Goods();
                //设置实体的属性值，从当前行中获得数据
                entity.setId(rs.getInt("id"));
                entity.setTitle(rs.getString("title"));
                entity.setCategory_id(rs.getInt("category_id"));
                entity.setAdd_date(rs.getDate("add_date"));
                entity.setPicture(rs.getString("picture"));
                entity.setState(rs.getInt("state"));
                entity.setDetails(rs.getString("details"));
                entity.setPrice(rs.getBigDecimal("price"));
                //将实体添加到集合中
                entities.add(entity);
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            //确保可以关闭对象
            JDBCUtils.close(rs);
        }
        //返回结果
        return entities;
    }

    /**根据编号删除*/
    public int delete(int id){
        return JDBCUtils.update("delete from goods where id=?",id);
    }

    /**添加*/
    public int add(Goods entity){
        String sql="insert into goods(title,category_id,picture,price,details,state,add_date) \n" +
                "VALUES(?,3,'pic(1).jpg',?,?,default,?);";
        return JDBCUtils.update(sql,entity.getTitle(),entity.getPrice(),entity.getDetails(),entity.getAdd_date());
    }
}

controller:

package com.zhangguo.mall.controller;

import com.zhangguo.mall.dao.GoodsDao;
import com.zhangguo.mall.utils.JsonUtils;

import javax.servlet.ServletException;
import javax.servlet.ServletResponse;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Date;
import java.util.HashMap;

@WebServlet("/GoodsApi")
public class GoodsApi extends HttpServlet {
    GoodsDao goodsDao;

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response);
    }

    PrintWriter write;
    public void out(String outString){
        try {
            write.write(outString);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //设置编码
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/json;charset=utf-8");
        request.setCharacterEncoding("utf-8");

        write=response.getWriter();

        //请求类型
        String action = request.getParameter("action");

        goodsDao=new GoodsDao();
        //如果是列表
        if(action.equals("list")) {

            //R r=R.ok(goodsDao.getAllGoods()).put("a",100).put("b",200).put("dateTime",new Date());
            out(R.ok(goodsDao.getAllGoods()).Json());
        }
        else if(action.equals("del")) {
            int id=Integer.parseInt(request.getParameter("id"));
            if(goodsDao.delete(id)>0) {
                out(R.ok().Json());
            }else{
                out(R.error().Json());
            }
        }
        else{
            out(R.error("action不能为空").Json());
        }
    }
}

/**封装返回结果*/
class R extends HashMap{
    public R(int code, String msg, Object data) {
        this.put("code",code);
        this.put("msg",msg);
        this.put("data",data);
    }

    public String Json(){
        return JsonUtils.toJson(this);
    }

    public R put(Object key, Object value) {
        super.put(key, value);
        return this;
    }

    public static R ok(String msg, Object data){
        return new R(1,msg,data);
    }
    public static R ok(Object data){
        return new R(1,"请求成功！",data);
    }
    public static R ok(){
        return new R(1,"请求成功！",null);
    }

    public static R error(String msg, Object data){
        return new R(0,msg,data);
    }
    public static R error(String msg){
        return new R(0,msg,null);
    }
    public static R error(){
        return new R(0,"请求失败！",null);
    }

}

UI调用：

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>天狗商城</title>

</head>
<body>
<h2>天狗商城</h2>

<table border="1" width="100%" id="tabGoods">
    <tr>
        <th>序号</th>
        <th>编号</th>
        <th>名称</th>
        <th>价格</th>
        <th>日期</th>
        <th>状态</th>
        <th>操作</th>
    </tr>

</table>

<form id="formGoods">
<fieldset>
    <legend>商品信息</legend>
    <p>
        <label for="title">名称：</label>
        <input id="title" name="title" type="text"/>
    </p>
    <p>
        <label for="price">价格：</label>
        <input id="price" name="price" type="text"/>
    </p>
    <p>
        <label for="add_date">日期：</label>
        <input id="add_date" name="add_date" type="text"/>
    </p>
    <p>
        <label for="details">详细：</label>
        <textarea id="details" name="details" rows="5" cols="50"></textarea>
    </p>
    <p>
        <button>添加</button>
    </p>
</fieldset>
</form>


<script src="js/jquery/jquery-1.11.3.js"></script>
<script>

    var app={
        init:function () {
            app.load();
        },
        load:function () {
            $.ajax({
                url:"GoodsApi?action=list",
                type:"get",
                dataType:"json",
                success:function (data) {
                    if(data.code==1) {
                        for (var i = 0; i < data.data.length; i++) {
                            var obj = data.data[i];
                            var tr = $("<tr/>").data("obj",obj);

                            $("<td/>").text(i + 1).appendTo(tr);
                            $("<td/>").text(obj.id).appendTo(tr);
                            $("<td/>").text(obj.title).appendTo(tr);
                            $("<td/>").text(obj.price).appendTo(tr);
                            $("<td/>").text(obj.add_date).appendTo(tr);
                            $("<td/>").text(obj.state > 0 ? "正常" : "冻结").appendTo(tr);

                            var del=$("<a/>").html("删除").prop("href","#").addClass("del");
                            $("<td/>").append(del).appendTo(tr);

                            $("#tabGoods").append(tr);
                        }
                        app.del();
                    }

                },
                error:function (xhr, textStatus, errorThrown) {
                    alert("错误，"+textStatus+","+errorThrown);
                }
            });
        },
        del:function () {
            $(".del").click(function () {
               if(confirm("您确定要删除吗？")){
                   var obj=$(this).closest("tr").data("obj");
                   var that=this;

                   $.ajax({
                       url:"GoodsApi?action=del",
                       type:"get",
                       data:{"id":obj.id},
                       dataType:"json",
                       success:function (data) {
                           if(data.code==1) {
                               $(that).closest("tr").remove();  //删除当前行
                           }
                           alert(data.msg);
                       },
                       error:function (xhr, textStatus, errorThrown) {
                           alert("错误，"+textStatus+","+errorThrown);
                       }
                   });

               }
                return false;
            });
        }
    };

    app.init();

</script>
</body>
</html>

运行结果：

2.5、新增商品功能

dao数据访问：

    /**添加*/
    public int add(Goods entity){
        String sql="insert into goods(title,category_id,picture,price,details,state,add_date) \n" +
                "VALUES(?,3,'pic(1).jpg',?,?,default,?);";
        return JDBCUtils.update(sql,entity.getTitle(),entity.getPrice(),entity.getDetails(),entity.getAdd_date());
    }

控制器，服务：

package com.zhangguo.mall.controller;

import com.zhangguo.mall.dao.GoodsDao;
import com.zhangguo.mall.entities.Goods;
import com.zhangguo.mall.utils.JsonUtils;

import javax.servlet.ServletException;
import javax.servlet.ServletResponse;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;

@WebServlet("/GoodsApi")
public class GoodsApi extends HttpServlet {
    GoodsDao goodsDao;

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response);
    }

    PrintWriter write;
    public void out(String outString){
        try {
            write.write(outString);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //设置编码
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/json;charset=utf-8");
        request.setCharacterEncoding("utf-8");

        write=response.getWriter();

        //请求类型
        String action = request.getParameter("action");

        goodsDao=new GoodsDao();
        //如果是列表
        if(action.equals("list")) {

            //R r=R.ok(goodsDao.getAllGoods()).put("a",100).put("b",200).put("dateTime",new Date());
            out(R.ok(goodsDao.getAllGoods()).Json());
        }
        else if(action.equals("del")) {
            int id=Integer.parseInt(request.getParameter("id"));
            if(goodsDao.delete(id)>0) {
                out(R.ok().Json());
            }else{
                out(R.error().Json());
            }
        }
        else if(action.equals("add")) {
            Goods entity=new Goods();

            //从客户端获得提交的参数
            String title=request.getParameter("title");
            String price=request.getParameter("price");
            String add_date=request.getParameter("add_date");
            String details=request.getParameter("details");

            entity.setTitle(title);
            //先将字符串类型的价格转换成double类型，再转换成定点小数
            entity.setPrice(new BigDecimal(Double.parseDouble(price)));


            SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
            try {
                entity.setAdd_date(sdf.parse(add_date));
            } catch (ParseException e) {
                e.printStackTrace();
            }

            entity.setDetails(details);

            if(goodsDao.add(entity)>0) {
                out(R.ok().Json());
            }else{
                out(R.error().Json());
            }
        }
        else{
            out(R.error("action不能为空").Json());
        }
    }
}

/**封装返回结果*/
class R extends HashMap{
    public R(int code, String msg, Object data) {
        this.put("code",code);
        this.put("msg",msg);
        this.put("data",data);
    }

    public String Json(){
        return JsonUtils.toJson(this);
    }

    public R put(Object key, Object value) {
        super.put(key, value);
        return this;
    }

    public static R ok(String msg, Object data){
        return new R(1,msg,data);
    }
    public static R ok(Object data){
        return new R(1,"请求成功！",data);
    }
    public static R ok(){
        return new R(1,"请求成功！",null);
    }

    public static R error(String msg, Object data){
        return new R(0,msg,data);
    }
    public static R error(String msg){
        return new R(0,msg,null);
    }
    public static R error(){
        return new R(0,"请求失败！",null);
    }

}

前端页面：

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>天狗商城</title>

</head>
<body>
<h2>天狗商城</h2>

<table border="1" width="100%" id="tabGoods">
    <tr>
        <th>序号</th>
        <th>编号</th>
        <th>名称</th>
        <th>价格</th>
        <th>日期</th>
        <th>状态</th>
        <th>操作</th>
    </tr>

</table>

<form id="formGoods">
<fieldset>
    <legend>商品信息</legend>
    <p>
        <label for="title">名称：</label>
        <input id="title" name="title" type="text"/>
    </p>
    <p>
        <label for="price">价格：</label>
        <input id="price" name="price" type="text"/>
    </p>
    <p>
        <label for="add_date">日期：</label>
        <input id="add_date" name="add_date" type="date"/>
    </p>
    <p>
        <label for="details">详细：</label>
        <textarea id="details" name="details" rows="5" cols="50"></textarea>
    </p>
    <p>
        <button id="btnAdd" type="button">添加</button>
    </p>
</fieldset>
</form>


<script src="js/jquery/jquery-1.11.3.js"></script>
<script>

    var app={
        init:function () {
            app.load();
            app.bind();
        },
        load:function () {
            $.ajax({
                url:"GoodsApi?action=list",
                type:"get",
                dataType:"json",
                success:function (data) {
                    if(data.code==1) {
                        $("#tabGoods tr:gt(0)").remove();
                        for (var i = 0; i < data.data.length; i++) {
                            var obj = data.data[i];
                            var tr = $("<tr/>").data("obj",obj);

                            $("<td/>").text(i + 1).appendTo(tr);
                            $("<td/>").text(obj.id).appendTo(tr);
                            $("<td/>").text(obj.title).appendTo(tr);
                            $("<td/>").text(obj.price).appendTo(tr);
                            $("<td/>").text(obj.add_date).appendTo(tr);
                            $("<td/>").text(obj.state > 0 ? "正常" : "冻结").appendTo(tr);

                            var del=$("<a/>").html("删除 ").prop("href","#").addClass("del");

                            var edit=$("<a/>").html(" | 编辑").prop("href","#").addClass("edit");
                            $("<td/>").append(del).append(edit).appendTo(tr);

                            $("#tabGoods").append(tr);
                        }
                        app.del();
                    }

                },
                error:function (xhr, textStatus, errorThrown) {
                    alert("错误，"+textStatus+","+errorThrown);
                }
            });
        },
        del:function () {
            $(".del").click(function () {
               if(confirm("您确定要删除吗？")){
                   var obj=$(this).closest("tr").data("obj");
                   var that=this;


                   $.ajax({
                       url:"GoodsApi?action=del",
                       type:"get",
                       data:{"id":obj.id},
                       dataType:"json",
                       success:function (data) {
                           if(data.code==1) {
                               $(that).closest("tr").remove();  //删除当前行
                           }
                           alert(data.msg);
                       },
                       error:function (xhr, textStatus, errorThrown) {
                           alert("错误，"+textStatus+","+errorThrown);
                       }
                   });

               }
                return false;
            });
        },
        bind:function () {  //用于绑定事件
            $("#btnAdd").click(function () {
                $.ajax({
                    url:"GoodsApi?action=add",
                    type:"post",
                    data:$("#formGoods").serialize(),
                    dataType:"json",
                    success:function (data) {
                        if(data.code==1) {
                            app.load();
                        }
                        alert(data.msg);
                    },
                    error:function (xhr, textStatus, errorThrown) {
                        alert("错误，"+textStatus+","+errorThrown);
                    }
                });

            });

            $("#tabGoods").on("click",".edit",function () {
                var obj=$(this).closest("tr").data("obj");
                var that=this;
                alert(JSON.stringify(obj));
            });
        }
    };

    app.init();

</script>
</body>
</html>

运行结果：

2.6、编辑商品功能

dao数据访问：

    /**编辑*/
    public int edit(Goods entity){
        String sql="update goods set title=?,price=?,details=?,add_date=? where id=?";
        return JDBCUtils.update(sql,entity.getTitle(),entity.getPrice(),entity.getDetails(),entity.getAdd_date(),entity.getId());
    }

控制器，服务：

package com.zhangguo.mall.controller;

import com.zhangguo.mall.dao.GoodsDao;
import com.zhangguo.mall.entities.Goods;
import com.zhangguo.mall.utils.JsonUtils;

import javax.servlet.ServletException;
import javax.servlet.ServletResponse;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;

@WebServlet("/GoodsApi")
public class GoodsApi extends HttpServlet {
    GoodsDao goodsDao;

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }

    PrintWriter write;

    public void out(String outString) {
        try {
            write.write(outString);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //设置编码
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/json;charset=utf-8");
        request.setCharacterEncoding("utf-8");

        write = response.getWriter();

        //请求类型
        String action = request.getParameter("action");

        goodsDao = new GoodsDao();
        //如果是列表
        if (action.equals("list")) {

            //R r=R.ok(goodsDao.getAllGoods()).put("a",100).put("b",200).put("dateTime",new Date());
            out(R.ok(goodsDao.getAllGoods()).Json());
        } else if (action.equals("del")) {
            int id = Integer.parseInt(request.getParameter("id"));
            if (goodsDao.delete(id) > 0) {
                out(R.ok().Json());
            } else {
                out(R.error().Json());
            }
        } else if (action.equals("add")) {
            Goods entity = new Goods();

            //从客户端获得提交的参数
            String title = request.getParameter("title");
            String price = request.getParameter("price");
            String add_date = request.getParameter("add_date");
            String details = request.getParameter("details");

            entity.setTitle(title);
            //先将字符串类型的价格转换成double类型，再转换成定点小数
            entity.setPrice(new BigDecimal(Double.parseDouble(price)));


            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            try {
                entity.setAdd_date(sdf.parse(add_date));
            } catch (ParseException e) {
                e.printStackTrace();
            }

            entity.setDetails(details);

            if (goodsDao.add(entity) > 0) {
                out(R.ok().Json());
            } else {
                out(R.error().Json());
            }
        } else if (action.equals("edit")) {
            Goods entity = new Goods();

            //从客户端获得提交的参数
            int id = Integer.parseInt(request.getParameter("id"));
            String title = request.getParameter("title");
            String price = request.getParameter("price");
            String add_date = request.getParameter("add_date");
            String details = request.getParameter("details");

            entity.setId(id);
            entity.setTitle(title);
            //先将字符串类型的价格转换成double类型，再转换成定点小数
            entity.setPrice(new BigDecimal(Double.parseDouble(price)));


            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            try {
                entity.setAdd_date(sdf.parse(add_date));
            } catch (ParseException e) {
                e.printStackTrace();
            }

            entity.setDetails(details);

            if (goodsDao.edit(entity) > 0) {
                out(R.ok().Json());
            } else {
                out(R.error().Json());
            }
        } else {
            out(R.error("action不能为空").Json());
        }
    }
}

/**
 * 封装返回结果
 */
class R extends HashMap {
    public R(int code, String msg, Object data) {
        this.put("code", code);
        this.put("msg", msg);
        this.put("data", data);
    }

    public String Json() {
        return JsonUtils.toJson(this);
    }

    public R put(Object key, Object value) {
        super.put(key, value);
        return this;
    }

    public static R ok(String msg, Object data) {
        return new R(1, msg, data);
    }

    public static R ok(Object data) {
        return new R(1, "请求成功！", data);
    }

    public static R ok() {
        return new R(1, "请求成功！", null);
    }

    public static R error(String msg, Object data) {
        return new R(0, msg, data);
    }

    public static R error(String msg) {
        return new R(0, msg, null);
    }

    public static R error() {
        return new R(0, "请求失败！", null);
    }

}

前端页面：

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>天狗商城</title>

</head>
<body>
<h2>天狗商城</h2>

<table border="1" width="100%" id="tabGoods">
    <tr>
        <th>序号</th>
        <th>编号</th>
        <th>名称</th>
        <th>价格</th>
        <th>日期</th>
        <th>状态</th>
        <th>操作</th>
    </tr>

</table>

<form id="formGoods">
<fieldset>
    <legend>商品信息</legend>
    <p>
        <label for="title">名称：</label>
        <input id="title" name="title" type="text"/>
    </p>
    <p>
        <label for="price">价格：</label>
        <input id="price" name="price" type="text"/>
    </p>
    <p>
        <label for="add_date">日期：</label>
        <input id="add_date" name="add_date" type="date"/>
    </p>
    <p>
        <label for="details">详细：</label>
        <textarea id="details" name="details" rows="5" cols="50"></textarea>
    </p>
    <p>
        <input id="id" name="id" value="" type="hidden"/>
        <button id="btnAdd" type="button">添加</button>
        <button id="btnEdit" type="button">更新</button>
    </p>
</fieldset>
</form>


<script src="js/jquery/jquery-1.11.3.js"></script>
<script>

    var app={
        init:function () {
            app.load();
            app.bind();
        },
        load:function () {
            $.ajax({
                url:"GoodsApi?action=list",
                type:"get",
                dataType:"json",
                success:function (data) {
                    if(data.code==1) {
                        $("#tabGoods tr:gt(0)").remove();
                        for (var i = 0; i < data.data.length; i++) {
                            var obj = data.data[i];
                            var tr = $("<tr/>").data("obj",obj);

                            $("<td/>").text(i + 1).appendTo(tr);
                            $("<td/>").text(obj.id).appendTo(tr);
                            $("<td/>").text(obj.title).appendTo(tr);
                            $("<td/>").text(obj.price).appendTo(tr);
                            $("<td/>").text(obj.add_date).appendTo(tr);
                            $("<td/>").text(obj.state > 0 ? "正常" : "冻结").appendTo(tr);

                            var del=$("<a/>").html("删除 ").prop("href","#").addClass("del");

                            var edit=$("<a/>").html(" | 编辑").prop("href","#").addClass("edit");
                            $("<td/>").append(del).append(edit).appendTo(tr);

                            $("#tabGoods").append(tr);
                        }
                        app.del();
                    }

                },
                error:function (xhr, textStatus, errorThrown) {
                    alert("错误，"+textStatus+","+errorThrown);
                }
            });
        },
        del:function () {
            $(".del").click(function () {
               if(confirm("您确定要删除吗？")){
                   var obj=$(this).closest("tr").data("obj");
                   var that=this;


                   $.ajax({
                       url:"GoodsApi?action=del",
                       type:"get",
                       data:{"id":obj.id},
                       dataType:"json",
                       success:function (data) {
                           if(data.code==1) {
                               $(that).closest("tr").remove();  //删除当前行
                           }
                           alert(data.msg);
                       },
                       error:function (xhr, textStatus, errorThrown) {
                           alert("错误，"+textStatus+","+errorThrown);
                       }
                   });

               }
                return false;
            });
        },
        bind:function () {  //用于绑定事件
            $("#btnAdd").click(function () {
                $.ajax({
                    url:"GoodsApi?action=add",
                    type:"post",
                    data:$("#formGoods").serialize(),
                    dataType:"json",
                    success:function (data) {
                        if(data.code==1) {
                            app.load();
                        }
                        alert(data.msg);
                    },
                    error:function (xhr, textStatus, errorThrown) {
                        alert("错误，"+textStatus+","+errorThrown);
                    }
                });

            });

            $("#tabGoods").on("click",".edit",function () {
                var obj=$(this).closest("tr").data("obj");
                var that=this;
                //alert(JSON.stringify(obj));
                $("#id").val(obj.id);
                $("#title").val(obj.title);
                $("#price").val(obj.price);
                $("#add_date").val(obj.add_date);
                $("#details").val(obj.details);

                return false;
            });

            $("#btnEdit").click(function () {
                $.ajax({
                    url:"GoodsApi?action=edit",
                    type:"post",
                    data:$("#formGoods").serialize(),
                    dataType:"json",
                    success:function (data) {
                        if(data.code==1) {
                            app.load();
                        }
                        alert(data.msg);
                    },
                    error:function (xhr, textStatus, errorThrown) {
                        alert("错误，"+textStatus+","+errorThrown);
                    }
                });

            });

        }
    };

    app.init();

</script>
</body>
</html>

运行结果：

三、视频

https://www.bilibili.com/video/av9219224/

四、示例

https://git.coding.net/zhangguo5/NFMall1.git

五、作业

5.1、使用MVC模式完成一个简单学生选课系统

所有的dao要求有单元测试

表结构可以参考MySQL强化练习

要求分页

5.2、MySQL强化练习 

5.3、内部测试

JavaWeb内部测试（一）

JavaWeb内部测试（二）