retrieving data from database into jsp page

[shijunair]

Hi everyone,
I want to retrieve data from database in jsp page based on the particular m_emp_no .I am finding it very difficult so please can you all help me out with the problem.
Here is the structure of my database and i am using ms.access

m_emp_no          m_leavetype          m_bal
 1004                pl                     30
 1004                sl                      7
 1004                cl                      7
 1009                pl                     30
 1009                sl                      7
 1009                cl                      7

now i want to display the m_bal for pl,sl,cl in the jsp page for the respective userid who has logged in.
Please help me its very urgent.
thanks in advance.

[peter_budo]

Have look at this tutorial. It is not finished yet, however you can use it for initial start

[shijunair]

Hello peter,
I have already gone through this and my half the project is almost done,but i am stuck into this problem, so for that reason i needed help .
I hope my post is clear and understandable.I just dont know the way to retreive the m_bal with respect to the emp no for sl,pl,cl onto the jsp page as given above.
pls help
thanks in advance.

[peter_budo]

Query to find these data on employee number 1004 would look like this in plain SQL

select m_leavetype, m_bal from TABLENAME where m_emp_no='1004';

Now you only have to cast it in your connection and replace 1004 with ID retrieved from user input.
Is that what you want?

[shijunair]

hi peter,
thanks for the reply
but through this i cant get the value individually for pl,sl,cl.
see the problem is that i have to display the total balance of leave which is remaining after the person applies for leave such as pl,sl,cl all the three on the jsp page from the table given above and for that i should get the indivigual values for pl ,sl , cl .From your query i can only get one value for all the three .
if i am not clear please let me know.
help me out.
thanks

[stephen84s]

Peter's query would give you three rows each row indicating how many leaves left of that particular type for the given employee. From your posts .. even the last one I feel you needed that.
However in case you just need the total number of leaves left for an employee you could use

SELECT SUM(m_bal)
FROM TABLENAME 
WHERE m_emp_no='1004';

The above would give you for just for one employee, in case you wish to get back the balance of leaves for all employees, you could use:-

SELECT m_emp_no,SUM(m_bal) 
FROM TABLENAME
GROUP BY (m_emp_no)

[peter_budo]

Your explanations doesn't make any sense...
First you ask

I want to retrieve data from database in jsp page based on the particular m_emp_no .

and I gave you the answer. My query will not return only one value. Will return all listing where employee number occurred. So if there are multiple entries for pl, sl and cl they will be all listed there.
Either provider clearer explanation or give example of table and example of expected output.

[stephen84s]

Honestly I was just shooting in the dark, Even I think your(Peter) first post answers the question asked(at least what I thought he was asking) perfectly.

now i want to display the m_bal for pl,sl,cl in the jsp page for the respective userid who has logged in.

[shijunair]

Actually i am a bit confused with the problem i am facing so bcz of that it might have happened that i was not able to put the question clearly or properly in front of you'll ,so for that i am sorry.
Thanks to both of you,
As suggested i have made the changes but to get it onto the jsp page should i write it this way

ArrayList bal1 = new ArrayList();
balance bal = null;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");Connection con=DriverManager.getConnection("jdbc:odbc:employee_details"); PreparedStatement p= null;
p=con.prepareStatement("select  m_bal from emp_leave_master where m_emp_no='"+user+"'");
ResultSet rs =p.executeQuery();
while(rs.next());
{
[B]balance bal = new balance();
bal.setpl_bal(rs.getString(1,"m_bal"));
bal.setsl_bal(rs.getString(2,"m_bal"));
bal.setcl_bal(rs.getString(3,"m_bal"));[/B]

bal1.add(bal);
}
con.close();

If i am doing anything wrong pls correct me.
thanks

[stephen84s]

start quote:

while(rs.next());
{
balance bal = new balance();
bal.setpl_bal(rs.getString(1,"m_bal"));
bal.setsl_bal(rs.getString(2,"m_bal"));
bal.setcl_bal(rs.getString(3,"m_bal"));

bal1.add(bal);
}
con.close();

If i am doing anything wrong pls correct me.

Yes you are doing this completely wrong.
Have you tried running the query Peter gave directly in your DBMS ?
It gives you three rows(CL,PL and SL) with two columns (m_leavetype, m_bal).
and if you add an extra order by clause you will get you CL first then PL and finally SL.

Although I can give you the code directly but I think redirecting you to the JDBC tutorial would be more helpful for you.
http://java.sun.com/docs/books/tutorial/jdbc/

Cause there you will learn how to actually extract the data from the query and most probably not need our assistance on it any more.

Also check out the javadocs for ResultSet Interface. You may figure out what you are doing wrong there.

Also please use code-tags while posting code.

[shijunair]

hi stephens,
i promise you that i would go through the jdbc tutorial entirely and get all the concepts clear but right now i have to get this done as soon as possible.if you could just tell me how to write the code for now it would be a great favour.only for this time help me with the code for this particular scenario.Please help.

[stephen84s]

That shows that you have not put in an effort to go through either of my links, why should I help you then, for a guy of average IQ that should be no more than an hours work and what guarantees that you will go through the tutorial when you no longer need it.
The major reason I am not revealing the mistake is cause the mistake is too simple.

[shijunair]

ok stephens ,
I will do as you say, no matter it takes whatever time.Actually the moment you posted me the link i am going through that only but unfortunately i cant show you .I will go through that tutorial entirely and get the solution in which i am lacking
till then bye and thanks.

[stephen84s]

First run Peter's Query in your DBMS and see how the results are displayed.

Next if your JDBC concepts are good go to my second link first (the one pointing to the javadocs of the ResultSet interface).

If the answer still doesn't click, then I guess you will need to go through the tutorial till you get what you want.

Trust me if you find and correct these small mistakes yourself, you will know how to go about larger problems.

Also I will tell you where you are going wrong you are expecting all the three parameters for SL,PL and CL in one row like:.

PL     CL     SL
30     7     7

Whereas when you run the query given by Peter attached with the order by clause as shown here:-

SELECT m_leavetype, m_bal 
FROM TABLENAME
WHERE m_emp_no='1004' ORDER BY (m_leavetype);

Note the use of the order by clause to force the database to sort the way the records are stored in your ResultSet so that you will always get "cl", "pl" and "sl" in that order in your ResultSet and not in the order their rows were inserted.
You would get three rows like this:-

m_leavetype            m_bal
 cl                     7
 pl                     30
 sl                      7

So you have to just change your code to search for CL,PL and SL vertically rather than horizontally(i.e in one row).

[shijunair]

hi,
I have found the solution and i have rectified the mistake that i was making.I cant even believe that it was such a small mistake.But anyways thanks to you all.
bye

[peter_budo]

Would you mind tell us what it was?

[stephen84s]

Hmmm ........... my crystal ball tells me my previous post was correct !!! ;)

But I could be wrong cause I am using a cheap third world imitation of a crystal ball (a paper weight to be exact ;) ).

[shijunair]

hi stephens and peter,
Actually the same code which both of you provided was very much correct but i was doing mistake from my side. i was confused with the display part into the jsp page because when i was retrieving values from the database i was getting all the values properly but when i had to display into the jsp page i was taking the string value stored in session from the previous page and not from the database bcz of which it was giving me only the first row value. but finally bcz of your help i have found the solution .
thanks to both of you.
bye take care

[chicago1985]

Have look at this tutorial. It is not finished yet, however you can use it for initial start

peter_budo,

Your tutorial is great! It fully explains MVC working with database and your descriptions are very clear and helpful. Thanks!!

[peter_budo]

peter_budo,

Your tutorial is great! It fully explains MVC working with database and your descriptions are very clear and helpful. Thanks!!

Thank you, however as mention previously tutorial is not done yet, I made some changes to database structure, deployed PeparedStatements plus some other things. All these require some post editing which I can not do now as I'm in process of finishing my MSc thesis. But on the end of October I should get back to it and finish it