티스토리 뷰

언어/JAVA

[JAVA] 자바 MYSQL 삽입,삭제,수정,검색

Sime 2016. 10. 21. 11:52
구문객체를 던지는 3가지 방법
1. execute -> 테이블 생성, 수정,삭제 등 데이터베이스 관리 명령어 사용    create..
2. excuteUpdate -> 레코드 삽입 수정 삭제등 데이터 조작 명령어 사용    insert, update, delete..
3. excuteQuery -> 레코드 조회, 테이블 조회 등 조회 명령어 사용    selete..

[ ResultSet 을 통해 쿼리 결과를 가져 올 수 있다. 79번줄 참고! ]

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
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class SqlTest {
    Connection conn = null;
    Statement stmt = null;
    String table;
 
    public SqlTest(Connection conn, String table) {
        this.conn = conn;
        this.table = table;
        try {
            this.stmt = conn.createStatement();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
 
    // 삽입
    public void insert(int id, String name, int grade) {
        StringBuilder sb = new StringBuilder();
        String sql = sb.append("insert into " + table + " values(")
                .append(id + ",")
                .append("'" + name + "',")
                .append(grade)
                .append(");")
                .toString();
        try {
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
 
    // 삭제
    public void delete(int id) {
        StringBuilder sb = new StringBuilder();
        String sql = sb.append("delete from " + table + " where id = ")
                .append(id)
                .append(";")
                .toString();
        try {
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
 
    // 수정
    public void update(int id, String name, int grade) {
        StringBuilder sb = new StringBuilder();
        String sql = sb.append("update " + table + " set")
                .append(" name = ")
                .append("'" + name + "',")
                .append(" grade = ")
                .append(grade)
                .append(" where id = ")
                .append(id)
                .append(";").toString();
        try {
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
 
    // 모든 검색
    public void selectAll() {
        StringBuilder sb = new StringBuilder();
        String sql = sb.append("select * from " + table)
                .append(";").toString();
        try {
            ResultSet rs = stmt.executeQuery(sql);
            
            System.out.print("id");
            System.out.print("\t");
            System.out.print("name");
            System.out.print("\t");
            System.out.print("grade");
            System.out.print("\n");
            System.out.println("────────────────────────");
            
              while(rs.next()){
                     System.out.print(rs.getInt("id"));
                     System.out.print("\t");
                     System.out.print(rs.getString("name"));
                     System.out.print("\t");
                     System.out.print(rs.getString("grade"));
                     System.out.print("\n");
                }
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
 
    // 검색
    public void select(int id) {
        StringBuilder sb = new StringBuilder();
        String sql = sb.append("select * from " + table + " where")
                .append(" id = ")
                .append(id)
                .append(";").toString();
        try {
            ResultSet rs = stmt.executeQuery(sql);
            
            System.out.print("id");
            System.out.print("\t");
            System.out.print("name");
            System.out.print("\t");
            System.out.print("grade");
            System.out.print("\n");
            System.out.println("────────────────────────");
            
              while(rs.next()){
                     System.out.print(rs.getInt("id"));
                     System.out.print("\t");
                     System.out.print(rs.getString("name"));
                     System.out.print("\t");
                     System.out.print(rs.getString("grade"));
                     System.out.print("\n");
                }
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
 
}
 
cs


모델을 이용한 방법


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
 
public class Student {
    //모델 1:1 대응
    private int id;
    private String name;
    private int grade;
    
    public Student(int id,String name,int grade) {
        this.id = id;
        this.name = name;
        this.grade = grade;
    }
    
    public Student(){};
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getGrade() {
        return grade;
    }
    public void setGrade(int grade) {
        this.grade = grade;
    }
    
    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + ", grade=" + grade + "]";
    }
}
 
cs


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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
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;
 
// 이 클래스는 상태로써 디비와의 연결 객체를 가지고
// 연결객체를 사용해서 Student테이블에 데이터를 
// insert, delect, update, select.. 하는 기능을 수행해줌
 
public class StudentDao {
 
    private Connection conn;
    private static final String USERNAME = "root";
    private static final String PASSWORD = "mysql";
    private static final String URL = "jdbc:mysql://localhost/min";
 
    public StudentDao() {
        // connection객체를 생성해서 디비에 연결해줌..
        try {
            Class.forName("com.mysql.jdbc.Driver");
 
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
 
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("클래스 적재 실패!!");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("연결 실패!!");
        }
    }
 
    public void insertStudent(Student student) {
        String sql = "insert into student values(?,?,?);";
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, student.getId());
            pstmt.setString(2, student.getName());
            pstmt.setInt(3, student.getGrade());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null && !pstmt.isClosed())
                    pstmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
 
    public void updateStudent(Student student) {
        String sql = "update student set name=?, grade=? where id = ?;";
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, student.getName());
            pstmt.setInt(2, student.getGrade());
            pstmt.setInt(3, student.getId());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null && !pstmt.isClosed())
                    pstmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
 
    public void deleteStudent(int id) {
        String sql = "delete from student where id = ?;";
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null && !pstmt.isClosed())
                    pstmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
 
    public Student selectOne(int id) {
        String sql = "select * from student where id = ?;";
        PreparedStatement pstmt = null;
        Student re = new Student();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
 
            if (rs.next()) {
                re.setId(rs.getInt("id"));
                re.setName(rs.getString("name"));
                re.setGrade(rs.getInt("grade"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null && !pstmt.isClosed())
                    pstmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return re;
    }
 
    public List<Student> slectAll() {
        String sql = "select * from student;";
        PreparedStatement pstmt = null;
 
        List<Student> list = new ArrayList<Student>();
 
        try {
            pstmt = conn.prepareStatement(sql);
            ResultSet re = pstmt.executeQuery();
 
            while (re.next()) {
                Student s = new Student();
                s.setId(re.getInt("id"));
                s.setName(re.getString("name"));
                s.setGrade(re.getInt("grade"));
                list.add(s);
            }
 
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null && !pstmt.isClosed())
                    pstmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return list;
    }
}
 
cs


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
import java.util.List;
 
public class StudentDB_Test {
 
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        StudentDao sDao = new StudentDao();
 
        Student a = sDao.selectOne(3);
        System.out.println(a.toString());
 
        // List<Student> a = sDao.slectAll();
        // for(int i=0;i<a.size();i++){
        // Student s = a.get(i);
        // System.out.println(s.toString());
        // }
 
    }
}
 
cs


반응형

'언어 > JAVA' 카테고리의 다른 글

[JAVA] 초기화 블럭, 인스턴스 블럭  (0) 2018.01.25
[JAVA] MYSQL 연결 및 쿼리 보내기  (1) 2016.10.21
[JAVA] 라이브러리 추가  (0) 2016.10.21
[JAVA] TCP 통신 예제  (1) 2016.09.29
[JAVA] UDP 통신해보기  (0) 2016.09.27
공지사항
최근에 올라온 글
최근에 달린 댓글
Total
Today
Yesterday
링크
TAG more
«   2024/05   »
일 월 화 수 목 금 토
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
글 보관함