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.
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