精华内容
下载资源
问答
  • 多表创建视图

    2010-12-20 15:51:16
    要从这几个中把字段名相同的数据放在一个视图中去,sql语句为: DROP VIEW IF EXISTS `d5000`.`device_view`; CREATE VIEW `d5000`.`device_view` AS select ID,CODE,NAME,ST_ID,BV_ID,VL_ID from ...


    要从这几个表中把字段名相同的数据放在一个视图中去,sql语句为:

    DROP VIEW IF EXISTS `d5000`.`device_view`;
    
    CREATE
        VIEW `d5000`.`device_view` 
    	AS  
    	 select ID,CODE,NAME,ST_ID,BV_ID,VL_ID from aclineend union all select ID,CODE,NAME,ST_ID,BV_ID,VL_ID from disconnector union all select ID,CODE,NAME,ST_ID,BV_ID,VL_ID from  generatingunit union all select ID,CODE,NAME,ST_ID,BV_ID,VL_ID from  compensator_p union all select ID,CODE,NAME,ST_ID,BV_ID,VL_ID from energyconsumer union all select ID,CODE,NAME,ST_ID,BV_ID,VL_ID from breaker



    关键点:用union all 把这几个没有直接关联的表连接起来。


     

    展开全文
  • -- emp_dept_ve 是给视图取的视图名   第二种方法 create or replace view emp_dept_ve as select e.empno,e.ename,e.job,e.hiredate,e.sal, d.dname,d.loc from emp e left join dept d on d.deptno ...

    create or replace view emp_dept_ve

    (empno,ename,job,hiredate,sal,dname,loc)  AS
    select e.empno,e.ename,e.job,e.hiredate,e.sal, d.dname,d.loc
    from emp e
    left join dept d on d.deptno = e.deptno

    -- emp_dept_ve 是给视图取的视图名


     

    第二种方法

    create or replace view emp_dept_ve as
    select e.empno,e.ename,e.job,e.hiredate,e.sal, d.dname,d.loc
    from emp e
    left join dept d on d.deptno = e.deptno; 

     

    学习分享必看:javacto.taobao.com

    展开全文
  • 同用户,多表创建视图 2、同一个数据库实例,不同用户下。多表创建视图 3、同一个数据库,不同数据库实例,多表创建视图 4、不同类型数据库,多表创建视图 1、同一个数据库实例。同用户,多表创建视图 暂缺。! 2、...
    • 1、同一个数据库实例。同用户,多表创建视图
    • 2、同一个数据库实例,不同用户下。多表创建视图
    • 3、同一个数据库,不同数据库实例,多表创建视图
    • 4、不同类型数据库,多表创建视图

    • 1、同一个数据库实例。同用户,多表创建视图

      暂缺。!

    • 2、同一个数据库实例,不同用户下,多表创建视图

      步骤一、创建实例,创建两个用户的sql:

      -- 创建实例 : Database Configuration Assistant 创建数据库  
      
      -- 查询表SYSTEM表空间的数据文件的物理路径
      -- SELECT FILE_NAME FROM DBA_DATA_FILES WHERE (TABLESPACE_NAME = 'SYSTEM')
      
      -- 创建门户系统的表空间 TBS_ORCL_TEMPLETE1_0 
      
      -- 遵循表空间命名规范 TBS_Servername_Function_SerialNumber
      -- TBS是必需具备的,表示该对象为表空间。

      -- ServerName是数据库物理server的名称或名称简写。 -- Function是表示该表空间的作用 -- SerialNumber是具有该作用的数据库表空间的序列号 --------------------------------------------------------- --设置表空间自己主动扩容 CREATE TABLESPACE HY_02C DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TBS_ORCL_DEMO.DBF' size 200M AUTOEXTEND on next 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL ; -- 假设存在HY_02C用户 先删除 -- DROP USER "HY_02C" CASCADE; -- 创建用户 CREATE USER HY_02C IDENTIFIED BY HY_02C DEFAULT TABLESPACE HY_02C QUOTA UNLIMITED ON HY_02C QUOTA 100000 K ON USERS ACCOUNT UNLOCK ; -- 设置用户权限 GRANT CONNECT,RESOURCE,DBA TO HY_02C; GRANT CREATE SESSION TO HY_02C; GRANT CREATE PROCEDURE TO HY_02C; --导入数据 $imp file=F:\user09.dmp full=y; ------測试同一个数据库实例下的不同用户,新建用户 --设置表空间自己主动扩容 CREATE TABLESPACE DOPD DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TBS_ORCL_DOPD.DBF' size 200M AUTOEXTEND on next 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL ; --删除表空间 --DROP TABLESPACE DOPD INCLUDING CONTENTS AND DATAFILES; -- 假设存在USER_DEMO用户 先删除 -- DROP USER "DOPD" CASCADE; -- 创建用户 CREATE USER DOPD IDENTIFIED BY DOPD DEFAULT TABLESPACE TBS_ORCL_DOPD QUOTA UNLIMITED ON TBS_ORCL_DEMO QUOTA 100000 K ON USERS ACCOUNT UNLOCK ; -- 设置用户权限 GRANT CONNECT,RESOURCE,DBA TO DOPD; GRANT CREATE SESSION TO DOPD; GRANT CREATE PROCEDURE TO DOPD; --导入数据 $imp file=F:\user09.dmp full=y;

      第一个用户:
      第一个用户
      备注:採用的2张表:
      TB_BAS_CKYX
      TB_BAS_META_BLOB

      第二个用户:
      第二个用户
      备注:採用的3张表:
      TB_BAS_YGYX
      TB_BAS_HEADTABLE
      TB_BAS_YXZZCP

    步骤二、数据库表设计:

    用户HY_02C下:
    1、TB_BAS_CKYX
    TB_BAS_CKYX

    sql:
    -- Create table
    create table TB_BAS_CKYX
    (
      id                     VARCHAR2(40) not null,
      source_data_id         NUMBER(38),
      image_no               VARCHAR2(50),
      image_handling_no      VARCHAR2(40),
      satellite_code         VARCHAR2(50),
      sensor_code            VARCHAR2(50),
      image_rr_value         NUMBER(38,8),
      image_scale_code       VARCHAR2(4),
      image_bands            VARCHAR2(40),
      projection_code        VARCHAR2(20),
      central_meridian       NUMBER(38,8),
      locator_unit           VARCHAR2(10),
      zone_code              VARCHAR2(10),
      zone_no                NUMBER(38),
      es_code                VARCHAR2(10),
      es_value               NUMBER(38,8),
      cloudcover_code        NUMBER(38,8),
      tl_lon                 NUMBER(38,8),
      tl_lat                 NUMBER(38,8),
      tr_lon                 NUMBER(38,8),
      tr_lat                 NUMBER(38,8),
      br_lon                 NUMBER(38,8),
      br_lat                 NUMBER(38,8),
      bl_lon                 NUMBER(38,8),
      bl_lat                 NUMBER(38,8),
      tl_x                   NUMBER(38,8),
      tl_y                   NUMBER(38,8),
      tr_x                   NUMBER(38,8),
      tr_y                   NUMBER(38,8),
      br_x                   NUMBER(38,8),
      br_y                   NUMBER(38,8),
      bl_x                   NUMBER(38,8),
      bl_y                   NUMBER(38,8),
      product_ar_code        VARCHAR2(10),
      product_category_code  VARCHAR2(20),
      product_ownership_unit VARCHAR2(50),
      production_unit        VARCHAR2(50),
      product_grade_code     VARCHAR2(4),
      product_quality        VARCHAR2(4),
      product_security_code  VARCHAR2(4),
      product_format_code    VARCHAR2(4),
      product_archive_date   TIMESTAMP(6),
      product_time_phase     DATE,
      product_size_measure   VARCHAR2(4),
      product_size           NUMBER(38),
      product_quality_report VARCHAR2(500),
      product_order_no       VARCHAR2(40),
      product_storage_path   VARCHAR2(500),
      create_date            TIMESTAMP(6),
      used_count             NUMBER(38),
      status                 VARCHAR2(4),
      remarks                VARCHAR2(400),
      product_file_list      BLOB,
      envelope               LONG,
      source_table_name      VARCHAR2(100),
      cs_code                VARCHAR2(10),
      data_name              VARCHAR2(400),
      sjsj                   DATE
    )
    tablespace GF_GXFW
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 128K
        minextents 1
        maxextents unlimited
      );
    -- Add comments to the columns 
    comment on column TB_BAS_CKYX.id
      is '记录编号';
    comment on column TB_BAS_CKYX.source_data_id
      is '源数据ID';
    comment on column TB_BAS_CKYX.image_no
      is '图幅号';
    comment on column TB_BAS_CKYX.image_handling_no
      is '图处理关联编号';
    comment on column TB_BAS_CKYX.satellite_code
      is '卫星类别';
    comment on column TB_BAS_CKYX.sensor_code
      is '传感器类别';
    comment on column TB_BAS_CKYX.image_rr_value
      is '影像分辨率';
    comment on column TB_BAS_CKYX.image_scale_code
      is '影像比例尺';
    comment on column TB_BAS_CKYX.image_bands
      is '影像波段信息';
    comment on column TB_BAS_CKYX.projection_code
      is '投影方式';
    comment on column TB_BAS_CKYX.central_meridian
      is '中央子午线';
    comment on column TB_BAS_CKYX.locator_unit
      is '坐标单位';
    comment on column TB_BAS_CKYX.zone_code
      is '分带类型';
    comment on column TB_BAS_CKYX.zone_no
      is '投影带号';
    comment on column TB_BAS_CKYX.es_code
      is '高程基准';
    comment on column TB_BAS_CKYX.es_value
      is '高程基准值';
    comment on column TB_BAS_CKYX.cloudcover_code
      is '云量';
    comment on column TB_BAS_CKYX.tl_lon
      is '左上经度';
    comment on column TB_BAS_CKYX.tl_lat
      is '左上纬度';
    comment on column TB_BAS_CKYX.tr_lon
      is '右上经度';
    comment on column TB_BAS_CKYX.tr_lat
      is '右上纬度';
    comment on column TB_BAS_CKYX.br_lon
      is '右下经度';
    comment on column TB_BAS_CKYX.br_lat
      is '右下纬度';
    comment on column TB_BAS_CKYX.bl_lon
      is '左下经度';
    comment on column TB_BAS_CKYX.bl_lat
      is '左下纬度';
    comment on column TB_BAS_CKYX.tl_x
      is '左上x坐标';
    comment on column TB_BAS_CKYX.tl_y
      is '左上y坐标';
    comment on column TB_BAS_CKYX.tr_x
      is '右上x坐标';
    comment on column TB_BAS_CKYX.tr_y
      is '右上y坐标';
    comment on column TB_BAS_CKYX.br_x
      is '右下x坐标';
    comment on column TB_BAS_CKYX.br_y
      is '右下y坐标';
    comment on column TB_BAS_CKYX.bl_x
      is '左下x坐标';
    comment on column TB_BAS_CKYX.bl_y
      is '左下y坐标';
    comment on column TB_BAS_CKYX.product_ar_code
      is '行政区';
    comment on column TB_BAS_CKYX.product_category_code
      is '产品分类';
    comment on column TB_BAS_CKYX.product_ownership_unit
      is '产品全部权单位';
    comment on column TB_BAS_CKYX.production_unit
      is '产品生产单位';
    comment on column TB_BAS_CKYX.product_grade_code
      is '产品等级';
    comment on column TB_BAS_CKYX.product_quality
      is '产品质检';
    comment on column TB_BAS_CKYX.product_security_code
      is '产品秘密等级';
    comment on column TB_BAS_CKYX.product_format_code
      is '产品行式';
    comment on column TB_BAS_CKYX.product_archive_date
      is '产品归档日期';
    comment on column TB_BAS_CKYX.product_time_phase
      is '产品时相';
    comment on column TB_BAS_CKYX.product_size_measure
      is '产品大小单位';
    comment on column TB_BAS_CKYX.product_size
      is '产品大小';
    comment on column TB_BAS_CKYX.product_quality_report
      is '产品质量报告';
    comment on column TB_BAS_CKYX.product_order_no
      is '产品任务单编号';
    comment on column TB_BAS_CKYX.product_storage_path
      is '产品存储位置';
    comment on column TB_BAS_CKYX.create_date
      is '记录创建时间';
    comment on column TB_BAS_CKYX.used_count
      is '使用次数';
    comment on column TB_BAS_CKYX.status
      is '记录状态';
    comment on column TB_BAS_CKYX.remarks
      is '备注';
    comment on column TB_BAS_CKYX.product_file_list
      is '产品文件清单';
    comment on column TB_BAS_CKYX.envelope
      is '产品数据时间(2014年5月27日)';
    comment on column TB_BAS_CKYX.source_table_name
      is '来源表名';
    comment on column TB_BAS_CKYX.cs_code
      is '坐标系(2014年5月27日)';
    comment on column TB_BAS_CKYX.data_name
      is '产品名称(2014年5月27日)';
    comment on column TB_BAS_CKYX.sjsj
      is '数据范围';
    -- Create/Recreate primary, unique and foreign key constraints 
    alter table TB_BAS_CKYX
      add constraint PK_CKYX_ID primary key (ID)
      using index 
      tablespace GF_GXFW
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
        initial 64K
        minextents 1
        maxextents unlimited
      );
    -- Grant/Revoke object privileges 
    grant select on TB_BAS_CKYX to DOPD;
    

    2、TB_BAS_META_BLOB
    TB_BAS_META_BLOB

    sql:
    -- Create table
    create table TB_BAS_META_BLOB
    (
      f_dataid      NUMBER not null,
      f_metadata    BLOB,
      f_quickimage1 BLOB,
      f_quickimage2 BLOB,
      f_quickimage3 BLOB,
      f_quickimage4 BLOB,
      f_thumimage   BLOB,
      f_shapeimage  BLOB
    )
    tablespace HY_02C
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 128M
        minextents 1
        maxextents unlimited
      );
    -- Create/Recreate indexes 
    create index INDEX_TB_BAS_META_BLOB_DATAID on TB_BAS_META_BLOB (F_DATAID)
      tablespace HY_02C
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
        initial 10M
        minextents 1
        maxextents unlimited
      );
    

    用户DOPD下:
    1、TB_BAS_META_YGYX
    TB_BAS_META_YGYX

    sql:
    -- Create table
    create table TB_BAS_META_YGYX
    (
      dataid                NUMBER,
      catalogid             VARCHAR2(50),
      satelliteid           VARCHAR2(50),
      sensorid              VARCHAR2(50),
      recstationid          VARCHAR2(50),
      subscenemode          VARCHAR2(100) not null,
      imagingmode           VARCHAR2(50),
      productdate           DATE,
      productlevel          VARCHAR2(100),
      pixelspacing          NUMBER,
      bands                 VARCHAR2(255),
      scenecount            NUMBER,
      overallquality        NUMBER,
      resampletechnique     VARCHAR2(100),
      productorientation    VARCHAR2(100),
      trackid               NUMBER,
      satpath               NUMBER,
      satrow                NUMBER,
      satpathbias           VARCHAR2(100),
      satrowbias            VARCHAR2(100),
      sunelevation          NUMBER,
      sunazimuthelevation   NUMBER not null,
      scenedate             DATE,
      imagingstarttime      VARCHAR2(100),
      imagingstoptime       VARCHAR2(100),
      satoffnadir           NUMBER,
      cloudamount           VARCHAR2(100),
      wkt                   VARCHAR2(255),
      scenecenterlat        NUMBER,
      scenecenterlong       NUMBER,
      dataupperleftlat      NUMBER,
      dataupperleftlong     NUMBER,
      dataupperrightlat     NUMBER,
      dataupperrightlong    NUMBER,
      datalowerleftlat      NUMBER,
      datalowerleftlong     NUMBER,
      datalowerrightlat     NUMBER,
      datalowerrightlong    NUMBER,
      productupperleftlat   NUMBER,
      productupperleftlong  NUMBER,
      productupperrightlat  NUMBER,
      productupperrightlong NUMBER,
      productlowerleftlat   NUMBER,
      productlowerleftlong  NUMBER,
      productlowerrightlat  NUMBER,
      productlowerrightlong NUMBER,
      scenepath             NUMBER,
      scenerow              NUMBER,
      f_datatypename        VARCHAR2(100),
      f_mapprojection       NVARCHAR2(50),
      id                    VARCHAR2(40),
      cloud_amount          NUMBER,
      receive_time          DATE,
      available             VARCHAR2(50)
    )
    tablespace GF_GXFW
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 192M
        minextents 1
        maxextents unlimited
      );
    -- Add comments to the columns 
    comment on column TB_BAS_META_YGYX.dataid
      is '数据唯一标识';
    comment on column TB_BAS_META_YGYX.catalogid
      is '分类编码';
    comment on column TB_BAS_META_YGYX.satelliteid
      is '卫星标识  ZY3';
    comment on column TB_BAS_META_YGYX.sensorid
      is '谱段模式  PAN MS';
    comment on column TB_BAS_META_YGYX.recstationid
      is '接收站标识 MY HS SY OS';
    comment on column TB_BAS_META_YGYX.subscenemode
      is '分景模式  N(注:标准景) d(注:双倍景) t(注:三倍景) s(注: 条带影像)';
    comment on column TB_BAS_META_YGYX.imagingmode
      is '成像模式  N F B L R';
    comment on column TB_BAS_META_YGYX.productdate
      is '生产日期  YYYYMMDDHHMMSS';
    comment on column TB_BAS_META_YGYX.productlevel
      is '产品级别  SC GEC eGEC GTC DOM';
    comment on column TB_BAS_META_YGYX.pixelspacing
      is '空间分辨率(像元间距)   ';
    comment on column TB_BAS_META_YGYX.bands
      is '波段号列表(以逗号分隔)  波段号以逗号隔开。波段取值范围:如。CCD:1。2,3,4,5';
    comment on column TB_BAS_META_YGYX.scenecount
      is '条带景数  ';
    comment on column TB_BAS_META_YGYX.overallquality
      is '质量评价结果    取值范围:0-9';
    comment on column TB_BAS_META_YGYX.resampletechnique
      is '几何处理方式';
    comment on column TB_BAS_META_YGYX.productorientation
      is '辐射处理方式    ';
    comment on column TB_BAS_META_YGYX.trackid
      is '轨道号';
    comment on column TB_BAS_META_YGYX.satpath
      is '星下点PATH   1-457';
    comment on column TB_BAS_META_YGYX.satrow
      is '星下点ROW    1~480';
    comment on column TB_BAS_META_YGYX.satpathbias
      is '星下点PATH偏离值    A、B、C、D、E(由东向西)';
    comment on column TB_BAS_META_YGYX.satrowbias
      is '星下点ROW偏离值 1、2、3、4、5(由北向南)';
    comment on column TB_BAS_META_YGYX.sunelevation
      is '太阳高度角';
    comment on column TB_BAS_META_YGYX.sunazimuthelevation
      is '太阳方位角';
    comment on column TB_BAS_META_YGYX.scenedate
      is '景的日期(图像採集日期)  YYYYMMDDHHMMSS';
    comment on column TB_BAS_META_YGYX.imagingstarttime
      is '该景各波段起始採集时间   YYYYMMDDHHMMSS';
    comment on column TB_BAS_META_YGYX.imagingstoptime
      is '该景各波段结束採集时间   YYYYMMDDHHMMSS';
    comment on column TB_BAS_META_YGYX.satoffnadir
      is '卫星側摆角度    -90~+90度';
    comment on column TB_BAS_META_YGYX.cloudamount
      is '云盖量   ';
    comment on column TB_BAS_META_YGYX.wkt
      is '坐标系PROJCS["Transverse Mercator",GEOGCS["China2000",DATUM["China2000",SPHEROID["GRS80",6378137,298.257222101],TOWGS84[0, 0, 0,0,0,0,0]],PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433]],
        //UNIT["meters",1],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",75],PARAMETER["scale_factor",1],PARAMETER["false_easting",500000],PARAMETER["false_northing",0]]';
    comment on column TB_BAS_META_YGYX.scenecenterlat
      is '景中心纬度 -90~+90度';
    comment on column TB_BAS_META_YGYX.scenecenterlong
      is '景中心经度 -180~+180度';
    comment on column TB_BAS_META_YGYX.dataupperleftlat
      is '图像左上角纬度   -90~+90度';
    comment on column TB_BAS_META_YGYX.dataupperleftlong
      is '图像左上角经度   -180~+180度';
    comment on column TB_BAS_META_YGYX.dataupperrightlat
      is '图像右上角纬度   -90~+90度';
    comment on column TB_BAS_META_YGYX.dataupperrightlong
      is '图像右上角经度   -180~+180度';
    comment on column TB_BAS_META_YGYX.datalowerleftlat
      is '图像左下角纬度   -90~+90度';
    comment on column TB_BAS_META_YGYX.datalowerleftlong
      is '图像左下角经度   -180~+180度';
    comment on column TB_BAS_META_YGYX.datalowerrightlat
      is '图像右下角纬度   -90~+90度';
    comment on column TB_BAS_META_YGYX.datalowerrightlong
      is '图像右下角经度   -180~+180度';
    comment on column TB_BAS_META_YGYX.productupperleftlat
      is '产品左上角纬度   -90~+90度';
    comment on column TB_BAS_META_YGYX.productupperleftlong
      is '产品左上经度    -180~+180度';
    comment on column TB_BAS_META_YGYX.productupperrightlat
      is '产品右上角纬度   -90~+90度';
    comment on column TB_BAS_META_YGYX.productupperrightlong
      is '产品右上角经度   -180~+180度';
    comment on column TB_BAS_META_YGYX.productlowerleftlat
      is '产品左下角纬度   -90~+90度';
    comment on column TB_BAS_META_YGYX.productlowerleftlong
      is '产品左下角经度   -180~+180度';
    comment on column TB_BAS_META_YGYX.productlowerrightlat
      is '产品右下角纬度   -90~+90度';
    comment on column TB_BAS_META_YGYX.productlowerrightlong
      is '产品右下角经度   -180~+180度';
    comment on column TB_BAS_META_YGYX.scenepath
      is '星下点PATH   1-457';
    comment on column TB_BAS_META_YGYX.scenerow
      is '星下点ROW    1~480';
    comment on column TB_BAS_META_YGYX.f_datatypename
      is '产品类型';
    comment on column TB_BAS_META_YGYX.f_mapprojection
      is '地图投影';
    comment on column TB_BAS_META_YGYX.id
      is 'guid';
    comment on column TB_BAS_META_YGYX.cloud_amount
      is '判别后云量';
    comment on column TB_BAS_META_YGYX.receive_time
      is '接受时间(採集时间都用这个)';
    comment on column TB_BAS_META_YGYX.available
      is '0 、2 不可用 1 是可用(BOLB)-1 未判定';
    -- Create/Recreate indexes 
    create index INDEX_TB_BAS_META_YGYX_DATAID on TB_BAS_META_YGYX (DATAID)
      tablespace GF_GXFW
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
        initial 17M
        minextents 1
        maxextents unlimited
      );
    

    2、TB_BAS_HEADTABLE
    TB_BAS_HEADTABLE

    sql:
    -- Create table
    create table TB_BAS_HEADTABLE
    (
      f_dataid               NUMBER(10) not null,
      f_productname          VARCHAR2(255),
      f_productid            NUMBER(10),
      f_productdate          DATE,
      f_geographicidentifier VARCHAR2(64),
      f_dataformatdes        VARCHAR2(50),
      f_productunit          VARCHAR2(255),
      f_importuser           VARCHAR2(50),
      f_importdate           DATE,
      f_importway            VARCHAR2(20),
      f_datasource           VARCHAR2(255),
      f_getdate              DATE,
      f_datasize             NUMBER(10),
      f_dataunit             VARCHAR2(50),
      f_datadesc             VARCHAR2(255),
      f_keyword              VARCHAR2(100),
      f_isfile               NUMBER(4),
      f_location             VARCHAR2(255),
      f_flag                 NUMBER(4),
      f_hassdelayer          NUMBER(4),
      f_hastable             NUMBER(4),
      f_shape                MDSYS.SDO_GEOMETRY,
      f_catalogcode          VARCHAR2(100),
      f_filelist             VARCHAR2(2000),
      f_dirid                NUMBER(10),
      f_release              NUMBER(2),
      f_title                VARCHAR2(255),
      f_ismark               NUMBER,
      id                     VARCHAR2(40)
    )
    tablespace GF_GXFW
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 104M
        minextents 1
        maxextents unlimited
      );
    -- Create/Recreate indexes 
    create index INDEX_HEADTABLEDATA_DATAID on TB_BAS_HEADTABLE (F_DATAID)
      tablespace GF_GXFW
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
        initial 17M
        minextents 1
        maxextents unlimited
      );
    

    3、TB_BAS_YXZZCP
    TB_BAS_YXZZCP

    sql:
    -- Create table
    create table TB_BAS_YXZZCP
    (
      id                     VARCHAR2(40),
      source_data_id         NUMBER(38),
      image_no               VARCHAR2(50),
      image_handling_no      VARCHAR2(40),
      satellite_code         VARCHAR2(10),
      sensor_types           VARCHAR2(10),
      image_rr_value         NUMBER(38,8),
      image_color_values     VARCHAR2(4),
      image_bands            NUMBER(38),
      image_pixelbits        NUMBER(38),
      width_pixels           NUMBER(38),
      height_pixels          NUMBER(38),
      cs_code                VARCHAR2(4),
      projection_desc        VARCHAR2(500),
      resample_method        VARCHAR2(4),
      tl_lon                 NUMBER(38,8),
      tl_lat                 NUMBER(38,8),
      tr_lon                 NUMBER(38,8),
      tr_lat                 NUMBER(38,8),
      br_lon                 NUMBER(38,8),
      br_lat                 NUMBER(38,8),
      bl_lon                 NUMBER(38,8),
      bl_lat                 NUMBER(38,8),
      tl_x                   NUMBER(38,8),
      tl_y                   NUMBER(38,8),
      tr_x                   NUMBER(38,8),
      tr_y                   NUMBER(38,8),
      br_x                   NUMBER(38,8),
      br_y                   NUMBER(38,8),
      bl_x                   NUMBER(38,8),
      bl_y                   NUMBER(38,8),
      product_ar_code        VARCHAR2(10),
      product_category_code  VARCHAR2(20),
      product_ownership_unit VARCHAR2(50),
      production_unit        VARCHAR2(50),
      product_grade_code     VARCHAR2(4),
      product_quality        VARCHAR2(4),
      product_security_code  VARCHAR2(4),
      product_format_code    VARCHAR2(4),
      product_archive_date   TIMESTAMP(6),
      production_date        TIMESTAMP(6),
      product_size_measure   VARCHAR2(4),
      product_size           NUMBER(38),
      product_quality_report VARCHAR2(500),
      product_order_no       VARCHAR2(40),
      product_storage_path   VARCHAR2(500),
      create_date            TIMESTAMP(6),
      used_count             NUMBER(38),
      status                 VARCHAR2(4),
      remarks                VARCHAR2(400),
      product_file_list      BLOB,
      envelope               LONG,
      source_table_name      VARCHAR2(100),
      image_frame_code       VARCHAR2(10),
      data_name              VARCHAR2(400)
    )
    tablespace HY_02C
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 3M
        minextents 1
        maxextents unlimited
      );
    -- Add comments to the columns 
    comment on column TB_BAS_YXZZCP.id
      is '记录编号';
    comment on column TB_BAS_YXZZCP.source_data_id
      is '源数据ID';
    comment on column TB_BAS_YXZZCP.image_no
      is '图幅号';
    comment on column TB_BAS_YXZZCP.image_handling_no
      is '图处理关联编号';
    comment on column TB_BAS_YXZZCP.satellite_code
      is '卫星类别';
    comment on column TB_BAS_YXZZCP.sensor_types
      is '传感器类型';
    comment on column TB_BAS_YXZZCP.image_rr_value
      is '影像分辨率';
    comment on column TB_BAS_YXZZCP.image_color_values
      is '影像色彩模式';
    comment on column TB_BAS_YXZZCP.image_bands
      is '影像波段数';
    comment on column TB_BAS_YXZZCP.image_pixelbits
      is '像素位数';
    comment on column TB_BAS_YXZZCP.width_pixels
      is '行像素数';
    comment on column TB_BAS_YXZZCP.height_pixels
      is '列像素数';
    comment on column TB_BAS_YXZZCP.cs_code
      is '坐标系';
    comment on column TB_BAS_YXZZCP.projection_desc
      is '投影信息描写叙述';
    comment on column TB_BAS_YXZZCP.resample_method
      is '重採样方法';
    comment on column TB_BAS_YXZZCP.tl_lon
      is '左上经度';
    comment on column TB_BAS_YXZZCP.tl_lat
      is '左上纬度';
    comment on column TB_BAS_YXZZCP.tr_lon
      is '右上经度';
    comment on column TB_BAS_YXZZCP.tr_lat
      is '右上纬度';
    comment on column TB_BAS_YXZZCP.br_lon
      is '右下经度';
    comment on column TB_BAS_YXZZCP.br_lat
      is '右下纬度';
    comment on column TB_BAS_YXZZCP.bl_lon
      is '左下经度';
    comment on column TB_BAS_YXZZCP.bl_lat
      is '左下纬度';
    comment on column TB_BAS_YXZZCP.tl_x
      is '左上x坐标';
    comment on column TB_BAS_YXZZCP.tl_y
      is '左上y坐标';
    comment on column TB_BAS_YXZZCP.tr_x
      is '右上x坐标';
    comment on column TB_BAS_YXZZCP.tr_y
      is '右上y坐标';
    comment on column TB_BAS_YXZZCP.br_x
      is '右下x坐标';
    comment on column TB_BAS_YXZZCP.br_y
      is '右下y坐标';
    comment on column TB_BAS_YXZZCP.bl_x
      is '左下x坐标';
    comment on column TB_BAS_YXZZCP.bl_y
      is '左下y坐标';
    comment on column TB_BAS_YXZZCP.product_ar_code
      is '行政区代码';
    comment on column TB_BAS_YXZZCP.product_category_code
      is '产品分类';
    comment on column TB_BAS_YXZZCP.product_ownership_unit
      is '产品全部权单位';
    comment on column TB_BAS_YXZZCP.production_unit
      is '产品生产单位';
    comment on column TB_BAS_YXZZCP.product_grade_code
      is '产品等级';
    comment on column TB_BAS_YXZZCP.product_quality
      is '产品质检';
    comment on column TB_BAS_YXZZCP.product_security_code
      is '产品秘密等级';
    comment on column TB_BAS_YXZZCP.product_format_code
      is '产品行式';
    comment on column TB_BAS_YXZZCP.product_archive_date
      is '产品归档日期';
    comment on column TB_BAS_YXZZCP.production_date
      is '产品生产时间';
    comment on column TB_BAS_YXZZCP.product_size_measure
      is '产品大小单位';
    comment on column TB_BAS_YXZZCP.product_size
      is '产品大小';
    comment on column TB_BAS_YXZZCP.product_quality_report
      is '产品质量报告';
    comment on column TB_BAS_YXZZCP.product_order_no
      is '产品任务单编号';
    comment on column TB_BAS_YXZZCP.product_storage_path
      is '产品存储位置';
    comment on column TB_BAS_YXZZCP.create_date
      is '记录创建时间';
    comment on column TB_BAS_YXZZCP.used_count
      is '使用次数';
    comment on column TB_BAS_YXZZCP.status
      is '记录状态';
    comment on column TB_BAS_YXZZCP.remarks
      is '备注';
    comment on column TB_BAS_YXZZCP.product_file_list
      is '产品文件清单';
    comment on column TB_BAS_YXZZCP.envelope
      is '产品名称';
    comment on column TB_BAS_YXZZCP.source_table_name
      is '来源表名';
    comment on column TB_BAS_YXZZCP.image_frame_code
      is '影像分幅类别';
    comment on column TB_BAS_YXZZCP.data_name
      is '数据范围';
    

    步骤三、创建视图:
    用户:HY_02C(表TB_BAS_CKYX与TB_BAS_META_BLOB)
    用户:DOPD(TB_BAS_META_YGYX、TB_BAS_HEADTABLE、TB_BAS_YXZZCP)
    在用户DOPD中创建视图:
    1、首先得给DOPD授予HY_02C用户下表的查询权限:

    PL/SQL登陆HY_02C用户,给DOPD用户授权
    grant select any table to DOPD;

    2、创建视图

    sql:
    create or replace view view_same_ins_multi_user
    (dataid, satellite, sensor, cloudcover_amount, upper_leftlong, upper_leftlat, upper_rightlong, upper_rightlat, lower_rightlong, lower_rightlat, lower_leftlong, lower_leftlat, receive_date, product_level, datasize, productname, tb_flag)
    as
    select
      DATAID fid,
      SATELLITEID,
      SENSORID,
      CLOUD_AMOUNT,
      DATAUPPERLEFTLONG,
      DATAUPPERLEFTLAT,
      DATAUPPERRIGHTLONG,
      DATAUPPERRIGHTLAT,
      DATALOWERRIGHTLONG,
      DATALOWERRIGHTLAT,
      DATALOWERLEFTLONG,
      DATALOWERLEFTLAT,
      RECEIVE_TIME,
      PRODUCTLEVEL,
      F_DATASIZE,
      F_PRODUCTNAME,
      'tb_bas_meta_ygyx' tb_flag
    from DOPD.TB_BAS_META_YGYX ygyx,DOPD.TB_BAS_HEADTABLE head
    where ygyx.dataid = head.f_dataid and rownum<=1000
    union all
    select
      SOURCE_DATA_ID fid ,
      SATELLITE_CODE,
      SENSOR_CODE,
      CLOUDCOVER_CODE,
      TL_LON,
      TL_LAT,
      TR_LON,
      TR_LAT,
      BR_LON,
      BR_LAT,
      BL_LON,
      BL_LAT,
      SJSJ,
      PRODUCT_GRADE_CODE,
      PRODUCT_SIZE,
      DATA_NAME,
      'tb_bas_ckyx' tb_flag
    from HY_02C.TB_BAS_CKYX
    union all
    select
      SOURCE_DATA_ID,
      SATELLITE_CODE,
      SENSOR_TYPES,
      0 CLOUDCOVER_AMOUNT,
      TL_LON,
      TL_LAT,
      TR_LON,
      TR_LAT,
      BR_LON,
      BR_LAT,
      BL_LON,
      BL_LAT,
      to_date('','yyyy/MM/dd hh24:mi:ss')  RECEIVE_TIME,
      '0'  PRODUCTLEVEL,
      PRODUCT_SIZE,
      DATA_NAME,
      'tb_bas_yxzzcp' tb_flag
    from DOPD.TB_BAS_YXZZCP
    order by fid desc;
    

    上面的基础准备工作完毕之后,上代码:
    公司框架採用SSH
    1、用hibernate完毕ORM映射
    參考资料: 连接
    2、spring整合
    3、Java后台代码实现及測试结果

    1、在hibernate.properties中配置两个用户
    hibernate.properties中配置两个用户
    “ 1、在hibernate.properties中配置两个用户”有误,请忽略。!

    !!(不删除是为了追溯历史版本号,同一时候留个记号以防下次出错,
    PS:假设须要实现:不同数据库下不同用户。或者不同数据库类型下不同用户才须要在hibernate.properties中配置多个用户.
    1、仅仅需在hibernate.properties中配置一个用户
    hibernate.properties中配置一个用户
    PS:下面在applicationContext-Hibernate.xml加入的“数据源配置”以及“SessionFactory”配置都仅仅须要一个即可!。(假设须要实现:不同数据库下不同用户。或者不同数据库类型下不同用户才须要加入多个数据源的配置项以及多个SessionFactory配置项)

    2、spring整合配置,在applicationContext-Hibernate.xml加入

    1)配置数据源DataSource,加入例如以下代码:

    这里使用的是DBCP连接池。

    <!-- 数据源 -->
        <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
            <property name="url">
                <value>${jdbc.url}</value>
            </property>
            <property name="username">
                <value>${jdbc.username}</value>
            </property>
            <property name="password">
                <value>${jdbc.password}</value>
            </property>
    
            <!-- 数据库驱动类 -->
            <property name="driverClassName">
                <value>${jdbc.driverClassName}</value>
            </property>
            <!-- 连接池的最大数据库连接数。设为0表示无限制。 -->
            <property name="maxActive">
                <value>${maxActive}</value>
            </property>
            <!-- 最大的空暇连接数。这里取值为30,表示即使没有数据库连接时依旧能够保持30个空暇的连接。而不被清除。随时处于待命状态。设为0表示无限制。-->
            <property name="maxIdle">
                <value>${maxIdle}</value>
            </property>
            <!-- 最大建立连接等待时间(毫秒)。假设超过此时间将接到异常。设为-1表示无限制-->
            <property name="maxWait">
                <value>${maxWait}</value>
            </property>
            <!--指定数据库的默认自己主动提交-->
            <property name="defaultAutoCommit">
                <value>${defaultAutoCommit}</value>
            </property>
            <!--是否自己主动回收超时连接-->
            <property name="removeAbandoned">
                <value>${removeAbandoned}</value>
            </property>
            <!--超时时间(以秒数为单位)-->
            <property name="removeAbandonedTimeout">
                <value>${removeAbandonedTimeout}</value>
            </property>
            <property name="logAbandoned">
                <value>${logAbandoned}</value>
            </property>
        </bean>
    
        <!--配置第二个数据源(同一个数据库同一个实例下,不同用户)  -->
        <bean id="dataSource_sec" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
            <property name="url">
                <value>${jdbc.url_sec}</value>
            </property>
            <property name="username">
                <value>${jdbc.username_sec}</value>
            </property>
            <property name="password">
                <value>${jdbc.password_sec}</value>
            </property>
            <!-- 数据库驱动类 -->
            <property name="driverClassName">
                <value>${jdbc.driverClassName_sec}</value>
            </property>
            <!-- 连接池的最大数据库连接数。设为0表示无限制。

    -->

    <property name="maxActive"> <value>${maxActive}</value> </property> <!-- 最大的空暇连接数,这里取值为30。表示即使没有数据库连接时依旧能够保持30个空暇的连接,而不被清除。随时处于待命状态。设为0表示无限制。

    -->

    <property name="maxIdle"> <value>${maxIdle}</value> </property> <!-- 最大建立连接等待时间(毫秒)。

    假设超过此时间将接到异常。设为-1表示无限制-->

    <property name="maxWait"> <value>${maxWait}</value> </property> <!--指定数据库的默认自己主动提交--> <property name="defaultAutoCommit"> <value>${defaultAutoCommit}</value> </property> <!--是否自己主动回收超时连接--> <property name="removeAbandoned"> <value>${removeAbandoned}</value> </property> <!--超时时间(以秒数为单位)--> <property name="removeAbandonedTimeout"> <value>${removeAbandonedTimeout}</value> </property> <property name="logAbandoned"> <value>${logAbandoned}</value> </property> </bean>

    注:id不能反复,所以后面的使用了dataSource_sec和dataSource以差别,这样才干区分使用的是那一个数据库。假设上面的driverClassName配置一样。你能够不用配置jdbc_sec.driverClassName,都用jdbc.driverClassName即可。

    3、配置sessionFactory

     <!-- sessionFactory1-->  
       <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">  
           <property name="dataSource">  
               <ref local="dataSource"/>  
           </property>  
           <!-- 处理CLOB对象 -->  
           <property name="lobHandler" ref="lobHandler" />  
           <property name="mappingDirectoryLocations">  
               <list>  
                   <value>classpath:org/hdht/</value>  
               </list>  
           </property>  
           <property name="hibernateProperties">  
               <props>  
                   <prop key="hibernate.dialect">${hibernate.dialect}</prop>  
                   <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>  
                   <prop key="hibernate.format_sql">${hibernate.format_sql}</prop>   
                   <prop key="hibernate.jdbc.batch_size">${hibernate.jdbc.batch_size}</prop>  
                   <prop key="hibernate.cache.use_second_level_cache">${hibernate.cache.use_second_level_cache}</prop>  
                   <prop key="hibernate.cache.provider_class">${hibernate.cache.provider_class}</prop>  
                   <prop key="Hibernate.cache.use_query_cache">${Hibernate.cache.use_query_cache}</prop>  
                   <prop key="Hibernate.cache.query_cache_factory">${Hibernate.cache.query_cache_factory}</prop>  
               </props>  
           </property>  
       </bean>
    
       <!-- sessionFactory2-->  
       <bean id="sessionFactory_sec" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">  
           <property name="dataSource">  
               <ref local="dataSource_sec"/>  
           </property>  
           <!-- 处理CLOB对象 -->  
           <property name="lobHandler" ref="lobHandler" />  
           <property name="mappingDirectoryLocations">  
               <list>  
                   <value>classpath:org/hdht/</value>  
               </list>  
           </property>  
           <property name="hibernateProperties">  
               <props>  
                   <prop key="hibernate.dialect">${hibernate.dialect}</prop>  
                   <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>  
                   <prop key="hibernate.format_sql">${hibernate.format_sql}</prop>   
                   <prop key="hibernate.jdbc.batch_size">${hibernate.jdbc.batch_size}</prop>  
                   <prop key="hibernate.cache.use_second_level_cache">${hibernate.cache.use_second_level_cache}</prop>  
                   <prop key="hibernate.cache.provider_class">${hibernate.cache.provider_class}</prop>  
                   <prop key="Hibernate.cache.use_query_cache">${Hibernate.cache.use_query_cache}</prop>  
                   <prop key="Hibernate.cache.query_cache_factory">${Hibernate.cache.query_cache_factory}</prop>  
               </props>  
           </property>  
       </bean>

    3、Java后台代码实现及測试结果

    1)实体类Bean: TestSameInsMultiUserView

    package org.hdht.business.test.bean;
    
    /**
     * Station.java
     * 
     * 监測站实体类
     * 
     * @author 付建波
     */
    
    import java.io.Serializable;
    import java.util.Date;
    
    public class TestSameInsMultiUserView  implements Serializable{
    
        private static final long serialVersionUID = 2985240370584424720L;
    
        private Long dataid;
        private java.lang.String satellite;//卫星
        private java.lang.String sensor;//传感器
        private Long cloudCoverAmount ;//云覆盖量    
        private Float upperLeftLat ;//图像左上角纬度   -90~+90度
        private Float upperLeftLong ;//图像左上角经度  -180~+180度
        private Float upperRightLat ;//图像右上角纬度  -90~+90度
        private Float upperRightLong ;//图像右上角经度 -180~+180度
        private Float lowerLeftLat ;//图像左下角纬度   -90~+90度
        private Float lowerLeftLong ;//图像左下角经度  -180~+180度
        private Float lowerRightLat ;//图像右下角纬度  -90~+90度
        private Float lowerRightLong ;//图像右下角经度 -180~+180度
        private java.util.Date receiveDate;//接收日期
        private String productLevel ;//产品级别 SC GEC eGEC GTC DOM
        private Long dataSize; //数据大小
        private String productName; //产品名称
        private java.lang.String tb_flag; //表标识
        public Long getDataid() {
            return dataid;
        }
        public void setDataid(Long dataid) {
            this.dataid = dataid;
        }
        public java.lang.String getSatellite() {
            return satellite;
        }
        public void setSatellite(java.lang.String satellite) {
            this.satellite = satellite;
        }
        public java.lang.String getSensor() {
            return sensor;
        }
        public void setSensor(java.lang.String sensor) {
            this.sensor = sensor;
        }
        public Long getCloudCoverAmount() {
            return cloudCoverAmount;
        }
        public void setCloudCoverAmount(Long cloudCoverAmount) {
            this.cloudCoverAmount = cloudCoverAmount;
        }
        public Float getUpperLeftLat() {
            return upperLeftLat;
        }
        public void setUpperLeftLat(Float upperLeftLat) {
            this.upperLeftLat = upperLeftLat;
        }
        public Float getUpperLeftLong() {
            return upperLeftLong;
        }
        public void setUpperLeftLong(Float upperLeftLong) {
            this.upperLeftLong = upperLeftLong;
        }
        public Float getUpperRightLat() {
            return upperRightLat;
        }
        public void setUpperRightLat(Float upperRightLat) {
            this.upperRightLat = upperRightLat;
        }
        public Float getUpperRightLong() {
            return upperRightLong;
        }
        public void setUpperRightLong(Float upperRightLong) {
            this.upperRightLong = upperRightLong;
        }
        public Float getLowerLeftLat() {
            return lowerLeftLat;
        }
        public void setLowerLeftLat(Float lowerLeftLat) {
            this.lowerLeftLat = lowerLeftLat;
        }
        public Float getLowerLeftLong() {
            return lowerLeftLong;
        }
        public void setLowerLeftLong(Float lowerLeftLong) {
            this.lowerLeftLong = lowerLeftLong;
        }
        public Float getLowerRightLat() {
            return lowerRightLat;
        }
        public void setLowerRightLat(Float lowerRightLat) {
            this.lowerRightLat = lowerRightLat;
        }
        public Float getLowerRightLong() {
            return lowerRightLong;
        }
        public void setLowerRightLong(Float lowerRightLong) {
            this.lowerRightLong = lowerRightLong;
        }
        public java.util.Date getReceiveDate() {
            return receiveDate;
        }
        public void setReceiveDate(java.util.Date receiveDate) {
            this.receiveDate = receiveDate;
        }
        public String getProductLevel() {
            return productLevel;
        }
        public void setProductLevel(String productLevel) {
            this.productLevel = productLevel;
        }
        public Long getDataSize() {
            return dataSize;
        }
        public void setDataSize(Long dataSize) {
            this.dataSize = dataSize;
        }
        public String getProductName() {
            return productName;
        }
        public void setProductName(String productName) {
            this.productName = productName;
        }
        public java.lang.String getTb_flag() {
            return tb_flag;
        }
        public void setTb_flag(java.lang.String tb_flag) {
            this.tb_flag = tb_flag;
        }
    
    }
    

    2)Hibernate映射文件: TestSameInsMutilUserView.hbm.xml

    <?

    xml version="1.0" encoding="utf-8"?>

    <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <!-- Mapping file autogenerated by MyEclipse Persistence Tools --> <hibernate-mapping> <class name="org.hdht.business.test.bean.TestSameInsMultiUserView" table="DOPD.view_same_ins_multi_user"> <id name="dataid" type="java.lang.Long"> <column name="DATAID" precision="22" scale="0" /> </id> <property name="satellite" type="java.lang.String"> <column name="satellite" length="50" ></column> </property> <property name="sensor" type="java.lang.String"> <column name="sensor" length="100"></column> </property> <property name="cloudCoverAmount" type="java.lang.Long"> <column name="cloudcover_amount" length="100"></column> </property> <property name="upperLeftLat" type="java.lang.Float"> <column name="upper_LeftLat" length="100"></column> </property> <property name="upperLeftLong" type="java.lang.Float"> <column name="upper_LeftLong" length="100"> </column> </property> <property name="upperRightLat" type="java.lang.Float"> <column name="upper_RightLat" length="100"> </column> </property> <property name="upperRightLong" type="java.lang.Float"> <column name="upper_RightLong" length="100"> </column> </property> <property name="lowerLeftLat" type="java.lang.Float"> <column name="lower_LeftLat" length="100"> </column> </property> <property name="lowerLeftLong" type="java.lang.Float"> <column name="lower_LeftLong" length="100"> </column> </property> <property name="lowerRightLat" type="java.lang.Float"> <column name="lower_RightLat" length="100"> </column> </property> <property name="lowerRightLong" type="java.lang.Float"> <column name="lower_RightLong" length="100"></column> </property> <property name="receiveDate" type="java.util.Date"> <column name="receive_date" length="200"> <comment>接收日期</comment> </column> </property> <property name="productLevel" type="java.lang.String"> <column name="product_level"></column> </property> <property name="dataSize" type="java.lang.Long"> <column name="dataSize"></column> </property> <property name="productName" type="java.lang.String"> <column name="productName"></column> </property> <property name="tb_flag" type="java.lang.String"> <column name="TB_FLAG" length="5"></column> </property> </class> </hibernate-mapping>

    3)数据訪问对象DAO:TestSameInsMultiUserViewDAO

    package org.hdht.business.test.dao;
    
    /**
     * 类名:StationDAO.java
     * 功能:运输资源数据訪问类
     * @author 付建波
     * @version Ver 1.0 2010-01-26 初版
     */
    
    
    import java.io.Serializable;
    import java.sql.SQLException;
    
    import org.apache.commons.logging.Log;
    import org.apache.commons.logging.LogFactory;
    import org.hdht.business.test.bean.TestSameInsMultiUserView;
    import org.hdht.commonweb.basedao.BaseHibDAO;
    import org.hibernate.HibernateException;
    import org.hibernate.Session;
    import org.springframework.orm.hibernate3.HibernateCallback;
    import org.springframework.orm.hibernate3.HibernateTemplate;
    
    public class TestSameInsMultiUserViewDAO extends BaseHibDAO<TestSameInsMultiUserView> implements Serializable {
    
        private static final long serialVersionUID = 1246807624187296626L;
    
        private final static Log log = LogFactory.getLog(TestSameInsMultiUserViewDAO.class);
    
        /**
         * 构造方法
         */
        public TestSameInsMultiUserViewDAO() {
            super(TestSameInsMultiUserView.class, log, "id");
        }
    
           /**
         * 通过dataid取出元数据
         * @param dataid
         * @return
         */
        public TestSameInsMultiUserView findById(final java.lang.Long dataid) {
            log.info("getting TestSameInsMultiUserView instance with id: " + dataid);
            try {
                HibernateTemplate ht = getHibernateTemplate();
                return (TestSameInsMultiUserView)ht.execute(new HibernateCallback(){
                    public Object doInHibernate(Session session) throws HibernateException, SQLException {
                        TestSameInsMultiUserView testSameInsMultiUserView = (TestSameInsMultiUserView) session.get(TestSameInsMultiUserView.class, dataid);
                        return testSameInsMultiUserView;
                    }
                });
            } catch (RuntimeException re) {
                log.error("get failed", re);
                throw re;
            }
        }
    
    }
    

    4)业务逻辑层Logic:TestSameInsMultiUserViewLogic

    package org.hdht.business.test.logic;
    
    /**
     * 类名:StationLogic.java
     * 功能:运输资源业务逻辑类
     * @author 付建波
     * @version Ver 1.0 2010-01-26 初版
     */
    
    
    import java.io.Serializable;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import org.hdht.business.test.bean.TestBlobView;
    import org.hdht.business.test.bean.TestSameInsMultiUserView;
    import org.hdht.business.test.dao.TestBlobViewDAO;
    import org.hdht.business.test.dao.TestSameInsMultiUserViewDAO;
    import org.hdht.commonweb.baselogic.GridLogic;
    import org.hdht.commonweb.baselogic.GridSearchBean;
    import org.hdht.map.spatial.bean.ParaProductQuery;
    import org.hdht.map.spatial.logic.SpatialQuery;
    
    public class TestSameInsMultiUserViewLogic extends GridLogic implements Serializable{
        private static final long serialVersionUID = 7424950578617734859L;
        private SpatialQuery spatialQuery;
        /**运输资源数据訪问对象*/
        private TestSameInsMultiUserViewDAO testSameInsMultiUserViewDAO;
    
    
        public TestSameInsMultiUserViewDAO getTestSameInsMultiUserViewDAO() {
            return testSameInsMultiUserViewDAO;
        }
    
    
        public void setTestSameInsMultiUserViewDAO(
                TestSameInsMultiUserViewDAO testSameInsMultiUserViewDAO) {
            this.testSameInsMultiUserViewDAO = testSameInsMultiUserViewDAO;
        }
    
        private TestBlobViewDAO testBlobViewDAO;
    
        public TestBlobViewDAO getTestBlobViewDAO() {
            return testBlobViewDAO;
        }
    
    
        public void setTestBlobViewDAO(TestBlobViewDAO testBlobViewDAO) {
            this.testBlobViewDAO = testBlobViewDAO;
        }
    
        public TestBlobView getTestBlobViewById(Long id){
            TestBlobView testBlobView = new TestBlobView();
            testBlobView = (TestBlobView)this.testBlobViewDAO.get(id);
            return testBlobView;
        }
    
        /***
         * 查询基本卫星信息视图中列表记录
         * 
         * @param currentPage    当前页码
         * @param pageSize    页面最大行数
         * @param map  查询条件键值对 key -> String, value -> Object
         * @return
         */
        @SuppressWarnings("unchecked")
        private List listSpital = new ArrayList(); 
        private List metaList = new ArrayList();
        public Map listTestSameInsMultiUserView(int currentPage, int pageSize, Map map) {
            StringBuffer dataHQL = new StringBuffer("from TestSameInsMultiUserView s ");
            StringBuffer countHQL =  new StringBuffer("select count(*) from TestSameInsMultiUserView s ");
            Map<String,Object> smap = new HashMap<String,Object>();
            String sat = null!=map.get("sat")?map.get("sat").toString(): "";
            String sensor = null!=map.get("sensor")?map.get("sensor").toString():"";;
            if(!"".equals(sat)&&!"".equals(sensor)){
                smap.put("s.sat", sat);
                smap.put("s.sensor",sensor);
            }
            GridSearchBean gsb = initGridSearchBean(smap);
            String whereSql = gsb.getWhereSql().toString();
            String starttime = null!=map.get("starttime")?map.get("starttime").toString():"";
            String endttime = null!=map.get("endttime")?

    map.get("endttime").toString():""; String geometryType = null!=map.get("geometryType")?map.get("geometryType").toString():"";//空间查询类型 boolean issearch = null!=map.get("issearch")&&map.get("issearch").toString().equals("false")?false:true; if(!"".equals(starttime)) { whereSql += " and receive_date >= to_date('"+starttime+"','yyyy-MM-dd HH24:mi:ss')"; } if(!"".equals(endttime)) { whereSql += " and receive_date <= to_date('"+endttime+"','yyyy-MM-dd HH24:mi:ss')"; } dataHQL.append(whereSql); countHQL.append(whereSql); Map m = null; if(!geometryType.equals("")){ if(currentPage==1&&issearch){ listSpital = this.searchSDE(map); //从fileGDB中查询出来的 List<TestSameInsMultiUserView> listProp = this.testSameInsMultiUserViewDAO.findByHQLQuery(dataHQL.toString());//从数据库中查询出来的 //属性查询结果 List newList = new ArrayList(); for (TestSameInsMultiUserView metaImg : listProp) { String dataid = metaImg.getDataid().toString(); newList.add(dataid); } newList.retainAll(listSpital);//取交集,将fileGDB中查询出来的数据以及从数据库中查询出来的取交集 metaList = new ArrayList();//存放取完交集后的数据实体 int size = listProp.size(); for (int i = 0; i < size; i++) { TestSameInsMultiUserView bf = listProp.get(i); if(newList.contains(bf.getDataid().toString())){ metaList.add(bf); } } } m = this.getPageList(metaList, currentPage, pageSize); }else{ m = this.search(dataHQL.toString(), gsb.getArgs(), countHQL.toString(), gsb.getArgs(), currentPage, pageSize); } return m; } //開始分页 public Map getPageList(List list,int currentPage, int pageSize){ int countAll=list.size(); List pageResult=new ArrayList(); for(int i=(currentPage-1)*pageSize;i<currentPage*pageSize;i++){ if(i>=countAll){ break; } pageResult.add(list.get(i)); } Map m = new HashMap(); m.put("list", pageResult); m.put("all", countAll); return m; } /** * 依据Id获取BasicSatInfo对象 * @param id * @return */ public TestSameInsMultiUserView getInstanceById(Long id, String modelName) { try { TestSameInsMultiUserView instance = testSameInsMultiUserViewDAO.findByProperty("id", id); return instance; } catch(Exception ex) { return new TestSameInsMultiUserView(); } } /*** * SDE数据检索 * @param searchConditionMap 检索条件 * @return list = pids * */ public List searchSDE(Map searchConditionMap){ String satelliteIdStr = null!=searchConditionMap.get("satelliteIdStr")?searchConditionMap.get("satelliteIdStr").toString():""; String sensorCodeStr = null!=searchConditionMap.get("sensor_codeStr")?searchConditionMap.get("sensor_codeStr").toString():""; String datatypenameStr = null!=searchConditionMap.get("datatypenameStr")?searchConditionMap.get("datatypenameStr").toString():""; String productLevelStr = null!=searchConditionMap.get("productLevelStr")?searchConditionMap.get("productLevelStr").toString():""; String cloudAmountStr = null!=searchConditionMap.get("cloudAmountStr")?searchConditionMap.get("cloudAmountStr").toString():""; String begintime = null!=searchConditionMap.get("starttime")?searchConditionMap.get("starttime").toString():"";//採集開始时间 String endtime = null!=searchConditionMap.get("endttime")?searchConditionMap.get("endttime").toString():""; //採集開始时间//採集结束时间 String lowerRightLong = null!=searchConditionMap.get("lowerRightLong")?

    searchConditionMap.get("lowerRightLong").toString():""; //经纬度 String upperLeftLong = null!=searchConditionMap.get("upperLeftLong")?searchConditionMap.get("upperLeftLong").toString():""; String lowerRightLat = null!=searchConditionMap.get("lowerRightLat")?searchConditionMap.get("lowerRightLat").toString():""; String upperLeftLat = null!=searchConditionMap.get("upperLeftLat")?searchConditionMap.get("upperLeftLat").toString():""; String geometryType = null!=searchConditionMap.get("geometryType")?searchConditionMap.get("geometryType").toString():""; String distName = null!=searchConditionMap.get("distName")?

    searchConditionMap.get("distName").toString():""; String rings = null!=searchConditionMap.get("rings")?searchConditionMap.get("rings").toString():""; // String searchSpaceFlag = null!=searchConditionMap.get("searchSpaceFlag")?searchConditionMap.get("searchSpaceFlag").toString():""; ParaProductQuery paraProductQuery = new ParaProductQuery(); paraProductQuery.setIsContain("1"); paraProductQuery.setStrSatellite(satelliteIdStr); paraProductQuery.setSensorCodeStr(sensorCodeStr); paraProductQuery.setDatatypename(datatypenameStr); paraProductQuery.setProductLevel(productLevelStr); paraProductQuery.setCloudAmount(cloudAmountStr); paraProductQuery.setSceneStartTime(begintime); paraProductQuery.setSceneEndTime(endtime); paraProductQuery.setLowerRightLong(lowerRightLong); paraProductQuery.setLowerRightLat(lowerRightLat); paraProductQuery.setUpperLeftLong(upperLeftLong); paraProductQuery.setUpperLeftLat(upperLeftLat); paraProductQuery.setGeometryType(geometryType) ; paraProductQuery.setDistName(distName); paraProductQuery.setRings(rings); List pids = this.spatialQuery.querySdePolygon(paraProductQuery); return pids; } public SpatialQuery getSpatialQuery() { return spatialQuery; } public void setSpatialQuery(SpatialQuery spatialQuery) { this.spatialQuery = spatialQuery; } /** * 通过id取出BasicSatInfoView 主要的卫星信息 * * @param dataid * @return */ public TestSameInsMultiUserView getTestSameInsMultiUserViewById(long dataid) { TestSameInsMultiUserView testSameInsMultiUserView = this.testSameInsMultiUserViewDAO.findById(dataid); return testSameInsMultiUserView; } }

    5)Action类:TestSameInsMultiUserViewAction

    /**
     * 类名:TransportAction.java
     * 功能:运输资源控制器类
     * @author 付建波
     * @version Ver 1.0 2010-01-26 初版
     */
    
    package org.hdht.business.test.action;
    
    import java.util.Map;
    
    import org.hdht.business.search.sde.bean.PointBean;
    import org.hdht.business.test.bean.TestSameInsMultiUserView;
    import org.hdht.business.test.logic.TestSameInsMultiUserViewLogic;
    import org.hdht.commonweb.baseservlet.IocServlet;
    import org.hdht.util.character.UtilString;
    
    public class TestSameInsMultiUserViewAction  extends IocServlet{
    
        private static final long serialVersionUID = 1L;
        private String tb_flag;//多源数据表名
        private String source_data_id;//源数据ID
        /**产品显示具体的数据对象*/
        private TestSameInsMultiUserView testSameInsMultiUserView ;
        private TestSameInsMultiUserViewLogic testSameInsMultiUserViewLogic;
        private PointBean pointBean = new PointBean();
        public TestSameInsMultiUserView getTestSameInsMultiUserView() {
            return testSameInsMultiUserView;
        }
        public void setTestSameInsMultiUserView(
                TestSameInsMultiUserView testSameInsMultiUserView) {
            this.testSameInsMultiUserView = testSameInsMultiUserView;
        }
        public TestSameInsMultiUserViewLogic getTestSameInsMultiUserViewLogic() {
            return testSameInsMultiUserViewLogic;
        }
        public void setTestSameInsMultiUserViewLogic(
                TestSameInsMultiUserViewLogic testSameInsMultiUserViewLogic) {
            this.testSameInsMultiUserViewLogic = testSameInsMultiUserViewLogic;
        }
        public String getTb_flag() {
            return tb_flag;
        }
        public void setTb_flag(String tb_flag) {
            this.tb_flag = tb_flag;
        }
        public String getSource_data_id() {
            return source_data_id;
        }
        public void setSource_data_id(String source_data_id) {
            this.source_data_id = source_data_id;
        }
    
        public TestSameInsMultiUserView getTestSameMultiUserView() {
            return testSameInsMultiUserView;
        }
        public void setTestSameMultiUserView(TestSameInsMultiUserView testSameInsMultiUserView) {
            this.testSameInsMultiUserView = testSameInsMultiUserView;
        }
        public PointBean getPointBean() {
            return pointBean;
        }
        public void setPointBean(PointBean pointBean) {
            this.pointBean = pointBean;
        }
        /***
         * 查询基本卫星信息列表记录
         * 
         * @param currentPage    当前页码
         * @param pageSize    页面最大行数
         * @param map    查询条件键值对 key -> String, value -> Object
         * @return
         */
        @SuppressWarnings("unchecked")
        public Map listTestSameInsMultiUserView(int currentPage, int pageSize, Map map) {
             return this.testSameInsMultiUserViewLogic.listTestSameInsMultiUserView(currentPage, pageSize, map);
        }
    
        /**
         * 依据Id获取station对象
         * @param id    检測站主键
         * @return Station 检測站相应
         */
        public TestSameInsMultiUserView getStationById(Long id, String modelName) {
            return this.testSameInsMultiUserViewLogic.getInstanceById(id, modelName);
        }
    
    
        /**
         * 多源数据信息 “详情” 列表展示
         * @param source_data_id 源数据ID
         * @param tablename 所在表
         * */
        @SuppressWarnings("unchecked")
        public String getShowDetail(){
            try {
                if(UtilString.isNotNull(this.tb_flag)&&UtilString.isNotNull(this.source_data_id)){
                    TestSameInsMultiUserView testSameInsMultiUserView = this.testSameInsMultiUserViewLogic.getTestSameInsMultiUserViewById(Long.valueOf(this.source_data_id)) ;
                    this.setTestSameInsMultiUserView(testSameInsMultiUserView);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return SUCCESS;
        }
    
        /**
         * 依据流水号获取影像
         * @param serialid
         * @return
         */
        public String rightLitterMap(){
            return SUCCESS;
        }
    
    
    
    
    }
    

    6、DWR以及Spring配置文件
    TestSameInsMutilUserView.dwr.xml
    TestSameInsMutilUserView.spring.xml

    1)TestSameInsMutilUserView.dwr.xml

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE dwr PUBLIC "-//GetAhead Limited//DTD Direct Web Remoting 3.0//EN" "http://getahead.org/dwr//dwr30.dtd">
    <dwr>
        <allow>
            <create creator="spring" javascript="TestSameInsMultiUserViewAction" scope="application">
                <param name="beanName" value="testSameInsMultiUserViewAction" />
            </create>
            <convert converter="hibernate3" match="org.hdht.business.test.bean.TestSameInsMultiUserView" >
            </convert>
        </allow>
    </dwr>

    2)TestSameInsMutilUserView.spring.xml

    <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
            xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd">
    
        <bean id="testSameInsMultiUserViewAction" class="org.hdht.business.test.action.TestSameInsMultiUserViewAction" scope="prototype" autowire="byName" />
    
        <bean id="testSameInsMultiUserViewLogic" class="org.hdht.business.test.logic.TestSameInsMultiUserViewLogic" autowire="byName" />
    
        <bean id="testSameInsMultiUserViewDAO" class="org.hdht.business.test.dao.TestSameInsMultiUserViewDAO" autowire="byName" />
    
    </beans>   

    7、測试代码:(基于本公司框架)
    測试代码

    TestSameInsMultiUserViewAction testSameInsMultiUserViewAction = (TestSameInsMultiUserViewAction) ApplicationContextHolder.getBean("testSameInsMultiUserViewAction");
            java.util.Map m = testSameInsMultiUserViewAction.listTestSameInsMultiUserView(1, 10, new java.util.HashMap());
            TTool.println(m);

    測试结果

    測试结果

    转载于:https://www.cnblogs.com/gccbuaa/p/7327552.html

    展开全文
  • 同一个数据库实例,不同用户下多表创建视图,Hibernate完成ORM映射,Spring整合,后台实现 1、同一个数据库实例,同用户,多表创建视图 2、同一个数据库实例,不同用户下,多表创建视图 3、同一个数据库,不同数据库...
    • 1、同一个数据库实例,同用户,多表创建视图
    • 2、同一个数据库实例,不同用户下,多表创建视图
    • 3、同一个数据库,不同数据库实例,多表创建视图
    • 4、不同类型数据库,多表创建视图

    • 1、同一个数据库实例,同用户,多表创建视图

      暂缺!!

    • 2、同一个数据库实例,不同用户下,多表创建视图

      步骤一、创建实例,创建两个用户的sql:

      -- 创建实例 : Database Configuration Assistant 创建数据库  
      
      -- 查询表SYSTEM表空间的数据文件的物理路径
      -- SELECT FILE_NAME FROM DBA_DATA_FILES WHERE (TABLESPACE_NAME = 'SYSTEM')
      
      -- 创建门户系统的表空间 TBS_ORCL_TEMPLETE1_0 
      
      -- 遵循表空间命名规范 TBS_Servername_Function_SerialNumber
      -- TBS是必需具备的,表示该对象为表空间。
      -- ServerName是数据库物理服务器的名称或名称简写。
      -- Function是表示该表空间的作用
      
      -- SerialNumber是具有该作用的数据库表空间的序列号
      ---------------------------------------------------------
      
      --设置表空间自动扩容
      CREATE TABLESPACE HY_02C DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TBS_ORCL_DEMO.DBF' size 200M AUTOEXTEND on next 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL ; 
      
      -- 如果存在HY_02C用户 先删除
      -- DROP USER "HY_02C" CASCADE;
      
      -- 创建用户
      CREATE USER HY_02C
          IDENTIFIED BY HY_02C DEFAULT TABLESPACE HY_02C
          QUOTA UNLIMITED 
          ON HY_02C 
          QUOTA 100000 K 
          ON USERS 
          ACCOUNT UNLOCK ;
      
      -- 设置用户权限
      GRANT CONNECT,RESOURCE,DBA TO HY_02C;
      GRANT CREATE SESSION TO HY_02C;
      GRANT CREATE PROCEDURE TO HY_02C;
      
      --导入数据
      $imp file=F:\user09.dmp full=y;
      
      ------测试同一个数据库实例下的不同用户,新建用户
      
      --设置表空间自动扩容
      CREATE TABLESPACE DOPD DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TBS_ORCL_DOPD.DBF' size 200M AUTOEXTEND on next 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL ; 
      --删除表空间
      --DROP TABLESPACE DOPD INCLUDING CONTENTS AND DATAFILES;
      
      -- 如果存在USER_DEMO用户 先删除
      -- DROP USER "DOPD" CASCADE;
      
      -- 创建用户
      CREATE USER DOPD
          IDENTIFIED BY DOPD DEFAULT TABLESPACE TBS_ORCL_DOPD
          QUOTA UNLIMITED 
          ON TBS_ORCL_DEMO 
          QUOTA 100000 K 
          ON USERS 
          ACCOUNT UNLOCK ;
      
      -- 设置用户权限
      GRANT CONNECT,RESOURCE,DBA TO DOPD;
      GRANT CREATE SESSION TO DOPD;
      GRANT CREATE PROCEDURE TO DOPD;
      
      --导入数据
      $imp file=F:\user09.dmp full=y;
      
      
      

      第一个用户:
      第一个用户
      备注:采用的2张表:
      TB_BAS_CKYX
      TB_BAS_META_BLOB

      第二个用户:
      第二个用户
      备注:采用的3张表:
      TB_BAS_YGYX
      TB_BAS_HEADTABLE
      TB_BAS_YXZZCP

    步骤二、数据库表设计:

    用户HY_02C下:
    1、TB_BAS_CKYX
    TB_BAS_CKYX

    sql:
    -- Create table
    create table TB_BAS_CKYX
    (
      id                     VARCHAR2(40) not null,
      source_data_id         NUMBER(38),
      image_no               VARCHAR2(50),
      image_handling_no      VARCHAR2(40),
      satellite_code         VARCHAR2(50),
      sensor_code            VARCHAR2(50),
      image_rr_value         NUMBER(38,8),
      image_scale_code       VARCHAR2(4),
      image_bands            VARCHAR2(40),
      projection_code        VARCHAR2(20),
      central_meridian       NUMBER(38,8),
      locator_unit           VARCHAR2(10),
      zone_code              VARCHAR2(10),
      zone_no                NUMBER(38),
      es_code                VARCHAR2(10),
      es_value               NUMBER(38,8),
      cloudcover_code        NUMBER(38,8),
      tl_lon                 NUMBER(38,8),
      tl_lat                 NUMBER(38,8),
      tr_lon                 NUMBER(38,8),
      tr_lat                 NUMBER(38,8),
      br_lon                 NUMBER(38,8),
      br_lat                 NUMBER(38,8),
      bl_lon                 NUMBER(38,8),
      bl_lat                 NUMBER(38,8),
      tl_x                   NUMBER(38,8),
      tl_y                   NUMBER(38,8),
      tr_x                   NUMBER(38,8),
      tr_y                   NUMBER(38,8),
      br_x                   NUMBER(38,8),
      br_y                   NUMBER(38,8),
      bl_x                   NUMBER(38,8),
      bl_y                   NUMBER(38,8),
      product_ar_code        VARCHAR2(10),
      product_category_code  VARCHAR2(20),
      product_ownership_unit VARCHAR2(50),
      production_unit        VARCHAR2(50),
      product_grade_code     VARCHAR2(4),
      product_quality        VARCHAR2(4),
      product_security_code  VARCHAR2(4),
      product_format_code    VARCHAR2(4),
      product_archive_date   TIMESTAMP(6),
      product_time_phase     DATE,
      product_size_measure   VARCHAR2(4),
      product_size           NUMBER(38),
      product_quality_report VARCHAR2(500),
      product_order_no       VARCHAR2(40),
      product_storage_path   VARCHAR2(500),
      create_date            TIMESTAMP(6),
      used_count             NUMBER(38),
      status                 VARCHAR2(4),
      remarks                VARCHAR2(400),
      product_file_list      BLOB,
      envelope               LONG,
      source_table_name      VARCHAR2(100),
      cs_code                VARCHAR2(10),
      data_name              VARCHAR2(400),
      sjsj                   DATE
    )
    tablespace GF_GXFW
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 128K
        minextents 1
        maxextents unlimited
      );
    -- Add comments to the columns 
    comment on column TB_BAS_CKYX.id
      is '记录编号';
    comment on column TB_BAS_CKYX.source_data_id
      is '源数据ID';
    comment on column TB_BAS_CKYX.image_no
      is '图幅号';
    comment on column TB_BAS_CKYX.image_handling_no
      is '图处理关联编号';
    comment on column TB_BAS_CKYX.satellite_code
      is '卫星类别';
    comment on column TB_BAS_CKYX.sensor_code
      is '传感器类别';
    comment on column TB_BAS_CKYX.image_rr_value
      is '影像分辨率';
    comment on column TB_BAS_CKYX.image_scale_code
      is '影像比例尺';
    comment on column TB_BAS_CKYX.image_bands
      is '影像波段信息';
    comment on column TB_BAS_CKYX.projection_code
      is '投影方式';
    comment on column TB_BAS_CKYX.central_meridian
      is '中央子午线';
    comment on column TB_BAS_CKYX.locator_unit
      is '坐标单位';
    comment on column TB_BAS_CKYX.zone_code
      is '分带类型';
    comment on column TB_BAS_CKYX.zone_no
      is '投影带号';
    comment on column TB_BAS_CKYX.es_code
      is '高程基准';
    comment on column TB_BAS_CKYX.es_value
      is '高程基准值';
    comment on column TB_BAS_CKYX.cloudcover_code
      is '云量';
    comment on column TB_BAS_CKYX.tl_lon
      is '左上经度';
    comment on column TB_BAS_CKYX.tl_lat
      is '左上纬度';
    comment on column TB_BAS_CKYX.tr_lon
      is '右上经度';
    comment on column TB_BAS_CKYX.tr_lat
      is '右上纬度';
    comment on column TB_BAS_CKYX.br_lon
      is '右下经度';
    comment on column TB_BAS_CKYX.br_lat
      is '右下纬度';
    comment on column TB_BAS_CKYX.bl_lon
      is '左下经度';
    comment on column TB_BAS_CKYX.bl_lat
      is '左下纬度';
    comment on column TB_BAS_CKYX.tl_x
      is '左上x坐标';
    comment on column TB_BAS_CKYX.tl_y
      is '左上y坐标';
    comment on column TB_BAS_CKYX.tr_x
      is '右上x坐标';
    comment on column TB_BAS_CKYX.tr_y
      is '右上y坐标';
    comment on column TB_BAS_CKYX.br_x
      is '右下x坐标';
    comment on column TB_BAS_CKYX.br_y
      is '右下y坐标';
    comment on column TB_BAS_CKYX.bl_x
      is '左下x坐标';
    comment on column TB_BAS_CKYX.bl_y
      is '左下y坐标';
    comment on column TB_BAS_CKYX.product_ar_code
      is '行政区';
    comment on column TB_BAS_CKYX.product_category_code
      is '产品分类';
    comment on column TB_BAS_CKYX.product_ownership_unit
      is '产品所有权单位';
    comment on column TB_BAS_CKYX.production_unit
      is '产品生产单位';
    comment on column TB_BAS_CKYX.product_grade_code
      is '产品等级';
    comment on column TB_BAS_CKYX.product_quality
      is '产品质检';
    comment on column TB_BAS_CKYX.product_security_code
      is '产品秘密等级';
    comment on column TB_BAS_CKYX.product_format_code
      is '产品格式';
    comment on column TB_BAS_CKYX.product_archive_date
      is '产品归档日期';
    comment on column TB_BAS_CKYX.product_time_phase
      is '产品时相';
    comment on column TB_BAS_CKYX.product_size_measure
      is '产品大小单位';
    comment on column TB_BAS_CKYX.product_size
      is '产品大小';
    comment on column TB_BAS_CKYX.product_quality_report
      is '产品质量报告';
    comment on column TB_BAS_CKYX.product_order_no
      is '产品任务单编号';
    comment on column TB_BAS_CKYX.product_storage_path
      is '产品存储位置';
    comment on column TB_BAS_CKYX.create_date
      is '记录创建时间';
    comment on column TB_BAS_CKYX.used_count
      is '使用次数';
    comment on column TB_BAS_CKYX.status
      is '记录状态';
    comment on column TB_BAS_CKYX.remarks
      is '备注';
    comment on column TB_BAS_CKYX.product_file_list
      is '产品文件清单';
    comment on column TB_BAS_CKYX.envelope
      is '产品数据时间(2014年5月27日)';
    comment on column TB_BAS_CKYX.source_table_name
      is '来源表名';
    comment on column TB_BAS_CKYX.cs_code
      is '坐标系(2014年5月27日)';
    comment on column TB_BAS_CKYX.data_name
      is '产品名称(2014年5月27日)';
    comment on column TB_BAS_CKYX.sjsj
      is '数据范围';
    -- Create/Recreate primary, unique and foreign key constraints 
    alter table TB_BAS_CKYX
      add constraint PK_CKYX_ID primary key (ID)
      using index 
      tablespace GF_GXFW
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
        initial 64K
        minextents 1
        maxextents unlimited
      );
    -- Grant/Revoke object privileges 
    grant select on TB_BAS_CKYX to DOPD;
    

    2、TB_BAS_META_BLOB
    TB_BAS_META_BLOB

    sql:
    -- Create table
    create table TB_BAS_META_BLOB
    (
      f_dataid      NUMBER not null,
      f_metadata    BLOB,
      f_quickimage1 BLOB,
      f_quickimage2 BLOB,
      f_quickimage3 BLOB,
      f_quickimage4 BLOB,
      f_thumimage   BLOB,
      f_shapeimage  BLOB
    )
    tablespace HY_02C
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 128M
        minextents 1
        maxextents unlimited
      );
    -- Create/Recreate indexes 
    create index INDEX_TB_BAS_META_BLOB_DATAID on TB_BAS_META_BLOB (F_DATAID)
      tablespace HY_02C
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
        initial 10M
        minextents 1
        maxextents unlimited
      );
    

    用户DOPD下:
    1、TB_BAS_META_YGYX
    TB_BAS_META_YGYX

    sql:
    -- Create table
    create table TB_BAS_META_YGYX
    (
      dataid                NUMBER,
      catalogid             VARCHAR2(50),
      satelliteid           VARCHAR2(50),
      sensorid              VARCHAR2(50),
      recstationid          VARCHAR2(50),
      subscenemode          VARCHAR2(100) not null,
      imagingmode           VARCHAR2(50),
      productdate           DATE,
      productlevel          VARCHAR2(100),
      pixelspacing          NUMBER,
      bands                 VARCHAR2(255),
      scenecount            NUMBER,
      overallquality        NUMBER,
      resampletechnique     VARCHAR2(100),
      productorientation    VARCHAR2(100),
      trackid               NUMBER,
      satpath               NUMBER,
      satrow                NUMBER,
      satpathbias           VARCHAR2(100),
      satrowbias            VARCHAR2(100),
      sunelevation          NUMBER,
      sunazimuthelevation   NUMBER not null,
      scenedate             DATE,
      imagingstarttime      VARCHAR2(100),
      imagingstoptime       VARCHAR2(100),
      satoffnadir           NUMBER,
      cloudamount           VARCHAR2(100),
      wkt                   VARCHAR2(255),
      scenecenterlat        NUMBER,
      scenecenterlong       NUMBER,
      dataupperleftlat      NUMBER,
      dataupperleftlong     NUMBER,
      dataupperrightlat     NUMBER,
      dataupperrightlong    NUMBER,
      datalowerleftlat      NUMBER,
      datalowerleftlong     NUMBER,
      datalowerrightlat     NUMBER,
      datalowerrightlong    NUMBER,
      productupperleftlat   NUMBER,
      productupperleftlong  NUMBER,
      productupperrightlat  NUMBER,
      productupperrightlong NUMBER,
      productlowerleftlat   NUMBER,
      productlowerleftlong  NUMBER,
      productlowerrightlat  NUMBER,
      productlowerrightlong NUMBER,
      scenepath             NUMBER,
      scenerow              NUMBER,
      f_datatypename        VARCHAR2(100),
      f_mapprojection       NVARCHAR2(50),
      id                    VARCHAR2(40),
      cloud_amount          NUMBER,
      receive_time          DATE,
      available             VARCHAR2(50)
    )
    tablespace GF_GXFW
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 192M
        minextents 1
        maxextents unlimited
      );
    -- Add comments to the columns 
    comment on column TB_BAS_META_YGYX.dataid
      is '数据唯一标识';
    comment on column TB_BAS_META_YGYX.catalogid
      is '分类编码';
    comment on column TB_BAS_META_YGYX.satelliteid
      is '卫星标识  ZY3';
    comment on column TB_BAS_META_YGYX.sensorid
      is '谱段模式  PAN MS';
    comment on column TB_BAS_META_YGYX.recstationid
      is '接收站标识 MY HS SY OS';
    comment on column TB_BAS_META_YGYX.subscenemode
      is '分景模式  N(注:标准景) d(注:双倍景) t(注:三倍景) s(注: 条带影像)';
    comment on column TB_BAS_META_YGYX.imagingmode
      is '成像模式  N F B L R';
    comment on column TB_BAS_META_YGYX.productdate
      is '生产日期  YYYYMMDDHHMMSS';
    comment on column TB_BAS_META_YGYX.productlevel
      is '产品级别  SC GEC eGEC GTC DOM';
    comment on column TB_BAS_META_YGYX.pixelspacing
      is '空间分辨率(像元间距)   ';
    comment on column TB_BAS_META_YGYX.bands
      is '波段号列表(以逗号分隔)  波段号以逗号隔开。波段取值范围:如,CCD:1,2,3,4,5';
    comment on column TB_BAS_META_YGYX.scenecount
      is '条带景数  ';
    comment on column TB_BAS_META_YGYX.overallquality
      is '质量评价结果    取值范围:0-9';
    comment on column TB_BAS_META_YGYX.resampletechnique
      is '几何处理方式';
    comment on column TB_BAS_META_YGYX.productorientation
      is '辐射处理方式    ';
    comment on column TB_BAS_META_YGYX.trackid
      is '轨道号';
    comment on column TB_BAS_META_YGYX.satpath
      is '星下点PATH   1-457';
    comment on column TB_BAS_META_YGYX.satrow
      is '星下点ROW    1~480';
    comment on column TB_BAS_META_YGYX.satpathbias
      is '星下点PATH偏离值    A、B、C、D、E(由东向西)';
    comment on column TB_BAS_META_YGYX.satrowbias
      is '星下点ROW偏离值 1、2、3、4、5(由北向南)';
    comment on column TB_BAS_META_YGYX.sunelevation
      is '太阳高度角';
    comment on column TB_BAS_META_YGYX.sunazimuthelevation
      is '太阳方位角';
    comment on column TB_BAS_META_YGYX.scenedate
      is '景的日期(图像采集日期)  YYYYMMDDHHMMSS';
    comment on column TB_BAS_META_YGYX.imagingstarttime
      is '该景各波段起始采集时间   YYYYMMDDHHMMSS';
    comment on column TB_BAS_META_YGYX.imagingstoptime
      is '该景各波段结束采集时间   YYYYMMDDHHMMSS';
    comment on column TB_BAS_META_YGYX.satoffnadir
      is '卫星侧摆角度    -90~+90度';
    comment on column TB_BAS_META_YGYX.cloudamount
      is '云盖量   ';
    comment on column TB_BAS_META_YGYX.wkt
      is '坐标系PROJCS["Transverse Mercator",GEOGCS["China2000",DATUM["China2000",SPHEROID["GRS80",6378137,298.257222101],TOWGS84[0, 0, 0,0,0,0,0]],PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433]],
        //UNIT["meters",1],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",75],PARAMETER["scale_factor",1],PARAMETER["false_easting",500000],PARAMETER["false_northing",0]]';
    comment on column TB_BAS_META_YGYX.scenecenterlat
      is '景中心纬度 -90~+90度';
    comment on column TB_BAS_META_YGYX.scenecenterlong
      is '景中心经度 -180~+180度';
    comment on column TB_BAS_META_YGYX.dataupperleftlat
      is '图像左上角纬度   -90~+90度';
    comment on column TB_BAS_META_YGYX.dataupperleftlong
      is '图像左上角经度   -180~+180度';
    comment on column TB_BAS_META_YGYX.dataupperrightlat
      is '图像右上角纬度   -90~+90度';
    comment on column TB_BAS_META_YGYX.dataupperrightlong
      is '图像右上角经度   -180~+180度';
    comment on column TB_BAS_META_YGYX.datalowerleftlat
      is '图像左下角纬度   -90~+90度';
    comment on column TB_BAS_META_YGYX.datalowerleftlong
      is '图像左下角经度   -180~+180度';
    comment on column TB_BAS_META_YGYX.datalowerrightlat
      is '图像右下角纬度   -90~+90度';
    comment on column TB_BAS_META_YGYX.datalowerrightlong
      is '图像右下角经度   -180~+180度';
    comment on column TB_BAS_META_YGYX.productupperleftlat
      is '产品左上角纬度   -90~+90度';
    comment on column TB_BAS_META_YGYX.productupperleftlong
      is '产品左上经度    -180~+180度';
    comment on column TB_BAS_META_YGYX.productupperrightlat
      is '产品右上角纬度   -90~+90度';
    comment on column TB_BAS_META_YGYX.productupperrightlong
      is '产品右上角经度   -180~+180度';
    comment on column TB_BAS_META_YGYX.productlowerleftlat
      is '产品左下角纬度   -90~+90度';
    comment on column TB_BAS_META_YGYX.productlowerleftlong
      is '产品左下角经度   -180~+180度';
    comment on column TB_BAS_META_YGYX.productlowerrightlat
      is '产品右下角纬度   -90~+90度';
    comment on column TB_BAS_META_YGYX.productlowerrightlong
      is '产品右下角经度   -180~+180度';
    comment on column TB_BAS_META_YGYX.scenepath
      is '星下点PATH   1-457';
    comment on column TB_BAS_META_YGYX.scenerow
      is '星下点ROW    1~480';
    comment on column TB_BAS_META_YGYX.f_datatypename
      is '产品类型';
    comment on column TB_BAS_META_YGYX.f_mapprojection
      is '地图投影';
    comment on column TB_BAS_META_YGYX.id
      is 'guid';
    comment on column TB_BAS_META_YGYX.cloud_amount
      is '判别后云量';
    comment on column TB_BAS_META_YGYX.receive_time
      is '接受时间(采集时间都用这个)';
    comment on column TB_BAS_META_YGYX.available
      is '0 、2 不可用 1 是可用(BOLB)-1 未判定';
    -- Create/Recreate indexes 
    create index INDEX_TB_BAS_META_YGYX_DATAID on TB_BAS_META_YGYX (DATAID)
      tablespace GF_GXFW
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
        initial 17M
        minextents 1
        maxextents unlimited
      );
    

    2、TB_BAS_HEADTABLE
    TB_BAS_HEADTABLE

    sql:
    -- Create table
    create table TB_BAS_HEADTABLE
    (
      f_dataid               NUMBER(10) not null,
      f_productname          VARCHAR2(255),
      f_productid            NUMBER(10),
      f_productdate          DATE,
      f_geographicidentifier VARCHAR2(64),
      f_dataformatdes        VARCHAR2(50),
      f_productunit          VARCHAR2(255),
      f_importuser           VARCHAR2(50),
      f_importdate           DATE,
      f_importway            VARCHAR2(20),
      f_datasource           VARCHAR2(255),
      f_getdate              DATE,
      f_datasize             NUMBER(10),
      f_dataunit             VARCHAR2(50),
      f_datadesc             VARCHAR2(255),
      f_keyword              VARCHAR2(100),
      f_isfile               NUMBER(4),
      f_location             VARCHAR2(255),
      f_flag                 NUMBER(4),
      f_hassdelayer          NUMBER(4),
      f_hastable             NUMBER(4),
      f_shape                MDSYS.SDO_GEOMETRY,
      f_catalogcode          VARCHAR2(100),
      f_filelist             VARCHAR2(2000),
      f_dirid                NUMBER(10),
      f_release              NUMBER(2),
      f_title                VARCHAR2(255),
      f_ismark               NUMBER,
      id                     VARCHAR2(40)
    )
    tablespace GF_GXFW
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 104M
        minextents 1
        maxextents unlimited
      );
    -- Create/Recreate indexes 
    create index INDEX_HEADTABLEDATA_DATAID on TB_BAS_HEADTABLE (F_DATAID)
      tablespace GF_GXFW
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
        initial 17M
        minextents 1
        maxextents unlimited
      );
    

    3、TB_BAS_YXZZCP
    TB_BAS_YXZZCP

    sql:
    -- Create table
    create table TB_BAS_YXZZCP
    (
      id                     VARCHAR2(40),
      source_data_id         NUMBER(38),
      image_no               VARCHAR2(50),
      image_handling_no      VARCHAR2(40),
      satellite_code         VARCHAR2(10),
      sensor_types           VARCHAR2(10),
      image_rr_value         NUMBER(38,8),
      image_color_values     VARCHAR2(4),
      image_bands            NUMBER(38),
      image_pixelbits        NUMBER(38),
      width_pixels           NUMBER(38),
      height_pixels          NUMBER(38),
      cs_code                VARCHAR2(4),
      projection_desc        VARCHAR2(500),
      resample_method        VARCHAR2(4),
      tl_lon                 NUMBER(38,8),
      tl_lat                 NUMBER(38,8),
      tr_lon                 NUMBER(38,8),
      tr_lat                 NUMBER(38,8),
      br_lon                 NUMBER(38,8),
      br_lat                 NUMBER(38,8),
      bl_lon                 NUMBER(38,8),
      bl_lat                 NUMBER(38,8),
      tl_x                   NUMBER(38,8),
      tl_y                   NUMBER(38,8),
      tr_x                   NUMBER(38,8),
      tr_y                   NUMBER(38,8),
      br_x                   NUMBER(38,8),
      br_y                   NUMBER(38,8),
      bl_x                   NUMBER(38,8),
      bl_y                   NUMBER(38,8),
      product_ar_code        VARCHAR2(10),
      product_category_code  VARCHAR2(20),
      product_ownership_unit VARCHAR2(50),
      production_unit        VARCHAR2(50),
      product_grade_code     VARCHAR2(4),
      product_quality        VARCHAR2(4),
      product_security_code  VARCHAR2(4),
      product_format_code    VARCHAR2(4),
      product_archive_date   TIMESTAMP(6),
      production_date        TIMESTAMP(6),
      product_size_measure   VARCHAR2(4),
      product_size           NUMBER(38),
      product_quality_report VARCHAR2(500),
      product_order_no       VARCHAR2(40),
      product_storage_path   VARCHAR2(500),
      create_date            TIMESTAMP(6),
      used_count             NUMBER(38),
      status                 VARCHAR2(4),
      remarks                VARCHAR2(400),
      product_file_list      BLOB,
      envelope               LONG,
      source_table_name      VARCHAR2(100),
      image_frame_code       VARCHAR2(10),
      data_name              VARCHAR2(400)
    )
    tablespace HY_02C
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 3M
        minextents 1
        maxextents unlimited
      );
    -- Add comments to the columns 
    comment on column TB_BAS_YXZZCP.id
      is '记录编号';
    comment on column TB_BAS_YXZZCP.source_data_id
      is '源数据ID';
    comment on column TB_BAS_YXZZCP.image_no
      is '图幅号';
    comment on column TB_BAS_YXZZCP.image_handling_no
      is '图处理关联编号';
    comment on column TB_BAS_YXZZCP.satellite_code
      is '卫星类别';
    comment on column TB_BAS_YXZZCP.sensor_types
      is '传感器类型';
    comment on column TB_BAS_YXZZCP.image_rr_value
      is '影像分辨率';
    comment on column TB_BAS_YXZZCP.image_color_values
      is '影像色彩模式';
    comment on column TB_BAS_YXZZCP.image_bands
      is '影像波段数';
    comment on column TB_BAS_YXZZCP.image_pixelbits
      is '像素位数';
    comment on column TB_BAS_YXZZCP.width_pixels
      is '行像素数';
    comment on column TB_BAS_YXZZCP.height_pixels
      is '列像素数';
    comment on column TB_BAS_YXZZCP.cs_code
      is '坐标系';
    comment on column TB_BAS_YXZZCP.projection_desc
      is '投影信息描述';
    comment on column TB_BAS_YXZZCP.resample_method
      is '重采样方法';
    comment on column TB_BAS_YXZZCP.tl_lon
      is '左上经度';
    comment on column TB_BAS_YXZZCP.tl_lat
      is '左上纬度';
    comment on column TB_BAS_YXZZCP.tr_lon
      is '右上经度';
    comment on column TB_BAS_YXZZCP.tr_lat
      is '右上纬度';
    comment on column TB_BAS_YXZZCP.br_lon
      is '右下经度';
    comment on column TB_BAS_YXZZCP.br_lat
      is '右下纬度';
    comment on column TB_BAS_YXZZCP.bl_lon
      is '左下经度';
    comment on column TB_BAS_YXZZCP.bl_lat
      is '左下纬度';
    comment on column TB_BAS_YXZZCP.tl_x
      is '左上x坐标';
    comment on column TB_BAS_YXZZCP.tl_y
      is '左上y坐标';
    comment on column TB_BAS_YXZZCP.tr_x
      is '右上x坐标';
    comment on column TB_BAS_YXZZCP.tr_y
      is '右上y坐标';
    comment on column TB_BAS_YXZZCP.br_x
      is '右下x坐标';
    comment on column TB_BAS_YXZZCP.br_y
      is '右下y坐标';
    comment on column TB_BAS_YXZZCP.bl_x
      is '左下x坐标';
    comment on column TB_BAS_YXZZCP.bl_y
      is '左下y坐标';
    comment on column TB_BAS_YXZZCP.product_ar_code
      is '行政区代码';
    comment on column TB_BAS_YXZZCP.product_category_code
      is '产品分类';
    comment on column TB_BAS_YXZZCP.product_ownership_unit
      is '产品所有权单位';
    comment on column TB_BAS_YXZZCP.production_unit
      is '产品生产单位';
    comment on column TB_BAS_YXZZCP.product_grade_code
      is '产品等级';
    comment on column TB_BAS_YXZZCP.product_quality
      is '产品质检';
    comment on column TB_BAS_YXZZCP.product_security_code
      is '产品秘密等级';
    comment on column TB_BAS_YXZZCP.product_format_code
      is '产品格式';
    comment on column TB_BAS_YXZZCP.product_archive_date
      is '产品归档日期';
    comment on column TB_BAS_YXZZCP.production_date
      is '产品生产时间';
    comment on column TB_BAS_YXZZCP.product_size_measure
      is '产品大小单位';
    comment on column TB_BAS_YXZZCP.product_size
      is '产品大小';
    comment on column TB_BAS_YXZZCP.product_quality_report
      is '产品质量报告';
    comment on column TB_BAS_YXZZCP.product_order_no
      is '产品任务单编号';
    comment on column TB_BAS_YXZZCP.product_storage_path
      is '产品存储位置';
    comment on column TB_BAS_YXZZCP.create_date
      is '记录创建时间';
    comment on column TB_BAS_YXZZCP.used_count
      is '使用次数';
    comment on column TB_BAS_YXZZCP.status
      is '记录状态';
    comment on column TB_BAS_YXZZCP.remarks
      is '备注';
    comment on column TB_BAS_YXZZCP.product_file_list
      is '产品文件清单';
    comment on column TB_BAS_YXZZCP.envelope
      is '产品名称';
    comment on column TB_BAS_YXZZCP.source_table_name
      is '来源表名';
    comment on column TB_BAS_YXZZCP.image_frame_code
      is '影像分幅类别';
    comment on column TB_BAS_YXZZCP.data_name
      is '数据范围';
    

    步骤三、创建视图:
    用户:HY_02C(表TB_BAS_CKYX与TB_BAS_META_BLOB)
    用户:DOPD(TB_BAS_META_YGYX、TB_BAS_HEADTABLE、TB_BAS_YXZZCP)
    在用户DOPD中创建视图:
    1、首先得给DOPD授予HY_02C用户下表的查询权限:

    PL/SQL登陆HY_02C用户,给DOPD用户授权
    grant select any table to DOPD;

    2、创建视图

    sql:
    create or replace view view_same_ins_multi_user
    (dataid, satellite, sensor, cloudcover_amount, upper_leftlong, upper_leftlat, upper_rightlong, upper_rightlat, lower_rightlong, lower_rightlat, lower_leftlong, lower_leftlat, receive_date, product_level, datasize, productname, tb_flag)
    as
    select
      DATAID fid,
      SATELLITEID,
      SENSORID,
      CLOUD_AMOUNT,
      DATAUPPERLEFTLONG,
      DATAUPPERLEFTLAT,
      DATAUPPERRIGHTLONG,
      DATAUPPERRIGHTLAT,
      DATALOWERRIGHTLONG,
      DATALOWERRIGHTLAT,
      DATALOWERLEFTLONG,
      DATALOWERLEFTLAT,
      RECEIVE_TIME,
      PRODUCTLEVEL,
      F_DATASIZE,
      F_PRODUCTNAME,
      'tb_bas_meta_ygyx' tb_flag
    from DOPD.TB_BAS_META_YGYX ygyx,DOPD.TB_BAS_HEADTABLE head
    where ygyx.dataid = head.f_dataid and rownum<=1000
    union all
    select
      SOURCE_DATA_ID fid ,
      SATELLITE_CODE,
      SENSOR_CODE,
      CLOUDCOVER_CODE,
      TL_LON,
      TL_LAT,
      TR_LON,
      TR_LAT,
      BR_LON,
      BR_LAT,
      BL_LON,
      BL_LAT,
      SJSJ,
      PRODUCT_GRADE_CODE,
      PRODUCT_SIZE,
      DATA_NAME,
      'tb_bas_ckyx' tb_flag
    from HY_02C.TB_BAS_CKYX
    union all
    select
      SOURCE_DATA_ID,
      SATELLITE_CODE,
      SENSOR_TYPES,
      0 CLOUDCOVER_AMOUNT,
      TL_LON,
      TL_LAT,
      TR_LON,
      TR_LAT,
      BR_LON,
      BR_LAT,
      BL_LON,
      BL_LAT,
      to_date('','yyyy/MM/dd hh24:mi:ss')  RECEIVE_TIME,
      '0'  PRODUCTLEVEL,
      PRODUCT_SIZE,
      DATA_NAME,
      'tb_bas_yxzzcp' tb_flag
    from DOPD.TB_BAS_YXZZCP
    order by fid desc;
    

    上面的基础准备工作完成之后,上代码:
    公司框架采用SSH
    1、用hibernate完成ORM映射
    参考资料: 连接
    2、spring整合
    3、Java后台代码实现及测试结果

    1、在hibernate.properties中配置两个用户
    hibernate.properties中配置两个用户
    “ 1、在hibernate.properties中配置两个用户”有误,请忽略!!!!(不删除是为了追溯历史版本,同时留个记号以防下次出错,
    PS:如果需要实现:不同数据库下不同用户,或者不同数据库类型下不同用户才需要在hibernate.properties中配置多个用户.
    1、只需在hibernate.properties中配置一个用户
    hibernate.properties中配置一个用户
    PS:以下在applicationContext-Hibernate.xml添加的“数据源配置”以及“SessionFactory”配置都只需要一个就行!!(如果需要实现:不同数据库下不同用户,或者不同数据库类型下不同用户才需要添加多个数据源的配置项以及多个SessionFactory配置项)

    2、spring整合配置,在applicationContext-Hibernate.xml添加

    1)配置数据源DataSource,添加如下代码:

    这里使用的是DBCP连接池。
    
    <!-- 数据源 -->
        <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
            <property name="url">
                <value>${jdbc.url}</value>
            </property>
            <property name="username">
                <value>${jdbc.username}</value>
            </property>
            <property name="password">
                <value>${jdbc.password}</value>
            </property>
    
            <!-- 数据库驱动类 -->
            <property name="driverClassName">
                <value>${jdbc.driverClassName}</value>
            </property>
            <!-- 连接池的最大数据库连接数。设为0表示无限制。 -->
            <property name="maxActive">
                <value>${maxActive}</value>
            </property>
            <!-- 最大的空闲连接数,这里取值为30,表示即使没有数据库连接时依然可以保持30个空闲的连接,而不被清除,随时处于待命状态。设为0表示无限制。-->
            <property name="maxIdle">
                <value>${maxIdle}</value>
            </property>
            <!-- 最大建立连接等待时间(毫秒)。如果超过此时间将接到异常。设为-1表示无限制-->
            <property name="maxWait">
                <value>${maxWait}</value>
            </property>
            <!--指定数据库的默认自动提交-->
            <property name="defaultAutoCommit">
                <value>${defaultAutoCommit}</value>
            </property>
            <!--是否自动回收超时连接-->
            <property name="removeAbandoned">
                <value>${removeAbandoned}</value>
            </property>
            <!--超时时间(以秒数为单位)-->
            <property name="removeAbandonedTimeout">
                <value>${removeAbandonedTimeout}</value>
            </property>
            <property name="logAbandoned">
                <value>${logAbandoned}</value>
            </property>
        </bean>
    
        <!--配置第二个数据源(同一个数据库同一个实例下,不同用户)  -->
        <bean id="dataSource_sec" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
            <property name="url">
                <value>${jdbc.url_sec}</value>
            </property>
            <property name="username">
                <value>${jdbc.username_sec}</value>
            </property>
            <property name="password">
                <value>${jdbc.password_sec}</value>
            </property>
            <!-- 数据库驱动类 -->
            <property name="driverClassName">
                <value>${jdbc.driverClassName_sec}</value>
            </property>
            <!-- 连接池的最大数据库连接数。设为0表示无限制。 -->
            <property name="maxActive">
                <value>${maxActive}</value>
            </property>
            <!-- 最大的空闲连接数,这里取值为30,表示即使没有数据库连接时依然可以保持30个空闲的连接,而不被清除,随时处于待命状态。设为0表示无限制。-->
            <property name="maxIdle">
                <value>${maxIdle}</value>
            </property>
            <!-- 最大建立连接等待时间(毫秒)。如果超过此时间将接到异常。设为-1表示无限制-->
            <property name="maxWait">
                <value>${maxWait}</value>
            </property>
            <!--指定数据库的默认自动提交-->
            <property name="defaultAutoCommit">
                <value>${defaultAutoCommit}</value>
            </property>
            <!--是否自动回收超时连接-->
            <property name="removeAbandoned">
                <value>${removeAbandoned}</value>
            </property>
            <!--超时时间(以秒数为单位)-->
            <property name="removeAbandonedTimeout">
                <value>${removeAbandonedTimeout}</value>
            </property>
            <property name="logAbandoned">
                <value>${logAbandoned}</value>
            </property>
        </bean>

    注:id不能重复,所以后面的使用了dataSource_sec和dataSource以区别,这样才能区分使用的是那一个数据库。如果上面的driverClassName配置一样,你可以不用配置jdbc_sec.driverClassName,都用jdbc.driverClassName即可。

    3、配置sessionFactory

     <!-- sessionFactory1-->  
       <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">  
           <property name="dataSource">  
               <ref local="dataSource"/>  
           </property>  
           <!-- 处理CLOB对象 -->  
           <property name="lobHandler" ref="lobHandler" />  
           <property name="mappingDirectoryLocations">  
               <list>  
                   <value>classpath:org/hdht/</value>  
               </list>  
           </property>  
           <property name="hibernateProperties">  
               <props>  
                   <prop key="hibernate.dialect">${hibernate.dialect}</prop>  
                   <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>  
                   <prop key="hibernate.format_sql">${hibernate.format_sql}</prop>   
                   <prop key="hibernate.jdbc.batch_size">${hibernate.jdbc.batch_size}</prop>  
                   <prop key="hibernate.cache.use_second_level_cache">${hibernate.cache.use_second_level_cache}</prop>  
                   <prop key="hibernate.cache.provider_class">${hibernate.cache.provider_class}</prop>  
                   <prop key="Hibernate.cache.use_query_cache">${Hibernate.cache.use_query_cache}</prop>  
                   <prop key="Hibernate.cache.query_cache_factory">${Hibernate.cache.query_cache_factory}</prop>  
               </props>  
           </property>  
       </bean>
    
       <!-- sessionFactory2-->  
       <bean id="sessionFactory_sec" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">  
           <property name="dataSource">  
               <ref local="dataSource_sec"/>  
           </property>  
           <!-- 处理CLOB对象 -->  
           <property name="lobHandler" ref="lobHandler" />  
           <property name="mappingDirectoryLocations">  
               <list>  
                   <value>classpath:org/hdht/</value>  
               </list>  
           </property>  
           <property name="hibernateProperties">  
               <props>  
                   <prop key="hibernate.dialect">${hibernate.dialect}</prop>  
                   <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>  
                   <prop key="hibernate.format_sql">${hibernate.format_sql}</prop>   
                   <prop key="hibernate.jdbc.batch_size">${hibernate.jdbc.batch_size}</prop>  
                   <prop key="hibernate.cache.use_second_level_cache">${hibernate.cache.use_second_level_cache}</prop>  
                   <prop key="hibernate.cache.provider_class">${hibernate.cache.provider_class}</prop>  
                   <prop key="Hibernate.cache.use_query_cache">${Hibernate.cache.use_query_cache}</prop>  
                   <prop key="Hibernate.cache.query_cache_factory">${Hibernate.cache.query_cache_factory}</prop>  
               </props>  
           </property>  
       </bean>

    3、Java后台代码实现及测试结果

    1)实体类Bean: TestSameInsMultiUserView

    package org.hdht.business.test.bean;
    
    /**
     * Station.java
     * 
     * 监测站实体类
     * 
     * @author 付建波
     */
    
    import java.io.Serializable;
    import java.util.Date;
    
    public class TestSameInsMultiUserView  implements Serializable{
    
        private static final long serialVersionUID = 2985240370584424720L;
    
        private Long dataid;
        private java.lang.String satellite;//卫星
        private java.lang.String sensor;//传感器
        private Long cloudCoverAmount ;//云覆盖量    
        private Float upperLeftLat ;//图像左上角纬度   -90~+90度
        private Float upperLeftLong ;//图像左上角经度  -180~+180度
        private Float upperRightLat ;//图像右上角纬度  -90~+90度
        private Float upperRightLong ;//图像右上角经度 -180~+180度
        private Float lowerLeftLat ;//图像左下角纬度   -90~+90度
        private Float lowerLeftLong ;//图像左下角经度  -180~+180度
        private Float lowerRightLat ;//图像右下角纬度  -90~+90度
        private Float lowerRightLong ;//图像右下角经度 -180~+180度
        private java.util.Date receiveDate;//接收日期
        private String productLevel ;//产品级别 SC GEC eGEC GTC DOM
        private Long dataSize; //数据大小
        private String productName; //产品名称
        private java.lang.String tb_flag; //表标识
        public Long getDataid() {
            return dataid;
        }
        public void setDataid(Long dataid) {
            this.dataid = dataid;
        }
        public java.lang.String getSatellite() {
            return satellite;
        }
        public void setSatellite(java.lang.String satellite) {
            this.satellite = satellite;
        }
        public java.lang.String getSensor() {
            return sensor;
        }
        public void setSensor(java.lang.String sensor) {
            this.sensor = sensor;
        }
        public Long getCloudCoverAmount() {
            return cloudCoverAmount;
        }
        public void setCloudCoverAmount(Long cloudCoverAmount) {
            this.cloudCoverAmount = cloudCoverAmount;
        }
        public Float getUpperLeftLat() {
            return upperLeftLat;
        }
        public void setUpperLeftLat(Float upperLeftLat) {
            this.upperLeftLat = upperLeftLat;
        }
        public Float getUpperLeftLong() {
            return upperLeftLong;
        }
        public void setUpperLeftLong(Float upperLeftLong) {
            this.upperLeftLong = upperLeftLong;
        }
        public Float getUpperRightLat() {
            return upperRightLat;
        }
        public void setUpperRightLat(Float upperRightLat) {
            this.upperRightLat = upperRightLat;
        }
        public Float getUpperRightLong() {
            return upperRightLong;
        }
        public void setUpperRightLong(Float upperRightLong) {
            this.upperRightLong = upperRightLong;
        }
        public Float getLowerLeftLat() {
            return lowerLeftLat;
        }
        public void setLowerLeftLat(Float lowerLeftLat) {
            this.lowerLeftLat = lowerLeftLat;
        }
        public Float getLowerLeftLong() {
            return lowerLeftLong;
        }
        public void setLowerLeftLong(Float lowerLeftLong) {
            this.lowerLeftLong = lowerLeftLong;
        }
        public Float getLowerRightLat() {
            return lowerRightLat;
        }
        public void setLowerRightLat(Float lowerRightLat) {
            this.lowerRightLat = lowerRightLat;
        }
        public Float getLowerRightLong() {
            return lowerRightLong;
        }
        public void setLowerRightLong(Float lowerRightLong) {
            this.lowerRightLong = lowerRightLong;
        }
        public java.util.Date getReceiveDate() {
            return receiveDate;
        }
        public void setReceiveDate(java.util.Date receiveDate) {
            this.receiveDate = receiveDate;
        }
        public String getProductLevel() {
            return productLevel;
        }
        public void setProductLevel(String productLevel) {
            this.productLevel = productLevel;
        }
        public Long getDataSize() {
            return dataSize;
        }
        public void setDataSize(Long dataSize) {
            this.dataSize = dataSize;
        }
        public String getProductName() {
            return productName;
        }
        public void setProductName(String productName) {
            this.productName = productName;
        }
        public java.lang.String getTb_flag() {
            return tb_flag;
        }
        public void setTb_flag(java.lang.String tb_flag) {
            this.tb_flag = tb_flag;
        }
    
    }
    

    2)Hibernate映射文件: TestSameInsMutilUserView.hbm.xml

    <?xml version="1.0" encoding="utf-8"?>
    <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
    <!-- 
        Mapping file autogenerated by MyEclipse Persistence Tools
    -->
    <hibernate-mapping>
        <class name="org.hdht.business.test.bean.TestSameInsMultiUserView" table="DOPD.view_same_ins_multi_user">
            <id name="dataid" type="java.lang.Long">        
                <column name="DATAID" precision="22" scale="0" />
            </id>
            <property name="satellite" type="java.lang.String">
                <column name="satellite" length="50" ></column>
            </property>
            <property name="sensor" type="java.lang.String">
                <column name="sensor" length="100"></column>
            </property>
            <property name="cloudCoverAmount" type="java.lang.Long">
                <column name="cloudcover_amount" length="100"></column>
            </property>
            <property name="upperLeftLat" type="java.lang.Float">
                <column name="upper_LeftLat" length="100"></column>
            </property>
            <property name="upperLeftLong" type="java.lang.Float">
                <column name="upper_LeftLong" length="100">
                </column>
            </property>
            <property name="upperRightLat" type="java.lang.Float">
                <column name="upper_RightLat" length="100">
                </column>
            </property>
           <property name="upperRightLong" type="java.lang.Float">
                <column name="upper_RightLong" length="100">
                </column>
            </property>
            <property name="lowerLeftLat" type="java.lang.Float">
                <column name="lower_LeftLat" length="100">
                </column>
            </property>
            <property name="lowerLeftLong" type="java.lang.Float">
                <column name="lower_LeftLong" length="100">
                </column>
            </property>
            <property name="lowerRightLat" type="java.lang.Float">
                <column name="lower_RightLat" length="100">
                </column>
            </property>
            <property name="lowerRightLong" type="java.lang.Float">
                <column name="lower_RightLong" length="100"></column>
            </property>
            <property name="receiveDate" type="java.util.Date">
                <column name="receive_date" length="200">
                        <comment>接收日期</comment>
                </column>
            </property>
            <property name="productLevel" type="java.lang.String">
              <column name="product_level"></column>
            </property>
            <property name="dataSize" type="java.lang.Long">
              <column name="dataSize"></column>
            </property>
            <property name="productName" type="java.lang.String">
              <column name="productName"></column>
            </property>
            <property name="tb_flag" type="java.lang.String">
                <column name="TB_FLAG" length="5"></column>
            </property>
        </class>
    </hibernate-mapping>
    

    3)数据访问对象DAO:TestSameInsMultiUserViewDAO

    package org.hdht.business.test.dao;
    
    /**
     * 类名:StationDAO.java
     * 功能:运输资源数据访问类
     * @author 付建波
     * @version Ver 1.0 2010-01-26 初版
     */
    
    
    import java.io.Serializable;
    import java.sql.SQLException;
    
    import org.apache.commons.logging.Log;
    import org.apache.commons.logging.LogFactory;
    import org.hdht.business.test.bean.TestSameInsMultiUserView;
    import org.hdht.commonweb.basedao.BaseHibDAO;
    import org.hibernate.HibernateException;
    import org.hibernate.Session;
    import org.springframework.orm.hibernate3.HibernateCallback;
    import org.springframework.orm.hibernate3.HibernateTemplate;
    
    public class TestSameInsMultiUserViewDAO extends BaseHibDAO<TestSameInsMultiUserView> implements Serializable {
    
        private static final long serialVersionUID = 1246807624187296626L;
    
        private final static Log log = LogFactory.getLog(TestSameInsMultiUserViewDAO.class);
    
        /**
         * 构造方法
         */
        public TestSameInsMultiUserViewDAO() {
            super(TestSameInsMultiUserView.class, log, "id");
        }
    
           /**
         * 通过dataid取出元数据
         * @param dataid
         * @return
         */
        public TestSameInsMultiUserView findById(final java.lang.Long dataid) {
            log.info("getting TestSameInsMultiUserView instance with id: " + dataid);
            try {
                HibernateTemplate ht = getHibernateTemplate();
                return (TestSameInsMultiUserView)ht.execute(new HibernateCallback(){
                    public Object doInHibernate(Session session) throws HibernateException, SQLException {
                        TestSameInsMultiUserView testSameInsMultiUserView = (TestSameInsMultiUserView) session.get(TestSameInsMultiUserView.class, dataid);
                        return testSameInsMultiUserView;
                    }
                });
            } catch (RuntimeException re) {
                log.error("get failed", re);
                throw re;
            }
        }
    
    }
    

    4)业务逻辑层Logic:TestSameInsMultiUserViewLogic

    package org.hdht.business.test.logic;
    
    /**
     * 类名:StationLogic.java
     * 功能:运输资源业务逻辑类
     * @author 付建波
     * @version Ver 1.0 2010-01-26 初版
     */
    
    
    import java.io.Serializable;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import org.hdht.business.test.bean.TestBlobView;
    import org.hdht.business.test.bean.TestSameInsMultiUserView;
    import org.hdht.business.test.dao.TestBlobViewDAO;
    import org.hdht.business.test.dao.TestSameInsMultiUserViewDAO;
    import org.hdht.commonweb.baselogic.GridLogic;
    import org.hdht.commonweb.baselogic.GridSearchBean;
    import org.hdht.map.spatial.bean.ParaProductQuery;
    import org.hdht.map.spatial.logic.SpatialQuery;
    
    public class TestSameInsMultiUserViewLogic extends GridLogic implements Serializable{
        private static final long serialVersionUID = 7424950578617734859L;
        private SpatialQuery spatialQuery;
        /**运输资源数据访问对象*/
        private TestSameInsMultiUserViewDAO testSameInsMultiUserViewDAO;
    
    
        public TestSameInsMultiUserViewDAO getTestSameInsMultiUserViewDAO() {
            return testSameInsMultiUserViewDAO;
        }
    
    
        public void setTestSameInsMultiUserViewDAO(
                TestSameInsMultiUserViewDAO testSameInsMultiUserViewDAO) {
            this.testSameInsMultiUserViewDAO = testSameInsMultiUserViewDAO;
        }
    
        private TestBlobViewDAO testBlobViewDAO;
    
        public TestBlobViewDAO getTestBlobViewDAO() {
            return testBlobViewDAO;
        }
    
    
        public void setTestBlobViewDAO(TestBlobViewDAO testBlobViewDAO) {
            this.testBlobViewDAO = testBlobViewDAO;
        }
    
        public TestBlobView getTestBlobViewById(Long id){
            TestBlobView testBlobView = new TestBlobView();
            testBlobView = (TestBlobView)this.testBlobViewDAO.get(id);
            return testBlobView;
        }
    
        /***
         * 查询基本卫星信息视图中列表记录
         * 
         * @param currentPage    当前页码
         * @param pageSize    页面最大行数
         * @param map  查询条件键值对 key -> String, value -> Object
         * @return
         */
        @SuppressWarnings("unchecked")
        private List listSpital = new ArrayList(); 
        private List metaList = new ArrayList();
        public Map listTestSameInsMultiUserView(int currentPage, int pageSize, Map map) {
            StringBuffer dataHQL = new StringBuffer("from TestSameInsMultiUserView s ");
            StringBuffer countHQL =  new StringBuffer("select count(*) from TestSameInsMultiUserView s ");
            Map<String,Object> smap = new HashMap<String,Object>();
            String sat = null!=map.get("sat")?map.get("sat").toString(): "";
            String sensor = null!=map.get("sensor")?map.get("sensor").toString():"";;
            if(!"".equals(sat)&&!"".equals(sensor)){
                smap.put("s.sat", sat);
                smap.put("s.sensor",sensor);
            }
            GridSearchBean gsb = initGridSearchBean(smap);
            String whereSql = gsb.getWhereSql().toString();
            String starttime = null!=map.get("starttime")?map.get("starttime").toString():"";
            String endttime = null!=map.get("endttime")?map.get("endttime").toString():"";
            String geometryType = null!=map.get("geometryType")?map.get("geometryType").toString():"";//空间查询类型
            boolean issearch =  null!=map.get("issearch")&&map.get("issearch").toString().equals("false")?false:true;
            if(!"".equals(starttime)) {
                whereSql += "  and receive_date >= to_date('"+starttime+"','yyyy-MM-dd HH24:mi:ss')";
            }
            if(!"".equals(endttime)) {
                whereSql += "  and receive_date <= to_date('"+endttime+"','yyyy-MM-dd HH24:mi:ss')";
            }
            dataHQL.append(whereSql);
            countHQL.append(whereSql);
             Map m = null;
             if(!geometryType.equals("")){
                 if(currentPage==1&&issearch){
                     listSpital = this.searchSDE(map);  //从fileGDB中查询出来的
                     List<TestSameInsMultiUserView> listProp = this.testSameInsMultiUserViewDAO.findByHQLQuery(dataHQL.toString());//从数据库中查询出来的
                    //属性查询结果
                    List newList = new ArrayList();
                    for (TestSameInsMultiUserView metaImg : listProp) {
                        String dataid =  metaImg.getDataid().toString();
                        newList.add(dataid);
                    }
                    newList.retainAll(listSpital);//取交集,将fileGDB中查询出来的数据以及从数据库中查询出来的取交集
                    metaList = new ArrayList();//存放取完交集后的数据实体
                    int size = listProp.size();
                    for (int i = 0; i < size; i++) {
                        TestSameInsMultiUserView bf = listProp.get(i);
                        if(newList.contains(bf.getDataid().toString())){
                             metaList.add(bf);
                        }
                    }
                 }
                m = this.getPageList(metaList, currentPage, pageSize);
             }else{
                m = this.search(dataHQL.toString(), gsb.getArgs(), countHQL.toString(), gsb.getArgs(), currentPage, pageSize);
             }
             return m;
        }
    
        //开始分页
        public Map getPageList(List list,int currentPage, int pageSize){
            int countAll=list.size();
            List pageResult=new ArrayList();
            for(int i=(currentPage-1)*pageSize;i<currentPage*pageSize;i++){
                if(i>=countAll){
                    break;
                }
                pageResult.add(list.get(i));
            }
            Map m = new HashMap();
            m.put("list", pageResult);
            m.put("all", countAll);
            return m;
        }
    
        /**
         * 根据Id获取BasicSatInfo对象
         * @param id
         * @return
         */
        public TestSameInsMultiUserView getInstanceById(Long id, String modelName) {
            try {
                TestSameInsMultiUserView instance = testSameInsMultiUserViewDAO.findByProperty("id", id);
                return instance;
            } catch(Exception ex) {
                return new TestSameInsMultiUserView();
            }
        }
    
        /***
         * SDE数据检索
         * @param searchConditionMap 检索条件
         * @return list = pids
         * */
        public List searchSDE(Map searchConditionMap){
            String satelliteIdStr = null!=searchConditionMap.get("satelliteIdStr")?searchConditionMap.get("satelliteIdStr").toString():"";
            String sensorCodeStr = null!=searchConditionMap.get("sensor_codeStr")?searchConditionMap.get("sensor_codeStr").toString():"";
            String datatypenameStr = null!=searchConditionMap.get("datatypenameStr")?searchConditionMap.get("datatypenameStr").toString():"";
            String productLevelStr = null!=searchConditionMap.get("productLevelStr")?searchConditionMap.get("productLevelStr").toString():"";
            String cloudAmountStr = null!=searchConditionMap.get("cloudAmountStr")?searchConditionMap.get("cloudAmountStr").toString():"";
            String begintime = null!=searchConditionMap.get("starttime")?searchConditionMap.get("starttime").toString():"";//采集开始时间
            String endtime = null!=searchConditionMap.get("endttime")?searchConditionMap.get("endttime").toString():""; //采集开始时间//采集结束时间
            String lowerRightLong = null!=searchConditionMap.get("lowerRightLong")?searchConditionMap.get("lowerRightLong").toString():""; //经纬度
            String upperLeftLong = null!=searchConditionMap.get("upperLeftLong")?searchConditionMap.get("upperLeftLong").toString():"";
            String lowerRightLat = null!=searchConditionMap.get("lowerRightLat")?searchConditionMap.get("lowerRightLat").toString():"";
            String upperLeftLat  = null!=searchConditionMap.get("upperLeftLat")?searchConditionMap.get("upperLeftLat").toString():"";
            String geometryType  = null!=searchConditionMap.get("geometryType")?searchConditionMap.get("geometryType").toString():"";
            String distName  = null!=searchConditionMap.get("distName")?searchConditionMap.get("distName").toString():"";
            String rings  = null!=searchConditionMap.get("rings")?searchConditionMap.get("rings").toString():"";
    //      String searchSpaceFlag  = null!=searchConditionMap.get("searchSpaceFlag")?searchConditionMap.get("searchSpaceFlag").toString():"";
            ParaProductQuery paraProductQuery = new ParaProductQuery();
            paraProductQuery.setIsContain("1");
            paraProductQuery.setStrSatellite(satelliteIdStr);
            paraProductQuery.setSensorCodeStr(sensorCodeStr);
            paraProductQuery.setDatatypename(datatypenameStr);
            paraProductQuery.setProductLevel(productLevelStr);
            paraProductQuery.setCloudAmount(cloudAmountStr);
            paraProductQuery.setSceneStartTime(begintime);
            paraProductQuery.setSceneEndTime(endtime);
            paraProductQuery.setLowerRightLong(lowerRightLong);
            paraProductQuery.setLowerRightLat(lowerRightLat);
            paraProductQuery.setUpperLeftLong(upperLeftLong);
            paraProductQuery.setUpperLeftLat(upperLeftLat);
            paraProductQuery.setGeometryType(geometryType) ;
            paraProductQuery.setDistName(distName);
            paraProductQuery.setRings(rings);
            List pids = this.spatialQuery.querySdePolygon(paraProductQuery); 
            return pids;
        }
    
    
        public SpatialQuery getSpatialQuery() {
            return spatialQuery;
        }
    
    
        public void setSpatialQuery(SpatialQuery spatialQuery) {
            this.spatialQuery = spatialQuery;
        }
        /**
         * 通过id取出BasicSatInfoView 基本的卫星信息
         * 
         * @param dataid
         * @return
         */
        public TestSameInsMultiUserView getTestSameInsMultiUserViewById(long dataid) {
            TestSameInsMultiUserView testSameInsMultiUserView = this.testSameInsMultiUserViewDAO.findById(dataid);
            return testSameInsMultiUserView;
        }
    
    
    
    } 
    

    5)Action类:TestSameInsMultiUserViewAction

    /**
     * 类名:TransportAction.java
     * 功能:运输资源控制器类
     * @author 付建波
     * @version Ver 1.0 2010-01-26 初版
     */
    
    package org.hdht.business.test.action;
    
    import java.util.Map;
    
    import org.hdht.business.search.sde.bean.PointBean;
    import org.hdht.business.test.bean.TestSameInsMultiUserView;
    import org.hdht.business.test.logic.TestSameInsMultiUserViewLogic;
    import org.hdht.commonweb.baseservlet.IocServlet;
    import org.hdht.util.character.UtilString;
    
    public class TestSameInsMultiUserViewAction  extends IocServlet{
    
        private static final long serialVersionUID = 1L;
        private String tb_flag;//多源数据表名
        private String source_data_id;//源数据ID
        /**产品显示详细的数据对象*/
        private TestSameInsMultiUserView testSameInsMultiUserView ;
        private TestSameInsMultiUserViewLogic testSameInsMultiUserViewLogic;
        private PointBean pointBean = new PointBean();
        public TestSameInsMultiUserView getTestSameInsMultiUserView() {
            return testSameInsMultiUserView;
        }
        public void setTestSameInsMultiUserView(
                TestSameInsMultiUserView testSameInsMultiUserView) {
            this.testSameInsMultiUserView = testSameInsMultiUserView;
        }
        public TestSameInsMultiUserViewLogic getTestSameInsMultiUserViewLogic() {
            return testSameInsMultiUserViewLogic;
        }
        public void setTestSameInsMultiUserViewLogic(
                TestSameInsMultiUserViewLogic testSameInsMultiUserViewLogic) {
            this.testSameInsMultiUserViewLogic = testSameInsMultiUserViewLogic;
        }
        public String getTb_flag() {
            return tb_flag;
        }
        public void setTb_flag(String tb_flag) {
            this.tb_flag = tb_flag;
        }
        public String getSource_data_id() {
            return source_data_id;
        }
        public void setSource_data_id(String source_data_id) {
            this.source_data_id = source_data_id;
        }
    
        public TestSameInsMultiUserView getTestSameMultiUserView() {
            return testSameInsMultiUserView;
        }
        public void setTestSameMultiUserView(TestSameInsMultiUserView testSameInsMultiUserView) {
            this.testSameInsMultiUserView = testSameInsMultiUserView;
        }
        public PointBean getPointBean() {
            return pointBean;
        }
        public void setPointBean(PointBean pointBean) {
            this.pointBean = pointBean;
        }
        /***
         * 查询基本卫星信息列表记录
         * 
         * @param currentPage    当前页码
         * @param pageSize    页面最大行数
         * @param map    查询条件键值对 key -> String, value -> Object
         * @return
         */
        @SuppressWarnings("unchecked")
        public Map listTestSameInsMultiUserView(int currentPage, int pageSize, Map map) {
             return this.testSameInsMultiUserViewLogic.listTestSameInsMultiUserView(currentPage, pageSize, map);
        }
    
        /**
         * 根据Id获取station对象
         * @param id    检测站主键
         * @return Station 检测站对应
         */
        public TestSameInsMultiUserView getStationById(Long id, String modelName) {
            return this.testSameInsMultiUserViewLogic.getInstanceById(id, modelName);
        }
    
    
        /**
         * 多源数据信息 “详情” 列表展示
         * @param source_data_id 源数据ID
         * @param tablename 所在表
         * */
        @SuppressWarnings("unchecked")
        public String getShowDetail(){
            try {
                if(UtilString.isNotNull(this.tb_flag)&&UtilString.isNotNull(this.source_data_id)){
                    TestSameInsMultiUserView testSameInsMultiUserView = this.testSameInsMultiUserViewLogic.getTestSameInsMultiUserViewById(Long.valueOf(this.source_data_id)) ;
                    this.setTestSameInsMultiUserView(testSameInsMultiUserView);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return SUCCESS;
        }
    
        /**
         * 根据流水号获取影像
         * @param serialid
         * @return
         */
        public String rightLitterMap(){
            return SUCCESS;
        }
    
    
    
    
    }
    

    6、DWR以及Spring配置文件
    TestSameInsMutilUserView.dwr.xml
    TestSameInsMutilUserView.spring.xml

    1)TestSameInsMutilUserView.dwr.xml

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE dwr PUBLIC "-//GetAhead Limited//DTD Direct Web Remoting 3.0//EN" "http://getahead.org/dwr//dwr30.dtd">
    <dwr>
        <allow>
            <create creator="spring" javascript="TestSameInsMultiUserViewAction" scope="application">
                <param name="beanName" value="testSameInsMultiUserViewAction" />
            </create>
            <convert converter="hibernate3" match="org.hdht.business.test.bean.TestSameInsMultiUserView" >
            </convert>
        </allow>
    </dwr>

    2)TestSameInsMutilUserView.spring.xml

    <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
            xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd">
    
        <bean id="testSameInsMultiUserViewAction" class="org.hdht.business.test.action.TestSameInsMultiUserViewAction" scope="prototype" autowire="byName" />
    
        <bean id="testSameInsMultiUserViewLogic" class="org.hdht.business.test.logic.TestSameInsMultiUserViewLogic" autowire="byName" />
    
        <bean id="testSameInsMultiUserViewDAO" class="org.hdht.business.test.dao.TestSameInsMultiUserViewDAO" autowire="byName" />
    
    </beans>   

    7、测试代码:(基于本公司框架)
    测试代码

    TestSameInsMultiUserViewAction testSameInsMultiUserViewAction = (TestSameInsMultiUserViewAction) ApplicationContextHolder.getBean("testSameInsMultiUserViewAction");
            java.util.Map m = testSameInsMultiUserViewAction.listTestSameInsMultiUserView(1, 10, new java.util.HashMap());
            TTool.println(m);

    测试结果

    测试结果

    展开全文
  • 可是创建完就查看数据的时候发现除了B中的数据没有重复,但是C,D,E中的数据在视图中重复出现 比如,B中有同一个员工的3条数据,而C,D,E中只有1条,那么在视图中显示的情况是 user_id b.rizhi c.plan d....
  • 创建视图在单表上创建视图多表创建视图
  • 匿名用户1级2016-12-03 回答MySQL在个数据创建视图在MySQL中,使用CREATE VIEW语句也可以在两个或两个以上的数据创建视图。实例在department和worker上创建一个名为worker_view1的视图。1. 创建视图的...
  • MySQL视图(View)是一种虚拟的表,同真实的表一样,拥有相同的行和列 ,但视图并不实际存在于数据库中,而是在你使用的时候去动态的查询表,view的行和列都是基于你使用view的时候动态的去...基于单表创建视图 #创建学生表
  • MySQL中,在两个或者以上的基本创建视图 在student和stu_info上,创建stu_class视图,查询出s_id号、姓名和班级 首先,创建stu_info,并向中插入数据 查看中的数据 创建stu_class视图 查看视图 ...
  • 可以方便用户对数据的操作,那如何在已有的数据库创建视图,并且查看创建视图的信息,具体操作如下:1.为了不影响其他的数据库,新建一张数据库t_worker_info,代码如下:create table t_worker_info(id int...
  • MySQL在多表创建视图

    万次阅读 2017-07-29 23:40:43
    MySQL中,在两个或者以上的基本创建视图在student和stu_info上,创建stu_class视图,查询出s_id号、姓名和班级 首先,创建stu_info,并向中插入数据查看中的数据创建stu_class视图查看视图可以看出,...
  • :指定创建视图的 SELECT 语句,可用于查询个基础或源视图。对于创建视图中的 SELECT 语句的指定存在以下限制:用户除了拥有 CREATE VIEW 权限外,还具有操作中涉及的基础和其他视图的相关...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 3,411
精华内容 1,364
关键字:

多表创建视图