Updating records in the Database using JDBC with Java Servlets

Explanation
Notice that we are using the same Access database we built in the 'Displaying Records from the Database' article with the DSN of 'odbc_exmp'. Please consult that article for more details on the database and 'Names' table.

Our UpdateServlet class extends from HttpServlet class and overrides two methods; doGet() and doPost(). In doGet() we connect to the database and retrieve all the values from the 'Names' table and display a Form to the user with two input fields containing first and last names along with a submit button for updating records.

In doPost() we retrieve the first and last name values entered by the user using HttpServletRequest.getParameter() method.

Using a double if statement we make sure that we are not entering null values into the database. If user has entered both first and last name then we proceed.

We load the Sun's JDBC/ODBC driver and establish connection to our database using the DSN 'odbc_exmp'. Notice that this is the same database we used in 'Displaying Records from the Database'. Please consult that article to see the steps of creating such a database and assigning DSN.

package com.stardeveloper.servlets.db;

import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class UpdateServlet extends HttpServlet {

	public void doGet(HttpServletRequest req, HttpServletResponse res) 
		throws ServletException, IOException {

		res.setContentType("text/html");
		PrintWriter out = res.getWriter();

		out.print("<html><body>");

		// connecting to database

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			con=DriverManager.getConnection("jdbc:odbc:odbc_exmp");
			stmt = con.createStatement();

			// displaying records

			rs = stmt.executeQuery("SELECT * FROM Names");

			out.print("<form action=\"");
			out.print( req.getRequestURI() );
			out.print("\" method=\"post\">");
			out.print("<input type=\"hidden\" name=\"id\"");
			out.print(" value=\"0\">");
			out.print("<input type=\"submit\" value=\"癧">");
			out.print(" 	Display Records<br><br>");
			out.print("First & Last Names :<br><br>");

			while(rs.next()) {

				out.print("<form action=\"");
				out.print( req.getRequestURI() );
				out.print("\" method=\"post\">");
				out.print("<input type=\"hidden\"");
				out.print(" name=\"id\" value=\"");
				out.print( rs.getObject(1).toString() );
				out.print("\">");
				out.print("<input type=\"text\"");
				out.print(" name=\"first\" value=\"");
				out.print( rs.getObject(2).toString() );
				out.print("\">");
				out.print("<input type=\"text\"");
				out.print(" name=\"last\" value=\"");
				out.print( rs.getObject(3).toString() );
				out.print("\">");
				out.print(" 	<input type=\"submit\"");
				out.print(" value=\"癧">");
				out.print("	Update Record<br>");
				out.print("</form>");
			}

		} catch (Exception e) {
			throw new ServletException(e);
		} finally {
			try {
				if(rs != null) {
					rs.close();
					rs = null;
				}
				if(stmt != null) {
					stmt.close();
					stmt = null;
				}
				if(con != null) {
					con.close();
					con = null;
				}
			} catch (SQLException e) {}
		}

		out.print("</pre></code>");
		out.print("<p\"><a href=\"");
		out.print( req.getRequestURI() );
		out.print("\">Back</a></p>");

		out.print("</body></html>");
		out.close();
	}

	public void doPost(HttpServletRequest req, HttpServletResponse res) 
		throws ServletException, IOException {

		res.setContentType("text/html");
		PrintWriter out = res.getWriter();

		out.print("<html><body>");

		out.print("<code><pre>");
		out.print("<font color=green>ID\t");
		out.println("First Name\tLast Name\n</font>");

		// receiving parameters

		String first = req.getParameter("first").trim();
		String last = req.getParameter("last").trim();
		int id;
		try {
			id = Integer.parseInt(req.getParameter("id").trim());
		} catch (NumberFormatException e) {
			throw new ServletException(e);
		}

		boolean proceed = false;
		
		if(first != null && last != null)
			if(first.length() > 0 && last.length() > 0)
				proceed = true;

		// connecting to database

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		PreparedStatement ps = null;

		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			con=DriverManager.getConnection("jdbc:odbc:odbc_exmp");

			String sql = "UPDATE Names SET first_name=?";
			sql += ", last_name=? WHERE ID=?";
			ps = con.prepareStatement(sql);
			stmt = con.createStatement();

			// updating records

			if(proceed) {
				ps.setString(1, first);
				ps.setString(2, last);
				ps.setInt(3, id);
				ps.executeUpdate();
			}

			// displaying records

			rs = stmt.executeQuery("SELECT * FROM Names");
			while(rs.next()) {
				out.print( rs.getObject(1).toString() );
				out.print("\t");
				out.print( rs.getObject(2).toString() );
				out.print("\t\t");
				out.print( rs.getObject(3).toString() );
				out.print("\n");
			}

		} catch (Exception e) {
			throw new ServletException(e);
		} finally {
			try {
				if(rs != null) {
					rs.close();
					rs = null;
				}
				if(stmt != null) {
					stmt.close();
					stmt = null;
				}
				if(ps != null) {
					ps.close();
					ps = null;
				}
				if(con != null) {
					con.close();
					con = null;
				}
			} catch (SQLException e) {}
		}

		out.print("</pre></code>");
		
		out.print("<p\"><a href=\"");
		out.print( req.getRequestURI() );
		out.print("\">Back</a></p>");

		out.print("</body></html>");
		out.close();
	}
}

Start your application server and point your browser to http://localhost:8080/star/servlet/com.stardeveloper.
servlets.db.UpdateServlet to see the Servlet on your computer. To see the demo please move on to the last page of this article.

	String first = req.getParameter("first").trim();
	String last = req.getParameter("last").trim();
	int id;
	try {
		id = Integer.parseInt(req.getParameter("id").trim());
	} catch (NumberFormatException e) {
		throw new ServletException(e);
	}
	boolean proceed = false;

	if(first != null && last != null)
		if(first.length() > 0 && last.length() > 0)
			proceed = true;

In doPost() we retrieve the first and last name values entered by the user using HttpServletRequest.getParameter() method.

Using a double if statement we make sure that we are not entering null values into the database. If user has entered both first and last name then we proceed.

	Connection con;
	Statement stmt;
	ResultSet rs;
	PreparedStatement ps;

We declare the objects we are going to use to interact with the database.

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:odbc_exmp");

	String sql = "UPDATE Names SET first_name=?";
	sql += ", last_name=? WHERE ID=?";
	ps = con.prepareStatement(sql);
	stmt = con.createStatement();