Do you think IIT Guwahati certified course can help you in your career?
Introduction
A Database is a collection of information stored electronically in a system. While creating a website that requires the user data to function, it becomes crucial that we hold that data. For example, let us consider a grocery store that needs to maintain a log of millions of products. Each day hundreds of products are sold and bought from the inventory. Hence, there has to be a database that can store all the product details. In programming, databases have a significant role as all the valuable information that needs to be available has to be stored inside the database. A banking application's database has information about all registered customers, including their names, email addresses, phone numbers, and account numbers. Upon entering the correct credentials, a user can access the database. This blog discusses how to access a database in JSP.
Installation
This blog uses a MySQL database with JSP. Therefore, it needs to be downloaded in case it’s not installed. You can download MySQL from here Download MySQL Workbench from here The purpose of MySQL workbench is to provide the interface to work with databases more easily and in a more structured way.
Creation of Database
Now that we have MySQL installed let us create a Student table. We need to log in to our MySQL database to create a table. Press Windows + R and type cmd inside the run window. Then type the following commands inside the command prompt.
C:\>
C:\>cd Program Files\MySQL\bin
By this command, we are navigating to the bin directory of MySQL.
C:\Program Files\MySQL\bin>
Now we provide the credentials that we supplied during installation.
C:\Program Files\MySQL\bin>mysql -u root -p
Enter password: ********
mysql>
After logging in, we create an Example database that holds our data table named Student. After typing the given commands, we define the schema for our database. A schema is a type of blueprint that explains what the database is about.
mysql> use EXAMPLE;
mysql> create table Student
(
id int not null,
age int not null,
first varchar (255),
last varchar (255)
);
Query OK, 0 rows affected (0.09 sec)
mysql>
We have successfully created a database that will hold four types of values, id, age, first and last respectively.
Inserting Values in Database
Currently, The Student table holds no information about a student's ID, name, and age. We use the INSERT operation to push data into our database.
Now our table holds information about four different students.
Connecting Database to JSP
We use the JSTL SQL tags to connect our database with our application. These contain parameters that help establish an interaction between our application and database.
This tag imports the SQL Library tags on the JSP page. The SQL Library provides many useful tags such as
<sql:setDataSource>
<sql:query>
<sql:update>
<sql:param>
<sql:transaction>
<sql:setDataSource>
A datasource is required to provide all necessary information about the database. The datasource attributes house all the information that defines the relationship between an application and database, such as the database name, URL, password, and scope. Following attributes are supported -
driver- this attribute tells the driver class to be loaded to interact with the database.
url- URL for the database
user- username of Database
password- password of Database
var- variable to store datasource information
scope- scope in which variable will be saved .Default value is Page.
This tag is used to implement insert, delete or update select queries and saves the result inside a scoped variable. Attributes supported by this tag are-
SQL- to provide the select query.
Datasource- database connection.
Var- variable to store the result of query
scope- scope in which variable will be saved. The default value is Page.
The SELECT * from Student; selects or, in other words, picks up all the data from the Student table and stores it in the variable result. Then using forEach, all the data is printed out as an output. The output table is shown below:
We have already discussed how data is inserted inside the database using the command prompt.However,the insertion can also be done by again using the JSTL SQL tags inside the JSP application.
We used the SQL: Update tag to insert data from our JSP application to our Example database's student table.
Output:
The output table is shown below:
ID
First Name
Last Name
Age
1
Akshit
Sharma
12
2
Shreya
Jain
13
3
Abhishek
Singh
11
4
Anas
Ahmed
12
5
Nisha
Goyal
17
Delete Operation in Database
In case the student leaves the school the database must have the flexibility to delete the entry.This action is performed using the SQL DELETE operation.
<%@ 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 Example</title>
</head>
<body>
<sql:setDataSource var = "snapshot" driver = "com.mysql.jdbc.Driver"
url = "jdbc:mysql://localhost/EXAMPLE"
user = "root" password = "CN123"/>
<c:set var = "student_id" value = "5"/>
<sql:update dataSource = "${snapshot}" var = "count">
DELETE FROM Student WHERE Id = ?
<sql:param value = "${student_id}" />
</sql:update>
<sql:query dataSource = "${snapshot}" var = "result">
SELECT * from Student;
</sql:query>
<table border = "1" width = "100%">
<tr>
<th>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>
To delete the Student with ID=5, we created a variable called student_id that stored the value of the Student's ID. Then, with the help of the SQL: Update tag, deleted the student entry.
Output:
ID
First Name
Last Name
Age
1
Akshit
Sharma
12
2
Shreya
Jain
13
3
Abhishek
Singh
11
4
Anas
Ahmed
12
Update Operation in Database
The age parameter of the student table is bound to change every year and thus it is necessary that our table has the ability to change the entered information instead of deleting and reentering it.
<%@ 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 Example</title>
</head>
<body>
<sql:setDataSource var = "snapshot" driver = "com.mysql.jdbc.Driver"
url = "jdbc:mysql://localhost/EXAMPLE"
user = "root" password = "CN123"/>
<c:set var = "student_id" value = "4"/>
<sql:update dataSource = "${snapshot}" var = "count">
UPDATE Student SET WHERE age= '15'
<sql:param value = "${student_d}" />
</sql:update>
<sql:query dataSource = "${snapshot}" var = "result">
SELECT * from Student;
</sql:query>
<table border = "1" width = "100%">
<tr>
<th>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>
In the above example, we changed the Student's age with ID=4.
Output:
ID
First Name
Last Name
Age
1
Akshit
Sharma
12
2
Shreya
Jain
13
3
Abhishek
Singh
11
4
Anas
Ahmed
15
Frequently Asked Questions
How do you connect to the database from JSP?
A database can be connected to a JSP application with the help of JSTL SQL Tags.
How many Date Types are available in MySQL?
MySQL supports string, numeric, date, and time data types.
How to create a database in MySQL?
To create a database, one must log in to the SQL using the command prompt and then, with the help of the use keyword followed by the name of the database, create a database.
Can MySQL perform CRUD operations inside JSP?
Yes. With the help of JSTL SQL Tags, it is possible to perform all CRUD operations (Create, Read, Update and Delete) inside a JSP Application.
What is the setDataSource tag in JSP?
A Datasource is required to provide all necessary information about the database. The Datasource attributes house all the information that defines the relationship between an application and database, such as the database name, URL, password, and scope.
Conclusion
In this article, we learned about Database Access in JSP and how, with the help of JSTL SQL Tags, perform CRUD operations inside the JSP. However, this isn't enough, as there is always much more to explore and learn about this vast field of Web Development. To know more about JSP and its intricacies, check out the articles on JSP or enroll in our highly curated Web Development course.
26+ registered 
