A Java Beans Example that uses JDBC

/*
* BookWorldBean.java
*
* Created on November 7, 2007, 10:13 AM
*/
package BookWorldPackage;
import java.beans.*;
import java.io.Serializable;
/**
* @author Champak Roy
*/
public class BookWorldBean extends Object implements Serializable {

private java.sql.Connection con;

public void openConnection() throws Exception
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=java.sql.DriverManager.getConnection("jdbc:odbc:BookWorld","Uname", "Pass");
}
catch(Exception ex)
{
throw ex;
}
}

private int getNoOfResults(java.sql.PreparedStatement ps)
{java.sql.ResultSet rs=null;
int count=0;
try
{
rs =ps.executeQuery();
while(rs.next())
count+=1;
rs.close();
return(count);
}
catch(Exception ex)
{
return(0);
}

}
//********************Get books by BookName********************
public String getBooksByBook(String BookName)
{
try
{
BookName=BookName.replaceAll("`", "`");
BookName ="`%" + BookName+ "%`";
//******************************BookName Formatting***************************

java.sql.PreparedStatement ps=con.prepareStatement("select * from books where Bookname like " + BookName);

String s="<table width=100% border=2 bgcolor=yellow><tr><td><font color=blue>Serial No</font></td><td><font color=blue>Book Name</td></font><td><fontcolor=blue>Book Price</td></font><td><font color=blue>Book Contents</td></font><td><font color=blue>Orders</font> </td></tr>";
java.sql.ResultSet rs=ps.executeQuery();
int count=0;
while(rs.next())
{
count+=1;
s=s + "<tr bgcolor=green><td>" + count + "</td><td>" + rs.getString("BookName") + "</td><td>" + rs.getInt("BookPrice") + "</td><td>" + rs.getString("BookContents") + "</td><TD><a href=Orders.jsp?BookNo=" + rs.getInt("BookNo") + ">Order thisBook</a></td></tr>";
}
s=s + "</table>";
return(s);

}
//*********************************BookName Formatting***************************





catch(Exception ex)
{

String s="No Book found";
return(s);
}
}
//********************Get books by BookName********************




//********************Get books by SubjectName********************
public String getBooksBySubject(String SubjectName)
{
try
{
SubjectName=SubjectName.replaceAll("`", "`");
SubjectName ="`%" + SubjectName + "%`";
//******************************SubjectName Formatting***************************

java.sql.PreparedStatement ps=con.prepareStatement("select books.*,SubjectName from Books_Subjects,books where SubjectName like " + SubjectName + " and books.bookname=Books_Subjects.bookname");

String s="<table width=100% border=2 bgcolor=yellow><tr><td><font color=blue>Serial No</font></td><td><font color=blue>Book Name</td></font><td><font color=blue>Book Price</td></font><td><font color=blue>Book Contents</td></font><td><font color=blue>SubjectName</td></font></td><td>Orders </td></tr>";
java.sql.ResultSet rs=ps.executeQuery();
int count=0;
while(rs.next())
{
String BookName=rs.getString("BookName");
int BookPrice=rs.getInt("BookPrice");
String BookContents=rs.getString("BookContents");

int BookNo=rs.getInt("BookNo");
SubjectName=rs.getString("SubjectName");
count+=1;
s=s + "<tr bgcolor=green><td>" + count + "</td><td>" + BookName + "</td><td>" + BookPrice + "</td><td>" + BookContents + "</td><td>" + SubjectName + "</td><TD><a href=Orders.jsp?BookNo=" + BookNo + ">Order this Book</a></td></tr>";
}
s=s + "</table>";
return(s);

}
//*********************************SubjectName Formatting***************************






catch(Exception ex)
{

String s="No Book found" + ex;
return(s);
}
}
//********************Get books by SubjectName********************






//*********************Get book by AuthorName******************
publicString getBooksByAuthor(String AuthorName)
{
try
{
AuthorName=AuthorName.replaceAll("`", "`");
AuthorName ="`%" + AuthorName + "%`";
//**********************************************AuthorName Formatting***************************

java.sql.PreparedStatement ps=con.prepareStatement("select books.*,AuthorName from Books_Authors,books where Authorname like " + AuthorName + " and books.bookname=books_authors.bookname");

String s="<table width=100% border=2 bgcolor=yellow><tr><td><font color=blue>Serial No</font></td><td><font color=blue>Book Name</td></font><td><font color=blue>Book Price</td></font><td><font color=blue>Book Contents</td></font><td><font color=blue>AuthorName</td></font></td><td>Orders </td></tr>";
java.sql.ResultSet rs=ps.executeQuery();
int count=0;
while(rs.next())
{

String BookName=rs.getString("BookName");
int BookPrice=rs.getInt("BookPrice");
String BookContents=rs.getString("BookContents");

int BookNo=rs.getInt("BookNo");
AuthorName=rs.getString("AuthorName");


count+=1;
s=s + "<tr bgcolor=green><td>" + count + "</td><td>" + BookName + "</td><td>" + BookPrice + "</td><td>" + BookContents + "</td><td>" + AuthorName + "</td><TD><a href=Orders.jsp?BookNo=" + BookNo + ">Order this Book</a></td></tr>";
}

s=s + "</table>";
return(s);


//*********************************AuthorName Formatting***************************



}

catch(Exception ex)
{

String s="No Book found";
return(s);
}
}

//***********************************************************************************************
public String getBooksByBookNo(int BookNo)
{
try
{

//**********************************************AuthorName Formatting***************************

java.sql.PreparedStatement ps=con.prepareStatement("select BookName from Books where BookNo=?");
ps.setInt(1, BookNo) ;

java.sql.ResultSet rs=ps.executeQuery();

if(rs.next())
{

String BookName=rs.getString("BookName");
return(BookName);
}


return("Invalid Book");


//*********************************AuthorName Formatting***************************



}

catch(Exception ex)
{

String s="No Book found";
return(s);
}
}

//********************************************************************************************

//*********************Get book by AuthorName******************




//*********************Get book by ISBN no************************
public String getBooksByISBN(String ISBN)
{
try
{
ISBN=ISBN.replaceAll("`", "`");
ISBN ="`%" + ISBN + "%`";
//**************************************ISBN Formatting***************************

java.sql.PreparedStatement ps=con.prepareStatement("select * from Books where BookISBN like" + ISBN);

String s="<table width=100% border=2 bgcolor=yellow><tr><td><font color=blue>Serial No</font></td><td><font color=blue>Book Name</td></font><td><font color=blue>Book Price</td></font><td><font color=blue>Book Contents</td></font><td><font color=blue>ISBN</font></td><td>Orders </td></tr>";
java.sql.ResultSet rs=ps.executeQuery();
int count=0;
while(rs.next())
{
count+=1;
String BookName=rs.getString("BookName");
s=s + "<tr bgcolor=green><td>" + count + "</td><td>" + BookName + "</td><td>" + rs.getInt("BookPrice") + "</td><td>" + rs.getString("BookContents") + "</td><td>" + rs.getString("BookISBN") + "</td><TD><a href=Orders.jsp?BookNo=" + rs.getInt("BookNo") + ">Order this Book</a></td></tr>";
}
s=s + "</table>";
return(s);
}
//**************************************ISBN Formatting***************************
catch(Exception ex)
{

String s="No Book found" + ex;
return(s);
}
}


}
//*********************Get book by ISBN no************************