Forum:

JDBC and Relational Databases

problem with code

Greenhorn

Posts: 2

posted 19 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

hi friends iam doing online project,using servlets and jsps
first i developed a login page which will call servlet,in servlet i will get
the form details (consists of username and password) and using this form details i will get the details of user which i already stored before in oracle database.and finally i compare with form password with database password(which i already stored before in oracle database).and then i capture this details in a bean.if password is not equal agail i call login html page.problem is iam unable to retrieve data from database even if i give correct username and password. iam giving source code below
plzzzzzzzzzzz friends help me in this regard
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class LoginSrv extends HttpServlet {
public void doPost(HttpServletRequest req,HttpServletResponse res) throws ServletException,IOException
{
Connection con=null;
PrintWriter out=null;
res.setContentType("text/html");
out=res.getWriter();
pack.AccountBean abean=new pack.AccountBean();
try
{

Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection("jdbc

racle:thin:@localhost:1521

rcl","scott","tiger");
Statement stm=con.createStatement();
String un=req.getParameter("uname");
String q="select * from user_acc where username='" + un + "'";

ResultSet rs=stm.executeQuery(q);
out.println(rs);
out.println("before if part");
if(rs.next())
{
out.println("in if part");
String pass=rs.getString("password");
String pw=req.getParameter("password");
String fname=rs.getString("firstname");
String lname=rs.getString("lastname");
String email=rs.getString("email");
String street=rs.getString("street");
String city=rs.getString("city");
String state=rs.getString("state");
String country=rs.getString("country");
String telephone=rs.getString("telephone");
String card=rs.getString("card");
String cardno=rs.getString("cardno");
if(pw.equals(pass) )
{

abean.setUsername(un);
abean.setPassword(pw);
abean.setFirstname(fname);
abean.setLastname(lname);
abean.setEmail(email);
abean.setStreet(street);
abean.setCity(city);
abean.setState(state);
abean.setCountry(country);
abean.setTelephone(telephone);
abean.setPaymode(card);
abean.setCardnum(cardno);
req.getSession().invalidate();
HttpSession s=req.getSession(true);
s.setAttribute("acc",abean);

out.println("<html> <body bgcolor='yellow'> <center><font color=blue size=6> Login successful " +
"<br> Redirecting To Home Page </font> </center></div></body></html>");

RequestDispatcher rd=req.getRequestDispatcher("home.jsp");
rd.include(req,res);

}
else
{
RequestDispatcher rd=req.getRequestDispatcher("login.html");
rd.include(req,res);
out.println("in else part1");
}
}

else
{
RequestDispatcher rd=req.getRequestDispatcher("login.html");
rd.include(req,res);
out.println("in else part2");
}
}
catch(Exception e)
{
out.println("Unknown Exception "+e);
}
}
}

Ritchie Francis

Greenhorn

Posts: 2

posted 19 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

The connection will be auto committing therefore no results set. Once you've created the connection set auto commit to false.

You may want to use a PreparedStatement, as apposed to Statement, unless you do some character validation on the JSP.

I'd suggest using a finally block to close off you resources too, you could bring dowm the Application Server if you are not careful.

Hope this helps.

Maximilian Xavier Stocker

Ranch Hand

Posts: 381

posted 19 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Originally posted by Fran:
The connection will be auto committing therefore no results set. Once you've created the connection set auto commit to false.

This statement is 100% incorrect. Settting the auto commit mode of your connection affects your transaction status but has nothing to do with your ability or inability to execute queries that return Result Sets.

Gregg Bolinger

Ranch Hand

Posts: 15304

I like...

posted 19 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Fran

Welcome to Javaranch. We don't have too many rules around here but we do have a Naming Policy. Please adjust your display name by clicking here.

Thanks a bunch.

Michael Duffy

Ranch Hand

Posts: 163

posted 19 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Originally posted by Fran:
The connection will be auto committing therefore no results set. Once you've created the connection set auto commit to false.

Commits only affect write operations. You don't have to set auto commit to false prior to selecting.

You may want to use a PreparedStatement, as apposed to Statement, unless you do some character validation on the JSP.

You should use PreparedStatement, regardless of character validation. Your query won't be vulnerable to a SQL injection attack that way.

It'd be even better if you didn't do SQL in a JSP. I hope this code is in an object and not scriptlet code in a JSP.

I'd suggest using a finally block to close off you resources too, you could bring dowm the Application Server if you are not careful.

This is correct. I'm not sure about bringing down an app server, but you can get exceptions about running out of connections and cursors.

Ritchie Francis

Greenhorn

Posts: 2

posted 19 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

..and I was only trying to help!! Tough audience.

Although I dont agree that I was 100% incorrect, perhaps 99%...

String q= "select * from user_acc where username=?";
String un=req.getParameter("uname");

try
{

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc racle:thin:@localhost:1521 rcl","scott","tiger");
PreparedStatement ps =con.prepareStatement(q);
ps.setString(1,un);

ResultSet rs = ps.executeQuery();

}
catch(SQLException e)
{

}

//your stuff here

//remember you finally

Roger Chung-Wee

Ranch Hand

Posts: 1683

posted 19 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

problem is iam unable to retrieve data from database even if i give correct username and password.

Are you getting an empty ResultSet? Or is an exception being thrown?

SCJP 1.4, SCWCD 1.3, SCBCD 1.3

Maximilian Xavier Stocker

Ranch Hand

Posts: 381

posted 19 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Originally posted by Ritchie Francis:
..and I was only trying to help!! Tough audience.

Although I dont agree that I was 100% incorrect, perhaps 99%...

And you should be commended for caring enough to try and help.

But giving wrong advice is dangerous because it can mislead the person who started this thread as well as anybody who might be reading this thread in future and looking for a solution. There are more in the latter category then you might first think.

At any rate your advice about choosing PreparedStatement over Statement and the use of finally blocks was spot on.

But the commit mode/result set comment was not.

Please don't take this as a personal attack. It isn't. It is a correction of fact that was presented that was wrong. That was and is all.

Michael Duffy

Ranch Hand

Posts: 163

posted 19 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

..and I was only trying to help!! Tough audience.

You're taking this too personally.

Although I dont agree that I was 100% incorrect, perhaps 99%...

Not sure I agree that it's that high.

Some comments about the code that follows:

(1) Using "SELECT *" is not a good thing to do, even if you're requesting all the columns in the table. List each column individually.

(2) A very bad idea indeed to mix web and persistence layers this way. That getParameter() from the request should not be anywhere near persistence code.

(3) "Your stuff here" should still be inside the try block.

(4) I hope you never have an empty catch block. At least print the stack trace or log the error.

(5) You can't close Connection, Statement, or ResultSet in a finally block the way you've declared them. They've got to be declared before the try block.

String q= "select * from user_acc where username=?"; String un=req.getParameter("uname"); try { Class.forName("oracle.jdbc.driver.OracleDriver"); Connection con=DriverManager.getConnection("jdbc racle:thin:@localhost:1521 rcl","scott","tiger"); PreparedStatement ps =con.prepareStatement(q); ps.setString(1,un); ResultSet rs = ps.executeQuery(); } catch(SQLException e) { } //your stuff here //remember you finally