Joins tables in jsp

When we want to select data from two or more tables to make our result complete. We have to perform a join between tables.

Joins tables in jsp

Joins tables in jsp

     

When we want to select data from two or more tables to make our result complete. We have to perform a join between tables. In this example we have two tables stu_info and stu_marks. Structure of these tables are as follows.

 

stu_info
mysql> create table stu_info (
  -> ID INT NOT NULL Auto_increment,
  -> Name  varchar(20), Address varchar(20),
  -> Phone varchar(15), primary key(ID));
+----+----------+---------+------------+
| ID | Name     | Address | Phone      |
+----+----------+---------+------------+
| 1  | mahendra | Delhi   | 9990254913 |
| 2  | girish   | Delhi   | 123456     |
| 3  | anu      | Delhi   | 1234567    |
| 4  | komal    | Delhi   | 12345678   |
+----+----------+---------+------------+

 

stu_marks
mysql> create table stu_marks ( 
  -> stu_id int (3) NOT NULL AUTO_INCREMENT,
  -> english int (3) , maths int (3) ,
  -> computer int (3) , science int (3) ,
  -> PRIMARY KEY (stu_id));
+--------+---------+-------+----------+---------+
| stu_id | english | maths | computer | science |
+--------+---------+-------+----------+---------+
| 1      | 84      | 82    | 76       | 95      |
| 2      | 63      | 87    | 78       | 85      |
| 3      | 60      | 89    | 87       | 42      |
| 4      | 82      | 78    | 87       | 78      |
+--------+---------+-------+----------+---------+

 

Here are two tables and result will be from both tables on the basis of 'ID' which is primary key of the first table. Before running this java code you need mysql-connector-java-3.1.6-bin.jar file in the jdk1.6.0_01\lib and set class path to this  file.

table_joins_jsp.jsp

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
"http://www.w3.org/TR/html4/loose.dtd"> 
<%@ page import="java.sql.*" %> 
<%@ page import="java.io.*" %> 
<HTML>
<HEAD>
    <TITLE>joins of two tables through jsp</TITLE>
</HEAD>
<BODY bgcolor="#6E6E6E">
<font size="+3" color="#F6CECE"><br>Welcome in www.roseindia.net !</font>
<br>
<font size="+1" color="#F2F2F2"><br>Joins of two tables</font>
      <%   int current = 0;
       // declare a connection by using Connection interface 
     Connection connection = null;/* Create string of connection url within specified format with machine 
     name, port number and database name. Here machine name id localhost 
     and database name is student. */
          String connectionURL = "jdbc:mysql://localhost:3306/student";
               /*declare a resultSet that works as a table resulted by execute a specified 
     sql query. */
     ResultSet rs = null;
%>
<FORM NAME="form1" ACTION="table_joins_jsp.jsp" METHOD="GET"><%
    if (request.getParameter("hidden") != null) {
                current = Integer.parseInt(request.getParameter("hidden"));
      }
            // Declare statement.
       Statement statement = null;
       try {
               // Load JDBC driver "com.mysql.jdbc.Driver".
         Class.forName("com.mysql.jdbc.Driver").newInstance();
                 /* Create a connection by using getConnection() method that takes 
	 parameters of string type connection url, user name and password to 
	 connect to database. */
         connection = DriverManager.getConnection(connectionURL, "root", "root");
                  /* createStatement() is used for create statement object that is 
	 used for sending sql statements to the specified database. */
         statement = connection.createStatement();
                // executeQuery() method execute specified sql query. 
         rs = statement.executeQuery("select * from stu_info, stu_marks where "+
			"stu_info.ID=stu_marks.stu_id");
			         for (int i = 0; i < current; i++) {
           rs.next();
            }
              if (!rs.next()) {
%>
<FONT size="+2" color="red"></b>
<%
        out.println("Sorry ! found some problems with database.");
    } else {
%>
<TABLE style="background-color: #D8D8D8;" WIDTH="30%">
    
    <TR><TH width="50%">ID</TH><TD width="50%"> <%= rs.getInt(1)%> </TD></tr>
    <TR><TH>Name</TH><TD> <%= rs.getString(2)%> </TD></tr>
    <TR><TH>City</TH><TD> <%= rs.getString(3)%> </TD></tr>
    <TR><TH>Phone</TH><TD> <%= rs.getString(4)%> </TD></tr>
	<TR><TH align="left"><font color="#DF0101">MARKS</font></TH><TD></TD></tr>
	<TR><TH>English</TH><TD> <%= rs.getInt("english")%> </TD></tr>
	<TR><TH>Maths</TH><TD> <%= rs.getInt("maths")%> </TD></tr>
	<TR><TH>Computer</TH><TD> <%= rs.getInt("computer")%> </TD></tr>
	<TR><TH>Science</TH><TD> <%= rs.getInt("science")%> </TD></tr>
</TABLE>
<BR>
<INPUT TYPE="hidden" NAME="hidden" VALUE="<%=current + 1%>">
<INPUT TYPE="submit" VALUE="next record">
</FORM>
<%
    }
} 
catch (Exception ex) {
%>
<FONT size="+3" color="red"></b>
    <%
                out.println("Unable to connect to database.");
            } finally {
                // close all the connections.
                rs.close();
                statement.close();
                connection.close();
            }
    %>
</FONT>
</FORM>
</body> 
</html>

Create a new directory in Tomcat-6.0.16/webapps/ and Save this code with the name "table_joins_jsp.jsp" in Tomcat-6.0.16/webapps/user along with WEB-INF directory. Start tomcat server and type url 'http://localhost:8080/user/table_joins_jsp.jsp' in address bar of browser and run.

When user click on button 'next record' this page will show next record from the database.

Download Source Code