1 / 22

Briefing on Assignment Two & JDBC Technology

Briefing on Assignment Two & JDBC Technology. March 6, 2007. Briefing on Assignment 2. OnlineDrive v2.0 An extension of OnlineDrive v1.0 What are the differences? Use DMBS to manage user information and file information This part is a bit tedious, but straight forward.

palti
Download Presentation

Briefing on Assignment Two & JDBC Technology

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Briefing on Assignment Two & JDBC Technology March 6, 2007

  2. Briefing on Assignment 2 • OnlineDrive v2.0 • An extension of OnlineDrive v1.0 • What are the differences? • Use DMBS to manage user information and file information • This part is a bit tedious, but straight forward CSIS0402

  3. Briefing on Assignment 2 (2) • Implement Group/Public sharing options • If a file is open-to-group • Users with the same group of the file uploader can view / download the file • If a file is open-to-public • All users can view / download the file • Which modules are to be modified: • Registration: + Group ID (user may leave it empty) • Add File: Specify the sharing option • File tree display: When a file is newly added to the system, the update should be reflected on the relevant users instantly… • Get File: Just as before (if your file tree is displayed properly) • … CSIS0402

  4. More about File Tree Display • When a file is newly added to the system, which users should have their file tree updated? • If the file is Personal • Only the file uploader will see the update on the file tree • If the file is open-to-group • Users within the same group of the file uploader should see the new file on their file tree INSTANTLY • If the file is open-to-public • All users should be able to see the new file on their file tree INSTANTLY CSIS0402

  5. How to display? BEFORE Details of the new file should be displayed AFTER The path of the new file should be expanded. i.e. All its ancestor nodes should be displayed A system message should be displayed. CSIS0402

  6. How to display INSTANTLY? • Original model • Client always takes the initiative! • That’s why we need Client Callback Interface OnlineDriveServer.java 1 2 OnlineDriveServerImp.java OnlineDriveClientImp.java CSIS0402

  7. How to display INSTANTLY? (2) • Client Callback Interface • Serves as the interface for the client side • Tells the server how to talk to the client • You need to run rmic on OnlineDriveClientImp class to create the stub and skeleton • New model OnlineDriveServer.java 1 OnlineDriveClientCallback.java 2 OnlineDriveServerImp.java OnlineDriveClientImp.java CSIS0402

  8. How? • Server keeps (in an array/vector) a list of client callback objects • A client callback object is added to the list when the client first communicates (e.g. the user do registration/login) with the server Server login Client A Add file login Client B CBA login CBB Client C CBC CSIS0402

  9. How to use JDBC in this assignment? • In your server program • Connect to DB server using - userID = c0402 • Password = c0402pwd • Check if the database/tables exist, if not, create them • CREATE DATABASE tmchandb; • CREATE TABLE … • When user register - INSERT INTO … ; • When user login - SELECT … FROM … WHERE … ; • When user add file - INSERT INTO … ; … CSIS0402

  10. Steps for JDBC Database Access • Load a specific JDBC driver • Establish a connection to a specific database • Send SQL queries and obtain results • Process query results • Close connection Question: Can we do Step 5 before Step 4? CSIS0402

  11. Step 1: Load Driver • Register a driver by loading its class: • Class.forName(drivername).getInstance(); Step 2: Connect to a Database • Create a connection object to a database at a designated URL: • Connection con = DriverManager.getConnection( databaseURL, userID, passwd) CSIS0402

  12. Step 3 & 4: Running SQL Commands • Create object from one of the following 3 interfaces: • Statement, PreparedStatment & CallableStatement • Statement stmt = conn.createStatement(); • executeQuery(…) • return a ResultSet object • E.g. While (rset.next()){ System.out.println(rset.getString(“NAME”); } • executeUpdate(…) • DO NOT return result • execute(…) • Should be used if no idea whether the query will return a result or not CSIS0402

  13. Step 5: Close Connection • Release all DB resources by • rset.close(); // close the result set • stmt.close(); // close the statement • conn.close(); // close the connection CSIS0402

  14. Lab Exercise • Preparation • MySQL 5.0 installation • JDBC driver installation • Environment variables setup • Setup the database • Try Lab2Program.java CSIS0402

  15. A. Preparation • Download lab2.zip, in which you will find • builddb_sql – SQL statements for creating and setting up your database • Lab2Program.java - a simple java program which connects to the database, performs a query, displays the result in the console • Unzip the files to C:\c0402\lab2 CREATE DATABASE c0402db; USE c0402db; CREATE TABLE studentinfo (id SMALLINT, name VARCHAR(12), age SMALLINT); INSERT INTO studentinfo values (1,'Anson',20); INSERT INTO studentinfo values (2,'Bruce',21); INSERT INTO studentinfo values (3,'Candy',19); COMMIT; CSIS0402

  16. B. MySQL 5.0 installation • Get the setup program athttp://www.cs.hku.hk/~c0402/download/mysql-essential-5.0.27-win32.msi • Run the setup program • Select ‘Typical Installation ‘ • When completes, check ‘Configure the MySQL Server now’ • When MySQL Server Instance Configuration Wizard pops up, • Select ‘Standard Configuration’ • Uncheck ‘Install as Windows Service’ • Check ‘Include Binary Directory in Windows PATH’ • Execute CSIS0402

  17. C. JDBC driver installation • Get the driver at http://www.cs.hku.hk/~c0402/download/mysql-connector-java-5.0.5-bin.jar 2. Save it under C:/c0402/ CSIS0402

  18. D. Environment Variables Setting • PATH (similar to Lab 1)  Append the path of your JVM to PATH • CLASSPATH  Create CLASSPATH and set it as .;C:/c0402/mysql-connector-java-5.0.5-bin.jar; CSIS0402

  19. E. Database Setup • Start a command prompt, go to c:\c0402\lab2 • shell> cd c:\c0402\lab2 • Start the MySQL Server (and keep it running) • shell> start mysqld-nt • Connect to the server using the root account • shell> mysql –user=root mysql • Create the account “c0402” • mysql> GRANT all privileges on *.* TO 'c0402'@localhost identified by 'c0402pwd' with GRANT option; • End this session • mysql> exit; CSIS0402

  20. E. Database Setup (2) • Connect to the server using the account c0402 • shell>mysql –user=c0402 –p0402pwd • Create database and table in a batch mode • mysql>source builddb_sql; • Test if the table has been constructed properly • mysql> describe studentinfo; • End this session • mysql> exit; CSIS0402

  21. F. Try Lab2Program.java • View the source of Lab2Program.java and see if you understand what it is doing • Compile it • > javac Lab2Program.java • Run it • > java Lab2Program • Expected output (in the console) • Anson • Bruce • Candy CSIS0402

  22. References • jGuru: JDBC 2.0 Fundamental • http://java.sun.com/developer/onlineTraining/Database/JDBC20Intro/JDBC20.html • Java Tutorial – JDBC Technology • http://java.sun.com/developer/onlineTraining/Programming/JDCBook/jdbc.html • MySQL Documentation • http://dev.mysql.com/doc/ CSIS0402

More Related