MySQL

1
MySQL Project Technology Overview

• Project Architecture (3-Tier)?
• MySQL Administration and Tools
• Apache-Tomcat Overview
• Introduction to JavaServer Pages
• Project Suggestions, Environment Setup

• Questions
• derek.doran_at_uconn.edu

Derek Doran CSE255 Project Fall07
2
n-Tier Architecture

• A Type of Abstraction for Developing large-scale
  Applications
• Define A Tier 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.

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/3tier_2.png
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)?

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

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.

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

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

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

10
MySQL Architecture
http//dev.mysql.com/doc/refman/5.1/en/images/PSEA
rch.png
11
Schema Setup via MySQL Administrator
Implement Tables With InnoDB Engine, for Foreign
Key Support and Improved Logging, Compared To
MyISAM
12
MySQL Command Line Client

• Interface for Executing Manual Queries And
  Issuing Commands to MySQL
• Note Commands
• show databases
• use ltdbNamegt
• show tables

13
Executing Manual Queries
14
Executing Manual Queries
MySQL Command Line Resource http//dev.mysql.com/d
oc/refman/5.0/en/mysql.html
15
Browsing the catalog

• view table structure
• DESCRIBE lttablegt
• view your tables
• SHOW tables

16
Schema Administration

• MySQL Schema Permissions are Based on Username
• You Must Assign Permissions On a Per Schema Basis
• using other schemas requires permission
  (GRANTs)?
• Syntax
• http//dev.mysql.com/doc/refman/5.0/en/grant.html
• -OR-
• Use the MySQL Administrator GUI!

17
MySQL Administrator

• Tool To Prepare Your Database For Access
• User Administration

18
Delete

• Delete table with drop table
• DROP TABLE customer
• Delete rows with delete from
• DELETE FROM customer WHERE name Smith
• DELETE FROM customer

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)

20
Things to remember

• Always terminate your command with a Semicolon
  ()?
• Exit MySQL Prompt by entering quit or exit at the
  mysqlgt prompt
• Commands can be in lower or upper case

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 216 bytes

22
Relational Model

• Book
• Customer
• Order
• LineItem

ISBN
Author
Title
Publisher
Date
Price
Cust
Name
Email
Street
City
State
Zip
Order
Cust
Card
Exp.
Auth
Date
Order
Book
23
Create Table
24
Constraints

• Column constraints
• primary key
• foreign key
• NULL values
• create table CUSTOMER (id NUMBER CONSTRAINT
  pk_cust PRIMARY KEY, name VARCHAR2(250), email
  VARCHAR2(60) CONSTRAINT nn_email NOT NULL, addr
  NUMBER CONSTRAINT fk_addr REFERENCES
  address(id))

25
Table Constraint Clause
26
Column Constraint Clause
27
Schema Creation - book
Examples From SQLPlus, the Oracle DB Command
Line Tool
But Syntax is the same!
28
Schema Creation - customer
29
Schema Creation - order
30
Schema Creation - line_item
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?

32
View Command
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

34
Alter Table command
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

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.htmlJava-Home
• Recommendation Download Pre-Configured Package
  And Follow Directions

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!

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
• ltHTMLgt
• ltBODYgt
• Hello!  The time is now lt new java.util.Date()
  gt
• lt/BODYgt
• lt/HTMLgt
• lt!--
• lt //java expression gt Embedding a Java
  expression In HTML --gt
• Cannot Do Much With An Expression

39
Scriptlets

• A Block Of Java Code Executed Everytime The JSP
  Page Is Invoked
• Scriptlets Placed In lt gt brackets
• ltHTMLgt
• ltBODYgt
• lt    
• System.out.println( "Evaluating date now"
  )    
• java.util.Date date new
  java.util.Date()
• gt
• Hello!  The time is now lt date gt
• lt/BODYgt
• lt/HTMLgt
• Variable date is Available In Entire HTML Form!
• System.out.println outputs to Server Log

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
• ltHTMLgt ltBODYgt lt java.util.Date date new
  java.util.Date() gt
• Hello!  The time is now
• lt  out.println( date )    
• out.println( "ltBRgtYour machine's
  address is " )    
• out.println( request.getRemoteHost()
  ) gt
• lt/BODYgt lt/HTMLgt

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!
• ltHTMLgt
• lt if (!validateUser(userID))
• response.sendRedirect(loginFailure.jsp)
• gt
• ltBODYgt
• Thanks for Logging In!
• lt/BODYgt
• lt/HTMLgt

42
Example

• Generating an HTML table With Dynamic Number Of
  Rows
• JSP Fragment
• ltTABLE BORDER2gt
• lt   for ( int i 0 i lt n i ) gt
• ltTRgt   
• ltTDgtNumberlt/TDgt
•     ltTDgtlt i1 gtlt/TDgt
•     lt/TRgt        
• lt gt
• lt/TABLEgt

43
Conditional HTML

• Given Static HTML, Decide To Use It As Output Or
  Not
• In JSP
• lt if ( hello )  gt
•      ltPgtHello, world  
• lt else gt
•     ltPgtGoodbye, world
• ltgt

44
JSP Directives

• Package Importing
• lt_at_ page importjava.util., java.text. gt
• Good Form Place Imports Above ltHTMLgt Tag
• Directives Are Placed In lt_at_ gt Tags
• Including Other Files
• lt_at_ include fileother.jsp gt
• Useful for Keeping JSP Pages Clean

45
JSP Method Declarations

• Can Define Methods To Call In Your JSP
• lt!
• Date theDate newDate()
• Date getDate()
• return theDate
• gt
• 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

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)?

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
• UsuallyConfiguration is Very Unique For A PC
• Research Solutions On Internet To Guide You