What is the ProductManagement?

A simple product management system which can do some basic things

这里有中文说明

This is my first Web project

Before you do this project, there have something you need to learn first

Basic part of Servlet、JSP
MySql ( CRUD )
MVC design pattern（Model-View-Controller）

Mind Map

About this project

Honestly speaking, I think this is the the easiest project for the beginner of Java Web. It has only one data table, one Servlet, ugly user interface (I don't know how to do it better) and the basic CRUD. If you are a freshmen here, complete this project will greatly increase your confidence.

You can first take a look at the following development process, fork the project, set up the web environment and run this project. And then make your own project.

If you have something thar Google can not solve, like some problems when you set up the environment, you can email me, I will try my best to help every one who are enthusiastic about Web develop.

Version 0.1

Structure：

Servlet + JSP + MySQL
MVC design pattern

What can it do：

Create
Edit
Delete
ShowAll
Query

Preparation：

1. Package

dao
demo
page
product
service
servlet

2. Import JAR

c3p0-0.9.5.2
commons-beanutils-1.9.3
commons-collections-3.2.2
commons-dbutils-1.7
commons-logging-1.2
itcast-tools-1.4
jsp-api
jstl-1.2
mchange-commons-java-0.2.11
mysql-connector-java-5.0.8-bin
servlet-api

3. SQL Table

USE product;  
CREATE TABLE product (  
    id              VARCHAR(50) NOT NULL PRIMARY KEY,
    barcode         VARCHAR(13) NOT NULL,
    name            VARCHAR(40) NOT NULL,
    units           VARCHAR(5)  NOT NULL,
    purchasePrice   VARCHAR(10) NOT NULL,
    salePrice       VARCHAR(10) NOT NULL,
    inventory       VARCHAR(20) NOT NULL
);

4. Programming Environment

Intellij IDEA Ultimate + MySQL

5. How to run this project

1.Clone or download project https://github.com/lihanxiang/ProductManagement.git
2.configure the environment of web project (only have chinese tutorial Issues#1)
3.Deploy it to tomcat Issues#1
4.Change the database information in c3p0-config.xml
5.Run, open browser and enter： localhost:8080

Development process

1. Product class

According to the data in SQL table：

public class Product {
    private String id;              //use CommonUtils.uuid() to automatic generated ID
    private String barcode;         
    private String name;            
    private String units;           
    private String purchasePrice;   
    private String salePrice;       
    private String inventory;       
    
    //setter 和 getter
}

PageBean class, use it to show the data in different pages：

public class PageBean<Object> {
    private int pageCode;           
    private int pageRecord;        
    private int totalPage;          
    private int totalRecord;        
    private List<Object> beanList;  
    private String url;
    
    // setter 和 getter
    //getTotalPage() is a little different from others
    public int getTotalPage(){
        totalPage = totalRecord/pageRecord;
        return totalRecord % pageRecord == 0 ? totalPage : totalPage + 1;
    }
}

2. c3p0

Use database connection pool to manage the operation of database, so that the system can be more efficient. About c3p0, I wrote a chinese tutorial.

After you import the c3p0-jar, you need a file named c3p0-config.xml to write down some configuration:

<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
    <default-config>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/the name of database schema</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="user">username</property>
        <property name="password">password</property>
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">30</property>
        <property name="maxPoolSize">30</property>
        <property name="minPoolSize">1</property>
    </default-config>
</c3p0-config>

3. DAO

We use a class named ProductDao to do the work with database:

Use the TxQueryRunner to commit the SQL to database

    private QueryRunner queryRunner = new TxQueryRunner();

Here I only give the code of add()

    public void add(Product product){
        try{
            String sql = "INSERT INTO product VALUES (?,?,?,?,?,?,?)";
            //use this array to fill the ? in sql
            Object[] parameter = {product.getId(),product.getBarcode(),product.getName(),
                        product.getUnits(), product.getPurchasePrice(), product.getSalePrice(),
                        product.getInventory()};
            
            queryRunner.update(sql,parameter);
        }catch(SQLException e){
            e.printStackTrace();
        }
    }

And then we take a look at showAll()

First, we need to find the number of products, and show the specific products according to the page code

    public PageBean<Product> showAll(int pageCode, int pageRecord){
        try{
            PageBean<Product> pageBean = new PageBean<>();
            pageBean.setPageCode(pageCode);
            pageBean.setPageRecord(pageRecord);

            //count the number of products
            String sql = "SELECT COUNT(*) FROM product";
            Number totalRecord = queryRunner.query(sql, new ScalarHandler<>());

            pageBean.setTotalRecord(totalRecord.intValue());

            //find the specific products
            sql = "SELECT * FROM product ORDER BY name LIMIT ?,?";
            //page code is start from 1
            Object[] parameter = {(pageCode - 1) * pageRecord, pageRecord};  

            List<Product> beanList = queryRunner.query(sql, new BeanListHandler<>(Product.class),
                                            parameter);

            //set the content of pageBean
            pageBean.setBeanList(beanList);
            return pageBean;

        }catch(SQLException e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

Finally, the query()

    public PageBean<Product> query(Product product, int pageCode, int pageRecord){
        try{
            PageBean<Product> pageBean = new PageBean<>();
            pageBean.setPageCode(pageCode);
            pageBean.setPageRecord(pageRecord);

            StringBuilder countSQL = new StringBuilder("SELECT COUNT(*) FROM product ");
            StringBuilder whereSQL = new StringBuilder("WHERE 1 = 1 ");   //1 = 1 is always true
            List<Object> parameter = new ArrayList<>();

            //only show the part of barcode
            String barcode = product.getBarcode();
            if(barcode != null && !barcode.trim().isEmpty()){
                whereSQL.append(" AND barcode LIKE ?");
                parameter.add("%" + barcode + "%");
            }
            //...
            
            Number totalRecord = queryRunner.query(countSQL.append(whereSQL).toString(), 
                                                new ScalarHandler<>(),parameter.toArray());
            
            pageBean.setTotalRecord(totalRecord.intValue());

            StringBuilder selectSQL = new StringBuilder("SELECT * FROM product ");
            //limit the output
            StringBuilder limitSQL = new StringBuilder(" LIMIT ?,?");        

            //pageRecord = 10,list = {10,20}, means the products 10 to 20。

            parameter.add((pageCode - 1) * pageRecord);
            parameter.add(pageRecord);

            List<Product> beanList = queryRunner.query(
                            selectSQL.append(whereSQL).append(limitSQL).toString(), 
                            new BeanListHandler<>(Product.class), parameter.toArray());
            //set the content of pageBean
            pageBean.setBeanList(beanList);
            return pageBean;

        }catch (SQLException e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

So, in the sql above, why do we have 1 = 1, because if the user doesn't input anything, the sql will become like this：

    SELECT * FROM product WHERE

but if we use 1 = 1, even if the user input nothing, it will show all products, there will be no sql syntax error

4. Service

We need to use the methods in DAO:

    private ProductDao productDao = new ProductDao();

The code of Service is call the methods in DAO:

    public void add(Product product){
        productDao.add(product);
    }
    //other methods

Here I add one method find(String id), use the id to find product

We first add the method find(String id) in DAO：

    public Product find(String id){
        try{
            String sql = "SELECT * FROM product WHERE id = ? ";
            return queryRunner.query(sql, new BeanHandler<>(Product.class),id);
        }catch (SQLException e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

And call it in Service：

    public Product find(String id){
        return productDao.find(id);
    }

Servlet

Finally, we get to the most important part: Servlet

The methods in Servlet are based on Service：

    private ProductService productService = new ProductService();

add():

    public String add(HttpServletRequest request, HttpServletResponse response) 
                throws IOException, ServletException{

        //Encoding
        request.setCharacterEncoding("utf-8");
        //Mapping the request and product
        Product product = CommonUtils.toBean(request.getParameterMap(), Product.class);
        //Random ID
        product.setId(CommonUtils.uuid());     
        productService.add(product);

        //After add product, you need to give a message on the page
        //set an attribute named message, pass it to the request
        request.setAttribute("message", "Add Product Successfully");
        //return jsp
        return "/message.jsp";
    }

delete():

    public String delete(HttpServletRequest request, HttpServletResponse response)
                        throws IOException, ServletException{

        //Get the id of the product you want to delete from request
        String id = request.getParameter("id");
        productService.delete(id);

        //set the attribute
        request.setAttribute("message", "Delete Product Successfully");
        return "/message.jsp";
    }

Edit the product, here are two steps:

First, according to the id, find the specific product

    public String preEdit(HttpServletRequest request, HttpServletResponse response)
                        throws IOException, ServletException{
        //Get id
        String id = request.getParameter("id");
        //Find product
        Product product = productService.find(id);      

        //pass the product to the request
        request.setAttribute("product", product);
        return "/edit.jsp";
    }

Second, we make some change on the product：

    public String edit(HttpServletRequest request, HttpServletResponse response)
                        throws IOException, ServletException{
        
        Product product = CommonUtils.toBean(request.getParameterMap(), Product.class);
        productService.edit(product);

        //set the attribute
        request.setAttribute("message", "Edit Product Successfully");
        return "/message.jsp";
    }

Now, before we write the method to show all products, we need to find the page code and URL:

    private int getPageCode(HttpServletRequest request){
        //Get the page code, if it's null, set it to 1
        String value = request.getParameter("pageCode");
        if(value == null || value.trim().isEmpty()){
            return 1;
        }
        return Integer.parseInt(value);
    }
    
    //URL Stitching
    private String getUrl(HttpServletRequest request){
        String contextPath = request.getContextPath();
        String servletPath = request.getServletPath();
        String queryString = request.getQueryString();

        if (queryString.contains("&pageCode=")) {
            int index = queryString.lastIndexOf("&pageCode=");
            queryString = queryString.substring(0, index);
        }
        return contextPath + servletPath + "?" + queryString;
    }

showAll():

    public String showAll(HttpServletRequest request, HttpServletResponse response)
    throws IOException, ServletException{
        request.setCharacterEncoding("utf-8");
        int pageCode = getPageCode(request);
        int pageRecord = 10;        //10 records per page

        PageBean<Product> pageBean = productService.showAll(pageCode, pageRecord);
        //set the URL
        pageBean.setUrl(getUrl(request));

        //set the attribute
        request.setAttribute("pageBean",pageBean);
        return "/content.jsp";
    }

query is misty serch:

    public String query(HttpServletRequest request, HttpServletResponse response)
                    throws ServletException, IOException{
        request.setCharacterEncoding("utf-8");
        //Mapping
        Product product = CommonUtils.toBean(request.getParameterMap(), Product.class);

        int pageCode = getPageCode(request);
        int pageRecord = 10;

        PageBean<Product> pageBean = productService.query(product, pageCode, pageRecord);

        pageBean.setUrl(getUrl(request));

        //set the attribute
        request.setAttribute("pageBean",pageBean);
        return "/content.jsp";
    }

Jsp

About jsp, you can do it with basic knowledge of HTML, no discussion here

Screenshows：

Version 0.2

Better UI (not that ugly)

Project is closed, happy coding