• Home
  • Testing
  • SAP
  • Web
  • Must Learn!
  • Big Data
  • Live Projects
  • Blog

The database is used for storing various types of data which are huge and has 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.

In this tutorial, you will learn-

Create Table

In MYSQL database, we can create a table in the database with any MYSQL client.

Here we are using PHPMyadminclient, and there we have an option "new" to create a new table using below screenshot.

JSP database connection

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

Emp_idis havingdatatype as int

Emp_nameis havingdatatype as varchar

JSP database connection

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

C:\>

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

C:\>Program Files\MySql\bin>

We can login to database as follows:

C:\Program Files\MYSQL\bin>mysql –u gururoot –p

Enter Password: *******

Mysql>

Create table guru_testin the database named as GuruTestas the following on MYSQL prompt:

Mysql> use GuruTest;
MySql> create table guru_test(
Emp_idint NOT NULL,
Emp_namevarchar(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 INSERT query and then we can use SELECTquery 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 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 using an update. The Selectoperation 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 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 io, uti and SQL libraries of java.

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

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

Code Line 15-17: Here using gurusql, we are connecting data source by naming variable as "guru" and driver as a JDBC driver. Also adding username and password with "gururoot" and "guru".

Code Line 19-21: Here we are using 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 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

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 GuruTestdatabase.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 as 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 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 delete operation where we delete the records from the table guru_test.

Example:

Here we will delete query to delete the record from the table guru_test. The record which has to be deleted has to be set in 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 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 select query "select * from guru_test". In that case, if we get 3 as emp id then 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 withemp_id 2 is changed to 99. So, now the output will show emp"guru99" instead of emp "guru2".

Summary:

In this tutorial, we learnt about connecting JSP to database and database access. Also, we learned about various operations perform on the table in the database like create, delete, update, etc.

 

YOU MIGHT LIKE:
JSP

Debugging in JSP

Debugging is the process to trace the error in the application. It is not very easy to trace bugs...