Accessing database from JSP

**Kushan** · 30-11-2009

Hi, can anyone give me code or something which will help me to access the database using the jsp? I have created one but it is not working. If you have any just reply me, so I can use it for my purpose.

**kelfro** · 30-11-2009

Hi friend, you know the jsp but don't know how to access database form jsp. And I don't know jsp but I know how to access database from jsp as I have a book on jsp, so, I will help you in this case to you. Just use the code below:

Code:

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

<%
	String driver = "org.gjt.mm.mysql.Driver";
	Class.forName(driver).newInstance();
	Connection con=null;
	ResultSet rst=null;
	Statement stmt=null;

	try
{
		String url="jdbc:mysql://localhost/books?user=<user>&password=<password>";
		con=DriverManager.getConnection(url);
		stmt=con.createStatement();
	}
	catch(Exception e){
		System.out.println(e.getMessage());
	}

	if(request.getParameter("action") != null)
{ 
		String bookname=request.getParameter("bookname");
		String author=request.getParameter("author");
		stmt.executeUpdate("insert into books_details(book_name,
author) values('"+bookname+"','"+author+"')");
		rst=stmt.executeQuery("select * from books_details");
		%>
		<html>
		<body>
		<center>
			<h2>Books List</h2>
			<table border="1" cellspacing="0" cellpadding
="0">
			<tr>
				<td><b>S.No</b></td>
				<td><b>Book Name</b></td>
				<td><b>Author</.b></td>
			</tr>
			 	<%
				int no=1;
				while(rst.next()){
				%>
				<tr>
				  <td><%=no%></td>
				  <td><%=rst.getString("
book_name")%></td>
				  <td> <%=rst.getString("author")
%> </td>
				</tr>
				<%
				no++;
	}
	rst.close();
	stmt.close();
	con.close();
%>
			</table>
			</center>
		</body>
	</html>
<%}else{%>
	<html>
	<head>
		<title>Book Entry FormDocument</title>
		<script language="javascript">
		    function validate(objForm){
			if(objForm.bookname.value.length==0){
			alert("Please enter Book Name!");
			objForm.bookname.focus();
			return false;
			}
			if(objForm.author.value.length==0){
			alert("Please enter Author name!");
			objForm.author.focus();
			return false;
			}
			return true;
				}
			</script>
		</head>
		

		<body>
			<center>
<form action="BookEntryForm.jsp" method="post" 
name="entry" onSubmit="return
 validate(this)">
	<input type="hidden" value="list" name="action">
	<table border="1" cellpadding="0" cellspacing="0">
	<tr>
		<td>
			<table>
				<tr>
				<td colspan="2" align="center">
<h2>Book Entry Form</h2></td>
				</tr>
				<tr>
				<td colspan="2">&nbsp;</td>
				</tr>
				<tr>
				<td>Book Name:</td>
				<td><input name="bookname" type=
"text" size="50"></td>
				</tr>
				<tr>
				<td>Author:</td><td><input name=
"author" type="text" size="50"></td>
				</tr>
				<tr>
					<td colspan="2" align="center">
<input type="submit" value="Submit"></td>
					</tr>
				</table>
			</td>
		</tr>
	</table>
</form>
			</center>
		</body>
	</html>
<%
}%>

**Katty** · 30-11-2009

Hi, you can get to access database from jsp with the help of following code.

Code:

import java.io.*;
import java.sql.*;
import java.util.*;
import javax.servlet.jsp.*;
import javax.servlet.jsp.tagext.*;

public final class DataAccessTag implements BodyTag 
{
	private PageContext pagecon = null;
	private BodyContent bdy= null;
	private StringBuffer sbuffer = new StringBuffer();
	private Connection con = null;
	private Statement stmt = null;
	private ResultSet rst = null;

	public void setPageContext(PageContext p) 
	{
		pagecon = p;
	}

	public void setParent(Tag t) 
	{
	}

	public Tag getParent() 
	{
		 return null;
	 }

	public int doStartTag() throws JspException 
	{
		String path = "jdbc:odbc:Names";
		String sql = "SELECT ID, first_name, last_name FROM Names";
		try 
		{

		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		con = DriverManager.getConnection(path);

		stmt = con.createStatement();
		rst = stmt.executeQuery(sql);

		setVariables();

		} 
			catch (SQLException e) 
		{
		throw new JspTagException("An SQLException occurred!");
		}
			 catch (ClassNotFoundException e) 
		{
			throw new JspTagException("JDBC Driver not found.");
		}
		return EVAL_BODY_TAG;
	}

	public void setBodyContent(BodyContent b) 
	{
		bdy= b;
	}

	public void doInitBody() throws JspException 
	{
	}
	
	private boolean setVariables() throws JspTagException 
	{
		try
		{
		if (rst.next()) 
		{

			pagecon.setAttribute("id", rst.getObject(1).toString());
			pagecon.setAttribute("firstt_name", rst.getObject(2).toString());
			pagecon.setAttribute("last_name", rst.getObject(3).toString());
			return true;
		} 
		else 
		{
			return false;
		}

	} 	

	catch (SQLException e) 
	{
		throw new JspTagException("SQLException occurred!);
	}
	}

	public int doAfterBody() throws JspException
	 {
		try 
		{
			sbuffer.append(body.getString());
			body.clear();
		} 
		catch (IOException e) 
		{
			throw new JspTagException("Fatal IOException!");
		}
		
		if(setVariables()) 
		{
			return EVAL_BODY_TAG;
		}

		try 
		{
			body.getEnclosingWriter().write(sbuffer.toString());
		} 
		catch (IOException e) 
		{
			throw new JspTagException("Fatal IOException!");
		}

		return SKIP_BODY;
	}

	public int doEndTag() throws JspException 
	{
		try
		 {
			if(rst != null) 
			{
				rst.close();
				rst = null;
			}
			if(stmt != null) 
			{
				stmt.close();
				stmt = null;
			}
			if(con != null) 
			{
				con.close();
				con = null;
			}
		} 

		catch (SQLException e) 
		{
		}

		return EVAL_PAGE;
	}

	public void release() 
	{
		pagecon = null;
		bdy= null;
		sbuffer = null;
	}
}

**MindSpace** · 30-11-2009

I think the code given below will help you to access database from jsp. I have not tried it. But by looking it, I don't think that there is any error in code. So, use it. If any error occur just give me reply.

Code:

<html>
<head><title>Databae access with jsp</title></head>
<body>
<table>
<%@ page import="java.util.*" %>
<%@ page import="javax.sql.*;" %>
<% 

java.sql.Connection con;
java.sql.Statement stmt;
java.sql.ResultSet rst;
java.sql.PreparedStatement pstmt;

con=null;
stmt=null;
pstmt=null;
rst=null;

String url=  "jdbc:jtds:sqlserver://dbase name";
String id= "username";
String pass = "password";
try
{
Class.forName("net.sourceforge.jtds.jdbc.Driver");
con = java.sql.DriverManager.getConnection(url, userid, psw);

}
catch(ClassNotFoundException e)
{
e.printStackTrace();
}
String sql = "slect * from emplyee";
try
{
stmt = con.createStatement();
rst = stmt.executeQuery(sql);
%>

<%
while( rst.next() )
{
%><tr>
<td><%= rst.getString("emp_id") %></td>
<td><%= rst.getString("date") %></td>
<td><%= rst.getString("email") %></td>
</tr>
<%
}
%>
<%
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
if(rst!=null) rst.close();
if(stmt!=null) stmt.close();
if(con!=null) con.close();
}
%>
</body>
</html>