Java Project JSP JDBC Java Program Core Java Demo MORE

How to Retrieve Data from MySQL Using JSP


For retrieve data from MySQL database using JSP first we have to create a table in data base.

After create a table in the MySQL 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:

retrieve.jsp

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%
String id = request.getParameter("userid");
String driver = "com.mysql.jdbc.Driver";
String connectionUrl = "jdbc:mysql://localhost:3306/";
String database = "test";
String userid = "root";
String password = "";
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
%>
<!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 = DriverManager.getConnection(connectionUrl+database, userid, password);
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