For retrieve data from ORACLE database using JSP first we have to create a table in data base.
After create a table in the ORACLE database you need to insert record or data on it.If you want to know how to insert data in jsp please visit the link : Insert data in JSP.
The SELECT statement is used to retrieve data from one or more tables:
The SQL query for retrieve specific column.
SELECT column_name(s)
FROM table_name
or we can use the * character to retrieve ALL columns from a table:
SELECT *
FROM table_name
To learn more about SQL, please visit our SQL tutorial.
In this example we retrieve the employee data of a company.
The JSP file for retrieving data from database is:
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%
%>
<!DOCTYPE html>
<html>
<body>
<h1>Retrieve data from database in jsp</h1>
<table border="1">
<tr>
<td>first name</td>
<td>last name</td>
<td>City name</td>
<td>Email</td>
</tr>
<%
try{
Connection connection = null;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
connection = DriverManager.getConnection("jdbc:odbc:data", "username", "password");
Statement statement = connection.createStatement();
String sql ="SELECT * FROM users";
resultSet = statement.executeQuery(sql);
while(resultSet.next()){
%>
<tr>
<td><%=resultSet.getString("first_name") %></td>
<td><%=resultSet.getString("last_name") %></td>
<td><%=resultSet.getString("city_name") %></td>
<td><%=resultSet.getString("email") %></td>
</tr>
<%
}
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
%>
</table>
</body>
</html>
After retrieve the data from the data base the table look like this.
| id | first name | last name | City name | Email Id |
|---|---|---|---|---|
| 1 | Divyasundar | Sahu | Mumbai | divyasundar@gmail.com |
| 2 | Hritika | Sahu | Pune | hritika@gmail.com |
| 3 | Milan | Jena | Chennai | milanjena@gmail.com |