[Java]Error when Inserting SQL, auto increment in the table - userId

Question

So I am making a registration page and when I enter all the fields and click signup to submit, enterNewUser(,,,) is called and the fields userId, username,password and role are inserted into the table User. I confirm this by running select * from user; into MYSQL workbench.

Then enterUsername(,,,) is called and I get this error:

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 '(3,'Barry','Allen')' at line 1 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 '(3,'Barry','Allen')' at line 1

public static int enterNewUser(String username,String password, String role){
    //int userId = -1;
    int ID = 0;
    //int ID=-1;
    try{
        if(checkUserNameAvailable(username)==true){
            Class.forName("com.mysql.jdbc.Driver");
            cn = DriverManager.getConnection("jdbc:mysql://localhost/log", "root", "root");

            String q0 = "Select userId from user ORDER BY userId DESC LIMIT 1"; //get ID of last
            Statement st = cn.createStatement();
            ResultSet rs = st.executeQuery(q0);

            if(rs.next()){

                ID = rs.getInt("userId");
                ID++;
            }
            else
                ID=1; // Empty Table, so start with ID 1

            rs.close();
            st.close();

            String q1="insert into user values(?,?,?)";

            PreparedStatement ps = cn.prepareStatement(q1);
            //ps.setInt(1,ID);
            ps.setString(1,username);
            ps.setString(2,password);
            ps.setString(3,role); 
            ps.executeUpdate();
            ps.close();

        }
    }catch(Exception e){
        System.err.println(e.getMessage());
        e.printStackTrace();
    }
    DB_close();
    //if(userId!=-1)
    //  return userId;
    return -1;      
}
public static boolean enterUsername(int userId, String firstname, String lastname){
    try{
        Class.forName("com.mysql.jdbc.Driver");
        cn = DriverManager.getConnection("jdbc:mysql://localhost/log", "root", "root");

        //String q1="INSERT INTO user_profile values(?,?,?)";
        String q1 = "INSERT into user_profile (userId, firstname, lastname) VALUES (?,?,?)";

        PreparedStatement ps = cn.prepareStatement(q1);
        ps.setInt(1, userId);
        ps.setString (1, firstname);
        ps.setString (2, lastname);
        ps.executeUpdate();
        ps.close();
        return true;
    }catch(Exception e){
        System.err.println(e.getMessage());
        e.printStackTrace();
    }
    DB_close();
    return false;
}

Here is my database structure.

Edit: found the issue, database was not structured properly.

CREATE TABLE user ( userId int(3) NOT NULL AUTO_INCREMENT,
username varchar(20) DEFAULT NULL, password varchar(20) DEFAULT NULL, role varchar(20) DEFAULT NULL, PRIMARY KEY (userId),
UNIQUE KEY username (username) );

CREATE TABLE user_profile ( userId int(3) NOT NULL DEFAULT '0', firstName varchar(20) DEFAULT NULL, lastName varchar(20) DEFAULT NULL, PRIMARY KEY (userId), CONSTRAINT FK FOREIGN KEY (userId) REFERENCES user (userId) );

Answer 1

Shouldn't following section in method enterUsername

ps.setInt(1, userId);
ps.setString (1, firstname);
ps.setString (2, lastname);

be like this

 ps.setInt(1, userId);
 ps.setString (2, firstname);
 ps.setString (3, lastname);

Answer 2

I don't see the reason for the error message that you posted.

But I see some other things that look like a problem:

ps.setInt(1, userId);
ps.setString (1, firstname);
ps.setString (2, lastname);

The indexes are wrong: instead of 1, 1, 2, it should be 1, 2, 3. (Frankly, I don't see how the code could possibly work as posted.)

Btw, this also looks wrong in the other method:

insert into user values(?,?,?)

As the table has more than 3 columns, you need to specify their names, like you did in enterUsername.

Answer 3

Use

String q1 = "INSERT into user_profile (firstname, lastname) VALUES (?,?)";

because your first field is auto increment..So it automatically increment values while inserting values.

I recommended this way,

Delete your current table and create a new one like this

   id-->int(30) (AUTO INCREMENT) NOTNULL  //Dont need to take care of this field

   USER_ID-->int(30) NOT NULL  //you should create your own ID and increment it before adding a new person

   username-->varchar(100)

   password-->varchar(100)

   role-->varchar(100)

and usually, call userId exactly same like your code,

String q0 = "Select userId from user ORDER BY USER_ID DESC LIMIT 1"; //get ID of last
        Statement st = cn.createStatement();
        ResultSet rs = st.executeQuery(q0);

        if(rs.next()){

            ID = rs.getInt("USER_ID ");
            ID++;
        }