Shorting Table View By Column Name


 

Shorting Table View By Column Name

This tutorial explains how to shorting table view by column name from the database in JSP and Servlet. This example is created in eclipse IDE and run on tomcat server.

This tutorial explains how to shorting table view by column name from the database in JSP and Servlet. This example is created in eclipse IDE and run on tomcat server.

Shorting Table View By Column Name

This tutorial explains how to shorting table view by column name from the database in JSP and Servlet. This example is created in eclipse IDE and run on tomcat server. The application directory structure should look like as below, if you are developing the application in Eclipse.

shortingtableviewbycolumn1.gif

This tutorial applies following steps.

Step 1 :

First we create database and table in mysql that is used in this example.

create database if not exists `user_information`;

USE `user_information`;

CREATE TABLE `user` (
`user_id` bigint(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(100) DEFAULT NULL,
`user_email` varchar(100) DEFAULT NULL,
`password` varchar(50) DEFAULT NULL,
`address` text,
PRIMARY KEY (`user_id`)
)

Step 2 :

First we create Dynamic web project "shorting_tableview_bycolumn" in eclipse IDE . Again we create "index.jsp" file under WebContent folder. The code of "index.jsp" are given as:

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"

pageEncoding="ISO-8859-1"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"

"http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">

<title>Shorting Table View By Column</title>

</head>

<body>

<li><a href="userdetails">User Details</a></li>

</body>

</html>

Step 3 :

Now create "userdetails.jsp"  jsp file under WebContent folder. The file "userdetails.jsp" used for displays shorting  table view by column name. The code of "userdetails.jsp"  given below as:

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"

pageEncoding="ISO-8859-1"%>

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

<%@page import="net.roseindia.User;" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"

"http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">

<title>Shorting Table View By Column</title>

</head>

<body>

<h2 align="center">User Details</h2>

<table align="center">

<%

List<User> userlist=new ArrayList<User>();

userlist=(ArrayList)request.getAttribute("userlist");

String colum=request.getParameter("colum");

if(userlist!=null && userlist.size()>0 ){

%>

<tr>

<th><a href="userdetails?colum=userid" >User Id</a></th>

<th><a href="userdetails?colum=username" >User Name</a></th>

<th><a href="userdetails?colum=useremail" >User Email</a></th>

<th><a href="userdetails?colum=pass" >Password</a></th>

<th><a href="userdetails?colum=addr" >Address</a></th>

</tr>

<%

for(int i=0;i<userlist.size();i++){

User user=new User();

user=(userlist.get(i));

%>

<tr>

<td><%=user.getUserId()%></td>

<td><%=user.getUserName()%></td>

<td><%=user.getUserEmail()%></td>

<td><%=user.getPassword()%></td>

<td><%=user.getAddress()%></td>

<%

if(colum!=null && !colum.equals("")){

%>

<td><a href="userdetails?colum=<%=colum%>&userid=<%=user.getUserId()%>" >Delete</a></td>

<%

}else {

%>

<td><a href="userdetails?userid=<%=user.getUserId()%>" >Delete</a></td>

<%

}

%>

</tr>

<%

}

}else{

%>

Not Available User Details

<%}%>

</table>

</body>

</html>

Step 3 :

Now create pojo class "User.java"  under  " src/net/roseindia" directory . The code of "User.java" given below :

 

package net.roseindia;

import java.io.Serializable;

public class User implements Serializable{
  private Integer userId;
  private String userName;
  private String userEmail;
  private String password;
  private String address;
  
  public void setUserId(Integer userId){
	 this.userId=userId;
   }
  public Integer getUserId(){
	 return userId;
   }
  
  public void setUserName(String userName){
	 this.userName=userName;
   }
  public String getUserName(){
	 return userName;
	 }
  
  public void setUserEmail(String userEmail){
	 this.userEmail=userEmail;
   }
  public String getUserEmail(){
	 return userEmail;
   }
  
  public void setPassword(String password){
	 this.password=password;
   }
  public String getPassword(){
	 return password;
   }
  
  public void setAddress(String address){
	 this.address=address;
   }
  public String getAddress(){
	  return address;
   }
}

Step 4 :

Now create servlet  class "UserDetails.java"  under  "src/net/roseindia" directory . The class "UserDetails.java" used for fetch  data from database and send to "userdetails.jsp" page. The code of  "UserDetails.java" given below "

package net.roseindia;

import java.io.*;
import java.sql.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;

import net.roseindia.User;

public class UserDetails extends HttpServlet {
  public UserDetails(){
	  super();
    }
  protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
	// TODO Auto-generated method stub
	response.setContentType("text/html");
	List userlist=new ArrayList();
	
	Connection con = null;
	String url = "jdbc:mysql://localhost:3306/";
	String db = "user_information";
	String driver = "com.mysql.jdbc.Driver";
	String username = "root";
	String pass = "";
	
	String userIdInString=request.getParameter("userid");
	try{
	   Class.forName(driver);
	   con=DriverManager.getConnection(url+db, username, pass);
	  }catch (Exception e) {
		  e.getStackTrace();
	  }
	
	if(userIdInString!=null && !userIdInString.equals("")){
	  Integer userId=Integer.parseInt(userIdInString);
	  String delQuery="delete from user where user_id="+userId;
	  try{
		Statement sel_st=con.createStatement();
		sel_st.executeUpdate(delQuery);
		sel_st.close();
	   }catch (Exception e) {
		   e.printStackTrace();
	    }
	 }
	
	String queary="SELECT * FROM user ";
	String columnname=request.getParameter("colum");
	if(columnname!=null && !columnname.equals("")){
	 if(columnname.equals("userid")){
	   queary+=" ORDER BY user_id asc";
	  }
	 if(columnname.equals("username")){
	   queary+=" ORDER BY user_name asc";
	  }
	 if(columnname.equals("useremail")){
	   queary+=" ORDER BY user_email asc";
	  }
	 if(columnname.equals("pass")){
	   queary+=" ORDER BY password asc";
	  }
	 if(columnname.equals("addr")){
	   queary+=" ORDER BY address asc";
	  }
     }
	
	try{
	 Statement st = con.createStatement();
	 ResultSet rs = st.executeQuery(queary);
	 while (rs.next()) {
	  User user=new User();
	  user.setUserId(rs.getInt(1));
	  user.setUserName(rs.getString(2));
	  user.setUserEmail(rs.getString(3));
	  user.setPassword(rs.getString(4));
	  user.setAddress(rs.getString(5));
	  userlist.add(user);
	 }
	}catch(SQLException s){
	  System.out.println("SQL statement is not executed!");
	}	
	request.setAttribute("userlist", userlist);
	RequestDispatcher dispatcher=getServletContext()
	.getRequestDispatcher("/userdetails.jsp");
	dispatcher.forward(request, response);
  }
}

Step 5 :

Now download "mysql-connector-java-5.0.5.jar" jar file and paste under  "WebContent/WEB-INF/lib" directory .

Step 6 :

Now you can open open "web.xml" file and modify as :

<?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns="http://java.sun.com/xml/ns/javaee"

xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"

xsi:schemaLocation="http://java.sun.com/xml/ns/javaee

http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">

<display-name>shorting table view by column</display-name>

<servlet>

<servlet-name>UserDetails</servlet-name>

<servlet-class>net.roseindia.UserDetails</servlet-class>

</servlet>

<servlet-mapping>

<servlet-name>UserDetails</servlet-name>

<url-pattern>/userdetails</url-pattern>

</servlet-mapping>

<welcome-file-list>

<welcome-file>index.jsp</welcome-file>

</welcome-file-list>

</web-app>

Step 6 :

When run  this application on tomcat server displays output as:

shortingtableviewbycolumn2.gif

Again you click hyperlink "User Details" then displays output as :

shortingtableviewbycolumn3.gif

When you click hyperlink column name "User Email", then  table view shorting by column "User Email" and  displays table view as:

shortingtableviewbycolumn4.gif

Similarly if you select click column name "User Id", "User Name" , "Password" or "Address" then table view shorting by column name. If you want delete any row then click "Delete" hyperlink. If delete one row then display output as:

shortingtableviewbycolumn5.gif 

Download Code

Download example code

Ads