How can I update MySQL table timestamp from JPA query?

Question

Here is my model:

@Entity(name = "checkinusers")
public class User {

    @Id
    @JsonIgnore
    @ApiModelProperty(hidden = true)
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", nullable = false, columnDefinition = "INT(1)")
    private long userId;

    @Column(name = "username", nullable = false)
    private String username;

    @Column(name = "password", nullable = false, columnDefinition = "TEXT")
    private String password;

    @Column(name = "salt", nullable = false, columnDefinition = "TEXT")
    private String salt;

    @ApiModelProperty(hidden = true)
    @Column(name = "lastlogin", nullable = false, columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP")
    private Date created;

Here is the method:

@Modifying
@Transactional
@Query("UPDATE User u SET  u.created ='0000-00-00 00:00:00' WHERE u.username = :username ")
 void updateLastLogin(@Param("username") String username);

This is the error I get when I try to start spring application

Failed to execute goal
org.springframework.boot:spring-boot-maven-plugin:1.5.9.RELEASE:run(default-cli) on project licenta: An exception occurred while running. null: InvocationTargetException: Error creating bean with name 'userController': Unsatisfied dependency expressed through field 'userService'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'userService': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract void com.example.licenta.service.UserService.update(java.lang.String)! org.hibernate.hql.internal.ast.QuerySyntaxException: User is not mapped [UPDATE User u SET u.created ='0000-00-00 00:00:00' WHERE u.username = :user name ]

dpr · Accepted Answer · 2018-07-02T07:51:28.197

0

The actual problem seems to be that JPA doesn't know the User entity:

org.hibernate.hql.internal.ast.QuerySyntaxException: User is not mapped

As you specify a dedicated name in the @Entity annotation you need to use this name in all JPA queries on this entity:

@Entity(name = "checkinusers")

That is the query will need to be:

@Query("UPDATE checkinusers u SET  u.created = '0000-00-00 00:00:00' WHERE u.username = :username ")

See this question for details.

edited Jul 02 '18 at 07:51

answered Jul 02 '18 at 07:17

dpr

10,591
3
41
71

Thanks for trying but it didn't solve the problem :(. – cUser Jul 02 '18 at 07:20
1

very close to right answer. Working version is : @Query("UPDATE checkinusers u SET u.created = '0000-00-00 00:00:00' WHERE u.username = :username ") – cUser Jul 02 '18 at 07:38
It helped me a lot. Thank you – cUser Jul 02 '18 at 07:40
@cUser the `{ts '0000-00-00 00:00:00'}` notation didn't work? Could you perhaps comment the error that JPA reported when using my proposed query - just being curious... – dpr Jul 02 '18 at 07:42
It looks like the `{ts '0000-00-00 00:00:00'}` notation will only work with hibernate >= 5.0. See [the hibernate user guide](http://docs.jboss.org/hibernate/orm/5.0/userguide/html_single/Hibernate_User_Guide.html#hql-literals) – dpr Jul 02 '18 at 08:05
1

That must be the problem given the fact that my version is < 5 – cUser Jul 02 '18 at 08:31

How can I update MySQL table timestamp from JPA query?

1 Answers1