java调用方法
private void createOrUpdateDBStore(String storeName,String tableSQLs,boolean newStore, String dbType) throws java.sql.SQLException
{
if(null == storeName || storeName.length()<=0)
{
throw new SQLException("数据库名为空");
}
if(null == tableSQLs || tableSQLs.length()<=0)
{
throw new SQLException("建表脚本为空");
}
SessionFactory.dispose();
Session session = SessionFactory.getSession();
try
{
java.sql.Connection conn = session.getConnection("WBO_Server_DB_System");
java.sql.Statement ps = conn.createStatement();
if(newStore)
{
String createStoreSQL = this.toCrateStoreSQL(storeName,dbType);
ToolLog.getLog().debug(createStoreSQL);
ps.execute(createStoreSQL);
}
StringBuffer sqlSB = new StringBuffer();
sqlSB.append("USE " + storeName + "\r\n");
ToolLog.getLog().debug(sqlSB.toString());
ps.execute(sqlSB.toString());
String[] lines = this.toSQLLines(tableSQLs);
sqlSB = new StringBuffer();
for(int i=0;i<lines.length;i++)
{
String s = lines[i];
if("GO".equalsIgnoreCase(s))
{
String sql = sqlSB.toString();
ToolLog.getLog().debug(sql);
ps.execute(sql);
sqlSB = new StringBuffer();
}
else
{
sqlSB.append(s);
sqlSB.append("\r\n");
}
}
}
finally
{
SessionFactory.close(session);
}
}
sql脚本
drop procedure if exists sp_update_table_field;
GO
create procedure sp_update_table_field()
begin
if not exists(select 1 from information_schema.columns where table_name='WB_Sys_Role' and column_name='Is_Export') then
ALTER TABLE WB_Sys_Role ADD Is_Export TINYINT NOT NULL default '0';
end if;
end
GO
call sp_update_table_field();
GO
drop procedure if exists sp_update_table_field;
GO
这样是可以执行的;
但是在mysql的客户端是不会执行的,客户端执行的sql语句是:
drop procedure if exists sp_update_table_field;
delimiter //
create procedure sp_update_table_field()
begin
if not exists(select 1 from information_schema.columns where table_name='WB_Sys_Role' and column_name='Is_Export') then
ALTER TABLE WB_Sys_Role ADD Is_Export TINYINT NOT NULL default '0';
end if;
end
//
delimiter ;
call sp_update_table_field();
drop procedure if exists sp_update_table_field;
delimiter 分节符命令,是不被java解析的!!!