Quiz ①

losuif·2021년 7월 27일
0

학원 복습 - JDBC

목록 보기
4/6
post-thumbnail

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;

select pw from memberinfo where id = 'asd';
#↑일단 한번 저렇게 쳐보자 



최초실행화면

package pack_Member;

import java.util.Scanner;

public class Main {

	public static void main(String[] args) {
		
		while (true) {
			
			System.out.println("원하는 번호를 선택한 후 입력해주세요."
								+ "(번호만 입력 후 엔터)");
			
			Scanner scanner = new Scanner(System.in);
			System.out.print("1. 로그인	2.회원가입	3.전체 회원목록 "
								+ "\n번호 선택 : ");
			int code = scanner.nextInt();
			
			if (code == 1) { 
				LogIn.mtd_login();
				break;
			} else if (code == 2) {
				SignUp.mtd_signUp();
				break;
			} else if (code == 3) {
				MemberList.mtd_memlist();
				break;
			} else {
				System.out.println("번호를 정확하게 입력해주세요.");
			}
			
		}
	}
}



슈퍼클래스

package pack_Member;

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

public class Conn {

	static Connection 			conn 	= null;
	static Statement 			stmt 	= null;
	static PreparedStatement   	        pstmt 	= null;
	static ResultSet 			res 	= null;
	
	public Conn (Connection conn, Statement stmt, 
			PreparedStatement pstmt, ResultSet res) {
		this.conn	= conn;
		this.stmt	= stmt;
		this.pstmt	= pstmt;
		this.res	= res;
	}
	
	
	public static Connection mtd_conn() {
		//접속
		
			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";
				String uid = "root";
				String upw = "1234";
					
				conn = DriverManager.getConnection(url, uid, upw);		
				System.out.println("접속이 완료 되었습니다.");
				stmt = conn.createStatement();
			
			} catch (ClassNotFoundException e) {
				System.out.println("ClassNotFound : " + e.getMessage());
			} catch(SQLException e) {			
				System.out.println(e.getMessage());
			}
		
			return conn;
	}
}




😡 막 치기 전에 생각 하자! 로그인을 하려면 회원가입 먼저 해야겠지? 😯

회원가입

package pack_Member;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class SignUp extends Conn{
	
	// 회원가입 구현
	
	public SignUp(Connection conn, Statement stmt, 
			PreparedStatement pstmt, ResultSet res) {
		super(conn, stmt, pstmt, res);
	};
	
	
	public static void mtd_signUp() {
		
		Conn.mtd_conn();
		
		//회원가입
		try {
			String sql = "insert into memberinfo values (?, ?, ?, ?)";
			pstmt = conn.prepareStatement(sql);
			String id 	= "";
			String pw       = "";
			String name 	= "";
			String address  = "";
			
			System.out.println("[회원가입]");
			
			Scanner scanner = new Scanner(System.in);
			
			System.out.print("아이디 입력 : ");
			id = scanner.nextLine();
			System.out.print("비밀번호 입력 : ");
			pw = scanner.nextLine();
			System.out.print("이름 : ");
			name = scanner.nextLine();
			System.out.print("주소 입력(동까지만) : ");
			address = scanner.nextLine();
			
			scanner.close();
			
			
			pstmt.setString(1, id);
			pstmt.setString(2, pw);
			pstmt.setString(3, name);
			pstmt.setString(4, address);
			
			int res = pstmt.executeUpdate();
			
			if (res == 1) { // 입력해서 자료가 들어가면 1
				System.out.println("가입되었습니다. "
						+ "\n 프로그램을 재실행하여 로그인 해주세요.");
			} else {
				System.out.println("오류가 발생하여 미입력 되었습니다.");
				System.out.println("다시 실행해주세요.");
			}
			
			pstmt.close();
			stmt.close();
			conn.close();
			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
}
}



로그인

package pack_Member;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class LogIn extends Conn{
	
	//로그인 구현
	
	public LogIn(Connection conn, Statement stmt, 
			PreparedStatement pstmt, ResultSet res) {
		super(conn, stmt, pstmt, res);
	};
	
	
	public static void mtd_login() {
		
		Conn.mtd_conn();
		
		//로그인
		try {
			String sql = "select pw from memberinfo where id = ?";
			
			Scanner scanner = new Scanner(System.in);
			
			System.out.print("아이디를 입력해주세요 : ");
			String id = scanner.nextLine();
			System.out.print("\n비밀번호를 입력해주세요 : ");
			String pw = scanner.nextLine();
			
			scanner.close();
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			res = pstmt.executeQuery();
			
			
			if (res.next()) {
				if (res.getString(1).contentEquals(pw)) {
					System.out.println("[" + id + "]님께서 로그인 하셨습니다. "
							+ "프로그램을 종료합니다.");

				}else {
					System.out.println("비밀번호를 확인해주세요. "
							+ "프로그램을 종료합니다.");
				}
			} else {
				System.out.println("아이디를 확인해주세요. "
						+ "프로그램을 종료합니다.");
			}
			
			res.close();
			pstmt.close();
			stmt.close();
			conn.close();
			
		} catch(SQLException e) {			
			System.out.println(e.getMessage());
		}
	}
}



회원목록 출력

package pack_Member;

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

public class MemberList extends Conn{

	//회원 목록 구현
	
	public MemberList(Connection conn, Statement stmt, 
			PreparedStatement pstmt, ResultSet res) {
		super(conn, stmt, pstmt, res);
	}
	
	
	public static void mtd_memlist() {
		
		Conn.mtd_conn();
		
		try {
			String sql = "select * from memberinfo order by id asc";
			res = stmt.executeQuery(sql);
			
			System.out.println("아이디     비밀번호     이름     주소");
			System.out.println("======================================");
			
			while (res.next()) {
				String id 	= res.getString("id");
				String pw 	= res.getString("pw");
				String name     = res.getString("name");
				String address  = res.getString("address");
				
				System.out.println(id + pw + name + address);
				
			}
			
			res.close();
			stmt.close();
			conn.close();
			
		} catch(SQLException e) {			
			System.out.println(e.getMessage());
		}
	}
}

0개의 댓글