家庭记账本小程序之后台java代码部分（java web基础版二）

1.数据库连接类

package com.wang.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBUtils {
                                                              //数据库名
    public static String db_url = "jdbc:mysql://localhost:3306/wang?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT";
    public static String db_user = "root";   //用户名
    public static String db_password = "123456"; //密码
     
    public static Connection getConn () {
        Connection conn = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");//加载驱动
            conn = DriverManager.getConnection(db_url, db_user, db_password);//获取连接对象
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
     
    /**
     * 关闭连接
     * @param state
     * @param conn
     */
    public static void close (Statement state, Connection conn) {
        if (state != null) {
            try {
                state.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
     
    /**
     * 关闭连接
     * @param rs
     * @param state
     * @param conn
     */
    public static void close (ResultSet rs, Statement state, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (state != null) {
            try {
                state.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
 
}

2.实体类

package com.wang.pojo;

import java.math.BigDecimal;

public class HomeCost {
    
    private int id;//id
    private String name;//消费名称
    private BigDecimal money;//消费金额
    private String date;//消费日期
    private BigDecimal sum;//累计消费
    
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public BigDecimal getMoney() {
        return money;
    }
    public void setMoney(BigDecimal money) {
        this.money = money;
    }
    public String getDate() {
        return date;
    }
    public void setDate(String date) {
        this.date = date;
    }
    public BigDecimal getSum() {
        return sum;
    }
    public void setSum(BigDecimal sum) {
        this.sum = sum;
    }
    
    
       
    @Override
    public String toString() {
        return "HomeCost [id=" + id + ", name=" + name + ", money=" + money + ", date=" + date + ", sum=" + sum + "]";
    }
    
    public HomeCost() {}
    
    public HomeCost(String name, BigDecimal money) {
        super();
        this.name = name;
        this.money = money;
    }
    
    public HomeCost(int id,String name,BigDecimal money, String date) {
        super();
        this.id=id;
        this.name = name;
        this.money=money;
        this.date=date;
    }
    public HomeCost(int id, String name, BigDecimal money, String date, BigDecimal sum) {
        super();
        this.id = id;
        this.name = name;
        this.money = money;
        this.date = date;
        this.sum = sum;
    }
    
}

3.持久化Dao层

package com.wang.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.wang.pojo.HomeCost;
import com.wang.utils.DBUtils;

public class HomeCostDao {
    
    /**
     * updatesql()用来执行 insert/update/delete语句
     * @param sql 具体的sql语句
     * @return 返回-1，说明执行失败；否则为影响数据条数
     */
    public int updatesql(String sql) {
        Connection conn = DBUtils.getConn();//获取连接对象
        Statement state = null;
        try {
            state = conn.createStatement();
            return state.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(state, conn);
        }
        return -1;       
    }
    
    
    //添加
    public int add(HomeCost homecost) {
        //insert语句，形如insert into 表名(字段1，字段2) values('值1','值2');
        String sql = "insert into home(name,money) values('"+ homecost.getName() 
            + "','" + homecost.getMoney() +  "')";
        return updatesql(sql);
    }
    
    //删除
    public int delete (int id) {
        //delete语句，形如delete from 表名 where id='值';
        String sql = "delete from home where id='" + id + "'";
        return updatesql(sql);
    }
     
    //修改
    public int update(HomeCost homecost) {
        //update语句，形如update 表名 set 字段1 = '值1',字段2 = '值2'where id = '值3';
        String sql = "update home set name='" + homecost.getName() + "', money='" 
                + homecost.getMoney()+ "' where id='" + homecost.getId() + "'";
        return updatesql(sql);
        
    }
    
    //查询
    public List<HomeCost> query(String keyword) {
        String sql = "select * from home WHERE name LIKE '%"+keyword+"%' OR money LIKE '%"+keyword
                +"%'OR date LIKE '%"+keyword+ "%'";
        List<HomeCost> list = new ArrayList<>();
        Connection conn = DBUtils.getConn();
        Statement state = null;
        ResultSet rs = null;
        try {
            state = conn.createStatement();
            rs = state.executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt("id");//获取查询结果中的id
                String name = rs.getString("name");//获取查询结果中的name
                BigDecimal money = rs.getBigDecimal("money");//获取查询结果中的money
                String date = rs.getString("date");//获取查询结果中的date
                HomeCost homeCost = new HomeCost(id,name,money,date);//调用构造方法赋值
                list.add(homeCost);//添加到list集合中
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, state, conn);
        }
        return list;
    }
    
    //通过id找到某条信息
    public HomeCost getHomeCostById(int id) {
        String sql = "select * from home where id ='" + id + "'";
        Connection conn = DBUtils.getConn();
        Statement state = null;
        ResultSet rs = null;
        HomeCost homeCost = null;
        try {
            state = conn.createStatement();
            rs = state.executeQuery(sql);
            while (rs.next()) {
                String name = rs.getString("name");
                BigDecimal money = rs.getBigDecimal("money");
                String date = rs.getString("date");
                homeCost = new HomeCost(id,name,money,date);         
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, state, conn);
        }
        return homeCost;
    }
    
    /**
     * 通过id计算该条消费记录累计消费金额
     * @return BigDecimal类型money
     */
    public BigDecimal queryMoneySum(int id) {
        String sql = "select money from home where id <="+id;
        BigDecimal sum = new BigDecimal("0.00");
        Connection conn = DBUtils.getConn();
        Statement state = null;
        ResultSet rs = null;
        try {
            state = conn.createStatement();
            rs = state.executeQuery(sql);
            while (rs.next()) {
                BigDecimal money = rs.getBigDecimal("money");
                //sum是money累加值
                sum = sum.add(money);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, state, conn);
        }
        return sum;
    }
         
    //获取全部数据  
    public List<HomeCost> list() {
        String sql = "select * from home";
        List<HomeCost> list = new ArrayList<>();
        Connection conn = DBUtils.getConn();
        Statement state = null;
        ResultSet rs = null;
        try {
            state = conn.createStatement();
            rs = state.executeQuery(sql);
            HomeCost homeCost = null;
            while (rs.next()) {
                int id = rs.getInt("id");
                BigDecimal sum = queryMoneySum(id);
                String name = rs.getString("name");
                BigDecimal money = rs.getBigDecimal("money");
                String date = rs.getString("date");
                homeCost = new HomeCost(id,name,money,date,sum);
                list.add(homeCost);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, state, conn);
        }
        return list;
    }
    
    
}

4.事务层Service层

package com.wang.service;

import java.util.List;

import com.wang.dao.HomeCostDao;
import com.wang.pojo.HomeCost;

public class HomeCostService {
    
    HomeCostDao homeCostDao = new HomeCostDao();
    
    //新增消费账单
    public int add(HomeCost homecost) {
        return homeCostDao.add(homecost);
    }
    
    //删除消费账单
    public int delete(int id) {
        return homeCostDao.delete(id);
    }
    
    //修改消费账单
    public int update(HomeCost homecost) {
        return homeCostDao.update(homecost);
    }
    
    //关键字查询
    public List<HomeCost> query(String keyword) {
        return homeCostDao.query(keyword);
    }
    
    //全部消费记录
    public List<HomeCost> list() {
        return homeCostDao.list();
    }
    
    //由id查找某条消费记录
    public HomeCost getHomeCostById(int id) {
        return homeCostDao.getHomeCostById(id);
    }
    
}

5.Web层Servlet层

Servlet创建方式：项目名右键new--Servlet,使用@WebServlet注解，省去配置web.xml的时间

package com.wang.web;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.List;

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.wang.pojo.HomeCost;
import com.wang.service.HomeCostService;
import com.wang.utils.WebUtils;

/**
 * 访问地址url:localhost:8080/homeCost/manager/homeCostServlet
 * Servlet implementation class HomeCostServlet
 */
@WebServlet("/manager/homeCostServlet")
public class HomeCostServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
    private HomeCostService homeCostService = new HomeCostService();
    
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doPost(request, response);
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String action = request.getParameter("action");
        if("add".equals(action)) {
            add(request, response);
        }else if("delete".equals(action)) {
            delete(request, response);
        }else if("update".equals(action)) {
            update(request, response);
        }else if("list".equals(action)) {
            list(request, response);
        }else if("getHomeCostById".equals(action)) {
            getHomeCostById(request, response);
        }else if("query".equals(action)) {
            query(request, response);
        }
    }
    
    //添加消费记录
    protected void add(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //获取网页提交的参数
        String name = request.getParameter("name");
        BigDecimal money = WebUtils.bigdecimal(request.getParameter("money"), new BigDecimal(0.00));
        //封装成类对象
        HomeCost homeCost = new HomeCost(name,money);
        //执行添加操作，返回1,添加成功，反之失败
        if(homeCostService.add(homeCost) == 1) {
            //页面重定向
            response.sendRedirect(request.getContextPath()+"/manager/homeCostServlet?action=list");
        }else {
            //请求转发
            request.setAttribute("msg", "添加失败，联系管理员");
            request.setAttribute("homeCost", homeCost);
            request.getRequestDispatcher("/cost_edit.jsp").forward(request, response);
        }
    }
    //删除消费记录
    protected void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //获取id
        int id  = WebUtils.parseInt(request.getParameter("id"), 0);
        //执行删除操作，返回1,删除成功，反之失败
        if (homeCostService.delete(id) == 1) {
            //页面重定向
            response.sendRedirect(request.getContextPath()+"/manager/homeCostServlet?action=list");
        }else {
            //请求转发
            request.setAttribute("msg", "删除失败，联系管理员");
            request.getRequestDispatcher("/manager/homeCostServlet?action=list").forward(request, response);
        }
        
    }
    //修改消费记录
    protected void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //获取参数
        int id  = WebUtils.parseInt(request.getParameter("id"), 0);
        String name = request.getParameter("name");
        BigDecimal money = WebUtils.bigdecimal(request.getParameter("money"), new BigDecimal(0.00));
        String date = request.getParameter("date");
        //封装
        HomeCost homeCost = new HomeCost(id,name,money,date);
        //执行删除操作，返回1,修改成功，反之失败
        if(homeCostService.update(homeCost) == 1) {
            response.sendRedirect(request.getContextPath()+"/manager/homeCostServlet?action=list");
        }else {
            request.setAttribute("msg", "修改失败，联系管理员");
            request.setAttribute("homeCost", homeCost);
            request.getRequestDispatcher("/cost_edit.jsp").forward(request, response);
        }
    }
    //查询全部消费记录
    protected void list(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        List<HomeCost> homeCost = homeCostService.list();
        request.setAttribute("homeCost", homeCost);
        request.getRequestDispatcher("/manager.jsp").forward(request, response);
        
    }
    //通过id查询该条消费记录
    private void getHomeCostById(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        int id  = WebUtils.parseInt(request.getParameter("id"), 0);
        HomeCost homeCost = homeCostService.getHomeCostById(id);
        request.setAttribute("homeCost", homeCost);
        request.getRequestDispatcher("/cost_edit.jsp").forward(request, response);
    }
    //通过关键词查询
    protected void query(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String keyword=request.getParameter("keyword");
        List<HomeCost> homeCost = homeCostService.query(keyword);
        request.setAttribute("homeCost", homeCost);
        request.getRequestDispatcher("/query.jsp").forward(request, response);
        
    }


}

6.工具类

package com.wang.utils;

import java.math.BigDecimal;

public class WebUtils {
    
    //该类为工具类，封装常用的函数，catch类型转换异常，保障程序健壮性
    
    
    /**
     * String转BigDecimal发生异常返回默认值
     * @param str  字符串
     * @param defaultValue 默认值
     * @return BigDecimal值
     */
    public static BigDecimal bigdecimal(String str,BigDecimal defaultValue) {
        try {
            return new BigDecimal(str);
        } catch (Exception e) {
            //e.printStackTrace();
        }
        return defaultValue;
    }
    
    /**
     * 字符串转int
     * @param string 被转换的字符串
     * @param defaultValue 默认值
     * @return int
     */
    public static int parseInt(String string,int defaultValue){
        try {
            return Integer.parseInt(string);
        } catch (NumberFormatException e) {
            //e.printStackTrace();
        }
        return defaultValue;
    }
    

}

最终创建Web动态工程如下图：

**************************************************************************************************

注：需额外导入部分jar包，css样式，js文件，如下：

附下载链接（永久链）：

链接：https://pan.baidu.com/s/1XA99yVkFGCEeY0-9_5RAxg
提取码：jg0n
复制这段内容后打开百度网盘手机App，操作更方便哦

完整项目下载地址：

链接：https://pan.baidu.com/s/1AoVdFsq4eGvPehyFjVqIMw
提取码：5gvo