JDBC select
This will get records from database and print and view on JSP page. The SQL query is used as select command statement, and with while loop we can view all records in table.
Example of select record in database through JSP
Database Table
CREATE TABLE staff_register (
`iEmpID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`sStaffName` varchar(60) DEFAULT NULL,
`sStaffDept` varchar(45) DEFAULT NULL,
`iStaffPhone` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`iEmpID`),
UNIQUE KEY `sStaffName` (`sStaffName`)
)
selectRecords.jsp
HTML Code Example
<%@ page language="java" import="java.sql.*" errorPage="" %>
<%
Connection conn = null;
Class.forName("com.mysql.jdbc.Driver").newInstance();
String jdbcURL="jdbc:mysql://localhost:3306/databaseName";
conn = DriverManager.getConnection(jdbcURL,"username", "password");
PreparedStatement psSelectRecord=null;
ResultSet rsSelectRecord=null;
String sqlSelectRecord=null;
sqlSelectRecord ="SELECT * FROM staff_register";
psSelectRecord=conn.prepareStatement(sqlSelectRecord);
rsSelectRecord=psSelectRecord.executeQuery();
%>
<html>
<head>
<title>View records in JSP through database</title>
<style>
td
{
text-align:center
}
</style>
</head>
<body>
<h1>View Record from Database through JSP</h1>
S. No | EmpID | Staff Name | Staff Department | Staff Phone <br>
<%
int cnt=1;
while(rsSelectRecord.next())
{
%>
<%=cnt%>
<%=rsSelectRecord.getString("iEmpID")%>
<%=rsSelectRecord.getString("sStaffName")%>
<%=rsSelectRecord.getString("sStaffDept")%>
<%=rsSelectRecord.getString("iStaffPhone")%>
<br>
<%
cnt++; /// increment of counter
} /// End of while loop
%>
</body>
</html>
<%
try{
if(psSelectRecord!=null)
{
psSelectRecord.close();
}
if(rsSelectRecord!=null)
{
rsSelectRecord.close();
}
if(conn!=null)
{
conn.close();
}
}
catch(Exception e)
{
e.printStackTrace();
}
%>
|
