Java derby embedded DB error: The syntax of the string representation of a date/time value is incorrect

Question

I have the following problem using derby embedded DB in my application:

I changed a program that works with Mysql data base to use a Derby embedded data base because the client want it like this due to internet problems, everything works fine, but when i want to make a search using a range of dates the console shows the next error:

2018-12-10 17:26:18.920  INFO 107044 --- [nio-7009-exec-3] 

C.wDatos_ControlTransferencias_Servicios : /transferencias/consultar
2018-12-10 17:26:18.970  WARN 107044 --- [nio-7009-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 30000, SQLState: 22007
2018-12-10 17:26:18.970 ERROR 107044 --- [nio-7009-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : The syntax of the string representation of a date/time value is incorrect.
2018-12-10 17:26:19.009 ERROR 107044 --- [nio-7009-exec-3] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.DataException: could not execute query] with root cause

org.apache.derby.iapi.error.StandardException: The syntax of the string representation of a date/time value is incorrect.

Like i said when it was working with MySql it works perfectly.

the query that i use is this:

@SuppressWarnings("unchecked")
    @RequestMapping(value = "/transferencias/consultatodos", method = RequestMethod.POST, produces = "application/json; charset=utf-8")
    @CrossOrigin
    @RequestScope
    Collection<Transferencias> transferenciasConsultastodos(@RequestBody Consulta conf) throws JsonProcessingException {
        List<Transferencias> transl = new ArrayList<>();
        log.info("/transferencias/consultar");
        String Query = "Select trans from Transferencias trans";
        if (conf.getFecha1() != null && conf.getFecha2() != null) {
            Query = Query + " WHERE trans.fecha >= '" + conf.getFecha1() + "' AND trans.fecha<= '"+conf.getFecha2()+"'";
             if (conf.getBanco() != null) {
                Query = Query + " AND trans.banco = '" + conf.getBanco() +"'";
            }
             if (conf.getBeneficiario() != null) {
                Query = Query + " AND trans.beneficiario = '" + conf.getBeneficiario() +"'";
            }
             if (conf.getTipo() != null) {
                Query = Query + " AND trans.tipo = '" + conf.getTipo() +"'";
            }
        }
        else {
            if (conf.getBanco() != null) {
                Query = Query + " WHERE trans.banco = '" + conf.getBanco() +"'";
                if (conf.getBeneficiario() != null) {
                    Query = Query + " AND trans.beneficiario = '" + conf.getBeneficiario() +"'";
                }
                if (conf.getTipo() != null) {
                    Query = Query + " AND trans.tipo = '" + conf.getTipo() +"'";
                }
            }
            else {
                if (conf.getBeneficiario() != null) {
                    Query = Query + " WHERE trans.beneficiario = '" + conf.getBeneficiario() +"'";
                    if (conf.getTipo() != null) {
                        Query = Query + " AND trans.tipo = '" + conf.getTipo() +"'";
                    }
                }
                else {
                    if (conf.getTipo() != null) {
                        Query = Query + " WHERE trans.tipo = '" + conf.getTipo() +"'";
                    }
                }
            }
        }
        transl = em.createQuery(Query).getResultList();
        return transl;
    }

When i do the search using other parameters it works perfectly, the problem is with the date.

The entity:

package ControlTransferencias.wDatos;



import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;


/**
 *
 * @author juan.finol
 */
@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Transferencias {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long id;
    private Long referencia;
    private String beneficiario;
    private String banco;
    private Date fecha;
    private String notaAdicional;
    private String descripcion;
    private Long monto;
    private String tipo;

    public String getTipo() {
        return tipo;
    }
    public void setTipo(String tipo) {
        this.tipo = tipo;
    }
    public Long getReferencia() {
        return referencia;
    }
    public void setReferencia(Long referencia) {
        this.referencia = referencia;
    }
    public String getBeneficiario() {
        return beneficiario;
    }
    public void setBeneficiario(String beneficiario) {
        this.beneficiario = beneficiario;
    }
    public String getBanco() {
        return banco;
    }
    public void setBanco(String banco) {
        this.banco = banco;
    }
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getDescripcion() {
        return descripcion;
    }
    public void setDescripcion(String descripcion) {
        this.descripcion = descripcion;
    }
    public Date getFecha() {
        return fecha;
    }
    public void setFecha(Date fecha) {
        this.fecha = fecha;
    }
    public Long getMonto() {
        return monto;
    }
    public void setMonto(Long monto) {
        this.monto = monto;
    }
    public String getNotaAdicional() {
        return notaAdicional;
    }
    public void setNotaAdicional(String notaAdicional) {
        this.notaAdicional = notaAdicional;
    }

}

Another thing that i notice was on the front end, when i make the search it shows the date like this: 1544414400000 and when i was using MySql it shows a dd-mm-yyyy format.

Note: the back end was made in spring boot.

Bryan Pendleton · Accepted Answer · 2018-12-11 14:06:08Z

1

Indeed, there is no standardization of the default string format for date and time datatypes across database systems, so issuing SQL queries with dates in literal string format is quite non-portable.

A much more portable way is to use the JDBC PreparedStatement object, and its setDate() method, to get your date values into your query.

Using PreparedStatement and its parameter substitution features also has the excellent benefit of making your program much less susceptible to SQL Injection vulnerabilities.

Look here for excellent documentation: https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

answered Dec 11, 2018 at 14:06

Bryan Pendleton

16.4k3 gold badges35 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

phantomsnake Over a year ago

After changing my code to use PreparedStatement and using Timestamp instead of Date the problem is solved, but i still have a problem in the front-end, when it shows me the date it does in a diferent format, for example the page shows "1548820800000" instead of "30-01-2019"

Vector · Accepted Answer · 2019-08-10 02:56:58Z

1

@phantomsnake here is some code to format date's

   DateTimeFormatter mon = DateTimeFormatter.ofPattern("MMMM");

DateTimeFormatter day = DateTimeFormatter.ofPattern("d"); DateTimeFormatter year = DateTimeFormatter.ofPattern("yyyy"); DateTimeFormatter dayOFwk = DateTimeFormatter.ofPattern("EEEE"); DateTimeFormatter mdwd = DateTimeFormatter.ofPattern("MMMM d EEEE"); LocalDateTime now = LocalDateTime.now();

System.out.println(mon.format(now));

System.out.println(day.format(now));

System.out.println(year.format(now));

System.out.println(dayOFwk.format(now));

System.out.println(mdwd.format(now));

}

answered Aug 10, 2019 at 2:56

Vector

3,2816 gold badges32 silver badges63 bronze badges

Collectives™ on Stack Overflow

Java derby embedded DB error: The syntax of the string representation of a date/time value is incorrect

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related