Java MySQL Update Query

Question

I am getting the error "cannot issue data".

Here is the 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 update java query doesn't.

Here is what the JTextArea sees:

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.

@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.

Answer 1

AS PreparedStatement documentation:

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.

To execute querys that update, delete or insert any data in your DB, you can't use executeQuery... You must use: .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();

Answer 2

Also consider using a parameter for the username:

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

updateEXP.setString(1, loggedusername);

ResultSet updateEXP_done = updateEXP.executeUpdate();

Answer 3

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

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

That said, closing the conn (and Statement and ResultSet!) should be done in the finally block, else it will still be open when an exception is been thrown before close() is called. Also your use of PreparedStatement is 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.

See also:

• Basic JDBC tutorial
• Using PreparedStatement

Answer 4

executeUpdate(query) instead of executeQuery()