Pin It

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 - 





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