Binnur Kurt's Blog: The easiest way to retrieve data from a database and present them to web users on Java EE Platform

If your aim is to just retrieve information from a database and then present them to a web user on Java EE platform, probably the easiest way is to use Java Server Pages (JSP) and JSP Standard Tag Library (JSTL) technologies. JSP is a web component model. It heavily uses HTML, CSS, Javascript technologies to present the model to the web user. JSP is a presentation technology. In design time it is basically an HTML file. Though you can insert any java scriptlet. All JSP components are translated in run-time to a Servlet class and then the translated java codes are compiled into bytecodes by the web container . This process is done when the JSP page is requested for the first time. It is possible to tell the web container to do the translation and compilation during the deployment in order to enhance the response time . JSPs and Servlets are different components in design-time, the same component in run-time. JSP pages are dynamic while Servlets are static components. Whenever you make a change in JSP page, the web container detects the change and re-translate and re-compile the page into bytecode ready to be run on JVM.

MVC is an architectural design pattern used in both desktop and web applications. Due to its distributed nature, implementation of MVC pattern in web applications is somewhat different from the one used in desktop applications. For example any model update does not directly reflect to the web page in the client machine. It requires a request-response cycle. So observer pattern is not used in traditional web applications. Observer pattern in web applications is usually implemented by employing Ajax-push technology or by using Web sockets of HTML 5. Model in MVC is implemented by JavaBeans component model.

The JavaServer Pages Standard Tag Library (JSTL) is a collection of useful JSP tags which encapsulates core functionality common to many JSP applications. One of these functionalities is found in sql tag library:
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
In order to use this tag library, first you must define a a data source (connection pool). In our example, we use MySQL database server:
<sql:setDataSource driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/world"
user="root" password="root"/>
Using a connection from the connection pool, we can send a valid SQL statement to the server:
<sql:query var="continents">
SELECT DISTINCT CONTINENT FROM COUNTRY
</sql:query>
The result set is stored in the variable continents. Another example is given below:
<c:if test="${not empty param.continent}">
<sql:query var="countries">
SELECT * FROM COUNTRY WHERE continent= '${param.continent}'
</sql:query>
</c:if>
where the result set stored in the variable countries contains countries in the selected continent ${param.continent}.
In the sample application we ask for a continent from the user. User selects the continent and then press the List button. The page displays the countries in the selected continent. The complete solution is given in the following Jsp code, index.jsp:

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<sql:setDataSource driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/world"
user="root"
password="root"/>
<sql:query var="continents">
SELECT DISTINCT CONTINENT FROM COUNTRY
</sql:query>
<c:if test="${not empty param.continent}">
<sql:query var="countries">
SELECT * FROM COUNTRY WHERE continent= '${param.continent}'
</sql:query>
</c:if>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>World Countries</title>
<script type="text/javascript" src="js/jquery-1.8.3.min.js"></script>
<style>
tr.zebraHover { background-color: #FFFACD; }
tr.striped_even { background-color: #EFEFEF}
tr.striped_odd { background-color: #DFDFDF}
</style>
<script type="text/javascript">
$(document).ready(
function () {
$('tbody tr:even').mouseover(function(){
$(this).addClass('zebraHover');
$(this).removeClass('striped_even');
});
$('tbody tr:even').mouseout(function(){
$(this).removeClass('zebraHover');
$(this).addClass('striped_even');
});
$('tbody tr:odd').mouseover(function(){
$(this).addClass('zebraHover');
$(this).removeClass('striped_odd');
});
$('tbody tr:odd').mouseout(function(){
$(this).removeClass('zebraHover');
$(this).addClass('striped_odd');
});
$('tbody tr:odd').addClass('striped_odd');
$('tbody tr:even').addClass('striped_even');
}
);
</script>
</head>
<body>
<form action="index.jsp" method="POST">
<select name="continent">
<c:forEach items="${continents.rows}" var="row">
<option value="${row.continent}">${row.continent}</option>
</c:forEach>
</select>
<input type="submit" value="List"/>
<br/>
<c:if test="${not empty countries}">
<table border="0">
<thead>
<tr>
<c:forEach items="${countries.columnNames}" var="headerName">
<th>${headerName}</th>
</c:forEach>
</tr>
</thead>
<tbody>
<c:forEach items="${countries.rows}" var="row">
<tr>
<c:forEach items="${countries.columnNames}" var="columnName">
<td>${row[columnName]}</td>
</c:forEach>
</tr>
</c:forEach>
</tbody>
</table>
</c:if>
</form>
</body>
</html>

The solution contains jQuery to add zebra effect to the table. You can download jQuery from http://www.jquery.com. Sample screen shots are given below:

You can download world database following the link. You can read the pdf to learn how to install MySQL and the world database. Changing this solution to Ajax-based application is simple. First we partition index.jsp into search.jsp and countries.jsp. Second, we add jquery code which sends the request asynchronously and makes partial update when the response is received:

$('#listButton').click(function(){

var continent= $('#selectContinent').val();

$.ajax({

method: "GET",

url: "countries.jsp",

data: "continent="+continent,

success: function(responseData){

$('#result').fadeOut('fast');

$('#result').html(responseData).fadeIn('slow');

}

});

countries.jsp sends only the table containing the countries in the selected continent.

search.jsp:

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<sql:setDataSource driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/world"
user="root"
password="root"/>
<sql:query var="continents">
SELECT DISTINCT CONTINENT FROM COUNTRY
</sql:query>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>World Countries</title>
<script type="text/javascript" src="js/jquery-1.8.3.min.js"></script>
<style>
tr.zebraHover { background-color: #FFFACD; }
tr.striped_even { background-color: #EFEFEF}
tr.striped_odd { background-color: #DFDFDF}
</style>
<script type="text/javascript">
$(document).ready(
function () {
$('tbody tr:even').mouseover(function(){
$(this).addClass('zebraHover');
$(this).removeClass('striped_even');
});
$('tbody tr:even').mouseout(function(){
$(this).removeClass('zebraHover');
$(this).addClass('striped_even');
});
$('tbody tr:odd').mouseover(function(){
$(this).addClass('zebraHover');
$(this).removeClass('striped_odd');
});
$('tbody tr:odd').mouseout(function(){
$(this).removeClass('zebraHover');
$(this).addClass('striped_odd');
});
$('tbody tr:odd').addClass('striped_odd');
$('#result table tbody tr:even').addClass('striped_even');
$('#listButton').click(function(){
var continent= $('#selectContinent').val();
$.ajax({
method: "GET",
url: "countries.jsp",
data: "continent="+continent,
success: function(responseData){
$('#result').fadeOut('fast');
$('#result').html(responseData).fadeIn('slow');
}
});
});
}
);
</script>
</head>
<body>
<select id="selectContinent" name="continent">
<c:forEach items="${continents.rows}" var="row">
<option value="${row.continent}">${row.continent}</option>
</c:forEach>
</select>
<input id="listButton" type="submit" value="List"/>
<div id="result">

</div>
</body>
</html>

countries.jsp:

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>

<sql:setDataSource driver="com.mysql.jdbc.Driver"

url="jdbc:mysql://localhost:3306/world"

user="root"

password="root"/>

<c:if test="${not empty param.continent}">

<sql:query var="countries">

SELECT * FROM COUNTRY WHERE continent= '${param.continent}'

</sql:query>

</c:if>

<c:if test="${not empty countries}">

<table border="0">

<thead>

<tr>

<c:forEach items="${countries.columnNames}" var="headerName">

<th>${headerName}</th>

</c:forEach>

</tr>

</thead>

<tbody>

<c:forEach items="${countries.rows}" var="row">

<tr>

<c:forEach items="${countries.columnNames}" var="columnName">

<td>${row[columnName]}</td>

</c:forEach>

</tr>

</c:forEach>

</tbody>

</table>

</c:if>

Binnur Kurt's Blog

Wednesday, January 16, 2013

The easiest way to retrieve data from a database and present them to web users on Java EE Platform

No comments:

Post a Comment