Home > Mobile >  Passing SQL query results into ArrayList<Object>, returning list to servlet
Passing SQL query results into ArrayList<Object>, returning list to servlet

Time:07-20

I am trying to refactormy code, in doing so I am trying to leave the SQL querying to one class. I am trying to pass parameters from an html form to servlet, which then passes those parameters to the ApplicationDao in its method params, which (should) return the result set in the form of a user object arraylist. When I pass the email it is saying column not found, though when I do the exact same thing from within the servlet I am refactoring the results are fine. Obviously, the array list is passing length of 0 back because of the issue!

I am really confused as to what I am doing wrong and any advice would be appreciated!

Database Structure

LOG IN HTML FORM

<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Log In: Canadian Voting Portal</title>
<link rel="stylesheet" type="text/css" href="style2.css">
</head>
<body id="logInPage">
<div >
<img src="images/CanadaLogo.jpg" width="150px" height="70px">
<h1 id="LogInHeading">Voting Portal Website & Access</h1>

<div >

<div >
<form method="post" action="testServlet">
<label  id="emailLabel" for="email">Email:</label>
<input  type="text" id="userEmail" name="email">
<br>
<label  id="passwordLabel" for="password">Password:</label>
<input  type="text" id="userPassword" name="password">
<br>
<button id="logInButton" type="submit">LOG IN</button>
</form>
<p id="createParagraph">Don't have an account? Create one <a id="createLink" href="http://localhost:8080/CapstoneProject/CreateAccountServlet">here.</a> Forgot log-in information? Start the reset process of your account <a id="resetLink" href="http://localhost:8080/CapstoneProject/ResetPasswordServlet">here.</a></p>
</div>

</div>

</div>
</body>
</html>

APPLICATION DAO

public class ApplicationDao {

    

    public ArrayList<User> userLogIn(String email, String password){

        
        ArrayList<User> users = new ArrayList<User>();
        
        try{
            
            
        
            
            String sql="SELECT * FROM `user` WHERE email = ? and password = ?" ;
            
            Connection connection =DBConnection.getConnectionToDatabase();
            java.sql.PreparedStatement statement;
            
            statement = connection.prepareStatement(sql);
            statement.setString(1,email);
            statement.setString(2,password);
            
            
            ResultSet set = statement.executeQuery();
            
            if(set.next()){         
            
                User user = new User();
                
                user.setEmail(set.getString(email));
                user.setPassword(password);
                user.setDateOfBirth("DOB");
                user.setFirstName("firstName");
                user.setLastName("lastName");
                
                set.close();
                statement.close();
                
                users.add(user);
            }

        }
        catch(SQLException exception){
            exception.printStackTrace();
        }
        return users;
        
        }

    }

TEST SERVLET

@WebServlet("/testServlet" )
public class testServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
    //List<User> users = new ArrayList<User>();

    public testServlet() {
        super();
    }
   
    
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        
    
    }
    
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException{
        
        String email = request.getParameter("email");
        String password = request.getParameter("password");
        PrintWriter writer = response.getWriter();
        ApplicationDao dao = new ApplicationDao();
        ArrayList<User> user = dao.userLogIn(email,password);
        
        String DOB = user.get(0).getDateOfBirth();
        
        
        
        
        writer.write(email   DOB);
    }}

CONSOLE (when passing params from servlet)

MySQL JDBC Driver Registered!
Connection made to DB!
java.sql.SQLException: Column '[email protected]' not found.
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
    at com.mysql.cj.jdbc.result.ResultSetImpl.findColumn(ResultSetImpl.java:584)
    at com.mysql.cj.jdbc.result.ResultSetImpl.getString(ResultSetImpl.java:896)
    at dao.ApplicationDao.userLogIn(ApplicationDao.java:45)
    at servlets.testServlet.doPost(testServlet.java:51)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:681)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1722)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.base/java.lang.Thread.run(Thread.java:834)
Jul. 19, 2022 10:16:10 A.M. org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [servlets.testServlet] in context with path [/CapstoneProject] threw exception
java.lang.IndexOutOfBoundsException: Index 0 out of bounds for length 0
    at java.base/jdk.internal.util.Preconditions.outOfBounds(Preconditions.java:64)
    at java.base/jdk.internal.util.Preconditions.outOfBoundsCheckIndex(Preconditions.java:70)
    at java.base/jdk.internal.util.Preconditions.checkIndex(Preconditions.java:248)
    at java.base/java.util.Objects.checkIndex(Objects.java:372)
    at java.base/java.util.ArrayList.get(ArrayList.java:459)
    at servlets.testServlet.doPost(testServlet.java:53)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:681)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1722)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.base/java.lang.Thread.run(Thread.java:834)

CONSOLE (when hard coding params)

CodePudding user response:

The error is in the line 45 of your ApplicationDao, looking at the stack trace:

    at dao.ApplicationDao.userLogIn(ApplicationDao.java:45)

I assume it's this line:

user.setEmail(set.getString(email));

You pass the email variable as the column name. It should be:

user.setEmail(set.getString("email"));
  • Related