CODEWITHSUNDEEP
javamysqldatetimeplayframeworktimestamp
spiralarchitect
spiralarchitect

Reputation: 910

Play framework - Mysql error on save model

I have a Model created as follows:

@Entity
@Table(name ="news_items")
public class NewsItem extends Model {

/**
 * 
 */
private static final long serialVersionUID = 1L;

@Id
@GeneratedValue
public long id;
public String url;
public long friend_id;
public int count_for_the_day;
public String friend_name;
public String friend_img;
public Date friend_posted_at;
public String friend_text;

@Formats.DateTime(pattern="yyyy-MM-dd HH:mm:ss.SSS")
public Date current_time = new Date();

}

I am trying to save it in controller in the following way:

                     NewsItem _newsItem = new NewsItem();


                          _newsItem.url = shared_url;
                          _newsItem.friend_name = status.getUser().getName();
                          _newsItem.friend_img = status.getUser().getProfileImageURL();
                          _newsItem.friend_id = status.getUser().getId();
                          _newsItem.friend_text = status.getText();
                          _newsItem.friend_posted_at = status.getCreatedAt();
                          _newsItem.count_for_the_day = 1;
                          _newsItem.save();
                          resultItems.add(_newsItem);

None of the values are null. I am getting an error in the activator console as:

Caused by: javax.persistence.PersistenceException: ERROR executing DML bindLog[] 
error[You have an error in your SQL syntax; check the manual that corresponds to 
your MySQL server version for the right syntax to use near 'current_time) values 
('http://www.lahamag.com/Details/42019/119/%D8%B3%D8%B9%D9%' at line 1]

The partial table structure relevant to the variable in mysql DB looks like this:

| friend_text       | varchar(255) | YES  |     | NULL    |                |
| current_time      | datetime     | YES  |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+

I have following questions:

  1. I am not executing any query. I suppose Play internally does that. How I can see full query with values of what is being executed when it does save()?
  2. How to solve this?

Upvotes: 0

Views: 596

Answers (2)

singhakash
singhakash

Reputation: 7919

How I can see full query with values of what is being executed?

  1. In application.conf add

    db.default.logStatements=true

  2. Create a file logger.xml inside conf folder with content

    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
    <encoder>
        <pattern>%-5level - %msg%n</pattern>
    </encoder>
</appender>

<logger name="com.jolbox.bonecp" level="DEBUG">
    <appender-ref ref="STDOUT" />
</logger>

<logger name="play" level="DEBUG">
    <appender-ref ref="STDOUT" />
</logger>

<logger name="application" level="DEBUG">
    <appender-ref ref="STDOUT" />
</logger>

    This will show the sql generated in console.

Source

How to solve this?

The error is because current_time is reserved word in mysql.

Solution

@Column(name="`current_time`")   //backticks
public Date current_time = new Date();

It will do the trick.

Upvotes: 1

biesior
biesior

Reputation: 55798

Apparently current_time is reserved word in MySQL and should be quoted, TBH I have no idea how to force these quotes in Ebean, so fastest solution is renaming the field to some non-reserved like publishDate.

BTW, as you can see you can use camelCase names for your fields in models, Ebean handles them correctly by changing in queries to underscored_version

Upvotes: 0

Related Questions