Quiz) 결제 관련 정보 입력받고 목록 출력하기

구현할 기능

• 카드 번호 / 결제 금액 / 비밀번호 입력받기
• 결제 후 잔액 출력하기
• 전체 목록 출력하기
  
  
  

MySQL

create database card;
use card;

create table cardinfo(
num 		int,
cardnum 	int 	primary key,
name 		char(5),
cardpw 		int,
payment 	int
);

desc cardinfo;

select * from cardinfo;

insert into cardinfo
values
(1, 12345678, '밤이', 1234, 20000),
(2, 87654321, '이밤', 5678, 20000);

Main Class

package pack_Card;

import java.util.Scanner;

public class Main {

	public static void main(String[] args) {
		
		
		Scanner scanner = new Scanner(System.in);
		
		System.out.print("1. 카드 번호 입력 \t 2. 카드 정보 조회");
		System.out.print("\n번호 입력 : ");
		
		int code = scanner.nextInt();
		
		
		if (code == 1) {
			System.out.print("카드 번호를 입력해주세요. : ");
			int cardNum = scanner.nextInt();
			System.out.print("결제 금액을 입력해주세요 : ");
			int payment = scanner.nextInt();
			System.out.print("비밀번호를 입력해주세요 : ");
			int cardpw = scanner.nextInt();
			
			Insert insert = new Insert(cardNum, payment, cardpw);
			insert.mtd_Insert();
			
			
		} else if (code == 2) {
			System.out.print("[전체 목록입니다.]\n");
			List list = new List();
			list.mtd_List();
			
			
		} else {
			System.out.println("번호를 다시 입력해주세요.");
		}
		
		scanner.close();
	}

}

Conn.java

package pack_Card;

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

public class Conn {

	Connection conn = null;
	
	public void mtdConn() {
		
		try {
			
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			String url = "jdbc:mysql://localhost:3308/card?";
			url += "useSSL=false&"; 			
			url += "serverTimezone=Asia/Seoul&"; 
			url += "useUnicode=true&";			
			url += "characterEncoding=UTF-8&"; 
			url += "allowPublicKeyRetrieval=true";
			
			String uid = "root";
			String upw = "1234";
			
			conn = DriverManager.getConnection(url, uid, upw);
			
		} catch(ClassNotFoundException e){
			System.out.println(e.getMessage());
		} catch(SQLException e) {		
			System.out.println(e.getMessage());
		}
	}
}

Insert.java

package pack_Card;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;

public class Insert extends Conn {

	private int cardNum;
	private int payment;
	private int cardpw;
	
	public Insert(int cardNum, int payment, int cardpw) {
		this.cardNum = cardNum;
		this.payment = payment;
		this.cardpw = cardpw;
	}
	
	public void mtd_Insert() {
		
		
		mtdConn();
		
		Statement stmt = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		
		try {

			String sql = "select cardpw from cardinfo where cardnum = ?";
			pstmt = conn.prepareStatement(sql);		
			pstmt.setInt(1, cardNum);
			res = pstmt.executeQuery();
			
			if (res.next()) {		
				if (res.getInt(1) == cardpw) {
					
					String paysql = "update cardinfo set payment = payment -? where cardnum = ?";
					pstmt = conn.prepareStatement(paysql);		
					pstmt.setInt(1, payment);
					pstmt.setInt(2, cardNum);
					
					pstmt.executeUpdate();				
					
					System.out.println("결제가 완료되었습니다.");
					
					System.out.println("===== 잔액 =====");
					
					stmt = conn.createStatement
							(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
					
					String balanceSql = "select payment from cardinfo where cardnum = " + this.cardNum;		
					res = stmt.executeQuery(balanceSql);
				    
					int balance = 0;
					DecimalFormat decimalFormat = new DecimalFormat("###,###");
					
					res.beforeFirst();
					while(res.next()) {
						balance = res.getInt("payment");

						System.out.println("잔액 : " + decimalFormat.format(balance) + "원");

					}
					
				} else {
					System.out.println("비밀번호를 확인해주세요.");
				}
			} else {
				System.out.println("카드번호를 확인해주세요.");
			}
			
			pstmt.close();
			stmt.close();
			res.close();
			conn.close();
			
		} catch(SQLException e) {		
			System.out.println(e.getMessage());
		}
		
	}
	
}

List.java

package pack_Card;

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

public class List extends Conn {

	public void mtd_List() {
		
		mtdConn();
		
		Statement stmt = null;
		ResultSet res = null;
		
		try {
			stmt = conn.createStatement
					(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			
			String sql = "select * from cardinfo";
			res = stmt.executeQuery(sql);
			
			System.out.println("번호 \t 카드번호 \t 이름 \t 카드비밀번호 \t 잔액");
			System.out.println("====================================================");
			
			
			if (res.next()) {
				res.beforeFirst();
				while (res.next()) {
					int num = res.getInt("num");
					int cardnum = res.getInt("cardnum");
					String name = res.getString("name");
					int cardpw = res.getInt("cardpw");
					int payment = res.getInt("payment");
					System.out.println(num + "       " + cardnum + "       " + 
					 name + "       " + cardpw + "       " + payment);
				}
			}
			
			stmt.close();
			res.close();
			
		} catch(SQLException e) {		
			System.out.println(e.getMessage());
		}
		
	}
}