DB- 실습04

2021-12-23

create table tbl_account(
  ano int,
  aname varchar(20),
  balance int,
  primary key(ano)
);

create table tbl_transaction(
  tno int, /*거래번호*/
  ano int, /*계좌번호*/
  tdate timestamp default sysdate, /*거래일*/
  type char(4), /*입금/출금*/
  amount int, /*입금액/출금액*/
  primary key(tno),
  foreign key(ano) references tbl_account(ano)
);

create sequence seq_account start with 1111 increment by 1;
create sequence seq_transaction start with 1 increment by 1;

/*1*/
insert into tbl_account(ano, aname, balance)
values(seq_account.nextval, '홍길동', 1000);

/*5*/
insert into tbl_account(ano, aname, balance)
values(seq_account.nextval, '이몽룡', 1000);
select * from tbl_account;


/*2*/
insert into tbl_transaction(tno, ano, type, amount)
values(seq_transaction.nextval, 1111, 'IN', 1000);

/*3*/
insert into tbl_transaction(tno, ano, type, amount)
values(seq_transaction.nextval, 1111, 'OUT', 500);

/*6*/
insert into tbl_transaction(tno, ano, type, amount)
values(seq_transaction.nextval, 1112, 'IN', 1000);

/*7*/
insert into tbl_transaction(tno, ano, type, amount)
values(seq_transaction.nextval, seq_account.currval, 'IN', 1000);

select * from tbl_transaction;
delete from tbl_transaction
where TNO= '18';

/*4*/
update tbl_account set balance=balance-500 where ano=1111;

/*8*/
update tbl_account set balance=balance+1000 where ano=1112;


create view view_transaction as
(select t.*, aname, balance 
from tbl_transaction t, tbl_account a
where t.ano=a.ano);

select * from view_transaction
order by ano , tdate desc;

select * from tbl_transaction
where ano='1111' 
order by tno desc;

commit;

select seq_account.currval from dual;

insert into tbl_account(ano, aname, balance)
values(seq_account.nextval, '강감찬',  1000);
                                                                                                                                                                                                               
select * from tbl_account;

Main

public class Main {

	public static void main(String[] args) {
		//Connection CON=Database.CON;
		Scanner s = new Scanner(System.in);
		boolean run =true;
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		DecimalFormat df = new DecimalFormat("#,###원");
		TransactionDAO tdao = new TransactionDAO();
		AcoountDAO adao= new AcoountDAO();
			while(run) {
				System.out.println("");
				System.out.println("┌────────────────────────┐");
				System.out.print("│1.계좌생성  ");
				System.out.print("2.계좌조회  ");
				System.out.print("3.입금  ");
				System.out.print("4.출금  ");
				System.out.print("5.계좌목록  ");
				System.out.print("0.종료    │\n");
				System.out.println("└────────────────────────┘");
				System.out.print("선택>");
				String menu = s.nextLine();
				switch(menu) {
				case "1": 
					Account avo = new Account();
					System.out.print("계좌주>"); avo.setAname(s.nextLine());
					System.out.print("초기입금액>"); String amount = s.nextLine();
					avo.setBalance(Integer.parseInt(amount));
					boolean result = adao.insert(avo);
					if(result==true) {
						System.out.println("계좌생성완료!");
					}else {
						System.out.println("계좌생성실패!");
					}
					break;
				case "2":
					System.out.print("계좌번호>");
		            String ano=s.nextLine();
		            avo=adao.read(Integer.parseInt(ano));
		            if(avo.getAname()==null) {
		               System.out.println("해당 계좌번호가 존재하지 않습니다.");
		            }else {
		               System.out.println("계좌주명:" + avo.getAname());
		               System.out.println("잔액:" + df.format(avo.getBalance()));
		               ArrayList<Transaction> tarray=tdao.list(Integer.parseInt(ano));
		               System.out.println("────────────────────────");
		               for(Transaction t:tarray) {
			                  String date=sdf.format(t.getTdate());
			                  amount=df.format(t.getAmount());
			                  String type = t.getType().trim().equals("IN")? "입금":"출금"; 
			                  System.out.printf("%d\t%s\t%s\t%s\n",
			                        t.getTno(),
			                        date,
			                        type,
			                        amount);
		               }
		            }
	
					break;
				case "3":
					System.out.print("계좌번호>");ano=s.nextLine();
		            avo=adao.read(Integer.parseInt(ano));
		            if(avo.getAname()==null) {
		               System.out.println("해당 계좌번호가 존재하지 않습니다.");
		            }else {
		               System.out.println("계좌주명:" + avo.getAname());
		               System.out.println("잔액:" + df.format(avo.getBalance()));
		               System.out.print("입금액>");amount=s.nextLine();
		               Transaction tvo=new Transaction();
		               tvo.setAno(Integer.parseInt(ano));
		               tvo.setType("IN");
		               tvo.setAmount(Integer.parseInt(amount));
		               if(tdao.input(tvo)==true) { //입금이 성공하면 잔액변경
		                  adao.update(Integer.parseInt(ano), Integer.parseInt(amount));
		                  System.out.println("입금성공!");
		               };
		            }

					break;
				case "4":
					 System.out.print("계좌번호>");ano=s.nextLine();
			            avo=adao.read(Integer.parseInt(ano));
			            if(avo.getAname()==null) {
			               System.out.println("해당 계좌번호가 존재하지 않습니다.");
			            }else {
			               System.out.println("계좌주명:" + avo.getAname());
			               System.out.println("잔액:" + df.format(avo.getBalance()));
			               System.out.print("출금액>");amount=s.nextLine();
			               Transaction tvo=new Transaction();
			               tvo.setAno(Integer.parseInt(ano));
			               tvo.setType("OUT");
			               tvo.setAmount(Integer.parseInt(amount));
			               if(tdao.input(tvo)==true) { //출금이 성공하면 잔액변경
			                  adao.update(Integer.parseInt(ano), Integer.parseInt(amount)*-1);
			                  System.out.println("출금성공!");
			               };
			            }

					break;
				case "5":
					ArrayList<Account> array = adao.list();
					System.out.printf("%s\t%s\t%s\n","계좌번호", "계좌주","잔액" );
		               System.out.println("────────────────────────");

					for(Account a:array) {
		
						System.out.printf("%s\t%s\t%s\n",
								a.getAno(), a.getAname(),df.format(a.getBalance()));
					}
					break;
				case "0":
					run= false;
					break;
				default:
					System.out.println("0~4번 메뉴를 선택하세요!");
					break;
				}
			}
			System.out.println("프로그램종료!");
	}
}

Account

public class Account {

	private int ano;
	private String aname;
	private int balance;
	
	public int getAno() {
		return ano;
	}
	public void setAno(int ano) {
		this.ano = ano;
	}
	public String getAname() {
		return aname;
	}
	public void setAname(String aname) {
		this.aname = aname;
	}
	public int getBalance() {
		return balance;
	}
	public void setBalance(int balance) {
		this.balance = balance;
	}
	@Override
	public String toString() {
		return "Account [ano=" + ano + ", aname=" + aname + ", balance=" + balance + "]";
	}
}

AccountDAO

import java.sql.*;
import java.util.ArrayList;


public class AcoountDAO {
	
	public ArrayList<Account> list() {
		ArrayList<Account> array = new ArrayList<> ();
		try {
			String sql = "select * from tbl_account";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {  //rs에 next값을 하나씩 가져와서 true인동안 반복
				Account vo = new Account();
				vo.setAno(rs.getInt("ano"));
				vo.setAname(rs.getString("aname"));
				vo.setBalance(rs.getInt("balance"));
				array.add(vo);
			}
		}catch(Exception e) {
			System.out.println("Account list 오류 :"+ e.toString());
		}
		return array;
	}
	
	//계좌조회
	public Account read(int ano) {
		Account vo = new Account();
		try {
			String sql = "select * from tbl_account where ano = ?";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setInt(1, ano);
			ResultSet rs=ps.executeQuery();
			if(rs.next()) {
				vo.setAno(rs.getInt("ano"));
				vo.setAname(rs.getString("aname"));
				vo.setBalance(rs.getInt("balance"));
			}
		}catch(Exception e) {
			System.out.println("Account read 오류:" +e.toString());
		}
		return vo;
	}
	
	//계좌생성
	public boolean insert (Account vo) {
		boolean success =false;
		try {
			//계좌생
			String sql = "insert into tbl_account(ano,aname,balance) values(seq_account.nextval,?,?)";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setString(1,vo.getAname());
			ps.setInt(2, vo.getBalance());
			ps.execute();
			//입금
			sql="insert into tbl_transaction(tno, ano, type, amount) ";
	        sql+= "values(seq_transaction.nextval,seq_account.currval,'IN',?)";
	        ps = Database.CON.prepareStatement(sql);
			ps.setInt(1, vo.getBalance());
			ps.execute();
			
			success=true;
		}catch(Exception e) {
			System.out.println("Account insert 오류:" +e.toString());
		}
		return success;
	}
	
	//잔액변경
	public void update(int ano, int amount) {
		try {
			String sql="update tbl_account set balance=balance+? where ano=?";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setInt(1, amount);
			ps.setInt(2, ano);
			ps.execute();
	}catch(Exception e) {
		System.out.println("Account update 오류:" +e.toString());
	}
	}
}

Transaction

public class Transaction {

	private int tno;
	private int ano;
	private Date tdate;
	private String type;
	private int amount;
	
	public int getTno() {
		return tno;
	}
	public void setTno(int tno) {
		this.tno = tno;
	}
	public int getAno() {
		return ano;
	}
	public void setAno(int ano) {
		this.ano = ano;
	}
	public Date getTdate() {
		return tdate;
	}
	public void setTdate(Date tdate) {
		this.tdate = tdate;
	}
	
	public String getType() { 
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}
	public int getAmount() {
		return amount;
	}
	public void setAmount(int amount) {
		this.amount = amount;
	}
	@Override
	public String toString() {
		return "Transaction [tno=" + tno + ", ano=" + ano + ", tdate=" + tdate + ", type=" + type + ", amount=" + amount
				+ "]";
	}
}

TransactionDAO

import java.sql.*;
import java.util.ArrayList;

public class TransactionDAO {
	//거래목록
	public ArrayList<Transaction> list(int ano) {
		ArrayList<Transaction> array= new ArrayList<Transaction>();
		try {
			String sql = "select * from tbl_transaction where ano=? order by Tno desc";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setInt(1,ano);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				Transaction vo = new Transaction();
				vo.setTno(rs.getInt("tno"));
				vo.setTdate(rs.getTimestamp("tdate"));
				vo.setType(rs.getString("type"));
				vo.setAmount(rs.getInt("amount"));
				array.add(vo);
			}
		}catch(Exception e) {
			System.out.println("transaction list 오류 :" +e.toString());
		}
		return array;
	}
	
	//입금
		public boolean input(Transaction vo ) {
			boolean success = false;
			try {
				String sql = "insert into tbl_transaction(tno,ano,type,amount) values(seq_transaction.nextval,?,?,?)";
				PreparedStatement ps = Database.CON.prepareStatement(sql);
				ps.setInt(1, vo.getAno());
				ps.setString(2,vo.getType());
				ps.setInt(3, vo.getAmount());
				ps.execute();
			}catch(Exception e) {
				System.out.println("Account input 오류:" +e.toString());
			}
			return success;
		}
		
}

+내일아침에 한번더 볼것.

Twitter Facebook LinkedIn

KangKkaem

DB- 실습04

공유하기

댓글남기기

참고

CSS - Grid

CSS - Flex

[오류]Several ports (8005, 8080, 8009) required by Tomcat v7.0 Server at localhost are already in use.

git - github에 파일올리고 내려받기