Transcript database server

CSCI 6962:
Server-side Design and Programming
Server-side Databases
and Drivers
Outline
•
•
•
•
Database servers and drivers
Databases and the MVC paradigm
SQL overview
The Derby database server
– Creating databases
– Populating a database
• MySQL
Databases and E-Commerce
• Long term information stored in databases
– Queries used to produce lists of products
– Updates used to store orders
• New record created for order in Order table
• Customer information added to other tables
• Quantities updated in inventory tables
Database Servers
• Access to database controlled by database server
– Constantly running (like web container)
– Server runs database queries and updates for databases it controls
– Server handles security of database
• Most are password-controlled
–
–
–
–
MySQL (free)
Oracle
MS Server
Not Access!
Web server
Database
Access
Object
Database server
database
Database Servers
• Programs create statement objects inside server
• Server executes them on the database
• Server stores results if query performed
• Program may then access those results
Database server
Web server
Database
Access
Object
Statement object
select * from books
ResultSet object
productCode title price
productCode title price
productCode title price
database
Database Drivers
• Database server does not understand Java/C# commands
• Only understands its own DBMS protocols
– Each server has its own DBMS
• Need a database driver to perform translation
– Obtain from database server provider
– Install in Java libraries
web container
JSF
page
Managed JDBC
bean/
support
class
database server
database
driver
DBMS
Databases and the MVC Paradigm
• Usually do not directly access database from JSF/ASP pages
– Requires both web programming and SQL knowledge
• Usually done in model classes (such as managed bean)
– Bean stores to database when commanded to
– Bean performs database query when need to retrieve data
• Often have built-in tools for database management
– SqlDataSource object in ASP
web container
JSF pages/
Managed bean
ASP pages/
Code behind
database server
Support
classes/
tools
SQL and Databases
Commands to database server use SQL (structured query language)
Common examples:
• Query for all records matching some condition:
select field from table where condition
Example:
select * from books where price < 10
• Delete all records matching some condition:
delete from table where condition
Example:
delete from books where ISBN = ‘0004’
SQL and Databases
• Set new field value for all records matching some condition:
update table set field = value WHERE condition
Example:
update books set price = 9.95 where productCode = ‘0004’
• Insert new record with given field values:
insert into table (field, field, field…)
values (value, value, value…)
Example:
insert into books (productCode, title, price) values
(‘0004’, ‘Green Eggs and Ham’, 9.95)
Derby
• Open source database server from Apache
– Not as powerful as MySql, Oracle, etc.
– Low memory overhead
– Fewer access privileges required
• Included in later versions of NetBeans
– Create database, create connection to it
– Create tables and execute SQL commands
– Manipulate database from Java program running in
NetBeans
Creating a Derby Database
•
•
•
•
Go to Services tab
Expand Databases node
Right-click Java DB node
Choose Create Database
Creating a Derby Database
• Give database a name
• Set User name to app
– Strange schema errors
otherwise
• Set a password
– (I usually also just
use “app”)
Creating a Derby Database
• Must connect to the database before using
– Right-click on link corresponding to database
– Choose Connect
Adding a Table
•
•
•
•
•
Expand link to database
Expand App node
Right-click Tables node
Select Create Table
Will get Create Table dialog
Adding a Table
• Add column for each field of table
• First field should be key field
– Check Primary key
– Check Unique
• Set types and other
properties as needed
– # of characters
in VARCHAR
– Etc.
Adding a Table
• Final table structure now shown in services window
Manipulating the Database
• Can execute queries directly (without using code)
– Right click table
– Select Execute Command
– Brings up command window
– Type in SQL statement
– Right-click window
– Select Run Statement
Viewing the Database
• Right click table
• Select View Data
• Executes SELECT * FROM tablename query
MySQL
• Free database server
– Go to www.mysql.com
– Follow Products tab
– Go to MySQL Community
Server
– Download and run
MySQL Database Driver
• From downloads page at
mysql.com
• Go to connectors page
• Go to Connector/J (for Java)
• Download and save
• Extract the
mysql-connector-java-5.version-bin.jar file
MySQL Database Driver
• In Netbeans right-click Libraries folder
of project
• Choose Add JAR/Folder
• Select mysql-connector-java-5.
version-bin.jar file from downloads
The MySQL Interface
• Command line interface to manipulate databases MySQL controls
– Available from PROGRAMS menu
– Will need to give password
– Can create new databases
create database databasename;
Creating Database Tables
• Must define field names and field types
• Some types:
– varChar(n) : String of up to n characters
– int(n) : integer up to n digits
– double(n, d) : decimal number up to n digits and d after the decimal
• Must declare a key field with “primary key”
• create table tablename (
keyfieldname type primary key,
fieldname type,
fieldname type,
…
);