11/2/2019

Database

JSP

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

1
 11/2/2019

DATABASE
̵ A database is an organized collection of data.
It is the collection of schemes, tables, queries,
reports, views and other objects.

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

̵ A database management system (DBMS)

is a computer software application that
interacts with the user, other applications, and
the database itself to capture and analyze
data.
̵ A general-purpose DBMS is designed to allow
the definition, creation, querying, update,
and administration of databases.
̵ Well-known DBMSs include MySQL,
PostgreSQL, Microsoft SQL Server, Oracle,
Sybase and IBM DB2.
4

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

2
 11/2/2019

DATABASE SERVER
Updates to databases when orders placed
Queries to database to validate order
- Does item exist?
- Is it still in stock?

Queries to database to display

information to user
- Query for all items in stock
- Query to get order status, etc.

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

̵ Access to database controlled by database server

▪ Constantly running (like web container)
▪ Programs communicate with it Server runs database queries and
updates for databases it controls
▪ Server handles security of database
• Most are password-controlled
▪ Examples:
• MySQL (free)
• Oracle Database server
• MS Server
• Not Access! control
servlet database

JSP
6

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

3
 11/2/2019

̵ 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

Statement object
JSP select * from books
database

ResultSet object
productCode title price
productCode title price
productCode title price 7

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

MYSQL
̵ The MySQL software delivers a very fast, multi-
threaded, multi-user, and robust SQL (Structured
Query Language) database server.
̵ MySQL is a database system used on the web.
̵ MySQL is ideal for both small and large applications
̵ MySQL uses standard SQL
̵ MySQL compiles on a number of platforms
̵ MySQL is free to download and use

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

4
 11/2/2019

Using mysql in Xampp

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

Note

select disk except disk of system

10

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

10

5
 11/2/2019

Start Mysql server

Start or stop Mysql Server

11

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

11

MySQL Manager tool

̵ Navicat

12

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

12

6
 11/2/2019

Create MySQL connection

̵ Before connect to MySQL, we need start MySQL Server

13

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

13

Enter connection name

Enter port MySQL (3306)

Enter MySQL username

Enter MySQL password

14

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

14

7
 11/2/2019

̵ Double click Localhost to open connection

15

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

15

̵ List of database in localhost

16

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

16

8
 11/2/2019

New Database
̵ Right click to localhost -> choose New Database

17

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

17

New Database
̵ Enter information of new database

18

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

18

9
 11/2/2019

New Database

Enter Database
name

Choose charset of
database

Choose collation of
charset

19

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

19

New Database

20

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

20

10
 11/2/2019

Double click test1 to open database test1

21

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

21

SQL review
̵ SQL (Structured Query Language) is a special
purpose programming language designed for
managing data held in a relational database
management system.

̵ The scope of SQL includes data insert, query,

update and delete, schema creation and
modification, and data access control

22

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

22

11
 11/2/2019

Select
̵ Syntax:
▪ SELECT field FROM table WHERE condition;
̵ EX:
▪ SELECT ID, NAME, SALARY FROM CUSTOMERS;

Data in table Data after query

23

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

23

Insert
̵ Syntax:
▪ INSERT INTO TABLE (list column) VALUES (list value);
or
▪ INSERT INTO TABLE VALUES (list value);
̵ EX:
▪ INSERT INTO CUSTOMERS (ID,NAME,AGE,
ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad',
2000.00 );
▪ INSERT INTO CUSTOMERS VALUES (2, 'Muffy', 24, 'Indore',
10000.00 );

24

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

24

12
 11/2/2019

Update
̵ Syntax:
▪ UPDATE table SET [data change] WHERE [condition];
̵ Ex: UPDATE CUSTOMERS SET ADDRESS = 'Pune'
WHERE ID = 6;

Before update After update

25

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

25

Delete
̵ Syntax:
▪ DELETE FROM table WHERE [condition];
̵ Ex:
▪ DELETE FROM CUSTOMERS WHERE ID = 6;

Before delete After delete

26

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

26

13
 11/2/2019

JDBC
̵ JDBC is a Java database connectivity technology (Java
Standard Edition platform) from Oracle Corporation.
This technology is an API for the Java programming
language that defines how a client may access
a database.

̵ JDBC is oriented towards relational databases.

̵ JDBC provides methods for querying and updating data

in a database.

27

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

27

Database Drivers
̵ Database server does not understand JDBC 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 database server

control database
servlet driver
DBMS database
JSP JDBC

28

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

28

14
 11/2/2019

MySQL JDBC DRIVER

̵ Go to link: http://dev.mysql.com/downloads/connector/
and down load connecter j

29

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

29

30

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

30

15
 11/2/2019

̵ Choose no thanks, just start my download

31

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

31

Install MySQL lib

̵ Copy mysql-connector-java-xxx-bin.jar to folder lib

32

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

32

16
 11/2/2019

33

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

33

JDBC Connection
❖Major objects involved:
• Connection: represents connection to a database through a server
• Statement: represents SQL statement executed on database via that
connection
• ResultSet: represents “list” of records matching a query( select query)

ResultSet object
Select * from table

34

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

34

17
 11/2/2019

Create connection
̵ Load driver
▪ Class.forName(“driver name")
̵ Create Connection Object to open connection
to database
Load driver mysql
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection conn=DriverManager.getConnection(
"jdbc:mysql://localhost/test", "root", "");
%>

Create Connection Object

35

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

35

̵ To connect database server we need:

▪ Type of server (MySQL, oracle, MS SQL,…)
▪ Driver for database server.
▪ Url to access to server.
▪ Username and password to access to server.
̵ Base URL:
▪ Syntax: jdbc:server type:url of server/database name
▪ Ex:jdbc:mysql://localhost/test

36

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

36

18
 11/2/2019

Exception Handling in JDBC

̵ Any database-related statement may throw an SQLException
▪ Your code must put in try/catch block
▪ May also need to catch other exceptions
• ClassNotFoundException for missing database driver

<%
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost/test", "root", "");
} catch (ClassNotFoundException e1) {
// NO DRiVER
} catch (SQLException e2){
// CONNECTION ERROR
}
%>
37

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

37

Insert data
̵ Create statement:
▪ Statement s=conn.createStatement();
̵ Execute query: using method executeUpdate()

String username= ...

String password= ...
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost/test", "root", "");
Statement s=conn.createStatement();
s.executeUpdate("INSERT INTO user(username,password)
VALUES("+username+","+password+")");
38

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

38

19
 11/2/2019

Updata data
̵ Using like insert data.

String id= ...

String productName= ...
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost/test", "root", "");
Statement s=conn.createStatement();
s.executeUpdate("UPDATE product set name = '"+productName+"'
where id = "+id);

39

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

39

Select data
̵ Create statement:
▪ Statement s=conn.createStatement();
̵ Execute query: using method executeUpdate()
▪ Using ResultSet object to get data return.

String listProduct= ...

Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost/test", "root", "");
Statement s=conn.createStatement();
ResultSet rs=s.executeQuery("SELECT * FROM product where
listProduct = "+listProduct);

40

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

40

20
 11/2/2019

Reading ResultSets
̵ How to access:
▪ Only access current record.
▪ Move next record to continues read ResultSets.
̵ Syntax:
▪ Next record: using next() to move next record.
Method return a value Boolean, true if can read
next record, false if can’t read next record.
while(ResultSets.next()){
// read data here
}

41

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

41

▪ Read data:
• Read by column index:
DATATYPE value = ResultSets.getTYPE(int column_index).
• Read by column name:
DATATYPE value = ResultSets.getTYPE(int column_name).

Specify type data is to be read in as

varChar → getString
int → getInt
double → getDouble

42

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

42

21
 11/2/2019

try {
String listProduct= ...
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost/test", "root", "");
Statement s=conn.createStatement();
ResultSet rs=s.executeQuery("SELECT * FROM product
where listProduct = "+listProduct);
while(rs.next()){
rs.getString(1);//read by column index
rs.getString("name");//read by column name
}

} catch (ClassNotFoundException e1) {

// NO DRiVER
} catch (SQLException e2){
// CONNECTION ERROR
} 43

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

43

Efficient and Safe

Database

44

22
 11/2/2019

Problem
̵ We have a product. Now quantity of this product is
100, and 2 client modify data with a processor.
̵ Processor working with client 1:
▪ Client 1 load quantity of this product and view, quantity
of product is 100.
̵ Processor working with client 2:
▪ Client 2 load quantity of this product, too. And quantity
still is 100.
▪ Client 2 set quantity reduced 1 product. (quantity =99)
̵ Processor swap back client 1:
▪ Client 1 Client 2 set quantity reduced 1 product(➔
quantity =99)
̵ Expected results: 98
45

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

45

Why quantity product is 97

C
L
I Down quantity
Get quantity
E (quantity =100) (quantity = 99)
N
T
1
C
L
I Get quantity
E Down quantity
(quantity =100)
N (quantity = 99)
T
1

46

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

46

23
 11/2/2019

Solution

❖Can declare sections of code to be synchronized

• Only one thread may execute it at a time
• Another thread cannot start the code until the first
has finished it
❖Syntax: synchronized(object) { code }

Only one thread at a time should be able to execute this code

on this object

47

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

47

Statement s;
synchronized(s){
try{
s=conn.createStatement();
ResultSet rs=s.executeQuery("SELECT quantity FROM
product where idproduct = "+id);
rs.next() ;
int quantity=rs.getInt("quantity");
quantity-=1;
s.executeUpdate("UPDATE product SET quantity = “
+quantity+" WHERE idproduct = "+id);
}catch(SQLException e){
//BAD SQL
}
}

48

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

48

24
 11/2/2019

Problem
̵ We have a login from with 2 input
▪ Username
▪ Password
̵ And SQL to login:
▪ SELECT * FROM user where username =“username
value” and password = “password value”
▪ Ex: SELECT * FROM user where username =“ti”
and password = “123456”

49

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

49

̵ What happen when user input username is: “or

“1”=“1
❖SQL query now: SELECT * FROM user where
username =“”or “1”=“1” and password = “password
value”
➔ Query always true

50

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

50

25
 11/2/2019

Solution ➔ PrepareStatement
̵ Prepared statement is a feature used to
execute the same or similar database
statements repeatedly with high efficiency.
▪ Prepare: The statement template is created by the
application and sent to the database management
system (DBMS). Certain values are left unspecified,
called parameters, placeholders or bind variables
(labelled "?" below):
• INSERT INTO PRODUCT (name, price) VALUES
(?,?)

51

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

51

̵ The DBMS parses, compiles, and performs

query optimization on the statement template,
and stores the result without executing it.

̵ Execute: At a later time, the application

supplies (or binds) values for the parameters,
and the DBMS executes the statement
(possibly returning a result). The application
may execute the statement as many times as it
wants with different values.
52

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

52

26
 11/2/2019

Using prepareStatement
̵ Define prepareStatement:
▪ PreparedStatement psupdate=null;
̵ Create prepateStatement:
▪ psupdate=conn.prepareStatement("SELECT
quantity FROM product where idproduct
= ?");
̵ Set value to SQL:
▪ Systax: using method setTYPE(index, value);

psupdate.setInt(1, id);
53

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

53

psupdate=conn.prepareStatement("SELECT quantity FROM

product where idproduct = ?");
psupdate.setInt(1, id);
ResultSet rs=psupdate.executeQuery();
rs.next() ;
int quantity=rs.getInt("quantity");
quantity-=1;
psupdate=conn.prepareStatement("UPDATE product SET quantity
= ? WHERE idproduct = ?");
psupdate.setInt(1, quantity);
psupdate.setInt(2, id);
psupdate.executeUpdate();

54

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

54

27
 11/2/2019

Connection Pooling
̵ When connection requested:
▪ Get unused connection from pool

Connections currently in
web container use
JSP/servlet
Connection pool database server
JSP/servlet
database
Request for new
connection

New
JSP/servlet
Free unused connection 55

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

55

Connection Pooling
̵ When connection requested:
▪ Connection used by servlet/JSP

Connections currently in
web container use
JSP/servlet
Connection pool database server
JSP/servlet
database

New Reference to unused

JSP/servlet connection returned Free unused connection 56

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

56

28
 11/2/2019

Connection Pooling
̵ When finished, JSP/servlet returns the connection back to the
pool
▪ Now free for use by another

Connections currently in
web container use
JSP/servlet
Connection pool database server
JSP/servlet
database

Return to pool

New
JSP/servlet
Free unused connection 57

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

57

Connection Pooling
̵ Unlike prepared statement, no built in Java
methods/classes
▪ Write your own
• http://java.sun.com/developer/onlin
eTraining/Programming/JDCBook/conpo
ol.html
▪ Third party classes
• dbConnectionBroker, etc.
▪ Build components directly into
web.xml/context.xml files
• Page 466 of text
• Not well supported by Tomcat
58

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

58

29
 11/2/2019

Connection Pooling
̵ Usually static object
▪ Automatically constructs connections first time getConnection
called
̵ Usually provide following methods:
▪ ConnectionPool.getInstance()
▪ freeConnection()
̵ Example:
Connection connection = ConnectionPool.getInstance();

// Code that creates statement, executes queries, etc.

connection.freeConnection();

59

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

59

Connection Pooling
̵ Required parameters:
▪ Driver name
• "com.mysql.jdbc.Driver“
▪ Url, name, and password Necessary so connection
pool can connect to
• "jdbc:mysql://localhost/bookstore",
“root", “sesame" database
▪ Number of initial connections to create
• Usually a few hundred to a few thousand
▪ Timeout for idle connections
• Time after which idle connections are returned to pool automatically
• Important to prevent pool running out!

60

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

60

30
 11/2/2019

DEMO
61

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

61

Q&A

62

08/2019 KHOA CNTT - NLU LẬP TRÌNH WEB PHAN ĐÌNH LONG 43

62

31