Announcement Announcement Module
Collapse
No announcement yet.
How To Persist a DateTime (JodaTime) with jdbcTemplate into MySQL datetime/timestamp Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

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

    Dear Community

    I have the follow POJO

    Code:
    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)

    Code:
    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

    Code:
    @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

    Code:
    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

    Code:
    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

    Code:
    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

  • #2
    Hi Manuel,

    Have you looked at the following method in JdbcTemplate?

    Code:
    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

    Comment


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

      Code:
      @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 ...

      Comment


      • #4
        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

        Code:
        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

        Comment


        • #5
          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.

          Comment


          • #6
            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

            Comment

            Working...
            X