简介
通常使用mybatis或者是hibernate持久化框架,有点很麻烦就是每张表的映射实体类,需要“_”格式命名转成驼峰式命名,并且需要把表备注和字段备注,与类注解和成员变量注解对应上,如果一直用手动复制创建,不仅耗时,而且容易出错。虽然网络有很多java工具类可以生成,这边写了一个oracle函数,通过oracle原有的函数实现了生成表的映射类。
使用函数
执行查询sql
select create_entity('表名') from dual;
结果
导入两个函数to_hump,create_entity
to_hump函数
create or replace function udcp.to_hump(culumn_name IN VARCHAR2) return varchar2 is
str1 VARCHAR2(200);
RE_COLUMN varchar2(1024);
begin
str1 := culumn_name;
select decode(INSTR(str1,'_',1,1),0,str1,replace(str1, '_' || substr(str1, INSTR(str1, '_', 1, 1) + 1, 1),upper(substr(str1, INSTR(str1, '_', 1, 1) + 1, 1))))
into RE_COLUMN from dual;
return RE_COLUMN;
end to_hump;
create_entity
create or replace function create_entity(tableName2 IN VARCHAR2) return clob is
tableName varchar2(1000);/*大写的表名*/
head1 varchar2(1000);/*实体的头信息*/
head2 varchar2(1000);/*通用的packet路径,和类注解*/
head3 varchar2(1000);/*数据库表备注*/
clo_m varchar2(1000);/*字段的备注*/
params varchar2(2000):='';/*类的成员变量*/
set_met varchar2(2000):='';/*实体类的set方法*/
get_met varchar2(2000):='';/*实体类的get方法*/
u_char varchar2(1000);/*实体类类名*/
p_name varchar2(1000);/*表主键名称*/
c_type varchar2(1000);/*表字段的类型*/
c_name varchar2(1000);/*驼峰字段名*/
FunctionResult clob;
begin
tableName:=upper(tableName2);
u_char:= to_hump(to_hump(to_hump(to_hump(to_hump(lower(tableName))))));
head1 := '@Entity'||chr(10)||'@Table(name = "'||tableName||'")'||chr(10)||'public class '
|| regexp_replace(u_char,substr(u_char,1,1),upper(substr(u_char,1,1)),1,1)
|| ' implements Serializable {'|| chr(10)||chr(9)||'private static final long serialVersionUID = 1L;'||chr(10);/*拼接实体的头信息*/
select comments into head3
from user_tab_comments
where TABLE_NAME = tableName;
head2:='import java.io.Serializable;'||chr(10)||'import java.math.BigDecimal;'||chr(10)||'import java.util.Date;'||chr(10)
||'import javax.persistence.Column;'||chr(10)||'import javax.persistence.Entity;'||chr(10)
||'import javax.persistence.Id;'||chr(10)||'import javax.persistence.Table;'||chr(10)||chr(10);
head2:=head2||'/**'||chr(10)||' * @author '|| chr(10)||' * @date '||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')|| chr(10)||' * @description '|| head3 || chr(10)||'*/'||chr(10);/*类注解*/
/* 主键判断*/
select a.column_name into p_name
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'P' and a.table_name = UPPER(tableName);
/* 遍历字段*/
/*查询表的所有column 开启循环*/
for x in(select * from user_tab_columns where TABLE_NAME = upper(tableName))loop
/*字段注解*/
params:= params ||chr(9)||'/**'||chr(10)||chr(9)||'* '||chr(10);
select comments INTO clo_m
from user_col_comments
where TABLE_NAME = UPPER(tableName)
AND COLUMN_NAME= UPPER(x.COLUMN_NAME);
params:=params ||chr(9)||'*'|| clo_m||chr(10)||chr(9)||'*/'||chr(10);
IF x.COLUMN_NAME=UPPER(p_name) THEN
params:=params||chr(9)||'@Id'||chr(10);
END IF ;
params:=params||chr(9)||'@Column(name = "'||x.COLUMN_NAME||'")'||chr(10);
IF x.DATA_TYPE='NUMBER' THEN
c_type:='BigDecimal';
elsif x.DATA_TYPE='CLOB'then
c_type:='Byte[]';
elsif x.DATA_TYPE='DATE'then
c_type:='Date';
elsif x.DATA_TYPE='CHAR'then
c_type:='String';
elsif x.DATA_TYPE='VARCHAR2'then
c_type:='String';
elsif x.DATA_TYPE='TIMESTAMP'then
c_type:='Date';
elsif x.DATA_TYPE='BLOB'then
c_type:='Byte[]';
else
c_type:='String';
end if;/*判断字段类型*/
c_name:=to_hump(to_hump(to_hump(to_hump(to_hump(lower(x.COLUMN_NAME))))));
params:=params||chr(9)||'private '||c_type||' '||c_name||';'||chr(10);
/*set方法*/
set_met:=set_met||chr(10)||chr(9)||'public void'||' set'||regexp_replace(c_name,substr(c_name,1,1),upper(substr(c_name,1,1)),1,1)
||'('||c_type||' '|| c_name ||') {'||chr(10)||chr(9)||chr(9)||'this.'||c_name||' = '||c_name||';'||chr(10)||chr(9)||'}';
get_met:=get_met||chr(10)|| chr(9)||'public '||c_type||' get'||regexp_replace(c_name,substr(c_name,1,1),upper(substr(c_name,1,1)),1,1)
||'(){'||chr(10)||chr(9)||chr(9)||'return '||c_name||';'||chr(10)||chr(9)||'}'||chr(10);
/*对象成员变量*/
end loop;
FunctionResult:=head2||head1||params||set_met||get_met||'}';
return FunctionResult;
end create_entity;