Bài5 : Java –JDBC Java Web Application JavaServer Pages

GV: ĐOÀN THIỆN NGÂN

ĐoànThi ệnNgân Bài5 -1/72

Nộidung

• Tổngquan v ề JDBC. • Kết nối cơ sở dữ liệuMySQL. • Kết nối cơ sở dữ liệuPostGreSQL. • JSP –JavaServer Page. • Apache Tomcat Server • Glassfish Server • JDBC Resource • JDBC connection pool

ĐoànThi ệnNgân Bài5 -2/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 http://docs.oracle.com/javase/tutorial/jdbc/overview/index.html

ĐoànThi ệnNgân Bài5 -3/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ànThi ệnNgân Bài5 -4/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ànThi ệnNgân Bài5 -5/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 DataSourceobject 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 DataSourceobject is recommended whenever possible.

ĐoànThi ệnNgân Bài5 -6/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ànThi ệnNgân Bài5 -7/72

JDBC Architecture –2 tier • JDBC API supports both two-tier and three- tier processing models for database access.

ĐoànThi ệnNgân Bài5 -8/72

JDBC Architecture –3 tier • Commands are sent to a "middle tier" of services, that then sends the commands to the data source.

ĐoànThi ệnNgân Bài5 -9/72

java.sql

Chúý các đối tượng cơ sở dữ liệutrong 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ệusakila c ủaMySQL -manually coding.

ĐoànThi ệnNgân Bài5 -10/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ànThi ệnNgân Bài5 -11/72

JDBC in Java

try {

// 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

ResultSetMetaDatametaData= resultSet.getMetaData();

intnumberOfColumns=

metaData.getColumnCount();

System.out.println("Actor Table Database:\n");

ĐoànThi ệnNgân Bài5 -12/72

JDBC in Java

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

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

System.out.println(); while (resultSet.next()) {

for (inti= 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ànThi ệnNgân Bài5 -13/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ànThi ệnNgân Bài5 -14/72

Install MySQL JDBC

• Download MySQL JDBC http://dev.mysql.com/downloads/connector/j mysql-connector-java-5.1.33-bin.jar

• Install MySQL JDBC into JRE directory:

\lib\ext\

• Netbeans8.0.1 ‘s MySQL JDBC

\ide\modules\ext\ javacDisplayActor.java java DisplayActor

ĐoànThi ệnNgân Bài5 -15/72

MySQL JDBC Driver -Netbeans • MySQL JDBC: MySQL Connector/J Driver • Netbeans hỗ trợ kết nốiJDBC r ất tốt.

– Nhấpvào Services – Có2 cách t ạoconnection để test JDBC với

MySQL trướckhidùng 1.Dùngngayth ẻ Drivers, chọn MySQL

Connector/J Driver, R-click chọn Connect Using…

2.Dùngngayth ẻ Database, R-click chọn

New Connection, cửa sổ New Connection Wizard, chọn MySQL Connector/J Driver, …

ĐoànThi ệnNgân Bài5 -16/72

MySQL Connector/J Driver

• Netbeans • Services/

Databases/ Drivers/ MySQL(…)

ĐoànThi ệnNgân Bài5 -17/72

MySQL Connector/J Driver

• R-Click MySQL (. )/ Connect Using …

ĐoànThi ệnNgân Bài5 -18/72

MySQL Connector/J Driver

• Host • Port • Database • User Name • Password • Remember password

• Test

Connection

ĐoànThi ệnNgân Bài5 -19/72

MySQL Connector/J Driver

ĐoànThi ệnNgân Bài5 -20/72

MySQL Connector/J Driver • Services/ Databases –New Connection…

ĐoànThi ệnNgân Bài5 -21/72

MySQL Connector/J Driver

• Select MySQL (Connector/J Driver)

ĐoànThi ệnNgân Bài5 -22/72

/ide/modules/ext/

mysql-connector-java-******-bin.jar

Vị tríJDBC Driver với Netbeans 7.0.1 trên Linux

ĐoànThi ệnNgân Bài5 -23/72

Swing Project with JDBC.

• DùngJtablech ứa dữ liệu • DùngJScrollPanech ứaJtablegi ữ

Header

• Dữ liệu lấy từ table Actor trong cơ sở

dữ liệusakila

Chú ý • import javax.swing.JTable; • import

javax.swing.table.DefaultTableModel;

• import java.sql.ResultSetMetaData;

ĐoànThi ệnNgân Bài5 -24/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ànThi ệnNgân Bài5 -25/72

PostGreSQLJDBC • Tương tự như MySQL, chúý khácbi ệtkhai

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ànThi ệnNgân Bài5 -26/72

???

• JDBC vàNetBeans

– JDBC? – JDBC Driver vớiNetBeans? – Viếtth ử chươngtrìnhdùngJDBC – Viếtth ử chươngtrìnhdùngJDBC v ới

Netbeans

– Xem cấutrúc t ổ chức mộtproject trong

Netbeans

– Biên dịch(build) vàch ạyth ử – Chạyth ử từ góijar

ĐoànThi ệnNgân Bài5 -27/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ànThi ệnNgân Bài5 -28/72

Web Server hỗ trợ JSP

• Apache Tomcat • Glassfish • JBoss • jetty • Resin • IBM Websphere • Oracle Weblogic • …

ĐoànThi ệnNgân Bài5 -29/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. Bài5 -30/72

ĐoànThi ệnNgân

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ànThi ệnNgân Bài5 -31/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 webtiertechnologies, 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ànThi ệnNgân Bài5 -32/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 lvalueexpression, 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ànThi ệnNgân Bài5 -33/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ànThi ệnNgân Bài5 -34/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 Servletwas 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ànThi ệnNgân Bài5 -35/72

ĐoànThi ệnNgân Bài5 -36/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ànThi ệnNgân Bài5 -37/72

JSP & JavaBean

ĐoànThi ệnNgân Bài5 -38/72

JSP & MVC

ĐoànThi ệnNgân Bài5 -39/72

JSP Web Application with MySQL

• Glassfish Server • NetBeans • JSP • MySQL Database • MySQL Connector/J JDBC Driver • http://netbeans.org/kb/docs/web/mysql-

webapp.html

ĐoànThi ệnNgân Bài5 -40/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ànThi ệnNgân Bài5 -41/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ànThi ệnNgân Bài5 -42/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 GlassFishserver), and any content in SQL, the language understood by the database server (MySQL).

ĐoànThi ệnNgân Bài5 -43/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), Bài5 -44/72

ĐoànThi ệnNgân

Two-tier scenario.

ĐoànThi ệnNgân Bài5 -45/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ànThi ệnNgân Bài5 -46/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ànThi ệnNgân Bài5 -47/72

Page index.jsp

ĐoànThi ệnNgân Bài5 -48/72

Page response.jsp

ĐoànThi ệnNgân Bài5 -49/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ềuch ỉnhHeading 1 – ChènTable, điềuch ỉnh. – ChènForm (action response.jsp), điềuch ỉnh – ChènDropDownList, điềuch ỉnh. – ChènButton, điềuch ỉnh.

ĐoànThi ệnNgân Bài5 -50/72

Preparing the Web Interface

• Modify response.jsp

– ChènTable nh ư hướng dẫn – TạoCSS n ếu cần – Chạyth ử kiểmtrathi ết kế trangweb

ĐoànThi ệnNgân Bài5 -51/72

Index.jsp

ĐoànThi ệnNgân Bài5 -52/72

Response.jsp

ĐoànThi ệnNgân Bài5 -53/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. Bài5 -54/72

ĐoànThi ệnNgân

Preparing Communication between the Application and Database

• Kiểmtra c ơ sở dữ liệuMyNewDatabase

trongMySQL

• Tạo kết nốiJDBC v ới cơ sở dữ liệu

MyNewDatabasetrongMySQL.

• TạoJDBC Resource v ớiMySQL –New

File / JDBC Resource -JDBC pool • Kiểmtra glassfish-resources.xml • Referencing the data source from the application: web.xml / Add Resource

ĐoànThi ệnNgân Bài5 -55/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ểmtratrongServices/ Servers/

Resources/ JDBC

ĐoànThi ệnNgân Bài5 -56/72

Connection Spool • Services • Servers • GlassFishServer 4 hay 3 • Resources • JDBC • Connection Pools • IfpwafcadPool

ĐoànThi ệnNgân Bài5 -57/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ànThi ệnNgân Bài5 -58/72

Add Resource Reference

• Reference Tab • Add Resource Reference

– Resource Name: jdbc/IFPWAFCAD

ĐoànThi ệnNgân Bài5 -59/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ànThi ệnNgân Bài5 -60/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 sqltag libraries.

ĐoànThi ệnNgân Bài5 -61/72

Adding Dynamic Logic

• Cài đặtmãJSTL:

– Chèn DB Report vàoindex.jsp lấy2

thôngtin subject_idvàname t ừ table subject (Test, Comment Table, Test again)

– Gán dữ liệu tương ứngvàoDropDown

List

– Chèn DB Query vàoresponse.jsp lấy dữ

liệu tương ứng

• Gán dữ liệuvào v ị trí tương ứng

ĐoànThi ệnNgân Bài5 -62/72

DB report

<%@taglibprefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <%@taglibprefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%>

SELECT subject_id, name FROM Subject

ĐoànThi ệnNgân Bài5 -63/72

Test index.jsp

ĐoànThi ệnNgân Bài5 -64/72

Modify DropdownList

• Điềuch ỉnhform trongindex.jsp

name="submit" />

• Comment TABLE • Test again

ĐoànThi ệnNgân Bài5 -65/72

Test again index.asp • Dữ liệu được đưavàoDropdownList

ĐoànThi ệnNgân Bài5 -66/72

To modify response.jsp

• Insert DB Query dialog box

– Variable Name: counselorQuery – Scope: page – Data Source: jdbc/IFPWAFCAD – Query Statement: SELECT * FROM Subject, Counselor WHERE Counselor.counselor_id= Subject.counselor_idfk AND Subject.subject_id= ?

value="${param.subject_id}"/>

ĐoànThi ệnNgân Bài5 -67/72

To modify response.jsp • Use a tag to set a variable that

corresponds to the first record of the resultset

• Add the taglibdirective to the top of the file, so

that the tag is understood.

<%@taglibprefix="c" uri="http://java.sun.com/jsp/jstl/core"%> • In the HTML markup, replace all placeholders with EL statements code that display the data held in the counselorDetailsvariable

ĐoànThi ệnNgân Bài5 -68/72

${counselorDetails.name}

Description: ${counselorDetails.description}
Counselor: /strong> ${counselorDetails.first_name } ${counselorDetails.nick_name} ${counselorDetails.last_name}

ĐoànThi ệnNgân Bài5 -69/72


member since: ${counselorDetails.member_since}

Contact Details:

email:

href="mailto: ${counselorDetails.email}">${counselorDet ails.email}
phone: ${counselorDetails.telephone}

ĐoànThi ệnNgân Bài5 -70/72

Running the Completed Application

ĐoànThi ệnNgân Bài5 -71/72

???

• JSP vàNetBeans – Glassfish Server – JDBC Resource – JDBC Connection pool – Viếtth ử ứng dụngweb v ớiJSP k ết nối cơ

sở dữ liệuMySQL qua Glassfish

– Xem cấutrúc t ổ chức mộtproject web

application trongNetbeans – Biên dịch(build) vàch ạyth ử

ĐoànThi ệnNgân Bài5 -72/72