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 select 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 select 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 "user". Run the create query stated below -

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




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

INSERT INTO user () VALUES ( 'Jonty','Magic','jonty@magic.com','123456');

INSERT INTO user () VALUES ( 'JSP','Servlets','jsp@servlets.com','123');

INSERT INTO user () VALUES ( 'java','sun','java@sun.com','12');



5. Create a Servlet. It will perform necessary select query and output it to browser. 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.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
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("/JdbcConnection")
public class JdbcConnection extends HttpServlet {

 Connection connection = null;
 ResultSet resultSet = null;
 PreparedStatement preparedStatement = 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 = "select * from User";
  try {
   preparedStatement = connection.prepareStatement(query);
   resultSet = preparedStatement.executeQuery();

   out.println("<html><body><h2>The Select query has following results : </h2>");
   out.println("<hr></br><table cellspacing='0' cellpadding='5' border='1'>");
   out.println("<tr>");
   out.println("<td><b>First Name</b></td>");
   out.println("<td><b>Last Name</b></td>");
   out.println("<td><b>Email</b></td>");
   out.println("</tr>");

   while(resultSet.next()) {
    out.println("<tr>");
    out.println("<td>"+resultSet.getString(1) + "</td>");
    out.println("<td>"+resultSet.getString(2) + "</td>");
    out.println("<td>"+resultSet.getString(3) + "</td>");
    out.println("</tr>");

   }

   out.println("</table></br><hr></body></html>");
  }
  catch (SQLException e) {

   e.printStackTrace();
  }
  
 }

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

		e.printStackTrace();
	}	

}


}




6. Run the servlet JdbcConnection and see the output on the browser as - 





 
© 2021 Learn Java by Examples Template by Hubberspot