MySQL查询可在PhpMyAdmin中使用,但不能在JAVA Eclipse中使用 [英] MySQL Query works in PhpMyAdmin but not in JAVA Eclipse
问题描述
我是Java编程的完全新手,但我正在努力.我已经进行了广泛的搜索,但是即使许多新手程序员都遇到了同样的问题,我也无法弄清楚.
I'm a complete novice in Java programming but I'm hammering away. I have searched extensively but I can't figure it out even though many fellow beginner programmers have had the same problem..
我遇到的是Java<> MySQL问题,我无法解决这个问题!
I have is this Java <> MySQL problem, and I can't wrap my head around it!
如果我在phpMyAdmin中执行此查询:
If I execute this query in phpMyAdmin:
SET count = 0; UPDATE tablename SET table_id = count:= count + 1;
ALTER TABLE tablename AUTO_INCREMENT = 1;
它可以按我想要的方式工作,重置ID(在删除一行之后),它可能不是最优雅的解决方案,但可以完成工作.
It works as I want it to, reset ID (after I delete a row) it may not be the most elegant solutions but it gets the job done.
但是,一旦我从eclipse执行相同的查询,我的控制台就会像圣诞树一样亮起来,并且我得到一个:
But as soon as I execute the same query from eclipse my console lights up like a Christmas tree and I get a:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:您有一个 您的SQL语法错误;检查与您的手册相对应的手册 MySQL服务器版本,用于在'UPDATE附近使用的正确语法 tablename SET table_id = @count:= @count + 1; ALTER TABLE表名 AU'在第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 'UPDATE tablename SET table_id = @count:= @count + 1; ALTER TABLE tablename AU' at line 1
错误...请检查我的语法.它可以在phpMyAdmin中工作. 我的Eclipse Java方法:
Error... check my syntax. It works in phpMyAdmin. My Eclipse Java method:
public void resetDBID(){
try{
// query to reset menu_id manly after delete row
Statement statement = con.connection.createStatement();
String sset;
sset = "SET @count = 0; "
+ "UPDATE afhaalmenus SET menu_id = @count:= @count + 1; "
+ "ALTER TABLE afhaalmenus AUTO_INCREMENT = 1;";
statement.execute(sset);
} catch(SQLException ex){
String message = "rest ID fail";
JOptionPane.showMessageDialog(null, message, "method fail", JOptionPane.INFORMATION_MESSAGE);
ex.printStackTrace();
}
}
我在这里想念的是什么?还是做错了... 预先感谢您的所有帮助!
What is it that I'm missing here? Or doing wrong... Thanks in advance for all of your help!
推荐答案
您可以使用addBatch
You can use addBatch
String s1 = "SET @count = 0";
String s2 = ...
try {
// query to reset menu_id manly after delete row
connection = database.getConnection();
Statement statement = connection.createStatement();
statement.addBatch(s1);
statement.addBatch(s2);
statement.addBatch(s2);
s.executeBatch();
connection.commit();
} catch (SQLException e) {
connection.rollback();
throw e;
} finally {
close(statement);
close(connection);
}
这篇关于MySQL查询可在PhpMyAdmin中使用,但不能在JAVA Eclipse中使用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!