MySQL/Java error

问题:

Newbie programmer here. Upon doing mvn tomcat:run I get the following error:

SEVERE: Servlet.service() for servlet appServlet threw exception
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 'values (?, ?)' at line 1

The code in question is as follows:

public void create(User user) {
    this.jdbcTemplate.update("INSERT INTO xyz.user(user_name, user_password values (?, ?)");


            user.getUserName(); user.getId();

}

public void delete(User user) {
    this.jdbcTemplate.update("DELETE FROM xyz.user WHERE id = ?");


}

public void update(User user) {
    this.jdbcTemplate.update(
            "UPDATE xyz.user SET UserName = ? password = ? WHERE id = ?");

Googled - couldn't find a solution for (?, ?) scenarios. Pls. help - Thx in advance :)

Here's the complete code (almost) - I am doing something wrong but can't figure out what.

public User find(String login) {
  System.out.println("Trying to find the user...." + login);    
  User user = this.jdbcTemplate.queryForObject(
        "select * from xyz where user_name = ?",
        new Object[]{login},
        new RowMapper<User>() {
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setId(Long.valueOf(rs.getInt(1)));
                user.setUserName(rs.getString(2));
                user.setPassword(rs.getString(3));
                return user;
            }
        });
  System.out.println("Found user..." + user);
   return user;
}

public void create(User user) {

    this.jdbcTemplate.update("INSERT INTO ibstechc_dev.user(user_name, user_password) VALUES (?,?)");

            user.getUserName(); user.getId() ;
}


public void delete(User user) {
    this.jdbcTemplate.update("DELETE FROM xyz WHERE id = ?");
    // TODO Auto-generated method stub

}

public void update(User user) {
    this.jdbcTemplate.update(
            "UPDATE xyz SET user_name = ?, user_password = ? WHERE id = ?");
    // TODO Auto-generated method stub

}

}

I am stuck with the same error - tomcat:run throws the following -

SEVERE: Servlet.service() for servlet appServlet threw exception

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 '?,?)' at line 1

回答1:

Use this code:

    this.jdbcTemplate.update("INSERT INTO xyz.user(user_name, user_password) values (?, ?)");

There was an issue with your SQL statement. To give you perspective you were trying to do:

INSERT INTO xyz.user(user_name, user_password values ('testuser','testpass'))

instead of

INSERT INTO xyz.user(user_name, user_password) values ('testuser','testpass'))

Hope this makes sense?

回答2:

I think here is the sql syntax problem:

INSERT INTO xyz.user(user_name, user_password values (?, ?)

replace this by

INSERT INTO xyz.user(user_name, user_password) values (?, ?);

回答3:

There is a sql syntax error in update

public void update(User user) {
    this.jdbcTemplate.update(
            "UPDATE xyz.user SET UserName = ? password = ? WHERE id = ?");

this is not the way to update,give a comma(,) like this

public void update(User user) {
    this.jdbcTemplate.update(
            "UPDATE xyz.user SET UserName = ? ,password = ? WHERE id = ?");

回答4:

your error is this statement

this.jdbcTemplate.update("INSERT INTO xyz.user(user_name, user_password values (?, ?)");

It should be

this.jdbcTemplate.update("INSERT INTO xyz.user(user_name, user_password) values (?, ?)");

Hope it helps

Cheers

回答5:

Try this:-

this.jdbcTemplate.update("INSERT INTO xyz.user(user_name, user_password) values (?, ?)");

instead of

this.jdbcTemplate.update("INSERT INTO xyz.user(user_name, user_password values (?, ?)");