Problem with update and create methods in MVC

[Ana Pan]

Hello all,

I have a login MVC, where in my model User.java I have 2 methods that are not working correctly:

1-create method creates a new user in database, but servlet still redirects me to "operation failed" page
2-update method shows this when deployed:
java.lang.RuntimeException: 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 'SELECT userId FROM user WHERE username='oksana' AND password ='oksana'' at line
1

Can somebody please help me to understand what I am doing wrong here.

This is my model User.java

public class User {

    private String userId;
    private String userName;
    private String password;
    private String email;
    private String address;
    private String mobile;

    public User() {
    }

    public User(String userName, String password, String email, String address, String mobile) {
        this.userName = userName;
        this.password = password;
        this.email = email;
        this.address = address;
        this.mobile = mobile;
    }

    public User findUserByUserNamePassword(String uname, String pass) {

        String host = "127.0.0.1";
        String dbName = "login_example";
        int port = 3306;

        String mySqlUrl = "jdbc:mysql://" + host + ":" + port
                + "/" + dbName;

        Properties userInfo = new Properties();
        userInfo.put("user", "***");
        userInfo.put("password", "***");
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection =
                    DriverManager.getConnection(mySqlUrl, userInfo);
            Statement statement = connection.createStatement();
            String query = "SELECT * FROM user where username='" + uname + "' AND password ='" + pass + "'";
            // Send query to database and store results. 
            System.out.println(query);
            ResultSet resultSet = statement.executeQuery(query);

            while (resultSet.next()) {
                System.out.println("found");
                this.userId = resultSet.getString("userId");
                this.userName = resultSet.getString("userName");
                this.password = resultSet.getString("password");
                this.address = resultSet.getString("address");
                this.email = resultSet.getString("email");
                this.mobile = resultSet.getString("mobile");

            }

            connection.close();

        } catch (Exception ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        }



        return this;
    }

    public User updateUser(String username, String pass, String newUsername, String newPassword, String email, String addressCr, String mobile){

        String host = "127.0.0.1";
        String dbName = "login_example";
        int port = 3306;

        String mySqlUrl = "jdbc:mysql://" + host + ":" + port
                + "/" + dbName;

        Properties userInfo = new Properties();
        userInfo.put("user", "***");
        userInfo.put("password", "***");
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection =
                    DriverManager.getConnection(mySqlUrl, userInfo);
            Statement statement = connection.createStatement();
            String query1 ="SELECT userId FROM user WHERE username='" + username + "' AND password ='" + pass + "'";
            System.out.println(query1);
            this.userId=query1;
            String query2 = "UPDATE user SET userName ='"+newUsername
                    +"', password ='"+newPassword+"', email ='"+email
                    +"', address ='"+addressCr+ "',mobile='"+mobile
                    +"' WHERE userId="+userId+"";
                      
            // Send query to database and store results. 
            System.out.println(query2);
            statement.executeUpdate(query2);

            connection.close();

        } catch (Exception ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
            throw new RuntimeException(ex);
        } finally {
            if (connection != null) {

            }
        }
        return null;
    }

    public User createNewUser(String username, String password, String email, String addressCr, String mobile){

        String host = "127.0.0.1";
        String dbName = "login_example";
        int port = 3306;

        String mySqlUrl = "jdbc:mysql://" + host + ":" + port
                + "/" + dbName;

        Properties userInfo = new Properties();
        userInfo.put("user", "***");
        userInfo.put("password", "***");
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(mySqlUrl, userInfo);
            Statement statement = connection.createStatement();
            String query = "INSERT INTO user(`userId`, `userName`, `password`, `email`, `address`, `mobile`)"
                    + "VALUES(NULL,'" + username + "','" + password + "','" + email + "','" + addressCr + "','" + mobile + "')";

            // Send query to database and store results. 
            System.out.println(query);
            statement.executeUpdate(query);


            connection.close();

        } catch (Exception ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
            throw new RuntimeException(ex);
        } finally {
            if (connection != null) {
            }
        }
        return null;
    }

    public String getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getUserId() {
        return userId;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }
}

This is my controller UserSystem.java

public class UserSystem extends HttpServlet {

    /**
     * Processes requests for both HTTP
     * <code>GET</code> and
     * <code>POST</code> methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        String address = null;
        String option = request.getParameter("option");

        User u = new User();

        if (option != null) {

            
            if (option.equalsIgnoreCase("login")) {
                String username = request.getParameter("username");
                String password = request.getParameter("password");
                u = u.findUserByUserNamePassword(username, password);
                request.setAttribute("user", u);
                if (u.getUserId() != null) {
                    System.out.println(u.getEmail());
                    address = "/WEB-INF/CustomerDetails.jsp";
                } else {
                    System.out.println("notfound");
                    address = "/WEB-INF/invalid.jsp";

                }
                
                
            } else if (option.equalsIgnoreCase("create")) {
                String username = request.getParameter("username");
                String password = request.getParameter("password");
                String email = request.getParameter("email");
                String addressCr = request.getParameter("address");
                String mobile = request.getParameter("mobile");
                u.createNewUser(username, password, email, addressCr, mobile);
                request.setAttribute("user", u);
                if (u.getUserId() != null) {
                    System.out.println(u.getEmail());
                    address = "/WEB-INF/success.jsp";
                } else {
                    System.out.println("notfound");
                    address = "/WEB-INF/invalid.jsp";

                }
               

            } else if (option.equalsIgnoreCase("update")) {
                String username = request.getParameter("username");
                String password = request.getParameter("password");
                String newUsername = request.getParameter("username");
                String newPassword = request.getParameter("password");
                String email = request.getParameter("email");
                String addressCr = request.getParameter("address");
                String mobile = request.getParameter("mobile");
                u.updateUser(username, password,newUsername,newPassword, email, addressCr, mobile);
                request.setAttribute("user", u);
                if (u.getUserId() != null) {
                    System.out.println(u.getEmail());
                    address = "/WEB-INF/success.jsp";
                } else {
                    System.out.println("notfound");
                    address = "/WEB-INF/invalid.jsp";

                }
                


            }
            RequestDispatcher dispatcher =
                    request.getRequestDispatcher(address);
            dispatcher.forward(request, response);
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP
     * <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP
     * <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
}

Big THANK YOU in advance!

[Bear Bibeault]

Copied to the JDBC forum as this seems to be primarily JDBC issues.

[Ankit Dan]

the problem is in line no 85

 this.userId=query1;

and line no 89

 +"' WHERE userId="+userId+"";

as you are trying to assign the whole query to userid instead of storing the result from query1 to userId
hope this helps...

[Sresh Rangi]

It would be less error prone, and more secure, to use PreparedStatements with parameters to create all these queries.