Problem with update and create methods in MVC

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!

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

the problem is in line no 85

and line no 89

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

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