Quiz ②

DB 스키마

create database membership;
use membership;

create table memberinfo(
id		char(10) primary key,
pw		char(10),
name		char(5),
address		char(30)
);

select * from memberinfo;

최초실행화면

package pack_Member;

import java.util.Scanner;

import pack_Member.LogIn;
import pack_Member.MemberList;

public class Main {

	public static void main(String[] args) {
		
		String info = "원하는 번호를 선택한 후 입력해주세요. "
				+ "(번호만 입력 후 엔터)";		
		
		Scanner scanner = new Scanner(System.in);
		
		String uid 	= null;
		String upw 	= null;
		String uName 	= null;
		String addr 	= null;
		
		while (true) {
			System.out.println(info);
			System.out.println("1. 로그인   2. 회원가입   3. 전체 회원목록");
			System.out.print("번호 선택 : ");
			int code = scanner.nextInt();
			
			switch (code) {
			case 1: 
				System.out.println("아이디를 입력해주세요 : ");
				uid = scanner.next();
				System.out.println("비밀번호를 입력해주세요 : ");
				upw = scanner.next();
				
				Login logIn = new Login(uid, upw);
				logIn.Login();
				break;
				
			case 2 : 	
				System.out.println("[회원가입]");
				System.out.print("아이디 입력 : ");
				uid = scanner.next();
				
				System.out.print("비밀번호 입력 : ");
				upw = scanner.next();
				
				System.out.print("이름 입력 : ");
				uName = scanner.next();
				
				System.out.print("주소 입력(동까지만) : ");
				addr = scanner.next();
				
				SignUp signUp = new SignUp(uid, upw, uName, addr);
				signUp.SignUp();
				break;
				
			case 3 : 	
				Memberlist memberlist = new Memberlist();
				memberlist.memlist();
				break;
			
			default:
				System.out.println("번호를 정확하게 입력해주세요.");
				break;
			}

		}
		
		scanner.close();
	}

}

슈퍼클래스

package pack_Member;

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

public class DBConn_Proc {
	//Super Class
	
	Connection 	conn	 = null;
	
	public void mtd_dbConn() {
		
		//DB 접속 담당 코드
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			String url = "jdbc:mysql://127.0.0.1:3308/membership?";
	          	   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("SQLException : " + e.getMessage());
		}
	}
}

회원가입

package pack_Member;

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

public class SignUp extends DBConn_Proc{

		private String uid;
		private String upw;
		private String uName;
		private String addr;
		
		public SignUp(String uid, String upw, String uName, String addr) {
			this.uid = uid;
			this.upw = upw;
			this.uName = uName;
			this.addr = addr;
		}
		
		
		public void SignUp() {
			
			mtd_dbConn();
			
			PreparedStatement 	pstmt	 = null;
			
		try {
			
			String sql = "insert into memberInfo values (?, ?, ?, ?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, this.uid);
			pstmt.setString(2, this.upw);
			pstmt.setString(3, this.uName);
			pstmt.setString(4, this.addr);
			int resultCnt = pstmt.executeUpdate();
			
			if (resultCnt < 1) {
				System.out.println("죄송합니다. 정상입력되지 않았습니다.");
			} else {
				System.out.println("가입되었습니다.\n"
						+ "프로그램을 재실행하여 로그인 해주세요.\n");
			}
			
			pstmt.close();
			conn.close();
			
		} catch (SQLException e) {
			System.out.println("SQLException : " + e.getMessage());
		}
			
			
		}
}

로그인

package pack_Member;

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

public class Login extends DBConn_Proc{

	private String uid;
	private String upw;
	
	public Login(String uid, String upw) {
		this.uid = uid;
		this.upw = upw;
	}
	
	public void Login() {
		
		mtd_dbConn();
		
		Statement	stmt	= null;
		ResultSet	res	= null;
		
		try {
			
			stmt = conn.createStatement();
			
			String sql = "select name from memberinfo where id = '" 
							+ this.uid + "' and pw = '" + this.upw + "'";
			res = stmt.executeQuery(sql);
			
			if (res.next()) {
				String msg = "\n[" + res.getString("name") +"]님께서 로그인 하셨습니다."
						+ "\n프로그램을 종료합니다.";
				System.out.println(msg);
			} else {
				System.out.println("아이디와 비밀번호를 확인하시고 다시 입력해주세요."
						+ "\n프로그램을 종료합니다.");
			}
			
		} catch (SQLException e) {
			System.out.println("SQLException : " + e.getMessage());
		}
	}
	
	
}

회원목록 출력

package pack_MemberEZ;

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

public class Memberlist extends DBConn_Proc{
	
	public void memlist() {
		
		Statement	stmt	= null;
		ResultSet	res	= null;
		
		try {
			
			mtd_dbConn();
			
			stmt = conn.createStatement();
			String sql = "select * from memberinfo order by id";
			res = stmt.executeQuery(sql);
			
			System.out.println("id \t pw \t name \t address");
			System.out.println("============================");
			String row; // row에는 null
			while (res.next()) {
				row = ""; // 누적값 공백으로 만들기
				row += res.getString("id");
				row += "\t";
				row += res.getString("pw");
				row += "\t";
				row += res.getString("name");
				row += "\t";
				row += res.getString("address");
			System.out.println(row);
			
			}
			
			res.close();
			stmt.close();
			conn.close();
			
		} catch (SQLException e) {
			System.out.println("SQLException : " + e.getMessage());
		}
	}
}