This is the next article in the series of articles about database applications in JSP. In this article, I am going to develop an
application that joins two database tables. Through this article we join two tables
(authors and titleauthor). For this application we use the following steps.
Step 1: Create a new database
In the first step we click on the blank database option and click on create .
![make a new database.jpg]()
Step 2: Design the database
In this step we select field name and data type for the those fields. In this
application we select five fields (id, name, city, state and country).
![desing view.jpg]()
Step 3: Make DSN
In this step we make DSN using the following process.
First we select the Control Panel option from start.
![select the control panel.jpg]()
Then click on the administrative tools.
![click on administration tools.jpg]()
Select Data Sources option.
![select data source option.jpg]()
Then select Microsoft Access Driver(*.mdb,*.accdb), and click on the finish
button.
![create new data source.jpg]()
Then select database from the computer and give it a specific name. Now the DSN
is created.
![select database.jpg]()
Step 4: Create a New Project
In this step we select New Project option from File menu.
![create new project.jpg]()
Step 5: Choose Project
In this step we select web application from Java web option and then click on
the next button.
![select new web application.jpg]()
Step 6: Name and Location
In this step we give it a specific name and set a specific location and click
on the next button.
![nameand location.jpg]()
Step 7: Server and Setting
We select a specific server for this application and click on the next button.
![server and setting.jpg]()
Step 8: Select Framework
There is no need to select any framework for this application; just click on the
finish button.
![selectframework.jpg]()
Step 9: Create jsp file
We create one jsp file for this application.
![create new jsp file.jpg]()
join.jsp
<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %>
<HTML>
<HEAD>
<TITLE>Joining Tables</TITLE>
</HEAD>
<BODY BGCOLOR="green">
<H1>Joining Tables</H1>
<%
Connection connection =
DriverManager.getConnection(
"jdbc:odbc:joindata1");
Statement statement =
connection.createStatement();
String query =
"SELECT * From authors,titleauthor where authors.id =
titleauthor.id";
ResultSet resultset =
statement.executeQuery(query);
%>
<TABLE BORDER="1">
<TR>
<TH>First Name</TH>
<TH>Last Name</TH>
<TH>Book ID</TH>
</TR>
<% while(resultset.next()){ %>
<TR>
<TD> <%= resultset.getString(1)
%></TD>
<TD> <%= resultset.getString(2)
%></TD>
<TD> <%= resultset.getString(3)
%></TD>
</TR>
<% } %>
</TABLE>
</BODY>
</HTML>
Step 10: Compile and Run the application
Now we compile the application and then run it on the server and
find the following output.
Output
join.jsp
![output.jpg]()
Resources related to this article