Servlets are mainly used in Dynamic web applications which provides dynamic responses to client requests. In most cases, Dynamic web applications access a database to provide the client requested data. We can use Java standard database connection – JDBC in Servlets to perform database operations. To understand this tutorial, you should have basic understanding on how JDBC application works. Please go through our JDBC tutorial for more details.
Servlet – Database connection
A Servlet can generate dynamic HTML by retrieving data from the database and sending it back to the client as a response. We can also update the database based on data passed in the client HTTP request. We will create a simple servlet to fetch/retrieve data from the database based on the client’s request. In this example, we will be using Eclipse IDE and PostgreSQL database.
Steps for JDBC connection in Servlets
Install JDBC Driver:
We need to install the appropriate JDBC driver in our program to connect with the database. As we are using PostgreSQL database, install “postgresql.jar” file with the latest version from Maven Repository. After installation, place your jar file under “WebContent -> WEB-INF -> lib” folder.
Import JDBC packages:
To access and process the Database operations, we need to import all the required “java.sql” packages in the program.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
|
Register the JDBC Driver:
After importing all the packages, we need to register the JDBC driver which we installed into our program. Registering the driver tells the JVM to load the driver’s class file into the memory so that we can implement the JDBC operations. We can register the driver by using “Class.forName()” method:
Class.forName():
try {
// Register PostgreSQL Driver
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e) {
System.out.println("Unable to load Driver class");
// e.printStackTrace(); OR you
// can directly print the stack trace
System.exit(1);
} |
Establish the Connection to Database:
Now, we need to establish the connection to the database using the “DriverManager.getConnection()” method.
String USER = "username";
String PASSWORD = "password";
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD); |
As you can see, we need to pass the URL, Username, and Password of the database we are using as parameters to the connection.
- URL: This is the address of the database you are using. In URL, you need to mention the “jdbc:database:hostname:port:databasename”.
- Username and Password: You need to specify the username/password set to your database.
Create a JDBC Statement object:
After successful connection, prepare JDBC statement object using the Connection object.
Statement stmt = conn.createStatement(); |
Execute the SQL query:
Construct the SQL query based on the client request and execute the query using statement object.
stmt.executeQuery(sql); |
Close Database connection:
After processing the required operations, finally, close all the database connection objects.
stmt.close(); conn.close(); |
Example
In this example, we will create
- A table in PostgreSQL – to fetch the data from it
- An HTML form – for the client access
- A Servlet class – to process the client request and generate the response.
PostgreSQL table:
Create a table in the PostgreSQL database and insert some records in it like below.
PostgreSQL – Table
home.html:
<!DOCTYPE html> <html> <head> <meta charset="ISO-8859-1">
<title>Home Page</title> </head> <body> <form action="fetch" method="get">
Fetch Mobile phone details:<input type="submit" value="Search" />
</form>
</body> </html> |
- Create “home.html” file under “WebContent -> home.html”.
- This is the welcome file, where the browser accesses this form page.
- Here we are simply displaying a submit button to fetch the data from the database.
- In the form tag, mentioned the action “fetch” and method “get” to map the URL to a specific servlet class when the form is submitted.
FetchServlet.java:
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.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet("/fetch")
public class FetchServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
final String USER = "root";
final String PASSWORD = "root";
final String DRIVER = "org.postgresql.Driver";
Connection conn = null;
public void init() throws ServletException {
// Database connection through Driver Manager
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
// Set the response content type and
// get the PrintWriter object.
response.setContentType("text/html");
PrintWriter out = response.getWriter();
// Set up HTML table formatting for the output
out.println("<html><body>");
out.println("<h3>Mobile Phone Details</h3>");
out.println("<table border=1><tr>" + "<td><b>S.No</b></td>" + "<td><b>Brand</b></td>"
+ "<td><b>Processor</b></td>" + "<td><b>Operating System</b></td>"
+ "<td><b>Screen Size(inches)</b></td>" + "<td><b>Battery Life(mAh)</b></td></tr>");
// Create JDBC statement object, construct
// the SQL query and execute the query.
Statement stmt = conn.createStatement();
String sql = "select * from public.mobilePhones;";
ResultSet rs = stmt.executeQuery(sql);
// Loop through the result set to
// retrieve the individual data items.
while (rs.next()) {
int sno = rs.getInt("sno");
String brand = rs.getString("brand");
String processor = rs.getString("processor");
float screenSize = rs.getFloat("screensize");
String osystem = rs.getString("operatingsystem");
int batteryLife = rs.getInt("batterylife");
out.println("<tr>" + "<td>" + sno + "</td>" + "<td>" + brand + "</td>" + "<td>" + processor + "</td>"
+ "<td>" + osystem + "</td>" + "<td>" + screenSize + "</td>" + "<td>" + batteryLife
+ "</td></tr>");
}
out.println("</table></body></html>");
// Close Result set, Statement
// and PrintWriter objects.
rs.close();
stmt.close();
out.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void destroy() {
// Close connection object.
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} |
- Create “FetchServlet.java” under “src” folder.
init():
- As we know, Servlet “init()” method is called by the servlet container only once for initializing the servlet after instantiating it. After successful initialization of the servlet only, it can receive the client requests. If init() method fails, it throws “ServletException“.
- So, we are using this init() method to register the JDBC driver and establish a database connection while initializing the servlet class.
- While registering the JDBC driver, if it fails to load the class, it throws “ClassNotFoundException“.
- If the Connection to the database fails, it throws “SQLException“.
- So, we need to make sure that these checked exceptions are handled in our code using the try/catch block.
doGet():
- After the initialization of the servlet, when client requests the data to fetch from the database, the servlet container calls the respective service method to perform the operation.
- Here we specified the URL with annotation “@WebServlet(“/fetch”)” in our code. Instead of using annotation, you can also specify the URL mapping in web.xml – Web Deployment descriptor.
- When the request comes, it maps with the URL specified in the servlet and executes the “doGet()” method in this servlet as we specified the method as “get” in the HTML.
- As we need to generate the response and print the fetched data in it, set the response content type as “text/html” to specify which type of data will be sending in the response object.
- To print the data to the text-output stream, we are using the “PrintWriter” class object using the “getWriter()” method on the response object.
- Now, we need to display the data in the browser in user understandable and formatted way. So, format the information using the HTML table tag in the output.
- As we established the connection to the database, now create the statement object.
- Construct the select query to fetch the data from the database and execute it.
- Store the result of the executed query in a result set so that we can retrieve that data into the individual data items in the servlet.
- Loop through the result set and print the retrieved data in formatted way to the response.
- At last, close all the connections to the database to avoid any resource leak.
- While performing these database operations, if any step fails, it throws “SQLException“, so make sure to handle the exception using try/catch block.
destroy():
- Servlet container will call the “destroy()” method to destroy the specific servlet from the service. Once all the client requests are processed or after a specified timeout period has passed, this method will be called only once at last.
- In this method, we can specify all the clean-up activities such as resource deallocations.
- So, once all the client requests are processed, we need to close the connection to the database.
Deploy and Test the servlet
- To deploy and run the program, run the “home.html” file as it is the welcome file.
- Right click in HTML, “Run As -> Run on Server”.
- Once the server is started, Servlet container loads this web application and displays the HTML page in the browser.
- URL: http://localhost:8081/ServletDBAccess/home.html
Home Page
- Click the “Search” button to fetch the results.
Output
- This way, the servlet process the client request and fetch the data from the database and generate the response to the client with that data.
- As we formatted the output data in the HTML table, the information is displayed in that formatted way.
In this way, we can access the database through Java Servlets and generate dynamic responses based on the client requests. In this example, we learn about fetching the data from the database. We can perform all CRUD operations in Java Servlets using JDBC – Visit here to learn the tutorial.