Home arrow Java & J2EE arrow Page 5 - The JSP Files (part 5): No Forwarding Address

New Friends - Java

This week, find out how to connect your JSPs to a database and build dynamic, data-driven Web pages. This primer covers different techniques to select, insert and delete records, and uses a simple Web-based address book to illustrate the Connection, Statement, and ResultSet objects.

  1. The JSP Files (part 5): No Forwarding Address
  2. Dumped!
  3. The Scenic Route
  4. One Step At A Time
  5. New Friends
  6. No Forwarding Address
  7. Cleaning Up
By: Vikram Vaswani and Harish Kamath, (c) Melonfire
Rating: starstarstarstarstar / 3
March 19, 2001

print this article


Thus far, we've simply been using SELECT queries to pull information out of a database. But how about putting something in?

SQL aficionados know that this happens via an INSERT query. And so, the next item on the agenda involves adding new entries to the address book. Here's the form we'll be using:

<html> <head> <basefont face="Arial"> </head> <body> <center> <h2>Add Address Book Entry</h2> <table border=0 cellspacing=5 cellpadding=5> <form action="add_res.jsp" method="POST"> <tr> <td><b>Username</b></td> <td> <select name="uid"> <!-- generate list of available usernames from database --> <%@ page language="java" import="java.sql.*" %> <% // database parameters String host="localhost"; String user="us867"; String pass="jsf84d"; String db="db876"; String connString; // load driver Class.forName("org.gjt.mm.mysql.Driver"); // create connection string connString = "jdbc:mysql://" + host + "/" + db + "?user=" + user + "&password=" + pass; // pass database parameters to JDBC driver Connection Conn = DriverManager.getConnection(connString); // query statement Statement SQLStatement = Conn.createStatement(); // generate query String Query = "SELECT DISTINCT uid FROM abook"; // get result ResultSet SQLResult = SQLStatement.executeQuery(Query); // get and display each record while(SQLResult.next()) { String UId = SQLResult.getString("uid"); out.println("<option>" + UId); } // close connections SQLResult.close(); SQLStatement.close(); Conn.close(); %> </select> </td> </tr> <tr> <td>First name</td> <td><input type="Text" name="fname" size="15"></td> </tr> <tr> <td>Last name</td> <td><input type="Text" name="lname" size="15"></td> </tr> <tr> <td>Address</td> <td><textarea name="address"></textarea></td> </tr> <tr> <td>Tel</td> <td><input type="Text" name="tel" size="10"></td> </tr> <tr> <td>Fax</td> <td><input type="Text" name="fax" size="10"></td> </tr> <tr> <td>Email address</td> <td><input type="Text" name="email" size="10"></td> </tr> <tr> <td>Company</td> <td><input type="Text" name="company" size="25"></td> </tr> <tr> <td>Comment</td> <td><input type="Text" name="comment" size="25"></td> </tr> <tr> <td colspan=2><input type="submit" name="submit" value="Add"></td> </tr> </form> </table> </center> </body> </html>
If you examine it closely, you'll see that this form performs a query to retrieve the list of users currently available in the system, and uses this data to generate a list box containing the different user names. This makes it possible to specify the owner of each record when it is INSERTed.

Once the form has been filled up and submitted, control passes to "add_res.jsp", which takes care of actually performing the INSERT operation. Take a look.

<html> <head> <basefont face="Arial"> </head> <body> <center> <%@ page language="java" import="java.sql.*" %> <% // add_res.jsp // form data String uid = request.getParameter("uid"); String fname = request.getParameter("fname"); String lname = request.getParameter("lname"); String address = request.getParameter("address"); String tel = request.getParameter("tel"); String fax = request.getParameter("fax"); String email = request.getParameter("email"); String company = request.getParameter("company"); String comment = request.getParameter("comment"); // database parameters String host="localhost"; String user="us867"; String pass="jsf84d"; String db="db876"; String conn; Class.forName("org.gjt.mm.mysql.Driver"); // create connection string conn = "jdbc:mysql://" + host + "/" + db + "?user=" + user + "&password=" + pass; // pass database parameters to JDBC driver Connection Conn = DriverManager.getConnection(conn); // query statement Statement SQLStatement = Conn.createStatement(); // generate query String Query = "INSERT INTO abook (id, uid, fname, lname, tel, fax, email, addr, company, comment) VALUES (NULL, '" + uid + "', '" + fname + "', '" + lname + "', '" + tel + "', '" + fax + "', '" + email + "', '" + address + "', '" + company + "', '" + comment + "')"; // get result code int SQLStatus = SQLStatement.executeUpdate(Query); if(SQLStatus != 0) { out.println("Entry succesfully added."); } else { out.println("Error! Please try again."); } // close connection SQLStatement.close(); Conn.close(); %> </center> </body> </html>
This example demonstrates yet another method of the Statement object, the executeUpdate() method, used for INSERT or UPDATE operations. This method returns a result code indicating the number of rows affected by the operation - in case of the example above, this result code should be 1. In case it isn't...you've got trouble!

It should be noted at this point that if your INSERT statement contains special characters which need to be escaped (say, commas or single quotes), you'd do better to use the PreparedStatement class, which automatically takes care of escaping special characters and offers some performance benefits as well. A discussion of the PreparedStatement class is beyond the scope of this tutorial, but there's plenty of documentation out there should you ever require it.

>>> More Java & J2EE Articles          >>> More By Vikram Vaswani and Harish Kamath, (c) Melonfire

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- More Java Bugs Lead to More Attacks
- Oracle's Java One Brings News, Surprises
- Oracle Patches Java Runtime Environment
- Apple Syncs Java Update with Oracle
- Spring 3.1 Java Development Framework Compat...
- Jelastic Java PaaS Availability and Pricing ...
- NetBeans 7.1 Released, Supports JavaFX 2
- SolarWinds Releases Newest Version of Java M...
- Free Monitoring Tool for Java Apps on Heroku
- Heroku Adds JCloud Platform Support, Java 7 ...
- Java SE 8 Speculation in Full Swing
- Java SE 7 Now Available
- New JVM Language and Java Reporting Tool
- Java 7 Release Update and New Eclipse Toolkit
- The Best Java Netbeans IDE Plugins

Developer Shed Affiliates


Dev Shed Tutorial Topics: