Select a record from database - JSP Program

Q. Write a JSP application to select the record from the database.

Answer:

Below example shows how to connect JSP program to oracle database.

There is an Employee table in database, we fetch the all record from the database table and print them.

1. Create the Employee table

Create table Employees (id number, age number, FirstName varchar2(20), LastName varchar2(20));

2. Create the JSP application

JspDB.jsp

<%@ page import="java.sql.*"%>
<%@ page import="java.util.*"%>
<html>
    <head>
        <title>SELECT Operation</title>
    </head>
    <body>
        <%!
            Connection con;
            PreparedStatement ps;
            public void jspInit()
            {
                try
                {
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "local", "test");
                    ps = con.prepareStatement("select * from Employees");
                }
                catch(Exception ex)
                {
                    ex.printStackTrace();
                }
            }
        %>
        <%
            ResultSet rs = ps.executeQuery();
            out.println("<table border=1 >");
            out.println("<tr style='background-color:#ffffb3; color:red'>");
            out.println("<th>Emp Id</th>");
            out.println("<th> Age(Year)</th>");
            out.println("<th>First Name</th>");
            out.println("<th>Last Name</th>");
            out.println("</tr>");

            while(rs.next())
            {
                out.println("<tr style='background-color:#b3ffd9;'>");
                out.println("<td>"+rs.getInt(1)+"</td>");
                out.println("<td>"+rs.getInt(2)+"</td>");
                out.println("<td>"+rs.getString(3)+"</td>");
                out.println("<td>"+rs.getString(4)+"</td");
                out.println("</tr>");
            }
            out.println("</table>");
            rs.close();
        %>
    </body>
</html>


web.xml

<web-app>
    <servlet>
        <servlet-name>xyz</servlet-name>
        <jsp-file>/JspDB.jsp</jsp-file>
    </servlet>
    <servlet-mapping>
        <servlet-name>xyz</servlet-name>
        <url-pattern>/test</url-pattern>
    </servlet-mapping>
</web-app>


Output:

select record
▲