Updating records in the Database 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.
On the next page we make use of PreparedStatement object to develop a user Form page in which a user can update his first and last names and then when he presses the 'submit' button the existing record is updateed in the database using the methods we just discussed.
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 :
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("");
// 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("
");}
} 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("");
out.print("
out.print( req.getRequestURI() );
out.print("\">Back
");out.print("");
out.close();
}
public void doPost(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.setContentType("text/Html");
PrintWriter out = res.getWriter();
out.print("");
out.print("
");
out.print("ID\t");
out.println("First Name\tLast Name\n");
// 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("
out.print("
out.print( req.getRequestURI() );
out.print("\">Back
");out.print("");
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.
For explanation of UpdateServlet code above, please proceed to the next page.
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.
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");
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.
String sql = "UPDATE Names SET first_name=?";
sql += ", last_name=? WHERE ID=?";
ps = con.prepareStatement(sql);
stmt = con.createStatement();
We build the SQL statement which we will use to insert records into the database. Next we create the PreparedStatement and Statement objects using Connection object's methods.
if(proceed) {
ps.setString(1, first);
ps.setString(2, last);
ps.setInt(3, id);
ps.executeUpdate();
}
Next we set the three '?' mark parameters in our PreparedStatement object and update the record using PreparedStatement.executeUpdate() method.
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");
}
We create our ResultSet object by executing the SELECT query. We then iterate through the records and display it to the user. Notice that the new record we just inserted will also be visible to the user during this iteration.
if(rs != null)
rs.close();
if(stmt != null)
stmt.close();
if(ps != null)
ps.close();
if(con != null)
con.close();
Close all the objects that we created.
I haven't mentioned try..catch statements that we used to catch different exceptions that may be thrown during opening and closing of database connection.
On the next page I sum up what we learned in this article.
Summary :
In this step by step tutorial we learned what is PreparedStatement class and how to use it to build fast SQL statements. We then moved forward to build a simple Form application in which we display first and last names of all the users in our database allowing a user to update his first and last name and these values are then updated in the database. After that all the names are displayed to user. Note that this application assumes that you have already built the 'odbc_exmp.mdb' Access database with a DSN of 'odbc_exmp' and contains a single table 'Names' with three fIElds 'ID', 'first_name' and 'last_name' where 'ID' is the primary key. To learn more about this database read 'Displaying Records from the Database' article.
The driver we used was JDBC/ODBC driver, this driver comes with Java Development Kit so you don't need to download and install it separately. For more information on how we built the 'odbc_exmp.mdb' database and what are different types JDBC drivers please consult the above mentioned article.
If you have followed my 'Inserting Records into the Database' article then you must have noticed that the only real change that is required to update records instead of inserting is to change the SQL query from INSERT to UPDATE, so you have got only one line to change.
That's it for this article. Kindly post your questions in the Forum. Thanks.
There is no associated material for download
Click here to see the demo
