1) JDBC๋ฅผ ์ด์ฉํ CRUD ์ค์ต
(1) ์ด๊ธฐ ์ค์
โ ์ค์ต ํ๊ฒฝ
- MySQL 8.0
- JDK 11 (IDE : IntelliJ)
- Spring boot 2.7
โก ์ค์ต ํ ์ด๋ธ ์์ฑ
// ํ
์ด๋ธ ์์ฑ
CREATE TABLE MEMBER (
ID INT not null auto_increment,
NAME VARCHAR(100) not null,
JOB VARCHAR(30) not null,
primary key (ID));
// ์ด๊ธฐ ๋ฐ์ดํฐ ์์ฑ
INSERT INTO MEMBER VALUES (1, "userA", 'Student');
INSERT INTO MEMBER VALUES (2, "userB", 'Student');
INSERT INTO MEMBER VALUES (3, "userC", 'Student');
โข application.properties ์ค์
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/example?serverTimezone=UTC&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=root
โฃ dependency in build.gradle ์ค์
dependencies {
implementation 'org.springframework.boot:spring-boot-starter-jdbc'
compileOnly 'org.projectlombok:lombok'
runtimeOnly 'com.mysql:mysql-connector-j'
runtimeOnly 'com.oracle.database.jdbc:ojdbc8'
annotationProcessor 'org.projectlombok:lombok'
testImplementation 'org.springframework.boot:spring-boot-starter-test'
}
(2) Create [INSERT] ์ค์ต in CRUD
์ค์ต ์ฝ๋
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1. JDBC ๋๋ผ์ด๋ฒ ๋ก๋
Class.forName("com.mysql.cj.jdbc.Driver");
// DB ์ ์ ์ ๋ณด
// url์ DBMS ์ ํ๋ง๋ค Format์ด ๋ค๋ฅผ ์ ์๋ค.
String url = "jdbc:mysql://localhost:3306/sesac?serverTimezone=Asia/Seoul&characterEncoding=UTF-8";
String id = "root";
String pwd = "root";
// 2. Connection ๊ฐ์ฒด ์์ฑ
Connection conn = DriverManager.getConnection(url, id, pwd);
conn.setAutoCommit(false);
// 3. Statement ๊ฐ์ฒด ์์ฑ
Statement stmt = conn.createStatement();
// 4. Query ์คํ
// 5. ResultSet → Select๋ฅผ ํตํ ์กฐํ ์ฟผ๋ฆฌ๊ฐ ์๋๊ธฐ ๋๋ฌธ์ ResultSet ๊ฐ์ฒด๋ฅผ ์ด์ฉํ์ง ์๋๋ค.
int result = stmt.executeUpdate("INSERT INTO MEMBER VALUES (4, \"userD\", 'Student');");
// Query๊ฐ ์ ๋๋ก ์คํ๋ ๊ฒฝ์ฐ
if (result == 1) {
System.out.println("๋ฐ์ดํฐ ์
๋ ฅ ์๋ฃ!");
conn.commit();
}
// Query๊ฐ ์ ๋๋ก ์คํ๋์ง ์์ ๊ฒฝ์ฐ
else {
System.out.println("๋ฐ์ดํฐ ์
๋ ฅ ์คํจ!");
conn.rollback();
}
// 6. ResultSet, Statement, Connection ๊ฐ์ฒด close()
stmt.close();
conn.close();
}
๊ฒฐ๊ณผ
MySQL [sesac]> select * from member;
+----+-------+---------+
| ID | NAME | JOB |
+----+-------+---------+
| 1 | userA | Student |
| 2 | userB | Student |
| 3 | userC | Student |
| 4 | userD | Student |
+----+-------+---------+
4 rows in set (0.000 sec)
(3) Read [SELECT] ์ค์ต in CRUD
์ค์ต ์ฝ๋
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1. JDBC ๋๋ผ์ด๋ฒ ๋ก๋
Class.forName("com.mysql.cj.jdbc.Driver");
// DB ์ ์ ์ ๋ณด
// url์ DBMS ์ ํ๋ง๋ค Format์ด ๋ค๋ฅผ ์ ์๋ค.
String url = "jdbc:mysql://localhost:3306/sesac?serverTimezone=Asia/Seoul&characterEncoding=UTF-8";
String id = "root";
String pwd = "root";
// 2. Connection ๊ฐ์ฒด ์์ฑ
Connection conn = DriverManager.getConnection(url, id, pwd);
conn.setAutoCommit(false);
// 3. PreparedStatement ๊ฐ์ฒด ์์ฑ
Statement stmt = conn.createStatement();
// 4. Query ์คํ
ResultSet resultSet = stmt.executeQuery("SELECT * FROM MEMBER;");
// 5. ResultSet ๊ฐ์ฒด๋ก๋ถํฐ ๋ฐ์ดํฐ ์กฐํ
while(resultSet.next()) {
int num = resultSet.getInt("id");
String name = resultSet.getString("name");
String job = resultSet.getString("job");
System.out.println(num + ", " + name + ", " + job);
}
// 6. ResultSet, Statement, Connection ๊ฐ์ฒด close()
resultSet.close();
stmt.close();
conn.close();
}
๊ฒฐ๊ณผ
1, userA, Student
2, userB, Student
3, userC, Student
4, userD, Student
(4) Update ์ค์ต in CRUD
- ์ด๋ฒ ์ค์ต๋ถํฐ๋ Statement ๊ฐ์ฒด๊ฐ ์๋ PreparedStatement ๊ฐ์ฒด๋ฅผ ์ด์ฉํ๋ค!
์ค์ต ์ฝ๋
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1. JDBC ๋๋ผ์ด๋ฒ ๋ก๋
Class.forName("com.mysql.cj.jdbc.Driver");
// DB ์ ์ ์ ๋ณด
// url์ DBMS ์ ํ๋ง๋ค Format์ด ๋ค๋ฅผ ์ ์๋ค.
String url = "jdbc:mysql://localhost:3306/sesac?serverTimezone=Asia/Seoul&characterEncoding=UTF-8";
String id = "root";
String pwd = "root";
// 2. Connection ๊ฐ์ฒด ์์ฑ
Connection conn = DriverManager.getConnection(url, id, pwd);
conn.setAutoCommit(false);
// 3. PreparedStatement ๊ฐ์ฒด ์์ฑ
String sql = "UPDATE MEMBER SET JOB = ? WHERE JOB = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "SalesMan");
pstmt.setString(2, "Student");
// 4. Query ์คํ
// 5. ResultSet → Select๋ฅผ ํตํ ์กฐํ ์ฟผ๋ฆฌ๊ฐ ์๋๊ธฐ ๋๋ฌธ์ ResultSet ๊ฐ์ฒด๋ฅผ ์ด์ฉํ์ง ์๋๋ค.
int result = pstmt.executeUpdate();
// Query๊ฐ ์ ๋๋ก ์คํ๋ ๊ฒฝ์ฐ
if (result >= 1) {
System.out.println("๋ฐ์ดํฐ ์
๋ ฅ ์๋ฃ");
conn.commit();
}
// Query๊ฐ ์ ๋๋ก ์คํ๋์ง ์์ ๊ฒฝ์ฐ
else {
System.out.println("๋ฐ์ดํฐ ์
๋ ฅ ์คํจ");
conn.rollback();
}
// 6. ResultSet, Statement, Connection ๊ฐ์ฒด close()
pstmt.close();
conn.close();
}
๊ฒฐ๊ณผ
MySQL [sesac]> select * from member;
+----+-------+----------+
| ID | NAME | JOB |
+----+-------+----------+
| 1 | userA | SalesMan |
| 2 | userB | SalesMan |
| 3 | userC | SalesMan |
| 4 | userD | SalesMan |
+----+-------+----------+
4 rows in set (0.001 sec)
(5) Delete ์ค์ต in CRUD
์ค์ต ์ฝ๋
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1. JDBC ๋๋ผ์ด๋ฒ ๋ก๋
Class.forName("com.mysql.cj.jdbc.Driver");
// DB ์ ์ ์ ๋ณด
// url์ DBMS ์ ํ๋ง๋ค Format์ด ๋ค๋ฅผ ์ ์๋ค.
String url = "jdbc:mysql://localhost:3306/sesac?serverTimezone=Asia/Seoul&characterEncoding=UTF-8";
String id = "root";
String pwd = "root";
// 2. Connection ๊ฐ์ฒด ์์ฑ
Connection conn = DriverManager.getConnection(url, id, pwd);
conn.setAutoCommit(false);
// 3. PreparedStatement ๊ฐ์ฒด ์์ฑ
String sql = "DELETE FROM MEMBER WHERE ID = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 4);
// 4. Query ์คํ
// 5. ResultSet → Select๋ฅผ ํตํ ์กฐํ ์ฟผ๋ฆฌ๊ฐ ์๋๊ธฐ ๋๋ฌธ์ ResultSet ๊ฐ์ฒด๋ฅผ ์ด์ฉํ์ง ์๋๋ค.
int result = pstmt.executeUpdate();
// Query๊ฐ ์ ๋๋ก ์คํ๋ ๊ฒฝ์ฐ
if (result >= 1) {
System.out.println("๋ฐ์ดํฐ ์
๋ ฅ ์๋ฃ");
conn.commit();
}
// Query๊ฐ ์ ๋๋ก ์คํ๋์ง ์์ ๊ฒฝ์ฐ
else {
System.out.println("๋ฐ์ดํฐ ์
๋ ฅ ์คํจ");
conn.rollback();
}
// 6. ResultSet, Statement, Connection ๊ฐ์ฒด close()
pstmt.close();
conn.close();
}
๊ฒฐ๊ณผ
MySQL [sesac]> select * from member;
+----+-------+---------+
| ID | NAME | JOB |
+----+-------+---------+
| 1 | userA | Student |
| 2 | userB | Student |
| 3 | userC | Student |
+----+-------+---------+
3 rows in set (0.000 sec)
[Reference]
๋ฐ์ํ
'๐จโ๐ป Back End > JDBC' ์นดํ ๊ณ ๋ฆฌ์ ๋ค๋ฅธ ๊ธ
Synchrozied ํค์๋๋ฅผ ์ด์ฉํ ๋์์ฑ ์ด์ ํด๊ฒฐ (0) | 2023.08.28 |
---|---|
์ปค๋ฅ์ ํ์ด๋ (Connection Pool) (0) | 2023.08.28 |
Statement๋ณด๋ค PreparedStatement๋ฅผ ์ฌ์ฉํด์ผ ํ๋ ์ด์ (2) | 2023.08.27 |
JDBC์์ ์์ฃผ ์ฌ์ฉ๋๋ ๋ฉ์๋ ์ ๋ฆฌ (0) | 2023.08.26 |
Java์์ DB ์ฐ๋์ ์ํ JDBC์ ๋ํด ์์๋ณด์! (0) | 2023.08.26 |