JDBC Programming

heenkokoยท2020๋…„ 5์›” 23์ผ
0

MVC & JDBC

๋ชฉ๋ก ๋ณด๊ธฐ
2/4

๐ŸŽฏ JDBC๋ž€?

์ž๋ฐ” ์–ธ์–ด๋กœ ๋‹ค์–‘ํ•œ ์ข…๋ฅ˜์˜ ๊ด€๊ณ„ํ˜• ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ์ ‘์†ํ•˜๊ณ  SQL๋ฌธ์„ ์ˆ˜ํ–‰ํ•˜์—ฌ ์ฒ˜๋ฆฌํ•˜๊ณ ์ž ํ•  ๋•Œ ์‚ฌ์šฉ๋˜๋Š” ํ‘œ์ค€ SQL ์ธํ„ฐํŽ˜์ด์Šค API

  • ์ ‘์†ํ•˜๋ ค๋Š” DBMS ์„œ๋ฒ„์— ๋”ฐ๋ผ์„œ JDBC ๋“œ๋ผ์ด๋ฒ„๊ฐ€ ํ•„์š”ํ•˜๋‹ค.
  • ์—ฐ๋™ ๊ณผ์ • : JDBC ํ”„๋กœ๊ทธ๋žจ - JDBC ์ธํ„ฐํŽ˜์ด์Šค - JDBC ๋“œ๋ผ์ด๋ฒ„ - DB

JDBC ์ธํ„ฐํŽ˜์ด์Šค

  • java.sql ํŒจํ‚ค์ง€๋ฅผ ์˜๋ฏธํ•œ๋‹ค.
  • JDBC ํ”„๋กœ๊ทธ๋žจ์„ ๊ตฌํ˜„ํ•  ๋•Œ ์‹ค์ œ๋กœ ์‚ฌ์šฉํ•˜๋Š” ๊ฐ์ฒด๋“ค์€ ๋Œ€๋ถ€๋ถ„ ์ธํ„ฐํŽ˜์ด์Šค์ด๋‹ค.

JDBC ๋“œ๋ผ์ด๋ฒ„

java.sql์˜ ์ธํ„ฐํŽ˜์ด์Šค๋“ค์„ ์ƒ์†ํ•˜์—ฌ ๋ฉ”์†Œ๋“œ์˜ ๋ชธ์ฒด๋ฅผ ๊ตฌํ˜„ํ•œ ํด๋ž˜์Šค ํŒŒ์ผ๋“ค์ด ํ•„์š”ํ•˜๋ฉฐ ์ด ํŒŒ์ผ๋“ค์„ JDBC ๋“œ๋ผ์ด๋ฒ„๋ผ๊ณ  ํ•œ๋‹ค.

  1. ๊ฐ DBMS์— ๋งž๋Š” ๋“œ๋ผ์ด๋ฒ„ ๋‹ค์šด๋กœ๋“œ ํ•„์š”
  2. ์›น์• ํ”Œ๋ฆฌ์ผ€์ด์…˜์—์„œ ์ž๋™์œผ๋กœ ์ธ์‹ํ•  ์ˆ˜ ์žˆ๋„๋ก ํ•ด์ค˜์•ผ ํ•œ๋‹ค.
    ํ”„๋กœ์ ํŠธ lib ํด๋”์— ์œ„์น˜์‹œํ‚ค๊ธฐ
    ์ดํด๋ฆฝ์Šค ํ”„๋กœ์ ํŠธ ์šฐํด๋ฆญ - Properties - Java Buid Path - Add Exteranl JARs..

๐ŸŽฏ ๊ตฌํ˜„ํ•˜๊ธฐ

1. JDBC ๋“œ๋ผ์ด๋ฒ„ ๋กœ๋”ฉ

Class.forName("oracle.jdbc.OracleDriver");
  • ๋™์  ๋กœ๋”ฉ์„ ์œ„ํ•ด Class.forName()์„ ์ด์šฉ
  • JDBC ๋“œ๋ผ์ด๋ฒ„ ํŒŒ์ผ์˜ ๋“œ๋ผ์ด๋ฒ„ ์ธํ„ฐํŽ˜์ด์Šค๋ฅผ ์ƒ์†ํ•œ ํด๋ž˜์Šค๊ฐ€ ๋™์ ์œผ๋กœ ๋กœ๋”ฉ๋  ๋•Œ ์ž๋™์œผ๋กœ JDBC ๋“œ๋ผ์ด๋ฒ„ ์ธ์Šคํ„ด์Šค๊ฐ€ ์ƒ์„ฑ๋˜์–ด ์ค€๋น„๊ณผ ์™„๋ฃŒ๋œ๋‹ค.

2. DBMS ์„œ๋ฒ„ ์ ‘์†(Connection ๊ฐ์ฒด ์ƒ์„ฑ)

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe", student, student);
  • DriverManager.getConnection( ) ์‹ค์ œ ์ž๋ฐ” ํ”„๋กœ๊ทธ๋žจ๊ณผ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ๋„คํŠธ์›Œํฌ์ƒ์—์„œ ์—ฐ๊ฒฐํ•ด์ฃผ๋Š” ๋ฉ”์†Œ๋“œ์ด๋ฉฐ ์—ฐ๊ฒฐ์— ์„ฑ๊ณตํ•˜๋ฉด DB์™€ ์—ฐ๊ฒฐ๋œ
    ์ƒํƒœ๋ฅผ Connection ๊ฐ์ฒด๋กœ ํ‘œํ˜„ํ•˜์—ฌ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.

๋งค๊ฐœ์ธ์ž
String url ์ ‘์†ํ•  ์„œ๋ฒ„์˜ URL์ด๋ฉฐ, ํ”„๋กœํ† ์ฝœ, ์„œ๋ฒ„์ฃผ์†Œ, ์„œ๋ฒ„ํฌํŠธ, DB์ด๋ฆ„์œผ๋กœ ๊ตฌ์„ฑ
String user DB์„œ๋ฒ„์— ๋กœ๊ทธ์ธํ•  ๊ณ„์ •
String password DB์„œ๋ฒ„์— ๋กœ๊ทธ์ธํ•  ๋น„๋ฐ€๋ฒˆํ˜ธ

3. PreparedStatement

์œ„์—์„œ ๋งŒ๋“  Connection์„ ์ž๋ฐ” ํ”„๋กœ๊ทธ๋žจ๊ณผ DB ์‚ฌ์ด์— ์—ฐ๊ฒฐ์ด ๋˜์—ˆ๋‹ค๋ฉด ์ด ์—ฐ๊ฒฐ์„ ํ†ตํ•ด ์ž๋ฐ”ํ”„๋กœ๊ทธ๋žจ์€ DB ์ชฝ์œผ๋กœ SQL ๋ฌธ์„ ์ „์†กํ•˜๊ณ , DB๋Š” ์ฒ˜๋ฆฌ๋œ ๊ฒฐ๊ณผ๋ฅผ ๋‹ค์‹œ ์ž๋ฐ”ํ”„๋กœ๊ทธ๋žจ ์ชฝ์œผ๋กœ ์ „๋‹ฌํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค. ๋ฐ”๋กœ ์ด ์—ญํ™œ์„ ํ•˜๋Š” ๊ฐ์ฒด๊ฐ€ Statement์ž…๋‹ˆ๋‹ค.

  • SQL๋ฌธ์žฅ์ด ๋ฏธ๋ฆฌ ์ปดํŒŒ์ผ๋˜๊ณ , ์‹คํ–‰์‹œ๊ฐ„๋™์•ˆ ์ธ์ˆ˜ ๊ฐ’์„ ์œ„ํ•œ
    ๊ณต๊ฐ„์„ ํ™•๋ณดํ•  ์ˆ˜ ์žˆ๋‹ค๋Š” ์ ์—์„œ Statement์™€ ๋‹ค๋ฅด๋‹ค.

PreparedStatement์˜ ์žฅ์ 

1. ๊ฐ€๋…์„ฑ๊ณผ ์œ ์ง€๋ณด์ˆ˜๊ฐ€ ์ข‹๋‹ค.

  • sql๋ฌธ ์ง€์ •์‹œ ๊ฐ’์„ ๋™์ ์œผ๋กœ ์ง€์ •ํ•ด์•ผํ•  ๋•Œ ? ๊ธฐํ˜ธ๋กœ ๋Œ€์ฒดํ•  ์ˆ˜ ์žˆ๋‹ค.
String sql = "insert into member values(?,?,?,?,?,?,?,?,?,default)";
PreparedStatement pstmt = conn.prepareStatement(sql);
/* ๋ฐ์ดํ„ฐ ์ฃผ์ž… */
pstmt.setString(1, m.getMemberId());	
pstmt.setString(2, m.getPassword());
pstmt.setString(3, m.getMemberName());
pstmt.setString(4, m.getGender());
pstmt.setInt(5, m.getAge());
pstmt.setString(6, m.getEmail());
pstmt.setString(7, m.getPhone());
pstmt.setString(8, m.getAddress());
pstmt.setString(9, m.getHobby());

2. ์ฟผ๋ฆฌ ์‹คํ–‰๊ณ„ํš์ด ์žฌ์‚ฌ์šฉ๋œ๋‹ค.

3. SQL ์ธ์ ์…˜ ์ทจ์•ฝ์ ์„ ๋ณด์™„ํ•  ์ˆ˜ ์žˆ๋‹ค.

SQL ์ธ์ ์…˜์ด๋ž€?

์œ„์˜ ๋งŒํ™”์˜ ํ•™๊ต์—์„œ ์ž…๋ ฅํ•œ ๋ช…๋ น

    INSERT INTO students (์ด๋ฆ„) VALUES ('ํ•™์ƒ ์ด๋ฆ„');

ํ•ด๋‹น ํ•™์ƒ์ด๋ฆ„์„ ๋„ฃ์„ ๊ฒฝ์šฐ

    INSERT INTO students (์ด๋ฆ„) VALUES ('Robert');
DROP TABLE students;
--');

์ฒซ ๋ฒˆ์งธ ์ค„์—์„œ๋Š” Robert๋ผ๋Š” ํ•™์ƒ์ด ์ž…๋ ฅ๋˜์—ˆ์ง€๋งŒ, ๋‘ ๋ฒˆ์งธ ์ค„์—์„œ ํ•™์ƒ๋“ค์˜ ๋ฐ์ดํ„ฐ๊ฐ€ ์žˆ๋Š” ํ…Œ์ด๋ธ”์„ ์ œ๊ฑฐํ•œ๋‹ค. ๊ทธ๋ฆฌ๊ณ  ์„ธ ๋ฒˆ์งธ์—์„œ๋Š” ๋’ค์— ์˜ค๋Š” ๋‚ด์šฉ์„ ๋ชจ๋‘ ์ฃผ์„ ์ฒ˜๋ฆฌํ•œ๋‹ค. ๊ฒฐ๊ณผ์ ์œผ๋กœ โ€˜๋ชจ๋“  ํ•™์ƒ ๊ธฐ๋ก์„ ์‚ญ์ œํ•œ๋‹ค.โ€™๋ผ๋Š” ๋œป์˜ ๋ช…๋ น๋ฌธ์ด ์™„์„ฑ๋œ๋‹ค.

4. SQL๋ฌธ ์‹คํ–‰

PreparedStatement ๊ฐ์ฒด์—์„œ ์ œ๊ณต๋˜๋Š” ๋ฉ”์†Œ๋“œ

DML ์‚ฌ์šฉ์‹œ
int result = pstmt.executeUpdate();

DQL ์‚ฌ์šฉ์‹œ
ResultSet result = pstmt.executeQuery();

4-1. ResultSet -> List<>๋กœ ์˜ฎ๊ธฐ๋Š” ์ž‘์—…

while(rset.next()) {
	// ์ปค์„œ๊ฐ€ ๊ฐ€๋ฆฌํ‚ค๋Š” ๋‹ค์Œํ–‰์—์„œ ์ปฌ๋Ÿผ ์ •๋ณด๋ฅผ ์ฝ์–ด์˜จ๋‹ค.
	String memberId = rset.getString("member_id");
	String password = rset.getString("password");
	String memberName = rset.getString("member_name");
	String gender = rset.getString("gender");
	int age = rset.getInt("age");
	String email = rset.getString("email");
	String phone = rset.getString("phone");
	String address = rset.getString("address");
	String hobby = rset.getString("hobby");
	Date enrollDate = rset.getDate("enroll_date");
				
	Member m = new Member(memberId, password, memberName, gender, age, 
    			email, phone, address, hobby, enrollDate);
	list.add(m);
}  

5. ํŠธ๋žœ์žญ์…˜ ์ฒ˜๋ฆฌ

  • commit / rollback
  • DQL์€ TCL ํ•„์š” ์—†์Œ
if(result > 0)
	conn.commit();
else
	conn.rollback();

6. ์ž์› ๋ฐ˜๋‚ฉ

  • ํ•ด์ œ ์ˆœ์„œ๋Š” ์ตœ๊ทผ์— ์‚ฌ์šฉํ–ˆ๋˜ ๊ฐ์ฒด๋ถ€ํ„ฐ ๊ฑฐ๊พธ๋กœ ์˜ฌ๋ผ๊ฐ€๋ฉฐ ํ•ด์ œํ•œ๋‹ค.
rs.close();
pstmt.close();
conn.close();

์ถœ์ฒ˜ : https://opentutorials.org/module/3569/21222

0๊ฐœ์˜ ๋Œ“๊ธ€