Start Your Project

Environment setup and template project

Notes on Web DB Programming
Outline
• Create an Amazon RDS instance – MySQL DB instance (check
AWS_RDS.pdf under resources)
• Setting up MySQL workbench and connecting to MySQL DB instance
(check Setting up MySQL workbench.pdf)
• Create an Amazon EC2 instance (check AWS_EC2.pdf)
• Install a Web Server (check AWS_EC2.pdf)
• Notes on Web DB Programming
• Set up Environment and Introduction of the template project

2
Outline
• Create an Amazon RDS instance – MySQL DB instance (check
AWS_RDS.pdf under resources)
• Setting up MySQL workbench and connecting to MySQL DB
instance (check Setting up MySQL workbench.pdf)
• Create an Amazon EC2 instance (check AWS_EC2.pdf)
• Install a Web Server (check AWS_EC2.pdf)
• Notes on Web DB Programming
• Set up Environment and Introduction of the template project

3
Notes on Web DB
Programming
Based on M.Muscari and UCSD (anon)
Needed tools and installation
• JRE, IDE (JAVA, Eclipse for EE developers)
• MySQL (it is your AWS RDS instance)
• Apache Tomcat (or any web server)
• You will install it under the AWS EC2 instance AND locally in your computer
for development purposes.
• JDBC

5
Three-Tier Application

Presentation Tier : user interface to make requests, provide input and see results

Middle Tier: application logic

Data Management Tier: database management

6
Three-Tier architecture

7
HTTP protocol
Protocol that allows web servers and clients to exchange data over the web.
It is a request - response protocol.
 Clients (web browsers) send requests to web servers
• GET : ask for a resource
• POST : send some data (e.g. HTML form)
Server sends response
• Status code (200 OK, 404 Not Found!)
HTTP is a "stateless" protocol; each time a client retrieves a Webpage, the client
opens a separate connection to the Web server and the server automatically does
not keep any record of previous client request.

8
Difference between GET/POST requests
Anatomy of GET request Anatomy of POST request

9
HTML (Hyper Text Markup Language)
Standard markup language for creating web pages
Language for creating structured documents
 It consists of elements which can be nested
The HTML standard specifies a number of universally supported elements
(“tags”)
Web browsers receive HTML documents from a webserver and render
them into multimedia web pages.
HTML is commonly delivered as part of an HTTP response

Tutorial: https://www.w3schools.com/html/

10
HTML Tags

Common HTML tags include:

• <div></div> - a logical division (section)
• <p> </p> - a paragraph
• <table> </table> -a table of values
• <tr> </tr> - table row
• <td> </td> -table column
• <form></form> - a form enclosing input fields
• <input></input> - an input field

11
HTML Example

12
HTML Table Example

13
JSP (Java Server Pages)
A technology for building web applications that serve dynamic content
A JSP page is a text document that contains two types of text:
 static data, which can be expressed in any text-based format (e.g. HTML)
 JSP elements, which construct dynamic content.
The dynamic content in a JSP page is in specially marked Java code
fragments (enclosed between <% and %>).
To deploy and run JSPs, a compatible web server with a servlet container,
such as Apache Tomcat is required.
When executed, the Java code fragments usually generate additional HTML
into the page (in our case either accessing the database or processing
parameters passed to HTTP requests)
At the end, the resulting HTML page is sent to the browser to be displayed.

14
JSP Syntax
Comment
<%-- Comment--%>
Expression
<%= Java expression %>
Expression tag evaluates the expression placed in it, converts the result into String and send the
result back to the client through response object.
e.g. <p>Today is <%= new Date().toString(); %> </p>

Scriplet
<% java code fragment%>
e.g. <% person.getFirstName();%>

Include
<jsp:include page="relativeURL"/>

15
JSP Implicit Objects

Object Class
request HttpServletRequest
response HttpServletResponse
session HttpSession
out Writer

16
request
• <%@ page errorPage="errorpage.jsp" %>
<html>
<head>
<title>UseRequest</title>
• </head>
<body>
<%
// Get the User's Name from the request
out.println("<b>Hello: " + request.getParameter(”username") + "</b>");
%>
</body>
</html>

17
session
• <%@ page errorPage="errorpage.jsp" %>
<html>
<head>
<title>UseSession</title>
</head>
<body>
<%
HttpSession session = request.getSession(); //create a session object
// Try and get the current count from the session
Integer count = (Integer)session.getAttribute("COUNT");
// If COUNT is not found, create it and add it to the session
if ( count == null ) {
count = new Integer(1);
session.setAttribute("COUNT", count);
} else {
count = new Integer(count.intValue() + 1);
session.setAttribute("COUNT", count);
}
// Print the number of times the user has visited the site
out.println("<b>Hello you have visited this site: " + count + " times. </b>");
%>
</body> </html> 18
Java Database Connectivity (JDBC)
An interface to communicate with a relational database
 Allows database agnostic Java code
 Treat database tables/rows/columns as Java objects
JDBC driver
 An implementation of the JDBC interface
 Communicates with a particular database

19
JDBC steps

1. Connect to database
2. Query database (or insert/update/delete)
3. Process results
4. Close connection to database

20
1. Connect to database
Load JDBC driver
 Class.forName("com.mysql.jdbc.Driver").newInstance();
 Make connection
 Connection conn = DriverManager.getConnection(url);

URL
 Format: “jdbc:mysql//<hostname>:<port>/<databaseName>”
 jdbc:mysql://cs336.ckksjtjg2jto.us-east-2.rds.amazonaws.com:3036/BarBeerDrinkerSample

21
2. Query database
Create statement
 Statement stmt = conn.createStatement();
 stmt object sends SQL commands to database
 Methods
• executeQuery() for SELECT statements
• executeUpdate() for INSERT, UPDATE, DELETE, statements
Send SQL statements
 stmt.executeQuery(“SELECT ...”);
 stmt.executeUpdate(“INSERT ...”);

22
2. Query database
Prepared Statements
 If you want to execute dynamic or parameterized SQL queries,
use a “PreparedStatement” object instead of a statement.
PreparedStatement updateStud=conn.prepareStatement("UPDATE
Student SET
fname=? WHERE lastname LIKE?");

updateStud.setString(1,"John");
updateStud.setString(2,"Doe");
updateStud.executeUpdate();

23
3. Process results
Result of a SELECT statement (rows/columns) returned as a
ResultSet object
 ResultSet rs =
stmt.executeQuery("SELECT drinker,beer from LIKES");
Step through each row in the result
 rs.next()

Get column values in a row

 String userid = rs.getString(“drinker”);
 int type = rs.getInt(“type”);

24
3. Process results
Add a row to the users table
• String str = "INSERT INTO LIKES VALUES('Bob', ‘Corona')”;

Returns number of rows in table

• int rows = stmt.executeUpdate(str);

25
4. Close connection to database
Close the ResultSet object
 rs.close();

Close the Statement object

 stmt.close();

Close the connection

 conn.close();

26
The Template Project
0. import schema BarBeerDrinkerSample in your created DB instance
using the provided script ”BarBeerDrinkerSample.sql”. Open the script
and run it in your MySqlWorkbench. (File->Open SQL script)

27
The Template Project
1. Download Eclipse IDE for Java EE Developers
https://eclipse.org/downloads/eclipse-packages/

28
The Template Project
2. Open eclipse and import the template project (cs336Final.war)

File – Import – Web – WAR file

29
30
The Template Project
3. Structure of the template

Java code

Meta data of
your website

HTML, JSP, JS,

CSS code

31
The Template Project
4. Set your Tomcat server in eclipse
• If you don’t have tomcat yet go to:
https://tomcat.apache.org/download-70.cgi
and download the binary distribution for your OS.
• After go back to eclipse:
Windows - Preference - Server - Runtime Environment - Add - Apache
Tomcat v7.0 or
Eclipse- Preferences - Server - Runtime Environments - Add - Apache
Tomcat v7.0

32
33
34
The Template Project
5. Run the project based on Tomcat 7

Right click on the project - Run as - Run on Server - Apache - Tomcat7

35
36
The Template Project
• Now you can see your project home page, index.html page.

37
The Template Project
6. The home page is set in web.xml, you can set your own page if you
want.

38
The Template Project
7. Connect to your own db instance in Project

• In order to interact with db instance (add, delete, update, select), you need to
set your own database address in the project.

• At the same time, the database username and password are both essential.

• Replace the database information with your own database information as

follows.

39
The Template Project

Hostname: Port/Schema

Username and Password

40
The Template Project
8. Let’s have a beer
Select the radio button and then click submit below it

41
The Template Project
9. Let’s go to a pub
Select the radio button and then click submit below it

42
The Template Project
10. Insert a tuple into sells table

Input pub name, beer name and cost, then click submit.

You can find a new record inserted into your database after
submitting this form.
-NOTE: since you insert a tuple in sells table which has FKs in the
bar and beer table, make sure the beer and bar you insert already
exist in these two tables.

43
The Template Project
11. Query the beers with cost

Choose one option from the dropdown menu, then click submit.

44
The Template Project
• Query the beers with cost <= 3

45
The Template Project
• Query the beers with cost<= 5

46