mysql 、 postgresql 转换 java sql

Mysql

驼峰转换

DELIMITER $$
DROP FUNCTION IF EXISTS transform_underline$$
CREATE FUNCTION transform_underline(name varchar(50)) RETURNS varchar(50)
BEGIN
  DECLARE ret VARCHAR(50) DEFAULT '';
-- 下划线个数接受5个内,若多于5个自行修改下面值即可
	declare v_max int unsigned default 5;
  SET ret=name;
	while v_max >0  do
		set ret=REPLACE(ret,SUBSTR(ret,INSTR(ret,'_'),2),SUBSTR(UPPER(SUBSTR(ret,INSTR(ret,'_'),2)),2));
    set v_max=v_max-1;
  end while;
  return ret;
END $$
DELIMITER ;

------使用示例------
// 查询 test 表字段 并转为 驼峰格式
SELECT transformUnderline(COLUMN_NAME)
FROM information_schema.`COLUMNS`
WHERE TABLE_SCHEMA = 'test'        -- 数据库名
  AND TABLE_NAME = 'test' -- 表名
order by ORDINAL_POSITION;

字段类型映射

DELIMITER $$
DROP FUNCTION IF EXISTS dataTypeMapping$$
CREATE FUNCTION dataTypeMapping (type VARCHAR(20)) RETURNS VARCHAR (20)
BEGIN
	DECLARE ret VARCHAR(20) DEFAULT type;
	 CASE UPPER(type)
-- 布尔型
		WHEN 'BIT' THEN SET ret ='Boolean';
-- 整型
		WHEN 'INT' THEN SET ret ='Integer';
		WHEN 'INTEGER' THEN SET ret ='Integer';
		WHEN 'TINYINT' THEN SET ret ='Integer';
-- 浮点型
		WHEN 'DECIMAL' THEN SET ret ='BigDecimal';
		WHEN 'FLOAT' THEN SET ret ='FLOAT';
		WHEN 'DOUBLE' THEN SET ret ='DOUBLE';
-- 字符串类型
		WHEN 'VARCHAR' THEN SET ret ='String';
		WHEN 'CHAR' THEN SET ret ='String';
		WHEN 'TEXT' THEN SET ret ='String';
-- 时间类型
		WHEN 'DATETIME' THEN SET ret ='Date';
		WHEN 'DATE' THEN SET ret ='Date';
		WHEN 'TIME' THEN SET ret ='Time';
		WHEN 'TIMESTAMP' THEN SET ret ='Date';
		WHEN 'DATETIME' THEN SET ret ='Date';
-- 特殊类型按照以下格式添加即可
-- 		WHEN '数据库中类型大写' THEN SET ret ='Java中类型';
-- 其他类型不做映射
		ELSE SET ret = type;
	END CASE;
	return ret;
END $$
DELIMITER ;
------使用示例------
// 查询 test 表字段 类型
SELECT dataTypeMapping(DATA_TYPE)
FROM information_schema.`COLUMNS`
WHERE TABLE_SCHEMA = 'test'        -- 数据库名
  AND TABLE_NAME = 'test' -- 表名
order by ORDINAL_POSITION;

postgresql

驼峰转换

CREATE OR REPLACE FUNCTION transform_underline(name text)
  RETURNS text
AS $$
DECLARE
  ret text := '';
  i int := 0;
BEGIN
  LOOP
    i := POSITION('_' IN name);
    IF i = 0 THEN
      EXIT;
    END IF;
    ret := ret || INITCAP(SUBSTRING(name FROM 1 FOR i - 1));
    name := SUBSTRING(name FROM i + 1);
  END LOOP;
  ret := ret || INITCAP(name);
  RETURN LOWER(SUBSTRING(ret FROM 1 FOR 1)) || SUBSTRING(ret FROM 2);
END;
$$ LANGUAGE plpgsql;

------使用示例------
// 查询 test 表字段 并转为 驼峰格式
SELECT transform_underline(column_name)
FROM information_schema.columns
WHERE table_name = 'test'
ORDER BY ordinal_position;

字段类型映射

CREATE OR REPLACE FUNCTION dataTypeMapping(type TEXT) RETURNS TEXT
AS
$$
DECLARE
    ret TEXT := type;
BEGIN
    CASE UPPER(type)
        WHEN 'BIGINT' THEN ret := 'Long';
        WHEN 'INTEGER' THEN ret := 'Integer';
        WHEN 'CHARACTER VARYING' THEN ret := 'String';
        WHEN 'NUMERIC' THEN ret := 'BigDecimal';
        WHEN 'TIMESTAMP WITHOUT TIME ZONE' THEN ret := 'Date';
        WHEN 'SMALLINT' THEN ret := 'Integer';
        -- 类型按照以下格式添加即可
        -- 		WHEN '数据库中类型大写' THEN SET ret ='Java中类型';
        -- 其他类型不做映射
        ELSE ret := type;
        END CASE;
    RETURN ret;
END;
$$ LANGUAGE plpgsql;
------使用示例------
// 查询 test 表字段 类型
SELECT dataTypeMapping(data_type)
FROM information_schema.columns
WHERE table_name = 'test '
ORDER BY ordinal_position;

//驼峰转换适用与一个"_"的，多个下划线不可以
select concat(
               '/** ',
               COLUMN_COMMENT,
               ' */\r\n',
               'private ',
               (case DATA_TYPE
                    when 'varchar' then 'String '
                    when 'text' then 'String '
                    when 'int' then 'Integer '
                    when 'bigint' then 'Long '
                    when 'double' then 'Double '
                    when 'float' then 'Float '
                    when 'decimal' then 'BigDecimal '
                    when 'datetime' then 'Date '
                    when 'date' then 'Date '
                    when 'bit' then 'Boolean '
                   end),
               leftstr,
               if(leftstr = '', SUBSTRING(rightstr, 1, 1), UPPER(SUBSTRING(rightstr, 1, 1))),
               SUBSTRING(rightstr, 2, length(rightstr)),
               ';'
           ) as java_variable
from (select DISTINCTROW ORDINAL_POSITION,
                         substr(COLUMN_NAME, 1, instr(COLUMN_NAME, '_') - 1)   leftstr,
                         substr(COLUMN_NAME, instr(COLUMN_NAME, '_') + 1,
                                length(COLUMN_NAME) - instr(COLUMN_NAME, '_')) rightstr,
                         DATA_TYPE,
                         IS_NULLABLE,
                         COLUMN_COMMENT
      from information_schema.COLUMNS
      where table_schema = 'test'
        and table_name = 'test' -- 填写所需要的表名
     ) a
order by ORDINAL_POSITION;

-- 获取字段
SELECT
	CONCAT(
-- 注释部分
		'/**\n* ',
-- 注释值
		COLUMN_COMMENT,
		'\n*/',
		'\n',
-- 修饰符
		'private ',
-- 数据类型
		dataTypeMapping(DATA_TYPE),
		' ',
-- 属性
		transformUnderline (COLUMN_NAME),
		';'
	)
FROM
	information_schema.`COLUMNS`
WHERE
	TABLE_SCHEMA = 'test' -- 数据库名
AND TABLE_NAME = 'test' -- 表名
order by ORDINAL_POSITION;