How to Insert and Fetch data from MYSQL using JSP

How to Insert and Fetch data from MYSQL using JSP. In this JSP article, we will see How to Insert and Fetch data from MYSQL using JSP.

Insert and Fetch Data in MYSQL Database

Here, we will create a form that will add books to the MYSQL database using JSP(JavaServer Pages). Also, we will create a table where we will fetch data from the MYSQL database on a JSP page.

Create Database and table in MYSQL workbench

First, let us create a database “demo1234″ and table using MYSQL Workbench.

CREATE TABLE `demo1234`.`booktable` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `bookname` VARCHAR(45) NULL,
  `authorname` VARCHAR(45) NULL,
  `language` VARCHAR(45) NULL,
  `isbn` BIGINT NULL,
  `genre` VARCHAR(45) NULL,
  `pdate` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

Create Maven Project

Create a Maven project in Eclipse IDE. Following are steps to create a simple JSP and Servlet Maven project in Eclipse.

Open Eclipse.
Go to File > New > Other > Maven project > Next > Next > select maven.archetype-webapp > Next Enter Artefact Id > Finish.

I have created a folder structure as shown below to follow the basic coding practice in the Eclipse IDE.

Add MYSQL Maven Dependencies into pom.xml

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>

Create a Modal Class

Now, we will create a Model class called book.java.

package in.codebun.bean;

public class Book {

 private int id;
 private String bookName;
 private String authorName;
 private String bookLanguage;
 private long isbn;
 private String genre;
 private String publishingDate;
 
 
 public Book() {
  
 }
 public Book(String bookName, String authorName, String bookLanguage, long isbn, String genre,
   String publishingDate) {
  super();
  this.bookName = bookName;
  this.authorName = authorName;
  this.bookLanguage = bookLanguage;
  this.isbn = isbn;
  this.genre = genre;
  this.publishingDate = publishingDate;
 }
 public int getId() {
  return id;
 }
 public void setId(int id) {
  this.id = id;
 }
 public String getBookName() {
  return bookName;
 }
 public void setBookName(String bookName) {
  this.bookName = bookName;
 }
 public String getAuthorName() {
  return authorName;
 }
 public void setAuthorName(String authorName) {
  this.authorName = authorName;
 }
 public String getBookLanguage() {
  return bookLanguage;
 }
 public void setBookLanguage(String bookLanguage) {
  this.bookLanguage = bookLanguage;
 }
 public long getIsbn() {
  return isbn;
 }
 public void setIsbn(long isbn) {
  this.isbn = isbn;
 }
 public String getGenre() {
  return genre;
 }
 public void setGenre(String genre) {
  this.genre = genre;
 }
 public String getPublishingDate() {
  return publishingDate;
 }
 public void setPublishingDate(String publishingDate) {
  this.publishingDate = publishingDate;
 }
 
}

Create Database Connection

DBConnect.java is the database connection class of this application that contains the connection with MYSQL. Read more about Database connectivity in java using JDBC.

package in.codebun.dbconnect;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnect {
 public static Connection getConn() {
    System.out.println("inside conn");
    Connection con = null;
    String loadDriver = "com.mysql.cj.jdbc.Driver"; // driver name for mysql
    String dbURL = "jdbc:mysql://localhost:3306/demo1234"; // url of the
                 // database
    String dbUSERNAME = "root"; // username to coonect db
    String dbPASSWORD = "khan"; // password to connect db
    try {
     Class.forName(loadDriver); // load the driver
     con = DriverManager.getConnection(dbURL, dbUSERNAME, dbPASSWORD);// get
                      // the
                      // connection
    } catch (ClassNotFoundException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    } catch (SQLException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    }
    return con; // return the connection obj.
}
}

Now we will create a Dao class (Data Access Object). It involves the business logic for database operations like insert, fetch(basically the CRUD operation of the database).

package in.codebun.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import in.codebun.bean.Book;
import in.codebun.dbconnect.DBConnect;

public class BookDAO {

 public static int insert(Book book) throws SQLException{
  Connection conn = DBConnect.getConn();
  String sql = "INSERT INTO BOOKTABLE (BOOKNAME,AUTHORNAME,LANGUAGE,ISBN,GENRE,PDATE) VALUES (?,?,?,?,?,?)";
  PreparedStatement ps = conn.prepareStatement(sql);
  ps.setString(1,book.getBookName());
  ps.setString(2, book.getAuthorName());
  ps.setString(3, book.getBookLanguage());
  ps.setLong(4, book.getIsbn());
  ps.setString(5, book.getGenre());
  ps.setString(6, book.getPublishingDate());
  int i = ps.executeUpdate();
  return i;
 }
 
 public List<Book> fetch() throws SQLException{
  Connection conn = DBConnect.getConn();
  ArrayList<Book> list = new ArrayList<>();
  String sql = "SELECT * FROM BOOKTABLE";
  PreparedStatement ps = conn.prepareStatement(sql);
  ResultSet rs = ps.executeQuery();
  while(rs.next()){
   Book book = new Book();
   book.setId(rs.getInt(1));
   book.setBookName(rs.getString(2));
   book.setAuthorName(rs.getString(3));
   book.setBookLanguage(rs.getString(4));
   book.setIsbn(rs.getLong(5));
   book.setGenre(rs.getString(6));
   book.setPublishingDate(rs.getString(7));
   list.add(book);
   
  }
  return list;
 }
}

Create View

Next, create an index.jsp file inside the src/main/webapp folder.

<html>
<head>
<title></title>
<!-- CSS only -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
<!-- JavaScript Bundle with Popper -->
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-ka7Sk0Gln4gmtz2MlQnikT1wXgYsOg+OMhuP+IlRH9sENBO0LRn5q+8nbTov4+1p" crossorigin="anonymous"></script>
<link rel="preconnect" href="https://fonts.googleapis.com">
<link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
<link href="https://fonts.googleapis.com/css2?family=Roboto+Slab&display=swap" rel="stylesheet">
<style type="text/css">
.container{
border: 2px solid #fff; 
background: #fff;
border-radius:5px;
width: 50%;
margin-bottom: 20px;
}
h1,label{
font-family: 'Roboto Slab', serif;
}
a{
font-size: 20px;
text-decoration: none;
color: #5f2c82;
padding: 10px 10px ;
background-color:#fff;
border-radius:5px;
font-size: larger;
}
button[type=submit] {
background-color: #49a09d;
border-radius:5px;
font-size: larger;
padding: 5px 5px;
color: #fff;
}
</style>
</head>
<body style="background: linear-gradient(#5f2c82,#49a09d)">
<h1 align="center" class="mt-5" style="color: white">Add Book Data</h1>
<center><a href="viewbooks.jsp">View Books</a></center>
<div class="container mt-5">
 
 <form action="processingform.jsp" method="post" >
 <span style="color: #000; font-style: inherit; font-weight: bolder;"><%=(request.getAttribute("success") == null) ? "" : request.getAttribute("success")%></span>
 <div class="mb-3">
  	<label for="exampleFormControlInput1" class="form-label">Book Name</label>
  	<input type="text" class="form-control" id="exampleFormControlInput1" placeholder="Enter Book Name" name="bookName">
 </div>
 <div class="mb-3">
  	<label for="exampleFormControlInput1" class="form-label">Author Name</label>
  	<input type="text" class="form-control" id="exampleFormControlInput1" placeholder="Enter Author Name" name="authorName">
 </div>
 <div class="mb-3">
  	<label for="exampleFormControlInput1" class="form-label">Book Language</label>
  	<select class="form-select" aria-label="Default select example" name="bookLanguage">
  	<option selected>--Select--</option>
  	<option value="English" >English</option>
  	<option value="Hindi">Hindi</option>
  	<option value="Korean">Korean</option>
 </select>
 </div>
 <div class="mb-3">
  	<label for="exampleFormControlInput1" class="form-label">ISBN</label>
  	<input type="text" class="form-control" id="exampleFormControlInput1" placeholder="Enter ISBN" name="isbn">
 </div>
 <div class="mb-3">
 <label for="exampleFormControlInput1" class="form-label">Genre</label>
  	 <input class="form-check-input" type="radio" value="Non-fiction" name="genre" id="flexRadioDefault1">
  <label class="form-check-label" for="flexRadioDefault1">
    Non-fiction
  </label>
  <input class="form-check-input" type="radio" value="Novel" name="genre" id="flexRadioDefault1">
  <label class="form-check-label" for="flexRadioDefault1">
    Novel
  </label>
  <input class="form-check-input" type="radio" value="Fiction" name="genre" id="flexRadioDefault1">
  <label class="form-check-label" for="flexRadioDefault1">
    Fiction
  </label>
 </div>
 <div class="mb-3">
  	<label for="exampleFormControlInput1" class="form-label">Published Date</label>
  	<input type="date" class="form-control" id="exampleFormControlInput1" placeholder="Enter ISBN" name="pdate">
 </div>
 <div class="mb-3 text-center">
 <button type="submit" >Add Book</button>
 </div>
 
 </form>
</div>
</body>
</html>

Next, after submission of the form, form processing will be done on processingform.jsp page.

<%@page import="in.codebun.dao.BookDAO"%>
<%@page import="in.codebun.bean.Book"%>
<%@ 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>Insert title here</title>
</head>
<body>
<%
String bookName = request.getParameter("bookName");
String authorName = request.getParameter("authorName");
String bookLanguage = request.getParameter("bookLanguage");
Long isbn = Long.parseLong(request.getParameter("isbn"));
String genre = request.getParameter("genre");
String pdate =  request.getParameter("pdate");

Book book = new Book(bookName,authorName,bookLanguage,isbn,genre,pdate); 
BookDAO bookDAO = new BookDAO();
int i = bookDAO.insert(book);
if(i > 0){
 request.setAttribute("success","Book Added Successfully");
 request.getRequestDispatcher("index.jsp").forward(request, response);
}
%>
</body>
</html>

Inserting into Database in JSP

Here, first, we receive all the parameters from the form with POST request.
Next, we initialize the data to the Book Object. Then, create the Object of BookDAO and pass the book object to the insert(Book book) method.
Now, at the Data Access Layer, we set the parameters and save them to the database.

Now, we will create a viewbooks.jsp page that would fetch the inserted book from the database. Here, we have to use the object of Iterator to iterate over the book object.

<%@page import="in.codebun.bean.Book"%>
<%@page import="java.util.Iterator"%>
<%@page import="java.util.List"%>
<%@page import="in.codebun.dao.BookDAO"%>
<%@page import="in.codebun.dbconnect.DBConnect"%>
<%@page import="java.sql.Connection"%>
<html>
<head>
<title></title>
<!-- CSS only -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
<!-- JavaScript Bundle with Popper -->
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-ka7Sk0Gln4gmtz2MlQnikT1wXgYsOg+OMhuP+IlRH9sENBO0LRn5q+8nbTov4+1p" crossorigin="anonymous"></script>
<link rel="preconnect" href="https://fonts.googleapis.com">
<link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
<link href="https://fonts.googleapis.com/css2?family=Roboto+Slab&display=swap" rel="stylesheet">
<style type="text/css">
.container{
border: 2px solid #fff; 
background: #fff;
border-radius:5px;
width: 100%;
margin-bottom: 20px;
}
h1,label{
font-family: 'Roboto Slab', serif;
}
a{
font-size: 20px;
text-decoration: none;
color: #5f2c82;
padding: 10px 10px ;
background-color:#fff;
border-radius:5px;
font-size: larger;
}
button[type=submit] {
background-color: #49a09d;
border-radius:5px;
font-size: larger;
padding: 5px 5px;
color: #fff;
}
thead{
background-color: #49a09d;
color: #fff;
font-family: 'Roboto Slab', serif;
}
</style>
</head>
<body style="background: linear-gradient(#5f2c82,#49a09d)">
<h1 align="center" class="mt-5" style="color: white">View Book Data</h1>
<center><a href="index.jsp">Add Books</a></center>
<div class="container mt-5">
 <table class="table table-bordered mt-2" >
   <thead>
    <tr>
      <th scope="col">#</th>
      <th scope="col">Book Name</th>
      <th scope="col">Book Author</th>
      <th scope="col">Language</th>
      <th scope="col">ISBN</th>
      <th scope="col">Genre</th>
      <th scope="col">Published Date</th>
    </tr>
  </thead>
  <tbody>
  <%
  Connection conn = DBConnect.getConn();
  BookDAO bookDAO = new BookDAO();
  List list = bookDAO.fetch();
  Iterator iterator = list.iterator();
  while(iterator.hasNext()){
   Book book = (Book)iterator.next();
 
  %>
   	<tr>
   	 <td><%=book.getId() %></td>
     <td><%=book.getBookName() %></td>
     <td><%=book.getAuthorName() %></td>
     <td><%=book.getBookLanguage() %></td>
     <td><%=book.getIsbn() %></td>
     <td><%=book.getGenre() %></td>
     <td><%=book.getPublishingDate()%></td>
    </tr>
  <%}%>
   
  </tbody>
 </table>
 
</div>
</body>
</html>

Fetching Data from the Database

Here, first, we set the connection with the database.
Now, we will call the fetch() method of BookDAO class that would return a list of Books from the table.
Next, we will iterate over it and print the data on the web page.

Run the Application

Now, run the application on the server, add the book information and hence on the view page, we can see the added information.

Thus, in this way, we insert and fetch data into MYSQL Database using JSP(JavaServer Pages).

CODEBUN

How to Insert and Fetch data from MYSQL using JSP

Insert and Fetch Data in MYSQL Database

Create Database and table in MYSQL workbench

Create Maven Project

Add MYSQL Maven Dependencies into pom.xml

Create a Modal Class

Create Database Connection

Create View

Inserting into Database in JSP

Fetching Data from the Database

Run the Application

Recent Post

How to Explain Your Automation Testing Project in an Interview

Automation Testing Interview Questions for Wait in Selenium C Sharp

Space Booking Project in java using JSP and Servlet with source code

Design Test Automation Framework using Java, Selenium, TestNG, POM

Bug Reporting Project using Spring Boot, Hibernate, JPA and Mysql

Cannot find the declaration of element ‘project’ in Pom.xml

Failed to configure a DataSource: ‘url’ attribute is not specified

Solve “Non-resolvable parent POM” Error in pom.xml with Spring Boot 3

JSP and Servlet Project Configuration Tutorial

CODEBUN

Automation

Programs

Automation