基于javaJDBC技术的账务管理系统(思路+代码)
技术要点：
1.JDBC工具类的搭建
public class JDBCutils {
private static BasicDataSource datasource = new BasicDataSource();
static {
datasource.setDriverClassName("com.mysql.cj.jdbc.Driver");
datasource.setUrl("jdbc:mysql://localhost:3306/zb?serverTimezone=UTC");
datasource.setUsername("root");
datasource.setPassword("123456");
datasource.setInitialSize(10);
datasource.setMaxIdle(8);
datasource.setMinIdle(3);
}
public static DataSource getdatasource() {
return datasource;
}
}
2.数据库增删改查四大功能熟练使用
3.数据IO流的掌握
4.确定几个层级之间的关系与连通关系（如下图所示）
步骤一：环境的搭建及数据库的建立
需要用到的几个包（commons-dbcp，commons-dbutils，commons-pool，mysql-connector-java）
需要在mysql中建立以个数据库，同时建立一张表格来存储用户数据，表格属性包括账务名、账务类别、金额、转出账户、创建时间、具体描述。
步骤二：模块的构建
按照上述图需要搭建4个模块，不同模块有不同的分工，可以使得任务完成更加顺畅，大的项目工程更加需要这种分包合作。
步骤三：javaBean
JavaBean是指的是Java中的类，该类中的成员变量与数据库表中的字段相对应（变量名对应数据库表字段名、变量数据类型对应数据库表字段类型），并提供空参数构造方法、set、get方法。
此后我们在调用方法时的对象全部来自于这个javabean类对象，在本系统中构建的是ZhangWu对象，包含已经创建好的数据库的所有属性(步骤一所提及)都在这个类对象ZhangWu中。
public class ZhangWu {
private int zwid;
private String flname;
private double money;
private String ZhangHu;
private String createtime;
private String description;
public ZhangWu(int zwid, String flname, double money, String zhangHu, String createtime, String description) {
super();
this.zwid = zwid;
this.flname = flname;
this.money = money;
ZhangHu = zhangHu;
this.createtime = createtime;
this.description = description;
}
public ZhangWu() {
}
@Override
public String toString() {
return "ZhangWu [zwid=" + zwid + ", flname=" + flname + ", money=" + money + ", ZhangHu=" + ZhangHu
+ ", createtime=" + createtime + ", description=" + description + "]";
}
public int getZwid() {
return zwid;
}
public void setZwid(int zwid) {
this.zwid = zwid;
}
public String getFlname() {
return flname;
}
public void setFlname(String flname) {
this.flname = flname;
}
public double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
public String getZhangHu() {
return ZhangHu;
}
public void setZhangHu(String zhangHu) {
ZhangHu = zhangHu;
}
public String getCreatetime() {
return createtime;
}
public void setCreatetime(String createtime) {
this.createtime = createtime;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
}
步骤四：View视图层搭建
主要用于搭建运行界面显示，是与用户进行交互的直接平台。主要构建四大功能：添加账务、编辑账务、删除账务、查询账务，最后加上一个退出系统的功能。其中查询账务分成“查询所有账务”和“条件查询”两个功能。
最后视图层需要与Controller控制层建立连接。
public class MainView {
private static Controllerpart cont = new Controllerpart();
public void run() {
Scanner sc = new Scanner(System.in);
while(true) {
System.out.print("---------GJP账务系统----------");
System.out.println("1.添加账务 2.编辑账务 3.删除账务 4.查询账务 5.退出系统");
System.out.println("请输入需要操作的功能序号");
int choose = sc.nextInt();
switch(choose) {
case 1:
addZhangWu();
break;
case 2:
eidtZhangWu();
break;
case 3:
deleteZhangWu();
break;
case 4:
selectZhangWu();
break;
case 5:
System.exit(0);
break;
default:
System.out.println("请重新输入正确的序号");
}
}
}
private void deleteZhangWu() {
// TODO Auto-generated method stub
Scanner sc = new Scanner(System.in);
System.out.println("请输入需要删除的账务ID");
cont.deleteZhangWu(sc.nextInt());
System.out.println("删除成功");
}
private void eidtZhangWu() {
// TODO Auto-generated method stub
Scanner sc = new Scanner(System.in);
System.out.println("选择需要修改的账务ID");
int zwid = sc.nextInt();
System.out.println("输入分类名称");
String flname = sc.next();
System.out.println("请输入金额");
double money = sc.nextDouble();
System.out.println("请输入账户");
String zhangHu = sc.next();
System.out.println("请输入时间");
String createtime = sc.next();
System.out.println("请输入具体描述");
String description = sc.next();
ZhangWu zw = new ZhangWu(zwid, flname, money, zhangHu, createtime, description);
cont.editZhangWu(zw);
System.out.println("账务更新成功");
}
private void addZhangWu() {
// TODO Auto-generated method stub
System.out.println("选择添加的账务，输入如下内容");
Scanner sc = new Scanner(System.in);
System.out.println("输入分类名称");
String flname = sc.next();
System.out.println("请输入金额");
double money = sc.nextDouble();
System.out.println("请输入账户");
String zhangHu = sc.next();
System.out.println("请输入时间");
String createtime = sc.next();
System.out.println("请输入具体描述");
String description = sc.next();
ZhangWu zw = new ZhangWu(0, flname, money, zhangHu, createtime, description);
cont.addZhangWu(zw);
System.out.println("账务添加成功");
}
public void selectZhangWu() {
// TODO Auto-generated method stub
System.out.println("1.查询所有账务 2.条件查询");
Scanner op = new Scanner(System.in);
int choose = op.nextInt();
switch(choose) {
case 1:
selectAll();
break;
case 2:
select();
break;
}
}
public void select() {
// TODO Auto-generated method stub
System.out.println("选择条件查询，输入日期格式：xxxx-xx-xx");
Scanner sc = new Scanner(System.in);
System.out.println("请输入查询的起始时间");
String startDate = sc.nextLine();
System.out.println("请输入查询的结束时间");
String endDate = sc.nextLine();
List<ZhangWu> list = cont.select(startDate, endDate);
if(list.size()!=0)
print(list);
else
System.out.println("没有查询到需要的数据");
}
private void selectAll() {
// TODO Auto-generated method stub
List<ZhangWu> list = cont.selectAll();
if(list.size()!=0)
print(list);
else
System.out.println("--------没有查询到需要的数据---------");
}
public void print(List<ZhangWu> list) {
System.out.println("ID\t类别\t\t账户\t\t金额\t\t时间\t\t说明");
for (ZhangWu zw : list) {
System.out.println(zw.getZwid() + "\t" + zw.getFlname() + "\t\t"
+ zw.getZhangHu() + "\t\t" + zw.getMoney() + "\t\t"
+ zw.getCreatetime() + "\t" + zw.getDescription());
}
}
}
步骤五、六：Controller控制层搭建与Service服务层搭建
此处不做详细处理，主要是在其中写入控制层与服务层以及dao数据访问层的连通函数(主要是前台方法的重写调用)。
public class Controllerpart {
private static Servicepart ser = new Servicepart();
public void editZhangWu(ZhangWu zw) {
ser.editZhangWu(zw);
}
public List<ZhangWu> addZhangWu(ZhangWu zw){
return ser.addZhangWu(zw);
}
public List<ZhangWu> select(String startDate,String endDate){
return ser.select(startDate,endDate);
}
public List<ZhangWu> selectAll() {
return ser.selectAll();
}
public void deleteZhangWu(int zwid) {
// TODO Auto-generated method stub
ser.deleteZhangWu(zwid);
}
}
public class Servicepart {
private static Daopart dao = new Daopart();
public List<ZhangWu> selectAll() {
// TODO Auto-generated method stub
return dao.selectAll();
}
public List<ZhangWu> select(String startDate, String endDate) {
// TODO Auto-generated method stub
return dao.select(startDate,endDate);
}
public List<ZhangWu> addZhangWu(ZhangWu zw) {
// TODO Auto-generated method stub
return dao.addZhangWu(zw);
}
public void editZhangWu(ZhangWu zw) {
// TODO Auto-generated method stub
dao.editZhangWu(zw);
}
public void deleteZhangWu(int zwid) {
// TODO Auto-generated method stub
dao.deleteZhangWu(zwid);
}
步骤七：dao数据访问层搭建
数据访问层用于与数据库连接，通过用户反馈的操作来调用数据库数据来完成操作。主要掌握数据库语句的使用，以及jdbc QueryRunner类的使用。
QueryRunner.query用于查询数据库；QueryRunner.update用于修改更新数据库
public class Daopart {
private QueryRunner qr = new QueryRunner(JDBCutils.getdatasource());
public List<ZhangWu> selectAll() {
// TODO Auto-generated method stub
try {
String sql = "select * from zbzhangwu";
List<ZhangWu> list = qr.query(sql,new BeanListHandler<ZhangWu>(ZhangWu.class));
return list;
}catch(SQLException ex) {
System.out.println(ex);
throw new RuntimeException("查询失败");
}
}
public List<ZhangWu> select(String startDate, String endDate) {
// TODO Auto-generated method stub
try {
String sql = "select * from zbzhangwu where createtime between ? and ?";
Object[] params = {startDate,endDate};
List<ZhangWu> list = qr.query(sql, new BeanListHandler<ZhangWu>(ZhangWu.class),params);
return list;
}catch(SQLException ex) {
System.out.println(ex);
throw new RuntimeException("查询失败");
}
}
public List<ZhangWu> addZhangWu(ZhangWu zw) {
// TODO Auto-generated method stub
try {
String sql = "insert into zbzhangwu (flname,money,zhangHu,createtime,description)values(?,?,?,?,?)";
Object[] params = {zw.getFlname(),zw.getMoney(),zw.getZhangHu(),zw.getCreatetime(),zw.getDescription()};
qr.update(sql, params);
return null;
}catch(SQLException ex) {
System.out.println(ex);
throw new RuntimeException("账务添加失败");
}
}
public void editZhangWu(ZhangWu zw) {
// TODO Auto-generated method stub
try {
String sql = "update zbzhangwu set flname=?,money=?,zhangHu=?,createtime=?,description=? where zwid=?";
Object[] params = {zw.getFlname(),zw.getMoney(),zw.getZhangHu(),zw.getCreatetime(),zw.getDescription(),zw.getZwid()};
qr.update(sql, params);
}catch(SQLException ex) {
System.out.println(ex);
throw new RuntimeException("数据更新失败");
}
}
public void deleteZhangWu(int zwid) {
// TODO Auto-generated method stub
try {
String sql = "delete from zbzhangwu where zwid=?";
qr.update(sql,zwid);
}catch(SQLException ex) {
System.out.println(ex);
throw new RuntimeException("删除操作失败");
}
}
}