Error: You have an error in your SQL syntax

Asked

Viewed 330 times

0

I am developing a software for a video rental with Pattern MVC (Model-view-controller) design and the problem of not changing people’s data is occurring. When I change the person’s data displays the following message: "Error while changing data!".

Video person class, view layer:

public class VideoPessoa extends javax.swing.JFrame {


    PessoaController pessoaController;
    Pessoa pessoa;




    /**
     * Creates new form Pessoa
     */
    public VideoPessoa() {
        initComponents();

        new Conexao();
        pessoaController = new PessoaController();
        pessoa = new Pessoa();
        this.carregarPessoas();
         this.novaPessoa();
        this.habilitarCampos();

    }

public boolean alterarPessoa() {
        pessoa.setCodigo( Integer.parseInt(this.txtCodigo.getText()));
        pessoa.setNome(this.txtNome.getText());
        pessoa.setEndereco(this.txtEndereco.getText());
        pessoa.setBairro(this.txtBairro.getText());
        pessoa.setCPF(this.txtCPF.getText());
        pessoa.setSexo(this.txtSexo.getText());
        pessoa.setUf(this.txtUF.getText());
        pessoa.setCelular(this.txtCelular.getText());
        pessoa.setTelefone(this.txtTelefone.getText());
        pessoa.setCidade(this.txtCidade.getText());

        if (pessoaController.alterar(pessoa)) {

            JOptionPane.showMessageDialog(this, "Registro alterado com sucesso!");
             this.desabilitarCampos();
            this.carregarPessoas();
        } else {

            JOptionPane.showMessageDialog(this, "Erro ao alterar os dados!", "ERRO", JOptionPane.ERROR_MESSAGE);

        }

        return true;

    }

Personal Classcontroller:

public class Personal Controller {

private final PessoaDAO pessoaDAO;


public PessoaController() {
    pessoaDAO = new PessoaDAO();


}



public boolean alterar( Pessoa pessoa ) {
        boolean retorno;



        retorno = pessoaDAO.alterar(pessoa);

        System.out.println("Pessoa: "+pessoa); 

        return retorno;
    }

Personal class, including the method of changing people:

public class PessoaDAO {

    private Connection con;

    private final String SQLINSERT = " INSERT INTO pessoa(nome, endereco, bairro, sexo, telefone, celular, CPF, uf, cidade)"
            + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?) ";

    private final String SQLPESSOAPELOCODIGO = "SELECT codigo, nome, endereco, bairro, sexo, telefone, celular, CPF, uf, cidade"
            + " FROM pessoa"
            + " WHERE codigo=? ";

    private final String SQLSELECT = " SELECT  codigo, nome, endereco, bairro, sexo, telefone, celular, CPF,  uf, cidade FROM PESSOA";

    private final String SQLUPDATE = " UPDATE pessoa"
            + " SET nome = ?, "
            + " endereco = ?, "
            + " bairro   = ?, "
            + " sexo     = ?, "
            + " telefone = ?, "
            + " celular  = ?, "
            + " CPF      = ?, "
            + " uf       =?, "
            + " cidade   =?, "
            + " WHERE codigo = ?";

    private final String SQLDELETE = "DELETE FROM pessoa"
            + " WHERE codigo = ?";

    private PreparedStatement psInsert, sqlPessoaPeloCodigo, sqlSelect, sqlUpdate, sqlDelete;

    public PessoaDAO() {

        con = Conexao.getConnection();
        try {
            psInsert = con.prepareStatement(SQLINSERT);
            sqlPessoaPeloCodigo = con.prepareStatement(SQLPESSOAPELOCODIGO);
            sqlSelect = con.prepareStatement(SQLSELECT);
            sqlUpdate = con.prepareStatement(SQLUPDATE);
            sqlDelete = con.prepareStatement(SQLDELETE);
        } catch (SQLException ex) {
            Logger.getLogger(PessoaDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

public Pessoa getPessoaPeloCodigo(int codigo) {    

    Pessoa pessoa = null;    

    try {    
        // Apenas mudei a declaração e a inicialização para dentro do método.     
PreparedStatement sqlPessoaPeloCodigo = con.prepareStatement(SQLPESSOAPELOCODIGO);  
        sqlPessoaPeloCodigo.setInt(1, codigo);    
        ResultSet rs = sqlPessoaPeloCodigo.executeQuery();    

        if (rs.next()) {    
            //Instancia a nova pessoa    
            pessoa = new Pessoa();    

            //Seta as informações na pessoa    
            pessoa.setCodigo(rs.getInt("codigo"));    
            pessoa.setNome(rs.getString("nome"));    
            pessoa.setEndereco(rs.getString("endereco"));    
            pessoa.setBairro(rs.getString("bairro"));    
            pessoa.setSexo(rs.getString("sexo"));    
            pessoa.setTelefone(rs.getString("telefone"));    
            pessoa.setCelular(rs.getString("celular"));    
            pessoa.setCPF(rs.getString("CPF"));      
            pessoa.setUf(rs.getString("uf"));    
            pessoa.setCidade(rs.getString("cidade"));    

        }    
  // fechando o resultSet e o PreparedStatement   
        rs.close();  
        sqlPessoaPeloCodigo.close();  



    } catch (SQLException ex) {    
        Logger.getLogger(PessoaDAO.class.getName()).log(Level.SEVERE, null, ex);    
    }    
    return pessoa;    
}    

public boolean alterar(Pessoa pessoa) {
        boolean retorno = false;

        try {
            sqlUpdate = con.prepareStatement(SQLUPDATE);

            sqlUpdate.setString(1,pessoa.getNome());
            sqlUpdate.setString(2, pessoa.getEndereco());
            sqlUpdate.setString(3, pessoa.getBairro());
            sqlUpdate.setString(4, pessoa.getSexo());
            sqlUpdate.setString(5, pessoa.getTelefone());
            sqlUpdate.setString(6, pessoa.getCelular());
            sqlUpdate.setString(7, pessoa.getCPF());
            sqlUpdate.setString(8, pessoa.getUf());
            sqlUpdate.setString(9, pessoa.getCidade());
            sqlUpdate.setInt(10, pessoa.getCodigo());




            sqlUpdate.executeUpdate(SQLUPDATE);



            retorno = true;



        } catch (SQLException ex) {
            Logger.getLogger(PessoaDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return retorno;
    }

Error:

Conexao obtida!
PessoaPessoa{nome=ee, endereco=eee, bairro=eee, sexo=, telefone=4444, celular=44, CPF=6666, codigo=46, cidade=eee, uf=jj, pessoa=null}
out 28, 2015 4:38:04 PM dao.PessoaDAO alterar
GRAVE: null
Pessoa: Pessoa{nome=ee, endereco=eee, bairro=eee, sexo=, telefone=4444, celular=44, CPF=6666, codigo=46, cidade=eee, uf=jj, pessoa=null}
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 '?  endereco = ?  bairro   = ?  sexo     = ?  telefone = ? celular  = ?  CPF     ' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
    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:1053)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2788)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1816)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1730)
    at dao.PessoaDAO.alterar(PessoaDAO.java:151)
    at controller.PessoaController.alterar(PessoaController.java:47)
    at view.VideoPessoa.alterarPessoa(VideoPessoa.java:423)
    at view.VideoPessoa.btnAlterarActionPerformed(VideoPessoa.java:346)
    at view.VideoPessoa.access$200(VideoPessoa.java:25)
    at view.VideoPessoa$3.actionPerformed(VideoPessoa.java:124)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2346)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
    at java.awt.Component.processMouseEvent(Component.java:6527)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
    at java.awt.Component.processEvent(Component.java:6292)
    at java.awt.Container.processEvent(Container.java:2234)
    at java.awt.Component.dispatchEventImpl(Component.java:4883)
    at java.awt.Container.dispatchEventImpl(Container.java:2292)
    at java.awt.Component.dispatchEvent(Component.java:4705)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4898)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4533)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4462)
    at java.awt.Container.dispatchEventImpl(Container.java:2278)
    at java.awt.Window.dispatchEventImpl(Window.java:2739)
    at java.awt.Component.dispatchEvent(Component.java:4705)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:746)
    at java.awt.EventQueue.access$400(EventQueue.java:97)
    at java.awt.EventQueue$3.run(EventQueue.java:697)
    at java.awt.EventQueue$3.run(EventQueue.java:691)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:75)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:86)
    at java.awt.EventQueue$4.run(EventQueue.java:719)
    at java.awt.EventQueue$4.run(EventQueue.java:717)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:75)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:716)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)

Pessoa: Pessoa{nome=ee, endereco=eee, bairro=eee, sexo=, telefone=4444, celular=44, CPF=6666, codigo=46, cidade=eee, uf=jj, pessoa=null}
CONSTRUÍDO COM SUCESSO (tempo total: 20 segundos)
java mysql
  • 1

    There’s no such comma here: cidade =?,

  • I already took out that comma and keep making mistakes!

  • @You couldn’t solve my doubt..

  • 1

    Failed to fix? is still the same problem or another error appeared?

  • You were unable to resolve this question : [ http://answall.com/questions/94958/sqlupdate-n%C3%A3o-altera-dados-no-banco-de-dados ] . You mark my question as duplicated because of your answer in a similar question but your answer does not help me at all. Soon after I threw the same question with different name because it did not solve my doubt and had several people who marked as duplicate and as a consequence I can not publish questions to solve my doubt!

  • you can edit the question and add the new details or tests you have made.

  • I’ve done the tests and now I want to publish it again but its publish it again will mark as duplicate and I’m already blocked because of those two duplicate questions!

  • You can’t even edit that question?

  • I’ve been looked at and I can’t even edit with you!

  • When you click on edit what appears?

  • I went to click on your edit and it worked, but my edit had not worked. What I will edit in my question?

  • I put what you already did and it didn’t work or what you did to change the error, update the code, error message.

  • I’ll do it then.

Show 8 more comments

1 answer

1

Man, I think the mistake is in Classe PessoaDAO, in this passage:

        sqlUpdate.setInt(10, pessoa.getCodigo());

 ---->  sqlUpdate.executeUpdate(SQLUPDATE);  <-----

        retorno = true;

You have loaded the values into the sqlUpdate object, but then you inform what to run, you do not need because you have already reported this above when instantiating the class in the object, try to exchange the line for:

sqlUpdate.executeUpdate();

Já tentei isso já e exibe a mensagem "alterado com sucesso" mas simplesmente não altera! – Igor Contini

The ideal is to debug, but do this test, put below the:

pessoa.setCidade(this.txtCidade.getText());
Integer codigo = pessoa.getCodigo();
String nome = pessoa.getNome();
JOptionPane.showMessageDialog(this, codigo+' - '+nome);

  • I tried that already and displays the message "changed successfully" but simply does not change!

  • 2

    Are you debugging the execution ? , are you sure that in Videoperson, the information is being loaded into the object person ?, if you don’t know how to debug pass an Alert with the values of person after you arrow them into alter Persons

  • What do you mean? How do I pass an Alert? I’m a beginner in java yet..

  • Where do you put the JOptionPane.showMessageDialog(null, pessoa.get('codigo')+pessoa.get('nome')); ??? I put under the message " successfully altered"but gives several errors!

  • 1

    I edited the answer, adapts the type of data I reported to what you used in the Person class and checks that the object and function names are correct.

  • They are correct yes and when I do the tests you passed, it doesn’t really change and displays the message changed successfully!

  • 1

    If the text codigo+' - '+nome empty, so the error is in assigning data from the form pessoa.setCodigo( Integer.parseInt(this.txtCodigo.getText()));, check if you used the right names, txtCodigo has to be a field of form and make sure syxtax is right.

  • I checked and you’re right! The mistake is here: 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 '? endereco = ? bairro = ? sexo = ? telefone = ? celular = ? CPF ' at line 1

  • 1

    But this error did not disappear after you started using sqlUpdate.executeUpdate(); ?

  • Opa disappeared yes! , why?

  • 1

    Try to include a record in the hand in your database, use the query text and replace the ? manually.

  • I inserted and changed manually! The problem is in the same code..

  • 1

    Well nobody solved my doubt..

Show 8 more comments

Browser other questions tagged java mysql

You are not signed in. Login or sign up in order to post.