30일 차 Model / DAO

package model;

public class Member {

private String id;

private String pw;

private String name;

private String email;

private int birth_year;

public String getId() {

return id;

}

public void setId(String id) {

this.id = id;

}

public String getPw() {

return pw;

}

public void setPw(String pw) {

this.pw = pw;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getEmail() {

return email;

}

public void setEmail(String email) {

this.email = email;

}

public int getBirth_year() {

return birth_year;

}

public void setBirth_year(int birth_year) {

this.birth_year = birth_year;

}

@Override

public String toString() {

return "Member [id=" + id + ", pw=" + pw + ", name=" + name + ", email=" + email + ", birth_year=" + birth_year

+ "]";

}

}

package dao;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import model.Member;

public class MemberDAO {

private static MemberDAO instance;

private Connection connection;

private MemberDAO() {

String url = "jdbc:mysql://localhost:3306/sds2";

String user = "root";

String password = "mysql";

try {

connection = DriverManager.getConnection(url, user, password);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public static MemberDAO getInstance() {

if (instance == null)

instance = new MemberDAO();

return instance;

}

public void insertMember(Member member) {

PreparedStatement pstmt = null;

try {

pstmt = connection.prepareStatement("insert into member values(?,?,?,?,?)");

pstmt.setString(1, member.getId());

pstmt.setString(2, member.getPw());

pstmt.setString(3, member.getName());

pstmt.setString(4, member.getEmail());

pstmt.setInt(5, member.getBirth_year());

pstmt.executeUpdate();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

if (pstmt != null)

try {

pstmt.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

public void updateMember(Member member) {

PreparedStatement pstmt = null;

try {

pstmt = connection.prepareStatement("update member set pw=?, name=?, email=?, birth_year=? where id = ?");

pstmt.setString(5, member.getId());

pstmt.setString(1, member.getPw());

pstmt.setString(2, member.getName());

pstmt.setString(3, member.getEmail());

pstmt.setInt(4, member.getBirth_year());

pstmt.executeUpdate();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

if (pstmt != null)

try {

pstmt.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

public void deleteMember(String id) {

PreparedStatement pstmt = null;

try {

pstmt = connection.prepareStatement("delete from member where id = ?");

pstmt.setString(1, id);

pstmt.executeUpdate();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

if (pstmt != null)

try {

pstmt.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

public Member selectOne(String id) {

PreparedStatement pstmt = null;

ResultSet resultSet = null;

Member member = new Member();

try {

pstmt = connection.prepareStatement("select * from member where id = ?");

pstmt.setString(1, id);

resultSet = pstmt.executeQuery();

if (resultSet.next()) {

member.setId(resultSet.getString("id"));

member.setPw(resultSet.getString("pw"));

member.setName(resultSet.getString("name"));

member.setEmail(resultSet.getString("email"));

member.setBirth_year(resultSet.getInt("birth_year"));

}

System.out.println(member);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

try {

if (pstmt != null)

pstmt.close();

if (resultSet != null)

resultSet.close();

} catch (SQLException e) {

}

}

return member;

}

public List<Member> selectAll() {

PreparedStatement pstmt = null;

ResultSet resultSet = null;

List<Member> list = new ArrayList<Member>();

try {

pstmt = connection.prepareStatement("select * from member");

resultSet = pstmt.executeQuery();

while (resultSet.next()) {

Member member = new Member();

member.setId(resultSet.getString("id"));

member.setPw(resultSet.getString("pw"));

member.setName(resultSet.getString("name"));

member.setEmail(resultSet.getString("email"));

member.setBirth_year(resultSet.getInt("birth_year"));

list.add(member);

}

for(Member m : list)

System.out.println(m);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

try {

if (pstmt != null)

pstmt.close();

if (resultSet != null)

resultSet.close();

} catch (SQLException e) {

}

}

return list;

}

}

package test;

import java.util.Scanner;

import dao.MemberDAO;

import model.Member;

public class MemberTest {

static MemberDAO dao = MemberDAO.getInstance();

static Member member = new Member();

static Scanner scan = new Scanner(System.in);

static int birth_year = 0;

static String id = null;

static String pw = null;

static String name = null;

static String email = null;

public static void main(String[] args) {

int selec = 0;

System.out.println("1 : 삽입");

System.out.println("2 : 수정");

System.out.println("3 : 삭제");

System.out.println("4 : 레코드 한개 조회");

System.out.println("5 : 레코드 전체 조회");

selec = scan.nextInt();

if (selec == 1) {

in();

dao.insertMember(member);

} else if (selec == 2) {

up();

dao.updateMember(member);

} else if (selec == 3) {

scan.nextLine();

System.out.println("id입력");

id = scan.nextLine();

member.setId(id);

dao.deleteMember(id);

} else if (selec == 4) {

scan.nextLine();

System.out.println("id입력");

id = scan.nextLine();

member.setId(id);

dao.selectOne(id);

} else if (selec == 5) {

scan.nextLine();

dao.selectAll();

}

}

public static void in() {

scan.nextLine();

System.out.println("id입력");

id = scan.nextLine();

member.setId(id);

System.out.println("pw입력");

pw = scan.nextLine();

member.setPw(pw);

System.out.println("name입력");

name = scan.nextLine();

member.setName(name);

System.out.println("email입력");

email = scan.nextLine();

member.setEmail(email);

System.out.println("birth_year입력");

birth_year = scan.nextInt();

member.setBirth_year(birth_year);

}

public static void up() {

scan.nextLine();

System.out.println("pw입력");

pw = scan.nextLine();

member.setPw(pw);

System.out.println("name입력");

name = scan.nextLine();

member.setName(name);

System.out.println("email입력");

email = scan.nextLine();

member.setEmail(email);

System.out.println("birth_year입력");

birth_year = scan.nextInt();

member.setBirth_year(birth_year);

scan.nextLine();

System.out.println("id입력");

id = scan.nextLine();

member.setId(id);

}

}

******주의******

할때 꼭 프로젝트 폴더에 

lib폴더 생성해서

mysql.jar넣어야 합니다.