21.02.22 TIL(D+29 JDBC 활용 AccountBook 만들기)

AccoutBook UML 다이어그램

 

 

AccoutBook UML 다이어그램

 

MainClass

package main;


import java.util.Scanner;

import cls.DeleteCls;
import cls.InsertCls;
import cls.SelectCls;
import cls.UpdateCls;
import dao.AccountDao;

public class MainClass {

	
	public static void main(String[] args) {
		/*
		 	추가하기
		 	삭제하기
		 	날짜로 검색하기
		 	제목으로 검색하기
		 	수정하기
		 	모두 출력
		 	-----------
		 	합계 출력
		*/
		
		Scanner sc = new Scanner(System.in);
      		InsertCls ic = new InsertCls();
		DeleteCls dc = new DeleteCls();
		UpdateCls uc = new UpdateCls();
		SelectCls scs = new SelectCls();
		
out: while(true) {
			
		System.out.println("---------메뉴---------");
		System.out.println("1. 추가");
		System.out.println("2. 삭제");
		System.out.println("3. 날짜로 검색");
		System.out.println("4. 용도로 검색");
		System.out.println("5. 수정");
		System.out.println("6. 모두 출력");
		System.out.println("7. 합계 출력");
		System.out.println("8. 종료");
		
		int count = sc.nextInt();
		
		
		switch(count) {
			case 1 :
				 ic.insertData();
				 break;
			case 2 :
				 dc.deleteData();
				 break;
			case 3 :
				 scs.selectDate();
				 break;
			case 4 :
				 scs.selectUse();
				 break;
			case 5 :
				 uc.updateData();	
				 break;
			case 6 :
				 scs.selectAll();
				 break;
			case 7 :
				 scs.selectSum();
				 break;
			case 8 :
				 break out;
		 
		}
		
	}
		
	}

}

 

 

 

InsertCls

package cls;

import java.util.Scanner;

import dao.AccountDao;
import dto.AccountDto;

public class InsertCls {
	
	Scanner sc = new Scanner(System.in);
	
	public void insertData() {

		System.out.print("용도 = ");
		String use = sc.next();
		
		System.out.print("분류(수입/지출) = ");
		String classify = sc.next();
		
		System.out.print("금액 = ");
		int money = sc.nextInt();
		
		System.out.print("내용 = ");
		String memo = sc.next();
		
		AccountDao dao = AccountDao.getInstance();
		boolean b = dao.insert(new AccountDto(null, use, classify, money, memo));
		if(b) {
			System.out.println("데이터가 추가되었습니다.");
			return;
		} else {
			System.out.println("데이터가 추가되지 않았습니다.");
		}
	

	}
	

}

 

 

 

DeleteCls

package cls;

import java.util.Scanner;

import dao.AccountDao;
import dto.AccountDto;

public class DeleteCls {
	
	Scanner sc = new Scanner(System.in);

	public void deleteData() {
	
		System.out.print("삭제 날짜 (XX/XX/XX) = ");
		String date = sc.next();
		
		AccountDao dao = AccountDao.getInstance();
		boolean b = dao.delete(new AccountDto(date));
		if(b) {
			System.out.println("데이터가 삭제되었습니다.");
			return;
		} else {
			System.out.println("데이터가 삭제되지 않았습니다.");
		}
		
	}
		
}

 

 

UpdateCls

package cls;

import java.util.Scanner;

import dao.AccountDao;
import dto.AccountDto;

public class UpdateCls {
	
	Scanner sc = new Scanner(System.in);
	
	public void updateData() {
		
		System.out.print("수정할 데이터 용도 = ");
		String use = sc.next();
		
		System.out.print("수정 금액 = ");
		int money = sc.nextInt();
		
		AccountDao dao = AccountDao.getInstance();
		boolean b = dao.update(new AccountDto(money, use));
		if(b) {
			System.out.println("데이터가 수정되었습니다.");
			return;
		} else {
			System.out.println("데이터가 수정되지 않았습니다.");
		}
		
	}
}

 

 

SelectCls

package cls;

import java.util.List;
import java.util.Scanner;

import dao.AccountDao;
import dto.AccountDto;

public class SelectCls {
	
	Scanner sc = new Scanner(System.in);

	public void selectDate() {
		
		System.out.print("검색 날짜 (XX/XX/XX) = ");
		String search = sc.next();
		
		AccountDao dao = AccountDao.getInstance();
		List<AccountDto> list = dao.selectDate(search);
		
		
		for (int i = 0; i < list.size(); i++) {
			AccountDto dto = list.get(i);
			System.out.println(dto.toString());
		}
	
	}
	
	
	public void selectUse() {
			
			System.out.print("사용 용도 = ");
			String search = sc.next();
			
			AccountDao dao = AccountDao.getInstance();
			List<AccountDto> list = dao.selectUse(search);
			
			
			for (int i = 0; i < list.size(); i++) {
				AccountDto dto = list.get(i);
				System.out.println(dto.toString());
			}
		
	}
		
	
	public void selectAll() {
		
		System.out.print("모든 사용처 출력 -------------");
		
		AccountDao dao = AccountDao.getInstance();
		List<AccountDto> list = dao.selectAll();
		
		
		for (int i = 0; i < list.size(); i++) {
			AccountDto dto = list.get(i);
			System.out.println(dto.toString());
		}
	
	}
	
	public void selectSum() {
		
		System.out.print("수입/지출별 합계 -------");
		
		AccountDao dao = AccountDao.getInstance();
		List<AccountDto> list = dao.selectSum();
		
		
		for (int i = 0; i < list.size(); i++) {
			AccountDto dto = list.get(i);
			System.out.println(dto.sumToString());
		}
	
	}
	

}

 

 

AccountDao

package dao;

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

import db.DBClose;
import db.DBConnection;
import dto.AccountDto;

// singleton
public class AccountDao {
	
	Scanner sc = new Scanner(System.in);
	private static AccountDao dao = null;
	

	private AccountDao() {
	}
	
	public static AccountDao getInstance() {
		if(dao == null) {
			dao = new AccountDao();
		}
		return dao;
	}
	
	
	
	public boolean insert(AccountDto dto) {
		
		String sql = " INSERT INTO ACCOUNTBOOK(DATE_TIME, USE, CLASSIFY, MONEY, MEMO) " // 날짜, 용도, 분류(수입/지출), 금액, 내용
		+	 " VALUES(SYSDATE, ?, ?, ?, ?) "; 
		
		System.out.println(sql);
		
		Connection conn = DBConnection.getConnection();
		PreparedStatement psmt = null;
	
		
		int count = 0;
		
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, dto.getUse());	//setString(sql문의 ?위치, value값) 
			psmt.setString(2, dto.getClassify());
			psmt.setInt(3, dto.getMoney());
			psmt.setString(4, dto.getMemo());
			count = psmt.executeUpdate();	// insert문에서는 반영된 레코드의 개수를 반환
			
			System.out.println("추가 완료");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("추가 실패");
		} finally {
			
			try {
				if(psmt != null) {
					psmt.close();
				}
				if(conn != null) {
					conn.close();
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			
		}
		
		return count>0?true:false;	
	
	
	}
	
	public boolean delete(AccountDto dto) {
		
		String sql = " DELETE FROM ACCOUNTBOOK " // 날짜, 용도, 분류(수입/지출), 금액, 내용
		+	 " WHERE TO_CHAR(DATE_TIME, 'YY/MM/DD') = ? "; 
		
		System.out.println(sql);
		
		Connection conn = DBConnection.getConnection();
		PreparedStatement psmt = null;

		
		int count = 0;
		
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, dto.getDate_time());

			count = psmt.executeUpdate();
			
			System.out.println("삭제 완료");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("삭제 실패");
		} finally {
			
			try {
				if(psmt != null) {
					psmt.close();
				}
				if(conn != null) {
					conn.close();
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			
		}
		
		return count>0?true:false;	
		
		}
	
	
	public boolean update(AccountDto dto) {
		
		String sql = " UPDATE ACCOUNTBOOK "
				+ " SET MONEY = ? " // 날짜, 용도, 분류(수입/지출), 금액, 내용
		+	 " WHERE USE = ? "; 
		
		System.out.println(sql);
		
		Connection conn = DBConnection.getConnection();
		PreparedStatement psmt = null;

		
		int count = 0;
		
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, dto.getMoney());
			psmt.setString(2, dto.getUse());

			count = psmt.executeUpdate();
			
			System.out.println("수정 완료");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("수정 실패");
		} finally {
			
			try {
				if(psmt != null) {
					psmt.close();
				}
				if(conn != null) {
					conn.close();
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			
		}
		
		return count>0?true:false;	
		
		
}
	
	public List<AccountDto> selectDate(String date) {
		String sql = " SELECT DATE_TIME, USE, CLASSIFY, MONEY, MEMO "
				+ " FROM ACCOUNTBOOK "
				+ " WHERE TO_CHAR(DATE_TIME, 'YY/MM/DD') = ? ";
		
		System.out.println(sql);
		
		
		Connection conn = DBConnection.getConnection();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		
		List<AccountDto> list = new ArrayList<AccountDto>();		
		
		
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, date);
			rs = psmt.executeQuery();
			
			while(rs.next()) {	
				String date_time = rs.getString("DATE_TIME");
				String use = rs.getString("USE");
				String classify = rs.getString("CLASSIFY");
				int money = rs.getInt("MONEY");
				String memo = rs.getString("MEMO");
				
				AccountDto dto = new AccountDto(date_time, use, classify, money, memo);
				
				list.add(dto);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		} finally {
			DBClose.close(conn, psmt, rs);
		}
		
		return list;
		
	}
	
	
	public List<AccountDto> selectUse(String u) {
		String sql = " SELECT DATE_TIME, USE, CLASSIFY, MONEY, MEMO "
				+ " FROM ACCOUNTBOOK "
				+ " WHERE USE = ? ";
		
		System.out.println(sql);
		
		
		Connection conn = DBConnection.getConnection();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		
		List<AccountDto> list = new ArrayList<AccountDto>();		
		
		
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, u);
			rs = psmt.executeQuery();
			
			while(rs.next()) {	
				String date_time = rs.getString("DATE_TIME");
				String use = rs.getString("USE");
				String classify = rs.getString("CLASSIFY");
				int money = rs.getInt("MONEY");
				String memo = rs.getString("MEMO");
				
				AccountDto dto = new AccountDto(date_time, use, classify, money, memo);
				
				list.add(dto);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		} finally {
			DBClose.close(conn, psmt, rs);
		}
		
		return list;
		
	}
	
	
	public List<AccountDto> selectAll() {
		String sql = " SELECT DATE_TIME, USE, CLASSIFY, MONEY, MEMO "
				+ " FROM ACCOUNTBOOK ";
		
		System.out.println(sql);
		
		
		Connection conn = DBConnection.getConnection();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		
		List<AccountDto> list = new ArrayList<AccountDto>();		
		
		
		try {
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();
			
			while(rs.next()) {	
				String date_time = rs.getString("DATE_TIME");
				String use = rs.getString("USE");
				String classify = rs.getString("CLASSIFY");
				int money = rs.getInt("MONEY");
				String memo = rs.getString("MEMO");
				
				AccountDto dto = new AccountDto(date_time, use, classify, money, memo);
				
				list.add(dto);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		} finally {
			DBClose.close(conn, psmt, rs);
		}
		
		return list;
		
	}
	
	public List<AccountDto> selectSum() {
		String sql = " SELECT CLASSIFY, SUM(MONEY) "
				+ " FROM ACCOUNTBOOK "
				+ " GROUP BY CLASSIFY ";
		
		System.out.println(sql);
		
		
		Connection conn = DBConnection.getConnection();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		
		List<AccountDto> list =  new ArrayList<AccountDto>();
		
		
		try {
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();
			
			while(rs.next()) {	
				String classify = rs.getString("CLASSIFY");
				int money = rs.getInt("SUM(MONEY)");

				
				AccountDto dto  = new AccountDto(classify, money);
				list.add(dto);

			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		} finally {
			DBClose.close(conn, psmt, rs);
		}
		
		return list;
		
	}


}

 

 

AccountDto

package dto;

public class AccountDto {
	
	private String date_time;
	private String use;
	private String classify;
	private int money;
	private String memo;
	private int sumMoney;
	
	public AccountDto(String date_time, String use, String classify, int money, String memo) {
		super();
		this.date_time = date_time;
		this.use = use;
		this.classify = classify;
		this.money = money;
		this.memo = memo;
	}

	public AccountDto(int money, String use) {
		this.money = money;
		this.use = use;
	}
	
	
	public AccountDto(String classify, int sumMoney) {
		this.classify = classify;
		this.sumMoney = sumMoney;
	}
	
	
	
	public AccountDto(String date_time) {
		this.date_time = date_time;
	}
	

	public String getDate_time() {
		return date_time;
	}

	public void setDate_time(String date_time) {
		this.date_time = date_time;
	}

	public String getUse() {
		return use;
	}

	public void setUse(String use) {
		this.use = use;
	}

	public String getClassify() {
		return classify;
	}

	public void setClassify(String classify) {
		this.classify = classify;
	}

	public int getMoney() {
		return money;
	}

	public void setMoney(int money) {
		this.money = money;
	}

	public String getMemo() {
		return memo;
	}

	public void setMemo(String memo) {
		this.memo = memo;
	}

	@Override
	public String toString() {
		return "AccountDto [date_time=" + date_time + ", use=" + use + ", classify=" + classify + ", money=" + money
				+ ", memo=" + memo + "]";
	}
	
	public String sumToString() {
		return classify + " : sum = " + sumMoney;
	}


}

 

 

DBConnection

package db;

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

public class DBConnection {

	public static void initConnection() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			System.out.println("Driver Loading Success");
			
		} catch (ClassNotFoundException e) {
			System.out.println("Driver가 없습니다");
		}
	}
	
	public static Connection getConnection() {
		
		Connection conn = null;
		
		try {
			conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.231:1521:xe", "hr", "hr");
		
			System.out.println("Oracle Connection Success");
			
		} catch (SQLException e) {
			System.out.println("DB를 연결하지 못했습니다");
		}
		
		return conn;		
	}
}

 

DBClose

package db;

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

public class DBClose {

	public static void close(Connection conn, Statement stmt, ResultSet rs) {
		
		try {
			if(conn != null) {
				conn.close();
			}
			if(stmt != null) {
				stmt.close();
			}
			if(rs != null) {
				rs.close();
			}			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}