在开发的时候,我们常常需要高级查询,高级查询我们使用视图:
这是我的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.com','男',SYSDATE,'贵州省毕节市织金县','admin.jpg');
INSERT INTO tbl_user VALUES(2,'JadeonTwo','电脑2','666666','1778928152@qq.com','男',SYSDATE,'贵州省毕节市织金县','admin.jpg');
INSERT INTO tbl_user VALUES(3,'JadeonThree','电脑3','666666','1778928153@qq.com','男',SYSDATE,'贵州省毕节市织金县','admin.jpg');
INSERT INTO tbl_user VALUES(4,'JadeonFour','电脑4','666666','1778928154@qq.com','男',SYSDATE,'贵州省毕节市织金县','admin.jpg');
INSERT INTO tbl_user VALUES(5,'JadeonFive','电脑5','666666','1778928155@qq.com','男',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) AS SELECT 's' vtitle, s.u_id, s.s_remark, s.s_time FROM 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_time FROM tbl_log l --WHERE l.u_id IN ( SELECT u_id FROM tbl_user WHERE u_id in(1,2,3)) UNION SELECT 'p' vtitle, p.u_id, p.p_cover, p.p_time FROM 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并注解:
package com.zone.entity;
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;
}
@Override
public String toString() {
return "FriendDynamicInfo [vuid=" + vuid + ", vnote=" + vnote + ", vtitle=" + vtitle
+ ", vtime=" + vtime + "]";
}
}
