nme.kr

추적: › ch20
현재 위치: index » 프로그램 » java » ㄴhomework » book_source » ch20

Ch.20 데이터베이스

+목차

데이터베이스 개요

데이터베이스 구조

MariaDB 설치

https://mariadb.org/download/?t=mariadb&p=mariadb&r=10.6.5&os=windows&cpu=x86_64&pkg=msi&m=yongbok

기본 SQL

자바 연결

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
package chapter20;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
 
public class DBConnect {
 
    public static void main(String[] args) {
         
        // 데이터베이스 접속 객체
        Connection conn = null;
         
        try {
            // MariaDB 드라이버 로드
            Class.forName("org.mariadb.jdbc.Driver");
            // 데이터베이스 접속
            conn = DriverManager.getConnection(
                    "jdbc:mariadb://localhost:3306/javadb", // Host
                    "root", // 사용자
                    "java1234"); // 암호
             
        } catch (ClassNotFoundException e) {
            System.out.println(e.getMessage());
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) try {conn.close();} catch(Exception e) {}
        }
         
        if (conn != null) {
            System.out.println("데이터베이스 접속");
        }
    }
}

데이터 조회 / 처리

MemberVO.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
package chapter20;
 
public class MemberVO {
 
    private int memberno; // 회원번호
    private String id;    // 아이디
    private String name;  // 이름
     
    public int getMemberno() {
        return memberno;
    }
    public void setMemberno(int memberno) {
        this.memberno = memberno;
    }
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
     
}
MemberDAO.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
package chapter20;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
 
public class MemberDAO {
     
    // 데이터베이스 접속 객체
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
 
    // 데이터베이스 접속
    public MemberDAO() {
         
        try {
            // MariaDB 드라이버 로드
            Class.forName("org.mariadb.jdbc.Driver");
            // 데이터베이스 접속
            conn = DriverManager.getConnection(
                    "jdbc:mariadb://localhost:3306/javadb", // Host
                    "root", // 사용자
                    "java1234"); // 암호
             
        } catch (ClassNotFoundException e) {
            System.out.println(e.getMessage());
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
         
    }
     
    // 데이터 입력
    public int insert(MemberVO vo) {
         
        int result = 0;
        try {
            String sql = "INSERT INTO member (memberno, id, name) "
                    + "VALUES ("+vo.getMemberno()+", '"+vo.getId()+"', '"
                    + vo.getName()+"')";
            stmt = conn.createStatement();
            result = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return result;
    }
     
    // 데이터 조회
    public MemberVO selectOne(int memberno) {
         
        MemberVO vo = new MemberVO();
        try {
            String sql = "SELECT * FROM member WHERE memberno="+memberno;
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
             
            if (rs.next()) {
                vo.setMemberno(rs.getInt("memberno"));
                vo.setId(rs.getString("id"));
                vo.setName(rs.getString("name"));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return vo;
    }
     
    // 데이터 수정
    public int update(MemberVO vo) {
         
        int result = 0;
        try {
            String sql = "UPDATE member SET id='"
                    + vo.getId()+"', name='"+vo.getName()+"' "
                    + "WHERE memberno="+vo.getMemberno();
            stmt = conn.createStatement();
            result = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return result;
    }
     
    // 데이터 삭제
    public int delete(int memberno) {
         
        int result = 0;
        try {
            String sql = "DELETE FROM member WHERE memberno="+memberno;
            stmt = conn.createStatement();
            result = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return result;
    }
     
    // 회원목록
    public List<MemberVO> list() {
        List<MemberVO> list = new ArrayList<MemberVO>();
         
        try {
            String sql = "SELECT * FROM member";
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
             
            while (rs.next()) {
                MemberVO vo = new MemberVO();
                vo.setMemberno(rs.getInt("memberno"));
                vo.setId(rs.getString("id"));
                vo.setName(rs.getString("name"));
                list.add(vo);
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return list;
    }
     
     
     
    // 자원 close()
    public void close() {
        if (conn != null) {
            try { conn.close(); } catch (SQLException e) {}
        }
        if (stmt != null) {
            try { stmt.close(); } catch (SQLException e) {}
        }
        if (rs != null) {
            try { rs.close(); } catch (SQLException e) {}
        }
    }
}
MemberMain.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
package chapter20;
 
import java.util.List;
 
public class MemberMain {
 
    public static void main(String[] args) {
         
        // 회원객체 생성
        MemberVO vo = new MemberVO();
        vo.setMemberno(2);
        vo.setId("hong");
        vo.setName("홍길동");
         
        // dao 객체 생성
        MemberDAO dao = new MemberDAO();
         
        // 회원 추가
        int r = dao.insert(vo);
        if (r > 0) {
            System.out.println("회원 등록 성공");
        }
         
        // 2번 회원 조회후 출력
        MemberVO rvo = dao.selectOne(2);
        System.out.println("회원정보 출력");
        System.out.println("회원번호 : "+rvo.getMemberno());
        System.out.println("아이디 : "+rvo.getId());
        System.out.println("이름 : "+rvo.getName());
         
        // 회원정보 수정
        vo.setName("고길동");
        int r2 = dao.update(vo);
        if (r2 > 0) {
            System.out.println("회원 수정 성공");
        }
         
        // 전체회원 목록 조회
        System.out.println("회원목록 조회");
        List<MemberVO> list = dao.list();
        for (int i=0; i<list.size(); i++) {
            System.out.println("회원번호:"+list.get(i).getMemberno()
                    +"\t아이디:"+list.get(i).getId()
                    +"\t이름:"+list.get(i).getName());
        }
         
         
        // 회원 삭제
        int r3 = dao.delete(2);
        if (r3 > 0) {
            System.out.println("회원 삭제 성공");
        }
         
         
        // 자원 해제
        dao.close();
 
    }
 
}

[실행결과]

마지막으로 수정됨: 2025/06/27 16:07
CC Attribution-Share Alike 4.0 International 별도로 명시하지 않을 경우, 이 위키의 내용은 다음 라이선스에 따라 사용할 수 있습니다: CC Attribution-Share Alike 4.0 International