CS 195 Course Outline & Introduction to Java - Orca

indexadjustmentInternet and Web Development

Nov 13, 2013 (3 years and 9 months ago)

61 views

CSC 411/511:

DBMS Design

Dr. Nan Wang

CSC411_L12_JDBC_MySQL

1

MySQL and JDBC

CSC411_L12_JDBC_MySQL

Dr. Nan Wang

2

Web
-
based database applications


Online book store


What are needed:


Web pages


Web server


Apache


Database


Database server


MySQL


CGI scripts


Java serverlet


Connector which enables applications connect to database


JDBC


2

CSC411_L12_JDBC_MySQL

Dr. Nan Wang

3

Web Server

3

CSC411_L12_JDBC_MySQL

Dr. Nan Wang

4

Database Server

4

CSC411_L12_JDBC_MySQL

Dr. Nan Wang

5

5

Introduction


RDBMS


Relational database management system


MySQL


Open source


Available for both Windows and Linux


dev.mysql.com/downloads/mysql/5.0.hml


JDBC


Java Database Connectivity


JDBC driver


Enable Java applications to connect to database


Enable programmers to manipulate databases using JDBC

CSC411_L12_JDBC_MySQL

Dr. Nan Wang

6

6

Instructions to Install MySQL and MySQL
Connector/J


Install MySQL


Insert CD and change directory to
D:
\
software
\
MySQL
\
mysql
-
4.0.20c
-
win


Double click
SETUP.EXE


Following the instruction


Install MySQL Connector/J


Copy
mysql
-
connector
-
java
-
3.0.14
-
production.zip


Open
mysql
-
connector
-
java
-
3.0.14
-
production.zip


Extract its content to the C:
\

driv

CSC411_L12_JDBC_MySQL

Dr. Nan Wang

7

7

Instructions on Setting MySQL User
Account


Set up a user account


Start database server by executing the script
C:
\
mysql
\
bin
\
mysqld


Start the MySQL monitor by executing the command
C:
\
mysql
\
bin>mysql

h localhost

u root


Create an account


mysql> USE mysql;


mysql> INSERT INTO user SET Host=‘localhost’,


User=‘jhtp6’, Password=PASSWORD(‘jhtp6’),





Select_priv=‘Y’, Insert_priv=‘Y’,




Update_priv=‘Y’, Delete_priv=‘Y’,


Create_priv=‘Y’, Drop_priv=‘Y’,


References_priv=‘Y’, Execute_priv=‘Y’;


mysql> FLUSH PRIVILEGES;


mysql> exit;

CSC411_L12_JDBC_MySQL

Dr. Nan Wang

8

8

Creating Database
books

in MySQL


Create
books

database


Open Command Prompt


Change to the
C:
\
mysql
\
bin

directory


Start database by executing the command
C:
\
mysql
\
bin
\
mysqld


Copy SQL script
books.sql

to
C:
\
mysql
\
bin

directory


Open another Command Prompt


Change to the
C:
\
mysql
\
bin

directory


Create the
books

database by executing the command
C:
\
mysql
\
bin>mysql

h localhost

u jhtp6

p < books.sql

CSC411_L12_JDBC_MySQL

Dr. Nan Wang

9

9

Relational Databases


Relational database


Table


Rows, columns


Primary key


Unique data


SQL queries


Specify which data to select from a table


Employee table

CSC411_L12_JDBC_MySQL

Dr. Nan Wang

10

10

Relational Database Overview: The
books

Database


Sample
books

database


Four tables


authors


authorID
,
firstName
,
lastName


publishers


publisherID
,
publisherName


titles


isbn
,
title
,
editionNumber
,
copyright, publisherID
,
imageFile
,
price


authorISBN


authorID
,
isbn

CSC411_L12_JDBC_MySQL

Dr. Nan Wang

11

11

Sample Data (
author

and
publisher
)

authorID

firstName

lastName

1

Harvey

Deitel

2

Paul

Deitel

3

Tem

Nieto

4

Sean

Santry


publisherID

publisherName

1

Prentice Hall

2

Prentice Hall PTG


CSC411_L12_JDBC_MySQL

Dr. Nan Wang

12

12

Sample data (
titles

and
authorISBN)



isbn


title

editionN
umber

copy
-

right

publisher
ID

Image

File

price

0131426443

C How to
Program

4

2004

1

chtp4.jpg

85.00

0130384747

C++ How to
Program

4

2003

1

cpphtp4.j
pg

85.00

0130461342

Java Web
Services for
Experienced
Programmers

1

2003

1

jwsfep1.j
pg

54.99

0131483986

Java How to
Program

6

2005

1

jhtp6.jpg

85.00

013100252X

The Complete
C++ Training
Course

4

2003

2

cppctc4.j
pg

109.99

0130895601

Advanced Java
2 Platform
How to
Program

1

2002

1

advjhtp1.
jpg

69.95


authorID

isbn

authorID

isbn

1

0130895725

2

0139163050

2

0130895725

3

0130829293

2

0132261197

3

0130284173

2

0130895717

3

0130284181

2

0135289106

4

0130895601


CSC411_L12_JDBC_MySQL

Dr. Nan Wang

13

13

Table relationships in
books


CSC411_L12_JDBC_MySQL

Dr. Nan Wang

14

14

Manipulating Databases with JDBC

Seven Basic Steps in Using JDBC

1.
Load the driver

2.
Define the Connection URL

3.
Establish the Connection

4.
Create a Statement object

5.
Execute a query

6.
Process the results

7.
Close the connection

CSC411_L12_JDBC_MySQL

Dr. Nan Wang

15

15

JDBC Drivers


JDBC consists of two parts:


JDBC API, a purely

Java
-
based API


JDBC Driver Manager,which

communicates with

vendor
-
specific drivers that

perform the real communication

with the database.


Point: translation to vendor

format is performed on

the client


No changes needed

to server


Driver (translator) needed

on client

Database
JDBC Driver Manager
Java Application
JDBC API
JDBC Driver API
Vendor Specific
JDBC Driver
Vendor Specific
ODBC Driver
JDBC-ODBC
Bridge
Database

CSC411_L12_JDBC_MySQL

Dr. Nan Wang

16

16

Connecting to and Querying a Database


DisplayAuthors


Retrieves the entire
authors

table


Displays the data in the standard output stream


Example illustrates


Connect to the database


Query the database


Process the result



http://www.apl.jhu.edu/~hall/java/Servlet
-
Tutorial/Servlet
-
Tutorial
-
Intro.html

CSC411_L12_JDBC_MySQL

Dr. Nan Wang

17

17

Example: DisplayAuthors.java (1)


1

// Fig. 25.25: DisplayAuthors.java


2

// Displaying the contents of the authors table.


3

import

java.sql.Connection;


4

import

java.sql.Statement;


5

import

java.sql.DriverManager;


6

import

java.sql.ResultSet;


7

import

java.sql.ResultSetMetaDat
a;


8

import

java.sql.SQLException;


9


10

public

class

DisplayAuthors

11

{

12


// JDBC driver name and database URL


13


static

final

String JDBC_DRIVER =
"com.mysql.jdbc.Driver"
;


14


static

final

St
ring DATABASE_URL =
"jdbc:mysql://localhost/books"
;

15



16


// launch the application

17


public

static

void

main( String args[] )

18


{

19


Connection connection =
null
;
// manages connection

20


Statement statement =
null
;
// query statement

21



22


// connect to database books and query database

23


try


24


{

25


Class.forName(
JDBC_DRIVER

);
// load database driver clas
s

26


27


// establish connection to database



28


connection =


29


DriverManager.getConnection(
DATABASE_URL
,
"jhtp6"
,
"jhtp6"

);

30



Imports the JDBC classes and
interfaces from package
java.sql.*

Specifies the JDBC
driver’s class name

Specifies the
database URL

Loads the database
driver.

Declare and initialize
a
Connection

reference

CSC411_L12_JDBC_MySQL

Dr. Nan Wang

18

18

Example: DisplayAuthors.java (2)

31


// create Statement for querying database

32


statement = connection.createStatement();

33



34


// query database


35


ResultSet resultSet = statement.executeQuery
(


36


"SELECT authorID, firstName, lastName FROM authors"
);

37



38

// process query results

39


ResultSetMetaData metaData = resultSet.getMetaData();

40


int
numberOfColumns = metaData.get
ColumnCount();


41

System.out.println(
"Authors Table of Books Database:"
);

42



43


for
(
int
i =
1
; i <= numberOfColumns; i++ )

44

System.out.printf(
"%
-
8s
\
t"
,
metaData.getColumnName( i )
);

45


System.out.println();

46



47


while
(
resultSet.next()
)

48

{

49


for
(
int
i =
1
; i <= numberOfColumns; i++ )

50

System.out.printf(
"%
-
8s
\
t"
,
resultSet.getObject( i )
);

51

Syst
em.out.println();

52

}
// end while

53

}
// end try

54


catch
(
SQLException sqlException
)

55

{

56

sqlException
.printStackTrace();

57

System.exit(
1
);

58

}
// end catch



Obtain an object that implements
interface
Statement
.

execute a query

Obtains the metadata.

Retrieve the number of
columns

Obtain column
name

Position to the first row in the
ResultSet

with method
next

Extract the contents
of one column in the
current row

Catch
SQLException


CSC411_L12_JDBC_MySQL

Dr. Nan Wang

19

19

Example: DisplayAuthors.java (3)

59


catch
( ClassNotFoundException classNotFound )


60

{

61

classNotFound.printStackTrace();

62

System.exit(
1
);


63

}
// end catch


64


finally

// ensure statement and connection are closed properly

65


{



66


try


67


{


68


statement.close();


69


connect
ion.close();


70


}
// end try



71


catch
( Exception exception )


72


{



73


exception.printStackTrace();


74


System.exit(
1
);


75


}
// end catch



76


}
// en
d finally



77

}
// end main

78

}
// end class DisplayAuthors



Authors Table of Books Database:

authorID firstName lastName

1 Harvey Deitel

2 Paul
Deitel

3 Tem Nieto

4 Sean Santry




Close the
Statement

and
the database
Connection
.

ClassNotFoundException is
thrown if the class loader
cannot locate the driver class

CSC411_L12_JDBC_MySQL

Dr. Nan Wang

20

20

Popular JDBC driver names and
database URL.

RDBMS

JDBC driver name

Database URL format

MySQL

com.mysql.jdbc.Driver

jdbc:mysql://
hostname
/
databaseNa
me

ORACLE

oracle.jdbc.driver.OracleDr
iver

jdbc:oracle:thin:@
hostname
:
port

Number
:
databaseName

DB2

COM.ibm.db2.jdbc.net.DB2Dri
ver

jdbc:db2:
hostname
:
portnumber
/
data

baseName

Sybase

com.sybase.jdbc.SybDriver

jdbc:sybase:Tds:
hostname
:
portnub
er
/
databaseName


For more information on JDBC drivers, visit the Sun
Microsystems JDBC Web site,
http://developers.sun.com/product/jdbc/drivers

CSC 411/511:

DBMS Design

Dr. Nan Wang

CSC411_L12_JDBC_MySQL

21

Questions?