[Java][mysql] 자바와 mysql을 연동한 은행 프로그램

은행 프로그램을 만들었는데, 크게 2가지로
1. 고객관리
2. 통장관리
를 간단하게 만들어 봤다.

로직은

이와같이 하려고 했으나, 입출금 내역부분은 추가 하지 않았다.
(업데이트 예정)

우선 고객정보와 통장정보만 만들었다.

Mysql

Java

package [account]

AccountDAO.java

package account;

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 dBUtil.DBUtil;

public class AccountDAO {
	// 1. 입출금 통장 개설
		public void insert(AccountDTO dto) { // [3] 입력된 클래스의 정보를
			// sql = value(accountNumber,customerNumber,accountPW,balance)
			String sql = "insert into account values (?,?,?,?)";
			try (Connection conn = DBUtil.getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
				// [4] 사용해서 쿼리문을 dto클래스 내부의 get메서드를 사용해서 private 변수들의 값들을 가져와서
				ps.setString(1, dto.getAccountNumber());
				ps.setInt(2, dto.getCustomerNumber());
				ps.setInt(3, dto.getAccountPW());
				ps.setLong(4, dto.getBalance());
				
				int count = ps.executeUpdate(); // [5] PreparedStatement클래스의 executeUpdate메서드를 통해 업데이트를 하게된다. * 참고로 ps는
												// conn으로 sql이 연동되어있음을 확인하자.
				if (count > 0) {
					System.out.println(count + "입력됨");
				} else {
					System.out.println("입력 실패");
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		}

		// 2. 통장 정보 보기 
		public AccountDTO getAccount(String accountNumber) {
			AccountDTO accountDTO = null;
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
		
			try {
				conn = DBUtil.getConnection();
				String sql = "select accountNumber,customerNumber,accountPW,balance from account where accountNumber = ?";
				ps = conn.prepareStatement(sql);
				ps.setString(1, accountNumber);
				
				rs = ps.executeQuery();
				//결과값 얻어오기 없는데이터를 검색했을때 에러나니까 조건문 사용
				if(rs.next()) {
					accountDTO = new AccountDTO();
					accountDTO.setAccountNumber(rs.getString(1)); // 뒤에 숫자는 컬럼순 (컬럼명 직접 지정해줘도 됨 "")
					accountDTO.setCustomerNumber(rs.getInt(2));
					accountDTO.setAccountPW(rs.getInt(3));
					accountDTO.setBalance(rs.getLong(4));
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				DBUtil.close(conn, ps, rs);
			}	
			return accountDTO;		
		}
}

AccountDTO.java

package account;

public class AccountDTO {
	private String accountNumber;
	private int customerNumber;
	private int accountPW;
	private long balance;
	public String getAccountNumber() {
		return accountNumber;
	}
	public void setAccountNumber(String accountNumber) {
		this.accountNumber = accountNumber;
	}
	public int getCustomerNumber() {
		return customerNumber;
	}
	public void setCustomerNumber(int customerNumber) {
		this.customerNumber = customerNumber;
	}
	public int getAccountPW() {
		return accountPW;
	}
	public void setAccountPW(int accountPW) {
		this.accountPW = accountPW;
	}
	public long getBalance() {
		return balance;
	}
	public void setBalance(long balance) {
		this.balance = balance;
	}
	@Override
	public String toString() {
		return "AccountDTO [accountNumber=" + accountNumber + ", customerNumber=" + customerNumber + ", accountPW="
				+ accountPW + ", balance=" + balance + "]";
	}
	
}

AccountService.java

package account;

import java.util.Scanner;

public class AccountService {
	AccountDAO dao = new AccountDAO();
	AccountDTO dto = new AccountDTO();
	 
	Scanner scan = new Scanner(System.in);

	public void accountView() {
		while (true) {
			System.out.println("\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n");
			System.out.println("안녕하세요 통장 관리 시스템 입니다.");
			System.out.println("번호는 입력해 원하는 서비스를 선택해주세요.");
			System.out.println("\n1. 입출금 통장 생성");
			System.out.println("2. 통장 정보 보기");
			System.out.println("3. 뒤로가기");
			System.out.println("");
			System.out.print("번호 : ");
			int num = scan.nextInt();
			if (num == 1) {
				System.out.println("추가");
				System.out.print("accountNumber : ");
				String accountNumber = scan.next();
				
				System.out.print("customerNumber : ");
				int customerNumber = scan.nextInt();
				
				System.out.print("accountPW : ");
				int accountPW = scan.nextInt();
				
				System.out.println("얼마를 넣으시겠습니까? : ");
				long balance = scan.nextLong();
				
				dto.setAccountNumber(accountNumber); // [1] 위에서 스캐너로 입력받은 값을 차례대로 dto클래스의 set메서드를 사용해 private한 변수에 넣고
				dto.setCustomerNumber(customerNumber);
				dto.setAccountPW(accountPW);
				dto.setBalance(balance);
				
				dao.insert(dto); // [2] set된 private 변수들을 dao의 insert메서드의 파라메터에 넣어준다. 
			} else if (num == 2) {
				// 계좌검색
				System.out.print("계좌번호 : ");
				String accountNumber = scan.next();
				dto = dao.getAccount(accountNumber);
				System.out.println(dto);
			} else if (num == 3) {
				break;
			} else {
				System.out.println("오타");
			}

		}
	}

}

package [customer]

CustomerDAO.java

package customer;

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

import dBUtil.DBUtil;

/*
 * 각 DAO 는 테이블 당 하나씩 있으면 된다.
 * Connector는 클래스를 하나 만들어서 참조하게 하면 된다.
 */

public class CustomerDAO {
	// 1. 고객 생성
	public void insert(CustomerDTO dto) { // [3] 입력된 클래스의 정보를
		// sql =
		// value(customerNumber,customerName,customerPhone,customerAddress,customerRRN,customerGender)
		String sql = "insert into customer (customerName, customerPhone, customerAddress, customerRRN, customerGender) values (?,?,?,?,?)";
		try (Connection conn = DBUtil.getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			// [4] 사용해서 쿼리문을 dto클래스 내부의 get메서드를 사용해서 private 변수들의 값들을 가져와서
			//ps.setInt(1, dto.getCustomerNumber());
			ps.setString(1, dto.getCustomerName());
			ps.setString(2, dto.getCustomerPhone());
			ps.setString(3, dto.getCustomerAddress());
			ps.setString(4, dto.getCustomerRRN());
			ps.setString(5, dto.getCustomerGender());
			int count = ps.executeUpdate(); // [5] PreparedStatement클래스의 executeUpdate메서드를 통해 업데이트를 하게된다. * 참고로 ps는
											// conn으로 sql이 연동되어있음을 확인하자.
			if (count > 0) {
				System.out.println(count + "입력됨");
			} else {
				System.out.println("입력 실패");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e1) {
			e1.printStackTrace();
		}
	}

	// 2. 고객 전체 보기
	public List<CustomerDTO> getCustomerList() {

		List<CustomerDTO> customerList = new ArrayList<CustomerDTO>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.getConnection();

			String sql = "select customerNumber,customerName,customerPhone,customerAddress,customerRRN,customerGender from customer"; // *로 하기보다는 그냥 나열
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			// 결과값 얻어오기 없는데이터를 검색했을때 에러나니까 조건문 사용
			while (rs.next()) {
				CustomerDTO customerDTO = new CustomerDTO(); // 한 행씩 정보를 저장해서 리스트에 객체를 넣어야 하니까 매 반복문 마다 DeptDTO객체를 만들어야함
				customerDTO.setCustomerNumber(rs.getInt(1)); // 뒤에 숫자는 컬럼순 (컬럼명 직접 지정해줘도 됨 "")
				customerDTO.setCustomerName(rs.getString(2));
				customerDTO.setCustomerPhone(rs.getString(3));
				customerDTO.setCustomerAddress(rs.getString(4));
				customerDTO.setCustomerRRN(rs.getString(5));
				customerDTO.setCustomerGender(rs.getString(6));

				customerList.add(customerDTO);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return customerList;
	}
	// 3. 고객 검색 하기
	public CustomerDTO getcustomer(int customerNumber) {
		CustomerDTO customerDTO = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
	
		try {
			conn = DBUtil.getConnection();
			String sql = "select customerNumber,customerName,customerPhone,customerAddress,customerRRN,customerGender from customer where customerNumber = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, customerNumber);
			rs = ps.executeQuery();
			//결과값 얻어오기 없는데이터를 검색했을때 에러나니까 조건문 사용
			if(rs.next()) {
				customerDTO = new CustomerDTO();
				customerDTO.setCustomerNumber(rs.getInt(1)); // 뒤에 숫자는 컬럼순 (컬럼명 직접 지정해줘도 됨 "")
				customerDTO.setCustomerName(rs.getString(2));
				customerDTO.setCustomerPhone(rs.getString(3));
				customerDTO.setCustomerAddress(rs.getString(4));
				customerDTO.setCustomerRRN(rs.getString(5));
				customerDTO.setCustomerGender(rs.getString(6));
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(conn, ps, rs);
		}	
		return customerDTO;		
	}
	// 4. 고객 삭제
	public void deleteCustomer(CustomerDTO dto) {
		String sql = "delete from customer where customerNumber = ?";
		try (Connection conn = DBUtil.getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setInt(1, dto.getCustomerNumber());
			int count = ps.executeUpdate();
			if (count > 0) {
				System.out.println(count + "입력됨");
			} else {
				System.out.println("입력 실패");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e1) {
			e1.printStackTrace();
		}
	}
}

CustomerDTO.java

package customer;

public class CustomerDTO {
	private int customerNumber;
	private String customerName;
	private String customerPhone;
	private String customerAddress;
	private String customerRRN;
	private String customerGender;
	
	public int getCustomerNumber() {
		return customerNumber;
	}
	public void setCustomerNumber(int customerNumber) {
		this.customerNumber = customerNumber;
	}
	
	public String getCustomerName() {
		return customerName;
	}
	public void setCustomerName(String customerName) {
		this.customerName = customerName;
	}
	public String getCustomerPhone() {
		return customerPhone;
	}
	public void setCustomerPhone(String customerPhone) {
		this.customerPhone = customerPhone;
	}
	public String getCustomerAddress() {
		return customerAddress;
	}
	public void setCustomerAddress(String customerAddress) {
		this.customerAddress = customerAddress;
	}
	public String getCustomerRRN() {
		return customerRRN;
	}
	public void setCustomerRRN(String customerRRN) {
		this.customerRRN = customerRRN;
	}
	public String getCustomerGender() {
		return customerGender;
	}
	public void setCustomerGender(String customerGender) {
		this.customerGender = customerGender;
	}
	
	@Override
	public String toString() {
		return "customerDTO [customerNumber=" + customerNumber + ", customerName=" + customerName + ", customerPhone="
				+ customerPhone + ", customerAddress=" + customerAddress + ", customerRRN=" + customerRRN
				+ ", customerGender=" + customerGender + "]";
	}
}

CustomerService.java

package customer;

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

public class CustomerService {
	CustomerDAO dao = new CustomerDAO();
	CustomerDTO dto = new CustomerDTO();
	Scanner scan = new Scanner(System.in);

	public void customerView() {
		while (true) {
			System.out.println("\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n");
			System.out.println("안녕하세요 국민은행 입니다.");
			System.out.println("번호는 입력해 원하는 서비스를 선택해주세요.");
			System.out.println("\n1. 고객생성");
			System.out.println("2. 고객 전체 검색");
			System.out.println("3. 고객 검색");
			System.out.println("4. 고객 삭제");
			System.out.println("5. 뒤로 가기");
			System.out.println("");
			System.out.print("번호 : ");
			int num = scan.nextInt();
			if (num == 1) {
				System.out.println("고객생성");

				System.out.print("customerName : ");
				String customerName = scan.next();

				System.out.print("customerPhone : ");
				String customerPhone = scan.next();
				
				System.out.print("customerAddress : ");
				String customerAddress = scan.next();

				System.out.print("customerRRN : ");
				String customerRRN = scan.next();
				
				System.out.print("customerGender : ");
				String customerGender = scan.next();
				
				// dto.setCustomerNumber(customerNumber);
				dto.setCustomerName(customerName);
				dto.setCustomerPhone(customerPhone);
				dto.setCustomerAddress(customerAddress);
				dto.setCustomerRRN(customerRRN);
				dto.setCustomerGender(customerGender);
				dao.insert(dto);
				
			} else if (num == 2) {
				List<CustomerDTO> dtoList = dao.getCustomerList();
				for (CustomerDTO i : dtoList) {
					System.out.println(i);
				}
			} else if (num == 3) {
				System.out.print("고객 번호 : ");
				int customerNumber = scan.nextInt();
				dto = dao.getcustomer(customerNumber);
				System.out.println(dto);
			} else if (num == 4) {
				System.out.print("고객 번호(삭제): ");
				int customerNumber = scan.nextInt();
				dto.setCustomerNumber(customerNumber);
				dao.deleteCustomer(dto);
			} else if (num == 5) {
				break;
			} else {
				System.out.println("오타");
			}

		}
	}
}

package [dBUtil]

DBUtil.java

package dBUtil;

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

public class DBUtil {	
	// 리턴으로 커넥션을 받아와야 한다.
	static String URL = "jdbc:mysql://localhost:3306/testdb?serverTimezone=UTC";
	static String USER = "joe";
	static String PASSWORD = "1234";
	static String DRIVER_NAME = "com.mysql.cj.jdbc.Driver";
	// 접속
	public static Connection getConnection() throws Exception{
		Connection conn = null;
		Class.forName(DRIVER_NAME);
		conn = DriverManager.getConnection(URL, USER, PASSWORD);
		return conn;
	}
	// 접속종료
	public static void close(Connection conn) {
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	public static void close(Connection conn, PreparedStatement ps) {
		if(ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		close(conn);
	}
	public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
		if(rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			close(conn,ps);
		}
	}
}

package [main]

Main.java

package main;

import java.util.Scanner;

import account.AccountService;
import atm.AtmService;
import customer.CustomerService;


public class Main {

	public static void main(String[] args) {
		CustomerService customerService = new CustomerService();
		AccountService accountService = new AccountService();		
		Scanner scan = new Scanner(System.in);
		
		while(true) {
			System.out.println("\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n");
			System.out.println("안녕하세요 국민은행 입니다.");
			System.out.println("번호는 입력해 원하는 서비스를 선택해주세요.");
			System.out.println("\n1. 고객관리");
			System.out.println("2. 통장개설");
			System.out.println("3. 프로그램 종료");
			System.out.println("");
			System.out.print("번호 : ");
			int num = scan.nextInt();
			if(num == 1) {
				customerService.customerView();
			} else if (num == 2) {
				accountService.accountView();
			} else if (num == 3) {
				scan.close();
				System.exit(0);
			} else {
				System.out.println("오타");
			}	
		}
	}
}

댓글 달아 주시면 오전 9시 이전에 답글 달아드려요~!

'workSpace > JAVA' 카테고리의 다른 글

basic info for HTML (0)	2021.03.03
[Java][mysql][Swing] 연동한 간단한 미니 프로젝트 - GUI game - (GUI 란?) (0)	2020.12.09
[Java][DBUtil][DAO][DTO] 방식 기본 구현법 (0)	2020.12.07
[Java][JDBC][Insert][Update][Delete]자바에 MYSQL(workbench) 데이터베이스에 추가, 수정, 변경 하기 (0)	2020.12.07
[Java][JDBC][Connection]자바에 MYSQL(workbench) 데이터베이스 연동하기 (0)	2020.12.07

Mysql

Java

'workSpace > JAVA' 카테고리의 다른 글

티스토리툴바