Updating records in the Database using JDBC with Java Servlets

Overview
This article is next in the series of articles about selecting, inserting, updating and deleting records from the database using JDBC. In this article we will learn how to update records in the database. If you have followed my earlier article about 'Inserting records in the Database' then this article is not going to be difficult at all. 90% of the code will be same. So if you haven't read that article then I will suggest that you go through that article before starting this one as quite a few important things have been explained in detail there.

How to Update Records ?
To update records in the database we will be using the same PreparedStatement class we used before for inserting records. Although we can update records using the Statement class, the update operation is less efficient and not optimized at all. PreparedStatement fills that gap and lets us build SQL queries which are compiled and thus more efficient.

PreparedStatement
This class like other JDBC classes we have been discussing is present in the java.sql package. This is how you get handle on a PreparedStatement object :

String sql = "UPDATE Names SET first_name=?, last_name=? WHERE ID=?";

// con is Connection object

PreparedStatement ps = con.prepareStatement(sql);

Connection.prepareStatement() returns a reference to the PreparedStatement object. The only argument to the Connection.prepareStatement() method is an SQL statement containing optional '?' ( question mark ).

You should put '?' marks in the statement where you are going to put or change the values, for example in my example above I placed '?' marks at three places where I will put different values depending on the values entered by the user.

So how to set the values of '?' parameters. You set the values by using a setXxx() methods of PreparedStatement class. setXxx() are over 25 methods whose syntax is setObject(int paramIndex, Object o) where paramIndex is the number of '?' mark from left to right in the SQL statement. For example we will use setString(1, value1) and setString(2, value2) methods to set the value of both parameters to two different values. And setInt(3, value3) to set the value of third '?' mark to value3.

	ps.setString(1, "First Name");
	ps.setString(2, "Last Name");
	ps.setId(3, 1);
	ps.executeUpdate();
	

Once the parameters are set in the PreparedStatement object, we execute the query using PreparedStatement.executeUpdate() method. You should use PreparedStatement.executeUpdate() for update, UPDATE and DELETE SQL queries and PreparedStatement.executeQuery() for any SQL statement that returns records.

UpdateServlet
Create a new UpdateServlet.java file in the /APP_NAME/WEB-INF/classes/com/stardeveloper/servlets/db/ folder. Note /APP_NAME/ is the path of your application within your application server, in Tomcat 4.0 /APP_NAME/ will be /CATALINA_HOME/webapps/star/ where 'star' is the name of the application.

Copy and paste the following code into the UpdateServlet.java file :

String sql = "UPDATE Names SET first_name=?, last_name=? WHERE ID=?";

// con is Connection object

PreparedStatement ps = con.prepareStatement(sql);

	ps.setString(1, "First Name");
	ps.setString(2, "Last Name");
	ps.setId(3, 1);
	ps.executeUpdate();