은행계좌 Mysql 연동 (계좌 생성+조회)

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.Scanner;

public class BankAccounts {

public static void main(String[] args) {

boolean loop = true;

while(loop){

System.out.println("------------------------------------------------");

System.out.println("1.계좌 생성| 2. 계좌 조회 | 3. 입금 | 4.출금 | 5. 종료");

System.out.println("------------------------------------------------");

System.out.println("선택 : ");

Scanner sc = new Scanner(System.in);

int select = sc.nextInt();

if(select == 1){

createAcc();

}

else if(select == 2){

getAcc();

}

else if(select == 3){

depositAcc();

}

else if(select == 4){

withdrawAcc();

} else{

System.out.println("프로그램을 종료합니다");

sc.close();

loop = false;

}

}

}

while loop select문

public static void createAcc(){

Connection con = null;

try {

Scanner sc = new Scanner(System.in);

System.out.println("계좌주 : ");

String accName = sc.nextLine();

System.out.println("계좌번호 : ");

String accNumber = sc.nextLine();

System.out.println("초기입금액 : ");

        int accMoney = sc.nextInt();

    

        Scanner sc2 = new Scanner(System.in);

        System.out.println("주소 : ");

         String accAddress = sc2.nextLine();

System.out.println("폰번호 : ");

        String accPhone = sc2.nextLine();

   

        System.out.println("직업 : ");

        String accJob = sc2.nextLine();

   

        con = DriverManager.getConnection("jdbc:mysql://localhost", "root", "mysql");

java.sql.Statement st = null;

ResultSet rs = null;

java.sql.PreparedStatement pstmt = null;

st = con.createStatement();

String sql;

String sql2;

rs = st.executeQuery("use dbname");

   

sql = "insert into user_info(acc_number,acc_money) values(?,?)";        

        sql2 = "insert into user_detail(acc_number, user_name, user_address, user_phone, user_job, user_no)

                                            values(?,?,?,?,?,(select user_no from user_info where acc_number = ?))";

        pstmt = con.prepareStatement(sql);

        pstmt.setString(1, accNumber);

        pstmt.setInt(2, accMoney);

        pstmt.executeUpdate();

 

pstmt = con.prepareStatement(sql2);                          

pstmt.setString(1,accNumber);

pstmt.setString(2,accName);

pstmt.setString(3,accAddress);

pstmt.setString(4,accPhone);

pstmt.setString(5,accJob);

pstmt.setString(6,accNumber);

pstmt.executeUpdate();

con.close();

System.out.println("계좌가 성공적으로 생성되었습니다");

   

} catch (SQLException e) {

System.out.println(e.getLocalizedMessage());

System.out.println("서버시스템에 오류가 발생하였습니다.");

}

}

계좌에 친구놈들 별명을 집어넣고... ㅋㅋ java console에서 천천히 입력해보자!

public static void getAcc(){

try{

Connection con = null;

con = DriverManager.getConnection("jdbc:mysql://localhost","root","mysql");

java.sql.Statement st = null;

ResultSet rs = null;

st = con.createStatement();

rs = st.executeQuery("use dbname");

System.out.println("계좌번호를 입력하세요 : ");

Scanner sc = new Scanner(System.in);

String number = sc.nextLine();

String sql;

sql="SELECT a.user_name, a. user_address, b.acc_money, b. acc_number  

                                       FROM user_detail a, user_info b 

                                       where a.user_no = b.user_no and b.acc_number= '"+number+"'";

rs = st.executeQuery(sql);

while(rs.next()){

String accName = rs.getString("a.user_name");

String accAddress = rs.getString("a.user_address");   

System.out.println("계좌주 : " + accName);

System.out.println("계좌주 주소 : " + accAddress);

String accNumber = rs.getString("b.acc_number");

int accMoney = rs.getInt("b.acc_money");

System.out.println("계좌번호 : " + accNumber);

System.out.println("잔액 : " + accMoney);

}

con.close();

}catch (SQLException e) {

System.out.println(e.getLocalizedMessage());

System.out.println("서버시스템에 오류가 발생하였습니다.");

}

}

계좌에 넣은 친구들을 불러보자!

친구들 중에 동명이인이 있을 수 있으니 pk는 계좌번호로 정하자!