HW4 Making Simple BBS Using JDBC Jaesoo Yoo

HW#4 Making Simple BBS Using JDBC Jaesoo Yoo Chungbuk National University Email : yjs@chungbuk. ac. kr

Contents u Introduction to JDBC – Example – Main classes & methods – JDBC driver installationnt program installation u HW Assignment u Directions for HW u References 2

JDBC 1. Introduction to JDBC 2. Example 3. Main classes & method 4. JDBC driver installation

Introduction to JDBC u What is JDBC? – “Java Database Connectivity” – Connector to access DB, when developing applications in Java. TM Platform JDBC Application SQL statement JDBC API Result. Set 4 JDBC Driver DBMS

Example import java. sql. *; class Test { public static void main(String[] args) { Connection con = null; Statement stmt = null; try { Class. for. Name("oracle. jdbc. driver. Oracle. Driver"); con = Driver. Manager. get. Connection( "jdbc: oracle: thin: @dbserver. cbnu. ac. kr: 1521: orcl", "user", "passwd"); stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query("select name from product"); while (rs. next()) { String product = rs. get. String(1); System. out. println(product); } } catch (Exception e) { e. print. Stack. Trace(); } finally { try { if (stmt != null) stmt. close(); if (con != null) con. close(); } catch (Exception e) { } } <An example code: Test. java> 5

Main classes & method u Loading JDBC driver – Using Class. for. Name() Class. for. Name(“oracle. jdbc. driver. Oracle. Driver”); u Connecting to DB – Using Driver. Manager. get. Connection() Connection con = Driver. Manager. get. Connection("jdbc: oracle: thin: @dbserver. chungbuk. ac. kr", "user", "passwd"); – jdbc: oracle: thin: @dbserver. chungbuk. ac. kr is URL 6

Main classes & method (cont’d) u Executing queries – Using Statement class Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query("select name from product"); – Using Prepared. Statement class Prepared. Statement pstmt = con. prepare. Statement(“insert into product values(? , ? )”); pstmt. set. String(1, “mp 3”); pstmt. set. Int(2, 150); pstmt. execute. Update(); ※ Use execute. Update() for insert, update, and delete 7

Main classes & method (cont’d) u Cursor operations – Use methods of Result. Set class » Ex) next(), get. String(), etc. Result. Set rs = stmt. execute. Query("select name from product"); while (rs. next()) { String product = rs. get. String(1); System. out. println(product); } 8

Main classes & method (cont’d) u Using ‘finally’ – Before finishing code, connection should be closed try { … con = Driver. Manager. get. Connection( … ); stmt = con. create. Statement(); … } catch (Exception e) { e. print. Stack. Trace(); } finally { try { if (stmt != null) stmt. close(); if (con != null) con. close(); } catch (Exception e) {} } 9

Main classes & method (cont’d) u Executing Query within a Transaction try { … con = Driver. Manager. get. Connection( … ); con. set. Auto. Commit(false); stmt = con. create. Statement(); stmt. execute. Query( … ); … conn. commit(); } catch (SQLException e) { conn. rollback(); … } 10

JDBC driver installation u JAVA SDK 1. 6 or 1. 7 must be installed u Download (ojdbc 6. jar) – http: //www. oracle. com/technetwork/database/enterpriseedition/jdbc-112010 -090769. html u Environment variable setting – Add to the CLASSPATH environment variable the driver installation path » Ex) If the path is c: Oraclejdbcojdbc 6. jar 11

JDBC driver installation (cont’d) u Example file execution – Compiling & running in a DOS command window import java. sql. *; class Test { public static void main(String[] args) { Connection con = null; try { Class. for. Name("oracle. jdbc. driver. Oracle. Driver"); con = Driver. Manager. get. Connection( "jdbc: oracle: thin: @dbserver. chungbuk. ac. kr: 1521: orcl", "user", "passwd"); System. out. println(“Connection created"); } catch (Exception e) { e. print. Stack. Trace(); } finally { try { if (con != null) con. close(); } catch (Exception e) { } } 12

Eclipse Setting u Working on Eclipse IDE – Add ojdbc 6. jar to project build path » Right click on JRE System Library Build Path Configure Build Path 13

Eclipse Setting (cont’d) u Working on Eclipse IDE – Add External IDE select ojdbc 6. jar 14

Homework #4 1. Overview 2. Homework Assignment 3. Directions 4. References

Overview u Simple BBS – manages user accounts – supports write/read/delete/show commands – transaction control – uses two tables ARTICLES and USERS in DB » ARTICLES: contains users’ articles » USERS: stores user account info. ARTICLES NO: WRITER: COUNT: TITLE: CONTENT: USERS INT VARCHAR 2(10) INT VARCHAR 2(30) VARCHAR 2(500) 16 USERID PASSWD VARCHAR 2(10) Myoung Ho Kim, KAIST

Homework #4 u Initial screen – user can login to BBS, or – create a new account by typing ‘new’ 17 Myoung Ho Kim, KAIST

Homework #4 (cont’d) u HW 4 -1) Managing user accounts – Create a new account check if there exists the same ID 18

Homework #4 (cont’d) u HW 4 -1) Managing user accounts (cont’d) – Login to BBS If a user type a wrong ID or password three times, login is denied and the program exits. 19

Homework #4 (cont’d) u HW 4 -2) Command: show(s)/quit(q) type ‘s’; show the catalog of user articles in the order of article number (no) type ‘q’; BBS program exits 20

Homework #4 (cont’d) u HW 4 -3) Command: read(r) & transaction control type ‘r’; select article number(no); show the title and content of the selected article increase count by one after a user read this article 21

Homework #4 (cont’d) u HW 4 -4) Command: write(w) type ‘w’; enter title; input content; Note that entering content ends with the carriage return 22

Homework #4 (cont’d) u HW 4 -5) Command: delete(d) type ‘d’; select article number(no); Note that a user cannot delete the others’ articles 23

Homework #4 (cont’d) u HW 4 -5) Command: delete(d) (cont’d) After deletion, rearrange the article numbers 24

Submission u Files to submit – 1. JAVA (*. java) files that implement the Simple BBS u How to submit – e-mail : sypark 1992@chungbuk. ac. kr(박선용 조교) u Due date – December 21 (Sunday), 11: 59 pm. 25

References u JDBC – JAVA Platform, Standard Edition 6 API Specification » http: //docs. oracle. com/javase/6/docs/ – documentation » http: //docs. oracle. com/javase/6/docs/technotes/guides/jdbc/ 26