Pin It

How to perform create 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 create 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 Servlet. It will perform necessary create 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("/CreateJdbcQueryServlet")
public class CreateJdbcQueryServlet extends HttpServlet {

 Connection connection = null;
 ResultSet resultSet = null;
 Statement statement = null;
 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 {

  response.setContentType("text/html;charset=UTF-8");
  PrintWriter out = response.getWriter();

  query = "create table customer " +      
    "(First_Name varchar(20) NOT NULL,"+    
    "Last_Name varchar(30) NOT NULL,"+
    "Email varchar(50) NOT NULL,"+
    "City varchar(30) NOT NULL,"+    
    "password varchar(30) NOT NULL PRIMARY KEY "+    
    ");";

  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();
  } 

 }


}





Please Give Us Your 1 Minute In Sharing This Post!
SOCIALIZE IT →
FOLLOW US →
SHARE IT →
Related Posts Plugin for WordPress, Blogger...

Post your related queries below ...

comments powered by Disqus
Powered by Blogger.
 
© 2012 Learn Java by Examples Template by Hubber Spot