How to change Result-set data view types

[Kishor Joshi]

Hi there
I am making a web-application in which I am verifying data send by user from database reply.I am getting data from both side
from web page and also from database but problem is I want to compare only data obtain from web page to result set data.But It compares with all data.

Let me explain
here is my code

//Create a Connection with database
			conn=new DatabaseConnection().connection();
			
			//Get all parameters
			String email=request.getParameter("email");
			String pass=request.getParameter("pass");
			System.out.println("Value got successfully:"+email+pass);
			
			//Create a Statement to query from database
			Statement stmt=conn.createStatement();
			
			//Create a Query to execute on database
			String query="SELECT EMAIL,PASSWORD from data";
			
			//Execute Query and Obtain result set of data
			ResultSet rs=stmt.executeQuery(query);
			
			
			
			while(rs.next())
			{
				String returnEmail=rs.getString("EMAIL");
				String returnPass=rs.getString("PASSWORD");
				if((returnEmail.equals(email))&&(returnPass.equals(pass)))
				{
			    out.println("Welcome:"+email);
		        }
				else
				{
					out.println("Not match");
				}
				
			}
		}

in this case I want that welcome message only displayed in browser when both email and password match.
but I am seeing this type of response(suppose value is found on database)

Not match
Not match
Not match
Not match
Welcome:hi
Not match

I want only welcome:hi display when a succesful match in other case no msg on output.

if I remove else condition no output is displayed although there is a successful match.

any more clarfication please ask


Thanking you

[Jeanne Boyarsky]

Removing the else shouldn't break that. Most people want to display something else if there is not a successful match. Therefore this pattern is common for validation. It uses a PreparedStatement so you don't have to worry about someone attacking your database. It also only returns matching emails rather than passing the password all over.

If this is for a real application (vs a school assignment), you should also encrypt the password.

           String query="SELECT EMAIL from data where userId = ? and password = ?";  
              
            //Execute Query and Obtain result set of data  
            PreparedStatement stmt =conn.prepareStatement(query);  
            stmt.setString(1, email);
            stmt.setString(2, password);
            ResultSet rs = stmt.executeQuery();
              
              
            if(rs.next())  
            {  
                out.println("Welcome:"+email);  
            }  

[K. Tsang]

It's your query (line 13). You need to put in the where clause to get the exact row/record.

You can use PreparedStatement to pass in the parameters.

[Kishor Joshi]

ok here is few changes that i made in my code

try {
			//Create a Connection with database
			conn=new DatabaseConnection().connection();
			
			//Get all parameters
			String email=request.getParameter("email");
			String pass=request.getParameter("pass");
			System.out.println("Value got successfully:"+email+pass);
			
			//Create a Statement to query from database
			Statement stmt=conn.createStatement();
			
			//Create a Query to execute on database
			String query="SELECT EMAIL,PASSWORD from data";
			
			//Execute Query and Obtain result set of data
	          ps=conn.prepareStatement(query);
	          ps.setString(1, email);
	          ps.setString(2, pass);
	          ResultSet rs=ps.executeQuery(query);
			System.out.println("Query is eecuted:");
			
			
			if(rs.next())
			{
			
			    out.println("Welcome:"+email);
		    }
		    else
				{
					out.println("Not match");
				}
				
			
		}

but i am getting following error on my console

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3729)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3713)
at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4553)
at login.Login.doGet(Login.java:50)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2441)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2430)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)

How to sort out this?


Thanks for all your help

[Jeanne Boyarsky]

Now you have two Statement objects (stmt and ps)

You should delete this one:
Statement stmt=conn.createStatement();

[Kishor Joshi]

i deleted this one connection now my code is like

//Create a Connection with database
			conn=new DatabaseConnection().connection();
			
			//Get all parameters
			String email=request.getParameter("email");
			String pass=request.getParameter("pass");
			System.out.println("Value got successfully:"+email+pass);
			
			
			
			//Create a Query to execute on database
			String query="SELECT EMAIL,PASSWORD from data WHERE EMAIL=? and PASSWORD=?";
			
			//Execute Query and Obtain result set of data
	          ps=conn.prepareStatement(query);
	          ps.setString(1, email);
	          ps.setString(2, pass);
	          ResultSet rs=ps.executeQuery(query);
			System.out.println("Query is eecuted:");
			
			
			if(rs.next())
			{
			
			    out.println("Welcome:"+email);
		    }
		    else
				{
					out.println("Not match");
				}
				
			
		}
		
			
			
				catch (ClassNotFoundException e) {
				
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

and now i am getting this msg on console

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? and PASSWORD=?' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1557)
at login.Login.doGet(Login.java:51)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2441)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2430)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)

my database structure is:please find attachment

[Kishor Joshi]

Thanks guys its done know I am making mistake in line 18

ResultSet rs=ps.executeQuery(query);

instead i corrected it to

ResultSet rs=ps.executeQuery();

and it works and i am getting what i want..


Thanks all for your support