SpringDataJpa創建聯合索引的實現

SpringDataJpa創建聯合索引

在這裡插入圖片描述

創建聯合索引對應類

/**
 * 作者:guoyzh
 * 時間:2019/12/30 14:58
 * 功能:戴鏡視力復查聯合主鍵
 */
@Data
@Embeddable
public class VisualReexaminationUnionKey implements Serializable {
    @Column(name = "id")
    private String id;
    @Column(name = "c_review_date")
    private java.sql.Timestamp cReviewDate;
}

創建映射實體類

@Table(name = "qy_visual_reexamination")
@Entity
@Data
public class QyVisualReexamination {
    /*@Id
    @Column(nullable = true, name = "id")
    private String id;
    @Id
    @Column(nullable = true, name = "c_review_date")
    private java.sql.Timestamp cReviewDate;*/
    // 復合主鍵
    @EmbeddedId
    private VisualReexaminationUnionKey id;
    @Column(nullable = true, name = "c_clientid")
    private String cClientid;
    @Column(nullable = true, name = "c_ygscode")
    private String cYgscode;
    @Column(nullable = true, name = "c_primary_vision_r")
    private String cPrimaryVisionR;
    @Column(nullable = true, name = "c_primary_vision_l")
    private String cPrimaryVisionL;
    @Column(nullable = true, name = "c_ball_r")
    private String cBallR;
    @Column(nullable = true, name = "c_ball_l")
    private String cBallL;
    @Column(nullable = true, name = "c_pole_r")
    private String cPoleR;
    @Column(nullable = true, name = "c_pole_l")
    private String cPoleL;
    @Column(nullable = true, name = "c_axes_r")
    private String cAxesR;
    @Column(nullable = true, name = "c_axes_l")
    private String cAxesL;
    @Column(nullable = true, name = "c_add_r")
    private String cAddR;
    @Column(nullable = true, name = "c_add_l")
    private String cAddL;
    @Column(nullable = true, name = "c_check_r")
    private String cCheckR;
    @Column(nullable = true, name = "c_check_l")
    private String cCheckL;
    @Column(nullable = true, name = "c_proposal")
    private String cProposal;
    @Column(nullable = true, name = "c_com")
    private String cCom;
}

添加新數據

@Override
public Object addVisualReexamination(String id, String clientId, String reviewDate, String ygsCode, String primaryVisionR,
                                     String primaryVisionL, String ballR, String ballL, String poleR, String poleL, String axesR,
                                     String axesL, String addR, String addL, String checkR, String checkL, String proposal, String comId) {
    QyVisualReexamination bean = new QyVisualReexamination();
    // 生成聯合索引
    VisualReexaminationUnionKey unionId = new VisualReexaminationUnionKey();
    unionId.setCReviewDate(Timestamp.valueOf(reviewDate));
    unionId.setId(id);
    bean.setId(unionId);
    bean.setCClientid(clientId);
    bean.setCYgscode(ygsCode);
    bean.setCPrimaryVisionR(primaryVisionR);
    bean.setCPrimaryVisionL(primaryVisionL);
    bean.setCBallR(ballR);
    bean.setCBallL(ballL);
    bean.setCPoleR(poleR);
    bean.setCPoleL(poleL);
    bean.setCAxesR(axesR);
    bean.setCAxesL(axesL);
    bean.setCAddR(addR);
    bean.setCAddL(addL);
    bean.setCCom(comId);
    bean.setCCheckR(checkR);
    bean.setCCheckL(checkL);
    bean.setCProposal(proposal);
    QyVisualReexamination save = mQyVisualReexaminationDao.save(bean);
    return save.getId();
}

SpringDataJpa指定聯合索引

如何,現在我的表裡使用訂單ID和產品ID作為唯一索引,那麼需要在定義表實體類時

在@Table中指定UniqueConstraint

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import javax.persistence.*;
/**
 * 產品表
 *
 * @author wulinfeng
 * @since 2019/12/13
 */
@Entity
@Table(name = "t_product", uniqueConstraints = @UniqueConstraint(columnNames = {"orderId", "productId"}))
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class ProductItem {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    // 訂單Id
    @Column(nullable = false, length = 32)
    private String orderId;
    // 受理產品編碼
    @Column(length = 32)
    private String productId;
    // 產品名稱
    @Column(length = 32)
    private String productName;
    // 時間戳
    @Column(length = 13)
    private Long timestamp;
}

把原來的t_product表drop掉,重啟spring boot,再看該表

自動加上唯一索引瞭

mysql> show index from t_product;
+-----------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name                    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_product |          0 | PRIMARY                     |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| t_product |          0 | UK1mvw2lcd07t4cuicl4awfbgkw |            1 | order_id    | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| t_product |          0 | UK1mvw2lcd07t4cuicl4awfbgkw |            2 | product_id  | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

以上為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。

推薦閱讀: