Insert JDateChoose into MySQL

Question

I'm having a problem when I'm trying to Insert some Data into MySQL Database. I think it's caused because of the JDateChooser.

This is the error that I'm getting:

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 'May 24 21:06:17 CEST 2017, Fri May 26 00:00:00 CEST 2017, Desayuno, Adrian Poved' at line 1

And this is the method for insert the data:

    public void nuevaReserva(ReservaVO reserva){
            try {
                Statement st = bd.getConexion().createStatement();
                st.executeUpdate("INSERT INTO reserva VALUES(null,"+reserva.getInicio()+", "+reserva.getFin()
                +", "+reserva.getRegimen()+", "+reserva.getCod_cliente()+", "+reserva.getCod_usuario()
                +", "+reserva.getCod_habitacion()+");");
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

I also have this method in my controler to get the Data from the JDateChooser and from my ComboBoxes:

private void insertaReserva() {
        ReservaDAO modeloReserva = new ReservaDAO();
        String refEmpeladoS = String.valueOf(refEmpleado);
        ReservaVO reserva = new ReservaVO("",nrv.getDateChooserLlegada().getDate().toString(),
                nrv.getDateChooserSalida().getDate().toString(),
                nrv.getListaPension().getSelectedItem().toString(),
                nrv.getListaClientes().getSelectedItem().toString(),refEmpleadoS,
                nrv.getListaHabitaciones().getSelectedItem().toString());

        modeloReserva.nuevaReserva(reserva);
        }

Thanks for help.

In that case it would be nice it you upvoted and/or accepted the answers that helped you fix your issue - so that next time someonw has your problem, they find a solution here. Welcome to Stackoverflow! — Jan, May 25 '17 at 13:34

score 0 · Answer 1 · answered May 23 '17 at 19:21

0

You need to use SimpleDateFormat.format() method to format the date to a string. You can't just use nrv.getDateChooserSalida().getDate().toString(), that gives you a data Object.

SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd");
String dateFormatted = sdf.format(nrv.getDateChooserSalida().getDate().toString());

use DateFormatted,in the ReservaVO contructor.

answered May 23 '17 at 19:21

Amatya Annamaneni

27
5

Wouldn't help with the fact that the value is not properly encased in quotes, would it? – Jan May 23 '17 at 19:27
Yeah, should be like st.executeUpdate("INSERT INTO reserva VALUES(null,'"+reserva.getInicio()+"', '"+reserva.getFin() +"','"+reserva.getRegimen()+"', '"+reserva.getCod_cliente()+"','"+reserva.getCod_usuario() +"','"+reserva.getCod_habitacion()+"');"); – Amatya Annamaneni May 23 '17 at 19:30
without additional ' this will break for almost all strings - or worse (think of reserva.getInicio() to be "); DROP DATABASE" ... – Jan May 23 '17 at 19:32
its getting more toward SQL injection now. Missing Quotes, and String format are two major things here, usage of execution SP or a SQL, its upto the standards. – Amatya Annamaneni May 23 '17 at 19:35

score 0 · Answer 2 · answered May 23 '17 at 19:26

Most likely you're missing quotes ' around your date value.

But that will only get you so far.

Some things to improve:

a) Use PreparedStatement / Bind Variables

Mainly this prevents nasty SQL injection into your database - and makes the SQL a bit better to read as well

public void nuevaReserva(ReservaVO reserva){
        try {
            PreparedStatement st = bd.getConexion().prepareStatement(
   "INSERT INTO reserva VALUES(null, ?, ?, ?, ?, ?, ?)");
          st.setString(1, reserva.getInicio());
          st.setString(2, reserva.getFin());
          st.setString(3, reserva.getRegimen());
          st.setString(4, reserva.getCod_cliente());
          st.setString(5, reserva.getCod_usuario());
          st.setString(6, reserva.getCod_habitacion());
          st.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

Remember: I don't know about your column definitions. Might be some setInt() or else might be much better for your needs.

b) Improve by using try-with-resource

Your Databaseconnection bd.getConnexion() is never closed. That might be wanted (one connection open a long time) but in most systems I've seen that getConexion() of yours either opens a connection to the database (in which case you need to close it!) or takes one from a connection pool - in which case you want to return it ater use. Both can be done easily by using try-with-resource:

public void nuevaReserva(ReservaVO reserva){
        try (Connection con = bd.getConexion();
            PreparedStatement st = bd.getConexion().prepareStatement(
   "INSERT INTO reserva VALUES(null, ?, ?, ?, ?, ?, ?)")) {
          st.setString(1, reserva.getInicio());
          st.setString(2, reserva.getFin());
          st.setString(3, reserva.getRegimen());
          st.setString(4, reserva.getCod_cliente());
          st.setString(5, reserva.getCod_usuario());
          st.setString(6, reserva.getCod_habitacion());
          st.executeUpdate();
         //Both statement and connection will be closed and closing curly brace
         // even in case of an Exception!
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

Insert JDateChoose into MySQL

2 Answers2