retreiving values from MS ACCESS DB to a jsp page using tomcat?

By rush2pooja ·
i have created a DB in MS ACCESS. driver:JDBC-ODBC DRIVER..the connection has been established...but how do i retreive values from this DB and display in a jsp page?plz help

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Happy reading.....

Java Server Pages, SQL, HTML Forms and Databases

This section examines how to communicate with a database from Java. 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. The objectives of this section are to understand how to:

1.Administratively register a databases.
2.Connect a JSP to an Access database.
3.Insert records in a database using JSP.
4.Inserting 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.
7.Apply SQL operations like sort, create table, remove table, delete, and Access-based arithmetic functions.

Administratively Register 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 D. (In my case, the database was saved as testCase001.mdb.) Make sure to close the data base 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 System DSN tab (for Tomcat version 5 or later ? User DSN for earlier
versions), un-highlight any previously selected name and then click on the Add

4. On the window that then opens up, highlight MS Access Driver & 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 data base in your Java program ? like
Mimi. This name does not have to match the file name.
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 data base 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. If the program runs successfully, it indicates that the previous registration steps have been successful. We will first give a simplified version of the code, and then a more robust version which the Java try/catch apparatus. The try/catch tries to execute code and intercept errors that occur without crashing the program.

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.

The required (simplified) JSP syntax to do this follows:

<%@ page import="java.sql.*" %>

Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn=null;
conn = DriverManager.getConnection("jdbc:odbc:Mimi", "", "");
out.println ("Database Connected.");

This syntax is basically standard boilerplate code that you can include in your JSP program, but the customary try/catch error detection apparatus has been omitted for simplicity. 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.

As we will see later, the DriverManager method can also supply a password to a password-protected database. The print statement merely indicates the program has ended.
If you run the program successfully it will only print the "Database Connected" statement. If the administrative setup has been done incorrectly, or the data source name misspelled, or the database is not where it is supposed to be, then you will get a Tomcat Server Error report indicating, for example, that a java.lang.NullPointerException has occurred, or "Data source name not found", or "could not find file", etc.

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 for a trapeze. 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;

{ 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 data base. This section illustrates that we have actually communicated with the database by using simple SQL insert examples. You might wonder why we have not decided to start with what would seem a more passive inquiry: issuing Select queries against the database. The reason is that Selects require the use of a special data structure to hold the retrieved data (called result sets). Thus, inserts are simpler (as would be deletes!) 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 inserts 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.jsp (under /myjspapp/chapter11) adds the following statements to insert an entry in the database:

Statement stm = conn.createStatement ( );
String s = "INSERT INTO Managers VALUES ( 'Charlie') ";
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 chose. 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 via Tomcat and check the Managers table contents afterwards, you will see that the new record has been added to the table.

We can modify the Managers table by going directly to the database and introducing additional attributes. For example, suppose we add attributes: age of type number and salary of type currency. The values of attributes are listed in the parameter list of the insert query in the order of the appearance of the attributes in the table definition. Thus, we can change the insert to:

String s = "INSERT INTO Managers VALUES ('Charlie',33,70000.00) ";

Observe that the number attribute (age) is given as a number without quotes and that the currency attribute (salary) is given as a decimal value. We can also input the currency value as '$70000.00' inside single quotes. However, using $70000.00 with the dollar sign but no quotes causes an error report: "Syntax error in query expression '$70000.00' ". This insert is illustrated in testCase003.jsp. If the number of attributes does not match up then the Tomcat error reports: "Number of query values and destination fields are not the same." Verify the inserts by opening the database table afterwards. If the table is open when the JSP program runs, then the inserts will not be evident until the table is closed and reopened.

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 testCase004.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. As an illustration, consider what happens if we modify testCase003.jsp so it inserts only two values in the modified table (which expects three values for the three attributes) versus what happens if we do the same to testCase004.jsp which has the try/catch safeguards in place. The version without the try/catch pair crashes the JSP with an abnormal termination and an error report indicating the number of arguments in the insert are wrong. The try/catch version reports the error via the program's print statement which outputs: "Database Connected. java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same." The important point here is that the error did not cause the program to terminate as it did in the absence of the try/catch safety net. The catch published the error under the program's control. Then the program continued on with the rest of its processing. Since the only statements left merely closed the database and announced that fact ("Database closed"), the program then ended at that point. Similarly, if we misspell the table name as Manager rather than Managers, then the error is "caught" and the error message stored in exc is output: "java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Could not find output table 'Manager'."

Inserting Data from an HTML Form in Database using JSP

In a three-tier application, 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.

We have already seen all but one of these elements. Aspects 1 and 2 were addressed in our initial discussion of JSP and HTML pages. Aspect 4 has just been addressed previously in this section. Aspect 3 merely involves pasting together the variables values passed from the HTML page and the fixed string features of the insert SQL.

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 testCase008.jsp. For simplicity, initially assume the Access table has a single text attribute whose value is picked up from the Form. The example testCase008.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>");

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.

Data acquisition is done with the usual getParameter method. The JSP must know the name(s) of any Form variables it acquires, like mName. It is worth differentiating between the various names under which a value may appear. Thus, a parameter:

1.Appears on the Form as an HTML input field ? mName.

2.Is acquired in the JSP via getParameter using this name - request.getParameter ("mName"). The argument "mName" is the string name of the HTML variable. If the HTML field name referred to in getParameter does not match the actual field name, then the method returns a null string but does not cause an error.

3.Is stored as a JSP String variable ? name.

4.Corresponds to an attribute in the Managers table. This attribute name is not required by the insert query since the insert parameters depend on the order of the attributes in the table and not on their names. In this case the attribute in the Managers table happens to be called name, but it could be anything and is not even explicitly referenced.

The query could also be constructed in analogy to a fixed query string. Thus a fixed string like:

"INSERT INTO Managers VALUES ( 'Mimi' )"

"INSERT INTO Managers VALUES (" + name + ")"

where the constant 'Mimi' is replaced by the variable that holds its value and where single quotes (for a text attribute) have previously been attached to the variable name. Notice how the query string is;


where the string mName value is Mimi ? without quotes ? while the insert executed by the JSP program is INSERT INTO Managers VALUES ( 'Mimi' ) where the single quotes have been pasted on the inserted value 'Mimi'.

Now let us modify the JSP (as shown in testCase007.jsp) to acquire and insert the other fields on the HTML Form: mAge and mSalary, as well as mName. A question that arises is whether and how the data from the Form has to converted. As far as the Form is concerned, all these variable are of type string. However, the JSP program has to convert them to the same type as their corresponding attributes have in the database table Managers. These are name (text), age (number), and salary (currency). The string data from the Form is already of type, so no conversion is required (although the string does need to be prepped with the single quotes). The number data from the Form is originally string data and so has to be converted to Integer data.

The additional JSP data conversion statements for the attribute of type number are:
String sAge = request.getParameter ("mAge");
sAge = sAge.trim ( );
int mAge = Integer.parseInt (sAge);

This can be implanted in the insert query in the same way the string variable name was.

The currency data comes from the Form as string data, but can be inserted in the data base in two ways. It can be passed as a string with a leading $ - or alternatively it can be interpreted as decimal data. In the latter case, the decimal data from the Form can be converted to a floating point number (type double) and passed to the database that way. The conversion syntax (testCase007.jsp) is:

String sSalary = request.getParameter ( "mSalary" );
sSalary = sSalary.trim ( );
double mSalary = Double.parseDouble ( sSalary );

which is analogous to the integer insertion conversion. On the other hand, if the data is handled as a string with a leading $ in front, then the JSP statements are:

String sSalary = request.getParameter("mSalary");
sSalary = sSalary.trim( );
sSalary = "'" + sSalary + "'";

as illustrated in testCase009.jsp. Incidentally, if $ input is sent from the Form the attribute=value pair in the query string submitted to the server by the Form is:

mSalary =%24175000.25

for $175000 as the input where the form-url-encoding expresses the $ as %24, versus:


for the decimal data representation of the salary. In either case the insert is constructed the same way:

String s = "INSERT INTO Managers VALUES (" ;
s += name ;
s += "," ;
s += sAge ;
s += "," ;
s += sSalary ;
s += ")" ;

The insert can also be constructed analogously to a fixed data insert. Thus:

INSERT INTO Managers VALUES ( 'Mimi', 25, '$175000' ) ;


INSERT INTO Managers VALUES ("+name+ "',"+mAge+","+ sSalary+")" ;

when the constants are replaced by variables.

Hint: As a shortcut technique to expedite developing and testing examples, you can directly invoke a JSP program and send it data directly in the query string instead of invoking it from an HTML Form. This way you do not have to re-write a new Form to invoke a new JSP program each time: you can just play with the Form-query-strings and make trivial modifications to previously submitted Form-query-strings. You can even send the JSP program attribute value pairs that it does not acquire and just ignores: there does not even have to be an exact match with preceding Form-query-strings, though any HTML input transmitted does have to have the same name as the JSP program expects. For example testCase008.jsp needs only the name variable from a 'Form' but the following query string still serves even with its excess attribute value pairs:


You can manually modify this to the query-string needed by testCase007.jsp which requires all the variables, with the currency expected to have a leading $ sign:


You can also make hyperlinks on an HTML page that do the same thing ? invoking a JSP program when clicked and simultaneously providing the JSP program with previously determined data.

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 (for convenience). 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 Mimi, the fixed SQL is:

Delete From Managers Where name = 'Mimi'

Observe that if there are multiple records where the condition is satisfied then all those records are deleted. The example testCase010.jsp picks up the name data from the HTML Form and constructs the query:

"Delete From Managers Where name = " + name

where the name variable has single quotes already attached. If the table contains no records satisfying the where condition, then nothing happens though the query still completes successfully. The where condition can of course be more complex, involving multiple Boolean conditions. Example testCase011.jsp illustrates a where clause with data from an HTML Form in which deletion depends both on the name and age of the individual. The delete SQL is constructed with:

String s = "DELETE FROM Managers WHERE name =";
s += name ;
s += " AND " ;
s += " age =" ;
s += mAge ;

The variables name and mAge are the Java names for the Managers table attributes name and age. The query depends on both the table attribute identifiers (name & age) and the identifiers for the Java variables whose values are used (name and mAge) and which in turn have been picked up from the HTML Form input fields (mName and mAge). Make sure you understand how the where condition is constructed since it is potentially confusing ? especially given the partial overlap between the identifiers from the HTML Form, the JSP variables, and the database table attributes.

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 (one word, note the capitalization). 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: ( ) 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 testCase012 where a fixed query is defined. The relevant code is:

String s = "SELECT * FROM Managers";
ResultSet r = stm.executeQuery(s);

while ( ) )
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 ) 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 point 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, ) returns False and the while terminates. The entire process can be included in a try/catch combination for safety. The example testCase017.jsp combines an insert with a select, so you can verify the insert was done by seeing the revised contents of the database displayed.

The attribute age was a number in the table Managers but we retrieved it as a string. If we wanted to do some arithmetic on this value, we would first have to convert it back to an integer. To simplify this, we can also retrieve an attribute in a way that is consistent with its data type. For example, we could retrieve age using:

int N = r.getInt ( "age" );

which stores age in a Java integer N. Likewise, we can retrieve the currency attribute salary using:

double d = r.getFloat ("salary" );

Refer to testCase013 for examples.

Summarizing the ResultSet syntax:

ResultSet r ;
Declares object r ? which contains rows returned by Select query.

stm.executeQuery (s)
Executes Select query s ? contrast executeUpdate for updates like Insert. ( )
Returns next row of results retrieved by Select for use by r.getString( ).

r.getString ( "name" )
Returns named attribute from currently pointed to row of results. This does not have to be in same order as the order f the attribute in table. It works even if the attribute is not a string (such as type number or currency).

int N = r.getInt ( "age" )
Returns named attribute as Java integer assuming it is of type number in the database table.

double D = r.getFloat ( "salary" )
Returns named attribute as Java double assuming it is of type currency in the database table.

We can of course dynamically manufacture HTML as data is retrieved from the data base, for example embedding it in a table for better display. We can enhance the basic testCase012 (see testCase014.jsp) by adding table tags and attributes as follows

String s = "SELECT * FROM Managers";
ResultSet r = stm.executeQuery(s);

out.println ("<table border=5 bordercolor='cyan' >" );
out.println ("<tr span = 3><th><Results Table</th></tr>");
out.println ("<tr><th>Name</th> <th>Age</th></th> <th>Salary</th></tr>");

while( ( ) )
out.println("<tr><th>" + r.getString ("name") + "</th>" );
out.println( "<th>" + r.getString ("age") + "</th>");
out.println( "<th>" + r.getString ("salary") + "</th></tr>");

out.println("</table>" );

The spanned row is not appearing in testCase014.jsp. See if you can spot the cause of the error.

Additional SQL operations in JSP Context

SQL allows many other operations other than the inserts, deletes, and selects we have illustrated so far. You can also sort tables, create tables, remove tables, apply Access-based arithmetic functions, and enforce password connection to the database.

1. Sort: The select query allows an Order By clause that lets you sort the retrieved table of results by attribute. The example testCase018.jsp uses the query:

Select * From Managers Where age > 0 Order By name

which has the Order By clause on the name attribute. The default is ascending order; putting the modifier DESC after the attribute produces descending order. Multiple attributes/modifier combinations can be used.

2. Create Table You can also use the Create table query to create and define the attributes and types of a table. See testCase019.jsp which creates a table, inserts a record in the table, and then retrieves the rows of the table. The SQL for the query is:

Create Table Players ( pName char(22), pNumber integer )

where the query provides the name of the table, its attributes, ad their types in this case text (char(22)) and number (integer).

3. Arithmetic: The select SQL can also be used to do arithmetic calculations as illustrated in testCase020.jsp. In this case to retrieve the results, we have to use the getString method using the cardinality of the retrieved 'attribute' in the results table. This is one since it is the 'first' (and only) column in the returned table of results, whence we use the notation:

The example testCase021 illustrates the situation where a single scalar value (the sum of the salaries) is returned by the select: "Select sum(salary) From Managers". As we have indicated previously, this type of capability is very important since it relieves the Java programmer of having to develop the corresponding, albeit simple, algorithms.

4. Password protection on Database Password protection on Database

5. Accessing Metadata Password protection on Database

The following is part of an error page sent by Tomcat. It occurred because the database referred to in the Java program did not exist or was not set up as an ODBC data source yet. Only a few of the report lines are included here. The partial report is:

Apache Tomcat - common error reports:

Apache Tomcat/4.0.1 - HTTP Status 500 - Internal Server Error

type Exception report
message Internal Server Error
description The server encountered an internal error (Internal Server Error) that prevented it from fulfilling this request.
at org.apache.jsp.apacheError$jsp._jspService(apacheError$
at org.apache.jasper.runtime.HttpJspBase.service(

The first line number is particularly useful. It refers not to the line numbers in the original Java program, but to line numbers in an associated program built by the Java/Apache system out of the original program (It's called the 'generated servelet' and allows the program to operate in a browser-server environment.) . The first reference is:

at org.apache.jsp.apacheError$jsp._jspService(apacheError$

The source for apacheError$ is located in:

C:\Program Files\Apache Tomcat 4.0\work\localhost\myapp\chapter11

which is under the Apache program directory. If we check that source, then line no. 75 is:

Statement stm = conn.createStatement();

This statement is in the original source code. So the error that occurred is probably at or before that statement in the original source. Observe that the error or "exception" reported was:


The NullPointerException indicates there was a reference to a non-existing object occurred. Presumably this is the Connection object named "conn" referred to on this line. If we look back shortly before this statement in the original code, then we see the try/catch combination:

{ Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbc:odbc:Lab2", "", "");
catch (Exception e) { ? }

where some print statements have been omitted for clarity. The try part of this statement apparently returned a null object for conn because the driver for the DB could not locate the Lab2 data source. No error was recognized at this point, but it was recognized when the code attempted to execute the method: conn.createStatement() ? which failed because conn was empty. Once that data source was created and identified as a data source to the system, the program ran without error. When it was removed [using the admin>ODBC tool], the error appeared again.

The conclusion is that the Apache directory C:\Program Files\Apache Tomcat 4.0\work\localhost\myapp\chapter11 (or some related subdirectory) can be useful in helping to localize errors in the Java source code.

Apache Tomcat environment ? common problems:

1. BindException

This happens if port 8080 used by Tomcat is already allocated to another application - like a previously started copy of Tomcat. Useful diagnostic tools are:

- use the task-manager tool in windows [ ctrl-alt-del ]to see what processes & applications are active (such as an already running copy of Tomcat).
- use netstat -a to tell what ports are allocated to current processes ? like the 8080 port used by convention for Tomcat [or sometimes 888.
use netstat ?n for another view of ports
- For a list of dos commands see Windows Help index entry:

Please post back if you have more problems or questions.

Collapse -

Oh Puleeeezee!

by robo_dev In reply to Happy reading.....

I would suggest trying the tool known as JSPMAKER which automatically creates web pages to connect to databases. You just allow the app to discover the schema, press 'generate' and you have a working database-access page.

I have used this product for lots of little projects.

Collapse -

I put the list on here because...

If you or others keep on giving a short answer to their problem(s) then they will not learn. In my work i have to learn how things work and why, that way i understand at least what is going on, so if an issue does come up i have at least some knowledge of the workings.

Please post back if you have more problems or questions.

Collapse -

There are these things called hyperlinks.....

by robo_dev In reply to I put the list on here be ...

If somebody has a DHCP question, do you just cut and paste all ~100 pages of RFC 2131 and walk away?

While I agree that we should not 'do people's homework' for them, a more constructive approach would be to give the OP some links to possible solutions, and some words of wisdom, if we have wisdom in the given subject area.

Related Discussions

Related Forums