Hibernate

阅读：评论：0

Hibernate

在开发的时候，我们常常需要高级查询，高级查询我们使用视图：

这是我的Oracle数据库表（三张表关联用户表，三张表没有任何没有联系）：

/*==============================================================*/
/* Table: 用户信息表                                             
/*==============================================================*/CREATE TABLE tbl_user  (u_id                   NUMBER   NOT NULL,                       --用户ID（主键）u_name             VARCHAR2(50),                                         --用户名u_realname        VARCHAR2(50),                                      --用户真实姓名u_password        VARCHAR2(50),                                      --用户密码u_email              VARCHAR2(50),                              --用户邮箱u_sex                 VARCHAR2(4),                                 --用户性别(女，男)u_birthday         DATE,                                              --用户生日u_address          VARCHAR2(50),                             --用户地址u_imgsrc                VARCHAR2(50),                           --用户头像CONSTRAINT pk_uid PRIMARY KEY (u_id)                --主键
);
DELETE FROM tbl_user;
INSERT INTO tbl_user VALUES(1,'JadeonOne','电脑','666666','1778928151@qq','男',SYSDATE,'贵州省毕节市织金县','admin.jpg'); 
INSERT INTO tbl_user VALUES(2,'JadeonTwo','电脑2','666666','1778928152@qq','男',SYSDATE,'贵州省毕节市织金县','admin.jpg'); 
INSERT INTO tbl_user VALUES(3,'JadeonThree','电脑3','666666','1778928153@qq','男',SYSDATE,'贵州省毕节市织金县','admin.jpg'); 
INSERT INTO tbl_user VALUES(4,'JadeonFour','电脑4','666666','1778928154@qq','男',SYSDATE,'贵州省毕节市织金县','admin.jpg'); 
INSERT INTO tbl_user VALUES(5,'JadeonFive','电脑5','666666','1778928155@qq','男',SYSDATE,'贵州省毕节市织金县','admin.jpg'); 
/*==============================================================*/
/* Table: 用户——说说表                                            
/*==============================================================*/
CREATE TABLE tbl_say
(s_id         NUMBER,                     --说说ID（主键）u_id         NUMBER,                     --用户ID（外键）s_remark VARCHAR2(600),           --说说内容s_time      DATE DEFAULT(SYSDATE),                           --说说时间CONSTRAINT pk_sid PRIMARY KEY (s_Id),                     --主键CONSTRAINT  fk_sid FOREIGN KEY(u_id) REFERENCES tbl_user(u_id)    --外键
);
DELETE FROM tbl_say;
INSERT INTO tbl_say VALUES(1,1,'艹，Funk You！One',DEFAULT);
INSERT INTO tbl_say VALUES(2,2,'艹，Funk You！Two',DEFAULT);
INSERT INTO tbl_say VALUES(3,3,'艹，Funk You！Three',DEFAULT);
INSERT INTO tbl_say VALUES(4,4,'艹，Funk You！Four',DEFAULT);
/*==============================================================*/
/* Table: 用户——日志表                                            
/*==============================================================*/
CREATE TABLE tbl_log
(l_id         NUMBER,                                              --日志ID（主键）u_id        NUMBER,                                              --用户ID（外键）l_title      VARCHAR2(50),                                     --日志标题l_remark VARCHAR2(600),                                    --日志内容l_time      DATE DEFAULT(SYSDATE),                   --日志时间CONSTRAINT pk_lid PRIMARY KEY (l_id),                     --主键CONSTRAINT  fk_lid FOREIGN KEY(u_id) REFERENCES tbl_user(u_id)    --外键
);
DELETE FROM tbl_log;
INSERT INTO tbl_log VALUES(1,1,'One威宁潮海','One潮海上新浪头条',DEFAULT);
INSERT INTO tbl_log VALUES(2,2,'Two威宁潮海','Two潮海上新浪头条',DEFAULT);
INSERT INTO tbl_log VALUES(3,3,'Three威宁潮海','Three威宁潮海上新浪头条',DEFAULT);
INSERT INTO tbl_log VALUES(4,4,'Four威宁潮海','Four威宁潮海上新浪头条',DEFAULT);
INSERT INTO tbl_log VALUES(5,5,'Five威宁潮海','Five威宁潮海上新浪头条',DEFAULT); INSERT INTO tbl_say VALUES(5,5,'艹，Funk You！Five',DEFAULT);
/*==============================================================*/
/* Table: 用户——相册表                                         
/*==============================================================*/
CREATE TABLE tbl_photo
(p_id                       NUMBER,            --相册ID（主键）u_id                       NUMBER,            --相册用户（外键）p_name                 VARCHAR2(50),  --相册名称(动态 title)p_context              VARCHAR2(50),   --相册描述（动态 Note）p_count                 NUMBER,            --相册数量p_cover                 VARCHAR2(100),  --相册封面（动态之后再加） p_time                   DATE DEFAULT(SYSDATE),                    --相册创建时间CONSTRAINT  pk_poid PRIMARY KEY (p_id),                     --主键CONSTRAINT  fk_poid FOREIGN KEY(u_id) REFERENCES tbl_user(u_id),    --外键    
);
DELETE FROM tbl_photo;
INSERT INTO tbl_photo VALUES (1,1,'One朋友相册','1的朋友相册One',40,'One相册的封面.jpg',0,DEFAULT);
INSERT INTO tbl_photo VALUES (2,2,'Two朋友相册','2的朋友相册Two',40,'Two相册的封面.jpg',0,DEFAULT);
INSERT INTO tbl_photo VALUES (3,3,'Three朋友相册','3的朋友相册Three',40,'Three相册的封面.jpg',0,DEFAULT);
INSERT INTO tbl_photo VALUES (4,4,'Four朋友相册','4的朋友相册Four',40,'Four相册的封面.jpg',0,DEFAULT);
INSERT INTO tbl_photo VALUES (5,5,'Five朋友相册','5的朋友相册Five',40,'Five相册的封面.jpg',0,DEFAULT);

我们给创建视图需要管理员登录，给用户赋予创建视图view的权限；

--1.给SCOTT用户赋予创建视图权限
--grant create view to scott;
--2.创建视图CREATE OR REPLACE VIEW view_friend (vtitle, vuid,    vnote,  vtime)ASSELECT   's' vtitle,      s.u_id,         s.s_remark,    s.s_timeFROM tbl_say s -- WHERE s.u_id IN  ( SELECT u_id  FROM tbl_user  WHERE u_id IN(1,2,3))UNION SELECT      l_title ,   l.u_id ,    l.l_remark,    l.l_timeFROM tbl_log l--WHERE l.u_id IN  ( SELECT u_id  FROM tbl_user WHERE u_id in(1,2,3))UNIONSELECT   'p' vtitle,       p.u_id,    p.p_cover,   p.p_timeFROM tbl_photo p  ;  -- WHERE p.u_id IN  ( SELECT u_id  FROM tbl_user WHERE u_id in(1,2,3));  
SELECT * FROM view_friend  ORDER BY vtime DESC;

创建视图的JavaBean并注解：

ntity;import java.io.Serializable;
import java.util.Date;import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;@Entity
@Table(name = "view_friend")
public class FriendDynamicInfo implements Serializable {private static final long serialVersionUID = 1L;private Integer vuid;private String vnote;private String vtitle;private Date vtime;public FriendDynamicInfo() {super();}public FriendDynamicInfo(Integer vuid, String vnote, String vtitle, Date vtime) {super();this.vuid = vuid;this.vnote = vnote;this.vtitle = vtitle;this.vtime = vtime;}@Id@Column(name = "vuid", nullable = false, precision = 22, scale = 0)public Integer getVuid() {return vuid;}public void setVuid(Integer vuid) {this.vuid = vuid;}@Column(name = "vnote")public String getVnote() {return vnote;}public void setVnote(String vnote) {this.vnote = vnote;}@Column(name = "vtitle")public String getVtitle() {return vtitle;}public void setVtitle(String vtitle) {this.vtitle = vtitle;}@Column(name = "vtime")public Date getVtime() {return vtime;}public void setVtime(Date vtime) {this.vtime = vtime;}@Overridepublic String toString() {return "FriendDynamicInfo [vuid=" + vuid + ", vnote=" + vnote + ", vtitle=" + vtitle+ ", vtime=" + vtime + "]";}}