JDBCUtils.java

public class JDBCUtils {
public static final StringDRIVER_CLASS_NAME ="com.mysql.jdbc.Driver";

public static final StringURL = "jdbc:mysql://localhost:3306/gjp";

public static final StringUSERNAME = "root";

public static final StringPASSWORD = "root";

private static BasicDataSourcedataSource = new BasicDataSource();

static {
dataSource.setDriverClassName(DRIVER_CLASS_NAME);

dataSource.setUrl(URL);

dataSource.setUsername(USERNAME);

dataSource.setPassword(PASSWORD);

}

public static DataSource getDataSource() {
return dataSource;

}

}

CREATE TABLE gjp_zhangwu (

  zwid INT PRIMARY KEY AUTO_INCREMENT,

  flname VARCHAR(200),

  money DOUBLE,

  zhangHu VARCHAR(100),

  createtime DATE,

  description VARCHAR(1000) 

);

INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description)VALUES (1,'吃飯支出',247,'交通銀行','2016-03-02','家庭聚餐');

INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description)VALUES (2,'工資收入',12345,'現(xiàn)金','2016-03-15','開(kāi)工資了');

INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description)VALUES (3,'服裝支出',1998,'現(xiàn)金','2016-04-02','買衣服');

INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description)VALUES (4,'吃飯支出',325,'現(xiàn)金','2016-06-18','朋友聚餐');

INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description)VALUES (5,'股票收入',8000,'工商銀行','2016-10-28','股票大漲');

INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description)VALUES (6,'股票收入',5000,'工商銀行','2016-10-28','股票又大漲');

INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description)VALUES (7,'工資收入',5000,'交通銀行','2016-10-28','又開(kāi)工資了');

INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description)VALUES (8,'禮金支出',5000,'現(xiàn)金','2016-10-28','朋友結(jié)婚');

INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description)VALUES (9,'其他支出',1560,'現(xiàn)金','2016-10-29','丟錢了');

INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description)VALUES (10,'交通支出',2300,'交通銀行','2016-10-29','油價(jià)還在漲啊');

INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description)VALUES (11,'吃飯支出',1000,'工商銀行','2016-10-29','又吃飯');

INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description)VALUES (12,'工資收入',1000,'現(xiàn)金','2016-10-30','開(kāi)資');

INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description)VALUES (13,'交通支出',2000,'現(xiàn)金','2016-10-30','機(jī)票好貴');

INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description)VALUES (14,'工資收入',5000,'現(xiàn)金','2016-10-30','又開(kāi)資');

/** 
* 主方法類 */
public class MainApp {
public static void main(String[]args) {
}
} 

/** 
* 賬務(wù)類 */
public class ZhangWu {} 

/**
* 賬務(wù)數(shù)據(jù)層類 */
public class ZhangWuDao{
//獲取數(shù)據(jù)庫(kù)連接池，得到操作表數(shù)據(jù)的對(duì)象
QueryRunnerQueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
}

/**
* 賬務(wù)業(yè)務(wù)層類 */
public class ZhangWuService {
//service都依賴daoprivate
 ZhangWuDao zhangWuDao =new ZhangWuDao();
} 

MainView.javapublic class MainView {
//本項(xiàng)目中view依賴serviceprivate 
ZhangWuServicezhangWuService = new ZhangWuService();
} 

/**
 * 賬務(wù)類 */
public class ZhangWu {private int zwid;// id
private String flname;// 分類名稱
private String zhangHu;// 賬戶名稱
private double money;//金額
private String createtime;// 創(chuàng)建時(shí)間
private String description;// 說(shuō)明 
public String getFlname() {return flname;}public void setFlname(Stringflname) {this.flname =flname;}public String getZhangHu() {return zhangHu;}public void setZhangHu(StringzhangHu) {this.zhangHu =zhangHu;}
public int getZwid() {return zwid;}public void setZwid(int zwid) {this.zwid =zwid;}public double getMoney() {return money;}
public void setMoney(double money) {this.money =money;}public String getCreatetime() {return createtime;}
public void setCreatetime(Stringcreatetime) {this.createtime =createtime;}public String getDescription() {return description;}
public void setDescription(Stringdescription) {this.description =description;}} 

/** 
* 運(yùn)行方法 
*/
public void run() {
/*
 * 
1. 打印菜單2. 獲取用戶輸入 3. 調(diào)用對(duì)應(yīng)方法 
*/
boolean flag =true;
Scanner in = new Scanner(System.in);
while (flag) {
System.out.println("---------------管家婆家庭記賬軟件---------------");
System.out.println(1.添加賬務(wù)　2.編輯賬務(wù)　3.刪除賬務(wù)　4.查詢賬務(wù)　5.退出系統(tǒng)");
System.out.println("請(qǐng)輸入要操作的功能序號(hào)[1-5]:");
int op =in.nextInt();
switch (op) {
case 1:addZhangWu();
break;
case 2:editZhangWu();
break;
case 3:deleteZhangWu();
break;
case 4:selectZhangWu();
break;
case 5:System.out.println("再見(jiàn)！");
flag = false;
break;
default:System.out.println("輸入錯(cuò)誤！");}}} 
2.app包MainApp類的main主方法中，調(diào)用MainView類中run方法
public static void main(String[]args) {
new MainView().run();} 

/** 
* 查詢賬務(wù)方法
 */
public void selectZhangWu() {
System.out.println("1.查詢所有　2.按條件查詢");
Scanner in = new Scanner(System.in);
int op =in.nextInt();
switch (op) {
case 1:selectAll();
break;
case 2:select();
break;
default:
System.out.println("輸入錯(cuò)誤！");}} 

/** 
* 查詢所有賬務(wù)方法 
*/
public void selectAll() {
List<ZhangWu> zhangWuList = zhangWuService.selectAll();
print(zhangWuList);}

/* 
* 輸出賬務(wù)方法 
*/private void print(List<ZhangWu>zhangWuList) {
System.out.println("ID\t類別\t\t賬戶\t\t金額\t\t時(shí)間\t\t說(shuō)明");
for (ZhangWu zw : zhangWuList) {
System.out.println(zw.getZwid() +"\t" + zw.getFlname() + "\t\t"+ zw.getZhangHu() + "\t\t" + zw.getMoney() + "\t\t"+ zw.getCreatetime() + "\t" + zw.getDescription());}} 

/**
 * 查詢所有
*/
public List<ZhangWu> selectAll() {
return zhangWuDao.selectAll();}

/** 
* 查詢所有賬務(wù)
 */
public List<ZhangWu> selectAll() {
String sql = "select * from gjp_zhangwu";
try {
return qr.query(sql,new BeanListHandler<ZhangWu>(ZhangWu.class));} 
catch (SQLExceptione) {throw new RuntimeException(e);}} 

/** 
* 按條件查詢賬務(wù)方法 
*/
public void select() {/* *
 1. 獲取用戶輸入查詢?nèi)掌诜秶?* 
2. 調(diào)用service的select()方法完成條件查詢功能 */
Scanner in = new Scanner(System.in);
System.out.print("請(qǐng)輸入查詢起始時(shí)間：");
String start = in.next();
System.out.print("請(qǐng)輸入查詢結(jié)束時(shí)間：");
String end = in.next();
List<ZhangWu> zhangWuList = zhangWuService.select(start, end);
print(zhangWuList);}

/**
 * 按條件查詢賬務(wù)方法
 * @param start * 
@param end * 
@return */
public List<ZhangWu> select(
Stringstart, String end) {
return zhangWuDao.select(start,end);}

/**
 * 按條件查詢賬務(wù)方法
 * 
@param start * 
@param end * 
@return */
public List<ZhangWu> select(
Stringstart, String end) {
String sql = "select * from gjp_zhangwu where createtime between ? and ?";
Object[] params = {start,end};
try {
return qr.query(sql,new BeanListHandler<ZhangWu>(ZhangWu.class),params);} 
catch (SQLExceptione) {throw new RuntimeException(e);}} 

/** 
* 添加賬務(wù)方法 
*/
public void addZhangWu() {
/* *
1. 獲取用戶輸入，封裝到bean中。 * 
2. 調(diào)用service的addZhangWu()方法完成添加功能 */
Scanner in = new Scanner(System.in);
ZhangWu zw = new ZhangWu();
System.out.print("請(qǐng)輸入類別：");
zw.setFlname(in.next());
System.out.print("請(qǐng)輸入賬戶：");
zw.setZhangHu(in.next());
System.out.print("請(qǐng)輸入金額：");
zw.setMoney(in.nextDouble());
System.out.print("請(qǐng)輸入時(shí)間：");
zw.setCreatetime(in.next());
System.out.print("請(qǐng)輸入說(shuō)明：");
zw.setDescription(in.next());zhangWuService.addZhangWu(zw);
System.out.println("添加賬務(wù)成功！");}

/** 
* 添加賬務(wù) * 
@param zw */
public void addZhangWu(ZhangWuzw) {zhangWuDao.addZhangWu(zw);} 

/**
 * 添加賬務(wù) 
* 
@param zw */
public void addZhangWu(ZhangWuzw) {
String sql = "insert into gjp_zhangwu(flname,money,zhanghu,createtime,description) values(?,?,?,?,?)";
try {Object[] params = {
zw.getFlname(),
zw.getMoney(),
zw.getZhangHu(),
zw.getCreatetime(),
zw.getDescription()};
qr.update(sql,params);}
 catch (SQLExceptione) {
throw new RuntimeException(e);} } 

/** 
* 編輯賬務(wù)方法
 */
public void editZhangWu() {
/* 
*1. 獲取用戶輸入，封裝到bean中。
*2. 調(diào)用service的editZhangWu()方法完成添加功能

 */

Scanner in = new Scanner(System.in);

ZhangWu zw = new ZhangWu();

System.out.print("請(qǐng)輸入ID：");

zw.setZwid(in.nextInt());

System.out.print("請(qǐng)輸入新類別：");

zw.setFlname(in.next());

System.out.print("請(qǐng)輸入新賬戶：");

zw.setZhangHu(in.next());

System.out.print("請(qǐng)輸入新金額：");

zw.setMoney(in.nextDouble());

System.out.print("請(qǐng)輸入新時(shí)間：");

zw.setCreatetime(in.next());

System.out.print("請(qǐng)輸入新說(shuō)明：");

zw.setDescription(in.next());

zhangWuService.editZhangWu(zw);

System.out.println("編輯賬務(wù)成功！");

}

/**

 * 編輯賬務(wù)

 * @param zw

 */

public void editZhangWu(ZhangWuzw) {
zhangWuDao.editZhangWu(zw);

}

 

/**

 * 編輯賬務(wù)

 * @param zw

 */

public void editZhangWu(ZhangWuzw) {
String sql = "update gjp_zhangwu set flname=?, money=?,zhanghu=?,createtime=?,description=? where zwid=?";

try {
Object[] params = {zw.getFlname(),zw.getMoney(),zw.getZhangHu(),zw.getCreatetime(),zw.getDescription(),zw.getZwid()};

qr.update(sql,params);

} catch (SQLExceptione) {
throw new RuntimeException(e);

}

}

/**

 * 刪除賬務(wù)方法

 */

public void deleteZhangWu() {
/*

 * 1. 獲取用戶輸入，封裝到bean中。

 * 2. 調(diào)用service的deleteZhangWu()方法完成添加功能

 */

Scanner in = new Scanner(System.in);

System.out.print("請(qǐng)輸入ID：");

zhangWuService.deleteZhangWu(in.nextInt());

System.out.println("刪除賬務(wù)成功！");

}

/**

 * 刪除賬務(wù)

 * @param zwid

 */

public void deleteZhangWu(int zwid) {
zhangWuDao.deleteZhangWu(zwid);

}

/**

 * 刪除賬務(wù)

 * @param zwid

 */

public void deleteZhangWu(int zwid) {
String sql = "delete from gjp_zhangwu where zwid=?";

try {
qr.update(sql,zwid);

} catch (SQLExceptione) {
throw new RuntimeException(e);

}

}