Free Data Structures and Algorithms Course









Subscribe below and get all best seller courses for free !!!










OR



Subscribe to all free courses

How to perform update sql query in JSP and Servlets using JDBC ?




A simple web application showing how to query a table in a database i.e performing a update query using JDBC

Steps for querying a Database using JDBC -

1. Download the driver jar from Internet and place it in WEB-INF/lib folder of your Web application. Here I am using MySql as Database so I have downloaded the jar with name as : "mysql-connector-java-5.1.20-bin.jar"

2. Create a Database (am using MySql as backend) name as "javaee".

3. Create a table in "javaee" database as "customer". Run the create query stated below -

 CREATE TABLE customer (
   'First Name' varchar(30) ,
   'Last Name' varchar(30) ,
   'E-Mail' varchar(45) ,
   'City' varchar(30) ,
   'password' varchar(30) NOT NULL,
   PRIMARY KEY ('password')
 ) 



4. Populate the table user with some data (of your own), if not run the four insert queries stated below -

insert into customer () values ('Jonty','Magic','jonty@magic.com','Pune','123456');

insert into customer () values ('Java','Sun','java@sun.com','New York','456');

insert into customer () values ('Jesse','lool','jesse@lool.com','Jamaica','23456');

insert into customer () values ('Cameroon','Black','cameroon@black.com','Sydney','34');



5. Create a Servlet. It will perform necessary update query. The code for servlet is shown below -


package com.hubberspot.jdbc;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/UpdateJdbcQueryServlet")
public class UpdateJdbcQueryServlet extends HttpServlet {

 Connection connection = null;
 ResultSet resultSet = null;
 Statement statement = null;
 static String query = null;
 String url = null;
 String username = null;
 String password = null;

 public void init(ServletConfig config) throws ServletException {

  url = "jdbc:mysql://localhost:3306/javaee";
  username = "root"; 
  password = "root"; 
  try {
   Class.forName("com.mysql.jdbc.Driver").newInstance();

   connection = DriverManager.getConnection(url, username , password);
  }
  catch (Exception e) {

   e.printStackTrace();
  }

 }


 protected void doGet(
   HttpServletRequest request, 
   HttpServletResponse response
   ) throws ServletException, IOException {

  doPost(request, response);

 }


 protected void doPost(
   HttpServletRequest request, 
   HttpServletResponse response
   ) throws ServletException, IOException {

  query = "update customer set Last_Name='Smarty' , First_Name='John' , " +
    "Email='john@smarty.com' where First_Name='Jonty';";

  executeQuery(query);

  query = "update customer set Last_Name='Tommy' , First_Name='Hil' , " +
    "Email='hil@tommy.com' where First_Name='Jesse';";

  executeQuery(query);


  query = "update customer set Last_Name='Tendulkar' , First_Name='Sachin' , " +
    "Email='sachin@god.com' where First_Name='Cameroon';";

  executeQuery(query);

  query = "update customer set Last_Name='Sharp' , First_Name='See' , " +
    "Email='see@sharp.com' where First_Name='Java';";

  executeQuery(query);

 }

 private void executeQuery(String query) {

  try {

   statement = connection.createStatement();
   statement.execute(query);

  }
  catch (SQLException e) {

   e.printStackTrace();

  }
 } 

 @Override
 public void destroy() {
  try {    
   statement.close();
   connection.close();
  }
  catch (SQLException e) {

   e.printStackTrace();
  } 

 }

}




Output after Insert query -











Output after Update query -




 
© 2021 Learn Java by Examples Template by Hubberspot