In this blog, 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.

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

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 ID	First Name	Last Name	Age
100	Zara	Ali	18
101	Mahnaz	Fatma	25
102	Zaid	Khan	30
103	Sumit	Mittal	28

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 ID	First Name	Last Name	Age
100	Zara	Ali	18
101	Mahnaz	Fatma	25
102	Zaid	Khan	30
103	Sumit	Mittal	28
104	Nuha	Ali	2

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 ID	First Name	Last Name	Age
100	Zara	Ali	18
101	Mahnaz	Fatma	25
102	Zaid	Khan	30

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 WHERE 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 ID	First Name	Last Name	Age
100	Zara	Ali	18
101	Mahnaz	Fatma	25
102	Zaid	Ali	30

So, this brings us to the end of blog. This Tecklearn ‘How to Access Database with JSP’ blog helps you with commonly asked questions if you are looking out for a job in Java Programming. If you wish to learn JSP and build a career Java Programming domain, then check out our interactive, Java and JEE Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/java-and-jee-training/

Java and JEE Training

About the Course

Java and JEE Certification Training is designed by professionals as per the industrial requirements and demands. This training encompasses comprehensive knowledge on basic and advanced concepts of core Java & J2EE along with popular frameworks like Hibernate, Spring & SOA. In this course, you will gain expertise in concepts like Java Array, Java OOPs, Java Function, Java Loops, Java Collections, Java Thread, Java Servlet, and Web Services using industry use-cases and this will help you to become a certified Java expert.

Why Should you take Java and JEE Training?

Java developers are in great demand in the job market. With average pay going between $90,000/- to $120,000/- depending on your experience and the employers.
Used by more than 10 Million developers worldwide to develop applications for 15 Billion devices.
Java is one of the most popular programming languages in the software world. Rated #1 in TIOBE Popular programming languages index (15th Consecutive Year)

What you will Learn in this Course?

Introduction to Java

Java Fundamentals
Introduction to Java Basics
Features of Java
Various components of Java language
Benefits of Java over other programming languages
Key Benefits of Java

Installation and IDE’s for Java Programming Language

Installation of Java
Setting up of Eclipse IDE
Components of Java Program
Editors and IDEs used for Java Programming
Writing a Simple Java Program

Data Handling and Functions

Data types, Operations, Compilation process, Class files, Loops, Conditions
Using Loop Constructs
Arrays- Single Dimensional and Multi-Dimensional
Functions
Functions with Arguments

OOPS in Java: Concept of Object Orientation

Object Oriented Programming in Java
Implement classes and objects in Java
Create Class Constructors
Overload Constructors
Inheritance
Inherit Classes and create sub-classes
Implement abstract classes and methods
Use static keyword
Implement Interfaces and use it

Polymorphism, Packages and String Handling

Concept of Static and Run time Polymorphism
Function Overloading
String Handling –String Class
Java Packages

Exception Handling and Multi-Threading

Exception handling
Various Types of Exception Handling
Introduction to multi-threading in Java
Extending the thread class
Synchronizing the thread

File Handling in Java

Input Output Streams
io Package
File Handling in Java

Java Collections

Wrapper Classes and Inner Classes: Integer, Character, Boolean, Float etc
Applet Programs: How to write UI programs with Applet, Java.lang, Java.io, Java.util
Collections: ArrayList, Vector, HashSet, TreeSet, HashMap, HashTable

Java Database Connectivity (JDBC)

Introduction to SQL: Connect, Insert, Update, Delete, Select
Introduction to JDBC and Architecture of JDBC
Insert/Update/Delete/Select Operations using JDBC
Batch Processing Transaction
Management: Commit and Rollback

Java Enterprise Edition – Servlets

Introduction to J2EE
Client Server architecture
URL, Port Number, Request, Response
Need for servlets
Servlet fundamentals
Setting up a web project in Eclipse
Configuring and running the web app with servlets
GET and POST request in web application with demo
Servlet lifecycle
Servlets Continued
Session tracking and filter
Forward and include Servlet request dispatchers

Java Server Pages (JSP)

Fundamentals of Java Server Page
Writing a code using JSP
The architecture of JSP
JSP Continued
JSP elements: Scriptlets, expressions, declaration
JSP standard actions
JSP directives
Introduction to JavaBeans
ServletConfig and ServletContext
Servlet Chaining
Cookies Management
Session Management

Hibernate

Introduction to Hibernate
Introduction to ORM
ORM features
Hibernate as an ORM framework
Hibernate features
Setting up a project with Hibernate framework
Basic APIs needed to do CRUD operations with Hibernate
Hibernate Architecture

POJO (Plain Old Java Object)

POJO (Plain Old Java Object)
Persistent Objects
Lifecycle of Persistent Object

Spring

Introduction to Spring
Spring Fundamentals
Advanced Spring

Got a question for us? Please mention it in the comments section and we will get back to you.

355