In the below JSP program the aim is to understand how JSP communicates with the database. The designing is done by HTML.
HTML Program
<html>
<title>DB-JSP</title>
<form action= “DB-JSP.jsp”>
<body bgcolor= ‘white’>
<h1 align=”center”><font color= ‘Black’ size=’8′ >Search Employee Details</font></h1>
<br><Center><font color= ‘Gray’ size= ‘6’ >Enter Employee Id</font>
<input type=’text ‘ size= ’20’ name=’t1′>
<input type=’Submit’ value=’Submit’ name=”b1″ />
<input type=’Reset’ value= ‘RESET’ name= ‘b2’/>
</center>
</body>
</form>
</html>
Output:
JSP Program
<%@ page import=”java.sql.*” %>
<%
try
{
String driver = “com.mysql.jdbc.Driver”;
String url = “jdbc:mysql://localhost/dbase”;
String uid = “root”;
String psw = “root”;
Class.forName(driver) ;
Connection con=DriverManager.getConnection (url,uid,psw) ;
Statement st=con.createStatement ();
String empno=request.getParameter(“t1”);
ResultSet rs = st.executeQuery(“select * from emp where EMPNO=”+empno);
if (rs.next ())
{
out.println(“<center><table border=’15’ color=’red’><tr><th>EMPNAME
</th><th>EMPNO</th><th>SAL</th>
<th>EMPADD</th><th>MAILID</th><th>PHNO.</th>< /tr>”);
out.println(“<tr><td>”+rs.getString(1)+”</td>”);
out.println(“<td>”+rs.getString(2)+”</td>”);
out.println(“<td>”+rs.getString(3)+”</td>”);
out.println(“<td>”+rs.getString(4)+”</td>”) ;
out.println(“<td>”+rs.getString(5)+”</td>”) ;
out.println(“<td>”+rs.getString(6)+”</td></tr>”);
out.println(“</table></center>”);
}
else
{
out.println(“<center><font color=’red’ size=’7′>Invalid Id Try Again</font></center>”);
}
}
catch(Exception ee)
{
System.out.println(ee);
}
%>
Output:
Explanation of the above program
• Class.forName(“oracle.jdbe.driver.Oracle Driver”);
It loads the type-4 driver.
• Connection
con=DriverManager.getConnection(” jdbe:oraele:thin:@localhost:1521:xe”,”system”,”manager”);
It establishes the connection.
• Statement st=con.createStatement();
It creates the statement.
• String empno=request.getParameter(“tl”);
It reads the employee number through the getParameter(); of the request interface.
• ResuItSet rs=st.exeeuteQuery(“select * from EMP where EMPNO=” +empno);
This statement executes the query and retrieves the information from the EMP table and stores all the value in the ResultSet object.
• if(rs.next())
If (rs.next) returns true then a table will be formed and all the values which are retrieved from the EMP table of the database are stored in that table and displayed on the browser through the out.println () ;.
else{
• out.println(“<center><font color=’red’ size=’7′>Invalid Id Try Again</font></center>”);
If (rs.next) returns falls then it print “Invalid Id Try again” on the browser window as the response.
• catch(Exception ee)
{
• System.out.println(ee);
}
The generated exception is handled by the catch block.
Dinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular