Fork me on GitHub

3/14/2014

[MySQL] Multiple foreign key cascade delete

事情是這樣的, 遇到了一個 table B, 他分別 foreign key 到另外兩個 table AC, 我在 B to C 的 foreign key 上面設定了 on delete cascade, 但是我希望 B 因為 C 被刪除也跟著被 cascade delete 的時候, A 也可以一起被刪除.

除了 trigger 之外, 我想不到什麼其他更好的作法, 所以上 stackoverflow 發問一下: http://stackoverflow.com/questions/22341402/mysql-table-multi-foreign-key-cascade, 下面有人提到 DB 的 foreign key 並沒有一對一的限制, 所以希望作到這件事其實有點違背邏輯. 於是乎只好尋求 trigger 一解.

正當我要定義一個 ON DELETE B 的 trigger 去殺掉 A 的時候, 發現由 C cascade delete 驅動 B 刪除, 並不會觸發該 trigger, 上網一查才發現, MySQL 的手冊上寫著 Triggers currently are not activated by foreign key actions.

目前想到的解決方式, 就是原本的 on delete cascade 部份也改成用 trigger...

-- EOF --

1/16/2014

[JPA] Use refresh to get updated data after database trigger

Today I met the issue about EntityManager cache issue.

Let's say that I have 2 tables comment and commant_like. I have a database trigger which increment the field like_count of comment when inserting one new comment_like record.

Below is the orignal code looks like:

In my expectation, the save call will make the database trigger to update the like_count in the comment, but it doesn't.

public void addLikeOnComment(...) {

    commentLikeDao.save(newEntity);
    
    Comment comment = commentDao.find(id);    
    
}

After digging into the code, I found the find has been called somewhere before addLikeComment. so the entity manager will use the cached version message.

public void doWork(...) {

    commentDao.find(id);

    addLikeOnComment(...);

}

The solution is simple. Use the entity manager refresh, to load data from database instead of the cached version

public void addLikeOnComment(...) {

    commentLikeDao.save(newEntity);
    
    Comment comment = commentDao.find(id); 
    commentDao.getEntityManager().refresh(comment)
    
}

-- EOF --

1/09/2014

[Vim] Map Ctrl+S to save file

Beacuse pressing Ctrl-S in terminal will performs an 'XOFF' which stops commands from being receiv

ed. (If Ctrl-S freezes your terminal, type Ctrl-Q to get it going again.)

So, we need to disable that first, put below command in .bashrc

stty stop undef

Then, in the vimrc, add below lines

noremap <silent> <C-S>          :update<CR>
vnoremap <silent> <C-S>         <C-C>:update<CR>
inoremap <silent> <C-S>         <C-O>:update<CR>

It's done. The Ctrl+S has been mapped to :w

References:

  1. In vim how to map “save” to ctrl-s
  2. How to map Ctrl+S in vim on gnome-terminal?

-- EOF --

1/01/2014

[Hibernate] Composite primary key maps composite foreign key

The blog records how to map two entities by the composite keys by using hibernate. Let's say we have 2 entities PrizeItem and PrizeWinnerInfo. We want to map them as OneToOne relation.

The PrizeWinnerInfo (pzwininfo_pzitem_pz_id, pzwininfo_pzitem_id) refer to PrizeItem (pzitem_pz_id, pzitem_id) with the composite foreign key.

Step 0.

Database schema of PrizeItem, table name: ix_prize_item

+----------------------------+--------------+------+-----+---------+-------+
| Field                      | Type         | Null | Key | Default | Extra |
+----------------------------+--------------+------+-----+---------+-------+
| pzitem_pz_id               | bigint(20)   | NO   | PRI | NULL    |       |
| pzitem_id                  | int(11)      | NO   | PRI | NULL    |       |
| pzitem_expiration          | datetime     | YES  |     | NULL    |       |
| pzitem_won                 | tinyint(1)   | NO   |     | 0       |       |
| pzitem_won_time            | datetime     | YES  |     | NULL    |       |
| pzitem_winner_info_applied | tinyint(1)   | NO   |     | 0       |       |
| pzitem_pzcha_id            | int(11)      | YES  | MUL | NULL    |       |
| pzitem_serial              | varchar(255) | YES  |     | NULL    |       |
+----------------------------+--------------+------+-----+---------+-------+

Database schema of PrizeWinnerInfo, table name: ix_prize_winner_info

+------------------------+--------------+------+-----+---------+-------+
| Field                  | Type         | Null | Key | Default | Extra |
+------------------------+--------------+------+-----+---------+-------+
| pzwininfo_pzcha_id     | int(11)      | NO   | MUL | 0       |       |
| pzwininfo_pzitem_pz_id | bigint(20)   | NO   | PRI | 0       |       |
| pzwininfo_pzitem_id    | int(11)      | NO   | PRI | 0       |       |
| pzwininfo_name         | varchar(255) | NO   |     | NULL    |       |
| pzwininfo_email        | varchar(128) | NO   |     | NULL    |       |
| pzwininfo_phone        | varchar(32)  | YES  |     | NULL    |       |
| pzwininfo_detail       | longtext     | YES  |     | NULL    |       |
+------------------------+--------------+------+-----+---------+-------+

Step 1.

Tip: Use @Embeddable to define the composite key

The composite primary key PrizeItemPK of PrizeItem

@Embeddable
public class PrizeItemPK implements Serializable
{
    private final static long serialVersionUID = 1L;

    @Column(name="pzitem_pz_id")
    private Long prizeId;

    @Column(name="pzitem_id")
    private Integer prizeItemId;

    public PrizeItemPK() {}
    public PrizeItemPK(Long prizeId, Integer prizeItemId) {
        super();
        this.prizeId = prizeId;
        this.prizeItemId = prizeItemId;
    }

    @JsonGetter("prize_id")
    public Long getPrizeId() {
        return prizeId;
    }

    public void setPrizeId(Long prizeId) {
        this.prizeId = prizeId;
    }

    @JsonGetter("prize_item_id")
    public Integer getPrizeItemId() {
        return prizeItemId;
    }

    public void setPrizeItemId(Integer prizeItemId) {
        this.prizeItemId = prizeItemId;
    }

}

The composite primary key PrizeWinnerInfoPK of PrizeWinnerInfo

@Embeddable
public class PrizeWinnerInfoPK implements Serializable
{
    private final static long serialVersionUID = 1L;

    @Column(name="pzwininfo_pzitem_pz_id")
    private Long prizeId;

    @Column(name="pzwininfo_pzitem_id")
    private Integer prizeItemId;

    public PrizeWinnerInfoPK() {}
    public PrizeWinnerInfoPK(Long prizeId, Integer prizeItemId) {
        super();
        this.prizeId = prizeId;
        this.prizeItemId = prizeItemId;
    }

    @JsonGetter("prize_id")
    public Long getPrizeId() {
        return prizeId;
    }

    public void setPrizeId(Long prizeId) {
        this.prizeId = prizeId;
    }

    @JsonGetter("prize_item_id")
    public Integer getPrizeItemId() {
        return prizeItemId;
    }

    public void setPrizeItemId(Integer prizeItemId) {
        this.prizeItemId = prizeItemId;
    }

}

Step2.

Tip1: Use @OneToMany mapping, not @OneToOne
Tip2: Use @JsonManagedReference and @JsonBackReference to avoid cycling json serialization

ORM entity of PrizeItem

@Entity
@Table(name="ix_prize_item")
@JsonAutoDetect(getterVisibility=JsonAutoDetect.Visibility.NONE)
public class PrizeItem
{
    @EmbeddedId
    private PrizeItemPK pk;

    ...

    @OneToMany(mappedBy="prizeItem", fetch=FetchType.EAGER)
    @JsonBackReference
    private List winnerInfo;

}

ORM entity of PrizeWinnerInfo

@Entity
@Table(name="ix_prize_winner_info")
@JsonAutoDetect(getterVisibility=JsonAutoDetect.Visibility.NONE)
public class PrizeWinnerInfo extends AbstractExtensibleJsonAliasBean
{
    @EmbeddedId
    private PrizeWinnerInfoPK PK;

    ...

    @ManyToOne(fetch=FetchType.EAGER)
    @JoinColumns ({
        @JoinColumn(name="pzwininfo_pzitem_pz_id", referencedColumnName = "pzitem_pz_id", insertable = false, updatable = false),
        @JoinColumn(name="pzwininfo_pzitem_id", referencedColumnName = "pzitem_id", insertable = false, updatable = false)
    })
    @JsonManagedReference
    private PrizeItem prizeItem;

}

2014/01/16 Note:

I found the use of @JsonBackReference will make the serialization fail on that field. So, I took the below solution (http://stackoverflow.com/a/10111411/474002)

References:

  1. java - hibernate composite Primary key contains a composite foreign key, how to map this - Stack Overflow
  2. java - @OneToOne annotation within composite key class is not working - Stack Overflow
  3. @JoinColumn is part of the composite primary keys | dwuysan