Phát triển ứng dụng mã nguồn mở - Bài 5: Java – jdbc java web application javaserver pages

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