insert and get Query for Time as AM/PM in JPA using java

Question

OR

SELECT TIME_FORMAT( `locStart`, '%h:%i %p' ) FROM locationtimes.

How could i achieve it using JPA

These are my Database Table entries

Database table

My java Entity class

package models;

package models;

import util.MyConverter;

import javax.persistence.*;
import java.sql.Time;
import java.sql.Timestamp;

/**
 * Created by abhinav on 11/2/15.
 */

@Entity
@Table(name = "locationtimes")
//@JsonIdentityInfo(generator = ObjectIdGenerators.PropertyGenerator.class, property = "id", scope = Locationtimes.class)
public class Locationtimes {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Long id;

    @Column
    public Long locationId;

    @Column
    public String weekday;

    @Column(insertable = false, updatable = false)
    @Temporal(TemporalType.TIME)
    public Time locStart;

    @Column(insertable = false, updatable = false)
    public Time locEnd;

    @Column(insertable = false, updatable = false)
    public Time todayStart;

    @Column(insertable = false, updatable = false)
    public Time futureStart;

    @Column(insertable = false, updatable = false)
    public Time futureEnd;

    //@Transient
    @Column(name = "locStart")
    @Convert(converter = MyConverter.class)
    public String locStartString;

    //@Transient
    @Column(name = "locEnd")
    @Convert(converter = MyConverter.class)
    public String locEndString;

    @Column(name = "todayStart")
    @Convert(converter = MyConverter.class)
    public String todayStartString;

    //@Transient
    @Column(name = "futureStart")
    @Convert(converter = MyConverter.class)
    public String futureStartString;

    // @Transient
    @Column(name = "futureEnd")
    @Convert(converter = MyConverter.class)
    public String futureEndString;

    @Column
    public Long modifiedBy;

    @Column
    public Timestamp modified;
}

farrellmr · Answer 1 · 2015-02-25T10:22:52.937

2

What I think you mean is you have time in a format -

14:21 --> 02:21 PM

And you want to maintain that format in the database?

The java.sql.Time object will store the time as 24hr format HH24:MM, in fact seconds since the epoch as its a wrapper around java.util.Date. This means the addition of AM/PM is a presentation issue through SimpleDateFormat -

DateFormat dateFormat = new SimpleDateFormat("hh:mm a");

You could either do this as a -

Straight convert using SDF
NamedQuery - to_char(,'HH:MM')
AttributeConverter

Consider -

@Column
public Time locEnd;

If we convert this to a String of format HH:MM a -

@Column
 @Convert(converter = DateStringConverter.class)
public String locEndString;

Now create the converter class -

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;

@Converter
public class DateTimeConverter implements AttributeConverter {

 private static final dateFormat = new SimpleDateFormat("hh:mm a");

 @Override
 public Time convertToDatabaseColumn(String datehhmma) {
  return dateFormat.parse(datehhmma);
 }

 @Override
 public String convertToEntityAttribute(String datehhmma) {
  return dateFormat.parse(datehhmma);
 }

}

edited Feb 25 '15 at 10:22

answered Feb 25 '15 at 08:38

farrellmr

1,815
2
15
26

yeah it's something like this, but my issue is regarding create a query that will fetch the time from db, and then it will convert it to the AM/PM format something like that. i want this SELECT TIME_FORMAT( `locStart`, '%h:%i %p' ) FROM locationtimes. from JPA – Ashish Ratan Feb 25 '15 at 08:41
Ive added some suggestions – farrellmr Feb 25 '15 at 09:12
Yeah that all seems to be related to my query, if u have any example then please add them as well. – Ashish Ratan Feb 25 '15 at 09:36
thanks for ur answer, but it's not working .. code has exceptions. – Ashish Ratan Feb 25 '15 at 12:22
Yeah the convertToEntityAttribute should have been a Time object. It was to show the principle of an AttributeConverter, not a perfect implementation because i dont handle exceptions either – farrellmr Feb 25 '15 at 13:16

score 0 · Accepted Answer · answered Feb 25 '15 at 13:38

I got the solution. it will be achieved by this process.

@Farrellmr Thanks for your support

package util;

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;
import java.sql.Time;

/**
 * Created by aratan on 2/25/2015.
 */
@Converter
public class MyConverter implements AttributeConverter<String, Time> {
    @Override
    public Time convertToDatabaseColumn(String s) {
        System.out.println("::::=========================convertToEntityAttribute ::" + s);
        return new Time(System.currentTimeMillis());
    }

    @Override
    public String convertToEntityAttribute(Time time) {
        System.out.println("::::=========== convertToDatabaseColumn  ====== DateUtil.getFormattedTime(time) ::" + DateUtil.getFormattedTime(time));
        return DateUtil.getFormattedTime(time);
    }
}

insert and get Query for Time as AM/PM in JPA using java

2 Answers2