UNIT 4 JAVA SERVER PAGES-II DATABASE HANDLING IN JSP

UNIT 4 JAVA SERVER PAGES-II DATABASE HANDLING IN  JSP  

We have already seen how to interface an HTML Form and a JSP. Now, we have to
see how that JSP can talk to a database. In this section, we will understand how to:

1. Administratively register a database.
2. Connect a JSP to an Access database.
3. Insert records in a database using JSP.
4. Insert data from HTML Form in a database using JSP.
5. Delete Records from Database based on Criteria from HTML Form.
6. Retrieve data from a database using – JSP result sets.

• Administratively Register a Database

Java cannot talk to a database until, it is registered as a data source to your system.
The easiest way to administratively identify or registrar the database to your system so
your Java Server Page program can locate and communicate with it is to do the
following:

1) Use MS Access to create a blank database in some directory such as D. (In my
case, the database was saved as testCase001.mdb.) Make sure to close the
database after it is created or you will get an invalid path message during the
following steps.

2) Go to: Control panel > Admin tool > ODBC where you will identify the database
as a so-called data source.

3) Under the User DSN tab, un-highlight any previously selected name and then
click on the Add button.

4) On the window that then opens up, highlight MS Access Driver and click Finish.

5) On the ODBC Setup window that then opens, fill in the data source name. This is
the name that you will use to refer to the database in your Java program such as
Mimi.

6) Then click Select and navigate to the already created database in directory D.
Suppose the file name is testCase001.mdb. After highlighting the named file,
click OKs all the way back to the original window.
This completes the registration process. You could also use the create option to create
the Access database from scratch. But the create setup option destroys any existing
database copy. So, for an existing DB follow the procedure described above.

• Connect a JSP to an Access Database

 We will now describe the Java code required to connect to the database although at
this point we will not yet query it. To connect with database, the JSP program has to
do several things:
 

1. Identify the source files for Java to handle SQL.
2. Load a software driver program that lets Java connect to the database.
3. Execute the driver to establish the connection.
 

 A simplified JSP syntax required to do this follows is:
<%@ page import= “java.sql.*” %>
<%
Class.forName (“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection conn=null;
conn = DriverManager.getConnection(“jdbc:odbc:Mimi”, “”, “”);
out.println (“Database Connected”);

 %>
 

In this syntax, the so-called page directive at the top of the page allows the program to
use methods and classes from the java.sql.* package that know how to handle SQL
queries. The Class.forName method loads the driver for MS Access. Remember this
is Java so the name is case-sensitive. If you misspell or misidentify the data source
name, you'll get an error message “Data source name not found and no default driver
specified”. The DriverManager.getConnection method connects the program to the
database identified by the data source Mimi allowing the program to make queries,
inserts, selects, etc. Be careful of the punctuation too: those are colons ( : ) between
each of the terms. If you use misspell or use dots, you’ll get an error message about
“No suitable driver”. The Connection class has a different purpose. It contains the
methods that let us work with SQL queries though this is not done in this example.
 

The DriverManager method creates a Connection object conn which will be used
later when we make SQL queries. Thus,
 

1. In order to make queries, we'll need a Statement object.
2. In order to make a Statement object, we need a Connection object (conn).
3. In order to make a Connection object, we need to connect to the database.
4. In order to connect to the database, we need to load a driver that can make the
connection.
 

The safer and more conventional code to do the same thing would include the
database connection statements in a Java try/catch combination. This combination
acts like a safety net. If the statements in the try section fail, then the Exceptions that
they caused are caught in the catch section. The catch section can merely report the
nature of the Exception or error (in the string exc shown here) or do more extensive
backup processing, depending on the circumstances. The code looks like:

 <%@ page import= “java.sql.*” %>
<%
Connection conn=null;
try
{ Class.forName (“sun.jdbc.odbc.JdbcOdbcDriver”);
conn = DriverManager.getConnection(“jdbc:odbc:Mimi”, “”,
“”);
}
catch (Exception exc)
{ out.println(exc.toString() + “<br>”); }

 out.println (“Database Connected”);
conn.close ( );
out.println (“Database closed”);
%>
We have also added another statement at the end that closes the connection to the data
base (using the close ( ) method of the connection object conn).
 

• Insert Records in Database using JSP

So far – other than connecting to the database and then closing the connection to the
database – we have not had any tangible impact on the database. This section
illustrates that we have actually communicated with the database by using simple SQL
insert examples. In general, SQL inserts in a JSP environment would depend on data
obtained from an HTML Form. But addressing that involves additional
complications, so we shall stick here to simple fixed inserts with hardwired data. To
do insert or indeed to use any kind of SQL queries we have to:
 

1. Create a Statement object - which has methods for handling SQL.
2. Define an SQL query - such as an insert query.
3. Execute the query.
 

Example testCase002. adds the following statements to insert an entry in the
database:
 

Statement stm = conn.createStatement ( );
String s = “INSERT INTO Managers VALUES ( 'Vivek’)”;
stm.executeUpdate (s);

 The Connection object conn that was previously created has methods that allow us to
in turn create a Statement object. The names for both these objects (in this case conn
and stm) are of course just user-defined and could be anything we choose. The
Statement object stm only has to be created once. The insert SQL is stored in a Java
String variable. The table managers we created in the database (off-line) has a single
text attribute (managerName) which is not indicated in the query. The value of a text
attribute must be enclosed in single quotes. Finally, the Statement object’s
executeUpdate method is used to execute the insert query. If you run the
testCase002.jsp example and check the managers table contents afterwards, you will
see that the new record has been added to the table.
 

Generally, these statements are executed under try/catch control. Thus, the
executeUpdate ( s ) method is handled using:
try { stm.executeUpdate(s); }
catch (Exception exc) { out.println(exc.toString()); }
 

as in testCase002.jsp. If the update fails because there is an error in the query string
submitted (in s), then the catch clause takes over. The error or exception is set by the
system in exc. The body of the catch can output the error message as shown. It could
also do whatever other processing the programmer deemed appropriate.
 

• Inserting Data from an HTML Form in Database using JSP

The JSP acquires the data to be inserted into a database from an HTML Form. This
interaction involves several elements:
1. An HTML Form with named input fields.

 2. JSP statements that access the data sent to the server by the form.
3. Construction of an insert query based on this data by the JSP program.
4. Execution of the query by the JSP program.
 

To demonstrate this process, we have to define the HTML page that will be accessed
by the JSP program. We will use testCase000.html which has three input fields:
mName, mAge, and mSalary. It identifies the requested JSP program as
testCase003.jsp. For simplicity, initially assume the Access table has a single text
attribute whose value is picked up from the Form. The example testCase003.jsp must
acquire the Form data, prep it for the query, build the query, then execute it. It also
sends a copy of the query to the browser so you can see the constructed query. The
relevant statements are:
 

String name = request.getParameter (“mName”);
name = “” + name + “” ;
String s = “INSERT INTO Managers VALUES (” ;
s += name ;
s += “)” ;
stm.executeUpdate (s);
out.println (“<br>”+s+“<br>”);

 In the above code, the first statement acquires the form data, the second preps it for
the database insert by attaching single quotes fore and aft, the next three statements
construct the SQL insert query, the next statement executes the query, and the final
statement displays it for review on the browser

 • Delete Records from Database based on Criteria from HTML Form

We can illustrate the application of an SQL delete query using the same HTML Form.
The difference between the insert and the delete is that the delete has a where clause
that determines which records are deleted from the database. Thus, to delete a record
where the attribute name has the text value Rahul, the fixed SQL is:
String s = ”Delete From Managers Where name = '
Rahul ' ”
 

• Retrieve Data from Database - JSP ResultSets

  Retrieving data from a database is slightly more complicated than inserting or deleting

data. The retrieved data has to be put someplace and in Java that place is called a

ResultSet. A ResultSet object, which is essentially a table of the returned results as

done for any SQL Select, is returned by an executeQuery method, rather than the

executeUpdate method used for inserts and deletes. The steps involved in a select

retrieval are:

1. Construct a desired Select query as a Java string.

2. Execute the executeQuery method, saving the results in a ResultSet object r.

3. Process the ResultSet r using two of its methods which are used in tandem:

a) r.next ( ) method moves a pointer to the next row of the retrieved table.

 b) r.getString (attribute-name) method extracts the given attribute value from the
currently pointed to row of the table

 A simple example of a Select is given in testCase04 where a fixed query is defined.
The relevant code is:
 

String s = “SELECT * FROM Managers”;
ResultSet r = stm.executeQuery(s);
while ( r.next( ) )
{
out.print (“<br>Name: ” + r.getString (“name”) );
out.println(“ Age : ” + r.getString (“age” ) );
}

 The query definition itself is the usual SQL Select. The results are retrieved from the
database using stm.executeQuery (s). The while loop (because of its repeated
invocation of r.next( ) advances through the rows of the table which was returned in
the ResultSet r. If this table is empty, the while test fails immediately and exits.
Otherwise, it points to the row currently available. The values of the attributes in that
row are then accessed using the getString method which returns the value of the
attribute “name” or “age”. If you refer to an attribute that is not there or misspell,
you'll get an error message “Column not found”. In this case, we have merely output
the retrieved data to the HTML page being constructed by the JSP. Once, the whole
table has been scanned, r.next( ) returns False and the while terminates. The entire
process can be included in a try/catch combination for safety