一、使用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());

    }

}

View Cod

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内部测试（二）

巴特西

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

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

1.1、创建项目

1.2、添加依赖

1.3、创建数据库与表

1.4、创建商品实体（Bean）

1.5、创建工具层（Utils）

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

1.7、JUnit单元测试

1.7.1、常用注解

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

1.7.3、使用Junit

1.8、控制器

1.9、表示层

1.10、分页

1.10.1、后台

1.10.2、前台

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

2.1、JSON工具类

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

2.3、使用jQuery+AJAX消费服务

2.4、删除商品功能

2.5、新增商品功能

2.6、编辑商品功能

三、视频

四、示例

五、作业

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

5.2、MySQL强化练习

5.3、内部测试

最新文章

热门文章