CRUD
CRUD는 Create, Read, Update, Delete의 앞글자를 따서 만든 단어로 소프트웨어가 데이터를 처리하는 가장 기본적인 네 가지 기능을 의미하는 단어입니다.
예를 들면 쇼핑몰같은것도 CRUD가 연속되어서 처리되는 것이라고 할 수 있습니다. 판매자가 쇼핑몰을 열고 상품을 등록(Create)하고 신상품을 올리며(Update), 단종된 제품은 삭제(Delete) 합니다. 그리고 소비자나 관리자는 상품 목록을 끊임없이 조회(Read)하며 쇼핑몰이 돌아가게 됩니다. 이는 쇼핑몰의 장바구니나, 주문 조회, 로그인 정보 등에도 비슷하게 적용될 정도로 모든 소프트웨어에서 CRUD는 가장 기본적이면서도 많이 사용되는 기능이라고 할 수 있습니다.
각 CRUD는 다음과 같은 표준 SQL로 대응됩니다.
| CRUD | SQL |
|---|---|
| Create | INSERT |
| Read | SELECT |
| Update | UPDATE |
| Delete | DELETE |
CRUD 실습 준비
CRUD 실습 전에 실습을 위해 실습용 데이터베이스 테이블을 하나 생성합니다.
Oracle DB, Java 17, IntelliJ, SQL Developer를 사용해서 실습합니다.
Oracle DB에 로그인하고 다음 명령어를 입력해서 실습용 테이블을 하나 생성해주세요.
CREATE TABLE users (
id VARCHAR(30) PRIMARY KEY,
username VARCHAR(30) NOT NULL,
userpassword VARCHAR(30) NOT NULL
);
VARCHAR는 가변형 문자열 타입입니다.PRIMARY KEY는 테이블에서 각 행을 구분하기 위한 키로 테이블에 반드시 하나는 존재해야하는 키 입니다.NOT NULL은 해당 컬럼 값이 null이면 안됨을 표시합니다. 즉, 반드시 값이 있어야한다는 것을 의미합니다.
위 Query의 결과로 다음과 같은 테이블이 생성됩니다.
| id | username | userpassword |
|---|---|---|
Create, 생성
먼저 CRUD에서 C에 해당하는 생성(Craete), 즉 데이터 저장부터 해보겠습니다. 위의 준비를 그대로 따라했다면 SQL Developer로 확인했을 때 빈 테이블이 하나 있을 것 입니다.
이제 데이터를 한 행만 집어넣을 건데요. 이를 SQL 쿼리로 나타내면 다음과 같습니다.
INSERT INTO users (id, username, userpassword) VALUES("1", "Scott", "1234");이 명령을 JDBC를 이용해서 자바 코드를 통해 실행하려면 다음과 같이 String 타입의 문자열로 만들어줍니다.
String sql = "INSERT INTO users (id, username, userpassword)"
+ "VALUES(?,?,?)";이때 VALUES(?, ?, ?)는 매개변수화된 INSERT 구문으로 코드에서 PreparedStatement의 setString()을 통해 설정하게 됩니다.
실제 코드로 Create를 수행하는 모습을 확인하시겠습니다.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class CreateExample {
public static void main(String[] args) {
Connection conn = null;
String username = ""; //여기에 DB의 유저명
String password = ""; //여기에 DB의 비밀번호
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521/xe",
username,
password
);
System.out.println("Connected to database");
String sql = "INSERT INTO users (id, username, userpassword)"
+ "VALUES(?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "1");
ps.setString(2, "Scott");
ps.setString(3, "1234");
int rows = ps.executeUpdate();
System.out.println("Rows inserted: " + rows);
ps.close();
}
catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
}
finally {
if (conn != null) {
try {
conn.close();
System.out.println("Connection closed");
}
catch (SQLException e) {}
}
}
}
}전체 코드에서 약 7할 정도는 DB와 연결을 수행하고 끊고 그에 대한 예외 처리를 하는 구문입니다. 이는 지난번 포스팅에서 따로 다뤘으니 여기서는 이야기하지 않겠습니다.
지금은 CREATE 동작을 이해하는데 필요한 INSERT SQL을 실행하는 부분만 떼어와서 보겠습니다.
String sql = "INSERT INTO users (id, username, userpassword)"
+ "VALUES(?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "1"); //1번 매개변수에 "1"이라는 데이터 지정
ps.setString(2, "Scott"); //2번 매개변수에 "Scott"이라는 데이터 지정
ps.setString(3, "1234"); //3번 매개변수에 "1234"라는 데이터 지정
int rows = ps.executeUpdate();
System.out.println("Rows inserted: " + rows);
ps.close();매개변수화된 INSERT의 ?는 1부터 순서대로 번호가 지정됩니다. 그리고 이를 setString()에서 첫 번째 인수로 매개변수 번호를 적고, 두 번째 인수로 데이터를 적습니다.
이렇게 값을 지정한 수 executeUpdate() 메소드를 실행하면 SQL 쿼리가 실행되면서 users 테이블에 행이 하나 삽입되게 됩니다. 이때 executeUpdate() 메소드는 현재 테이블에 저장된 행 수를 반환합니다.
마지막으로 사용했던 PreparedStatement를 close()를 통해 종료하고 메모리를 반환해주면 Create에 대한 동작이 종료됩니다.
위 코드를 실행하면 다음과 같이 메세지들이 제대로 출력되면서 테이블에 행이 하나 추가됩니다.
SELECT를 이용해서 조회하던, GUI를 이용하던 제대로 데이터가 한 행 입력된 것을 볼 수 있죠?
Read 조회
Read는 방금 전에 본 Create, 이후에 볼 Update, Delete 동작과는 조금 다르게 사용합니다. 데이터를 excuteQuery()로 가져온다는 점이나 가져온 데이터를 ResultSet 객체를 이용해서 저장하고 활용한다는 점이 다릅니다.
우리가 조회를 위해 사용할 SQL 쿼리는 다음과 같습니다.
SELECT id, username, userpassword FROM users WHERE id="1";위 쿼리를 매개변수화된 SELECT 구문으로 변경하면 다음과 같습니다.
String sql = "SELECT id, username, userpassword "
+ "FROM users "
+ "WHERE id=?";다음은 조회를 수행하는 전체 코드입니다.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
public class ReadExample {
public static void main(String[] args) {
Connection conn = null;
String username = "";
String password = "";
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521/xe",
username,
password
);
System.out.println("Connected to database");
String sql = "SELECT id, username, userpassword "
+ "FROM users "
+ "WHERE id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "1");
ResultSet rs = ps.executeQuery();
if (rs.next()) {
System.out.println("ID: " + rs.getInt(1));
System.out.println("Username: " + rs.getString(2));
System.out.println("Userpassword: " + rs.getString(3));
}
else {
System.out.println("No user found");
}
rs.close();
ps.close();
}
catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
}
finally {
if (conn != null) {
try {
conn.close();
System.out.println("Connection closed");
}
catch (SQLException e) {
}
}
}
}
}ResultSet의 동작
기존과 동일하지만 조회 결과를 ResultSet이라는 Set 컬렉션에 담아서 반환합니다. 이때 ResultSet은 커서(cursor)라는 개념을 가지고 데이터를 읽게 됩니다.
커서는 가져온 Set의 행을 가리킵니다. 그래서 우리는 커서가 가리키는 행에 위치한 데이터만을 읽을 수 있습니다. 실제 ResultSet의 저장 구조는 다음과 같습니다.
실제 데이터의 첫 행 전에 빈 행 하나를 삽입하고 beforeFirst라는 이름을 붙입니다. 그리고 마지막 행 이후에 빈 행 하나를 삽입하고 afterLast라는 이름을 붙입니다.
커서는 next()메소드를 통해 조작합니다. beforeFirst에서 시작해서 afterLast 까지 이동하는데 데이터가 있으면 true, 데이터가 없으면 false를 반환합니다. 그래서 빈 행인 afterLast에 커서가 도달하면 next()는 false를 반환하게 됩니다.
그래서 1행만 읽을 때와 n행을 읽어낼 때의 코드는 다음과 같이 작성할 수 있습니다.
//ps는 PrepareStatement //1행만 읽는 경우 ResultSet rs = ps.executeQuery(); if (rs.next()) { //1 행 데이터 처리 코드 } else { //afterLast 행 처리 코드 } //n행을 읽는 경우 ResultSet rs = ps.executeQuery(); while(rs.next()) { //n행 데이터 처리 코드 } //afterLast 행 처리 코드 rs.close();마찬가지로 ResultSet도 사용 후
close()를 통해 종료와 메모리 해제를 해주어야합니다.
다시 본론으로 돌아와서 Read를 수행하는 실제 코드 부분을 보겠습니다.
String sql = "SELECT id, username, userpassword "
+ "FROM users "
+ "WHERE id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "1");
ResultSet rs = ps.executeQuery();
if (rs.next()) {
System.out.println("ID: " + rs.getInt(1));
System.out.println("Username: " + rs.getString(2));
System.out.println("Userpassword: " + rs.getString(3));
}
else {
System.out.println("No user found");
}
rs.close();
ps.close();매개변수화된 SELECT에 조회하고자 하는 키인 id를 넘기고 excuteQuery()를 통해 ResultSet으로 결과를 받습니다. 그리고 제어구문을 통해서 읽어낸 데이터를 출력하도록 했습니다.
데이터가 제대로 출력되었음을 볼 수 있습니다.
Update, 수정
다음은 위에서 저장했던 데이터를 수정하는 Update 기능을 구현해보겠습니다.
username, userpassword 두 가지 컬럼을 변경하는 수정 SQL 구문은 다음과 같습니다. (id는 식별용 키이므로 여기서는 변경하지 않습니다.)
UPDATE users SET
username="Tom",
userpassword="5678"
WHERE id="1"id가 1인 행을 찾아 username과 userpassword를 변경하는 SQL 쿼리입니다. 마찬가지로 이 쿼리를 매개변수화된 UPDATE 문으로 바꾸면 다음과 같습니다.
String sql = "UPDATE users SET "
+ "username=?, "
+ "userpassword=? "
+ "WHERE id=?"다음은 Update를 수행하는 전체 코드입니다.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class UpdateExample {
public static void main(String[] args) {
Connection conn = null;
String username = "";
String password = "";
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521/xe",
username,
password
);
System.out.println("Connected to database");
String sql = "UPDATE users SET "
+ "username=?, "
+ "userpassword=? "
+ "WHERE id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "Tom");
ps.setString(2, "5678");
ps.setString(3, "1");
int rows = ps.executeUpdate();
System.out.println("Rows updated: " + rows);
ps.close();
}
catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
}
finally {
if (conn != null) {
try {
conn.close();
System.out.println("Connection closed");
}
catch (SQLException e) {
}
}
}
}
}마찬가지로 실제로 Update를 수행하는 부분만 가져와서 설명하겠습니다.
String sql = "UPDATE users SET "
+ "username=?, "
+ "userpassword=? "
+ "WHERE id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "Tom");
ps.setString(2, "5678");
ps.setString(3, "1");
int rows = ps.executeUpdate();
System.out.println("Rows updated: " + rows);
ps.close();Create때와 동일하게 PreparedStatement와 매개변수화된 UPDATE 구문의 ?의 번호를 이용해서 변경할 값을 setString()을 통해서 전달합니다.
Delete, 삭제
마지막으로 Delete, 삭제 기능을 구현해보겠습니다.
삭제를 수행하는 SQL 쿼리문은 다음과 같습니다.
DELETE FROM users WHERE id="1";이 역시 매개변수화된 DELETE 문으로 바꿔줍니다.
String sql = "DELETE FROM users WHERE id=?"다음은 Delete를 수행하는 전체 코드입니다.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DeleteExample {
public static void main(String[] args) {
Connection conn = null;
String username = "";
String password = "";
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521/xe",
username,
password
);
System.out.println("Connected to database");
String sql = "DELETE FROM users WHERE id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, 1);
int rows = ps.executeUpdate();
System.out.println("Rows deleted: " + rows);
ps.close();
}
catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
}
finally {
if (conn != null) {
try {
conn.close();
System.out.println("Connection closed");
}
catch (SQLException e) {
}
}
}
}
}역시 연결해제 과정이 길기 때문에 실제로 삭제를 수행하는 부분만 확인하겠습니다.
String sql = "DELETE FROM users WHERE id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "1");
int rows = ps.executeUpdate();
System.out.println("Rows deleted: " + rows);
ps.close();이제는 설명할게 없죠. 그냥 DELETE 쿼리문을 executeUpdate()로 실행했습니다. 그 후 SELECT 문으로 조회해보면 삭제되어서 더이상 데이터가 없기 때문에 조회할 수 없다고 나옵니다.
이렇게 애플리케이션에서 데이터 처리의 기본이 되는 4가지 동작인 CRUD 제어를 하는 법에 대해서 알아보았습니다. 정말 중요한 기본 4가지 동작이기 때문에 잘 기억했다가 잘 활용하는 것이 중요하다고 할 수 있습니다.
