Lecture 4: Functional Programming Paradigm

VO 506272: Constructional Example

Sample Servlet-Based Database System

Student: Sample Student MNr: 9312939

Table of Contents

1 Database Schema: 2

2 Practical implementation of the database with MySQL 3

3 Inserting Test Data with Java Servlets and HTML: 4

4 Database Queries (Java Servlets): 6

1 Database Schema:

o Domains:
cid Integer; customer id
cname String; customer name
cphone Integer; customer phone number
ccity String; customer city
pid Integer; product id
pname String; product name
pprice Integer; product price
tdate Date; transaction date
tqnt Integer; transaction quantity

o Relation: customer ( cid, cname, ccity, cphone )

o Relation: product ( pid, pname, pprice )

o Relation: transaction ( cid, pid, tdate, tqnt )

2 Practical implementation of the database with MySQL

SHOW DATABASES;

USE EXAMPLE

#Practical Definition of a database schema:

CREATE TABLE customer (cid Integer NOT NULL,

cname Varchar(30),

ccity Varchar(30),

cphone Integer,

PRIMARY KEY(cid));

CREATE TABLE product (pid Integer NOT NULL,

pname Varchar(20) ,

pprice Integer,

PRIMARY KEY(pid));

CREATE TABLE transaction (cid Integer NOT NULL,

pid Integer NOT NULL,

tdate Date NOT NULL,

tqnt Integer,

PRIMARY KEY(cid, pid, tdate));

SHOW TABLES;

3 Inserting Test Data with Java Servlets and HTML:

3.1 Inserting a New Transaction

HTML Form

<html>

<head>

<title>Insert Transaction</title>

</head>

<body>

<h1>

Insert a new transaction:

</h1>

<p>

Customer ID:

</p>

<p>

</p>

<p>

Product ID:

</p>

<p>

</p>

<p>

Transaction Date:

</p>

<p>

</p>

<p>

Transaction Quantity:

</p>

<p>

</p>

<p>

</p>

</form>

<hr/>

<p>

</p>

</body>

</html>

Java Servlet

package dbase2.example;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletResponse;

import java.io.IOException;

import java.io.PrintWriter;

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.DriverManager;

import java.sql.ResultSet;

public class InsertTransaction extends HttpServlet{

public void doGet(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException{

response.setContentType("text/html");

PrintWriter writer = response.getWriter();

writer.write("<html>\n");

writer.write("<head>\n");

writer.write("<title>Insert Transaction</title>\n");

writer.write("<link rel = \"stylesheet\" type = \"text/css\" href = \"style.css\"/>\n");

writer.write("</head>");

writer.write("<body>");

String cid = request.getParameter("cid");

if((cid == null) || (cid.length() == 0)){

printMsg("Can not insert a transaction with no cid!", writer, request);

return;

}

int cid_number;

try{

cid_number = Integer.parseInt(cid);

}catch(NumberFormatException exc){

exc.printStackTrace();

printMsg("Can not insert a transaction with no valid cid!", writer, request);

return;

}

String pid = request.getParameter("pid");

if((pid == null) || (pid.length() == 0)){

printMsg("Can not insert a transaction with no pid!", writer, request);

return;

}

int pid_number;

try{

pid_number = Integer.parseInt(pid);

}catch(NumberFormatException exc){

exc.printStackTrace();

printMsg("Can not insert a transaction with no valid pid!", writer, request);

return;

}

String date = request.getParameter("date");

if((date == null) || (date.length() == 0)){

printMsg("Can not insert a transaction with no date!", writer, request);

return;

}

String qnt = request.getParameter("qnt");

if((qnt == null) || (qnt.length() == 0)){

printMsg("Can not insert a transaction with no quantity!", writer, request);

return;

}

int qnt_number;

try{

qnt_number = Integer.parseInt(qnt);

}catch(NumberFormatException exc){

exc.printStackTrace();

printMsg("Can not insert a transaction with no valid quantity!", writer, request);

return;

}

try{

Class.forName("com.mysql.jdbc.Driver");

}catch(ClassNotFoundException exc){

exc.printStackTrace();

printMsg("Can not insert a transaction : no JDBC driver found!", writer, request);

return;

}

try{

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/example", "student", "student");

Statement statement = connection.createStatement(); //referential integrity

ResultSet result = statement.executeQuery("SELECT * FROM Customer WHERE cid=" + cid);

if(!result.next()){

printMsg("Can not insert a transaction : no such customer!", writer, request);

return;

}

statement = connection.createStatement(); //referential integrity

result = statement.executeQuery("SELECT * FROM Product WHERE pid=" + pid);

if(!result.next()){

printMsg("Can not insert a transaction : no such product!", writer, request);

return;

}

String insert_sql_stmt = "INSERT INTO Transaction VALUES(" + cid + "," + pid + ",\"" + date + "\"," + qnt + ")";

statement = connection.createStatement();

int row = statement.executeUpdate(insert_sql_stmt);

printMsg("Transaction inserted!", writer, request);

}catch(SQLException exc){

exc.printStackTrace();

printMsg("Can not insert a transaction : database error!", writer, request);

} }

private void printMsg(String msg, PrintWriter writer, HttpServletRequest request){

writer.write("<h1>" + msg + "</h1>\n");

writer.write("<hr/>");

writer.write("<a href = \"" + request.getHeader("Referer") + "\">Back</a>\n");

writer.write("</body>");

writer.write("</html>");

}}

Attention ! This section should contain at least two Database updating servlets !

4 Database Queries (Java Servlets):

4.1 Getting customers who bought a certain amount of a particular product

HTML Form

<html>

<head>

<title>Find Customer</title>

</head>

<body>

<h1>

Find customers who purchased certain amount of a particular product:

</h1>

<p>

Product name:

</p>

<p>

<p>

Amount:

</p>

<p>

</p>

<p>

</p>

</form>

<hr/>

<p>

</p>

</body>

</html>

Java Servlet

package dbase2.example;

import org.apache.ecs.Document;

import org.apache.ecs.Doctype;

import org.apache.ecs.Doctype.Html40Strict;

import org.apache.ecs.html.Body;

import org.apache.ecs.html.Head;

import org.apache.ecs.html.Title;

import org.apache.ecs.html.Link;

import org.apache.ecs.html.H1;

import org.apache.ecs.html.Table;

import org.apache.ecs.html.TR;

import org.apache.ecs.html.TH;

import org.apache.ecs.html.TD;

import org.apache.ecs.html.HR;

import org.apache.ecs.html.A;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletResponse;

import java.io.IOException;

import java.io.PrintWriter;

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.DriverManager;

import java.sql.ResultSet;

public class GetCustomer extends HttpServlet{

public void doGet(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException{

response.setContentType("text/html");

PrintWriter writer = response.getWriter();

Document document = new Document();

document.setDoctype(new Doctype.Html40Strict());

Title title = new Title("Shop Servlet");

document.setTitle(title);

Head head = document.getHead();

Body body = document.getBody();

Link link = new Link();

link.setRel("stylesheet");

link.setType("text/css");

link.setHref("style.css");

head.addElement(link);

String pname = request.getParameter("pname");

if((pname == null) || (pname.length() == 0)){

body.addElement(new H1("Can not search for a customer with no product name!"));

body.addElement(new HR());

body.addElement(new A(request.getHeader("Referer"), "Back"));

document.output(writer);

return;

}

String amount = request.getParameter("amount");

if((amount == null) || (amount.length() == 0)){

body.addElement(new H1("Can not search for a customer with no product amount!"));

body.addElement(new HR());

body.addElement(new A(request.getHeader("Referer"), "Back"));

document.output(writer);

return;

}

int amount_number;

try{

amount_number = Integer.parseInt(amount);

}catch(NumberFormatException exc1){

exc1.printStackTrace();

body.addElement(new H1("Can not search for a customer with no valid product amount!"));

body.addElement(new HR());

body.addElement(new A(request.getHeader("Referer"), "Back"));

document.output(writer);

return;

}

try{

Class.forName("com.mysql.jdbc.Driver");

}catch(ClassNotFoundException exc){

exc.printStackTrace();

body.addElement(new H1("Can not load JDBC driver!"));

body.addElement(new HR());

body.addElement(new A(request.getHeader("Referer"), "Back"));

document.output(writer);

return;

}

try{

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/example", "student", "student");

String query = "SELECT *, SUM(tqnt) AS sum FROM Customer, Transaction, Product WHERE ";

query += "Transaction.pid = Product.pid and Transaction.cid = Customer.cid AND pname='" + pname + "' ";

query += "GROUP BY Customer.cid HAVING sum >= " + amount;

Statement statement = connection.createStatement();

ResultSet result = statement.executeQuery(query);

body.addElement(new H1("Customers matching the query:"));

Table table = new Table();

TR table_header = new TR(true);

table_header.addElement((new TH(true)).addElement("Name"));

table_header.addElement((new TH(true)).addElement("City"));

table_header.addElement((new TH(true)).addElement("Phone"));

table.addElement(table_header);

while(result.next()){

TR table_row = new TR(true);

table_row.addElement((new TD(true)).addElement(result.getString("cname")));

table_row.addElement((new TD(true)).addElement(result.getString("ccity")));

table_row.addElement((new TD(true)).addElement((new Integer(result.getInt("cphone"))).toString()));

table.addElement(table_row);

}

body.addElement(table);

body.addElement(new HR());

body.addElement(new A(request.getHeader("Referer"), "Back"));

document.output(writer);

}catch(SQLException exc){

exc.printStackTrace();

body.addElement(new H1("Database Error!"));

body.addElement(new HR());

body.addElement(new A(request.getHeader("Referer"), "Back"));

document.output(writer);

}

Attention ! This section should contain at least three Database querying servlets ! Group By and Having statements should be illustrated !