Oracle数据表根据表名生成Select语句和Model及JavaBean对象

一、根据表名，通过sql生成select 语句

select 'select '|| wmsys.wm_concat(t.column_name ) ||' from '||table_name from user_tab_cols  t 

where lower(table_name)='t_tfm_vms_binding'  group by table_name

结果如下：

select ID,UPDATETIME,COLOR,ZOOM,PIXELY,PIXELX,TILEY,TILEX,LATITUDE,LONGITUDE,POINTY,POINTX,IMAGEID from T_TFM_VMS_BINDING

二、根据表名，生成model对象或javabean对象

--拼接model各字段生成（C#对象）

select '///<summary>' || chr(10) || '///' || c.comments || chr(10) ||
       '///<summary>' || chr(10) ||
       decode(t.data_type, 'NUMBER', 'public double ', 'public string ') ||
       upper(substr(t.column_name, 1, 1)) ||
       substr(lower(t.column_name), 2) || ' {get; set;}' || chr(13)
  from user_tab_cols t, user_col_comments c
 where lower(t.table_name) = 't_tfm_vms_binding'
   and t.table_name = c.table_name
   and t.column_name = c.column_name;

结果如下：

"///<summary>
///唯一标识
///<summary>
public string Id {get; set;}
"
"///<summary>
///底图ID
///<summary>
public string Imageid {get; set;}
"
"///<summary>
///关联像素X
///<summary>
public double Pointx {get; set;}
"
"///<summary>
///关联像素Y
///<summary>
public double Pointy {get; set;}
"
"///<summary>
///经度
///<summary>
public double Longitude {get; set;}
"
"///<summary>
///纬度
///<summary>
public double Latitude {get; set;}
"
"///<summary>
///瓦片X
///<summary>
public double Tilex {get; set;}
"
"///<summary>
///瓦片Y
///<summary>
public double Tiley {get; set;}
"
"///<summary>
///位置像素x
///<summary>
public double Pixelx {get; set;}
"
"///<summary>
///位置像素Y
///<summary>
public double Pixely {get; set;}
"
"///<summary>
///地图等级
///<summary>
public string Zoom {get; set;}
"
"///<summary>
///路况颜色
///<summary>
public string Color {get; set;}
"
"///<summary>
///更新时间
///<summary>
public string Updatetime {get; set;}
"

JavaBean对象

select '/**' || chr(10) || '*' || c.comments || chr(10) || '*/' || chr(10) ||
       decode(t.data_type, 'NUMBER', 'private double ', 'private String ') ||
       lower(t.column_name) || ';' || chr(13)
  from user_tab_cols t, user_col_comments c
 where lower(t.table_name) = 't_wx_info'
   and t.table_name = c.table_name
   and t.column_name = c.column_name;

Oracle查询生成JavaBean：

select '/**' || chr(13) || chr(10) || '*' ||
       lower(decode(B.comments, null, '', B.comments)) || chr(13) ||
       chr(10) || '*/' || chr(13) || chr(10) || 'private ' ||
       case A.data_type
         when 'NUMBER' then
          'int'
         when 'LONG' then
          'int'
         when 'VARCHAR2' then
          'String'
         when 'VARCHAR' then
          'String'
         when 'CHAR' then
          'String'
         when 'CLOB' then
          'String'
         when 'DATE' then
          'Date'
         when 'TIMESTAMP' then
          'Date'
         else
          'String'
       end || ' ' || lower(a.column_name) || ';' || chr(13) || chr(10) ||
       chr(13) || chr(10)
  from user_tab_columns A, user_col_comments B
 where A.Table_Name = 'T_WX_ARTICLE'
   and A.Table_Name = B.Table_Name
   and A.Column_Name = B.Column_Name

  //oracle根据表名生成select语句 ,顺序符合table中的colume顺序******


select table_name,'select '||trim(lower(max(key1)))||' from '||lower(table_name) from (
   select t.table_name,wmsys.wm_concat(' '||t.column_name ) OVER(PARTITION by t.table_name ORDER BY INTERNAL_COLUMN_ID asc) key1  
   from user_tab_cols  t 
where upper(table_name)='T_WX_ARTICLE' ) 
group by  table_name 

好久没写blog，以此勉励