Inserting records into 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 insert records into the database. If you have followed my earlIEr article about 'Displaying Records from 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 Insert Records ?
To insert records into the database we will have to learn about another JDBC class, PreparedStatement. Although we can insert records using the Statement class we discussed in the last article, the INSERT 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.
Note that not all database vendors support PreparedStatement class but still it is not a bad habit to use this class so that the ones that do support PreparedStatement class get the extra efficIEncy.
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 = "INSERT INTO Names(first_name, last_name) VALUES (?,?)";
// 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 ) containing SQL statement.
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 two places where I will put different values depending on the values inserted 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.
ps.setString(1, "First Name");
ps.setString(2, "Last Name");
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 INSERT, 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 enter his first and last name and when presses the 'submit' button the records are inserted into the database using the methods we just discussed.
InsertServlet :
Create a new InsertServlet.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 InsertServlet.Java file :
package com.stardeveloper.servlets.db;
import Java.sql.*;
import Java.io.*;
import Javax.servlet.*;
import Javax.servlet.http.*;
public class InsertServlet extends HttpServlet {
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.setContentType("text/Html");
PrintWriter out = res.getWriter();
out.print("");
out.print("
");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.println("ID\tFirst Name\tLast Name\n");
// receiving parameters
String first = req.getParameter("first").trim();
String last = req.getParameter("last").trim();
boolean proceed = false;
if(first != null && last != null)
if(first.length() > 0 && last.length() > 0)
proceed = true;
// connecting to database
Connection con;
Statement stmt;
ResultSet rs;
PreparedStatement ps;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:odbc_exmp");
String sql = "INSERT INTO Names(first_name,");
sql += " last_name) VALUES (?,?)";
ps = con.prepareStatement(sql);
stmt = con.createStatement();
// inserting records
if(proceed) {
ps.setString(1, first);
ps.setString(2, last);
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 (SQLException e) {
throw new ServletException(e);
} catch (ClassNotFoundException e) {
throw new ServletException(e);
} finally {
try {
if(rs != null)
rs.close();
if(stmt != null)
stmt.close();
if(ps != null)
ps.close();
if(con != null)
con.close();
} catch (SQLException e) {}
}
out.print("");
out.close();
}
}
Start your application server and point your browser to http://localhost:8080/star/servlet/com.stardeveloper.servlets.db.InsertServlet to see the Servlet on your computer. To see the demo please move on to the last page of this article.
For explanation of InsertServlet code above, please proceed to the next page.
Explanation :
Our InsertServlet class extends from HttpServlet class and overrides two methods; doGet() and doPost(). In doGet() we simply display a Form to the user with two input fIElds for first and last names and two submit buttons, one for inserting and the other one for displaying records.
String first = req.getParameter("first").trim();
String last = req.getParameter("last").trim();
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 = "INSERT INTO Names(first_name,");
sql += " last_name) VALUES (?,?)";
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.executeUpdate();
}
Next we set the two '?' mark parameters in our PreparedStatement object and insert the records 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 a user enters his first and last name and these values are inserted into the database. After that all the names entered are displayed to user.
The database we used in this article was a Microsoft Access database 'odbc_exmp.mdb' that we built in '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.
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
