<%@ page import="java.sql.*" %>
<%
//Simplest Database Sorting (Up & Down) in JSP 
//This is the simplest and fastest code that implements table sorting in JSP (Sort Ascending & Sort Descending). 
//Excellent for JSP Database beginers

//Author: Tanwani Anyangwe
 

Connection connection = null;
Statement statement = null;
ResultSet results = null;

try {
	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");	
	String url = "jdbc:odbc:nwind";	 	
	String query = "SELECT CompanyName, ContactName, Address, City, Phone FROM CUSTOMERS";
	
	String sort = request.getParameter("sort");
	String lastsort = request.getParameter("lastsort");

	if (sort != null){
		if (lastsort.equalsIgnoreCase(sort)){
			sort += " desc";
		}
		query += " order by " + sort;
	}
	//out.println(query);
	connection = DriverManager.getConnection(url, "", "");
	statement = connection.createStatement();
	results = statement.executeQuery(query);

	int i;
	ResultSetMetaData rsmd = results.getMetaData();
	int numCols = rsmd.getColumnCount();
			
			  
	// get column header info
	out.println("<table border=1><tr>");
	for (i=1; i <= numCols; i++){   
		String fname = rsmd.getColumnName(i);
		
		out.println("<th><a href=\"" + request.getRequestURI() 
			+ "?sort=" + fname + "&lastsort=" + sort + "\">" + fname);
			
		if (sort.equalsIgnoreCase(fname)){
			out.println(" +");
		}else if(sort.equalsIgnoreCase(fname + " desc")){	
			out.println(" -");
		}
		
		out.println("</a></th>");  	
	}    
	out.println("</tr>");

	//print columns data		  
	while (results.next()){	
	  	out.println("<tr>");		
	  	for (i=1; i <= numCols; i++) {
	  	  String text = results.getString(i);
	  	  if(text == null){text="";}				  
	  	  out.println("<td>" + text + "</td>");
	  	}
	  	out.println("</tr>");		
	}
	out.println("</table>");		

	//close connection
	connection.close();
}
catch (ClassNotFoundException e) {
	System.err.println("Could not load database driver!");
}
catch (SQLException e) {
	System.err.println("Could not connect to the database!");
}
finally
{ 
    try{if(connection != null)connection.close(); }   
    catch(SQLException e){ }
}
%>