JSP - Database Access

In this chapter, we will discuss how to access database with JSP. We assume you have good understanding on how JDBC application works. Before starting with database access through a JSP, make sure you have proper JDBC environment setup along with a database.
For more detail on how to access database using JDBC and its environment setup you can go through our JDBC Tutorial.
To start with basic concept, let us create a table and create a few records in that table as follows −

Create Table

To create the Employees table in the EMP database, use the following steps −

Step 1

Open a Command Prompt and change to the installation directory as follows −
C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>

Step 2

Login to the database as follows −
C:\Program Files\MySQL\bin>mysql -u root -p
Enter password: ********
mysql>

Step 3

Create the Employee table in the TEST database as follows − −
mysql> use TEST;
mysql> create table Employees
   (
      id int not null,
      age int not null,
      first varchar (255),
      last varchar (255)
   );
Query OK, 0 rows affected (0.08 sec)
mysql>

Create Data Records

Let us now create a few records in the Employee table as follows − −
mysql> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');
Query OK, 1 row affected (0.05 sec)
 
mysql> INSERT INTO Employees VALUES (101, 25, 'Mahnaz', 'Fatma');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO Employees VALUES (103, 28, 'Sumit', 'Mittal');
Query OK, 1 row affected (0.00 sec)
 
mysql>

SELECT Operation

Following example shows how we can execute the SQL SELECT statement using JTSL in JSP programming −
<%@ page import = "java.io.*,java.util.*,java.sql.*"%>
<%@ page import = "javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix = "c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix = "sql"%>
 
<html>
   <head>
      <title>SELECT Operation</title>
   </head>

   <body>
      <sql:setDataSource var = "snapshot" driver = "com.mysql.jdbc.Driver"
         url = "jdbc:mysql://localhost/TEST"
         user = "root"  password = "pass123"/>
 
      <sql:query dataSource = "${snapshot}" var = "result">
         SELECT * from Employees;
      </sql:query>
 
      <table border = "1" width = "100%">
         <tr>
            <th>Emp ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Age</th>
         </tr>
         
         <c:forEach var = "row" items = "${result.rows}">
            <tr>
               <td><c:out value = "${row.id}"/></td>
               <td><c:out value = "${row.first}"/></td>
               <td><c:out value = "${row.last}"/></td>
               <td><c:out value = "${row.age}"/></td>
            </tr>
         </c:forEach>
      </table>
 
   </body>
</html>
Access the above JSP, the following result will be displayed −


Emp IDFirst NameLast NameAge


100ZaraAli18


101MahnazFatma25


102ZaidKhan30


103SumitMittal28

INSERT Operation

Following example shows how we can execute the SQL INSERT statement using JTSL in JSP programming −
<%@ page import = "java.io.*,java.util.*,java.sql.*"%>
<%@ page import = "javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix = "c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix = "sql"%>
 
<html>
   <head>
      <title>JINSERT Operation</title>
   </head>
   
   <body>
      <sql:setDataSource var = "snapshot" driver = "com.mysql.jdbc.Driver"
         url = "jdbc:mysql://localhost/TEST"
         user = "root"  password = "pass123"/>
         <sql:update dataSource = "${snapshot}" var = "result">
         INSERT INTO Employees VALUES (104, 2, 'Nuha', 'Ali');
      </sql:update>
 
      <sql:query dataSource = "${snapshot}" var = "result">
         SELECT * from Employees;
      </sql:query>
 
      <table border = "1" width = "100%">
         <tr>
            <th>Emp ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Age</th>
         </tr>
         
         <c:forEach var = "row" items = "${result.rows}">
            <tr>
               <td><c:out value = "${row.id}"/></td>
               <td><c:out value = "${row.first}"/></td>
               <td><c:out value = "${row.last}"/></td>
               <td><c:out value = "${row.age}"/></td>
            </tr>
         </c:forEach>
      </table>
 
   </body>
</html>
Access the above JSP, the following result will be displayed −


Emp IDFirst NameLast NameAge


100ZaraAli18


101MahnazFatma25


102ZaidKhan30


103SumitMittal28


104NuhaAli2

DELETE Operation

Following example shows how we can execute the SQL DELETE statement using JTSL in JSP programming −
<%@ page import = "java.io.*,java.util.*,java.sql.*"%>
<%@ page import = "javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix = "c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix = "sql"%>
 
<html>
   <head>
      <title>DELETE Operation</title>
   </head>
   
   <body>
      <sql:setDataSource var = "snapshot" driver = "com.mysql.jdbc.Driver"
         url = "jdbc:mysql://localhost/TEST"
         user = "root" password = "pass123"/>
 
      <c:set var = "empId" value = "103"/>
 
      <sql:update dataSource = "${snapshot}" var = "count">
         DELETE FROM Employees WHERE Id = ?
         <sql:param value = "${empId}" />
      </sql:update>
 
      <sql:query dataSource = "${snapshot}" var = "result">
         SELECT * from Employees;
      </sql:query>
 
      <table border = "1" width = "100%">
         <tr>
            <th>Emp ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Age</th>
         </tr>
            
         <c:forEach var = "row" items = "${result.rows}">
            <tr>
               <td><c:out value = "${row.id}"/></td>
               <td><c:out value = "${row.first}"/></td>
               <td><c:out value = "${row.last}"/></td>
               <td><c:out value = "${row.age}"/></td>
            </tr>
         </c:forEach>
      </table>
 
   </body>
</html>
Access the above JSP, the following result will be displayed −


Emp IDFirst NameLast NameAge


100ZaraAli18


101MahnazFatma25


102ZaidKhan30

UPDATE Operation

Following example shows how we can execute the SQL UPDATE statement using JTSL in JSP programming −
<%@ page import = "java.io.*,java.util.*,java.sql.*"%>
<%@ page import = "javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri = "http://java.sun.com/jsp/jstl/core" prefix = "c"%>
<%@ taglib uri = "http://java.sun.com/jsp/jstl/sql" prefix = "sql"%>
 
<html>
   <head>
      <title>DELETE Operation</title>
   </head>
   
   <body>
      <sql:setDataSource var = "snapshot" driver = "com.mysql.jdbc.Driver"
         url = "jdbc:mysql://localhost/TEST"
         user = "root" password = "pass123"/>
 
      <c:set var = "empId" value = "102"/>
 
      <sql:update dataSource = "${snapshot}" var = "count">
         UPDATE Employees SET last = 'Ali'
         <sql:param value = "${empId}" />
      </sql:update>
 
      <sql:query dataSource = "${snapshot}" var = "result">
         SELECT * from Employees;
      </sql:query>
 
      <table border = "1" width = "100%">
         <tr>
            <th>Emp ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Age</th>
         </tr>
            
         <c:forEach var = "row" items = "${result.rows}">
            <tr>
               <td><c:out value = "${row.id}"/></td>
               <td><c:out value = "${row.first}"/></td>
               <td><c:out value = "${row.last}"/></td>
               <td><c:out value = "${row.age}"/></td>
            </tr>
         </c:forEach>
      </table>
 
   </body>
</html>
Access the above JSP, the following result will be displayed −


Emp IDFirst NameLast NameAge


100ZaraAli18


101MahnazFatma25


102ZaidAli30

Comments

Post a Comment

Popular posts from this blog

JSP - Http Status Codes

In this chapter, we will discuss the Http Status Codes in JSP. The format of the HTTP request and the HTTP response messages are similar and will have the following structure − An initial status line + CRLF (Carriage Return + Line Feed ie. New Line) Zero or more header lines + CRLF A blank line ie. a CRLF An optional message body like file, query data or query output. For example, a server response header looks like the following − HTTP / 1.1 200 OK Content - Type : text / html Header2 : ... ... HeaderN : ... ( Blank Line ) <! doctype ...> <html> <head> ...</ head > <body> ... </ body > </ html > The status line consists of the  HTTP version (HTTP/1.1 in the example) , a status code (200 in the example), and a very short message corresponding to the status code  (OK in the example) . Following table lists out the HTTP status codes and associated messages that might be returned from t
Read more

About JSP(Java Server Pages)

What is JSP(Java Server Pages) Java Server Pages (JSP) is a server-side programming technology that enables the creation of dynamic, platform-independent method for building Web-based applications. JSP have access to the entire family of Java APIs, including the JDBC API to access enterprise databases. This tutorial will teach you how to use Java Server Pages to develop your web applications in simple and easy steps.                                                                     Audience For These tutorial has been prepared for the beginners to help them understand basic functionality of Java Server Pages (JSP) to develop your web applications. After completing this tutorial you will find yourself at a moderate level of expertise in using JSP from where you can take yourself to next levels. Prerequisites We assume you have little knowledge of how web applications work over HTTP, what is web server and what is web browsers. It will be great if you have
Read more

JSP - Filters

In this chapter, we will discuss Filters in JSP. Servlet and JSP Filters are Java classes that can be used in Servlet and JSP Programming for the following purposes − To intercept requests from a client before they access a resource at back end. To manipulate responses from server before they are sent back to the client. There are various types of filters suggested by the specifications − Authentication Filters Data compression Filters Encryption Filters Filters that trigger resource access events Image Conversion Filters Logging and Auditing Filters MIME-TYPE Chain Filters Tokenizing Filters XSL/T Filters That Transform XML Content Filters are deployed in the deployment descriptor file  web.xml  and then map to either servlet or JSP names or URL patterns in your application's deployment descriptor. The deployment descriptor file web.xml can be found in  <Tomcat-installation-directory>\conf  directory. When the JSP container starts up your web appli
Read more

JSP - Security

JavaServer Pages and servlets make several mechanisms available to Web developers to secure applications. Resources are protected declaratively by identifying them in the application deployment descriptor and assigning a role to them. Several levels of authentication are available, ranging from basic authentication using identifiers and passwords to sophisticated authentication using certificates. Role Based Authentication The authentication mechanism in the servlet specification uses a technique called  role-based security . The idea is that rather than restricting resources at the user level, you create roles and restrict the resources by role. You can define different roles in file  tomcat-users.xml , which is located off Tomcat's home directory in conf. An example of this file is shown below − <? xml version = '1.0' encoding = 'utf-8' ?> <tomcat-users> <role rolename = "tomcat" /> <role rolename = "
Read more

JSP - Standard Tag Library (JSTL) Tutorial

In this chapter, we will understand the different tags in JSP. The JavaServer Pages Standard Tag Library (JSTL) is a collection of useful JSP tags which encapsulates the core functionality common to many JSP applications. JSTL has support for common, structural tasks such as iteration and conditionals, tags for manipulating XML documents, internationalization tags, and SQL tags. It also provides a framework for integrating the existing custom tags with the JSTL tags. Install JSTL Library To begin working with JSP tages you need to first install the JSTL library. If you are using the Apache Tomcat container, then follow these two steps − Step 1  − Download the binary distribution from  Apache Standard Taglib and unpack the compressed file. Step 2  − To use the Standard Taglib from its  Jakarta Taglibs distribution , simply copy the JAR files in the distribution's 'lib' directory to your application's  webapps\ROOT\WEB-INF\lib  directory. To use any of the l
Read more

JSP - Internationalization| i18n| l10n

In this chapter, we will discuss the concept of Internationalization in JSP. Before we proceed, let us understand the following three important terms − Internationalization (i18n)  − This means enabling a website to provide different versions of content translated into the visitor's language or nationality. Localization (l10n)  − This means adding resources to a website to adapt it to a particular geographical or cultural region for example Hindi translation to a web site. locale  − This is a particular cultural or geographical region. It is usually referred to as a language symbol followed by a country symbol which are separated by an underscore. For example, " en_US " represents english locale for US. There are a number of items which should be taken care of while building up a global Website. This tutorial will not give you complete detail on this but it will give you a good example on how you can offer your Webpage in different languages to the internet
Read more

JSP - Session Tracking

In this chapter, we will discuss session tracking in JSP. HTTP is a "stateless" protocol which means each time a client retrieves a Webpage, the client opens a separate connection to the Web server and the server automatically does not keep any record of previous client request. Maintaining Session Between Web Client And Server Let us now discuss a few options to maintain the session between the Web Client and the Web Server − Cookies A webserver can assign a unique session ID as a cookie to each web client and for subsequent requests from the client they can be recognized using the received cookie. This may not be an effective way as the browser at times does not support a cookie. It is not recommended to use this procedure to maintain the sessions. Hidden Form Fields A web server can send a hidden HTML form field along with a unique session ID as follows − <input type = "hidden" name = "sessionid" value = "12345"> This
Read more

JSP - Handling Date

In this chapter, we will discuss how to handle data in JSP. One of the most important advantages of using JSP is that you can use all the methods available in core Java. We will take you through the  Date  class which is available in the  java.util  package; this class encapsulates the current date and time. The Date class supports two constructors. The first constructor initializes the object with the current date and time. Date( ) The following constructor accepts one argument that equals the number of milliseconds that have elapsed since midnight, January 1, 1970. Date(long millisec) Once you have a Date object available, you can call any of the following support methods to play with dates − S.No. Methods & Description 1 boolean after(Date date) Returns true if the invoking Date object contains a date that is later than the one specified by date, otherwise, it returns false. 2 boolean before(Date date) Returns true if the invoking Date object contains a date
Read more

JSP - XML Data

When you send the XML data via HTTP, it makes sense to use JSP to handle incoming and outgoing XML documents; for example, RSS documents. As an XML document is merely a bunch of text, creating one through a JSP is much easier than creating an HTML document. Sending XML from a JSP You can send the XML content using JSPs the same way you send HTML. The only difference is that you must set the content type of your page to text/xml. To set the content type, use the  <%@page%>  tag, like this − <%@ page contentType = "text/xml" %> Following example will show how to send XML content to the browser − <%@ page contentType = "text/xml" %> <books> <book> <name> Padam History </name> <author> ZARA </author> <price> 100 </price> </book> </books> Access the above XML using different browsers to see the document tree presentation of the above XML. Processi
Read more

JSP - Database Access

In this chapter, we will discuss how to access database with JSP. We assume you have good understanding on how JDBC application works. Before starting with database access through a JSP, make sure you have proper JDBC environment setup along with a database. For more detail on how to access database using JDBC and its environment setup you can go through our  JDBC Tutorial . To start with basic concept, let us create a table and create a few records in that table as follows − Create Table To create the  Employees  table in the EMP database, use the following steps − Step 1 Open a  Command Prompt  and change to the installation directory as follows − C : \> C : \>cd Program Files \MySQL\b in C : \Program Files \MySQL\b in > Step 2 Login to the database as follows − C : \Program Files \MySQL\b in > mysql - u root - p Enter password : ******** mysql > Step 3 Create the  Employee  table in the  TEST  database as follows − − mysql > use TEST
Read more