[자바/DB] JDBC로 회원가입, 로그인, 조회하기

---

MemberVo 클래스

 

package JDBC_오후;

public class MemberVo {

	private String id;
	private String pw;
	private String name;
	private int age;
	
	public MemberVo(String id, String pw, String name, int age) {
		super();
		this.id = id;
		this.pw = pw;
		this.name = name;
		this.age = age;
	}
	
	
	public MemberVo(String id, String pw) {
		this.id = id;
		this.pw = pw;
	}
	
	public String getId() {
		return id;
	}
	public String getPw() {
		return pw;
	}
	public String getName() {
		return name;
	}
	public int getAge() {
		return age;
	}
	
	
	
}

---

MemberDAO 클래스

 

package JDBC_오후;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class MemberDAO {

	Connection conn;
	PreparedStatement psmt;
	ResultSet rs;
	
	private void getConnection() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String db_url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
			String db_id = "hr";
			String db_pw = "hr";
		
			conn = DriverManager.getConnection(db_url, db_id, db_pw);
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	private void close() {
		
		try {
			if(conn != null) {
				conn.close();
			}
			if(psmt != null) {
				psmt.close();
			}
			if(rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	//회원가입할 때 호출하는 메소드
	public int joinInsert(MemberVo vo) {
		int cnt = 0;
		//1. DB연결
		getConnection();
		
		//2. Query준비
		String sql = "insert into member values(?,?,?,?)";
		
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, vo.getId());
			psmt.setString(2, vo.getPw());
			psmt.setString(3, vo.getName());
			psmt.setInt(4, vo.getAge());
		
			//3. Query전송 - executeUpdate
			//4. 결과처리 - cnt
			cnt = psmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			close();
		}
		return cnt;
	}
	
	public String loginSelect(MemberVo vo) {
		String name = null;
		
		getConnection();
		String sql = "select name from member where id=? and pw=?";
		
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, vo.getId());
			psmt.setString(2, vo.getPw());
			
			rs=psmt.executeQuery();
			while(rs.next()) {
				name = rs.getString(1);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			close();
		}
		return name;
	}
	
	public ArrayList<MemberVo> searchAllSelect(){
		ArrayList<MemberVo> list = new ArrayList<MemberVo>();
		
		getConnection();
		String sql = "select * from member";
		
		try {
			psmt= conn.prepareStatement(sql);
			rs = psmt.executeQuery();
			while(rs.next()) {
				String id = rs.getString(1);
				String pw = rs.getString(2);
				String name = rs.getString(3);
				int age = rs.getInt(4);
			
				list.add(new MemberVo(id,pw,name,age));
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			close();
		}
		return list;
	}
	
	public ArrayList<MemberVo> searchSelect(String searchName){
		ArrayList<MemberVo> list = new ArrayList<MemberVo>();
		
		getConnection();
		String sql = "select * from member where name=?";
		
		try {
			psmt= conn.prepareStatement(sql);
			psmt.setString(1, searchName);
			rs = psmt.executeQuery();
			
			while(rs.next()) {
				String id = rs.getString(1);
				String pw = rs.getString(2);
				String name = rs.getString(3);
				int age = rs.getInt(4);
			
				list.add(new MemberVo(id,pw,name,age));
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			close();
		}
		
		
		return list;
	}
	
	
	
}

---

Main 클래스

 

package JDBC_오후;

import java.util.ArrayList;
import java.util.Scanner;

public class Main {

	public static void main(String[] args) {

		Scanner sc = new Scanner(System.in);
		MemberDAO dao = new MemberDAO();
	while(true) {
			System.out.print("[1]회원가입 [2]로그인 [3]조회 [4]종료 >>");
			int choice = sc.nextInt();

			if(choice == 4) {
				System.out.println("프로그램 종료");
				break;
			}
			
			else if(choice==1) {
				System.out.print("ID : ");
				String id = sc.next();
				System.out.print("PW : ");
				String pw = sc.next();
				System.out.print("Name : ");
				String name = sc.next();
				System.out.print("Age : ");
				int age = sc.nextInt();
				
				MemberVo vo = new MemberVo(id, pw, name, age);
				int result = dao.joinInsert(vo);
				if(result==0) {
					System.out.println("회원가입 실패!");
					
				}else {
					System.out.println("회원가입 성공!");
				}
			}
			
			else if(choice==2) {
				System.out.print("ID : ");
				String id = sc.next();
				System.out.print("PW : ");
				String pw = sc.next();
				
				MemberVo vo = new MemberVo(id,pw);
				String name = dao.loginSelect(vo);
				if(name == null) {
					System.out.println("로그인 실패");
				}else {
					System.out.println(name + "님 환영합니다!");
				}
			}
			
			else if(choice==3) {
				System.out.print("[1]전체조회 [2]선택조회 >>");
				int searchChoice = sc.nextInt();
				if(searchChoice == 1) {
					//전체조회 기능
					System.out.println("ID\tPW\tName\tAge");
					ArrayList<MemberVo> li = dao.searchAllSelect();
					if(li.size()>0) {
						for(int i=0;i<li.size();i++) {
							String id = li.get(i).getId();
							String pw = li.get(i).getPw();
							String name = li.get(i).getName();
							int age = li.get(i).getAge();
							System.out.println(id+"\t"+pw+"\t"+name+"\t"+age);
						}
					}else {
						System.out.println("조회실패");
					}
				}
				
				else if(searchChoice == 2) {
					//선택조회 기능
					System.out.println("===선택조회===");
					System.out.print("검색Name : ");
					String searchName = sc.next();
					
					ArrayList<MemberVo> li = dao.searchSelect(searchName);
					
					if(li.size()==0) {
						System.out.println("조회실패");
					}else {
						System.out.println("ID\tPW\tName\tAge");
						for(int i=0;i<li.size();i++) {
							String id = li.get(i).getId();
							String pw = li.get(i).getPw();
							String name = li.get(i).getName();
							int age = li.get(i).getAge();
							System.out.println(id+"\t"+pw+"\t"+name+"\t"+age);
						
					}
					
				}
				
			}
		
		}
	}

	}
}