Notice
Recent Posts
| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 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 |
Tags
- springboot
- 필터체인
- SESSION
- 설치
- 알고리즘
- Spring
- 자바스크립트
- 버튼
- 깃허브
- 제이쿼리
- 면접
- 셋업
- 이클립스
- MySQL
- 폼태그
- 오라클
- 설정
- jquery
- html
- 자바
- 마이바티스
- Eclipse
- jsp 내부객체
- 깃허브 간단요약
- 스프링
- java
- Oracle
- jsp
- jstl
- EL태그
Archives
- Today
- Total
은은하게 코드 뿌시기
JAVA SQL DB 연동 소스 예제 본문
728x90
|
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
|
package kr.co.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DbSelectAll {
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection conn = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
Statement stmt = null; // java.sql
ResultSet rs = null; // java.sql
String url = "jdbc:mysql://localhost:3306/scott?characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false";
String id = "root";
String pw = "123456";
conn = DriverManager.getConnection(url, id, pw);
System.out.println("DB CONNECT");
//======================== 질의문을 작성
String query = "select * from emp";
// stmt 생성
stmt = conn.createStatement();
// 질의문 실행
rs = stmt.executeQuery(query);
System.out.println("emp 전체 조회");
System.out.println("EMPNO ENAME ~~ ");
// 인덱스로 받을수도잇고, 필드로도 받을수잇음.
while(rs.next()) {
//my sql db 는 인덱스 1부터
System.out.print(rs.getString(1) + " ");
System.out.print(rs.getString(2) + " ");
System.out.print(rs.getString(3) + " ");
System.out.print(rs.getString(4) + " ");
System.out.print(rs.getString(5) + " ");
System.out.print(rs.getString(6) + " ");
System.out.print(rs.getString(7) + " ");
System.out.println(rs.getString(8));
}
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
conn.close();
} 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
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
|
package kr.co.jdbc;
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.ArrayList;
import java.util.Scanner;
public class DBInserStudent {
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
PreparedStatement pstmt = null;
ArrayList<Student> list = new ArrayList<Student>();
try {
Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
String url = "jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false";
String id = "root";
String pw = "123456";
conn = DriverManager.getConnection(url, id, pw);
System.out.println("TEST DB CONN");
//========================================================================== insert
String sql = "INSERT INTO `test`.`student` ( `name`, `num`, `age`, `major`)"
+ " VALUES (?, ?, ?, ?)";
Scanner scan = new Scanner(System.in);
System.out.print("학생이름 : ");
String name = scan.nextLine();
System.out.print("학번 : ");
String num = scan.nextLine();
System.out.print("나이 : ");
String age = scan.nextLine();
System.out.print("학과 : ");
String major = scan.nextLine();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setString(2, num);
pstmt.setString(3, age);
pstmt.setString(4, major);
int r = pstmt.executeUpdate(); // insert, update, delete
if (r <= 0) {
System.out.println("데이터 입력 실패");
} else {
System.out.println("데이터 입력 성공");
}
//========================================================================== 조회
String qurey = "select * from student";
stmt = conn.createStatement();
rs = stmt.executeQuery(qurey);
//INSERT INTO `test`.`student` (`sid`, `name`, `num`, `age`, `major`)
//VALUES ('10', '천길동', '10', '18', '컴퓨터공학과');
while (rs.next()) {
int nsid = rs.getInt(1);
String sname = rs.getString(2);
int nnum = rs.getInt(3);
int nage = rs.getInt(4);
String smajor = rs.getString(5);
Student tmp = new Student(nsid, sname, nnum, nage, smajor);
list.add(tmp);
}
System.out.println("");
System.out.println("sid name num age major");
for (Student f : list) {
System.out.printf("%3d %4s %3d %3d %7s%n", f.getSid()
, f.getname()
, f.getNum()
, f.getAge()
, f.getMajor());
}
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
conn.close();
} 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
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
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
|
package kr.co.jdbc;
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.ArrayList;
import java.util.Scanner;
public class DBUpdateStudent {
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
PreparedStatement pstmt = null;
ArrayList<Student> list = new ArrayList<Student>();
try {
Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
String url = "jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false";
String id = "root";
String pw = "123456";
conn = DriverManager.getConnection(url, id, pw);
System.out.println("TEST DB CONN");
//========================================================================== 조회
String qurey = "select * from student";
stmt = conn.createStatement();
rs = stmt.executeQuery(qurey);
while (rs.next()) {
int nsid = rs.getInt(1);
String sname = rs.getString(2);
int nnum = rs.getInt(3);
int nage = rs.getInt(4);
String smajor = rs.getString(5);
Student tmp = new Student(nsid, sname, nnum, nage, smajor);
list.add(tmp);
}
System.out.println("");
System.out.println("sid name num age major");
for (Student f : list) {
System.out.printf("%3d %4s %3d %3d %7s%n", f.getSid()
, f.getname()
, f.getNum()
, f.getAge()
, f.getMajor());
}
list.clear(); //귀찮으니까 어레이 초기화
System.out.println();
//========================================================================== UPDATE
//UPDATE `test`.`student` SET `num` = '11', `age` = '18', `major` = '소재' WHERE (`sid` = '11');
//UPDATE `test`.`student` SET name = '잉기차나',`num` = '11', `age` = '18', `major` = '소재' WHERE (`sid` = '11');
Scanner scan = new Scanner(System.in);
System.out.print("변경할 항목의 sid: ");
String sid = scan.nextLine();
//sid가 존재 하는지 확인
//select * from test.student where sid = 13;
String sql = "select * "
+ "from test.student "
+ "where sid = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, sid);
rs = pstmt.executeQuery();
if (!rs.next()) {
System.out.println("존재 하지 않는 sid");
return;
}
String sql1 = "UPDATE test.student SET name = ? , num = ? , age = ? , major = ? "
+ "WHERE sid = ? ";
System.out.print("변경할 이름 : ");
String name = scan.nextLine();
System.out.print("변경할 번호 : ");
String snum = scan.nextLine();
System.out.print("변경할 나이 : ");
String sage = scan.nextLine();
System.out.print("변경할 학과 : ");
String major = scan.nextLine();
pstmt = conn.prepareStatement(sql1);
pstmt.setString(1, name);
pstmt.setString(2, snum);
// pstmt.setInt(3, Integer.parseInt(sage));
pstmt.setString(3, sage);
pstmt.setString(4, major);
pstmt.setString(5, sid);
System.out.println("쿼리수행");
int r = pstmt.executeUpdate(); // insert, update, delete
if (r <= 0) {
System.out.println("데이터 입력 실패");
} else {
System.out.println("데이터 입력 성공");
}
//========================================================================== 조회
String qurey1 = "select * from student";
stmt = conn.createStatement();
rs = stmt.executeQuery(qurey1);
while (rs.next()) {
int nsid = rs.getInt(1);
String sname = rs.getString(2);
int nnum = rs.getInt(3);
int nage = rs.getInt(4);
String smajor = rs.getString(5);
Student tmp = new Student(nsid, sname, nnum, nage, smajor);
list.add(tmp);
}
System.out.println("");
System.out.println("sid name num age major");
for (Student f : list) {
System.out.printf("%3d %4s %3d %3d %7s%n", f.getSid()
, f.getname()
, f.getNum()
, f.getAge()
, f.getMajor());
}
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
conn.close();
} 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
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
168
169
170
171
172
173
174
|
package kr.co.jdbc;
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.ArrayList;
import java.util.Scanner;
public class DBDeleteStudent {
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
PreparedStatement pstmt = null;
ArrayList<Student> list = new ArrayList<Student>();
try {
Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
String url = "jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false";
String id = "root";
String pw = "123456";
conn = DriverManager.getConnection(url, id, pw);
System.out.println("TEST DB CONN");
//========================================================================== 조회
String qurey = "select * from student";
stmt = conn.createStatement();
rs = stmt.executeQuery(qurey);
while (rs.next()) {
int nsid = rs.getInt(1);
String sname = rs.getString(2);
int nnum = rs.getInt(3);
int nage = rs.getInt(4);
String smajor = rs.getString(5);
Student tmp = new Student(nsid, sname, nnum, nage, smajor);
list.add(tmp);
}
System.out.println("");
System.out.println("sid name num age major");
for (Student f : list) {
System.out.printf("%3d %4s %3d %3d %7s%n", f.getSid()
, f.getname()
, f.getNum()
, f.getAge()
, f.getMajor());
}
list.clear(); //귀찮으니까 어레이 초기화
System.out.println();
//========================================================================== UPDATE
//UPDATE `test`.`student` SET `num` = '11', `age` = '18', `major` = '소재' WHERE (`sid` = '11');
//UPDATE `test`.`student` SET name = '잉기차나',`num` = '11', `age` = '18', `major` = '소재' WHERE (`sid` = '11');
Scanner scan = new Scanner(System.in);
System.out.print("삭제할 항목의 sid: ");
String sid = scan.nextLine();
//sid가 존재 하는지 확인
//select * from test.student where sid = 13;
String sql = "select * "
+ "from test.student "
+ "where sid = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, sid);
rs = pstmt.executeQuery();
if (!rs.next()) {
System.out.println("존재 하지 않는 sid");
return;
}
String sql1 = "delete from test.student "
+ "WHERE sid = ? ";
pstmt = conn.prepareStatement(sql1);
pstmt.setString(1, sid);
int r = pstmt.executeUpdate(); // insert, update, delete
if (r <= 0) {
System.out.println("데이터 입력 실패");
} else {
System.out.println("데이터 입력 성공");
}
//========================================================================== 조회
String qurey1 = "select * from student";
stmt = conn.createStatement();
rs = stmt.executeQuery(qurey1);
while (rs.next()) {
int nsid = rs.getInt(1);
String sname = rs.getString(2);
int nnum = rs.getInt(3);
int nage = rs.getInt(4);
String smajor = rs.getString(5);
Student tmp = new Student(nsid, sname, nnum, nage, smajor);
list.add(tmp);
}
System.out.println("");
System.out.println("sid name num age major");
for (Student f : list) {
System.out.printf("%3d %4s %3d %3d %7s%n", f.getSid()
, f.getname()
, f.getNum()
, f.getAge()
, f.getMajor());
}
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
|
cs |
728x90
'자바 > 자바 기본' 카테고리의 다른 글
| 쓰레드 우선순위 (0) | 2022.07.05 |
|---|---|
| 스레드 기초 및 예제 (0) | 2022.07.05 |
| JAVA execute, executeQuery, executeUpdate (0) | 2022.06.27 |
| 자바 JAVA FILE COPY 파일 복사/수정 간단 예제 (0) | 2022.06.17 |
| 자바 JAVA ArrayList 간단 예제 및 함수 (3) | 2022.06.16 |
Comments