Jdbc
특징: 완성된 SQL문을 실행할 수 있는 객체임 Connection객체에 의해 프로그램에 구현되는 일종의 메소드 집합 Connection클래스의 createStatement()를 호출하여 객체 생성 Statement객체로 SQL문을 String객체로 담아 인자값으로 전달하여 질의 수행 | | Result Set | SELECT문을 사용한 질의 성공시 반환되는 객체(만일 실행한 SQL문이 SELECT문일 경우 조회된 결과가 result set객체에 들어감)SQL 질의에 해당하는 결과를 담고 있으며 ‘커서(CURSOR)’를 이용하여 특정 행에 대한 참조를 조작 |
JDBC를 이용한 데이터베이스 연동과정
.png)
1. JDBC 드라이버 로드
1
Class.forName("com.mysql.Jdbc.Driver");
Class.forName() 메서드를 호출하여, mysql에서 제공하는 Driver 클래스를 JVM method area에 로딩시킨다.
2. 데이터베이스 연결
1
2
String jdbc_url = "jdbc:mysql://localhost:3306/datebase?serverTimezone=UTC";
Connection con = DriverManager.getConnection(URL, "user", "password");
- url 의 localhost:3306 (로컬 환경에서 MySQL의 포트번호가 3306이기 때문), 그리고 database는 스키마 이름을 지정하면 됨
- 이제 Connection 객체를 만들어 사용하게 되는데 방법은 DriverManager 클래스의 static 메서드인 getConnection() 메서드를 호출해서, mysql에 연결하기 위한 커넥션 정보(url, user, password)를 입력한다.
- getConnection() 메서드 수행 결과로 Connection 객체를 반환하는데, 이 객체를 통해 쿼리를 날리는 statement를 작성할 수 있다. SELECT 쿼리에서는
createStatement(), INSERT에서는prepareStatement()를 호출한다.
3. SQL을 위한 객체생성
1
Statement stmt = con.createStatement();
4. SQL 문장 실행
1
2
String sql = "select * from student";
ResultSet result = stmt.executeQuery(sql);
select 문장은 테이블 형태의 결과를 반환.
그러므로 select 문장을 실행하기 위해 Statement의 메소드 executeQuery()를 사용한다.
메소드 executeQuery()는 질의 결과로 테이블 형태의 결과를 반환하는데, 이 반환형이 인터페이스 ResultSet이다.
객체 Statement의 메소드 **executeUpdate()**는 create 또는 drop, insert, delete, update와 같이 테이블의 내용을 변경하는 문장에 사용한다.
기초실습 및 예제
실습 1. Create Database
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Main {
public static void main(String[] args) {
try {
String URL = "jdbc:mysql://127.0.0.1:3306";
String user = "root";
String password = "!cjftn&dudgml617";
Connection conn = DriverManager.getConnection(URL, user, password);
Statement stmt = conn.createStatement();
**String sql ="CREATE DATABASE IF NOT EXISTS testDatabase";
stmt.execute(sql);**
System.out.println("데이터 베이스가 생성되었습니다.");
} catch (SQLException e){
e.printStackTrace();
}
}
}
실습 2. Create table
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
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class CreateTables {
public static void main(String[] args) {
try {
String URL = "jdbc:mysql://127.0.0.1:3306/testdatabase";
String user = "root";
String password = "!cjftn&dudgml617";
Connection conn = DriverManager.getConnection(URL, user, password);
Statement stmt = conn.createStatement();
String sql ="CREATE TABLE IF NOT EXISTS Board ( " +
"id INT AUTO_INCREMENT PRIMARY KEY , " +
"title VARCHAR(255) NOT NULL , " +
"body TEXT NOT NULL)";
**stmt.executeUpdate(sql);**
System.out.println("테이블을 생성되었습니다.");
sql = "CREATE TABLE IF NOT EXISTS Member (" +
"id INT AUTO_INCREMENT PRIMARY KEY, " +
"name VARCHAR(255) NOT NULL, " +
"email VARCHAR(255) NOT NULL UNIQUE, " +
"board_id INT, " +
"FOREIGN KEY (board_id) REFERENCES Board(id))";
**stmt.executeUpdate(sql);**
System.out.println("Member 테이블을 생성했습니다.");
stmt.close();
conn.close();
} catch (SQLException e){
e.printStackTrace();
}
}
}
실습 3. Insert
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
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class InsertintoExample {
public static void main(String[] args) {
try {
String URL = "jdbc:mysql://127.0.0.1:3306/testdatabase";
String user = "root";
String password = "!cjftn&dudgml617";
Connection conn = DriverManager.getConnection(URL, user, password);
Statement stmt = conn.createStatement();
String sql = "INSERT INTO Board (title, body) VALUES " +
"('JAVA 프로그래밍', 'JAVA 프로그래밍에 대한 기초' ), " +
"('데이터베이스의 기초', 'SQL 데이터베이스 관리' ) ";
int boardRows= stmt.executeUpdate(sql);
System.out.println(boardRows+"개의 글이 테이블에 생성되었습니다.");
sql = "INSERT INTO Member (name, email, board_id) VALUES " +
"('박혜지', 'jerry@gmail.com', 1), " +
"('김러키', 'lucky @naver.com', 2 )";
stmt.executeUpdate(sql);
stmt.close();
conn.close();
} catch (
SQLException e) {
e.printStackTrace();
}
}
}
JDBC 실습 - Instagram
ERD보고 테이블 생성
바로가기
Connection, Statement객체생성, Example class / Create table / Insert DermyData / 조회관련문제 1-9번 / 삽입,수정,삭제 등 관련문제 10-17번
-
Connection, Statement객체생성, Example class
객체 생성부분을 따로 클래스로 만들어서 사용
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
import java.sql.*; public class Example { private String URL; private String user; private String password; private Connection conn; private Statement stmt; public Example() throws SQLException { this.URL = "jdbc:mysql://127.0.0.1:3306/instagram"; this.user = "root"; this.password = "!cjftn&dudgml617"; this.conn = DriverManager.getConnection(URL, user, password); this.stmt = conn.createStatement(); } public Connection getConn() { return conn; } public Statement getStmt() { return stmt; } }
-
Create table
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
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class Create { public static void main(String[] args) { try { Example ex = new Example(); String sql ="CREATE TABLE IF NOT EXISTS Users ( " + "id INT AUTO_INCREMENT PRIMARY KEY , " + "userName VARCHAR(50) NOT NULL , " + "password VARCHAR(50) NOT NULL)"; ex.getStmt().executeUpdate(sql); System.out.println("Users 테이블이 생성되었습니다."); sql = "CREATE TABLE IF NOT EXISTS Posts (" + "id INT AUTO_INCREMENT PRIMARY KEY, " + "image BLOB, " + "message TEXT, " + "created_at DATETIME, " + "total_likes INT, " + "total_comments INT, " + "user_id INT, " + "FOREIGN KEY (user_id) REFERENCES Users(id))"; ex.getStmt().executeUpdate(sql); System.out.println("Posts 테이블을 생성했습니다."); sql="CREATE TABLE IF NOT EXISTS Post_comments(" + "id INT AUTO_INCREMENT PRIMARY KEY, " + "comment VARCHAR(255), " + "created_at DATETIME, " + "user_id INT, " + "post_id INT, " + "FOREIGN KEY (user_id) REFERENCES Users(id), " + "FOREIGN KEY (post_id) REFERENCES Posts(id)) "; ex.getStmt().executeUpdate(sql); System.out.println("Post_comments 테이블을 생성했습니다."); sql= "CREATE TABLE IF NOT EXISTS Post_likes( " + "id INT AUTO_INCREMENT PRIMARY KEY, " + "created_at DATETIME, " + "user_id INT, " + "post_id INT, " + "FOREIGN KEY(user_id) REFERENCES Users(id), " + "FOREIGN KEY(post_id) REFERENCES Posts(id))"; ex.getStmt().executeUpdate(sql); System.out.println("Post_likes 테이블을 생성했습니다."); sql="CREATE TABLE IF NOT EXISTS Hashtags( " + "id INT AUTO_INCREMENT PRIMARY KEY, " + "name CHAR(50))"; ex.getStmt().executeUpdate(sql); System.out.println("Hashtags 테이블을 생성했습니다."); sql = "CREATE TABLE IF NOT EXISTS Posts_hashtags( " + "id INT AUTO_INCREMENT PRIMARY KEY, " + "hashtag_id INT, " + "post_id INT, " + "FOREIGN KEY (hashtag_id) REFERENCES Hashtags(id), " + "FOREIGN KEY (post_id) REFERENCES Posts(id)) "; ex.getStmt().executeUpdate(sql); System.out.println("Posts_hashtags 테이블을 생성했습니다."); sql="CREATE TABLE IF NOT EXISTS Follow_follower( " + "id INT AUTO_INCREMENT PRIMARY KEY, " + "follow_id INT," + "user_id INT," + "FOREIGN KEY (follow_id) REFERENCES Follow_follower(id), " + "FOREIGN KEY (user_id) REFERENCES Users(id))"; ex.getStmt().executeUpdate(sql); System.out.println("Follow_follower 테이블을 생성했습니다."); ex.getStmt().close(); ex.getConn().close(); } catch (SQLException e){ e.printStackTrace(); } } }
-
INSERT
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
import java.sql.SQLException; public class INSERT { public static void main(String[] args) { try { Example ex = new Example(); String sqlInsertUsers = "INSERT INTO users (username, password) VALUES " + "('user1', 'password1'), ('user2', 'password2'), ('user3', 'password3'), " + "('user4', 'password4'), ('user5', 'password5'), ('user6', 'password6'), " + "('user7', 'password7'), ('user8', 'password8'), ('user9', 'password9'), " + "('user10', 'password10')"; int usersRows = ex.getStmt().executeUpdate(sqlInsertUsers); System.out.println(usersRows+"개의 글이 테이블에 생성되었습니다."); String sqlInsertPosts = "INSERT INTO posts (image, message, user_id) VALUES " + "(NULL, 'Hello World', 1), (NULL, 'Learning Java', 2), " + "(NULL, 'At the beach', 3), (NULL, 'Eating pizza', 4), " + "(NULL, 'Watching a movie', 5), (NULL, 'At a concert', 6), " + "(NULL, 'Playing games', 7), (NULL, 'Reading a book', 8), " + "(NULL, 'Visiting museums', 9), (NULL, 'Going hiking', 10)"; int sqlInsertPostsRows = ex.getStmt().executeUpdate(sqlInsertPosts ); System.out.println(sqlInsertPostsRows +"개의 글이 테이블에 생성되었습니다."); String sqlInsertComments = "INSERT INTO post_comments (comment, user_id, post_id) VALUES " + "('Nice post!', 1, 1), ('Love this!', 2, 1), ('So cool!', 3, 2), " + "('Wow!', 4, 3), ('Great pic!', 5, 3), ('Can''t agree more!', 6, 4), " + "('LOL', 7, 5), ('That''s awesome!', 8, 6), ('Yummy!', 9, 7), ('Interesting!', 10, 8)"; int sqlInsertCommentsRows = ex.getStmt().executeUpdate(sqlInsertComments); System.out.println(sqlInsertCommentsRows +"개의 글이 테이블에 생성되었습니다."); String sqlInsertLikes = "INSERT INTO post_likes (user_id, post_id) VALUES " + "(1, 1), (2, 1), (3, 1), (4, 2), (5, 2), " + "(6, 3), (7, 3), (8, 4), (9, 5), (10, 5)"; int sqlInsertLikesRows = ex.getStmt().executeUpdate(sqlInsertLikes ); System.out.println(sqlInsertLikesRows +"개의 글이 테이블에 생성되었습니다."); String sqlInsertFollows = "INSERT INTO follow_follower (follower_id, user_id) VALUES " + "(1, 2), (1, 3), (2, 3), (2, 4), (3, 4), " + "(3, 5), (4, 5), (4, 6), (5, 6), (5, 7)"; int sqlInsertFollowsRows = ex.getStmt().executeUpdate(sqlInsertFollows ); System.out.println(sqlInsertFollowsRows +"개의 글이 테이블에 생성되었습니다."); String sqlInsertHashtags = "INSERT INTO hashtags (name) VALUES " + "('summer'), ('java'), ('beach'), ('pizza'), ('movie'), " + "('concert'), ('games'), ('book'), ('museum'), ('hiking')"; int sqlInsertHashtagsRows = ex.getStmt().executeUpdate(sqlInsertHashtags ); System.out.println(sqlInsertHashtagsRows +"개의 글이 테이블에 생성되었습니다."); String sqlInsertPostsHashtags = "INSERT INTO posts_hashtags (hashtag_id, post_id) VALUES " + "(1, 1), (2, 2), (3, 3), (4, 4), (5, 5), " + "(6, 6), (7, 7), (8, 8), (9, 9), (10, 10)"; int sqlInsertPostsHashtagsRows = ex.getStmt().executeUpdate(sqlInsertPostsHashtags ); System.out.println(sqlInsertPostsHashtagsRows +"개의 글이 테이블에 생성되었습니다."); ex.getStmt().close(); ex.getConn().close(); } catch (SQLException e){ e.printStackTrace(); } } }
-
SELECTION
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
import java.sql.*; public class Selection { public static void main(String[] args) { try { Example ex = new Example(); String result ="SELECT userName from users "; ResultSet rsResult=ex.getStmt().executeQuery(result); System.out.println("-".repeat(40)); System.out.println("문제 1"); while(rsResult.next()){ System.out.println(rsResult.getString("userName")); } System.out.println("-".repeat(40)); rsResult.close(); result = "SELECT message,created_at FROM Posts "; rsResult = ex.getStmt().executeQuery(result); System.out.println("문제 2"); while(rsResult.next()){ System.out.println("message: "+rsResult.getString("message")+", created_at : "+rsResult.getString("created_at")); } System.out.println("-".repeat(40)); rsResult.close(); result="SELECT u.userName from Users u JOIN posts p " + "ON p.user_id = u.id WHERE p.id>=1 "; rsResult=ex.getStmt().executeQuery(result); System.out.println("문제 3"); while(rsResult.next()){ System.out.println("사용자 이름 :"+rsResult.getString("userName") ); } System.out.println("-".repeat(40)); rsResult.close(); result ="SELECT message FROM posts p WHERE created_at >= DATE_ADD( NOW(), INTERVAL -1 DAY)"; rsResult =ex.getStmt().executeQuery(result); System.out.println("문제 4"); while(rsResult.next()){ System.out.println("message :" + rsResult.getString("message")); } System.out.println("-".repeat(40)); rsResult.close(); result ="SELECT u.userName, COUNT(p.id) from users u JOIN posts p ON p.user_id = u.id GROUP BY u.userName; "; rsResult = ex.getStmt().executeQuery(result); System.out.println("문제 5"); while(rsResult.next()){ System.out.println( "사용자 이름 : "+rsResult.getString("userName")+" , 게시글 수 :" + rsResult.getString("COUNT(p.id)")); } System.out.println("-".repeat(40)); rsResult.close(); result = "SELECT message FROM posts WHERE message like '%;like%'"; rsResult=ex.getStmt().executeQuery(result); System.out.println("문제 6"); while(rsResult.next()){ System.out.println("like in message :" + rsResult.getString("message") ); } System.out.println("-".repeat(40)); rsResult.close(); result="SELECT p.message,count(pl.post_id) as conutlike FROM posts p " + "JOIN post_likes pl ON pl.post_id=p.id group by post_id having conutlike >=5"; rsResult =ex.getStmt().executeQuery(result); System.out.println("문제 7"); while(rsResult.next()){ System.out.println("like 5 in message :" + rsResult.getString("message") ); } System.out.println("-".repeat(40)); rsResult.close(); result ="SELECT p.id as post_id, COUNT(pc.id)as commentNumber FROM post_comments pc " + "JOIN posts p ON pc.post_id = p.id group by p.id"; rsResult =ex.getStmt().executeQuery(result); System.out.println("문제 8"); while (rsResult.next()){ System.out.println("post_id : "+rsResult.getString("post_id")+" , comment count : " +rsResult.getString("commentNumber")); } System.out.println("-".repeat(40)); rsResult.close(); result="SELECT u.userName,count(ff.follower_id)as follower_number FROM Users u JOIN follow_follower ff ON ff.user_id = u.id " + "group by userName " + "having follower_number = (select max(followerCnt) from (select u2.userName,count(ff2.follower_id)as followerCnt from users u2 " + "join follow_follower ff2 on ff2.user_id = u2.id " + "group by u2.userName)as sub);"; System.out.println("문제 9"); rsResult= ex.getStmt().executeQuery(result); while(rsResult.next()){ System.out.println("가장 많은 팔로워 수를 가지는 사용자 이름 : " +rsResult.getString("u.userName") + ", 팔로워 수 :" +rsResult.getString("follower_number")); } System.out.println("-".repeat(40)); rsResult.close(); ex.getStmt().close(); ex.getConn().close(); } catch (SQLException e) { e.printStackTrace(); } } }
- 문제 1
-
강사님코드
1 2 3 4 5 6 7 8 9 10
String SQL = "SELECT username, password FROM Users"; ResultSet rs = statement.executeQuery(SQL); while(rs.next()) { System.out.println(rs.getString("username") + " " + rs.getString("password")); } } }
-
- 문제 2
-
강사님 코드
1 2 3 4 5 6 7
String SQL = "SELECT message, created_at FROM Posts"; ResultSet rs = statement.executeQuery(SQL); while(rs.next()) { System.out.println(rs.getString("message") + " " + rs.getString("created_at")); }
-
- 문제 3
-
강사님코드
1 2 3 4 5 6 7 8
String SQL = "select **distinct** u.username from users u " + "join posts p " + "on u.id = p.user_id "; ResultSet rs = statement.executeQuery(SQL); while(rs.next()) { System.out.println(rs.getString("username"));
userName에 중복이 있을 수 있으니 DISTINCT 적어서 중 제거해주어야 함.
-
- 문제 4
-
강사님 코드✔️✔️
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
String SQL = "SELECT message FROM Posts " + "WHERE created_at >= CURRENT_TIMESTAMP - INTERVAL 1 DAY"; ResultSet rs = statement.executeQuery(SQL); // rs.last(); // int length = rs.getRow(); // rs.beforeFirst(); // System.out.println(length); int count = 0; while(rs.next()) { System.out.println(rs.getString("message")); count++; } System.out.println(count); } }
-
- 문제 5
-
강사님 코드
1 2 3 4 5 6 7 8 9 10 11
String SQL = "SELECT u.username as username , COUNT(p.id) AS post_count " + "FROM Users as u " + "left join Posts as p ON u.id = p.user_id " + "GROUP BY u.username"; ResultSet rs = statement.executeQuery(SQL); while(rs.next()) { System.out.println(rs.getString("username") + " " + rs.getString("post_count")); }
게시글 수 가 0인 user도 조회해야 하므로 Outer join 해야됨
-
- 문제 6
-
강사님 코드
1 2 3 4 5 6 7
String SQL = "SELECT message FROM Posts " + "WHERE message LIKE '%like%'"; ResultSet rs = statement.executeQuery(SQL); while(rs.next()) { System.out.println(rs.getString("message")); }
-
- 문제 7
-
강사님 코드
1 2 3 4 5 6 7 8 9 10 11
String SQL = "SELECT p.id as id, count(pl.id) as count from Posts as p " + "join post_likes as pl " + "on pl.post_id = p.id " + "group by p.id " + "having count >= 5"; ResultSet rs = statement.executeQuery(SQL); while(rs.next()) { System.out.println(rs.getString("id") + " " + rs.getInt("count")); }
-
- 문제 8
-
강사님 코드
1
-
Outer join 사용해야 함, 댓글이 없는 posts도 Count 해야하므로
-
- 문제 9
-
강사님코드
1 2 3 4 5 6 7 8 9 10 11 12 13 14
String SQL = "select ff.follower_id , count(*) " + "from follow_follower ff " + "group by ff.follower_id " + "having count(*) = (select MAX(cnt) from ( " + "select count(*) as cnt " + "from follow_follower ff " + "group by ff.follower_id " + ") as sq)"; ResultSet rs = statement.executeQuery(SQL); while(rs.next()) { System.out.println(rs.getString("ff.follower_id") + " " + rs.getInt("count(*)")); }
-
- 문제 1
-
UPDATE&DELETE&INSERT
- 문제 10번~ 17번까지
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
import java.sql.SQLException; public class Update { public static void main(String[] args) { try { Example ex=new Example(); String ten= "UPDATE Users SET userName = 'newUser1' WHERE userName='user1'"; int updateRows = ex.getStmt().executeUpdate(ten); System.out.println("문제 10"); System.out.println(updateRows+"개의 사용자 이름이 변경되었습니다."); System.out.println("-".repeat(40)); String eleven="INSERT INTO post_likes (user_id, post_id) " + "SELECT user_id, 3 FROM post_likes WHERE id <= 5;"; updateRows = ex.getStmt().executeUpdate(eleven); System.out.println("문제 11"); System.out.println(updateRows+"개의 좋아요 수가 증가하였습니다."); System.out.println("-".repeat(40)); String twelve="UPDATE Posts p JOIN Users u ON u.id = p.user_id SET message = CONCAT(message,'-updated') WHERE u.id=2"; updateRows = ex.getStmt().executeUpdate(twelve); System.out.println("문제 12"); System.out.println(updateRows+"개의 게시글 수가 변경되었습니다."); System.out.println("-".repeat(40)); String thirteen="UPDATE posts p " + "join users u on p.user_id =u.id " + "SET message ='hello world' " + "WHERE u.id = 4;"; updateRows= ex.getStmt().executeUpdate(thirteen); System.out.println("문제 13,14"); System.out.println(updateRows+"개의 message가 변경되었습니다."); System.out.println("-".repeat(40)); String fifteen= "UPDATE Posts SET created_at = NOW() WHERE id = 1" ; updateRows= ex.getStmt().executeUpdate(fifteen); System.out.println("문제15"); System.out.println(updateRows+"개의 게시글의 생성날짜가 오늘날짜로 변경되었습니다."); System.out.println("-".repeat(40)); /* String seventeen0="ALTER TABLE follow_follower DROP FOREIGN KEY follow_follower_ibfk_2;"; stmt.executeUpdate(seventeen1); seventeen0= "ALTER TABLE follow_follower ADD CONSTRAINT follow_follower_ibfk_2 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE "; stmt.executeUpdate(seventeen1); seventeen0="ALTER TABLE post_likes DROP FOREIGN KEY post_likes_ibfk_1"; stmt.executeUpdate(seventeen1); seventeen0= "ALTER TABLE post_likes ADD CONSTRAINT post_likes_ibfk_1 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE "; stmt.executeUpdate(seventeen1); seventeen0="ALTER TABLE posts DROP FOREIGN KEY posts_ibfk_1;"; stmt.executeUpdate(seventeen1); String seventeen1 = "ALTER TABLE posts ADD CONSTRAINT posts_ibfk_1 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE "; stmt.executeUpdate(seventeen1); seventeen1="ALTER TABLE post_comments DROP FOREIGN KEY post_comments_ibfk_1;"; stmt.executeUpdate(seventeen1); seventeen1= "ALTER TABLE post_comments ADD CONSTRAINT post_comments_ibfk_1 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE "; stmt.executeUpdate(seventeen1); String seventeen2="ALTER TABLE posts_hashtags DROP FOREIGN KEY posts_hashtags_ibfk_2;"; stmt.executeUpdate(seventeen2); seventeen2="ALTER TABLE posts_hashtags ADD CONSTRAINT posts_hashtags_ibfk_2 FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE"; stmt.executeUpdate(seventeen2); */ String seventeen ="DELETE FROM Users WHERE id =10"; int deleteRows= ex.getStmt().executeUpdate(seventeen); System.out.println("문제17"); System.out.println(deleteRows+"개의 사용자가 삭제되었습니다."); System.out.println("-".repeat(40)); String seventeen3="ALTER TABLE post_comments DROP FOREIGN KEY post_comments_ibfk_2;"; ex.getStmt().executeUpdate(seventeen3); seventeen3="ALTER TABLE post_comments ADD CONSTRAINT post_comments_ibfk_2 FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE"; ex.getStmt().executeUpdate(seventeen3); seventeen3="ALTER TABLE post_likes DROP FOREIGN KEY post_likes_ibfk_2;"; ex.getStmt().executeUpdate(seventeen3); seventeen3="ALTER TABLE post_likes ADD CONSTRAINT post_likes_ibfk_2 FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE"; ex.getStmt().executeUpdate(seventeen3); String sixteen="DELETE FROM Posts p WHERE p.user_id =5"; deleteRows= ex.getStmt().executeUpdate(sixteen); System.out.println("문제16"); System.out.println(deleteRows+"개의 게시글이 삭제되었습니다."); System.out.println("-".repeat(40)); ex.getStmt().close(); ex.getConn().close(); }catch(SQLException e){ e.printStackTrace(); } } }
- 문제 10
- 강사님코드
1 2 3
String SQL = "UPDATE Users SET username = 'newUSer1' WHERE username = 'user1'"; statement.executeUpdate(SQL);
- 문제 11
- 강사님코드
1 2 3 4
String SQL = "INSERT INTO post_likes (user_id, post_id) VALUES " + "(6, 3), (7, 3), (8, 3), (9, 3), (2, 3);"; int count = statement.executeUpdate(SQL);
- 문제 12
- 강사님 코드
1 2 3 4
String SQL = "UPDATE Posts SET message = " + "CONCAT(message, ' - updated') " + "WHERE user_id = 2"; int count = statement.executeUpdate(SQL);
- 문제 13
- 강사님 코드
1 2 3 4
String SQL = "UPDATE Posts SET message = " + "'hello world' " + "WHERE user_id = 4"; int count = statement.executeUpdate(SQL);
Posts에서 user_id를 가지고 있기 때문에 join할 필요가 없음
- 문제 14- 문제 13과 동일
- 문제 15
- 문제 16
-
강사님 코드
1 2 3 4
String SQL = "DELETE FROM Posts WHERE user_id = 5"; int count = statement.executeUpdate(SQL); System.out.println(count);
-
PreparedStatement
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
String SQL2 = "SELECT p.id FROM Posts as p WHERE user_id = 5;"; ResultSet rs= ex.getStmt().executeQuery(SQL2); ArrayList<Integer> list = new ArrayList<>(); while(rs.next()){ list.add(rs.getInt("p.id")); } for(Integer idx: list) { PreparedStatement pstm; ex.getStmt().execute("DELETE FROM post_comments WHERE post_id = " + idx); pstm = ex.getConn().prepareStatement("DELETE FROM post_comments WHERE post_id = ?"); pstm.setInt(1, idx); pstm.execute(); pstm = ex.getConn().prepareStatement("DELETE FROM post_likes WHERE post_id = ?"); pstm.setInt(1, idx); pstm.execute(); pstm = ex.getConn().prepareStatement("DELETE FROM posts_hashtags WHERE post_id = ?"); pstm.setInt(1, idx); pstm.execute(); pstm.close(); }
-
새로 알게 된 부분
Create table에서 참고
-
Posts table과 Post_likes, Post_comments 의 Created_at
- created_at 에 DATETIME 까지는 같으나 DEFAULT CURRENT_TIMESTAMP로 설정하여 생성된 시점에 초기화 시켜주는 역할을 함.
- NOW()를 써도 되지만 8버전 이후에 나와서 적용 안되는 회사들도 꽤 있음
-
TIMESTAMP는 다른 컬럼들과 다르게 NULL 허용이라고 명시적으로 지정하지 않으면 기본 값이 NOT NULL이다, TIMESTAMP NULL 이라고 지정해줘야 NULL 허용됨
1 2 3 4 5 6 7 8 9
CREATE TABLE t1 ( ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- default 0 ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL ); CREATE TABLE t1 ( dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP, -- default NULL dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0 );
ON DELETE CASCADE
ON DELETE 옵션 참고하기 - 해당 링크로 이동시 ON DELETE 정리 해놓은 파일로 갈 수 있
특정 사용자의 모든 게시글을 삭제하시오. 예를 들어, 사용자 ID가 5인 사용자의 모든 게시글을 삭제하시오.
특정 사용자를 삭제하시오. 이 때, 관련된 모든 게시글, 댓글, 좋아요, 팔로우 정보도 함께 삭제하시오.
예를 들어, 사용자 ID가 10인 사용자와 그의 관련 데이터를 모두 삭제하시오.
사용자ID가 10인 사용자를 삭제 시 user_id를 참조키를 가지고 있는 다른 테이블에서도 삭제가 되어야 하는데
테이블 만들 때 Restrict로 설정되어 있어서 ON DELETE CASCADE로 수정해야 함.
ON DELETE CASCADE를 넣고 싶으면 user_id를 외래키로 가지고 있는 것들을 다 삭제하고
다시 외래키를 추가해서 ON DELETE CASCADE를 넣어줘야함.
1
2
ALTER TABLE follow_follower DROP FOREIGN KEY follow_follower_ibfk_2
ALTER TABLE follow_follower ADD CONSTRAINT follow_follower_ibfk_2 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
외래키 지울때 그냥 이렇게 지우려고 하면 키가 없다고 뜸 , 이름이 따로 있어서 그 이름을 찾아주어야 함.
foreign key 이름 찾는 쿼리는 아래와 같다
저 키를 지워야 함. 키를 지우면 외래키가 삭제됨.
근데 많은 왜래키를 다시 작업하기 힘드니까
데이터베이스 다 지운뒤에 Create table 때 외래키 추가시에 ON DELETE CASCADE를 추가하는 것이 좋음
테이블 설계시 이부분은 미리 고민하는 것이 좋음
PreparedStatement
문제17번
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
String SQL2 =
"SELECT p.id FROM Posts as p WHERE user_id = 5;";
ResultSet rs= ex.getStmt().executeQuery(SQL2);
ArrayList<Integer> list = new ArrayList<>();
while(rs.next()){
list.add(rs.getInt("p.id"));
}
for(Integer idx: list) {
PreparedStatement pstm;
ex.getStmt().execute("DELETE FROM post_comments
WHERE post_id = " + idx);
pstm = ex.getConn().prepareStatement("DELETE
FROM post_comments WHERE post_id = ?");
pstm.setInt(1, idx);
pstm.execute();
pstm = ex.getConn().prepareStatement("DELETE
FROM post_likes WHERE post_id = ?");
pstm.setInt(1, idx);
pstm.execute();
pstm = ex.getConn().prepareStatement("DELETE
FROM posts_hashtags WHERE post_id = ?");
pstm.setInt(1, idx);
pstm.execute();
pstm.close();
}
PreparedStatement pstm; 를 선언 후
1
2
3
4
pstm = ex.getConn().prepareStatement("DELETE
FROM post_comments WHERE post_id = ?");
pstm.setInt(1, idx);
pstm.execute();
ptsm을 초기화 해주는데 해당 문장의 ‘? ‘가 인자가 들어가는 곳이다.
ptsm.setInt(1,idx)는 첫번째 인자에 idx라는 값이 들어간다는 표시이며 ‘?’ 가 한개밖에 없으므로 1밖에 올 수 없다.
만약 인자가 여러개라면? 이런식으로 입력가능
```sql PreparedStatement ptsm ptsm = ex.getConn().prepareStatement(“insert into Users (username,password,post_id)Values(?,?,?)”); ptsm.setString(1,’jerry’); ptsm.setString(2,’jerryIsSoCute’); ptsm.setInt(3,1);
int result = ptsm.executeUpdate(); ``` –>
Leave a comment