1 / 23

CS4273: Distributed System Technologies and Programming I

CS4273: Distributed System Technologies and Programming I. Lecture 8: Java Database Connection (JDBC). Java DataBase Connections (JDBC). JDBC driver A JDBC driver is located in cslab’s dir: /usr/local/jt6/lib/mysql-connector-java-5.1.7-bin.jar You can either:

earl
Download Presentation

CS4273: Distributed System Technologies and Programming I

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. CS4273: Distributed System Technologies and Programming I Lecture 8: Java Database Connection (JDBC)

  2. Java DataBase Connections (JDBC) JDBC driver • A JDBC driver is located in cslab’s dir: /usr/local/jt6/lib/mysql-connector-java-5.1.7-bin.jar • You can either: copy the zipped file to your local directory and unzip it: > jar xvf mysql-connector-java-5.1.7-bin.jar or make a link from directory “com” in your local dir to the system JDBC driver. • A brief manual of MySQL JDBC in CSlab can be found at: http://personal.cs.cityu.edu.hk/jdemo/

  3. Java applications JDBC API JDBC driver manager JDBC driver API JDBC/ODBC Bridge Vendor’s JDBC driver ODBC driver database database JDBC Structure

  4. Web server site applet JDBC (proxy): 4040 DBMS system 2-Tier’s Structure

  5. Web server site applet HTTP server your CGI server DBMS system 3-Tiers Structure

  6. Demonstration of using MySQL in Cslab Access MySQL interactive interface: mysql -hhostname -uusername -ppasswd e.g. % mysql -hjserv -ujdemo -papple1 (jserv is the host name of DBMS, jdemo the user name and apple1 the password.) > use db_jdemo; // set database to db_jdemo > select * from COFFEES; ……

  7. Demonstration of using MySQL in Cslab (Cont.) More SQL statements > show tables; // list all the table > help; > help contents; > select * from myCOFFEES; > drop table myCOFFEES; > exit; // quit the mysql interface

  8. Java applications and DBMS using JDBC Note: no proxy is needed in this case. Java Application DBMS JDBC

  9. import java.sql.*; public class CreateCoffees { public static void main(String args[]) { String url = “jdbc:mysql://jserv.cs.cityu.edu.hk:3306/ db_jdemo”; Connection con; Statement stmt; String createString = "create table myCOFFEES " + "(COF_NAME varchar(32), " + "SUP_ID int, " + "PRICE float, " + "SALES int, " + "TOTAL int)"; try { Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection(url, "jdemo", "apple1"); stmt = con.createStatement(); stmt.executeUpdate(createString); stmt.close(); con.close(); } catch(SQLException e) { System.err.println(e.getMessage()); } } } JDBC: Java applications and MySQL

  10. import java.sql.*; public class SelectCoffees { public static void main(String args[]) { String url = "jdbc:mysql://jserv.cs.cityu.edu.hk:3306 /db_jdemo”; Connection con; Statement stmt; String query = "select COF_NAME, PRICE from COFFEES"; try { Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection(url, "jdemo", "apple1"); stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); while (rs.next()) { // loop on each row of "rs" String s = rs.getString("COF_NAME"); float f = rs.getFloat("PRICE"); System.out.println(s + " " + f); } stmt.close(); con.close(); } catch(SQLException e) { System.err.println(e.getMessage()); } } } JDBC: Java applications and MySQL (select)

  11. Steps of JDBC Connection • Load driver manager Class.forName("Driver’s name"), e.g., Class.forName("com.mysql.jdbc.Driver"); Class.forName("com.sybase.jdbc.SybDriver"); Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Note: name components separated by ‘.’ are a dir path name, relative to the current directory. • Make a connection to DBMS system Database URL • A database URL specifies where the database is. The general syntax: jdbc: subprotocol_name: other_stuff • The format of other_stuff depends on the subprotocol used, e.g., String url="jdbc:mysql://jserv.cs.cityu.edu.hk:3306/db_50704380" String url = “jdbc:odbc://whitehouse.gov:5000/Cat” String url = "jdbc:sybase:Tds:ntr10:4100"; Database Connection • Connection con = DriverManager.getConnection(url, usr, pswd); e.g., con = DriverManager.getConnection(url, "jdemo", "apple1");

  12. Steps of JDBC Connection (Cont.) • Create a statement object • create a Statement object out of Connection object for every SQL statement: Statement stmt = con.createStatement(); • Execute an SQL statement • Two types of SQL statements: queries and updates ExecuteQuery ResultSet rs = stmt.executeQuery (“select * from books”); or: String query = “select * from books”; ResultSet rs = stmt.executeQuery (query); ExecuteUpdate stmt.executeUpdate(createString); (there is no return needed for update statements)

  13. Get results from DBMS The execution of an sql query returns a set of rows. Each row consists of several attributes (fields): ResultSetrs = stmt.executeQuery (query); Access Rows of a ResultSet The basic loop for analyzing a result set uses format: while ( rs.next()) { // rs.next pointers to the next row analyze a row of the result set; } Access Attributes of a row Get the value of an attribute by methods of format: Xxx getXxx (int clmn_num) or Xxx getXxx (String clmn_name) e.g., String cofName = rs.getString(1) or String cofName = rs.getString(“COF_NAME”); There are many other getXxx methods: int supId = rs.getInt (“SUP_ID”); float price = rs.getDouble (“PRICE”); ……. For other types, please refer to JDBC manual. Steps of JDBC Connection (Cont.)

  14. Web server site applet JDBC (proxy): 4040 DBMS system JDBC Connection between Applet and DBMS2-Tier Structure • Install a JDBC proxy at the web server site (“personal.cs.cityu”). • An applet makes a JDBC connection to DBMS via the proxy, bcs applets can only connect to its home server. • The result type of a sql query is ResultSet (process them in the same way as discussed before).

  15. public class myapplet extends Applet implements Runnable { private Vector queryResults; public synchronized void start() { if (worker == null) { message = "Connecting to database"; worker = new Thread(this); worker.start(); } } public void run() { String url = "jdbc:mysql: //personal.cs.cityu.edu.hk:4040/db_jdemo "; String query = "select COF_NAME, PRICE from COFFEES"; try { Class.forName("com.mysql.jdbc.Driver"); Vector results = new Vector(); con=DriverManager. getConnection(url, "jdemo", "apple1"); stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); while (rs.next()) {// process rs row by row String s = rs.getString("COF_NAME"); float f = rs.getFloat("PRICE"); String text = s + " " + f; results.addElement(text); } stmt.close(); con.close(); setResults(results); repaint(); } catch(SQLException e) {;} } An Example of 2 Tier Structure(Applet – DBMS)

  16. Disadvantages of the 2-Tier Structure • Fat clients (applets). • Little flexibility on the security control to the DBMS accesses.

  17. Web server site applet HTTP server your CGI server DBMS system 3-Tiers Structure

  18. Web server site HTTP server applet CGI server Applet design in 3-tier (interact with CGI) • make socket connection to HTTP server and start cgi-svr by POST method. • receive String lines from cgi-svr, one for each row of data, and put them into a Vector. Vector results = new Vector(); while ((line = in.readLine())!=null) results.addElement(line); • paint() extracts data rows from the vector by using an Enumerration object. Enumeration enum = queryResults.elements(); • check if it’s the end of the object: enum.hasMoreElements(); • get the next element from the object: String text = (String) enum.nextElement(); • for each line of data, break it into attributes (separated by “|”) by using an StringTokenizer object. StringTokenizer st = new StringTokenizer(text, "|"); String cof_name = st.nextToken(); String cof_price = st.nextToken();

  19. public class myapplet extends Applet implements Runnable { Thread worker; Vector queryResults; public synchronized void start() { ......... worker.start(); } public void run() { Vector results = CallCgiSvr(); if (results != null) setResults(results); } Vector CallCgiSvr() { Vector results = new Vector(); String sdata = "START_QUERY"; s = new Socket(“personal.cs.cityu.edu.hk",80); in = new DataInputStream(s.getInputStream()); out = new PrintStream(s.getOutputStream()); out.println("POST /3tier/shellcgi.cgi HTTP/1.0\r"); out.println("Content-type: plain/text\r"); out.println("Content-length: "+ sdata.length()+ "\r\r"); out.println(sdata+"\r"); while (! in.readLine.equals("START_DATA")); while ((line = in.readLine()) != null) if (line.length() > 0) results.addElement(line); in.close(); out.close(); return(results); } public synchronized void paint(Graphics g) { g.drawString("Prices of coffee per pound: ", 5, 10); int x = 5, y = 30; Enumeration enum = queryResults.elements(); while (enum.hasMoreElements()) { String text = (String)enum.nextElement(); StringTokenizer st = new StringTokenizer(text, "|"); String cof_name = st.nextToken(); String cof_price = st.nextToken(); g.drawString(cof_name, x, y); g.drawString(cof_price, x+140, y); y = y + 15; } } Applet in 3-tier JDBC

  20. applet HTTP server CGI server DBM CGI Program in 3-Tier JDBC • cgi-svr acts as a gateway between applet and DBMS. No more need of a proxy at the web site. • cgi-svr gets requests from client via stdin, connects to DBMS by JDBC, executes queries and receives results: • get query results of type ResultSet: ResultSet rs = stmt.executeQuery (query); • analyze each row and make it a line. Attributes of a line are separated by “|”. String s = rs.getString("COF_NAME"); float f = rs.getFloat("PRICE"); String text = s + "|" + f; • append the lines into a StringBuffer. StringBuffer results = new StringBuffer(); results.append (text + "\n"); • convert StringBuffer to a single string and send it to applet via stdout. String line = results.toString(); outs.println(line); // outs is the stdout stream

  21. CGI program in 3-Tier JDBC class cgisvr { public static void main(String[] args) { String request, line; try { DataInputStream ins = new DataInputStream(System.in); PrintStream outs = new PrintStream(System.out); while ((request = ins.readLine()) != null) { if (request.equals("START_QUERY")) { line = ReqSql (); outs.println(START_DATA); // a start token outs.println(line); }} // send reply to applet outs.close(); } catch (Exception e) { System.out.println("Error"+e);} }

  22. static String ReqSql() { String url = "jdbc:mysql: //jserv.cs.edu.hk:3306/db_jdemo "; String query = "select COF_NAME, PRICE from COFFEES"; StringBuffer results = new StringBuffer(); Class.forName("com.mysql.jdbc.Driver"); con= DriverManager. getConnection(url, "jdemo", "apple1"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); while (rs.next()) { String s = rs.getString("COF_NAME"); float f = rs.getFloat("PRICE"); String text = s + "|" + f; results.append(text + "\n"); } stmt.close(); con.close(); return(results.toString()); } CGI program in 3-Tier JDBC (Cont.)

  23. Shell Script starting the Java Program #!/bin/sh echo Content-type: text/plain Echo #the java command must use full path! /usr/local/jdk/bin/java cgisvr

More Related