Computer Revolution (www.comrevo.com): How to retrieve data from mysql database in jsp ( JSP-JDBC selecting records from mysql database )

Saturday 20 June 2015

How to retrieve data from mysql database in jsp ( JSP-JDBC selecting records from mysql database )

In this post, I have given Java Database Connectivity program (in JSP) for selecting records or retrieving data from the table in 'mysql' database.
Here 'test' is the database name in mysql. User name is 'test' and password is '123'. This program is retrieving records (tuples) from table 'syllabusplanpmcd4' which has four fields (columns).

Program:

<%@ page import="java.sql.*" %>
<html>

<head>
</head>

<body>

<br><br>

<table border="1">
<tr><th>Unit No.</th><th>Topic</th><th>Description</th><th>Hours Required</th></tr>
<%
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String db = "test";
String driver = "com.mysql.jdbc.Driver";
String userName ="test";
String password="123";

int sumcount=0;
Statement st;
try{
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url+db,userName,password);

String query="select * from syllabusplanpmcd4";
st = con.createStatement();
ResultSet rs = st.executeQuery(query);
%>
<%
while(rs.next()){
%>
<tr><td><%=rs.getString(1)%></td>
<td><%=rs.getString(2)%></td>
<td><%=rs.getString(3)%></td>
<td><%=rs.getString(4)%></td>
</tr>
<%
}
%>
<%
}
catch(Exception e)
{
e.printStackTrace();
}
%>
</table>

<br />
<br />

</body>
</html>

Output: