| Lesson 8 | Reading Information from a database into a ResultSet |
| Objective | Describe the code that generates the Access database contents. |
Read database Information into ResultSet
Assuming you made a connection in your init() override, your doPost() will use that connection to look something up. For example, I made a database table with two fields: a name and a number.
My starting values looked like this:
| Name | Number |
| Kate | 123 |
| Bill Gates | 456 |
If a user enters “Kate” on the form, which is the same “enter your name” form we have been using all along, I would
like doPost() to look up their number in the database and display it. Something like “Hello Kate, your number is 123.” To do this, you have to use two additional classes: Statement and ResultSet. Like Connection, these are part of the java.sql package, and will not be covered in detail here. I’m just going to show you how to use them.
First, build a statement like this:
Statement stmt = con.createStatement();
Member Variable Database Connection
It’s a good idea to check the member variable that holds your database connection is not null before trying to work with the database. If it was null and you tried to use it, a NullPointerException would be thrown. The connection member variable is null before I get the connection – if it’s null afterwards I know the connection wasn’t made properly.
Calling createStatement creates an SQL Statement object to which you can pass SQL to be executed, using the executeQuery() method. When you do that, you will be given a ResultSet object that holds the results of your statement. Here’s an example:
ResultSet rs = stmt.executeQuery("SELECT * FROM PEOPLE where AGE=25");
The string in the quotes is SQL, like the SELECT statements you saw in the sidebar. It is more interesting if you do not hard-code
the entire string. For example, if the age you want to search for is in a variable called age, you could write:
ResultSet rs = stmt.executeQuery("SELECT * FROM PEOPLE where AGE="+age);
Your SQL statement would be built out of variables, and this gives your servlets tremendous power.
One thing to note: when you pass a string that might contain a space, such as “Bill Gates”, you need quotes around it in your SQL. Single or double quotes will work, and single quotes will not confuse Java into thinking the SELECT statement is over. Here is an example using single quotes:
ResultSet rs = stmt.executeQuery("Select * from Logins where Name = '" + name + "'");
In the next lesson, you will learn how to use your result set.