使用java在mysql中使用外键插入到表中

Question

我无法使用外键插入到表中

String querystate = " insert into state (country_idcountry, State, short, Km2, Capital, Largest City)"
                    + " values (?, ?, ?, ?, ?, ?)";
preparedStmt = conn.prepareStatement(querystate);
preparedStmt.setInt(1, cID);
preparedStmt.setString(2, sName);
preparedStmt.setString(3,sShort);
preparedStmt.setInt(4, sArea);
preparedStmt.setString(5, sCapital);
preparedStmt.setString(6, sLargest_city);
preparedStmt.execute();

cID是外键，我肯定我做错了什么，但我不知道是什么

Exception in thread "main" java.lang.IllegalStateException: Cannot connect the database!
at DB_Connect.TryConn(DB_Connect.java:67)
at Main.main(Main.java:97)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
Caused by: 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 'mas grande) values (1, 'Córdoba', 'CBA', 165421, 'Córdoba', 'Córdoba')' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)
at DB_Connect.TryConn(DB_Connect.java:46)
... 6 more

Answer 1

如果有一列中有空格，则需要引用该列

例如

insert into state (country_idcountry, State, short, Km2, Capital, 'Largest City')"
                + " values (?, ?, ?, ?, ?, ?)";

Answer 2

您的SQL语法不正确

在你准备好的声明中，你应该只提到括号中的列名，并在每个相应的列上打问号。然后，您可以使用准备好的语句集方法为每一列设置值

假设你的桌子如下所示

state (country_id int, country text, state text, distance int, capital text, largest_city text)

那么插入查询语法应该是

String query= " insert into state (country_id , country , state , distance , capital , largest_city) values (?, ?, ?, ?, ?, ?)";
preparedStmt = conn.prepareStatement(query);
preparedStmt.setInt(1, cID);
preparedStmt.setString(2, cName);
preparedStmt.setString(3,sName);
preparedStmt.setInt(4, distance);
preparedStmt.setString(5, sCapital);
preparedStmt.setString(6, sLargest_city);
preparedStmt.execute();