请问我在mysql中的查询界面中可以执行下面的一句语句
set @mycnt = 0; insert into a select (@mycnt := @mycnt + 1) as ROWNUM , user_id from a_temp LIMIT 1000000000;但是我在程序中通过下面的方法却不能执行:
Connection conn = DBConnect.getConnection();
Statement statement_add1 = null;
String sql_add1 = "set @mycnt = 0; insert into a select (@mycnt := @mycnt + 1) as ROWNUM , a_temp.user_id from a_temp LIMIT 1000000000";
try {
statement_add1 = (Statement) conn.createStatement();
statement_add1.executeQuery(sql_add1);
statement_add1.close;
conn.close;
} catch (SQLException e) {
e.printStackTrace();
}但是抛出了下面的异常: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 'insert into a select (@mycnt := @mycnt + 1) as ROWNUM , a_temp.user_id from a_te' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2569)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1521)
at Project.GetUserDataJFrame$AA.run(GetUserDataJFrame.java:203)
set @mycnt = 0; insert into a select (@mycnt := @mycnt + 1) as ROWNUM , user_id from a_temp LIMIT 1000000000;但是我在程序中通过下面的方法却不能执行:
Connection conn = DBConnect.getConnection();
Statement statement_add1 = null;
String sql_add1 = "set @mycnt = 0; insert into a select (@mycnt := @mycnt + 1) as ROWNUM , a_temp.user_id from a_temp LIMIT 1000000000";
try {
statement_add1 = (Statement) conn.createStatement();
statement_add1.executeQuery(sql_add1);
statement_add1.close;
conn.close;
} catch (SQLException e) {
e.printStackTrace();
}但是抛出了下面的异常: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 'insert into a select (@mycnt := @mycnt + 1) as ROWNUM , a_temp.user_id from a_te' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2569)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1521)
at Project.GetUserDataJFrame$AA.run(GetUserDataJFrame.java:203)
2)executeQuery 是否支持 INSERT,是不是要用executeUpdate
连接字符串有这么个参数。默认是FALSE。改成TRUE试一下。
Statement ps=conn.createStatement();
ps.addBatch("set @mycnt = 0;");
ps.addBatch("insert into a select (@mycnt := @mycnt + 1) as ROWNUM , user_id from a_temp LIMIT 1000000000;");
ps.executeBatch();