Java MySQL 更新查询

Java MySQL Update Query

提问by test

I am getting the error "cannot issue data".

我收到错误“无法发布数据”。

Here is the SSCCE

这是 SSCCE

//package mysqltest;

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.applet.Applet;
import java.awt.TextArea.*;
import java.sql.*;
import java.util.*;
import javax.swing.plaf.*;
import javax.swing.plaf.basic.*;
import java.net.*;
import java.applet.*;

    public class test extends JApplet {

      public JTextArea c;

      public void init() {

          c = new JTextArea();

          add(c);
          c.append("Looking for database...");

        Connection conn = null;
        Properties props = new Properties();
        String url = "jdbc:mysql://localhost:3306/";
        String dbName = "mystik";
        String driver = "com.mysql.jdbc.Driver";
        String userName = "root";
        String password = "";
        String loggedusername = getParameter("name");
        try {
          Class.forName(driver).newInstance();
          props.put("user", "root");
          conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mystik", props);
          c.append("\nConnected to the database");


                        c.append("\nGetting stats for: " + loggedusername);
                        PreparedStatement statement = conn.prepareStatement( "select * from `user` where `username` = '"+loggedusername+"'");
                        ResultSet result = statement.executeQuery();
                        // just a dumb mysql statement!
                        while(result.next())
                        {
                        c.append("\nUsername: "+result.getString(2)+ "\nLevel: "+result.getString(6)+"\nEXP: "+result.getString(8)+"\n");
                        }

                        PreparedStatement updateEXP = conn.prepareStatement( "update`user` set `exp` = '666'  where `username` = '"+loggedusername+"'");
                        ResultSet updateEXP_done = updateEXP.executeQuery();

                        while(result.next())
                        {
                        c.append("\nUsername: "+result.getString(2)+ "\nLevel: "+result.getString(6)+"\nEXP: "+result.getString(8)+"\n");
                        }


          conn.close();
          c.append("\nDisconnected from database");
        } catch (Exception e) {
          e.printStackTrace();
        }

      }

    }

and it works... and it's just that updatejava query doesn't.

它有效......只是updatejava查询没有。

Here is what the JTextArea sees:

这是 JTextArea 看到的：

Looking for database...
Connected to the database
Getting stats for: weka
Username: weka
Level: 1
EXP: 1

and here is my error:

这是我的错误：

added manifest
adding: test.class(in = 2440) (out= 1308)(deflated 46%)
adding: mysql-connector-java-5.1.13-bin.jar(in = 767492) (out= 735869)(deflated
4%)

Warning:
The signer certificate will expire within six months.
java.sql.SQLException: Can not issue data manipulation statements with executeQu
ery().
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
        at com.mysql.jdbc.StatementImpl.checkForDml(StatementImpl.java:436)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:
2176)
        at test.init(test.java:53)
        at sun.applet.AppletPanel.run(AppletPanel.java:424)
        at java.lang.Thread.run(Thread.java:619)

Lastly, here is how i compile using a .bat file.

最后，这是我如何使用 .bat 文件进行编译。

@ECHO OFF
C:
CD \wamp\www\mystikrpg\mysqltest
javac -cp mysql-connector-java-5.1.13-bin test.java
jar cvf mysqlTry.jar test.class mysql-connector-java-5.1.13-bin.jar
jarsigner -keystore dankey -storepass soccer -keypass soccer mysqlTry.jar gamerpg
appletviewer -J-Djava.security.policy=game.policy mysqltry.html

How do I fix this error? Thanks.

我该如何解决这个错误？谢谢。

采纳答案by Garis M Suero

AS PreparedStatementdocumentation:

AS PreparedStatement文档：

Executes the SQL statement in this PreparedStatement object, which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement.

执行此 PreparedStatement 对象中的 SQL 语句，该语句必须是 SQL INSERT、UPDATE 或 DELETE 语句；或不返回任何内容的 SQL 语句，例如 DDL 语句。

To execute querys that update, delete or insert any data in your DB, you can't use executeQuery... You must use: .executeUpdate(query)

要执行更新、删除或插入数据库中任何数据的查询，您不能使用executeQuery...您必须使用：.executeUpdate(query)

So this code (WRONG):

所以这段代码（错误）：

   PreparedStatement updateEXP = conn.prepareStatement("update `user` set `exp` = '666'  where `username` = '"+loggedusername+"'");
   ResultSet updateEXP_done = updateEXP.executeQuery();

Must look like (GOOD):

必须看起来像（好）：

Correct usage

正确使用

   PreparedStatement updateEXP = conn.prepareStatement("update `user` set `exp` = ? ");
   updateEXP.setString(1, loggedusername);
   ResultSet updateEXP_done = updateEXP.executeUpdate();

回答by BalusC

As per the Javadoc, DML queries (INSERT, UPDATE, DELETE) needs to be executed using executeUpdate(), not executeQuery(). It returns an intwith amount of affected rows.

根据 Javadoc，DML 查询 ( INSERT, UPDATE, DELETE) 需要使用executeUpdate()而不是来执行executeQuery()。它返回int受影响的行数。

PreparedStatement updateEXP = conn.prepareStatement( "update`user` set `exp` = '666'  where `username` = '"+loggedusername+"'");
int affectedRows = updateEXP.executeUpdate();

That said, closing the conn(and Statementand ResultSet!) should be done in the finallyblock, else it will still be open when an exception is been thrown before close()is called. Also your use of PreparedStatementis wrong. You're still inlining the column values by simple string concatenation instead of setting them as parameterized values. You're not taking benefit of its SQL injection prevention capabilities.

也就是说，关闭conn（和Statement和ResultSet！）应该在finally块中完成，否则在close()调用之前抛出异常时它仍然会打开。你的使用PreparedStatement也是错误的。您仍然通过简单的字符串连接来内联列值，而不是将它们设置为参数化值。您没有利用其 SQL 注入预防功能。

See also:

也可以看看：

• Basic JDBC tutorial
• Using PreparedStatement

• 基本的 JDBC 教程
• 使用 PreparedStatement

回答by Eton B.

executeUpdate(query)instead of executeQuery()

executeUpdate(query)代替 executeQuery()

回答by Joshua Martell

Also consider using a parameter for the username:

还可以考虑为用户名使用参数：

PreparedStatement updateEXP = conn.prepareStatement("updateusersetexp= '666' whereusername= ? ");

PreparedStatement updateEXP = conn.prepareStatement("update用户setexp= '666' where用户名= ? ");

updateEXP.setString(1, loggedusername);

updateEXP.setString(1, loggedusername);

ResultSet updateEXP_done = updateEXP.executeUpdate();

ResultSet updateEXP_done = updateEXP.executeUpdate();