MySQL statement to correctly update an existing record from Java

Question

MySQL statement to correctly update an existing record from Java

Navigation

#1 by (1 votes)

1

I try to update a user's data but, I receive the SQL Syntax error.

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 '(name, surname, user, password, profile_id) VALUES ('Test 1rrr', 'Test 1 'at line 1

To the client click on the update button, I do this:

dato_usuario.setId_usuario(Integer.parseInt(txtId_Usuario.getText()));
                dato_usuario.setNombre(txtNombre.getText());
                dato_usuario.setApellido(txtApellido.getText());
                dato_usuario.setUsuario(txtUsuario.getText());
                dato_usuario.setContrasena(txtContrasena.getText());
                dato_usuario.setId_perfil(cboPerfil.getSelectedIndex());

// Envío los datos a un método de otra clase que contiene todo para conectarse con MySQL
                if (logica_usuario.actualizar(dato_usuario)) {  // Si el método "actualizar" retorna true
                    JOptionPane.showMessageDialog(null, "El usuario ha sido actualizado.", "Éxito", JOptionPane.INFORMATION_MESSAGE);
                } else {
                    JOptionPane.showMessageDialog(null, "Error!, no se ha podido actualizar el usuario.", "Error", JOptionPane.ERROR_MESSAGE);
                }

MySQL Code:

public boolean actualizar(Dato_usuario d_usr) {
        sSQL =  "UPDATE usuario SET"
                + "(nombre, apellido, usuario, contrasena, id_perfil)"
                + "VALUES (?, ?, ?, ?, ?) WHERE id_usuario=?";

        boolean resultado;

        // Java 7 try-with-resources
        try (PreparedStatement pstm = con.prepareStatement(sSQL)) {

            pstm.setString(1, d_usr.getNombre());
            pstm.setString(2, d_usr.getApellido());
            pstm.setString(3, d_usr.getUsuario());
            pstm.setString(4, d_usr.getContrasena());
            pstm.setInt(5, d_usr.getId_perfil());
            pstm.setInt(6, d_usr.getId_usuario());
            pstm.executeUpdate();

            resultado = true;

        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "SQLException:\n" + e, "Error: actualizar(Dato_usuario d_usr)", JOptionPane.ERROR_MESSAGE);
            resultado = false;
            }

        return resultado;
    }

I'm trying to tell you to update the user data with the id_usuario = ? but, since the wildcard ? of id_usuario is located in the last place, I pass it as the number 6.

I really do not know if it's correct, I'm a novice with SQL .

mysql java

asked by Robert Gomez 01.12.2016 в 06:44

source

1 answer

Sum of values conenidos in attributes in a tr Pass a TEXT data to FLOAT in CQL

score 1 · Accepted Answer

It seems to me that your SQL sentence in your variable sSQL should be like that for the UPDATE :

sSQL = "UPDATE usuario SET "
+ "nombre = ?"
+ ",apellido = ?"
+ ",usuario = ?"
+ ",contrasena = ?"
+ ",id_perfil = ? "
+ "WHERE id_usuario=?";

On the following link you can find a tutorial on the use of SQL sentences and their syntax for CRUD operations ( SELECT , INSERT , UPDATE and DELETE ) using MySQL and Java