Tổng quan về JDBC.
• Kết nối cơ sở dữ liệu MySQL.
• Kết nối cơ sở dữ liệu PostGreSQL.
• JSP – JavaServer Page.
• Apache Tomcat Server
• Glassfish Server
• JDBC Resource
• JDBC connection pool
72 trang |
Chia sẻ: thuychi16 | Lượt xem: 868 | Lượt tải: 1
Bạn đang xem trước 20 trang tài liệu Phát triển ứng dụng mã nguồn mở - Bài 5: Java – jdbc java web application javaserver pages, để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên
Đoàn Thiện Ngân Bài 5 - 1/72
Bài 5 :
Java – JDBC
Java Web Application
JavaServer Pages
GV: ĐOÀN THIỆN NGÂN
Đoàn Thiện Ngân Bài 5 - 2/72
Nội dung
• Tổng quan về JDBC.
• Kết nối cơ sở dữ liệu MySQL.
• Kết nối cơ sở dữ liệu PostGreSQL.
• JSP – JavaServer Page.
• Apache Tomcat Server
• Glassfish Server
• JDBC Resource
• JDBC connection pool
Đoàn Thiện Ngân Bài 5 - 3/72
Tổng quan về JDBC
• Java programs communicate with databases
and manipulate their data using the
JDBC™API (Java Database Connectivity).
• A JDBC driver enables Java applications to
connect to a database in a particular DBMS
and allows programmers to manipulate that
database using the JDBC API.
• JDBC is almost always used with a relational
database. However, it can be used with any
table-based data source.
• For more information on JDBC, visit
Đoàn Thiện Ngân Bài 5 - 4/72
JDBC API
• The JDBC API is a Java API that can access
any kind of tabular data, especially data
stored in a Relational Database.
• JDBC helps you to write java applications
that manage these three programming
activities:
– Connect to a data source, like a database
– Send queries and update statements to
the database
– Retrieve and process the results received
from the database in answer to your query
Đoàn Thiện Ngân Bài 5 - 5/72
JDBC Product Components
JDBC includes four components:
1. JDBC API: provides programmatic access
to relational data. Applications can execute
SQL statements, retrieve results, and
propagate changes back to an underlying
data source. It can also interact with
multiple data sources in a distributed,
heterogeneous environment. The JDBC 4.0
API is divided into two packages: java.sql
and javax.sql. Both packages are included
in the Java SE and Java EE platforms.
Đoàn Thiện Ngân Bài 5 - 6/72
JDBC Product Components
2. JDBC Driver Manager: defines objects which
can connect Java applications to a JDBC
driver. DriverManager has traditionally been
the backbone of the JDBC architecture. It is
quite small and simple. The Standard
Extension packages javax.naming and
javax.sql let you use a DataSource object
registered with a Java Naming and Directory
Interface™ (JNDI) naming service to establish
a connection with a data source. You can use
either connecting mechanism, but using a
DataSource object is recommended whenever
possible.
Đoàn Thiện Ngân Bài 5 - 7/72
JDBC Product Components
3. JDBC Test Suite: The JDBC driver test suite
helps you to determine that JDBC drivers
will run your program. These tests are not
comprehensive or exhaustive, but they do
exercise many of the important features in
the JDBC API.
4. JDBC-ODBC Bridge: provides JDBC access
via ODBC drivers. We need to load ODBC
binary code onto each client machine that
uses this driver. As a result, the ODBC
driver is most appropriate on a corporate
network where client installations are not a
major problem, or for application server
code written in Java in a three-tier
architecture.
Đoàn Thiện Ngân Bài 5 - 8/72
JDBC Architecture – 2 tier
• JDBC API supports both two-tier and three-
tier processing models for database access.
Đoàn Thiện Ngân Bài 5 - 9/72
JDBC Architecture – 3 tier
• Commands are sent to a "middle tier" of services,
that then sends the commands to the data source.
Đoàn Thiện Ngân Bài 5 - 10/72
java.sql
Chú ý các đối tượng cơ sở dữ liệu trong
java.sql
• import java.sql.Connection;
• import java.sql.DriverManager;
• import java.sql.ResultSet;
• import java.sql.ResultSetMetaData;
• import java.sql.SQLException;
• import java.sql.Statement;
Ví dụ lấy dữ liệu từ table Actor trong cơ sở dữ
liệu sakila của MySQL - manually coding.
Đoàn Thiện Ngân Bài 5 - 11/72
JDBC in Java – DisplayActor.java
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
public class DisplayActor {
static final String DB_URL =
"jdbc:mysql://localhost/sakila";
static final String DB_USER = "root";
static final String DB_PWD = "secret";
public static void main(String args[]) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
Đoàn Thiện Ngân Bài 5 - 12/72
JDBC in Javatry {
// establish connection to database
connection = DriverManager.getConnection(
DB_URL, DB_USER, DB_PWD);
// create Statement for querying database
statement = connection.createStatement();
// query database
resultSet = statement.executeQuery(
"SELECT * FROM actor");
// process query results
ResultSetMetaData metaData =
resultSet.getMetaData();
int numberOfColumns =
metaData.getColumnCount();
System.out.println("Actor Table Database:\n");
Đoàn Thiện Ngân Bài 5 - 13/72
JDBC in Java
for (int i = 1; i <= numberOfColumns; i++)
System.out.printf("%-8s\t",
metaData.getColumnName(i));
System.out.println();
while (resultSet.next()) {
for (int i = 1; i <= numberOfColumns; i++)
System.out.printf("%-8s\t",resultSet.getObject(i));
System.out.println();
} // end while
} // end try
catch (SQLException sExp) {
sExp.printStackTrace();
} // end catch
Đoàn Thiện Ngân Bài 5 - 14/72
JDBC in Java
finally { // resultSet, connection , are closed
try {
resultSet.close();
statement.close();
connection.close();
} // end try
catch (Exception exp) {
exp.printStackTrace();
} // end catch
} // end finally
} // end main
} // end class
Đoàn Thiện Ngân Bài 5 - 15/72
Install MySQL JDBC
• Download MySQL JDBC
mysql-connector-java-5.1.33-bin.jar
• Install MySQL JDBC into JRE directory:
\lib\ext\
• Netbeans 8.0.1 ‘s MySQL JDBC
\ide\modules\ext\
javac DisplayActor.java
java DisplayActor
Đoàn Thiện Ngân Bài 5 - 16/72
MySQL JDBC Driver - Netbeans
• MySQL JDBC: MySQL Connector/J Driver
• Netbeans hỗ trợ kết nối JDBC rất tốt.
– Nhấp vào Services
– Có 2 cách tạo connection để test JDBC với
MySQL trước khi dùng
1. Dùng ngay thẻ Drivers, chọn MySQL
Connector/J Driver, R-click chọn Connect
Using
2. Dùng ngay thẻ Database, R-click chọn
New Connection, cửa sổ New Connection
Wizard, chọn MySQL Connector/J Driver,
Đoàn Thiện Ngân Bài 5 - 17/72
MySQL Connector/J Driver
• Netbeans
• Services/
Databases/
Drivers/
MySQL()
Đoàn Thiện Ngân Bài 5 - 18/72
MySQL Connector/J Driver
• R-Click MySQL (. )/ Connect Using
Đoàn Thiện Ngân Bài 5 - 19/72
MySQL Connector/J Driver
• Host
• Port
• Database
• User Name
• Password
• Remember
password
• Test
Connection
Đoàn Thiện Ngân Bài 5 - 20/72
MySQL Connector/J Driver
Đoàn Thiện Ngân Bài 5 - 21/72
MySQL Connector/J Driver
• Services/ Databases – New Connection
Đoàn Thiện Ngân Bài 5 - 22/72
MySQL Connector/J Driver
• Select MySQL (Connector/J Driver)
Đoàn Thiện Ngân Bài 5 - 23/72
/ide/modules/ext/
mysql-connector-java-******-bin.jar
Vị
trí JDBC
Driver
với
Netbeans
7.0.1 trên
Linux
Đoàn Thiện Ngân Bài 5 - 24/72
Swing Project with JDBC.
• Dùng Jtable chứa dữ liệu
• Dùng JScrollPane chứa Jtable giữ
Header
• Dữ liệu lấy từ table Actor trong cơ sở
dữ liệu sakila
Chú ý
• import javax.swing.JTable;
• import
javax.swing.table.DefaultTableModel;
• import java.sql.ResultSetMetaData;
Đoàn Thiện Ngân Bài 5 - 25/72
Gói jar
• Chú ý việc đóng gói jar
• Netbeans tạo thư mục dist trong thư mục
chứa project
• Tập tin đóng gói: .jar
• Gọi lệnh chạy ứng dụng
$ java –jar .jar
$ java –jar .jar
Đoàn Thiện Ngân Bài 5 - 26/72
PostGreSQL JDBC
• Tương tự như MySQL, chú ý khác biệt khai
DB URL.
jdbc.postgresql.org/download.html
jdbc.postgresql.org/documentation/documentation.html
Cú pháp:
DB_URL="jdbc:postgresql://localhost/pagila"
(DB_URL="jdbc:mysql://localhost/sakila")
Đoàn Thiện Ngân Bài 5 - 27/72
???
• JDBC và NetBeans
– JDBC?
– JDBC Driver với NetBeans?
– Viết thử chương trình dùng JDBC
– Viết thử chương trình dùng JDBC với
Netbeans
– Xem cấu trúc tổ chức một project trong
Netbeans
– Biên dịch (build) và chạy thử
– Chạy thử từ gói jar
Đoàn Thiện Ngân Bài 5 - 28/72
Java Web Application
• A Java web application generates interactive web
pages containing various types of markup language
(HTML, XML, and so on) and dynamic content.
• It is typically comprised of web components such as
JavaServer Pages (JSP), servlets and JavaBeans to
modify and temporarily store data, interact with
databases and web services, and render content in
response to client requests.
• Because many of the tasks involved in web application
development can be repetitive or require a surplus of
boilerplate code, web frameworks can be applied to
alleviate the overhead associated with common
activities.
• For example, many frameworks, such as JavaServer
Faces, provide libraries for templating pages and
session management, and often promote code reuse.
Đoàn Thiện Ngân Bài 5 - 29/72
Web Server hỗ trợ JSP
• Apache Tomcat
• Glassfish
• JBoss
• jetty
• Resin
• IBM Websphere
• Oracle Weblogic
•
Đoàn Thiện Ngân Bài 5 - 30/72
JSP
• JSP (JavaServer Pages) is a server-side
technology that takes the Java language,
with its inherent simplicity and elegance,
and uses it to create highly interactive and
flexible web applications.
• In today’s unsure economic climate, having
the Java language as the cornerstone of JSP
makes JSP particularly compelling for
business: Because Java is an open
language (meaning it doesn’t require
expensive licenses),
• JSP solutions can be highly cost-effective.
Đoàn Thiện Ngân Bài 5 - 31/72
JavaServer Pages Technology
• JSP technology provides a simplified, fast
way to create dynamic web content. JSP
technology enables rapid development of
web-based applications that are server-
and platform-independent.
• The JSP Standard Tag Library (JSTL) is a
collection of tag libraries that implement
general-purpose functionality common to
many Web applications.
• JSP Technology in the Java EE 5 Platform
Đoàn Thiện Ngân Bài 5 - 32/72
JSP in the Java EE 5 Platform
• The focus of Java EE 5 has been ease of
development by making use of Java language
annotations that were introduced by J2SE 5.0.
JSP 2.1 supports this goal by defining
annotations for dependency injection on JSP
tag handlers and context listeners.
• Another key concern of the Java EE 5
specification has been the alignment of its
webtier technologies, namely JSP, JSF, and
JavaServer Pages Standard Tag Library (JSTL).
• The outcome of this alignment effort has been
the Unified Expression Language (EL), which
integrates the expression languages defined by
JSP 2.0 and JSF 1.1.
Đoàn Thiện Ngân Bài 5 - 33/72
The main key of Unified EL
• A pluggable API for resolving variable
references into Java objects and for
resolving the properties applied to these Java
objects,
• Support for deferred expressions, which may
be evaluated by a tag handler when needed,
unlike their regular expression counterparts,
which get evaluated immediately when a
page is executed and rendered
• Support for lvalue expression, which appear
on the left hand side of an assignment
operation. When used as an lvalue, an EL
expression represents a reference to a data
structure, for example: a JavaBeans property,
that is assigned some user input.
Đoàn Thiện Ngân Bài 5 - 34/72
The main key of Unified EL
• The new Unified EL is defined in its own
specification document, which is
delivered along with the JSP 2.1
specification.
• Unified EL, JSTL tags can now be used
with JSF components in an intuitive way.
• JSP 2.1 leverages the Servlet 2.5
specification for its web semantics.
Đoàn Thiện Ngân Bài 5 - 35/72
Java Servlet & JSP
• The Java Platform, Enterprise Edition 5 (Java
EE 5) has two different but complementary
technologies for producing dynamic web
content in the presentation tier—namely Java
Servlet and JavaServer Pages (JSP).
• Java Servlet was initially described as
extensions to a web server for producing
dynamic web content.
• JSP is a newer technology but is equally
capable of generating the same dynamic
content.
• However, the way in which a servlet and a JSP
page produce their content is fundamentally
different; servlets embed content into logic,
whereas JSP embed logic into content.
Đoàn Thiện Ngân Bài 5 - 36/72
Đoàn Thiện Ngân Bài 5 - 37/72
JavaServer Pages Best Practices
• One of the most important suggests that
there should be as little Java code as
possible embedded inside a JSP page.
• Experience has shown us that three key
factors benefit from this practice:
– Reusability
– Readability
– Maintainability
Đoàn Thiện Ngân Bài 5 - 38/72
JSP & JavaBean
Đoàn Thiện Ngân Bài 5 - 39/72
JSP & MVC
Đoàn Thiện Ngân Bài 5 - 40/72
JSP Web Application with MySQL
• Glassfish Server
• NetBeans
• JSP
• MySQL Database
• MySQL Connector/J JDBC Driver
•
webapp.html
Đoàn Thiện Ngân Bài 5 - 41/72
Web Application with MySQL
1. Planning the Structure
2. Creating a New Project
3. Preparing the Web Interface
4. Preparing Communication between the
Application and Database
5. Adding Dynamic Logic
6. Running the Completed Application
Đoàn Thiện Ngân Bài 5 - 42/72
Planning the Structure
• MySQL Database: MyNewDatabase
• MyNewDatabase: 2 Tables
– Subject
– Counselor
• MySQL Connector/J JDBC Driver
• Glassfish Server 4 hay 3
• NetBeans 8 hay 7
Đoàn Thiện Ngân Bài 5 - 43/72
Planning the Structure
• Simple web applications can be designed
using a two-tier architecture, in which a
client communicates directly with a server.
• In this tutorial, a Java web application
communicates directly with a MySQL
database using the Java Database
Connectivity API (MySQL Connector/J
JDBC Driver) that enables communication
between the Java code understood by the
application server (the GlassFish server),
and any content in SQL, the language
understood by the database server
(MySQL).
Đoàn Thiện Ngân Bài 5 - 44/72
Application Structure
• The application you build in this tutorial
involves the creation of two JSP pages.
• In each of these pages you use:
– HTML and CSS to implement a simple
interface
– JSTL technology to perform the logic that
• directly queries the database
• inserts the retrieved data into the two
pages.
• The two database tables, Subject and
Counselor (MySQL DB, MyNewDatabase),
Đoàn Thiện Ngân Bài 5 - 45/72
Two-tier scenario.
Đoàn Thiện Ngân Bài 5 - 46/72
Two-tier scenario.
• Project Java Web/ Web Application:
– index.jsp
– response.jsp
• The welcome page (index.jsp) presents the
user with a simple HTML form.
• When a browser requests index.jsp, the
JSTL code within the page initiates a query
on MyNewDatabase. It retrieves data from
the Subject database table, and inserts it
into to the page before it is sent to the
browser.
Đoàn Thiện Ngân Bài 5 - 47/72
Two-tier scenario.
• When the user submits his or her selection
in the welcome page's HTML form, the
submit initiates a request for the response
page (response.jsp).
• Again, the JSTL code within the page
initiates a query on MyNewDatabase. This
time, it retrieves data from both the Subject
and Counselor tables and inserts it into to
the page, allowing the user to view data
based upon his or her selection when the
page is returned to the browser.
Đoàn Thiện Ngân Bài 5 - 48/72
Page index.jsp
Đoàn Thiện Ngân Bài 5 - 49/72
Page response.jsp
Đoàn Thiện Ngân Bài 5 - 50/72
Creating a New Project
• New Project/ Java Web/ Web Application
• Server: Glassfish Server 4 hay 3
• Java EE 5 (hay Java EE 6, 7)
• No Framework
• Add New JSP page: response.jsp
• Modify index.jsp
– Điều chỉnh Heading 1
– Chèn Table, điều chỉnh.
– Chèn Form (action response.jsp), điều chỉnh
– Chèn DropDown List, điều chỉnh.
– Chèn Button, điều chỉnh.
Đoàn Thiện Ngân Bài 5 - 51/72
Preparing the Web Interface
• Modify response.jsp
– Chèn Table như hướng dẫn
– Tạo CSS nếu cần
– Chạy thử kiểm tra thiết kế trang web
Đoàn Thiện Ngân Bài 5 - 52/72
Index.jsp
Đoàn Thiện Ngân Bài 5 - 53/72
Response.jsp
Đoàn Thiện Ngân Bài 5 - 54/72
Preparing Communication between
the Application and Database
• The most efficient way to implement
communication between the server and database
is to set up a database connection pool.
• Numerous connections are created and
maintained in a connection pool. Any incoming
requests require access to the application's data
layer use an already-created connection from the
pool. When a request is completed, the connection
is not closed down, but returned to the pool.
• To instruct the application to use the data source,
this is typically done by creating an entry in the
application's web.xml deployment descriptor.
Đoàn Thiện Ngân Bài 5 - 55/72
Preparing Communication between
the Application and Database
• Kiểm tra cơ sở dữ liệu MyNewDatabase
trong MySQL
• Tạo kết nối JDBC với cơ sở dữ liệu
MyNewDatabase trong MySQL.
• Tạo JDBC Resource với MySQL – New
File / JDBC Resource - JDBC pool
• Kiểm tra glassfish-resources.xml
• Referencing the data source from the
application: web.xml / Add Resource
Đoàn Thiện Ngân Bài 5 - 56/72
Setup JDBC Data source &
Connection pool
• New File/ GlassFish/ JDBC Resource
• Create New JDBC Resource
– JDBC Connection Pool Name: IfpwafcadPool
– JNDI Name: jdbc/IFPWAFCAD
– Type: javax.sql.ConnectionPoolDataSource
• The wizard generates a file named
glassfish-resources.xml that contains
entries for the data source and
connection pool you specified.
• Kiểm tra trong Services/ Servers/
Resources/ JDBC
Đoàn Thiện Ngân Bài 5 - 57/72
Connection Spool
• Services
• Servers
• GlassFish Server 4 hay 3
• Resources
• JDBC
• Connection Pools
• IfpwafcadPool
Đoàn Thiện Ngân Bài 5 - 58/72
Referencing the data source
• Create an entry in the application's
web.xml deployment descriptor.
• New File/ Web/ Standard Deployment
Descriptor (web.xml)
Đoàn Thiện Ngân Bài 5 - 59/72
Add Resource Reference
• Reference Tab
• Add Resource Reference
– Resource Name: jdbc/IFPWAFCAD
Đoàn Thiện Ngân Bài 5 - 60/72
Adding Dynamic Logic
• We can now implement the JSTL code
that enables pages to generate content
dynamically, i.e., based on user input. To
do so, perform the following tasks.
1. Add the JSTL library to the project's
classpath
You can apply the JSTL to access and
display data taken from the database.
2. Implement JSTL code
Đoàn Thiện Ngân Bài 5 - 61/72
Adding the JSTL library
JSTL provides the following four basic
areas of functionality.
• core: common, structural tasks such as
iterators and conditionals for handling
flow control
• fmt: internationalization and localization
message formatting
• sql: simple database access
• xml: handling of XML content
This part focuses on usage of the core and
sql tag libraries.
Đoàn Thiện Ngân Bài 5 - 62/72
Adding Dynamic Logic
• Cài đặt mã JSTL:
– Chèn DB Report vào index.jsp lấy 2
thông tin subject_id và name từ table
subject (Test, Comment Table, Test
again)
– Gán dữ liệu tương ứng vào DropDown
List
– Chèn DB Query vào response.jsp lấy dữ
liệu tương ứng
• Gán dữ liệu vào vị trí tương ứng
Đoàn Thiện Ngân Bài 5 - 63/72
DB report
<%@taglib prefix="c"
uri=""%>
<%@taglib prefix="sql"
uri=""%>
SELECT subject_id, name FROM Subject
<c:forEach var="columnName"
items="${subjects.columnNames}">
Đoàn Thiện Ngân Bài 5 - 64/72
Test index.jsp
Đoàn Thiện Ngân Bài 5 - 65/72
Modify DropdownList
• Điều chỉnh form trong index.jsp
${row.name}
<input type="submit" value="submit"
name="submit" />
• Comment TABLE
• Test again
Đoàn Thiện Ngân Bài 5 - 66/72
Test again index.asp
• Dữ liệu được đưa vào DropdownList
Đoàn Thiện Ngân Bài 5 -