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 -
