Lecture 11: Networking and Databases

What You Will Learn Today

1. Connect to a web server using Telnet.
2. Create a Java client and server using stream sockets.
3. Compare connection-based and connectionless TCP/IP protocols.
4. Use datagram sockets and packets.
5. Create a URL connection.
6. Process HTTP GET and POST requests from HTML forms.
7. Identify and handle exceptions related to sockets and connections.
8. Use basic SQL commands to create relational database tables and query them.
9. Compare some available databases.
10. Install a database server and JDBC driver.
11. Write Java programs to connect to the database and execute SQL statements.

Connecting to a Web Server using Telnet

• You can connect to a web or other server by specifying its host name and port number.
  • localhost is the machine where you are working. The port number of most web servers is 80.
• To connect interactively from a shell, you can use telnet. 
  • telnet java.sun.com 80
• You can get a web page or other file from the server by specifying its full path and protocol.
  • GET /index.html HTTP/1.0 (plus blank line; press Enter twice)
• The HTTP protocol includes commands like GET (get data from server) and POST (send data to server).
• Telnet is useful for testing a server interactively by typing commands.

Creating a Java Client

• Java's networking features are in the package java.net.
• To make a connection you can create a stream socket.
  • Socket s = new Socket("java.sun.com", 80);
• Communicate with the server using input and output streams.
  • InputStream in = s.getInputStream(); OutputStream out = s.getOutputStream();
• Turn streams into readers and writers.
  • BufferedReader reader = new BufferedReader(new InputStreamReader(in));
  • PrintWriter writer = new PrintWriter(out);
• Send commands to the server, e.g. request a file.
  • writer.print("GET /index.html HTTP/1.0\n\n"); writer.flush();
• Receive the server response, e.g. display the requested file.
  • String line; while ((line = reader.readLine()) != null) System.out.println(line);
• Close the socket connection, as soon as it is not needed.
  • s.close();
• If the client program involves graphics, use a separate thread to handle the drawing.

Creating a Java Server

• Create a server socket which waits for client connections through an unused port number (e.g. above 1024).
  • ServerSocket server = new ServerSocket(5000);
• Create a socket to accept a connection and communicate with the client.
  • Socket s = server.accept();
• Create streams, readers and writers, communicate with the client, and close connection (similar to above).
• Loop forever (keep waiting for new connections until the administrator kills the server process).
• Create some communications protocol (messages the client can send that the server can understand and vice-versa).
  • e.g. a bank account protocol might have commands for getting balance, depositing, withdrawing and quitting.
• Determine how the client and server will know the end of data
  • e.g. end of line (short messages), blank line (HTTP header), closed connection (GET), number of bytes (POST)
• If the server (e.g. a chat server) can handle several clients at once, create a thread for each client.
• Many servers (e.g. web servers) have a pool of ready threads to quickly handle large numbers of requests.

TCP/IP Protocol Suite

• The Transport Control Protocol/Internet Protocol (TCP/IP) suite enables many types of computers to communicate.
• Stream sockets use the connection-oriented TCP, like making phone calls.
  • Sockets hide much of the complexity of network programming from the programmer.
• Connectionless protocols like User Datagram Protocol (UDP) are like sending letters through the post.
  • Datagrams are lower level than stream sockets and require packet sequencing and error handling but are more efficient.

Datagrams

• Create a datagram socket which can send and receive packets.
  • DatagramSocket socket = new DatagramSocket(port);
• Create a send packet and send data (as an array of bytes) to an address and port.
  • byte[] data = new byte[256]; // put data into this array
  • DatagramPacket sendPacket = new DatagramPacket (data, data.length, address, port);
  • socket.send(sendPacket);
• The receiver creates a datagram socket and receive packet and waits for packets in an infinite while loop.
  • DatagramPacket receivePacket = new DatagramPacket (data, data.length);
  • socket.receive(receivePacket);
  • String message = new String(receivePacket.getData());

Creating a URL Connection

• URL connections are easier than using HTTP commands for reading files from the network.
  • URL u = new URL("http://home.umu.ac.ug/index.html"); 
  • URLConnection c = u.openConnection();
  • InputStream in = c.getInputStream();
• A URL has methods for parsing its parts: getProtocol(), getHost(), getPort() and getFile().
• Note: You have already used a URL connection to open a local file (in homework 4).
  • Using sockets, network I/O appears virtually the same as sequential file I/O.
• A HttpURLConnection supports HTTP commands.
  • You can get information about the HTTP response including status messages.
    • getResponseMessage() can return 404 (not found), 200 (OK) or other status codes.

Processing HTML Forms

• When the user posts (submits) a HTML form, you can send user input to server-side programs.
• GET adds the input to the URL after a ? character in the format ?variable1=value1&variable2=value2&...
  • GET www.weather.com/cgi-bin/weatherprogram?city=Kampala&country=Uganda
• The server program reads the variable-value pairs and handles the input.
• URLEncoder.encode() and URLDecoder.decode() convert between URL ASCII characters and non-ASCII codes.
• GET is simple but limited to short inputs (e.g. 255 characters). POST can handle longer inputs:
  • POST url of server program
  • Content-Type: type, e.g. application/x-www-form-urlencoded
  • Content-Length: length in bytes
  • other request headers
  • blank line
  • input data

Network Exception Handling

• Whenever opening a connection, sending, receiving, etc., you need to handle possible exceptions.
• MalformedURLException if a string is not in proper URL form
• UnknownHostException if server address cannot be resolved
• ConnectException if a client cannot connect to a server
• BindException if a port is invalid or already in use

SQL Basics

• A relational database stores information in tables. Each column has a name and data type.
• Structured Query Language (SQL) is a command language for interacting with a database.
• SQL INT, REAL, DOUBLE, BOOLEAN and CHAR types are similar to Java int, float, double, boolean and String types.
• To create a table use CREATE TABLE tablename ( variablename variabletype, variablename variabletype ... )
  • CREATE TABLE Products ( Product_Code CHAR(11), Description CHAR(40), Price DECIMAL(10, 2) )
• To add data in a table use INSERT INTO tablename VALUES ( value1, value2, ... )
  • INSERT INTO Products VALUES ('3354', 'Sony CD-RW disc', 5000)
• To remove a table use DROP TABLE tablename.

Relational Databases

• To link tables, each table should have a primary key (variable or column which uniquely identifies each table entry).
  • e.g. product number, customer number, invoice number
  • A foreign key is a reference to a primary key in a linked table, e.g. customer number in an invoice.
• Tables can have one-to-one, one-to-many or many-to-many relationships.

SQL Queries

• A query is a question which creates a table by collecting information from one or more tables.
• Select an entire table
  • SELECT * FROM Customer
• Select only some table columns
  • SELECT City, Country FROM Customer
• Select only some rows (using an operator like =, <>, >, <, AND, OR, NOT, LIKE, etc.)
  • SELECT * FROM Customer WHERE City = 'Kampala'
• Update and delete information.
  • UPDATE Item SET Quantity = Quantity + 1 WHERE InvoiceNumber = '3456'
  • DELETE FROM Customer WHERE City = 'Bukoba'
• Calculations include SUM, COUNT, AVG, MIN, MAX.
  • COUNT(*), AVG(Price)
• Join two tables using the primary and foreign key.
  • SELECT StaffName, DepartmentName
FROM Staff, Department
WHERE Staff.Department = Department.ID

Some Available Databases

Installing a Database

• Obtain from CD-ROM or web, read instructions, install
• Start the server, set up user accounts, run a simple test

Installing the JDBC Driver

• The Java Database Connectivity (JDBC) driver forwards commands from Java to the database.
• Install the driver which comes with the database program.
• Find the class path for the driver, e.g. /usr/local/oracle/jdbc/classes111b.zip
• Find the name of the driver class, e.g. oracle.jdbc.driver.OracleDriver
• Find the name of the database URL that the driver expects, of the form jdbc:subprotocol:driver data
• Create, compile and run a test database program.
  • java -classpath driver_class_path;. testprogram

Connecting to the Database

• Load the driver and create a Connection object using strings for driver, URL, username and password.
  • Connection conn = DriverManager.getConnection(url, username, password);
• Close the connection when finished. conn.close();
• Separate connection management code from the rest of the code (e.g. to connect from many classes).
• Put connection information into a separate configuration file rather than hard-coding it.

Executing SQL Statements

• Statement stat = conn.createStatement();
• stat.execute("CREATE TABLE Test (Name CHAR(20))");
• stat.execute("INSERT INTO Test VALUES ('UMU')");
• String queryStatement = "SELECT * FROM Test";
• ResultSet result = stat.executeQuery(queryStatement);
• For UPDATE statements, use executeUpdate(), or execute() and getResultSet() or getUpdateCount().
• Each Statement only has one ResultSet.
• Close the ResultSet before making a new query on the same Statement. result.close();
• Close the Statement when done with it. stat.close();

Using Result Sets

• Iterate through the result set to get rows. while (result.next()) { ... }
• To get a column, use result.getString(column number) or result.getString(column name).
  • SQL column numbers begin with 1, unlike arrays which start with 0.
  • Names are easier to read than numbers and easier to change.
• For efficiency, let the database do the work and use a a specific SQL SELECT query.
  • Getting entire tables and selecting using Java is very inefficient (slow).
• Meta data are data about an object, e.g. column names.
  • ResultSetMetaData metaData = result.getMetaData();
  • metaData.getColumnLabel(i) is the name of column i.
  • getColumnDisplaySize(i) is the column width.
• A useful application of this is to read a text file containing SQL statements, execute them, and display the results.

To Do After Class

• Lab 9 and Homework 5 are due Friday. Lab 10 and Project 2 are due next Friday.
• Readings/References: Horstmann Ch. 22, 23, 25; Deitel & Deitel Ch. 16
• Electronic Resources: Java Tutorial: JDBC Database Access: JDBC Basics; JFC DBDemos; Examplets: java.net