mysql[Java]插入SQL时出错,在表userId中自动递增
因此,我正在创建一个注册页面,当我输入所有字段并单击“注册”提交时,会调用enterNewUser(,,,)并将字段userId、username、password和role插入到User表中。我通过运行select*from user来确认这一点进入MYSQL工作台
然后调用enterUsername(,,,),我得到以下错误:
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;
}
这是我的数据库结构
编辑:发现问题,数据库结构不正确
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 KEYusername
(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
), CONSTRAINTFK
FOREIGN KEY (userId
) REFERENCESuser
(userId
) );
# 1 楼答案
使用
因为你的第一个字段是
auto increment
。。因此,它会在插入值时自动增加值我建议这样
删除当前表并创建一个新表,如下所示
通常,调用与代码完全相同的userId
# 2 楼答案
不应该在方法enterUsername中包含以下部分
像这样