Select query in JSP

This example describe we have discuss select query in JSP from database table.

Select query in JSP

Select query in JSP

We are going to describe select query in JSP. In this example we have created table in database and we create some fields(id, name, address, destination, salary, dateOfJoin).After that you will insert dummy data. After that we create JSP page than we have make database connection. After that we use SELECT query. SELECT query is a retrieve the data from database than execute this query using executeQuery(sql) method of Statement interface and store the result into ResultSet. After that getString()method of ResultSet interface.

Syntax of select query:-

  • SELECT * FROM TableName;

Now we have created database, You can copy this code.

CREATE TABLE `employeedetails` (           
                   `id` int(10) NOT NULL,                   
                   `name` varchar(20) DEFAULT NULL,         
                   `address` varchar(20) DEFAULT NULL,      
                   `destination` varchar(20) DEFAULT NULL,  
                   `salary` varchar(20) DEFAULT NULL,       
                   `dateOfJoin` date DEFAULT NULL,          
                   PRIMARY KEY (`id`)                       
                 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Example

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>

<%
String driverName = "com.mysql.jdbc.Driver";
String connectionUrl = "jdbc:mysql://localhost:3306/";
String dbName = "naulej";
String userId = "root";
String password = "root";

try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
%>
<h2 align="center"><font color="#FF00FF"><strong>Select query in JSP</strong></font></h2>
<table align="center" cellpadding="4" cellspacing="4">
<tr>

</tr>
<tr bgcolor="#008000">
<td><b>Id</b></td>
<td><b>Name</b></td>
<td><b>Address</b></td>
<td><b>Destination </b></td>
<td><b>Salary</b></td>
<td><b>DateOfJoin</b></td>
</tr>
<%
try {
connection = DriverManager.getConnection(
connectionUrl + dbName, userId, password);
statement = connection.createStatement();
String sql = "SELECT * FROM employeedetails";

resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
%>
<tr bgcolor="#8FBC8F">

<td><%=resultSet.getString("id")%></td>
<td><%=resultSet.getString("name")%></td>
<td><%=resultSet.getString("address")%></td>
<td><%=resultSet.getString("destination")%></td>
<td><%=resultSet.getString("salary")%></td>
<td><%=resultSet.getString("dateOfJoin")%></td>


</tr>

<%
}

} catch (Exception e) {
e.printStackTrace();
}
%>
</table>

OutPut

Download Source Code