JSP Database Connection

⚡ Smart Summary

JSP Database Connection lets a web application store and manage large volumes of data by linking JSP pages to a relational database. This resource explains how to create a MySQL table, insert records, and perform Select, Insert, Update, and Delete operations using JSTL sql tags.

🗄️ Purpose: Databases store gigabytes of data that JSP can create, read, and manage.
🧱 Create Table: Build a guru_test table with emp_id and emp_name using a MySQL client or prompt.
🔌 Connection: The sql:setDataSource tag supplies the JDBC driver, URL, user, and password.
🔁 CRUD Operations: Select, Insert, Update, and Delete are run through JSTL sql tags.
🔑 Parameters: Use sql:param and a primary key, such as emp_id, to target specific records.

Connecting to Database in JSP

The database is used for storing various types of data which are huge and have storing capacity in gigabytes. JSP can connect with such databases to create and manage the records.

In this tutorial, we will learn about how to create a table in the database, and how to create records in these tables through JSP.

Create Table

In MYSQL database, we can create a table in the database with any MYSQL client. Here we are using the PHPMyadmin client, and there we have an option “new” to create a new table using the below screenshot.

In this, we have to provide the table name as guru_test, and we will create two fields, emp_id and emp_name.

Emp_id is having datatype as int
Emp_name is having datatype as varchar

Another option is by using the command prompt and changing to the MYSQL directory:

C:\>
C:\>cd Program Files\MY SQL\bin
C:\>Program Files\MySql\bin>

We can log in to the database as follows:

C:\Program Files\MYSQL\bin>mysql –u gururoot –p
Enter Password: *******
Mysql>

Create table guru_test in the database named as GuruTest as the following on the MYSQL prompt:

Mysql> use GuruTest;
MySql> create table guru_test(
Emp_id int NOT NULL,
Emp_name varchar(11),
           );
Once you execute this you get the following:
Query OK, 0 rows affected(0.10 sec)
MySQl> select * from guru_test;
Query OK, 0 rows affected(0.10 sec)

First the records are inserted using the INSERT query, and then we can use the SELECT query to check whether the table is created or not.

Create Records

After creating a table, we need to create records into the guru_test table using the insert query, which is shown below:

The records entered here are:

1 and guru emp1
2 and guru emp2

MySql>INSERT INTO `couch_tomato_db`.`guru_test` (`emp_id`, `emp_name`) VALUES ('1', 'guru emp1');
Query OK, 1 row affected (0.05 sec)

MySQL>INSERT INTO `couch_tomato_db`.`guru_test` (`emp_id`, `emp_name`) VALUES ('2', 'guru emp2');
Query OK, 1 row affected (0.05 sec)

JSP Operations: Insert, Update, Delete, Select

Using JSP, we can do multiple operations into the database. We can insert the records, and also, we can delete the records which are not required. If any record needs to be edited, then we can do it using an update. The Select operation will help to fetch the records which are required.

Select

The Select operation is used to select the records from the table.

Example:

In this example, we are going to learn about the select operation of fetching records from the guru_test table, which was created in the above section.

<%@ 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"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Guru Database JSP1</title>
</head>
<body>

 <sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost/GuruTest"
     user="gururoot"  password="guru"/>

<sql:query dataSource="${snapshot}" var="result">
SELECT * from guru_test;
</sql:query>

<table>
<tr>
   <th>Guru ID</th>
   <th>Name</th>

</tr>
<c:forEach var="row" items="${result.rows}">
<tr>
   <td><c:out value="${row.emp_id}"/></td>
   <td><c:out value="${row.emp_name}"/></td>

</tr>
</c:forEach>
</table>

</body>
</html>

Explanation of the code:

Code Line 1: Here we are importing the io, util, and SQL libraries of java.

Code Line 3: Here we are importing the core library of JSTL and giving its prefix as c, which will help to get output.

Code Line 4: Here we are importing the SQL library of JSTL and giving its prefix as sql, which will help to do the SQL operations.

Code Line 15-17: Here using sql, we are connecting the data source by naming the variable as “snapshot” and the driver as a JDBC driver. Also adding the username and password as “gururoot” and “guru”.

Code Line 19-21: Here we are using the SQL query of the select query.

Code Line 31-32: We are printing the output for emp id and emp name, which are fetched from the results of the query, and using a foreach loop we print the output.

When you execute the above code, we will get the output as below:

Output:

Here both the records will be fetched from the database:

1 guru emp1
2 guru emp2

Insert

The Insert operator is used to insert the records into the database.

Example:

In this example, we are going to learn about inserting the records in the table guru_test.

<%@ 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="gurucore"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="gurusql"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Guru Database JSP1</title>
</head>
<body>

 <gurusql:setDataSource var="guru" driver="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost/GuruTest"
     user="gururoot"  password="guru"/>

     <gurusql:update dataSource="${guru}" var="guruvar">
INSERT INTO guru_test VALUES (3, 'emp emp3');
</gurusql:update>



</body>
</html>

Explanation of the code:

Code Line 19-20: Here we are inserting records into the table guru_test of the GuruTest database. The records inserted are: empID – 3 and empname – emp emp3. These records will be inserted in the table. When you execute the code, the records are inserted into the table guru_test, with value 3 and emp emp3.

Note: Here we are not showing the output as we are just inserting the record in the table. We can get the record using the select query as ‘select * from guru_test’. If the record was inserted, then we would get the value as 3 and emp3. If the record is not inserted, then 3 will not be seen in records in the table.

Delete

This is the delete operation where we delete the records from the table guru_test.

Example:

Here we will use the delete query to delete the record from the table guru_test. The record which has to be deleted has to be set in the variable “guruid”, and the corresponding record is deleted from the database.

<%@ 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="gurucore"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="gurusql"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Guru Database JSP1</title>
</head>
<body>

 <gurusql:setDataSource var="guru" driver="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost/GuruTest"
     user="gururoot"  password="guru"/>
     <gurucore:set var="guruid" value="3"/>
     <gurusql:update dataSource="${guru}" var="guruvar">
DELETE FROM guru_test WHERE emp_id = ?
  <gurusql:param value="${guruid}" />
</gurusql:update>



</body>
</html>

Explanation of the code:

Code Line 18: We are setting a variable guruid whose value is 3, which has to be deleted from the database. This is always a primary key of the table. In this case, the primary key is the emp_id.

Code Line 19-22: Here we are using a delete query which is setting a parameter in the where clause. Here the parameter is guruid, which is set in code line 18. The corresponding record is deleted.

Output:

When you execute the above code, the record with emp_id as 3 is deleted.

Note: In this example, we cannot show the output as we are deleting the record from the table. To check whether that record is deleted, we need to use the select query “select * from guru_test”. In that case, if we get 3 as emp id, then the delete query has failed; else the record has been deleted successfully.

Update

The update is used to edit the records in the table.

Example:

<%@ 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="gurucore"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="gurusql"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Guru Database JSP1</title>
</head>
<body>

<gurusql:setDataSource var="guru" driver="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost/GuruTest"
     user="gururoot"  password="guru"/>
     <gurucore:set var="guruid" value="2"/>
     <gurusql:update dataSource="${guru}" var="guruvar">
UPDATE guru_test SET emp_name='emp guru99'
  <gurusql:param value="${guruid}" />
</gurusql:update>



</body>
</html>

Explanation of the code:

Code Line 18: Here we are setting a variable guruid as 2. This is the ID where we want to update the record.

Code Line 19-22: Here we are using an update query to update the record in the table guru_test of the record, which is set in point 18. Here emp guru2 is replaced by emp guru99.

Output:

When you execute the above code, the record with emp_id 2 is changed to 99. So, now the output will show emp “guru99” instead of emp “guru2”.

FAQs

Yes. AI assistants can generate JSTL sql tags or JDBC code for connecting JSP to MySQL, build queries, and map result rows. You should still verify the driver, URL, and credentials before running.

Yes. AI tools can flag string-concatenated queries and suggest parameterized queries or prepared statements. This reduces SQL injection risk, though manual security review of all database inputs is still recommended.

JDBC (Java Database Connectivity) is the API that lets Java and JSP applications connect to a database. It loads a driver such as com.mysql.jdbc.Driver and runs SQL statements over the connection.

No. The JSTL sql tags suit learning and prototypes. For production, database logic belongs in servlets, DAOs, or a framework, keeping JSP focused on presentation rather than direct queries.

JSP Database Connection

Connecting to Database in JSP

Create Table

Create Records

JSP Operations: Insert, Update, Delete, Select

FAQs

Summarize this post with:

Sign up for the newsletter

Connecting to Database in JSP

Create Table

RELATED ARTICLES

Create Records

JSP Operations: Insert, Update, Delete, Select

FAQs

Summarize this post with:

Sign up for the newsletter