MySQL & Project Technology Overview

thickbugSoftware and s/w Development

Oct 28, 2013 (3 years and 7 months ago)

46 views

CSE


255

Chapter 10
-
1

MySQL & Project Technology Overview


Questions…


derek.doran@uconn.edu




Project Architecture (
3
-
Tier)



MySQL Administration and Tools


Apache
-
Tomcat Overview


Introduction to JavaServer Pages


Project Suggestions, Environment Setup




Derek Doran CSE255 Project Fall07

CSE


255

Chapter 10
-
2

n
-
Tier Architecture


A Type of Abstraction for Developing large
-
scale
Applications


Define A T
ier

For Each Component Of A Project That
Can Be Developed In Tandem


Enables
Rapid Application Development


Focuses The Roles of Each Team Member


Example: 2
-
Tier Client/Server Architecture


Application (Client) Tier:


Engineer Implements Desktop Application


Database (Server) Tier:


DBA Defines the Schema, Implements Database, Writes
Stored Procedures, etc.


CSE


255

Chapter 10
-
3

3
-
Tier Architecture


Project Architecture Requirement
: A
3
-
Tier Web System


Data Access Tier


DB on a Secure Server


Logic (Business Logic), Application Tier


Servlets, Web Services
-

System Processing Code


Presentation Tier


HTML, JSP, ASP, ASPX Pages Served on Web Server


http://www.patriotbrothers.com/images/
3
tier_
2
.png

CSE


255

Chapter 10
-
4

Project


You Will Implement And Host All
3
-
Tiers on
One PC
!


MySQL Database Server (Data Tier)



JSP Backend Code, Servlets, …Code That Handles
Processing (Application Tier)



Dynamic HTML Pages via JSP/PHP (Presentation
Tier)



Group Members Need To Fill Specific Roles


Database Administrator


Graphic Artist and Web Developer


Software Engineer


Requirements Analyst (All Team Members)


CSE


255

Chapter 10
-
5

Database Tier


Secure Storage of Databases


Provide Authenticated Communication Only With
Application Tier


Objective: Keep Databases As Far Away From User
Interface As Possible


Hide DB Server Address


Hide Login Information


Make Database Transparent To User


Let DBA, Req. Analyst Develop DB and Schema
Separate From the Application

CSE


255

Chapter 10
-
6

Application Tier


A Collection of
Shared Procedures and Applications


Objective: Maximize Reusability


Examples


Common Processing Over Many Applications


Single Point of Communication with DB


Keep Processing Logic Separate From Form (GUI)



Prevents Mixing Of Form Logic….


When and How to Display Information


With Processing Logic


Cart Checkout


CC Verification


…etc.


CSE


255

Chapter 10
-
7

Presentation Tier


Contains HTML, JSP, ASP, PHP pages for
Presentation


Enables Graphic Designer, Web Developer to Develop
Attractive Pages
independently

Of Business
-
Level
Processing


Graphic Designer


Make The Static HTML Pages


Develop Page Resources (Images, Flash, Video, etc.)


Write Client
-
side Scripts (JavaScript/PHP) For Page


Web Developer



Modify HTML For JSP, ASP…



Communicate With Application Tier Developer


CSE


255

Chapter 10
-
8

Project Flexibility


All Technologies At All Tiers Can Be Substituted For
What You Are Most Familiar With


Implementation in JSP, PHP, ASP, .NET...


BUT


Your Pages Must Run On a
Free, Widely
Supported Application Server


Ex: If ASP.NET, VS 2005 or Web Express


If you Deviate From JSP and Apache Tomcat…


Alert Prof. Demurjian and I By E
-
Mail


Direct Us To Free Downloads of All Required
Technologies To Run Your System


You MUST Implement Your Database With MySQL

CSE


255

Chapter 10
-
9

MySQL by Example


MySQL architecture


Online book store implementation via MySQL


MySQL Administrator


Command Structures


Defining Tables and Relational Keys


Insert, update, and delete data


Commit and Rollback



MySQL Data Types


define a simple data model


Show Manual Construction As Example

CSE


255

Chapter 10
-
10

MySQL Architecture

http://dev.mysql.com/doc/refman/
5.1
/en/images/PSEArch.png

CSE


255

Chapter 10
-
11

Schema Setup via MySQL Administrator

Implement Tables With InnoDB Engine, for Foreign Key
Support and Improved Logging, Compared To MyISAM

CSE


255

Chapter 10
-
12

MySQL Command Line Client


Interface for Executing Manual Queries And Issuing Commands to
MySQL


Note Commands:


show databases;


use <dbName>;


show tables;


CSE


255

Chapter 10
-
13

Executing Manual Queries

CSE


255

Chapter 10
-
14

Executing Manual Queries

MySQL Command Line Resource

http://dev.mysql.com/doc/refman/5.0/en/mysql.html

CSE


255

Chapter 10
-
15

Browsing the catalog


view table structure


DESCRIBE <table>;


view your tables:


SHOW tables;

CSE


255

Chapter 10
-
16

Schema Administration


MySQL Schema Permissions are Based on Username



You Must Assign Permissions On a Per Schema Basis


using other schema’s requires permission (
GRANT
’s)


Syntax:

http://dev.mysql.com/doc/refman/
5.0
/en/grant.html


-
OR
-


Use the MySQL Administrator GUI!

CSE


255

Chapter 10
-
17

MySQL Administrator


Tool To Prepare Your Database For Access


User Administration

CSE


255

Chapter 10
-
18

Delete


Delete table with drop table


DROP TABLE customer;


Delete rows with delete from


DELETE FROM customer WHERE name = “Smith”;


DELETE FROM customer;


CSE


255

Chapter 10
-
19

Commit and Rollback


Modifications of tuples are temporarily stored in the
database


They become permanent after commit; statement has
been issued


You can undo all modifications since last commit using
rollback



Any data definition command like create results in
implicit commit



Termination of MySQL session also executes implicit
commit


Essential For Exception Handling!!


Try{ SQL(“insert…”); … SQL(“commit”); }


Catch(Exception){ SQL(“rollback”); }

CSE


255

Chapter 10
-
20

Things to remember


Always terminate your command with a Semicolon (;)



Exit MySQL Prompt by entering quit or exit at the
mysql> prompt



Commands can be in lower or upper case

CSE


255

Chapter 10
-
21

MySQL Useful Data types…


character data types :


varchar(x),


Variable Length String, where x is size in bytes


For ASCII, UTF
-
8
formats,
1
character =
1
byte


numerical data:


INT


implicit size of
32
-
bits


DECIMAL[(
M
[,
D
])] [UNSIGNED] [ZEROFILL]


Date


century year month day hour min sec


BLOB (Binary Large Object)



Data stored as a string of Binary Data


Max Size:
2
^
16
bytes

CSE


255

Chapter 10
-
22


Book



Customer



Order



LineItem

Relational Model

ISBN

Author

Title

Publisher

Date

Price

Cust #

Name

Email

Street

City

State

Zip

Order #

Cust#

Card

Exp.

Auth

Order#

Book#

Date

CSE


255

Chapter
10
-
23

Create Table

CSE


255

Chapter 10
-
24

Constraints


Column constraints:


primary key


foreign key


NULL values


create table CUSTOMER (id NUMBER
CONSTRAINT pk_cust PRIMARY KEY, name
VARCHAR
2
(
250
), email VARCHAR
2
(
60
)
CONSTRAINT nn_email NOT NULL, addr
NUMBER CONSTRAINT fk_addr REFERENCES
address(id));

CSE


255

Chapter
10
-
25

Table Constraint Clause

CSE


255

Chapter 10
-
26

Column Constraint Clause

CSE


255

Chapter
10
-
27

Schema Creation
-

book

Examples From SQL*Plus, the Oracle DB Command Line
Tool

But Syntax is the same!

CSE


255

Chapter 10
-
28

Schema Creation
-

customer

CSE


255

Chapter
10
-
29

Schema Creation
-

order

CSE


255

Chapter 10
-
30

Schema Creation
-

line_item

CSE


255

Chapter
10
-
31

View


Create a new view of data


CREATE VIEW clerk (id_number, person, department,
position) AS SELECT empno, ename, deptno, job FROM
emp WHERE job = 'CLERK’;



Why?

CSE


255

Chapter 10
-
32

View Command

CSE


255

Chapter
10
-
33

Alter Table


Add a column, or integrity constraint


Remove an integrity constraint


Cannot rename or drop a column


Keep a copy of the SQL statements you use to create
and alter tables


ALTER TABLE book MODIFY (price
NUMBER(
7
,
2
));


ALTER TABLE customer DROP PRIMARY KEY
CASCADE;


CSE


255

Chapter 10
-
34

Alter Table command

CSE


255

Chapter
10
-
35

Apache Tomcat


An Open
-
Source Application Server Platform for JSP


Widely Used


Mac OSX Uses Tomcat for Web Sharing


Provides environment for Java Code To Run In
Cooperation With The Web Server


Includes HTTP Content Server


JSP Pages Are Compiled Via Tomcat As The Page is
Requested


Rich Log
-
File, Error Handling Output

CSE


255

Chapter 10
-
36

Tomcat
-

JSP Connection


Tomcat Must Be Installed On PC That Runs Project To Deliver
Dynamic HTML


JSP Page With Embedded Java


Tomcat Compiles JSP, Then Executes Code


Embedded Java May Output Additional HTML


Tomcat Inserts
Dynamically Generated HTML

Into The Page


HTML Page Delivered To Requesting Entity


Important Resource:


http://www.coreservlets.com/Apache
-
Tomcat
-
Tutorial/index.html#Java
-
Home


Recommendation: Download Pre
-
Configured Package And
Follow Directions


CSE


255

Chapter
10
-
37

Java Server Pages + Servlets


Servlets


A Java Object That Receives an HTTP Request and
Generates an HTTP Response


Usually, an HTML Document


Like A Standalone Application That Is Always On, Living
On The Application Or Web Server


JSP


A
Definition
Of A Java Servlet Program!


Tomcat
Compiles the JSP Page Into a Servlet


The Servlet Executes On the Server


Sends An HTTP Reponse At End Of Computation


Compilation is Invisible To User


URL May Be
http://localhost/Query.jsp


Displayed Resource: An HTML Document!

CSE


255

Chapter 10
-
38

JSP


In a nutshell…


Enables the Embedding of Java Statements In your
HTML


In JSP page, The stdout Is Now The HTML
Document To Return

Ex:

<HTML>

<BODY>

Hello!


The time is now <%= new java.util.Date() %>

</BODY>

</HTML>

<!
--

<%= //java expression %> : Embedding a Java expression In HTML
--
>


Cannot Do Much With An Expression…



CSE


255

Chapter
10
-
39

Scriptlets


A Block Of Java Code Executed
Everytime The JSP
Page Is Invoked


Scriptlets Placed In <% … %> brackets

<HTML>

<BODY>


<%




System.out.println( "Evaluating date now" );



java.util.Date date = new java.util.Date();

%>


Hello!


The time is now <%= date %>

</BODY>

</HTML>


Variable date is Available In Entire HTML Form!


System.out.println outputs to Server Log

CSE


255

Chapter 10
-
40

Useful JSP Objects



out



out.println(“HTML”) prints content to HTML
document


“out” is of type javax.servlet.jsp.JspWriter



request



Object Referring to Transaction Between Browser
And Server

<HTML> <BODY> <% java.util.Date date = new java.util.Date(); %>


Hello!


The time is now


<%

out.println( date );




out.println( "<BR>Your machine's address is " );




out.println( request.getRemoteHost()); %>

</BODY> </HTML>


CSE


255

Chapter
10
-
41

Useful JSP Objects



response



Used To Alter HTTP Response Sent To Client Browser


response.sendRedirect(newURL);


When the HTTP Response For This Page Is Sent, The
Browser Will Instantly Send a Request For newURL!

<HTML>

<% if (!validateUser(userID)){



response.sendRedirect(“loginFailure.jsp”);

}%>

<BODY>

Thanks for Logging In!

</BODY>

</HTML>

CSE


255

Chapter 10
-
42

Example


Generating an HTML table With Dynamic Number Of
Rows


JSP Fragment:

<TABLE BORDER=
2
>


<%


for ( int i =
0
; i < n; i++ ) { %>


<TR>




<TD>Number</TD>



<TD><%= i+
1
%></TD>



</TR>



<% } %>

</TABLE>

CSE


255

Chapter
10
-
43

Conditional HTML


Given Static HTML, Decide To Use It As Output Or
Not


In JSP…

<% if ( hello ) {

%>



<P>Hello, world


<% } else { %>



<P>Goodbye, world

<%}%>


CSE


255

Chapter 10
-
44

JSP Directives


Package Importing


<%@ page import=“java.util.*, java.text.*” %>


Good Form: Place Imports Above <HTML> Tag


Directives Are Placed In <%@ … %> Tags


Including Other Files


<%@ include file=“other.jsp” %>


Useful for Keeping JSP Pages Clean


CSE


255

Chapter
10
-
45

JSP Method Declarations


Can Define Methods To Call In Your JSP


<%!


Date theDate = newDate();


Date getDate(){


return theDate;


}


%>

*Declarations are loaded
Once
, When The Page Is
First Loaded. On Refresh, theDate Will Not Change!

Suggestion: Declare MySQL Connection, So You Do
Not Restablish Connections On Page Refresh


CSE


255

Chapter 10
-
46

Attacking The Project


JSP Requires Combination Of


HTML Development


Java Development


First

Develop Static HTML Pages And Supporting Scripts


Presentation Tier Development


Use a Strong IDE If Unfamiliar With HTML


Dreamweaver


Visual Studio Express


You Will Get Familiar With HTML As You Create Pages
Via Drag
-
And
-
Drop


Then

Modify By Hand Static HTML To Include JSP Code


Application Tier Development


Eclipse EE Editor Offers Decent Support For JSP Editing


In Parallel Develop Your Data Tier


Data Model Development (Phase I and II)



MySQL Implementation And Population (Phase III)


CSE


255

Chapter
10
-
47

Environment Setup


1. Install Eclipse EE JDK5.0 for JSP editing


2. Install Your HTML Editor Of Choice


3. Install And Configure Tomcat


4. Install And Configure MySQL


5. Install MySQL Administrator


6. Install Connector/J 5.0


JDBC Connection Driver For MySQL


Test!


Static HTML


Simple DHTML


DHTML That Calls MySQL


DHTML With JavaScript


Usually…Configuration is Very Unique For A PC


Research Solutions On Internet To Guide You