Hi;
The below page validates the user. if the groupid of the user is null into the database,it forward the user to accessdenied.jsp page. The below code works fine without closing rs
If i had tried to close rs.close(); it show the compile error: rs might not have been initialised.
and without close, it does not work for the accessdenied.jsp

<%-- 
    Document   : as
    Created on : Jul 26, 2008, 12:41:17 PM
    Author     : user1
--%>

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page language ="java" %>
<%@ page import="java.sql.*, javax.sql.*, javax.naming.*,java.io.*,java.util.*" %>


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <%
   String userid=request.getParameter("userid");
   String password=request.getParameter("password");
   
   	InitialContext context = new InitialContext();		
	DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/mynewdatabase");			
	Connection conn = ds.getConnection();		
	context.close();
		
	
   Statement stmt=conn.createStatement();
   ResultSet rs;
   
 try {   
   rs=stmt.executeQuery("select groupid from user where emailid='"+userid+"' and Password='"+password+"'");
    %>
     <%  while(rs.next())
   {
         %>
    <%  String groupId = rs.getString("groupid");
       out.println(groupId);
        if ( groupId==null )
         {
   		out.println("Invalid User");
		
       %>

  <jsp:forward page="/AccessDenied.jsp" />

<%
      }
	else
	{
		out.println("Valid User");
	
		
 %>
 <jsp:forward page="/try.jsp" />
    <%  }
       	}
       }
   finally
        {
                           if (rs != null)
                           {
                               rs.close();
                               rs = null;
                           }
                           if (stmt != null)
                           {
                               stmt.close();
                               stmt = null;
                           }

                       }
        %>
        
    </body>
</html>

2. Can i access the variable groupid in try.jsp page by request.getparameter() method.
If not then can any have the alternative way for accessing same variable in try.jsp page.
Thanks and Regards
Haresh

It is YOUR school work/work project so deal with it if you ignore advice from others with more experiences!

Comments
Now is this a rare sign of frustration from the Tiger ;-)

Hi peter_budo;
I am really sorry for ignoring your precious suggestion.
see why I am doing is that i had just completed java course and in class i had learned about how to connect to database in jsp and I am doing that pattern as i had practiced it in lab.
yes, it seems really difficult to modify the jsp code with database connectivity.

now as per your suggestion whether i have to write separate servlet for database connectivity for each jsp page:
Kindly give me few minutes and tell how can use you suggested theme for above jsp page.
in my jsp project there are nearly 35 jsp pages. so for each jsp page should i require to write separate database connectivity servlet.
Give me details
Waiting for your precious suggestion .
Thanks and regards
Haresh

If you provide more in detail description of your problem/project then "in my jsp project there are nearly 35 jsp pages" and explain how you got to that conclusion you may actually get some precious suggestions...

Hi peter_bodo,
I am developing a project: student feedback system for an institution.
Here each student will get an userid and password .
There are six tables in database to support project
1. User—consist of user information like username, password etc.
2. Groupid—consists of various groups like group of student, group of faculty. Users are divided into various groups.
3. questionid- consists of question of various types. Each question-id consist multiple question
4. Questionbank: consist of large no. of question for particular question-id. Here you can assume question-id=questiontype. Common between above two table is question-id(int).
5. event table—where various event are published. For each event there may be more than one question-id
e.g event—feedback of semester –4-- containing question-id like: question-id for economics batch, question-id for commerce batch
6. Final output table- consist of userid,question-id,question,answer marked by various user.
There are two users. One is normal user and second is system administrator who can add, detete,modify all the questios,user,event, or any module.
So developed the pages for administration add, delete, modify for questionbank, question-id,event,group. So there will be about 15 jsp pages.
Once normal user will log in, it will shows all the event that is marked as published and targeted to the particular group. When it will click on this event it will show the various question-id (consist of various question)available for answering.
The question is in choice format. Like:
1.question name
a.option -A
b.option-B
once user answer all the questions it will stored the final output table.
So I had developed the pages for administrator, also for user to shows various event with question-id, also when user fill all the answer of all question, it will stored in final table.
please dont suggest to change database structure as it is assigned by my boss to me.

I hope it may clear to you.
Thanks and Regard
Haresh

What are the major views that admin and student get?
Like admin will see some of these options

  • add
  • delete
  • modify

I want to know what exactly is available to them and in what kind of form (links, buttons etc)?

Hi,
Once student log in he/she will see the link of all event available in table event and marked to that particular group. Once it will click on the that link it will shows the related question-id (question-title) name from question-id table. Once user click on that link it will see all the question belong to the question-id in questionbank table .
Answer of question is in radio-button format for choice a, choice-b which also from option available in questionbank table for respective question . once he/she will mark all question the result will stored in final table.
So view will contain first page- event name from event table click->> retated question-id(question-title) from question-id table click all questions and related options from questionbank table.

Thanks and Regards
Haresh

Can you get me full table descriptions with indication of primary keys? (Just table structure, not interested in data inside table)

Hi peter_budo;
here is my table structure of database:

CREATE TABLE user
(
 userid   int(4)  NOT NULL  auto_increment,
 firstname  varchar(100)  NOT NULL,
 lastname  varchar(100)  NOT NULL,
 emailid      varchar(100)  NOT NULL  UNIQUE,
 password   varchar(50)  NOT NULL,
 groupid   int(8)  not null,
  PRIMARY KEY ( userid)
);

CREATE TABLE markedevent
(
 userid   int(4)  NOT NULL ,
 eventid      int(4)  NOT NULL,
  PRIMARY KEY ( userid)
);


CREATE TABLE  Event
(
  Eventid     int(4)  NOT NULL  AUTO_INCREMENT,
  Description   varchar (255)  NOT NULL,
  Publish       varchar(20)   NOT NULL,
  Questionid   int(4)    NOT NULL,
  Targetgroup   varchar(70)   NOT NULL,
   Anonymous  varchar(15)   NOT NULL,
   PRIMARY KEY (  Eventid )
);
CREATE TABLE  Questionid
(
    Questionid   int(4)    NOT NULL  AUTO_INCREMENT,
     Description  varchar(70)   NOT NULL,
     Type    varchar(70)    NOT NULL,
     PRIMARY KEY (Questionid)
);
CREATE TABLE  QuestionBank
(
  Qserialno    int(3)  NOT NULL   AUTO_INCREMENT,
 Questionid   int(4)  NOT NULL ,
 Questionname  varchar(255)  NOT NULL,
  OptionA    varchar(50),
 OptionB    varchar(50),
 OptionC    varchar(50),
 OptionD    varchar(50),
 Other        varchar(50),
 Answer     varchar(1),
 PRIMARY KEY (Qserialno, Questionid  )
);
CREATE TABLE Final
(
 Userid    int(4) ,
 Eventid   int(4)  NOT NULL,
 Questionid  int(4)   NOT NULL,
 Qserialno    int(3)   NOT NULL,
 Answer     varchar(8)  NOT NULL
);

I had just copied the database creation page.

Thanks and Regards
Harshal

Now here is crucial question: "Do you want to submit only average project and forget about it or you want to get best out of your project and learn something?"
If

  1. answer is I want average project and forget about it, then continue as you started. Unfortunately I do not see many members want to help with such lousy coding...
  2. answer is I want to submit best project and learn in same time, then you will take this design to Database Design section and seek advice on current state of your DB design as there are serious flows in it. For example, aren't they supposed to be same?
    CREATE TABLE markedevent
    (
     userid   int(4)  NOT NULL ,
     eventid      int(4)  NOT NULL,
      PRIMARY KEY ( userid)
    );
    
    
    CREATE TABLE  Event
    (
      Eventid     int(4)  NOT NULL  AUTO_INCREMENT,
      Description   varchar (255)  NOT NULL,
      Publish       varchar(20)   NOT NULL,
      Questionid   int(4)    NOT NULL,
      Targetgroup   varchar(70)   NOT NULL,
       Anonymous  varchar(15)   NOT NULL,
       PRIMARY KEY (  Eventid )
    );

Hi peter_budo,
Thanks for yor post.
The highlighted column in two tables stores the same data.
Now forget about markedevent table for time being.
now if i want to restrict user against polling same questions in second times, i check userid and eventid in final output table like:

rs=stmt("SELECT userid FROM final where Userid='"+USERID+"'and Eventid='"+EVENTID+'""")

if the user has polled perticular event previusly then his userid and corresponding eventid will be there in final table. if not then result of rs will be null. can i use like this?

if(rs==null){

shows the event for polling}
else{

}

whether i am doing in right ways?
Thanks and Regards
Haresh

Hi peter_buto,
I am really interested about how to separate business logic from presentation logic.
Like you suggest me to do database connectivity with servlets or java class only.
As this is my first project, I require help from you.
Can I send my project folder with this post?
It’s really difficult to manage both this logic in jsp page with many times problem with closing scriplet.
Another one is can i write single page database connectivity code for entire project.
Thanks and Regards
Haresh

Yes you can attach compressed copy (in ZIP) of your project.
And it would be possible to have one servlet handling all, but I would advice against it (Java file with hundreds lines doesn't look pretty either ) and recommend splitting on more servlets to handle similar requests

OK, I downloaded files, but first I will have to re-install NetBeans as I have the simplest package for Java coding and Mobility Pack

For what sort of database is that zip file? Is that Derby or MySQL? If it is Derby you need to tell me how to import it and if it is MySQL then you didn't export it correctly...
The way to export from MySQL was something like this if I remember it:

  1. Open Command Prompt
  2. Got to top level of the hard disk C:\
  3. Type following command
    mysqldump -u YOUR_USERNAME DATABASE_NAME>FILE_NAME.sql -p

Replace sections in capital with your data and hit ENTER after "-p", where you will be asked for the password to your database. After that file with your given name and "sql" extension will be created on current directory level. So if you moved to the top as I told you it will be directly under root "C:\"

Hi Peter_bodu,
Sorry for late post as was not at my desk.
If you downloaded mysql database, then you can have the start window for it like-> start->programs->mysql_mysql server 5.o-->mysql command line client. otherwise you can access it in command promt window by using command

mysql -u userId -h Hostname -p here userId =root Hostname=localhost

Then it ask password. type password= root123
my datbase name is =mynewdatabase

Thanks and regards
Haresh

Hi Peter_bodu,
Sorry for late post as was not at my desk.
If you downloaded mysql database, then you can have the start window for it like-> start->programs->mysql_mysql server 5.o-->mysql command line client. otherwise you can access it in command promt window by using command

Thanks and regards
Haresh

I do have MySQL on my pc, but the file you provided are in wrong format. Please run that command that I provided in previous post to get them in "sql" file

I finally had look on what you done so far. Many of the JSP pages with DB connectivity can be combine into one servlet and that will reduce size of the whole project. For example login process with retrieving user info you do (assuming we have correct username and password)

index.jsp=>as.jsp(to check username&password)=>try.jsp(to retrieve user group data and display events)=>Questionforvote/fillquestion.jsp

simplified version should be

  1. index.jsp
  2. loginServlet.class
    • setup db connection
    • check if user exist
      1. user doesn't exist
        • set error message
        • set session attributes
        • return back to index.jsp that will display error message from session
      2. user does exist
        • get events available for user
        • store events in the session
        • redirect to next page
  3. events.jsp

Any questions?

Hi peter_budo,
Thank you very much for your cooperation.
I have some doubt like in loginServlet.class
Whether I have to connect database for table user and event and retrieving the neccessary field and store in session attributes?

index.jsp
loginServlet.class
setup db connection
check if user exist
user doesn't exist
set error message
set session attributes
return back to index.jsp that will display error message from session
user does exist
get events available for user
store events in the session
redirect to next page

I could not understand about: set session attributes
THis is for normal user.
Now if the user is administrative then it has to add,modyfy and delete the user,group,question,event like.
So I required seprate connection for that?
Can you give me some demo of my updated code view.for above mentioned page.
just give me atlest loginservlet logic.
Thanks and Regards
Haresh

How you gone distinguish between users groups that is what I asked you to answer in Database design section when I provided few suggestions. Unfortunately so far I did not get answer to that...

Check second post of this thread there is already partial implementation. After validation of submitted data, connect to database to retrieve info on user. The query will either return user details if username and passwords are correct or NULL if one of them fail. If null close DB connection and jump to redirect and go back to login screen. However if query return user details do not close connection but run another query this time on events related to userID and groupID. Exact queries are up to you.
Passing user details from servlet to JSP in session is very simple something like this

// what is structure of User bean doesn't matter in this example
// data retrived from the database and stored in a instance of User bean "user"
HttpSession session = request.getSession();
if(BOOLEAN_DATA_SUCCESSFULLY_RETRIEVED)
{
	session.setAttribute( "user", user);
	RequestDispatcher dispatcher = getServletContext().getRequestDispatcher("/event_page.jsp");
	dispatcher.forward( request, response);
}
else
{
        String strError = "Wrong username or password!";
	session.setAttribute( "error", strError);
	RequestDispatcher dispatcher = getServletContext().getRequestDispatcher("/index.jsp");
	dispatcher.forward( request, response);
}

Hi Peter_buto,
I want to really appreciates for your help.
Now i am going to Forward with you suggested project Structure. If i require any Help ,
I will come here again and call to you for help. As i completed 70% of my project work with older fashion,it will take some time to modulate into new form.
Thanks again
Regards
Haresh

This question has already been answered. Start a new discussion instead.