Insert single form data into multiple MYSQL tables using JSP and Servlet. In this JSP and Servlet Tutorial, Let’s create a simple Java web application to insert data of one HTML form into multiple MYSQL database tables let’s understand the problem statement in detail.
Problem Statement
Let’s assume, you have two MYSQL tables named “Student” and “Education” and registration from that contains all the fields of student registration. When a student registers into the application the form data should be divided into two parts. Student personal info and Student’s education info and also it will store in two different tables.
We are using JDBC to connect Java with MYSQL. let’s check out the JDBC tutorial to understand the below example in more detail.
Insert data from a single page into multiple tables
- Create a database with the name “mydb” and a “student” table with column name Id, Name, Surname, and email.
- Another table with the name “education” with column name Id, college, class, and subject.
- Create an HTML form into “index.jps” to register a student with the input fields of name, surname, email, college, class, and subject.
- Create a servlet “InsertData.java” to create a database connection and handle the request and response.
Configure your MYSQL database:
Create a new database with the name “mydb” and table1 with the name “student”
CREATE TABLE `mydb`.`student` ( `sid` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) NULL, `surname` VARCHAR(45) NULL, `email` VARCHAR(45) NULL, PRIMARY KEY (`sid`));
Create table2 with the name “education”
CREATE TABLE `mydb`.`education` ( `eid` INT NOT NULL AUTO_INCREMENT, `college` VARCHAR(45) NULL, `sclass` VARCHAR(45) NULL, `subject` VARCHAR(45) NULL, PRIMARY KEY (`eid`));
index.jsp
<%@ 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>
<form action="InsertData" method="post">
<pre>
Name : <input type="text" name="name">
Surname : <input type="text" name="surname">
Email : <input type="text" name="email">
College : <input type="text" name="college">
Class : <input type="text" name="sclass">
Subject : <input type="text" name="subject">
<input type="submit" value="Submit">
</pre>
</form>
</body>
</html>
InsertData.java (Servlet)
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class InsertData
*/
@WebServlet("/InsertData")
public class InsertData extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public InsertData() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.getWriter().append("Served at: ").append(request.getContextPath());
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
PrintWriter out = response.getWriter();
String name = request.getParameter("name");
String surname = request.getParameter("surname");
String email = request.getParameter("email");
String college = request.getParameter("college");
String sclass = request.getParameter("sclass");
String subject = request.getParameter("subject");
//database Connectivity code.........
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","root");
Statement stmt = con.createStatement();
stmt.executeUpdate("insert into student (name,surname,email)values('"+name+"','"+surname+"','"+email+"') ");
stmt.executeUpdate("insert into education (college,sclass,subject)values('"+college+"','"+sclass+"','"+subject+"') ");
out.println("Data is inserted successfully in both tables");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Insert data from a single page into multiple tables(Video Tutorial)
Let’s perform a real-time example to insert data from a single page into multiple tables and MySQL.
Used tool and Technologies in this Java web example
Database: MySQL workbench.
Language: HTML or JSP,servlet.
Server: Glassfish or Tomcat.
Practise Task in JSP
- Inserts a date in the database using date picker, JSP
- Insert Data of multiple pages in a single table.
- Insert data from the single page into multiple tables.
- login and logout with JSP and servlet with Mysql
- JSP page directives Jsp, Include Directive, JSP Taglib directive
- Include Directive in JSP
- Implicit Objects in JSP with example
- How to count the number of visitors for the website in JSP
- Java database connectivity with MYSQL 8.0
