Java创建存储过程添加字段和索引

// 创建字段添加存储过程，参数为：表名，字段名，创建语句。
session.execute("DROP PROCEDURE IF EXISTS proAddColumn;", Collections.emptyList());
session.execute("CREATE PROCEDURE proAddColumn(in tableName VARCHAR(50), in columnName VARCHAR(50), in addColumnSql VARCHAR(1000)) " +
                "BEGIN " +
                "  IF NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE TABLE_NAME = tableName AND COLUMN_NAME = columnName) THEN " +
                "    SET @sqlStmt = addColumnSql; " +
                "    PREPARE stmt FROM @sqlStmt; " +
                "    EXECUTE stmt; " +
                "  END IF; " +
                "END " , Collections.emptyList());
// 在mysql客户端执行调用存储过程时，若addColumnSql参数包有单引号，则改为两个单引号——不是双引号，才能执行。sql中引号嵌套全部使用单引号。                
session.execute("CALL proAddColumn(?, ?, ?);", Arrays.asList("table1111", "column1111", "ALTER TABLE `table1111` " +
                "ADD COLUMN `column1111` DATE NOT NULL DEFAULT '1970-01-01' COMMENT '测试字段' AFTER `column0000`"));

SQL

// 创建存储过程
DROP PROCEDURE IF EXISTS proAddColumn;
DELIMITER $$
CREATE PROCEDURE proAddColumn(in tableName VARCHAR(50), in columnName VARCHAR(50), in addColumnSql VARCHAR(1000)) BEGIN
IF NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE TABLE_NAME = tableName AND COLUMN_NAME = columnName) THEN
	SET @sqlStmt = addColumnSql;
	PREPARE stmt FROM @sqlStmt;
	EXECUTE	stmt;
END IF;
END $$

// 调用存储过程
CALL proAddColumn('table1111', 'column1111',
 'ALTER TABLE `table1111` ADD COLUMN `column1111` DATE NOT NULL DEFAULT ''1970-01-01'' COMMENT ''测试字段'' AFTER `column0000`');
 
 

-- 判断索引是否存在，不存在则创建索引
DROP PROCEDURE IF EXISTS proAddIndex;  
DELIMITER $$
CREATE PROCEDURE proAddIndex(in tableName VARCHAR(50), in indexName VARCHAR(50), in addIndexSql VARCHAR(1000)) BEGIN 
IF NOT EXISTS (SELECT 1 FROM information_schema.statistics WHERE table_name = tableName AND index_name = indexName) THEN
	SET @sqlStmt = addIndexSql;
	PREPARE stmt FROM @sqlStmt;
	EXECUTE	stmt;
END IF;
END $$
// 调用存储过程
CALL proAddIndex('table1111', 'index1111',
 'ALTER TABLE `table1111` ADD INDEX `index1111` (`column1111`, `column2222`)');