Error in the data update method next to the database

Asked

Viewed 88 times

0

I’m using the MySQL and Java in netbeans, but the SQL string code doesn’t update. I don’t know what might be wrong, because I’ve already checked sql and apparently it’s ok.

I have in my class DAO, the method update and the pesquisaCriterio, and call in my form. When I try to update, it gives error in pesquisarCriterio and on `update. Follow the DAO code and how I am calling in the form:

I have two fk that are registered in my bank, already thank who can help me. Below follows the code line update and query criteria in the DAO class.

Reserved

 public boolean atualizar(Object obj) {
        Reserva reservaVO;

        if (obj instanceof Reserva) {
            reservaVO = (Reserva) obj; //converte de Object para Reserva
        } else {
            return false;
        }
        String sql = "UPDATE reserva SET tipoReserva = '" + reservaVO.getTipoReserva()
                + "', dataReserva = '" + reservaVO.getDataReserva()
                + "', dataEntrada = '" + reservaVO.getDataEntrada()
                + "', dataSaida = '" + reservaVO.getDataSaida()
                + "', horarioEntrada = '" + reservaVO.getHorarioEntrada()
                + "', horarioSaida = '" + reservaVO.getHorarioSaida()
                + "', qtdPessoas = '" + reservaVO.getQtdPessoas()
                + "', status = '" + reservaVO.getStatus()
                + "', codHospede = '" + reservaVO.getCodHospede()
                + "', codApartamento = '" + reservaVO.getCodApartamento()
                + "' WHERE codReserva = '" + reservaVO.getCodReserva() + "'";
        try {

            getBanco().abrir();
            Statement stm = getBanco().getConexao().createStatement();
            if (stm.executeUpdate(sql) > 0) {
                getBanco().fechar();
                return true;
            } else {
                getBanco().fechar();
                return false;
            }
        } catch (SQLException ex) {
            getBanco().fechar();
            ex.printStackTrace();
            return false;
        }
    }

Reserved - search

public Object pesquisarCriterio(String criterio) {

        Reserva reservaVO = null;
        String sql = "SELECT r.codReserva , H.nome, A.numeroApto,"
                + " r.tipoReserva , r.dataReserva , r.dataEntrada ,"
                + " r.dataSaida , r.horarioEntrada , r.horarioSaida ,"
                + " qtdPessoas , r.status  "
                + " FROM reserva r"
                + " INNER JOIN Hospede AS H ON  r.codReserva = H.codHospede "
                + " INNER JOIN Apartamento AS A ON ( r.codReserva = A.codApartamento) "
                + " WHERE r.codReserva " + criterio + " Order by codReserva" ;
        getBanco().abrir();
        try {
            Statement stm = getBanco().getConexao().createStatement();
            ResultSet rs = stm.executeQuery(sql);
            if (rs.next() == true) { //Achou
                reservaVO = new Reserva();
                reservaVO.setCodReserva(rs.getInt("codReserva"));
                reservaVO.setCodHospede(rs.getInt("codHospede"));
                reservaVO.setCodApartamento(rs.getInt("codApartamento"));
                reservaVO.setTipoReserva(rs.getString("tipoReserva"));
                reservaVO.setDataReserva(rs.getDate("dataReserva"));
                reservaVO.setDataEntrada(rs.getDate("dataEntrada"));
                reservaVO.setDataSaida(rs.getDate("dataSaida"));
                reservaVO.setHorarioEntrada(rs.getString("horarioEntrada"));
                reservaVO.setHorarioSaida(rs.getString("horarioSaida"));
                reservaVO.setQtdPessoas(rs.getString("qtdPessoas"));
                reservaVO.setStatus(rs.getString("status"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return reservaVO;
    }

Canvas

Reserva reserva1 = new Reserva();
        Calendar cal;
        int d, m, a;//Variaveis para usar com calendario
        reserva1 = (Reserva) reservaDAO.pesquisarCriterio(txtNomeHospede.getText());
        if (reserva1.getCodReserva() != 0) {
        } else {
            if (!txtNomeHospede.getText().isEmpty()&& !txtNumero.getText().isEmpty()
                  && !txtQtdPessoas.getText().isEmpty()) {
                reserva1.setCodHospede(Integer.parseInt(txtNomeHospede.getText()));
                reserva1.setCodApartamento(Integer.parseInt(txtNumero.getText()));
                reserva1.setTipoReserva(String.valueOf(cmbTipoReserva.getSelectedItem
                cal = dcDataReserva.getCalendar();
                d = cal.get(Calendar.DAY_OF_MONTH);
                m = cal.get(Calendar.MONTH);
                a = cal.get(Calendar.YEAR) - 1900;
                reserva1.setDataReserva(new Date(a, m, d));
                cal = dcDataEntrada.getCalendar();
                d = cal.get(Calendar.DAY_OF_MONTH);
                m = cal.get(Calendar.MONTH);
                a = cal.get(Calendar.YEAR) - 1900;
                reserva1.setDataEntrada(new Date(a, m, d));
                cal = dcDataSaida.getCalendar();
                d = cal.get(Calendar.DAY_OF_MONTH);
                m = cal.get(Calendar.MONTH);
                a = cal.get(Calendar.YEAR) - 1900;
                reserva1.setDataSaida(new Date(a, m, d));

                reserva1.setHorarioEntrada(txtHorarioEntrada.getText());
                reserva1.setHorarioSaida(txtHorarioSaida.getText());
                reserva1.setQtdPessoas(txtQtdPessoas.getText());
                reserva1.setStatus(String.valueOf(cmbStatus.getSelectedItem()));      
                reservaDAO.atualizar(reserva1);
                limparCampos();
                dcDataReserva.setCalendar(null);
                dcDataEntrada.setCalendar(null);
                dcDataSaida.setCalendar(null);
                JOptionPane.showMessageDialog(this, "Dados da Reserva atualizado com sucesso!");
                conReserva.executaSQL("select *from reserva");
                try {
                    modeloTabelaReserva.setResult(conReserva.resultset);
                } catch (SQLException ex) {
                    Logger.getLogger(JFCadProduto.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

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 'Adriana Ferreira Order by codReserva' 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.ConnectionImpl.execSQL(ConnectionImpl.java:2738)
 at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1617)
 at DAO.ReservaDAO.pesquisarCriterio(ReservaDAO.java:196)
 at GUI.JFCadReserva.btnAtualizarActionPerformed(JFCadReserva.java:740)
 at GUI.JFCadReserva.access$1000(JFCadReserva.java:26)
 at GUI.JFCadReserva$11.actionPerformed(JFCadReserva.java:459)
java mysql

  • Welcome to sopt. Add the error in the update method.

  • 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 'Adriana Ferreira Order by codReserva' at line 1 At DAO.ReservaDAO.searchCriterio(Reserved.java:196) at GUI.JFCadReserva.btnAtualizarActionPerformed(Jfcadreserva.java:740)

  • Lizy, copy the complete error, click [Edit] and add at the end of the question.

1 answer

0


Along those lines:

String sql = "SELECT r.codReserva , H.nome, A.numeroApto,"
                + " r.tipoReserva , r.dataReserva , r.dataEntrada ,"
                + " r.dataSaida , r.horarioEntrada , r.horarioSaida ,"
                + " qtdPessoas , r.status  "
                + " FROM reserva r"
                + " INNER JOIN Hospede AS H ON  r.codReserva = H.codHospede "
                + " INNER JOIN Apartamento AS A ON ( r.codReserva = A.codApartamento) "
                + " WHERE r.codReserva " + criterio + " Order by codReserva" ;

there is a syntax error in WHERE r.codReserva " + criterio + " Order by codReserva", you forgot to add the condition you want to make in the clause where. I imagine the expected is:

String sql = "SELECT r.codReserva , H.nome, A.numeroApto,"
                + " r.tipoReserva , r.dataReserva , r.dataEntrada ,"
                + " r.dataSaida , r.horarioEntrada , r.horarioSaida ,"
                + " qtdPessoas , r.status  "
                + " FROM reserva r"
                + " INNER JOIN Hospede AS H ON  r.codReserva = H.codHospede "
                + " INNER JOIN Apartamento AS A ON ( r.codReserva = A.codApartamento) "
                + " WHERE r.codReserva = " + criterio + " Order by codReserva" ;

  • Diego f. - yes but you would know how to solve??

  • @Lizy The answer already has the solution.

  • @Lizy managed to solve?

  • @Lizy I suggest that you evaluate changing the way you query using Preparedstatement, so you separate the parameters from the query, you have more security against sql Injection and your code is easier to understand.

  • Here are some links if you are interested: https://www.caelum.com.br/apostila-java-web/bancos-dados-e-jdbc/#2-9-inserting data-in-the-database and https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

  • I am using java, in my DAO I am using preparedStatement I just posted sql to see if I could find solution in the query.

  • @Lizy preparedStatement is a java interface that separates the query parameters. You are adding the values directly in the query, it makes it difficult to read it and syntax errors may occur, as is your case, take a look at the links I sent, it is much easier to find errors later.

Show 2 more comments

Browser other questions tagged java mysql

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