Database Handling in JSP

The database interaction with the JSP page is similar to the core JDBC interaction. The complete JDBC code for the database interaction is to be written in the scriptlet tags. The following example shows the process of database handling with the JSP page. The following example will display the total hit for the current page and update the counter in the database.


 
<

%@page import="java.sql.*" %>
<%
int hitCount=0;
try{
Class.forName("org.gjt.mm.mysql.Driver");
Connection connection =DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
Statement statement = connection.createStatement();
int changed = statement.executeUpdate("update counters set hitCount = hitCount + 1 " +"where page like '" + request.getRequestURI() + "'");
if (changed == 0) statement.executeUpdate("insert counters(page) values('" + request.getRequestURI() + "')");
ResultSet rs = statement.executeQuery("select hitCount from counters where page like '" + request.getRequestURI() + "'");
rs.next();
hitCount = rs.getInt(1);
}catch(Exception e ){
}finally{
statement.close();
connection.close();
}
out.println("The hit count is " +hitCount );
%>

Getting Client Info in JSP

<html>
<head> <!-- Example5 --> <title>VisualBuilder.com</title> </head> <body> Client computer details: <br><br>
<b>Ip address</b>:
<br>
<%=request.getRemoteAddr()%>
<br><br>
<b>Computer name</b>:
<br>
<%=request.getRemoteHost()%>
<br><br>
</body>
</html>

Simple Java Servlet code to connvert km/l to miles/gallon

The following servlet will convert km/l to miles/g

package jspcr.servlets;
import java.io.*;
import java.text.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class K2MServlet extends HttpServlet
{
private static final DecimalFormat FMT = new DecimalFormat("#0.00");
private static final String PAGE_TOP = ""+ "<HTML>"+ "<HEAD>"+ "<TITLE>Fuel Efficiency Conversion Chart</TITLE>"
+ "</HEAD>"+ "<BODY>"+ "<H3>Fuel Efficiency Conversion Chart</H3>"+ "<TABLE BORDER=1 CELLPADDING=3 CELLSPACING=0>"
+ "<TR>"+ "<TH>Kilometers per Liter</TH>"+ "<TH>Miles per Gallon</TH>"+ "</TR>";
private static final String PAGE_BOTTOM = ""+ "</TABLE>"+ "</BODY>"+ "</HTML>" ;

public void doGet(
HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
out.println(PAGE_TOP);
for (double kpl = 5; kpl <= 10; kpl += 1.0) {
double mpg = kpl * 2.352146;
out.println("<TR>");
out.println("<TD>" + FMT.format(kpl) + "</TD>");
out.println("<TD>" + FMT.format(mpg) + "</TD>");
out.println("</TR>");
}
out.println(PAGE_BOTTOM);
}
}

How to fetch values from a table in a database using JSP?

To connect with a database, you need the following things.

1. A Database System.
2. A Database Connector
3. Apache Tomcat
4. jdk

The database system we use is MySQL. You can download it from the following location.

http://www.filehippo.com/download_mysql/

At the time of installation, you can configure the username and password.

After installation, you can go to Start->Programs->MySQL->MySQL Command Line Client. Give password to login.

You can create a database and table using the following commands in MySQL.

SQL> create database Company;
SQL> use Company;
SQL> create table Employee(Empid varchar(20),EmpName varchar(20),Designation varchar(20),primary key(Empid));
SQL> insert into Employee values('SCH001','Krishna','Software Engineer');
SQL> insert into Employee values('SCH002','Steve','Interface Designer');

Also we need an appropriate database connector which makes connection between jdk and MySQL.
You can download the connector from the following link.

http://mysqlmirror.netandhost.in/Downloads/Connector-J/mysql-connector-java-5.1.11.zip

Extract the contents of the zipped file to any location in your computer.

After doing this, you need to set class path in Envirornment variables. You can do that by following the steps below:

My Computer->Properties->Advanced->Environment Variables->User Variables.

You can click New and have 2 text fields: Variable Name and Variable Value.

You can give as follows:

Variable Name: classpath
Variable Value: .;C:\mysql-connector-java-5.0.8\mysql-connector-java-5.0.8-bin.jar;

Note: If you have a different version of Connector, you need to give appropriate connector name.
After this, create a JSP page with the following code. The Variable Value should also contain .; in the beginning and ; in the end. Also copy the jar file of Connector inside the lib directory of Tomcat.

Now you can write a JSP page with the following code as follows:

<!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>Connection with mysql database</title>
</head>
<body>

<%
try {

String connectionURL = “jdbc:mysql://localhost:3306/Company”;
Connection connection = null;
Class.forName(“com.mysql.jdbc.Driver”).newInstance();
connection = DriverManager.getConnection(connectionURL, “root”, “root”);
Statement st=connection.createStatement();
ResultSet rs=st.executeQuery(“select * from Employee”);
%>

<table cellpadding=”15″ border=”1″ style=”background-color: #ffffcc;”>

<%
while (rs.next()) {
%>
<tr>
<td><%=rs.getString(1)%></td>
<td><%=rs.getString(2)%></td>
<td><%=rs.getString(3)%></td>
</tr>
<% } %>
<% }

catch(Exception ex){
out.println(“Unable to connect to database.”);
}
%>

</body>
</html>

Place the JSP file inside a folder which should be resided in C:\Program Files\Apache Software Foundation\Tomcat 6.0\webapps\MyDatabase

Here I gave the folder name as “MyDatabase”.

Make sure that the Tomcat has been started, Take any browser, type the folowing link.

http://localhost:8080/MyDatabase/DatabaseDemo.jsp

You can see the values in the JSP Page in the form of a Table.

How to create a simple application in JSP?

To work with a J2EE application, you need two things in your system.

1. jdk
2. Apache Tomcat

Since both of them are open source you can download it from the Net. You can try the links below:

http://java.com/en/download/
http://tomcat.apache.org/download-60.cgi

After installing jdk 1.5 or latest version, you can also install Apache Tomcat of versions 5.0 or 6.0.
During installation of Tomcat, the default port number is configured as 8080. You can also change port number if the default port number is already allocated and conflict occurs.

To change the port number after installation, go to the default path where tomcat has been installed. In general, it resides in C:\Program Files\Apache Software Foundation\Tomcat 6.0.

Inside Tomcat, you can view the server.xml file; locate the following segment.

<!– Define a non-SSL HTTP/1.1 Connector on port 8080 –>
<Connector port=”8080″ … />

Here you can change the port number to 8081 or 8082 or any appropriate port numbers.

After installation of Tomcat, you can Start Apache Tomcat from the Start->Programs Menu.

To check whether Tomcat is running perfect, you can use any browser and give the following address in the address bar.
http://localhost:8080/

If it is running perfect, a Tomcat Home Page will be shown, otherwise it will display an error page.

Now we can create a simple application in J2EE. Here we are going to design a simple HTML page where a user can input 3 fields like Name, Age and Place. When the user clicks the submit button on the HTML Page, it will be navigated to a JSP Page where those details are shown.

The steps are as follows:

1. Create a HTML file with extension .html either in notepad or any other editor with the following code. [Here I use Form.html]

<html>  <body>      <form action="MyFirst.jsp" method="post">    <table>  	<tr>  		< td >Name</td>  		< td ><input type="text" name="name" /></td>  	</tr>    	<tr>  		<td > Age</td>  		<td ><input type="text" name="age" /></td>  	</tr>    	<tr>  		< td >Place:</td>  		< td ><input type="text" name="place" /></td>  	</tr>    	<tr>  		< td ><input type="submit" value="submit"></td>  		< td ><input type="reset" value="reset"></td>  	</tr>  </table>    </form>      </body>  </html>

2. Create a JSP(Java Server Page) with name “MyFirst” and extension .jsp in notepad or any other editor with the following code.

<%@ page language="java"%>    <html>  <body>      <%    String getname=request.getParameter("name");  String getage=request.getParameter("age");  String getplace=request.getParameter("place");      out.println("Name:"+getname);  out.println("Age:"+getage);  out.println("Place:"+getplace);    %>    </body>  </html>

3. Go to the location where the Tomcat has been installed. You can see a webapps folder. Create a new folder(Here I used Test) inside that and

copy these 2 files in to that.

4. Take any browser ie., IE or Mozilla and type in address bar as http://localhost:8080/Test/Form.html

5. Insert the fields and when you click the submit button, the values will be displayed on the JSP Page.

Points to Remember:

1. Normal JSP code is enclosed with in tags which are known as scriplets.
2. request.getParameter() is a method to get values from the HTML page using the name attribute in HTML page.
3. out.println is used to display data in a JSP page.

How to create a login script in JSP without any database

First of all, you have to  create a simple login form with plain html tags and name it as login.jsp or any-name.jsp and
save it in your apache-tomcat’s webapps folder. The script for creating login.jsp given below:

[sourcecode language=”html”]
<html>
<head><title>Login</title>
</head>
<body>
<form action="main_page.jsp">
<table>
<tr><th colspan="2">Login</th></tr>
<tr>
<td>Username</td><td>:<input type="text" name="username" id="username" /></td>
</tr>
<tr>
<td>Password</td><td>:<input type="password" name="password" id="password" /></td>
</tr>
<tr>
<td>&nbsp;</td><td><input type="submit" name="btn_submit" id="btn_submit" value="Login" /></td>
</tr>
</table>
</form>
</body>
</html>
[/sourcecode]

After that, you have to add session function <% session.invalidate(); %> at the top of this login.jsp file for clearing existing session variables.

After that, you have to create the action page “main_page.jsp” (<form action=”main_page.jsp”>) and paste the following code in it:

<html>
<head><title>Main Page</title></head>
<body>

[sourcecode language=”java”]
<%
String username = request.getParameter("username");
String password = request.getParameter("password");
if( username.equals("rajeev") && password.equals("rajeev123") ) {
session.setAttribute( "sess_username", username );

%>

[/sourcecode]

<b>Welcome <%=session.getAttribute(“sess_username”) %>. <a href=”login.jsp”>Logout</a>
<% }else{ %>
<b>Incorrect username or password. <a href=”login.jsp”>Retry</a> </b>
<% } %>
</body>
</html>

and save this file in the apache-tomcat’s webapps folder.
<%
String username = request.getParameter(“username”);
String password = request.getParameter(“password”);
%>
The above two lines are used to accept values from login.jsp form (username and password).

The the following lines are used to check whether the username is “rajeev” and password is “rajeev123″ and if the username and password are correct,
then the username will be stored in a session variable “sess_username” and echo a welcome user message and a logout link.
<%
if( username.equals(“rajeev”) && password.equals(“rajeev123″) ) {
session.setAttribute( “sess_username”, username );
%>
<b>Welcome <%=session.getAttribute(“sess_username”) %>. <a href=”login.jsp”>Logout</a>
If the username and password are incorrect then the script will echo “incorrect username or password” with a retry link.