Write sophisticated code?

Hi, everyone. There is this code I have written and it's very long(3 pages). It works fine, I have not detected any bugs. But the problem is I feel there is a way to write more sophisticated code. I am posting the code and anyone who has some free time to contribute, please examine the code and tell me where I can improve.
Thanks verymuch for your time. It will be a great help to me.

Background: This code is to be embedded in a JSP.
The code operates on 2 tables in the database. bookcopies and waitinglist.
The code is a module for online library management system(my semester project). We have book reservation facility in our system and the reservations are stored in waitinglist table. We have a bookcopies table which has the attributes: bookid,copynumber,borrower,duedate. I case the copy is not presently borrowed, the borrower and duedate will be null. Waitinglist has attributes like bookid,userid(user who reserved the book),listposition,activepassive,daycount.
activepassive is 1 when the waiter is active. activepassive is 0 when the waiter is passive.

If some thing is not understandable in my code, please ask and I shall reply back.
Here is the code

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@page import="java.io.IOException"%>
<%@page import="java.io.PrintWriter"%>
<%@page import="javax.servlet.ServletException"%>
<%@page import="javax.servlet.http.HttpServletRequest"%>
<%@page import="javax.servlet.http.HttpServletResponse"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Daily Update and Emailing</title>
</head>
<body>
<% 
int firstactive=0;
try
{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	Connection con=DriverManager.getConnection("jdbc:odbc:source","db2admin","db2admin");
	Statement stmt0=con.createStatement();

//--------------------------------------------------------------ACTIVE TO PASSIVE AND PASSIVE TO ACTIVE------------------------------------------------

Statement stmt = con.createStatement();
	ResultSet rst=stmt.executeQuery("SELECT DISTINCT BOOKID FROM USER.WAITINGLIST");
	while(rst.next())//FOR EACH BOOK
	{
		String bookid=rst.getString(1);
		Statement stmt1=con.createStatement();
		ResultSet rst1=stmt1.executeQuery("SELECT TITLE,EDITION,AVAILABLECOPIES FROM USER.BOOKS WHERE BOOKID='"+bookid+"'");
		while(rst1.next())//THIS LOOP WILL EXECUTE ONLY ONCE.
		{
			int available=rst1.getInt(3);
			int edition=rst1.getInt(2);
			String title=rst1.getString(1);
			Statement stmt2=con.createStatement();
			ResultSet rst2=stmt2.executeQuery("SELECT MIN(LISTPOSITION) FROM USER.WAITINGLIST WHERE BOOKID='"+bookid+"' AND ACTIVEPASSIVE=1");
			while(rst2.next())
			{
				firstactive=rst2.getInt(1);
			}
			rst2.close();
			
			
		if(available > 0 && firstactive!=0)//meaning active users will be turned to passive and there must be atleast one active user
		{
			Statement stmt7=con.createStatement();
			ResultSet rst7=stmt7.executeQuery("SELECT E.USERID,E.EMAIL FROM USER.STUDENT E,USER.WAITINGLIST W WHERE E.USERID=W.USERID AND W.BOOKID='"+bookid+"' AND (W.LISTPOSITION >= "+firstactive+" OR W.LISTPOSITION <= "+(firstactive+available-1)+")");
			while(rst7.next())
			{
				stmt0.executeUpdate("INSERT INTO USER.EMAIL VALUES('"+rst7.getString(2)+"','PLEASE COLLECT YOUR RESERVED BOOK','ID:"+bookid+" and TITLE:"+title+" AND EDITION NUMBER:"+edition+"',CURRENT DATE)");
				stmt0.executeUpdate("UPDATE USER.WAITINGLIST SET ACTIVEPASSIVE=0 WHERE USERID='"+rst7.getString(1)+"' AND BOOKID='"+bookid+"'");
				
			}
			rst7.close();
			stmt7.close();
			//the same thing for faculty
			Statement stmt7fac=con.createStatement();
			ResultSet rst7fac=stmt7fac.executeQuery("SELECT E.USERID,E.EMAIL FROM USER.FACULTY E,USER.WAITINGLIST W WHERE E.USERID=W.USERID AND W.BOOKID='"+bookid+"' AND (W.LISTPOSITION >= "+firstactive+" OR W.LISTPOSITION <= "+(firstactive+available-1)+")");
			while(rst7fac.next())
			{
				stmt0.executeUpdate("INSERT INTO USER.EMAIL VALUES('"+rst7fac.getString(2)+"','PLEASE COLLECT YOUR RESERVED BOOK','ID:"+bookid+" and TITLE:"+title+" AND EDITION NUMBER:"+edition+"',CURRENT DATE)");
				stmt0.executeUpdate("UPDATE USER.WAITINGLIST SET ACTIVEPASSIVE=0 WHERE USERID='"+rst7fac.getString(1)+"' AND BOOKID='"+bookid+"'");
				
			}
			rst7fac.close();
			stmt7fac.close();
			
		}
		if(available==0) //meaning passive will be turned to active
		{
			Statement stmt3=con.createStatement();
			ResultSet rst3=stmt3.executeQuery("SELECT E.USERID,E.EMAIL FROM USER.STUDENT E,USER.WAITINGLIST W WHERE E.USERID=W.USERID AND W.BOOKID='"+bookid+"' AND W.ACTIVEPASSIVE=0");
			while(rst3.next())
			{
				stmt0.executeUpdate("INSERT INTO USER.EMAIL VALUES ('"+rst3.getString(2)+"','RESERVED BOOK NOT AVAILABLE NOW','id-"+bookid+" TITLE:"+title+" EDITION:"+edition+"',CURRENT DATE)");
				stmt0.executeUpdate("UPDATE USER.WAITINGLIST SET ACTIVEPASSIVE=1 WHERE USERID='"+rst3.getString(1)+"' AND BOOKID='"+bookid+"'");
			}
			rst3.close();
			stmt3.close();
			//same for faculty
			Statement stmt3fac=con.createStatement();
			ResultSet rst3fac=stmt3fac.executeQuery("SELECT E.USERID,E.EMAIL FROM USER.FACULTY E,USER.WAITINGLIST W WHERE E.USERID=W.USERID AND W.BOOKID='"+bookid+"' AND W.ACTIVEPASSIVE=0");
			while(rst3fac.next())
			{
				stmt0.executeUpdate("INSERT INTO USER.EMAIL VALUES ('"+rst3fac.getString(2)+"','RESERVED BOOK NOT AVAILABLE NOW','id-"+bookid+" TITLE:"+title+" EDITION:"+edition+"',CURRENT DATE)");
				stmt0.executeUpdate("UPDATE USER.WAITINGLIST SET ACTIVEPASSIVE=1 WHERE USERID='"+rst3fac.getString(1)+"' AND BOOKID='"+bookid+"'");
			}
			rst3fac.close();
			stmt3fac.close();
		}
		out.println("check");
		stmt2.close();
		
		}//end of first while loop rst1.
		rst1.close();
		stmt1.close();
		
	}//end of second while loop
	rst.close();
//--------------------------------------------------------------ACTIVE TO PASSIVE AND PASSIVE TO ACTIVE(COMPLETED)------------------------------------------------

stmt.executeUpdate("UPDATE USER.WAITINGLIST SET DAYCOUNT=(DAYCOUNT+1) WHERE ACTIVEPASSIVE=0");

//--------------------------------------------------------------------REMOVING EXPIRED RESERVERS--------------------------------------------------------	
	
	Statement stmt4=con.createStatement();
	ResultSet rst4=stmt4.executeQuery("SELECT DISTINCT BOOKID FROM USER.WAITINGLIST");
	while(rst4.next())
	{
		int counter=0;
		String title="";
		int edition=0;
		String bookid=rst4.getString(1);
		
		Statement stmt5=con.createStatement();
		ResultSet rst5=stmt5.executeQuery("SELECT TITLE,EDITION FROM USER.BOOKS WHERE BOOKID='"+bookid+"'");
		while(rst5.next())
		{
			title=rst5.getString(1);
			edition=rst5.getInt(2);
		}
		rst5.close();
		stmt5.close();
		
		Statement stmt6=con.createStatement();
		ResultSet rst6=stmt6.executeQuery("SELECT E.EMAIL FROM USER.STUDENT E,USER.WAITINGLIST W WHERE E.USERID=W.USERID AND W.BOOKID='"+bookid+"' AND W.DAYCOUNT=6");
		while(rst6.next())
		{
			stmt0.executeUpdate("INSERT INTO USER.EMAIL VALUES('"+rst6.getString(1)+"','REMOVED FROM WAITING LIST','YOU RESERVATION CANCELLED AUTOMATICALLY FOR BOOK ID:"+bookid+" TITLE:"+title+" EDITION:"+edition+"',CURRENT DATE)");
			counter++;
		}
		rst6.close();
		stmt6.close();
		//doing the same as above for facs.
		Statement stmt6fac=con.createStatement();
		ResultSet rst6fac=stmt6fac.executeQuery("SELECT E.EMAIL FROM USER.FACULTY E,USER.WAITINGLIST W WHERE E.USERID=W.USERID AND W.BOOKID='"+bookid+"' AND W.DAYCOUNT=6");
		while(rst6fac.next())
		{
			stmt0.executeUpdate("INSERT INTO USER.EMAIL VALUES('"+rst6fac.getString(1)+"','REMOVED FROM WAITING LIST','YOU RESERVATION CANCELLED AUTOMATICALLY FOR BOOK ID:"+bookid+" TITLE:"+title+" EDITION:"+edition+"',CURRENT DATE)");
			counter++;
		}
		rst6fac.close();
		stmt6fac.close();
		
		stmt0.executeUpdate("DELETE FROM USER.WAITINGLIST WHERE BOOKID='"+bookid+"' AND DAYCOUNT=6");
		if(counter!=0)
		{
			stmt0.executeUpdate("UPDATE USER.WAITINGLIST SET LISTPOSITION=(LISTPOSITION-COUNTER) WHERE BOOKID='"+bookid+"'");
		}
		
	}
	rst4.close();
	stmt4.close();
	
	out.println("Update complete for reservers.");
//--------------------------------------------------------------------REMOVING EXPIRED RESERVERS(COMPLETED)--------------------------------------------------------

//--------------------------------------------------------------------------OVERDUES-------------------------------------------------------------------	
	
	ResultSet rst8=stmt.executeQuery("SELECT E.EMAIL,B.BOOKID,B.DUEDATE FROM USER.STUDENT E,USER.BOOKCOPIES B WHERE E.USERID=B.BORROWER AND B.DUEDATE < CURRENT DATE");
	while(rst8.next())
	{
		
		stmt0.executeUpdate("INSERT INTO USER.EMAIL VALUES ('"+rst8.getString(1)+"','BOOK OVERDUE','Dear user, the book with the following ID:"+rst8.getString(2)+" has exceeded the duedate of "+rst8.getDate(3)+"',CURRENT DATE)");
		
	}
	rst8.close();
	//doing the same for facs
	ResultSet rst8fac=stmt.executeQuery("SELECT E.EMAIL,B.BOOKID,B.DUEDATE FROM USER.FACULTY E,USER.BOOKCOPIES B WHERE E.USERID=B.BORROWER AND B.DUEDATE < CURRENT DATE");
	while(rst8fac.next())
	{
		
		stmt0.executeUpdate("INSERT INTO USER.EMAIL VALUES ('"+rst8fac.getString(1)+"','BOOK OVERDUE','Dear user, the book with the following ID:"+rst8fac.getString(2)+" has exceeded the duedate of "+rst8fac.getDate(3)+"',CURRENT DATE)");
		
	}
	rst8fac.close();
//--------------------------------------------------------------------------OVERDUES(COMPLETED)-------------------------------------------------------------------

//-------------------------------------------------------------------------ADVANCE OVERDUES------------------------------------------------------------	
	
	ResultSet rst9=stmt.executeQuery("SELECT E.EMAIL,B.BOOKID,B.DUEDATE FROM USER.STUDENT E,USER.BOOKCOPIES B WHERE E.USERID=B.BORROWER AND CURRENT DATE + 4 DAYS = B.DUEDATE");
	while(rst9.next())
	{
		stmt0.executeUpdate("INSERT INTO USER.EMAIL VALUES ('"+rst9.getString(1)+"','ADVANCE DUEDATE REMAINDER','This mail is to remind you in advance of the duedate of the book with id:"+rst9.getString(2)+". Duedate is "+rst9.getDate(3)+"',CURRENT DATE)");	
	}
	rst9.close();
	
	ResultSet rst9fac=stmt.executeQuery("SELECT E.EMAIL,B.BOOKID,B.DUEDATE FROM USER.FACULTY E,USER.BOOKCOPIES B WHERE E.USERID=B.BORROWER AND CURRENT DATE + 4 DAYS = B.DUEDATE");
	while(rst9fac.next())
	{
		stmt0.executeUpdate("INSERT INTO USER.EMAIL VALUES ('"+rst9fac.getString(1)+"','ADVANCE DUEDATE REMAINDER','This mail is to remind you in advance of the duedate of the book with id:"+rst9fac.getString(2)+". Duedate is "+rst9fac.getDate(3)+"',CURRENT DATE)");	
	}
	rst9fac.close();
//-------------------------------------------------------------------------ADVANCE OVERDUES(COMPLETED)------------------------------------------------------------	
	
	out.println("borrowers notified");
	stmt.close();
	stmt0.close();

}//end of try

catch (Exception e)
{
	e.printStackTrace();
	out.println("EXCEPTION:" + e.getMessage() );
	out.println("</br>");
}

%>
</body>
</html>

Thanks again for your valuble suggestions.

please examine the code and tell me where I can improve

Remove scriptlets from your JSP.

No code in a JSP can be "sophisticated" -- it is no longer acceptable, indeed it is irresponsible, to put code in a JSP at this point. The first step in sophisticating your code is to refactor the code out of the JSP and into a Java class or classes.

Here's some thoughts on refactoring your code,

- Remove scriptlets from JSP, there should be no java code in JSP, use JSP absolutely for presentation purpose. it just displays data provided by controller
- Move data access code to data access objects - DAO
- Move business logic related code to services
- No data access/business logic code in your servlets/controller
- You controller (or servlet if you are not using any framework) uses services and DAOs and provided data to view (JSP) for display
- Have your domain model and entities instead of writing database table oriented code.

Too difficult a question for "beginning". Moving thread.

I'll echo the other comments about scriptlet code.
Java code belongs in a java class - and this is 99% java code.

Feedback on the code itself (ignoring the fact that it is in a JSP)
- This code is potentially open to SQL Injection attack. Instead of building up SQL using string concatenation, you should use a Prepared Statement.

eg

- Instead of opening a database connection directly in your code, you should use a JNDI Datasource to get a connection from a connection pool

- I would not recommend using the JDBC-ODBC bridge driver. Use a proper database (eg Oracle, MSSql, MySQL) with a Type 4 JDBC connector.

- This code appears to always execute every single query in line. I would suggest breaking this up into methods, one action per method. You have already broken them up by comments, so extracting them into methods should be trivial.

- Often you are performing 2-3 seperate queries on seperate tables, where a better approach would be to use a single query and join the tables in the query.

For instance:
SELECT DISTINCT BOOKID FROM USER.WAITINGLIST
SELECT TITLE,EDITION,AVAILABLECOPIES FROM USER.BOOKS WHERE BOOKID=

Could be written as one query:

- You don't seem to be releasing your database connection, though you seem pretty good about closing resultset/statement. My recommendation would be that you always release these resources in a finally block after a try/catch, so that they do get closed in the event of an exception.

Thanks to you all for those useful responses. And, any more suggestions are welcome. Coderanch rocks!!!

Move business logic related code to services

So, I have to use EJB as services?

No, not at all.

I have to use "web services" as services?Can any one suggest some books that would help me build web services through J2EE? I am a beginner.