DaniWeb Forum Index > Web Development > JSP

retrieving data from database into jsp page

Please support our JSP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Jul 2008

Posts: 50

Reputation: shijunair is an unknown quantity at this point

Solved Threads: 0

shijunair

Offline

Junior Poster in Training

retrieving data from database into jsp page

Sep 11th, 2008

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

Help with Code Tags

(Toggle Plain Text)

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.

•

Join Date: Dec 2004

Posts: 4,176

Reputation: peter_budo has much to be proud of

Solved Threads: 479

peter_budo peter_budo is offline

Offline

Code tags enforcer

Re: retrieving data from database into jsp page

Sep 11th, 2008

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

Learn to see in another's calamity the ills which you should avoid.
Publilius Syrus
(~100 BC)
LJC - London Java Community, Graduate & Undergraduate Software Development Community, JAVAWUG (Java Web User Group), The London Android Group

•

Join Date: Jul 2008

Posts: 50

Reputation: shijunair is an unknown quantity at this point

Solved Threads: 0

shijunair

Offline

Junior Poster in Training

Re: retrieving data from database into jsp page

Sep 11th, 2008

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.

•

Join Date: Dec 2004

Posts: 4,176

Reputation: peter_budo has much to be proud of

Solved Threads: 479

peter_budo peter_budo is offline

Offline

Code tags enforcer

Re: retrieving data from database into jsp page

Sep 11th, 2008

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT m_leavetype, m_bal FROM TABLENAME WHERE m_emp_no='1004';
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?

•

Join Date: Jul 2008

Posts: 50

Reputation: shijunair is an unknown quantity at this point

Solved Threads: 0

shijunair

Offline

Junior Poster in Training

Re: retrieving data from database into jsp page

Sep 11th, 2008

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

•

Join Date: Jul 2007

Posts: 1,176

Reputation: stephen84s is a glorious beacon of light

Solved Threads: 125

stephen84s stephen84s is offline

Offline

Veteran Poster

Re: retrieving data from database into jsp page

Sep 11th, 2008

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT SUM(m_bal)
FROM TABLENAME 
WHERE m_emp_no='1004';
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:-

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT m_emp_no,SUM(m_bal) 
FROM TABLENAME
GROUP BY (m_emp_no)
SELECT m_emp_no,SUM(m_bal) 
FROM TABLENAME
GROUP BY (m_emp_no)

Last edited by stephen84s; Sep 11th, 2008 at 12:05 pm.

"Any fool can write code that a computer can understand. Good programmers write code that humans can understand."

"How to ask questions the smart way ?"

•

Join Date: Dec 2004

Posts: 4,176

Reputation: peter_budo has much to be proud of

Solved Threads: 479

peter_budo peter_budo is offline

Offline

Code tags enforcer

Re: retrieving data from database into jsp page

Sep 11th, 2008

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.

•

Join Date: Jul 2007

Posts: 1,176

Reputation: stephen84s is a glorious beacon of light

Solved Threads: 125

stephen84s stephen84s is offline

Offline

Veteran Poster

Re: retrieving data from database into jsp page

Sep 11th, 2008

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.

Last edited by stephen84s; Sep 11th, 2008 at 12:21 pm.

"Any fool can write code that a computer can understand. Good programmers write code that humans can understand."

"How to ask questions the smart way ?"

•

Join Date: Jul 2008

Posts: 50

Reputation: shijunair is an unknown quantity at this point

Solved Threads: 0

shijunair

Offline

Junior Poster in Training

Re: retrieving data from database into jsp page

Sep 12th, 2008

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

 Help with Code Tags 
 java Syntax (Toggle Plain Text) 
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();
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

Last edited by peter_budo; Sep 12th, 2008 at 5:19 am. Reason: Keep It Organized - please use [code] tags

•

Join Date: Jul 2007

Posts: 1,176

Reputation: stephen84s is a glorious beacon of light

Solved Threads: 125

stephen84s stephen84s is offline

Offline

Veteran Poster

Re: retrieving data from database into jsp page

#10

Sep 12th, 2008

•

Help with Code Tags

java Syntax (Toggle Plain Text)
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();
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.

Last edited by stephen84s; Sep 12th, 2008 at 2:08 am.

"Any fool can write code that a computer can understand. Good programmers write code that humans can understand."

"How to ask questions the smart way ?"