第七天上午、两个查询功能实现

2020-08-11 本文已影响0人别学编程

实现订单显示和销售额的统计

今天上午实现两个功能
第一个是所有订单的显示
第二个是销售额的统计
首先看下文件目录
这几个红框选中的文件一个都不能少

文件目录

然后功能实现主要是写代码

OrdersDao.java

package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import java.util.Calendar;
public class OrdersDao
{
    public static List<List<Object>> getSalesRate()
    {
        List<List<Object>> table = new ArrayList<List<Object>>();
        try
        {
            int year = getYear();
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","scott","tiger");
            PreparedStatement ps = conn.prepareStatement("select p.pro_brand,sum(op.pro_count),sum(op.pro_count*p.pro_price) "
                    + " from tb_orders o,tb_orders_product op,tb_product p "
                    + " where o.o_code=op.o_code "
                    + " and op.pro_id = p.pro_id "
                    + " and substr(o.create_date,1,4) = "+year
                    + " group by p.pro_brand ");
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                List<Object> row = new ArrayList<Object>();
                row.add(rs.getString(1));
                row.add(rs.getInt(2)+"单");
                row.add(rs.getInt(3)+"元");
                table.add(row);
            }
            rs.close();
            ps.close();
            conn.close();
            //sc.close();
        } catch (Exception e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("连接失败或数据库查询失败");
        }
        return table;
    }
    public static List<List<Object>> getSalesCount()
    {
        List<List<Object>> table = new ArrayList<List<Object>>();
        try
        {
            int year = getYear();
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","scott","tiger");
            PreparedStatement ps = conn.prepareStatement("select substr(create_date,6,2) 月份,count(1) 数量 from tb_orders"
                    + " where substr(create_date,1,4) = "+year
                    + " group by substr(create_date,6,2) "
                    + " order by substr(create_date,6,2)");
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                List<Object> row = new ArrayList<Object>();
                row.add(rs.getInt(1)+"月");
                row.add(rs.getInt(2)+"单");
                table.add(row);
            }
            rs.close();
            ps.close();
            conn.close();
            //sc.close();
        } catch (Exception e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("连接失败或数据库查询失败");
        }
        return table;
    }
    public static List<List<Object>> getSales(int year)
    {
        List<List<Object>> table = new ArrayList<List<Object>>();
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","scott","tiger");
            PreparedStatement ps = conn.prepareStatement("select substr(create_date,6,2),sum(o_total) "
                    + " from tb_orders "
                    + " where to_number(substr(create_date,1,4)) = "+year
                    + " group by substr(create_date,6,2) "
                    + " order by substr(create_date,6,2) ");
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                List<Object> row = new ArrayList<Object>();
                row.add(rs.getInt(1)+"月");
                row.add(rs.getInt(2)+"元");
                table.add(row);
            }
            rs.close();
            ps.close();
            conn.close();
            //sc.close();
        } catch (Exception e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("连接失败或数据库查询失败");
        }
        return table;
    }
    public static List<List<Object>> getOrders()
    {
        //调用sql语句，获取所有订单信息
//      String[] arr = {"第一张订单","第二张订单","第三张订单","第四张订单","第五张订单"};
//      return arr;
        List<List<Object>> table = new ArrayList<List<Object>>();
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //2、获取连接
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","scott","tiger");
            //3、预执行
            PreparedStatement ps = conn.prepareStatement("select rownum ,o.o_code,o.o_user_account,o.create_date,"
                    + "p.pro_name,p.pro_price,op.pro_count,o.o_total "
                    + "from tb_orders o,tb_orders_product op,tb_product p "
                    + "where o.o_code=op.o_code and op.pro_id = p.pro_id");
            //4、执行
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                List<Object> row = new ArrayList<Object>();
                row.add(rs.getInt(1));
                row.add(rs.getString(2));
                row.add(rs.getString(3));
                row.add(rs.getString(4));
                row.add(rs.getString(5));
                row.add(rs.getInt(6));
                row.add(rs.getInt(7));
                row.add(rs.getInt(8));
                table.add(row);
            }
            //5、释放
            rs.close();
            ps.close();
            conn.close();
        }
        catch (Exception e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("连接失败或数据库查询失败");
        }
    return table;
    }
    public static int getYear()
    {
        Calendar cal = Calendar.getInstance();
        int currentYear = cal.get(Calendar.YEAR);
        boolean cuye;
        int year;
        System.out.println("请输入近十年的年份：");
        do
        {
            Scanner sc = new Scanner(System.in);
            year = sc.nextInt();
            if(currentYear-5 < year && year < currentYear+5)
                break;
            else
            {
                cuye = true;
                System.out.println("输入的年份不对，重新输入：");
            }
        }while(cuye);
        return year;
    }
}

UserDao.java

package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class UserDao
{
    public static boolean login(String account,String password)
    {
        boolean b = false;
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");
            PreparedStatement ps = conn.prepareStatement("select * from tb_user where user_account=? and password=?");
            ps.setString(1, account);
            ps.setString(2, password);
            ResultSet rs = ps.executeQuery();
            if(rs.next())
                b = true;

            rs.close();
            ps.close();
            conn.close();
        } catch (Exception e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        //查询数据库是否有这个用户
//      if(account.equals("123") && password.equals("qwe"))
//          return true;
//      else
//          return false;
        return b;
    }
}

GetOrdersServlet.java

package servlet;

import java.util.List;
import java.io.IOException;
import java.io.PrintWriter;

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 com.alibaba.fastjson.JSON;

import dao.OrdersDao;

/**
 * Servlet implementation class GetOrdersServlet
 */
@WebServlet("/GetOrdersServlet")
public class GetOrdersServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public GetOrdersServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        //response.getWriter().append("Served at: ").append(request.getContextPath());
        //System.out.println("orders.html需要获取订单信息");
        //1、调用OrdersDao获取所有订单信息
        List<List<Object>> list = OrdersDao.getOrders();
        for(int i = 0;i<list.size();i++)
        {
            System.out.println(list.get(i));
        }
        //2、将查询的所有订单，返回给orders.html
        response.setCharacterEncoding("utf-8");//写入到浏览器中的内容支持中文
        PrintWriter out = response.getWriter();
        //将二维表转换成json格式，因为list格式不好解析
        Object json = JSON.toJSON(list);
        out.print(json);//将java写入到浏览器
        out.close();
        
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }
}

GetSalesServlet.java

package servlet;

import java.io.PrintWriter;
import java.util.List;
import java.io.IOException;
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 com.alibaba.fastjson.JSON;

import dao.OrdersDao;
/**
 * Servlet implementation class GetSaleServlet
 */
@WebServlet("/GetSalesServlet")
public class GetSalesServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public GetSalesServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        //response.getWriter().append("Served at: ").append(request.getContextPath());
        String year = request.getParameter("year");
        int y = Integer.parseInt(year);
        //System.out.println("年份"+y);
        List<List<Object>> list = OrdersDao.getSales(y);
        response.setCharacterEncoding("utf-8");
        PrintWriter out = response.getWriter();
        Object json = JSON.toJSON(list);
        out.print(json);
        out.close();
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }
}

LoginServLet.java

package servlet;

import java.io.IOException;
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 dao.UserDao;

/**
 * Servlet implementation class LoginServLet
 */
@WebServlet("/LoginServLet")
public class LoginServLet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public LoginServLet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        //response.getWriter().append("Served at: ").append(request.getContextPath());
        //用户填写的用户名密码会提交到这里
        //获取用户账号和密码
        String account = request.getParameter("account");
        String password = request.getParameter("password");
        System.out.println("账号为："+account);
        System.out.println("密码为："+password);
        
        boolean b = UserDao.login(account, password);
        //想办法跳转页面
        if(b)
        {
            System.out.println("登陆成功");
            response.sendRedirect("index.html");
        }
        else
        {
            System.out.println("登陆失败");
            response.sendRedirect("login.html");
        }
    }
    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}

index.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>主页</title>
<!-- 支持jQuery -->
</head>
<body>
    <hr>
        <h1>这里是主页面</h1><br/>
        <a href = "orders.html" target = "showHtml">订单查看</a><br/>
        <a href = "sales.html" target = "showHtml">销售额统计</a><br/>
        <a href = "count.html" target = "showHtml">销售记录的统计</a><br/>
        <a href = "rate.html" target = "showHtml">品牌销售占比统计</a><br/>
        <a href = "login.html">登陆页面</a><br/>
        <iframe src = "" name = "showHtml" width = "1600" height = "800"></iframe>
    <hr>
</body>
</html>

login.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>用户登录</title>
</head>
<body>
<!-- 表单元素 --注释快捷方式Ctrl+Shift+/，取消为Ctrl+Shift+\-->
<form action = "LoginServLet">
<!-- 文本框 -->
账号：<input type = "text" name = "account"/><br/>
<!-- 密码框 -->
密码：<input type = "password" name = "password"/><br/>
<!-- 提交按钮 -->
<input type = "submit" value = "登录"/>
</form>
</body>
</html>

orders.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>订单信息</title>
<script type = "text/javascript" src = "js/jquery.min.js"></script>
<script>
    $(function(){
        //从java代码中获取所有订单信息
        $.ajax({
            url:"GetOrdersServlet",//将请求发送给java文件，GetOrdersServlet.java
            dataType:"json",
            success:function(data){//java文件处理完，将所有订单信息放入data
                //alert(data[0])
                for(var i = 0;i < data.length;i++)
                {
                    var row ="<tr>"+
                        "<td>"+data[i][0]+"</td>"+
                        "<td>"+data[i][1]+"</td>"+
                        "<td>"+data[i][2]+"</td>"+
                        "<td>"+data[i][3]+"</td>"+
                        "<td>"+data[i][4]+"</td>"+
                        "<td>"+data[i][5]+"</td>"+
                        "<td>"+data[i][6]+"</td>"+
                        "<td>"+data[i][7]+"</td>"+
                    "</tr>"
                    $("#showTable").append(row);
                }
            }
        })
    })
</script>
</head>
<body>
<h2>订单查看</h2>
<table border = 1 cellspacing = 0>
    <tr>
        <th>序号</th>
        <th>订单编号</th>
        <th>所属账户</th>
        <th>生成时间</th>
        <th>商品名称</th>
        <th>商品单价</th>
        <th>购买数量</th>
        <th>订单总价</th>
    </tr>
    <!-- <tr>
        <th>1</th>
        <th>abcdcbacgbad67bagcbad</th>
        <th>15199918178</th>
        <th>2020-8-10 12:21:23</th>
        <th>联想 Air14</th>
        <th>2999.00</th>
        <th>2</th>
        <th>598.00</th>
    </tr> -->
    <tbody id="showTable">
        
    </tbody>
</table>
</body>
</html>

sales.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>销售额统计</title>
<script type = "text/javascript" src = "js/jquery.min.js"></script>
<script>
    function getSales()
    {
        var year = $("#year").val();
        //alert(year);
        $.ajax
        ({
            url:"GetSalesServlet",//将请求发送给java文件，GetOrdersServlet.java
            data:{"year":year},
            dataType:"json",
            success:function(data)
            {//java文件处理完，将所有订单信息放入data
                $("#showTable").html("")//清空tbody内容，不然按年份查询的数据将会追加到table表格里面
                for(var i = 0;i < data.length;i++)
                {
                    var row ="<tr>"+
                        "<td>"+data[i][0]+"</td>"+
                        "<td>"+data[i][1]+"</td>"+
                    "</tr>"
                    $("#showTable").append(row);
                }
            }
        })
    }
</script>
</head>
<body>
<h2>订单查看</h2>
<!-- <select name="myselect"> 
    <option value="1">2015</option> 
    <option value="2">2016</option> 
    <option value="3">2017</option> 
    <option value="4">2018</option> 
    <option value="5">2019</option> 
    <option value="6">2020</option>
</select>  -->
<!-- 点击按钮时执行getSale()函数 -->
<input type="text" id="year"/>
<input type = "button" value = "查询" onclick = "getSales()"/>
<!-- 表格：显示本年每年销售额 -->
<table border = 1 cellspacing = 0>
    <tr>
        <th>月份</th>
        <th>销售额</th>
    </tr>
    <tbody id="showTable">
        
    </tbody>
</table>
</body>
</html>

代码写完之后，启动服务器，浏览器输入http://127.0.0.1/20200810/login.html登录之后即可见到运行成功结果

启动服务器
浏览器运行截图

运行结果

第七天上午、两个查询功能实现

实现订单显示和销售额的统计

OrdersDao.java

UserDao.java

GetOrdersServlet.java

GetSalesServlet.java

LoginServLet.java

index.html

login.html

orders.html

sales.html

猜你喜欢

热点阅读