第二次冲刺03

今日主要进行对于广告的优化处理；

对于之后的数据库判断做出基础。

package com.example.math.repositiory;
/*
 * 购买信息辅助类
 * */

import com.example.math.R;
import com.example.math.bean.buyBean;
import com.example.math.jdbc.JDBCtools;

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

public class Buy_res {
    //    查询当日收益
    public static int total(String username, String time) throws Exception {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int num = 0;
        try {
            connection = JDBCtools.getConnect();
            String sql = "select SUM(price) from car_buy where user_phone = ? and time like '%" + time + "%'";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int total = resultSet.getInt(1);
                num = total;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCtools.release(connection, preparedStatement, resultSet);
        }
        return num;
    }

    //      累计收益
    public static int top_total(String username) throws Exception {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int num = 0;
        try {
            connection = JDBCtools.getConnect();
            String sql = "select SUM(price) from car_buy where user_phone = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int total = resultSet.getInt(1);
                num = total;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCtools.release(connection, preparedStatement, resultSet);
        }
        return num;
    }

    public static List<buyBean> findDg(String username, String time) throws Exception {
        List<buyBean> list = new ArrayList<>();
        Connection connection = JDBCtools.getConnect();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select name,price,time from car_buy where user_phone = ? and time like '%" + time + "%'";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                int price = resultSet.getInt("price");
                String time1 = resultSet.getString("time");
                buyBean buy_bean = new buyBean(username, name, price, time1);
                list.add(buy_bean);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCtools.release(connection, preparedStatement, resultSet);
        }
        return list;
    }

    /**
     * 获取某一月具体收入
     */
    public static int getSunMoneyOneMonth(String username, String time) throws Exception {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int num = 0;
        try {
            connection = JDBCtools.getConnect();
            String sql = "select SUM(price) from car_buy where user_phone = ? and time like '%" + time + "%'";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int total = resultSet.getInt(1);
                num = total;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCtools.release(connection, preparedStatement, resultSet);
        }
        return num;
    }

    /**
     * 获取某一月共几笔收入
     */
    public static int getCountItemOneMonth(String username, String time) throws Exception {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int num = 0;
        try {
            connection = JDBCtools.getConnect();
            String sql = "select count(price) from car_buy where user_phone = ? and time like '%" + time + "%'";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int total = resultSet.getInt(1);
                num = total;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCtools.release(connection, preparedStatement, resultSet);
        }
        return num;
    }
}