0

I have made a formulary with NetBeans using java.

In that formulary I have a field JDateChooser. The problem is when I try to insert that value in Postgres database:

public void insertarDatos(Usuario u){

    try {
        String sql = "INSERT INTO usuario( dni, nombre, apellidos, correo, "
                + "telefono, usuario, clave, fecha, foto)\n"
                + "VALUES (?, ?, ?, ?, ?, ?, ?, '"+u.getFecha()+"', ?);";
        PreparedStatement ps=con.con.prepareStatement(sql);
        ps.setString(1, u.getDni());
        ps.setString(2, u.getNombre());
        ps.setString(3, u.getApellidos());
        ps.setString(4, u.getCorreo());
        ps.setString(5, u.getTelefono());
        ps.setString(6, u.getUsuario());
        ps.setString(7, u.getClave());

        ps.setBinaryStream(8, u.getFis(), u.getLongitudBytes());
        boolean ejecucion=con.ejecutarSQL(ps);
        if(ejecucion==true){
            JOptionPane.showMessageDialog(null, "usuario correctamente"
                    + "registrado");
        }else if(ejecucion==false){
            JOptionPane.showMessageDialog(null, "error insertar usuario");
        }

    } catch (Exception e) {
        System.out.println("error al insertar: " + e);
    }
}

Then Java gives me an error like this:

error al ejecutar: org.postgresql.util.PSQLException: ERROR: la sintaxis de entrada no es válida para tipo date: «null»

However if I do the query in Postgres editor it works fine.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Fernando Triviño Gómez Cortina
  • 1
  • 2
  • 2
    Do **NOT** pass dates (or numbers) as Strings. Pass an instance of `LocalDate` (using `setObject()`) or at least an instance of `java.sql.Date` using `setDate()` –  Jul 27 '18 at 12:19
  • 3
    Why are you embedding `u.getFecha()` into your SQL command text when you are (correctly) using parameter placeholders for all of the other values? – Gord Thompson Jul 27 '18 at 13:19

3 Answers3

0

What is the datatype of your date column in the database? Does it store the date in date format or as a string?? If it stores the dates in date format, you should convert the Date, you get from the JDateChooser, to java.sql.Date. You have to do this because JDateChooser returns java.util.Date and the database should work with java.sql.date. Try to convert it like this:

java.util.Date datum = jDateChooser1.getDate();
java.sql.Date sqldate = new java.sql.Date(datum.getTime());
Robin
  • 41
  • 1
  • 9
  • my field "fecha" (date in english) is a date in postgres database. Thanks friend i am going to chek your solution and i will say you if it is ok – Fernando Triviño Gómez Cortina Jul 27 '18 at 12:49
  • Using a date object for the date is a good idea. Using the outdated `java.util.Date` and `java.sql.Date` less so. Today use `LocalDate` from java.time, the modern Java date and time API, for a date without time of day. Then do like `ps.setObject(7, u.getFecha());` where `getFecha` returns a `LocalDate`. – Ole V.V. Jul 28 '18 at 09:12
  • if i write: java.util.Date datum = jDateChooser1.getDate(); java.sql.Date sqldate = new java.sql.Date(datum.getTime()); Then it returns : error al iniciar: java.lang.NullPointerException – Fernando Triviño Gómez Cortina Jul 30 '18 at 07:25
  • i am coding LocalDate fec = jdateFecha.getDate(); and it returns me an error: Date cannot be converted to LocaDate – Fernando Triviño Gómez Cortina Jul 30 '18 at 07:30
0

java.time

Avoid the troublesome old date-time classes such as java.util.Date, java.util.Calendar, java.sql.Date, and java.sql.Timestamp.

Use only the java.time classes. Specifically, the Instant class replaces both java.util.Date and java.sql.Timestamp. An Instant resolves to nanoseconds, more than enough for the microseconds resolution of Postgres.

To interoperate with old code not yet updated to java.time, convert. Look to new methods added to the old classes.

Instant instant = myJavaUtilDate.toInstant() ;

…and…

java.util.Date myJavaUtilDate = java.util.Date.from( instant ) ;

JDBC 4.2

Do not pass or fetch date-time values as text with your database.

As of JDBC 4.2 and later, we can directly exchange java.time objects with the database.

Call PreparedStatement::setObject and ResultSet::getObject.

Search Stack Overflow

This topic has been addressed many times already on Stack Overflow. Search for more info and examples.

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • 1
    Your answer doesn't really address the question, you just post a selection of fragments that you seem to often post when answering questions involving JDBC and dates. I don't think this is really helpful in this form. Especially not as your answer suggest to use `Instant`, which is not even supported by JDBC (at least, not as parameter to `setObject`). – Mark Rotteveel Jul 28 '18 at 12:41
0

if i write: java.util.Date datum = jDateChooser1.getDate(); java.sql.Date sqldate = new java.sql.Date(datum.getTime());

Then it returns : error al iniciar: java.lang.NullPointerException

And when i write: LocalDate fec = jdateFecha.getDate(); and it returns me an error: Date cannot be converted to LocaDate –

Fernando Triviño Gómez Cortina
  • 1
  • 2