database connectivity
TRANSCRIPT
![Page 1: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/1.jpg)
Database Accessing from JSP Page
Presented by:
Akanksha Shukla
![Page 2: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/2.jpg)
2
IntroductionJSP pagesMarkupScriptletsExample
Accessing Database From JSP PageSteps to access databaseExampleDescription
References
Outline
![Page 3: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/3.jpg)
3
Java Server Pages (JSP) is a Java technology that helps software developers serve dynamically generated web pages based on HTML, XML, or other document types. JSP pages are loaded in the server and operated from a structured special installed Java server packet called a Java EE Web Application, often packaged as a .war or .ear file archive. JSP allows Java code and certain pre-defined actions to be interleaved with static web markup content, with the resulting page being compiled and executed on the server to deliver an HTML or XML document.
.
Introduction…
![Page 4: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/4.jpg)
4
Introduction…
JSP allows Java code and certain pre-defined actions to be interleaved with static web markup content, with the resulting page being compiled and executed on the server to deliver an HTML or XML document.
JSP syntax is a fluid mix of two basic content forms:
1. Scriptlet Elements 2. Markup
![Page 5: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/5.jpg)
5
Markup is typically standard HTML or XML. When the page is requested, the Java code is executed and its output is added with the surrounding markup to create the final page. Because Java is a compiled language, not a scripting language, JSP pages must be compiled to Java bytecode classes before they can be executed, but such compilation is needed only when a change to the source JSP file has occurred.
Markup
![Page 6: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/6.jpg)
6
Scriptlet elements are delimited blocks of Java code which may be intermixed with the markup. Java code is not required to be complete (self contained) within its scriptlet element block, but can straddle markup content providing the page as a whole is syntactically correct (for example, any Java if/for/while blocks opened in one scriptlet element must be correctly closed in a later element for the page to successfully compile).
Scriptlets
![Page 7: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/7.jpg)
7
Handling of JSP Page
Request
JSP Engine and
Web Server
JSP file ComponentClient
Response
Request Request
Response Response
Fig: Data Passed b/w Client & Server
![Page 8: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/8.jpg)
8
<%-- use the 'taglib' directive to make the JSTL 1.0 core tags available; use the uri "http://java.sun.com/jsp/jstl/core" for JSTL 1.1 --%> <%@ taglib uri="http://java.sun.com/jstl/core" prefix="c" %> <%-- use the 'jsp:useBean' standard action to create the Date object; the object is
set as an attribute in page scope --%> <jsp:useBean id="date" class="java.util.Date" /> <html> <head><title>First JSP</title></head> <body> <h2>Here is today's date</h2> <c:out value="${date}" /> </body> </html>
Example
![Page 9: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/9.jpg)
9
Accessing DB From JSP Page
While accessing JSP database from JSP page, we should have some database package installed. Here we consider the connectivity from MYSQL database with JSP.
Assumptions: TOMCAT web server is installed. MYSQL server is installed. JDK is installed.
![Page 10: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/10.jpg)
10
Various steps are:1. Create a database named “books”, statement is: MYSQL>CREATE DATABASE books ;
2. Create a table named “books_details”, statement is:MYSQL>CREATE TABLE books_details ( `id` INT( 11 ) NOT NULL AUTO_INCREMENT , `book_name` VARCHAR( 100 ) NOT NULL , `author` VARCHAR( 100 ) NOT NULL , PRIMARY KEY ( `id` ) )
Steps to access Database
![Page 11: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/11.jpg)
11
Steps to access Database
The table is:
ID Book Name Author 1. Java I/O Tim Ritchey 2. Java & XML, 2 Edition Brett McLaughlin 3. Java Swing, 2nd Edition Dave Wood, Marc Loy,
![Page 12: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/12.jpg)
12
3. Copy the MYSQL-connector-java-3.1.14-bin.jar to the C:\tomcat_directory\common\lib. Then set the Classpath. For that, we have to perform the following:
Control Panel->System-> System Properties-> Environmental Variables and set Classpath.
Steps to access Database
![Page 13: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/13.jpg)
13
Steps to access Database
4. After setting Classpath, restart the Tomcat Web Server using the startup.bat file at the command promt.
Create a folder DBDemo using following line:
C:\tomcat_directory\jsp-examples\DBDemo
and now write the JSP program which is used to connect the database.
![Page 14: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/14.jpg)
14
Example
<%@ page language="java" import="java.sql.*" %>
<% Connection con=null; ResultSet rst=null;
Statement stmt=null; String driver =
"org.gjt.mm.mysql.Driver"; Class.forName(driver).newInstance();
Classes & interfaces
object instantiation
![Page 15: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/15.jpg)
15
try{
String url= "jdbc:mysql://localhost/books?user=
<user>&password=<password>"; con=DriverManager.getConnection(url);
stmt=con.createStatement();}catch(Exception e)
{System.out.println(e.getMessage());
}
Example
![Page 16: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/16.jpg)
16
Example
if(request.getParameter("action") != null){
String book= request.getParameter("bookname");
String author=request.getParameter("author");stmt.executeUpdate("insert into
books_details(book_name, author) values('"+bookname+"','"+author+"')");
rst=stmt.executeQuery("select * from books_details");
%>
Retrieve fields
![Page 17: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/17.jpg)
17
while(rst.next()){%>
<tr><td><%=no%></td> <td><%=rst.getString(“book_name")%> </td>
<td> <%=rst.getString("author“)%> </td></tr><%no++;
}rst.close();stmt.close();con.close();
%>
Example
![Page 18: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/18.jpg)
18
1. JSP is a kind of scripting language in which we can embed JAVA code along with HTML elements.
2. Above code of JDBC and jsp shows how we can manipulate data in a relational database from JSP page. We need to use the java.sql package: DriverManager, Connection, Statement, and ResultSet. To create a Web application, we need JDBC to use more features such as prepared statements and connection pooling.
Conclusion
![Page 19: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/19.jpg)
19
Figures- http://www.google.co.in “Web Technology” by A.A.Puntambekar http://www.wikipedia.org
References…
![Page 21: DataBase Connectivity](https://reader035.vdocuments.us/reader035/viewer/2022062313/5587116bd8b42a853f8b4652/html5/thumbnails/21.jpg)
21
Thanking to you all......