Help connecting to a database from a JSP
I am trying to connect to a database using a very simple JSP. I made a Database connection servlet which is extended in my JSP. And I have the sql statement to be executed written in my JSP. I keep getting a java.lang.NullPointerException and I do not know why. Help!!
This is my DatabaseServlet.java
Code:
package app01b;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public abstract class DatabaseServlet
extends HttpServlet
{
private static final long serialVersionUID = 1L;
protected Connection con;
public void init(ServletConfig config)
throws ServletException
{
super.init(config);
try {
Class.forName("oracle.jdbc.OracleDriver");
con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:ganesh", "hr", "hr");
}
catch (Exception e) {
throw new UnavailableException(e.getMessage());
}
}
}
This is my welcome.jsp
Code:
<%@ page extends="app01b.DatabaseServlet"%>
<%@page import="java.io.*, java.sql.*"%>
<%
Statement stmt = con.createStatement();
String sql = "select name from lib_member_master where member_id = 101";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
String result = rs.getString(1);
%>
<html>
<head><title>Welcome</title></head>
<body>
Welcome <%=result%>
</body>
<%
}
rs.close();
stmt.close();
%>
</html>
Re: Help connecting to a database from a JSP
Don't write it that way.
Have your request go to a servlet.
Have that servlet call a DAO to get the data it wants.
The DAO will handle opening and closing connections (preferably with a connections pool) and will return a List<SomeModelClass>, where SomeModelClass is a class that represents the data returned. The DAO will convert each row in the resultset into one of these and add it to the List.
The servlet will add this list to the request, and then forward to the JSP (if everything is OK).
All the JSP has to do is display the Model.
Put simply, the servlet does the work, the JSP simply displays what the servlet supplies to it.
Oh, and you should pretty much never give a servlet state. That is, it should not have any attributes. That connection you have there is not threadsafe at all.
