[go: up one dir, main page]
Scribd
Upload
25 views7 pages

Cbs Dbms Even Sem

Uploaded by

dhanabal.cse
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
25 views7 pages

Cbs Dbms Even Sem

Uploaded by

dhanabal.cse
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 7

Hindusthan College of Engineering and Technology

An Autonomous Institution, Approved by AICTE, New Delhi, Affiliated to Anna University, Chennai
Accredited by NBA (AERO, AUTO, CIVIL, CSE, ECE, EEE, IT, MECH, MCTS)
Accredited by NAAC ‘A++’ Grade with CGPA of 3.69 out of 4 in Cycle 2
Valley Campus, Coimbatore – 641 032, Tamil Nadu, INDIA
Tel: +91 422 4242424 www.hicet.ac.in

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

CONTENT BEYOND THE SYLLABUS

Name of the Faculty : Dr.S.DHANABAL


Subject code / Name : 22CS4201 and DATABASE MANAGEMENT SYSTEM
Year & Semester : II & IV
Academic Year : 2023-2024 & EVEN
Topic : Java Servlet and JDBC Example | Insert data in MySQL

Course Outcomes:

CO1 Design database using ER model


CO2 Construct SQL Queries using relational algebra and normalize the database
CO3 Construct queries to handle transaction processing and maintain consistency of the
database
CO4 Compare and contrast various indexing strategies and apply the knowledge to tune the
performance of the database.
CO5 Appraise how advanced databases differ from Relational Databases and find a suitable
database for the given requirement

Mapping of Content beyond the Syllabus with the POs and PSOs:

PO & PSO PO PO PO PO PO PO PO PO PO PO PO PO PSO PSO


1 2 3 4 5 6 7 8 9 10 11 12 1 2
CO1 1
CO2 1 1 1 1 1 1 1 1 1 1 1 1 1
CO3
CO4
CO5

3 High level 2 Moderate level 1 Low level


Java Servlet and JDBC Example | Insert data in MySQL
Prerequisites: Servlet, JDBC Connectivity

To start with interfacing Java Servlet Program with JDBC Connection:


1. Proper JDBC Environment should set-up along with database creation.
2. To do so, download the mysql-connector.jar file from the internet,
3. As it is downloaded, move the jar file to the apache-tomcat server folder,
4. Place the file in lib folder present in the apache-tomcat directory.
5. To start with the basic concept of interfacing:
Step 1: Creation of Database and Table in MySQL
As soon as jar file is placed in the folder, create a database and table in
MySQL,
mysql> create database demoprj;
Query OK, 1 row affected (4.10 sec)

mysql> use demoprj


Database changed

mysql> create table demo(id int(10), string varchar(20));


Query OK, 0 rows affected (1.93 sec)

mysql> desc demo;


+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| string | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.40 sec)
 Step 2: Implementation of required Web-pages
Create a form in HTML file, where take all the inputs required to insert
data into the database. Specify the servlet name in it, with the POST
method as security is important aspects in database connectivity.
<!DOCTYPE html>
<html>
<head>
<title>Insert Data</title>
</head>
<body>
<!-- Give Servlet reference to the form as an instances
GET and POST services can be according to the problem statement-->
<form action="./InsertData" method="post">
<p>ID:</p>
<!-- Create an element with mandatory name attribute,
so that data can be transfer to the servlet using getParameter() -->
<input type="text" name="id"/>
<br/>
<p>String:</p>
<input type="text" name="string"/>
<br/><br/><br/>
<input type="submit"/>
</form>
</body>
</html>
 Output:

Submit the data (with validation) as all the required data are inserted.
 Step 3: Creation of Java Servlet program with JDBC Connection
To create a JDBC Connection steps are
1. Import all the packages
2. Register the JDBC Driver
3. Open a connection
4. Execute the query, and retrieve the result
5. Clean up the JDBC Environment
Create a separate class to create a connection of database, as it is a lame process to
writing the same code snippet in all the program. Create a .java file which returns a
Connection object.
import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

// This class can be used to initialize the database connection

public class DatabaseConnection {

protected static Connection initializeDatabase()

throws SQLException, ClassNotFoundException

// Initialize all the information regarding

// Database Connection

String dbDriver = "com.mysql.jdbc.Driver";

String dbURL = "jdbc:mysql:// localhost:3306/";

// Database name to access

String dbName = "demoprj";

String dbUsername = "root";

String dbPassword = "root";

Class.forName(dbDriver);

Connection con = DriverManager.getConnection(dbURL + dbName,

dbUsername,

dbPassword);

return con;
}

 Step 4: To use this class method, create an object in Java Servlet program
Below program shows Servlet Class which create a connection and insert the data in
the demo table,

import java.io.IOException;

import java.io.PrintWriter;

import java.sql.Connection;

import java.sql.PreparedStatement;

import javax.servlet.ServletException;

import javax.servlet.annotation.WebServlet;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

// Import Database Connection Class file

import code.DatabaseConnection;

// Servlet Name

@WebServlet("/InsertData")

public class InsertData extends HttpServlet {

private static final long serialVersionUID = 1L;

protected void doPost(HttpServletRequest request,

HttpServletResponse response)
throws ServletException, IOException

try {

// Initialize the database

Connection con = DatabaseConnection.initializeDatabase();

// Create a SQL query to insert data into demo table

// demo table consists of two columns, so two '?' is used

PreparedStatement st = con

.prepareStatement("insert into demo values(?, ?)");

// For the first parameter,

// get the data using request object

// sets the data to st pointer

st.setInt(1, Integer.valueOf(request.getParameter("id")));

// Same for second parameter

st.setString(2, request.getParameter("string"));

// Execute the insert command using executeUpdate()

// to make changes in database

st.executeUpdate();

// Close all the connections

st.close();
con.close();

// Get a writer pointer

// to display the successful result

PrintWriter out = response.getWriter();

out.println("<html><body><b>Successfully Inserted"

+ "</b></body></html>");

catch (Exception e) {

e.printStackTrace();

 Step 5: Get the data from the HTML file


To get the data from the HTML file, the request object is used which
calls getParameter() Method to fetch the data from the channel. After successful
insertion, the writer object is created to display a success message.
After insertion operation from Servlet, data will be reflected in MySQL Database
Output:

You might also like