I am using MySQL and Java in netbeans, however the SQL string code does not update. I do not know what might be giving error, because I already checked the sql and apparently it is ok.
I have in my class DAO , method update and pesquisaCriterio , and call in my form. When I try to update, it gives error in pesquisarCriterio and in 'update. Here's the DAO code and how I'm calling it in the form:
I have two fk that are registered in my bank, since now I thank who can help me. Below is the line of code to update and search for criteria in the DAO class.
DAO Reservation
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;
}
}
ReservaOO - searchCriteria
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;
}
Screen
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)