How To Persist a DateTime (JodaTime) with jdbcTemplate into MySQL datetime/timestamp

[dr_pompeii]

Dear Community

I have the follow POJO

package com.manuel.jordan.domain;

import java.io.Serializable;
import java.util.Date;

import org.joda.time.DateTime;


public class Persona implements Serializable{
	
	private static final long serialVersionUID = 1L;
	
	private String idPersona;
	private String nombrePersona;
	private String apellidoPersona;
	private DateTime fechaPersona;

        setters/getters

        @Override
	public String toString() {
	....
	}

I have the follow table (MySQL 5.5.28)

create table persona(

	idPersona varchar(10) not null,
	nombrePersona varchar(20) not null,
	apellidoPersona varchar(20) not null,
	fechaPersona timestamp not null,
  
	PRIMARY KEY(idPersona)
	 
)ENGINE=InnoDB;

And it is my repository

@Transactional
@Repository
public class PersonaJdbcDaoImpl implements PersonaDaoService{

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public void insertarPersona(Persona persona){
				
				            
		jdbcTemplate.update("INSERT INTO persona(idPersona, nombrePersona, apellidoPersona, fechaPersona) " +
	            "VALUES(?,?,?,?) ",
	            new Object[]{persona.getIdPersona(),
				       persona.getNombrePersona(),
				       persona.getApellidoPersona(),
				       persona.getFechaPersona()}		            
	            );
		
	}
}

In the Main class I create the Person class and call the service

Persona persona = new Persona();
		
persona.setIdPersona("MJE");
persona.setNombrePersona("Manuel");
persona.setApellidoPersona("Jordan");
persona.setFechaPersona(new DateTime("2012-12-25"));

personaBoService.insertarPersona(persona);

But sadly I got this error

Caused by: com.mysql.jdbc.MysqlDataTruncation: 
Data truncation: Incorrect datetime value: '??' for column 'fechaPersona' at row 1
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4072)

I already did a research in Google through many hours, but all is related with Hibernate.
And event worst, seems the unique solution available in the web is this page

Joda DateTime to Java SQL Timestamp storing into MySQL via Hibernate ORM – Timezone Problems

Perhaps some member in the community has a solution.

I am assuming you are working with a repository standalone for JDBC (Spring's jdbcTemplate support) or MyBatis. Nothing related with some JPA annotation. And of course you are working with DateTime (JodaTime)

How you work around this problem?

BTW, MySQL expectes the follow pattern

mysql> select * from persona;
+-----------+---------------+-----------------+---------------------+
| idPersona | nombrePersona | apellidoPersona | fechaPersona        |
+-----------+---------------+-----------------+---------------------+
| MJE       | Manuel        | Jordan          | 2012-10-27 21:34:50 |
+-----------+---------------+-----------------+---------------------+
1 row in set (0.00 sec)

mysql>

Thanks in advanced

[Sam Brannen]

Hi Manuel,

Have you looked at the following method in JdbcTemplate?

org.springframework.jdbc.core.JdbcTemplate.update(String, PreparedStatementSetter)

You should be able to write a custom PreparedStatementSetter that converts the Joda DateTime to a java.sql.Timestamp.

Regards,

Sam

[Marten Deinum]

Simply call the toDate method on the DateTime instead of directly storing the datetime.

@Transactional
@Repository
public class PersonaJdbcDaoImpl implements PersonaDaoService{

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public void insertarPersona(Persona persona){
				
				            
		jdbcTemplate.update("INSERT INTO persona(idPersona, nombrePersona, apellidoPersona, fechaPersona) " +
	            "VALUES(?,?,?,?) ",
	            new Object[]{persona.getIdPersona(),
				       persona.getNombrePersona(),
				       persona.getApellidoPersona(),
				       persona.getFechaPersona().toDate()}		            
	            );
		
	}
}

Something like this should work.

BTW... A DaoService, what is it a service or a dao ...

[dr_pompeii]

Hello Sam

Thanks for the reply

You should be able to write a custom PreparedStatementSetter that converts the Joda DateTime to a java.sql.Timestamp.

Oh, Seems I forgot such approach.

Thanks for the suggestion, the "problem" is create a new class just to work around with such variable.

Hello Marten

persona.getFechaPersona().toDate()

Your approach work. Thanks a lot!

BTW... A DaoService, what is it a service or a dao ...

I use XDaoService to know that is an interface and XJdbcDaoImpl for implementation.
Same idea for the Business Object XBoService and XBoImpl

Theferore

Main -> Bo -> Dao -> DB

Best Regards for both

[Marten Deinum]

I use XDaoService to know that is an interface and XJdbcDaoImpl for implementation.

I would expect something to be either a dao (XDao) or a Service (XService) but not both (XDaoService)... But whats in a name... Glad it worked out.

[dr_pompeii]

Hi Marten

I would expect something to be either a dao (XDao) or a Service (XService) but not both (XDaoService)...

I see

Normally for each entity (X) I create XDaoService, XDaoImpl and XBoService, XBoImpl

But whats in a name...

Seems each one has our own style

Glad it worked out.

Thanks again friend