JAVA JDBC CARD QUIZ

[구현할 기능 목록]

• 사용자로부터 카드번호 입력받기
• 결제금액 입력받기
• 결제관련 필요한 정보 입력받기
• 해당 카드를 가지고 있는 고객 목록 조회
  
  

Mysql

create database card;

use card;

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

desc cardinfo;

select * from cardinfo;

insert into cardinfo (num, cardnum, name, cardpw, payment)
values
(1, 123456789, '유재석', 1234, 20000);

insert into cardinfo (num, cardnum, name, cardpw, payment)
values
(2, 123456, '박명수', 1111, 20000);

Main.java

package pack_Card;

import java.util.Scanner;

public class Main {

	public static void main(String[] args) {
		
		Scanner scanner = new Scanner(System.in);
		System.out.println("1. 카드번호 입력    2.카드정보 조회");
		System.out.print("번호입력 : ");
		int num = scanner.nextInt();
		
		if (num == 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.mtdInsert();
			
		} else if (num == 2) {
			
			System.out.println("[전체 목록 입니다.]");
			Inquire inquire = new Inquire();
			inquire.mtd_inquire();
			
		} else {
			
			System.out.println("번호를 확인해주세요.");
			
		}
		scanner.close();
	}

}

Conn.java

package pack_Card;

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

public class Conn {
	
	Connection conn = null;
	
	public void mtd_conn() {
		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			String uri = "jdbc:mysql://localhost:3306/card?";
			uri += "useSSL=false&";
			uri += "serverTimezone=Asia/Seoul&";
			uri += "useUnicode=true&";
			uri += "characterEncoding=UTF-8";
			
			String uid = "root";
			String upw = "1234";
			
			conn = DriverManager.getConnection(uri, 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 mtdInsert() {
		
		PreparedStatement pstmt = null;
		Statement stmt = null;
		ResultSet res = null;
		
		mtd_conn();
	
		try {
			stmt = conn.createStatement(res.TYPE_SCROLL_INSENSITIVE, res.CONCUR_UPDATABLE);
			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("=======잔액=======");
					String balanceSql = "select payment from cardinfo where cardnum = " + this.cardNum;
					res = stmt.executeQuery(balanceSql);
					
					if (res.next()) {
						res.beforeFirst();
						
						int showPayment = 0;
						
						DecimalFormat decimalFormat = new DecimalFormat("###,###");
						
						while (res.next()) {
							
							showPayment = res.getInt("payment");
								
							System.out.println("잔액 : " + decimalFormat.format(showPayment) + "원");
						}
					}
					
				} else {
					
					System.out.println("비밀번호를 확인해주세요");
				}
				
			} else {
				
				System.out.println("카드번호가 없습니다.");
			}
						
		} catch (SQLException e) {
			
			System.out.println(e.getMessage());
			
		} finally {
			
			if (pstmt != null) { try {pstmt.close();} catch (SQLException e) {System.out.println(e.getMessage());}}
			if (stmt != null) { try {stmt.close();} catch (SQLException e) {System.out.println(e.getMessage());}}
			if (res != null) { try {res.close();} catch (SQLException e) {System.out.println(e.getMessage());}}
			if (conn != null) { try {conn.close();} catch (SQLException e) {System.out.println(e.getMessage());}}
			
		}
	}
}

Insert.java 결과

Inquire.java

package pack_Card;

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

public class Inquire extends Conn{
	
	public void mtd_inquire() {
		
		Statement stmt = null;
		ResultSet res = null;
		
		mtd_conn();
		
		try {
			stmt = conn.createStatement(res.TYPE_SCROLL_INSENSITIVE, res.CONCUR_UPDATABLE);
			String sql = "select * from cardinfo";
			res = stmt.executeQuery(sql);
			
			DecimalFormat decimalFormat = new DecimalFormat("###,###");
			
			System.out.println("번호     카드번호    이름    카드비밀번호    잔액");
			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 + "    " + decimalFormat.format(payment));
					
				}
			}	
		} catch (SQLException e) {
			
			System.out.println(e.getMessage());
			
		} finally {
			
			if (stmt != null) { try {stmt.close();} catch (SQLException e) {System.out.println(e.getMessage());}}
			if (res != null) { try {res.close();} catch (SQLException e) {System.out.println(e.getMessage());}}
			if (conn != null) { try {conn.close();} catch (SQLException e) {System.out.println(e.getMessage());}}
			
		}
	}
}

Insert.java 결과