`

java调用mysql的存储过程

阅读更多

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解析的!!!
0
0
分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics